IEBGENER - GENERATE Statement

Haresh · New User Joined: 04 Jan 2009 Posts: 18 Location: Germany

Hello,

I use the below control statement with IEBGENER to convert the zoned decimal into packed decimal value

GENERATE MAXFLDS=500,MAXLITS=500
RECORD FIELD=(15,93,ZP,1),FIELD=(5,1,ZP,9),
FIELD=(23,11,,12),FIELD=(28,46,,35)

I want to load the resultant dataset into a DB2 table. The problem is that the resultant converted packed decimal value is an unsigned decimal and so the DB2 load is failing with error message 'INPUT PACKED DECIMAL IS IN ERROR'. When i manually converted this for testing purpose the load job was successful.

Can anyone please tell me how to generate the signed packed decimal value out of generate statement.

Thank You

Phrzby Phil · Posted: Tue May 03, 2011 12:16 am

Has it occurred to you to show us your input/output, and not just your code?

Oh - hex please.

Haresh · New User Joined: 04 Jan 2009 Posts: 18 Location: Germany

for an example the value of the zoned decimal in the input is

Phrzby Phil · Posted: Tue May 03, 2011 12:28 am

And now your DB2 column attributes and load code?

Haresh · New User Joined: 04 Jan 2009 Posts: 18 Location: Germany

the DB2 column defnition is DECIMAL(15 , 0 ) NOT NULL and my load card is something like below

LOAD DATA RESUME NO LOG NO INDDN SYSREC00 INTO TABLE
xyz
(
aaa POSITION( 1: 8)
DECIMAL ,
.
.
.
)

Haresh · New User Joined: 04 Jan 2009 Posts: 18 Location: Germany

i have found a solution with DFSORT utility and it works fine, below is the control statement i used

Escapa · Posted: Tue May 03, 2011 7:13 pm

DFSORT has direct function to convert ZD TO PD....Look for TO=PD function in DFSORT documentation...

Haresh · New User Joined: 04 Jan 2009 Posts: 18 Location: Germany

thank you for the hint, i tried with DFSORT but getting the unsigned packed decimal only in the output. The problem is the input data from which i am converting are unsigned numeric values. Below is the code i tried,

Phrzby Phil · Posted: Wed May 04, 2011 7:22 pm

I do not have current DB2 access, but in a prior life have used LOAD quite a bit.

If there are any DB2 LOAD experts out there, is it really the case that an unsigned packed-decimal cannot be loaded?

Is there at least not some parm to cover this. I find this amazing!

Akatsukami · Posted: Wed May 04, 2011 8:52 pm

I ran a test:

I created a table with a DECIMAL (15,0) column
I added a few rows to it using DBX (File-AID for DB2)
I unloaded it with BMC Unload+
I edited the data set, changing the sign nybble on one of the decimal fields from C to F
I reloaded the data set with BMC Load+, using the control cards generated in step 3.

The load ran to CC=0; inspection of the table with DBX showed that DB2 had no difficulty recognizing the unsigned packed decimal. The TS is concealing something from us

Phrzby Phil · Posted: Wed May 04, 2011 9:08 pm

Thanks Akatsukami. It just made no sense that DB2 would not handle this.

Everyone should have questioned this.

TS - back to you!

Haresh · New User Joined: 04 Jan 2009 Posts: 18 Location: Germany

hello everyone,

i really apologise for wasting all of your time. This is totally out of my stupid mistake. This is what has happened,

- i created a load file out of IEBGENER GENERATE and loaded it into a DB2 table

- i got an error message as invalid decimal data, since i am new to the DB2 loads i didnt noticed that there has been already some records processed successfully

- for testing purpose i just copied the first record into a new dataset and changed the sign bit and ran the load job again and it was successful, hence from there on my assumption was completely that the problem was the unsigned decimal

- the actual problem was there was an corrupted data (character) in decimal field

Once again i am sorry for this.
the upside for me is that i learned a lot on DFSORT utility:-)

Phrzby Phil · Posted: Wed May 04, 2011 10:48 pm

Learn by stumbling - the best way.

You are probably worth your next pay grade now.