Conversion of characters to numerics from DB2 unload data.



Conversion of characters to numerics from DB2 unload data.

Postby nyamman » Tue Feb 19, 2013 1:04 pm

Hello,
I have unloaded data from database in the below format.

00A|0002D|0000A|0A|0A|00A|  |00{|0000{|0000{|0{|0{|00{
00A|0002D|0000B|0A|0B|00A|  |00{|0000{|0000{|0{|0{|00{
00A|0002D|0001G|0A|0A|00B|M |00A|0004D|0001H|0A|0A|00C


I would like to conver the above data to user understandable format as

001|00024|00001|01|01|001|  |000|00000|00000|00|00|000
001|00024|00002|01|02|001|  |000|00000|00000|00|00|000
001|00024|00017|01|01|002|M |001|00044|00018|01|01|003


How can i achieve this using SORT? Please help.

Code'd
nyamman
 
Posts: 5
Joined: Tue Feb 19, 2013 12:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Conversion of characters to numerics from DB2 unload dat

Postby BillyBoyo » Tue Feb 19, 2013 2:36 pm

Yes, you can.

You have signed, zoned-decimal data. All the values you show are positive. So they can all be changed to "unsigned, zoned-decimal".

Try

  OPTION COPY
  INREC OVERLAY=(1:1,3,ZD,TO=ZDF,LENGTH=3)


Can you arrange the unload into unsigned fields?

Can any of the fields be negative? What then, if so?

EDIT: I have put the "column" into the OVERLAY (the 1:). The default when not specified is 1: anyway, but I always like it to be explicit (but missed it this time).
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Conversion of characters to numerics from DB2 unload dat

Postby nyamman » Tue Feb 19, 2013 3:55 pm

Hi,
Thanks for the response... I am getting SYNTAX error..

//STEP03 EXEC PGM=SORT,REGION=8M
//SYSPRINT DD SYSOUT=*
//SYSLIST DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=TS3213.UNLOAD.DATA
//SORTOUT DD DISP=(,CATLG),DSN=TS3232.UNLOAD.SORTDATA,
// UNIT=SYSDA,SPACE=(TRK,(6000,6000),RLSE),
// DCB=(*.SORTIN)
//SYSIN DD *
OPTION COPY
INREC OVERLAY=(1,3,TO=ZDF,LENGTH=3)

I will try to get the unsigned unload.
nyamman
 
Posts: 5
Joined: Tue Feb 19, 2013 12:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Conversion of characters to numerics from DB2 unload dat

Postby BillyBoyo » Tue Feb 19, 2013 5:21 pm

Can you post the full SYSOUT from the failing step, in the Code tags please?

Why are you specifying SPACE in TRKs?

I'd ditch the DCB for your SORTOUT, and let SORT do it for you, much more flexible.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Conversion of characters to numerics from DB2 unload dat

Postby nyamman » Wed Feb 20, 2013 3:07 pm

Hi,
Below is the SYSOUT from the abended job.

ICE805I 1 JOBNAME: TESTJCLA , STEPNAME: STEP03                                 
ICE802I 0 BLOCKSET     TECHNIQUE IN CONTROL                                     
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 04:34 ON WED FE
           OPTION COPY                                                         
            INREC OVERLAY=(1,3,TO=ZDF,LENGTH=3)                                 
                               $                                               
ICE007A E SYNTAX ERROR                                                         
ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E7-K70685                     
ICE052I 3 END OF DFSORT                                                         
nyamman
 
Posts: 5
Joined: Tue Feb 19, 2013 12:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Conversion of characters to numerics from DB2 unload dat

Postby BillyBoyo » Wed Feb 20, 2013 3:22 pm

OK, so that's just "silly me".

  OVERLAY=(1:1,3,ZD,TO=ZDF)


Changed in original for clarity.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Conversion of characters to numerics from DB2 unload dat

Postby nyamman » Wed Feb 20, 2013 3:55 pm

Hi Thanks much for your response... I am able to run it successfully :) :) :) ... However i have the ZD values across the file, i am trying to use the below syntax, am getting the conversion applied only for the first occurance(i.e., 26th position only) not happening in 32 position. I am wrong with any syntax? Please correct me...
//STEP03   EXEC PGM=ICEMAN,REGION=8M                                   
//SYSPRINT DD  SYSOUT=*                                                 
//SYSLIST  DD  SYSOUT=*                                                 
//SYSOUT   DD  SYSOUT=*                                                 
//SORTIN   DD  DISP=SHR,DSN=T3213.TESTDATA.G013113         
//SORTOUT  DD  DISP=(,CATLG),DSN=T32231.TESTDATA.SORTOUT,           
//             DCB=(LRECL=81,RECFM=FB)                                 
//SYSIN DD *                                                           
 OPTION COPY                                                           
  INREC IFTHEN=(WHEN=(1,3,CH,NE,C' '),OVERLAY=(1,25,26,1,ZD,TO=ZDF)),   
        IFTHEN=(WHEN=(1,3,CH,NE,C' '),OVERLAY=(1,31,32,1,ZD,TO=ZDF))   
nyamman
 
Posts: 5
Joined: Tue Feb 19, 2013 12:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Conversion of characters to numerics from DB2 unload dat

Postby BillyBoyo » Wed Feb 20, 2013 4:56 pm

I'm not sure what you are trying there.

Have a look at this, which will convert the first few fields:

  INREC OVERLAY=(01:01,3,ZD,TO=ZDF,LENGTH=3,
                 05:05,5,ZD,TO=ZDF,LENGTH=5,
                 11:11,5,ZD,TO=ZDF,LENGTH=5,
                 17:17,2,ZD,TO=ZDF,LENGTH=2,
                 20:20,2,ZD,TO=ZDF,LENGTH=2,
                 23:23,3,ZD,TO=ZDF,LENGTH=3)


For each field, specifies a start column (those with ":" following the number) then the position and length of that field, type of ZD, conversion TO ZDF, and then the length of the field (else you get a default length).

You just need to extend this in a similar way for your entire line.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Conversion of characters to numerics from DB2 unload dat

Postby nyamman » Wed Feb 20, 2013 5:11 pm

Thanks much for your response... I made the changes as required and am good with the results.. Once again thankyou very much for the timely response... :)

//SYSIN DD *                                   
 OPTION COPY                                   
 INREC OVERLAY=(26:26,1,ZD,TO=ZDF,LENGTH=1,     
                  32:32,1,ZD,TO=ZDF,LENGTH=1,   
                  38:38,1,ZD,TO=ZDF,LENGTH=1,   
                  41:41,1,ZD,TO=ZDF,LENGTH=1,   
                  44:44,1,ZD,TO=ZDF,LENGTH=1,   
                  48:48,1,ZD,TO=ZDF,LENGTH=1)   
nyamman
 
Posts: 5
Joined: Tue Feb 19, 2013 12:57 pm
Has thanked: 0 time
Been thanked: 0 time


Return to Stupid Questions

 


  • Related topics
    Replies
    Views
    Last post