Page 1 of 1

sortjcl

PostPosted: Sat Sep 14, 2013 1:19 am
by dc5008253
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.

Re: sortjcl

PostPosted: Sat Sep 14, 2013 1:29 am
by Akatsukami
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.

Re: sortjcl

PostPosted: Sat Sep 14, 2013 12:09 pm
by dc5008253
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.

Re: sortjcl

PostPosted: Sat Sep 14, 2013 3:01 pm
by prino
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?

Re: sortjcl

PostPosted: Sat Sep 14, 2013 7:32 pm
by Akatsukami
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.

Re: sortjcl

PostPosted: Sat Sep 14, 2013 7:38 pm
by NicC
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

Re: sortjcl

PostPosted: Sun Sep 15, 2013 2:59 pm
by BillyBoyo
//         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 :-)