Help on IBM UNLOAD using IKJEFT01 (First post)



IBM's flagship relational database management system

Help on IBM UNLOAD using IKJEFT01 (First post)

Postby Ray1975 » Fri May 08, 2009 5:44 am

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
Ray1975
 
Posts: 3
Joined: Fri May 08, 2009 5:10 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby dick scherrer » Fri May 08, 2009 6:18 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby nandakishore » Fri May 08, 2009 6:47 am

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)
nandakishore
 
Posts: 1
Joined: Fri May 08, 2009 6:35 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Ray1975 » Fri May 08, 2009 7:23 am

Thanks, for your inputs. I sent some details to Dick so am hoping for some pointers.
Ray1975
 
Posts: 3
Joined: Fri May 08, 2009 5:10 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby dick scherrer » Fri May 08, 2009 7:38 am

Hello,

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

Details should be posted here in the topic. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby Ray1975 » Fri May 08, 2009 10:50 pm

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
--------------------------------
Ray1975
 
Posts: 3
Joined: Fri May 08, 2009 5:10 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby dick scherrer » Sat May 09, 2009 2:40 am

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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post