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):
- 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).
- 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