sortjcl



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

sortjcl

Postby dc5008253 » Sat Sep 14, 2013 1:19 am

Hi Friends,my task is to unload a table i am having one ps which contains 8 lakhs Records,which contains primary key of length s9(18) usage comp in cobol and in table it is defined as binary not null.so,i want to unload the table based on these values.As i cannot hard code these 8 lakh values in SYSIN DD *.Is there any other possible way where i can use these values in my query without hard coding it.
dc5008253
 
Posts: 2
Joined: Sat Sep 14, 2013 12:44 am
Has thanked: 0 time
Been thanked: 0 time

Re: sortjcl

Postby Akatsukami » Sat Sep 14, 2013 1:29 am

I think that your best bet would be to unload the entire table, then use JOINKEYS to select those unload records corresponding to the keys in the reference data set.

You do not say what sort product your shop has. If it is DFSORT, Kolusu can undoubtedly help you (and this thread will be moved to the DFSORT forum); if it is Syncsort Mr. Boyo very likely can.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: sortjcl

Postby dc5008253 » Sat Sep 14, 2013 12:09 pm

Hi Akatsukami,Thanks for your response.My query in jcl will be like
//sysin dd *
unload direct no
Select * from tc_501_prim_char_dtl
where mer_osd_settle_id in ('12345678',
'23456789',
.
.
.
.
.
'12345634')
But i cannot hard code all 8 lakh values in this step is any other way where i can directly call my ps in jcl.I am using DFSORT.
dc5008253
 
Posts: 2
Joined: Sat Sep 14, 2013 12:44 am
Has thanked: 0 time
Been thanked: 0 time

Re: sortjcl

Postby prino » Sat Sep 14, 2013 3:01 pm

dc5008253 wrote:But i cannot hard code all 8 lakh values in this step is any other way where i can directly call my ps in jcl.I am using DFSORT.

Yes, you can write a program in your favourite programming language, instead of wasting everybody's time asking for something that is not possible with SORT. The JCL that invokes the program can have a DDNAME for your dataset.

Happy?
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: sortjcl

Postby Akatsukami » Sat Sep 14, 2013 7:32 pm

dc5008253 wrote:Hi Akatsukami,Thanks for your response.My query in jcl will be like
//sysin dd *
unload direct no
Select * from tc_501_prim_char_dtl
where mer_osd_settle_id in ('12345678',
'23456789',
.
.
.
.
.
'12345634')
But i cannot hard code all 8 lakh values in this step is any other way where i can directly call my ps in jcl.I am using DFSORT.

My shop uses Syncsort, but I believe that the syntax is identical in this case. I wrote the following job:
//TOOLJOIN JOB  ,'JOINKEYS',CLASS=9,MSGCLASS=1,SCHENV=DB2@HD0D         
//*        TYPRUN=SCAN                                                 
//*MAIN FAILURE=CANCEL,LINES=(200,C)                                   
//*-----------------------------------------------------------------*   
//UNLOAD   EXEC BMCUNLD,RC='4',                                         
//             SYSTEM='HD0D',                                           
//             UID='TOOLUNLD1',                                         
//             UTPROC='NEW'                                             
//*                                                                     
//SYSREC   DD   DSN=xxxx.BAR,DISP=(NEW,CATLG,DELETE),                   
//        UNIT=DISK,SPACE=(CYL,(1,100),RLSE)                           
//SYSCNTL  DD   DSN=xxxx.BAR.L,                                         
//           DISP=(NEW,CATLG,DELETE),                                   
//           SPACE=(CYL,(1,1),RLSE),                                   
//           UNIT=DISK                                                 
//SYSIN    DD   *                                                       
  UNLOAD                                                               
    ORDER YES                                                           
    MAXSORTS 3                                                         
    ON MESSAGE 50253 CONTINUE UTILITY                                   
    SELECT * FROM AFTOOLS.EAMAY                                         
      ORDER BY JOB_ID                                                   
/*                                                                     
//JOIN     EXEC PGM=SORT                                               
//SYSOUT   DD   SYSOUT=*                                               
//SORTJNF1 DD   DSN=xxxx.REFER.ENCE,DISP=SHR                           
//SORTJNF2 DD   DSN=xxxx.BAR,DISP=SHR                                   
//SORTOUT  DD   DSN=xxxx.CARTESIA.NPRODUCT,DISP=(,CATLG,DELETE),       
//         DCB=*.SORTJNF2,SPACE=(CYL,(1,100),RLSE),UNIT=DISK           
//SYSIN    DD   *                                                       
  JOINKEYS FILE=F1,FIELDS=(3,2,A)                                       
  JOINKEYS FILE=F2,FIELDS=(1,2,A)                                       
  REFORMAT FIELDS=(F2:1,412)                                           
  OPTION   COPY                                                         

The first step unloads an entire table to BAR, ordering (i.e., sorting) it by the sort key. The second step takes that data set and the sorted list of wanted keys (REFER.ENCE), and writes those unloaded rows with keys matching the reference to CARTESIA.NPRODUCT, the result being as if the reference list had been incorporated in the query.

Two limitations, that our *Sort mavens can help you overcome (I could have gotten around them with a little research and experimentation, but I decided to act like a software engineer):
  1. The keys have to be the same size; the key on the tale is SMALLINT, but the key in REFER.ENCE is a 4-byte binary (= INTEGER).
  2. The actual LRECL needs to be given on the REFORMAT statement.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: sortjcl

Postby NicC » Sat Sep 14, 2013 7:38 pm

You are doing a DB2 unload using a DB2 utility. At least, I presume it is a DB2 utility as thos are not sort control cards. Why are you entitling this sortcl? If it really was JCL then it should be posted in the JCL section, if sort then in one of the sort sections, the one that refers to the sort product that you use.Now, what is it? A DB2 query, a Sort query or a JCL query?

If DB2 why not try WHERE column_name < value ?

BTW lahk is not a valid term - use 800,000 or 800k or...but not lahk
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: sortjcl

Postby BillyBoyo » Sun Sep 15, 2013 2:59 pm

//         DCB=*.SORTJNF2,SPACE=(CYL,(1,100),RLSE),UNIT=DISK           

//SYSIN    DD   *                                                       
  JOINKEYS FILE=F1,FIELDS=(?,?,A),SORTED                                       
  JOINKEYS FILE=F2,FIELDS=(1,8,A),SORTED                                       
  REFORMAT FIELDS=(F2:1,lreclf2)                                           
  OPTION   COPY                   


If the data are already sorted for any file, specify SORTED (for SyncSort) or SORTED,NOSEQCHK (for DFSORT).

The DCB reference is fine where the LRECL/BLKSIZE/RECFM are not changed in the sort, but more flexible to leave off all the DCB info and let SORT do it.

If the keys are of a different length, then the choices depend on the sort product and what is installed for that product.

Current versions of SyncSort support, but do not document, JNFnCNTL files. From 1.3.2 (I think it is) there is a patch available to implement JNFnCNTL files.

Using the JNFnCNTL files is a much cleaner way to do it.

Without JNFnCNTL files, put an OMIT on the JOINKEYS to exclude key values which are higher than the maximum possible on the file with the shorter key, then use the length of of shorter key (amending the start position appropriately). The OMIT will prevent "false hits".

Lakhs is fine, just remember we don't necessarily know what it means, and may have no motivation to look it up. Also using commas to separate elements of numbers in groups of three is what we are used to. Anything else is in danger of confusing us :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to DFSORT/ICETOOL/ICEGENER