Page 1 of 1

Help on IBM UNLOAD using IKJEFT01 (First post)

PostPosted: Fri May 08, 2009 5:44 am
by Ray1975
Dear Listers,

I used PGM=IKJEFT01 to successfully unload data from a DB2 table. However,
when I browse the dataset, the column value is not readable (it shows up as
binary). This caused the LRECL to be less than the expected record length. I
need to be able to see the data in the dataset (External format ?) as the binary
data is causing an ABEND in the downstream COBOL program

How do I modify the SYSIN cards to achive the above? The SYSIN currently has the
following SELECT statement:
=======================================================================
SELECT T1.C1, C1 defined as INTEGER
T1.C2, C2 defined as SMALLINT
T1.C3, C3 defined as INTEGER
T2.C5 C5 defined as DATE
FROM T1, T2
WHERE T1.C1 = T2.C1
AND T1.C4 = 'A'
=========================================================================

I had tried with following CARDS but got a SQLCODE a -104
SELECT T1.C1,
T1.C2,
T1.C3,
T2.C5
INTO col1 INTEGER EXTERNAL(9),
col2 SMALLINT EXTERNAL(4),
col3 INTEGER EXTERNAL(9),
col4 CHAR(10)
FROM T1, T2
WHERE T1.C1 = T2.C1
AND T1.C4 = 'A'

Looking for any pointers

Re: Help on IBM UNLOAD using IKJEFT01 (First post)

PostPosted: Fri May 08, 2009 6:18 am
by dick scherrer
Hello and welcome to the forum,

Suggest a better solution would be to correct to downstream cobol program to use the proper field definitions.

There is no reason to convert to an external format if the data is to be read/processed by code downstream. There is extra overhead converting the data between formats that is just wasted.

Post a bit of the unloaded data in hex and we can determine the proper pictures fof the fields.

Re: Help on IBM UNLOAD using IKJEFT01 (First post)

PostPosted: Fri May 08, 2009 6:47 am
by nandakishore
I do not know if this helps, but just posting my thoughts.

Integer in DB2 - is PIC S9(8) COMP in COBOL
Smallint in DB2 - is PIC S9(4) COMP in COBOL
Date in DB2 - will be retrieved as PIC X(10)

Re: Help on IBM UNLOAD using IKJEFT01 (First post)

PostPosted: Fri May 08, 2009 7:23 am
by Ray1975
Thanks, for your inputs. I sent some details to Dick so am hoping for some pointers.

Re: Help on IBM UNLOAD using IKJEFT01 (First post)

PostPosted: Fri May 08, 2009 7:38 am
by dick scherrer
Hello,

I sent some details to Dick
? No details here. . .

Details should be posted here in the topic. . .

Re: Help on IBM UNLOAD using IKJEFT01 (First post)

PostPosted: Fri May 08, 2009 10:50 pm
by Ray1975
Dunno what happenned ..I did hit the Reply to send the details to you ..Here are the details ..

Change to the COBOL program will take more time and effort (Unit Test/System test/UAT path). Somehow the perception is any change via utility introduces less chances of error, so .... :)

My current unload looks like this: (Data is not the same in the two files but the format is)
-------------------
..þÇ...ªÝ2004-07-01
00860039AFFFF6FF6FF
01E800BAD2004007001
 ------------------


Target file should be like this :

   101992   09999927002004-07-01
444FFFFFF444FFFFFFFFFFFFFF6FF6FF
00010199200009999927002004007001
--------------------------------

Re: Help on IBM UNLOAD using IKJEFT01 (First post)

PostPosted: Sat May 09, 2009 2:40 am
by dick scherrer
Hello,

Suggest you get comfortable with the "Code" tag (your data has been "coded") - this makes source, jcl, data, etc much more readable because it preserves alignment.

Why are there 3 spaces preceeding each number?

999992700 does not = 3B9AAD . . . It does however = 3B9AAD7C. . .

I don't know if you can force that pattern with the unload. . . I suppose you could add a sort step to reformat the data if the unload won't do this.

Change to the COBOL program will take more time and effort (Unit Test/System test/UAT path). Somehow the perception is any change via utility introduces less chances of error, so ....
Shouldn't be much time and effort. . . The perception is rather poor at best. . . Fiddling around with data that is already usable is more risky than using the data as it was delivered from the database utility. . .