DB2 export in fixed length for NULL columns

[DB2-v789]

Hello,

Here's is the scenario:

Table: TEST1

Columns:

C1 Char (5) Not Null
C2 Char(8) Null
C3 Char(4) Null

Data when exported in FIXED LENGTH file using asc/ixf looks like:

Row1: "AAAAA"|"ABCDEFGH"|"WXYZ"|
Row2: "AAAAA"|"ABC "|"DEF"|
Row3: "DDDDD"|"ABCDEFGH"|"X "|
Row4: "BBBBB"|||

Expected result for Row4 should look like:

Row4: "BBBBB"|" "|" "|

Please advise.
Thanks,
Myjish

[jefftyzzer]

EXPORT TO myfile OF IXF
SELECT C1, COALESCE(C2, CAST('' AS CHAR(8))), COALESCE(C3, CAST('' AS
CHAR(4))) FROM TEST1;

[DB2-v789]

> CHAR(4))) FROM TEST1;- Hide quoted text -
>
> - Show quoted text

Thanks for your reply.

I am getting SQL0171N error: The data type, length or value of
argument "2" of routine "sysibm.coalesce" is incorrect"

Please advise.

[Lennart]

Post ddl for the table and the export command. Given your description
of the table, Jeff's export is correct.

/Lennart

[DB2-v789]

> /Lennart- Hide quoted text -
>
> - Show quoted text -

Hello,

Table: DB2INST1.ABC123

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
SEQ_NUM SYSIBM BIGINT 8 0
No
CNO SYSIBM VARCHAR 6
0 No
BID SYSIBM BIGINT
8 0 Yes
AMT SYSIBM DECIMAL 10 0 Yes
SID SYSIBM CHARACTER 2 0
Yes
LUPD SYSIBM TIMESTAMP 10 0
Yes
TLINK SYSIBM VARCHAR 300 0
Yes

I want to export data from this table into FIXED LENGTH format (in
order to upload into IBM mainframe)

Export command that i used in DEL modified by coldel| format looks
something like this:

export to abc123.asc of del modified by coldel|
select
cast(SEQ_NO as char(8)),
cast(CNO as char(6)),
char(BID as char(8)),
cast(AMT as char(10)),
char(SID as char(2)),
cast(LUPD as char(26)),
cast(TLINK as varchar(300))
from db2inst1.abc123

I have cast all the data type to Char and Currently, exported data
looks like

Row1 --> "1001 "|"131 "|"3343 "|"1231313
"|"00"|"2001-02-10-11.07.22.238000"|"ABCDEFASDFSAFSADFSAFSADFSJFLSFJSAADFS
"|
ROw2 --> "1002 "|"232
"||||"2001-03-14-12.07.22.238000"|"DFSJFLSFJSAADFS
"|
ROw3 --> "1003 "|"231 "|" "|"
"|"00"|"2001-04-14-12.07.22.238000"||

Expected data after export should look like:

Row1 --> "1001 "|"131 "|"3343 "|"1231313
"|"00"|"2001-02-10-11.07.22.238000"|"ABCDEFASDFSAFSADFSAFSADFSJFLSFJSAADFS
"|
ROw2 --> "1002 "|"232 "|" "|"
"|"00"|"2001-03-14-12.07.22.238000"|"DFSJFLSFJSAADFS
"|
ROw3 --> "1003 "|"231 "|" "|" "|"
"|"2001-04-15-12.07.22.238000"|"
"|

Please advise.

Thanks,

[jefftyzzer]

I thought your issue was wanting to get NULL "values" to pad-out to
the length of the underlying datatype.

--Jeff

[Ian]

First of all, you probably want to differentiate between NULL and
some other value. For character columns, NULL is usually <> ''.
And for decimal columns, NULL != 0.0

Since many of your columns are nullable, you probably want to
check with whomever is receiving this data to make sure about how
they want to handle this.

Typically in a fixed width format you will have extra null indicators
to signal whether a column is supposed to be NULL.

If you truly don't care about losing NULLs, you need to use COALESCE,
as was suggested earlier. For the TLINK column:

cast(coalesce(TLINK,'') as CHAR(300))

Also: Don't cast as VARCHAR -- you won't get fixed-width fields!

[Lennart]

First an advise, when people ask for ddl they usually want a create
statement of some kind, in this case for the table. With the
description you posted of the table anyone trying to help, first have
to parse the description, and then create a statement of their own.
This of course only takes a minute or two, but surely will will have a
negative effect on the number of people trying to help. Beside that,
there is something wrong with either your description of the table, or
your query because in the query there is a column SEQ_NO that does not
exist in the table.

That said, if C is a numeric type you can't coalesce(C, cast('' as
char(...))). That explains the SQL0171N you got in your second post.
For this last post I'm not sure what the problem is, but I assume it
has something to do with nulls not allocating any space in the file.
Use something like:

coalesce(cast(SEQ_NO as char(8)), cast('' as char(8)))

to overcome this. Finally, I have no experience with mainframe, but I
would look into the possibilities during load/import. Perhaps this
whole thing with types and nulls is a non issue?

HTH
/Lennart

[jefftyzzer]

On May 20, 2:31 pm, DB2-v789 <tojigneshs...@gmail.com> wrote:

Another option:

Since the CLP effectively outputs its results in fixed-length format,
use the -r option to redirect the results to a file and use this
output as the basis for the subsequent import.

--Jeff