Unload Numeric Format Data from DB2 to dataset



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

Unload Numeric Format Data from DB2 to dataset

Postby mainframe_novice » Wed Apr 28, 2010 4:08 am

I was trying to unload DB2 data through query which involves joins and has functions like sum . There are 2 amount fields which are defined as Decimal 11,2 in DB2 .

I tried BMCUNLOAD and DSNTIUL .
But while data in non numerica fields was accurate , I found junk / garbage data in numeric fields .

I was suggested to use the copybook structure in order to view this data .

Since there is no processing involved and I just load the data back to other table , I don't prefer to add a copybook .

Can somebody help ?

IBM Unload,BMCUNLOAD or whatever is just fine with me .

Appreciate your help !

Please let me know if any additional information is required.
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby William Thompson » Wed Apr 28, 2010 4:28 am

mainframe_novice wrote:But while data in non numerica fields was accurate , I found junk / garbage data in numeric fields .
Please let me know if any additional information is required.
Yes, please provide a 'hex on' cut&paste image of the "junk / garbage" fields.
William Thompson
 
Posts: 81
Joined: Sat Jun 09, 2007 4:24 am
Location: Tucson AZ
Has thanked: 0 time
Been thanked: 1 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby dick scherrer » Wed Apr 28, 2010 6:07 am

Hello,

Do you understand packed-decimal and binary 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: Unload Numeric Format Data from DB2 to dataset

Postby mainframe_novice » Wed Apr 28, 2010 10:11 am

Little bit....

However I found some sample code on this and other forums where SORT was used for converting these values to readable format .
It was a combination of Sort,Edit and outrec .

I tried to write similar code but got the error CD = S000|U0016 when I tried to submit the job .I couldn't get more information on this error.
PREP was successfull on the job .

Can someone help me understand what happens when we unload the numeric fields from DB2 ? What format they are in ? And how they can be converted into readable format ?

For now I just see some symbols like & , @ or some letters in these fields .
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby William Thompson » Wed Apr 28, 2010 10:43 am

mainframe_novice wrote:I tried to write similar code but got the error CD = S000|U0016 when I tried to submit the job .I couldn't get more information on this error.
PREP was successfull on the job .
Please post the cut&paste of the JCL and all sysouts.
Can someone help me understand what happens when we unload the numeric fields from DB2 ?
Yes, if you provide us with more information as mentioned above.
What format they are in ? And how they can be converted into readable format ?
For now I just see some symbols like & , @ or some letters in these fields .
As said, more information from you is needed for these requests.
William Thompson
 
Posts: 81
Joined: Sat Jun 09, 2007 4:24 am
Location: Tucson AZ
Has thanked: 0 time
Been thanked: 1 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby mainframe_novice » Wed Apr 28, 2010 5:58 pm

db2 STRUCTURE for table car_sales
Car_Make Char 40
Car_Model Char 40
Sales_Price Decimal 11,2
Down_Payment Decimal 11,5

In the unloaded flat file I found the data for
Sales_Price was at position 81-89
Down_Payment at position 90,94
(I don't know why the data is not as per the db2 structuure..)
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby Tina_campbell » Wed Apr 28, 2010 6:10 pm

This is similar to what I had found on one of the sites
//S1 EXEC  PGM=SORT
//SORTIN      DD  DSN =DATA.CARSALES.RESULT,DISP=SHR
//SORTOUT   DD  DSN =DATA.CARSALES.RESULT,DISP=SHR
//SYSOUT      DD  SYSOUT=*
//SYSIN      DD  SYSOUT=*
    SORT FIELDS=COPY
    OUTREC FIELDS =(1,80,81,9,PD,EDIT(IIIITTTTT.TT),90,5,PD,EDIT=(ITTTTT.TTTTT))
/*


It failes with S0000|U0016
Tina_campbell
 
Posts: 4
Joined: Tue Apr 27, 2010 10:38 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby mainframe_novice » Wed Apr 28, 2010 7:20 pm

Yes...I still need to get it working
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby mainframe_novice » Wed Apr 28, 2010 9:01 pm

I figured it out .I can't use the same file as an input and output .
Because the length of input file is truncated since data was unloaded in binary format .
When through sort we convert it back to decimal , the same record length won't help .

Thanks everyone for the help .

If there is a way by which I can avoid SORT and directly unload it into readable decimal format , please reply .
Appreciate everybody's help again !
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Unload Numeric Format Data from DB2 to dataset

Postby Frank Yaeger » Wed Apr 28, 2010 10:24 pm

Tina,

I don't know where you got that SORT job, but it has several problems. Here's a version of it that wouldn't fail (although I can't say if it would do what you want since I don't know what you want to do).

//S1 EXEC  PGM=SORT
//SORTIN   DD  DSN=...  input file
//SORTOUT  DD  DSN=...  output file
//SYSOUT   DD  SYSOUT=*
//SYSIN  DD  *
    SORT FIELDS=COPY
    OUTREC FIELDS=(1,80,81,9,PD,EDIT=(IIIIIIIIT.TT),
       90,5,PD,EDIT=(IIIIIT.TTTTT))
/*


Note that the input and output must be different files.

If you're not familiar with DFSORT and DFSORT's ICETOOL, and you'd like to be, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/support/docview.wss? ... g3T7000080
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Next

Return to JCL

 


  • Related topics
    Replies
    Views
    Last post