View previous topic :: :: View next topic

Author 
Message 
gotlamitla
New User
Joined: 31 Oct 2006 Posts: 11




I have a DB2 column defined as Decimal(4,0) and is getting changed to Decimal(6,2). I have a file with old column length in a flatfile in PD format. I have to create a load file by converting this field to the new length(decimal 6,2) which is also in PD format.
Do we have any sample to convert a PD format field length as required to include decimal positions ? 

Back to top 




shankar.v
Active User
Joined: 25 Jun 2007 Posts: 196 Location: Bangalore




gotlamitla,
Let us assume the DB2 column defined as Decimal(4,0) is in position 1 and length 3 of load file.
Please check with the following code for your requirement.
Code: 
// EXEC PGM=SORT
//SORTIN DD DSN=OLDLOAD.FILE,DISP=SHR
//SORTOUT DD DSN=NEWLOAD.FILE,DISP=...
//SYSOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC BUILD=(1,3,PD,MUL,+100,TO=PD,LENGTH=4)
/*
// 
Thanks,
Shankar 

Back to top 


gotlamitla
New User
Joined: 31 Oct 2006 Posts: 11




Thanks for the quick response.
But I understand that this sample you have given will not harm the integer part even it is getting multiplied with +100 as Decimal(4,0) and Decimal(6,2) will have integer part as S9(4) (execluding decimal part) ..
Ex: 1234 in decimal(4,0)
after the jcl ... it will be 123400 which will get mapped as 1234.00 for decimal(6,0)
I guess this approach creates problem if the destination field is having length decimal(11,2).
Let me know how can we acheive this.
Correct me if my understanding is wrong. 

Back to top 


shankar.v
Active User
Joined: 25 Jun 2007 Posts: 196 Location: Bangalore




Hi gotlamitla,
Quote: 
I guess this approach creates problem if the destination field is having length decimal(11,2). 
Modify the before posted code as per the column datatype and length.
Let us assume the DB2 column defined as Decimal(4,0) changed to Decimal(11,2), then we have to modify the before posted code as below to get the new load file.
Code: 
OPTION COPY
INREC BUILD=(1,3,PD,MUL,+100000000,TO=PD,LENGTH=6) 
Thanks,
Shankar 

Back to top 


Frank Yaeger
DFSORT Moderator
Joined: 15 Feb 2005 Posts: 7130 Location: San Jose, CA




I don't use DB2 so I don't know the notation being used here.
Does Decimal(4,0) mean 4 digits with 0 digits after the decimal point (e.g. 1234)?
Does Decimal(6,2) mean 6 digits with 2 digits after the decimal point (e.g. 1234.56)?
Does Decimal(11,2) mean 11 digits with 2 digits after the decimal point (e.g. 123456789.01)?
If so, then to convert from Decimal(4,0) to Decimal(11,2), you could use:
Code: 
OPTION COPY
INREC BUILD=(1,3,PD,MUL,+100,TO=PD,LENGTH=6)

That would convert 1234 = X'01234C' = PL3'1234' to 123400 = X'00000123400C' = PL6'123400'.
Unless I'm misinterpreting what the Decimal notation means, I don't know why Shankar is multiplying by +100000000. 

Back to top 


Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA




He shouldn't be, he should just be multiplying by +100 to add 2 zeros to the end. 

Back to top 


shankar.v
Active User
Joined: 25 Jun 2007 Posts: 196 Location: Bangalore




Hi Craq Giegerich/Frank Yaeger
Quote: 
He shouldn't be, he should just be multiplying by +100 to add 2 zeros to the end. 
Yes, you are correct and I am wrong. Sorry for posting the wrong code.
Thanks,
Shankar 

Back to top 



