View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
Has it occurred to you to show us your input/output, and not just your code?
Oh - hex please. |
|
Back to top |
|
|
Haresh
New User
Joined: 04 Jan 2009 Posts: 18 Location: Germany
|
|
|
|
for an example the value of the zoned decimal in the input is
Code: |
FFFFFFFFFFFFFFF
220655171701002 |
and the value of the packed decimal in the output is
i need the signed packed decimal in the output, like below
|
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
And now your DB2 column attributes and load code? |
|
Back to top |
|
|
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 ,
.
.
.
) |
|
Back to top |
|
|
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
Code: |
//SYSIN DD *
OPTION COPY -
INREC FINDREP(INOUT=(X'1F',X'1C',X'2F',X'2C',X'3F',X'3C',X'4F',
X'4C',X'5F',X'5C',X'6F',X'6C',X'7F',X'7C',
X'8F',X'8C',X'9F',X'9C',X'0F',X'0C'),
STARTPOS=8,ENDPOS=11) |
thank you |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
DFSORT has direct function to convert ZD TO PD....Look for TO=PD function in DFSORT documentation... |
|
Back to top |
|
|
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,
Code: |
OPTION COPY -
OUTREC PARSE=(%01=(ABSPOS=93,FIXLEN=15),
%02=(ABSPOS=01,FIXLEN=05),
%03=(ABSPOS=11,FIXLEN=23),
%04=(ABSPOS=46,FIXLEN=28)),
BUILD=(1:%01,UFF,TO=PD,LENGTH=8,
9:%02,UFF,TO=PD,LENGTH=3,
%03,%04) |
so for the moment i will stick to my previous solution :-) |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
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! |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1787 Location: Bloomington, IL
|
|
|
|
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 |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
Thanks Akatsukami. It just made no sense that DB2 would not handle this.
Everyone should have questioned this.
TS - back to you! |
|
Back to top |
|
|
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:-) |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
Learn by stumbling - the best way.
You are probably worth your next pay grade now. |
|
Back to top |
|
|
|