Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Executing DB2 queries using SYNCSORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
raam_kumar

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Thu May 10, 2007 1:51 pm    Post subject: Executing DB2 queries using SYNCSORT
Reply with quote

Hi
I tried executing DB2 queries using SYNCSORT. while retrieving the amount fields there is a problem. here are the details

Record Description:

COLUMN START END DB2
NAME POSITION POSITION DATA TYPE
-------------------- -------- -------- --------------------
AMOUNT 58 64 DECIMAL(13,2)

The query is executing properly. but in output files at these positions (only for which data type is decimal) i am getting junk values instead of original amount values

Sample output (for the above mentioned positions)

" ?? "
" ??- "

Is it possible to print the amount in readable format?Could any one help me regarding this?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu May 10, 2007 9:15 pm    Post subject:
Reply with quote

Hello,

Your process asked for packed-decimal data and what was produced is packed-decimal data. If it is gong into another mainframe process, the packed-decimal data should work as-is. If you look at your output in hex, you will be able to see the values.

If you post your jcl and control statements, we can offer recommendations regarding converting the output to zoned decimal or some other format..
Back to top
View user's profile Send private message
raam_kumar

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Fri May 11, 2007 9:57 am    Post subject:
Reply with quote

Hi

Here is the jcl which i have used

Code:

//SORTSQL  EXEC PGM=SYNCSORT,PARM='DB2=DSN'                 
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,20)                     
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,20)                     
//SORTDBIN DD DIPS=SHR,DSN=<query dsn name>                                             
//SORTIN   DD DUMMY                                         
//SORTOUT  DD DSN=<output dataset name>,                       
//         DISP=(,CATLG,DELETE),                     
//         LRECL=80,RECFM=FB,BLKSIZE=0,           
//         SPACE=(CYL,(500,50),RLSE),MGMTCLAS=WORKM     
//SYSOUT   DD SYSOUT=*                                     
//SYSIN    DD *                                             
  SORT FIELDS=COPY                                           
/*                                                         


Query statement:

Code:

SELECT A.<FIELD1>, A.<FIELD2>, B.<FIELD3>, B.<FIELD4>, 
B.<FIELD5>, C.<FIELD6>, C.<FIELD7>, C.<FIELD8>,         
C.<FIELD9>,A.<FIELD10>,A.<FIELD11>,D.<FIELD12>         
FROM <TABLE1> A,                                       
<TABLE2> B,                                             
<TABLE3> C,                                             
<TABLE5> D                                             
WHERE B.<FIELD3> = C.<FIELD3> AND                       
B.<FIELD4> IN ('C','1') AND                             
B.<FIELD5> IN ('O','B','D','D','P') AND                 
A.<FIELD2> = B.<FIELD2> AND                             
A.<FIELD11> IN ('A','L') AND                           
A.<FIELD13> = '0012345' AND                             
A.<FIELD14> = B.<FIELD14> AND                           
B.<FIELD14> = C.<FIELD14> AND                           
C.<FIELD15> = D.<FIELD15> AND                           
C.<FIELD14> = 'CP' ORDER BY A.<FIELD1>                 
WITH UR;
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 11, 2007 7:02 pm    Post subject:
Reply with quote

Hello,

Please post a few of the input records, the output records for those input records and the sysout info from the sort process.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need help with below queries ashek15 DB2 3 Thu Mar 23, 2017 1:38 am
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 7 Sat Jan 21, 2017 4:02 am
No new posts Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts Viewing executing process in NDM .. q... Debb.Brant IBM Tools 0 Wed Dec 14, 2016 10:55 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us