IBM Mainframe Forum

Posted: **Thu Jun 16, 2011 12:58 pm**

Hi Please help me out with a JCL for the following scenario:

File 1

123 aaaaaaaa 2010-05-06 fffff
123 aaaaaaaa 2010-04-06 ggggg
234 cccccccc 2010-07-09 kkkkk
456 dddddddd 2010-09-05 ppppp
456 dddddddd 2010-06-06 rrrrr

File 2

123 1998-09-08
123 1997-05-06
234 1996-07-08
456 1995-09-02
456 1994-01-01

The output file should be as follows:

Output:

123 aaaaaaaa 1998-09-08 fffff
123 aaaaaaaa 1997-05-06 ggggg
234 cccccccc 1996-07-08 kkkkk
456 dddddddd 1995-09-02 ppppp
456 dddddddd 1994-01-01 rrrrr

It should be noted that for the first occurance of the key in the file1 the first timestamp of file2 should be replaced. For the second occurance of the key in file1 second timestamp of file 2 should be replaced

Posted: **Thu Jun 16, 2011 2:48 pm**

Seems like you are wanting a sort job to do a joinkeys operation. Nothing to do with JCL except that you need JCL to specify to the operating system what program you want to run and which files to be used by the program.

For Sort you will have to specify your sort product - probably DFSORT or SYNCSORT, the RECFM and LRECL of each file, specify whether duplicates can exist in either file, if either file can be empty, key fields and rules for getting from input to output. You should also use code tags around your input/output to help keep alignment.

Posted: **Thu Jun 16, 2011 3:35 pm**

Hi Nic,

Thanks for your suggestion.

Below is the JCL i have used.

Select all

//STEP01 EXEC PGM=SYNCSORT
//SORTJNF1 DD DSN=File1,DISP=SHR
//SORTJNF2 DD DSN=File2,DISP=SHR
//SORTOUT DD DSN=output,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(TRK,(25,15),RLSE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,3,A)
JOINKEYS FILE=F2,FIELDS=(1,3,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,13,F2:5,10,F1:25,5)
SORT FIELDS=COPY

The output i get from the above JCL is below which is not as expected,

Select all

123 aaaaaaaa 1998-09-08 fffff
123 aaaaaaaa 1997-05-06 fffff
123 aaaaaaaa 1998-09-08 ggggg
123 aaaaaaaa 1997-05-06 ggggg
234 cccccccc 1996-07-08 kkkkk
456 dddddddd 1995-09-02 ppppp
456 dddddddd 1994-01-01 ppppp
456 dddddddd 1995-09-02 rrrrr
456 dddddddd 1994-01-01 rrrrr

Please advise on what should be modified in the JCL.

Posted: **Wed Nov 16, 2011 1:45 am**

I don't see enough information about the details of these files, but it appears that there is a 1-1 relationship in them and this solution is based on that assumption. If this is not true, then this solution will not work.

This solution is to send each of your input files through separate sort processes that will assign a sequential number to each record and adds that value to the end of the file. I assume that your file is 29 bytes long, so this process will add a sequence number that is 7 bytes long to the end of the file (columns 29-36). Then a sort join process is done using both your original key in columns 1-3 and the sequence number in columns 29-36 as join key values. This output should give you what you have asked for.

Select all

//SORT1 EXEC PGM=SYNCSORT
//SORTIN DD *
123 AAAAAAAA 2010-05-06 FFFFF
123 AAAAAAAA 2010-04-06 GGGGG
234 CCCCCCCC 2010-07-09 KKKKK
456 DDDDDDDD 2010-09-05 PPPPP
456 DDDDDDDD 2010-06-06 RRRRR
/*
//SORTOUT DD DSN=SORT1.TEMPFILE,
// DCB=(RECFM=FB,LRECL=36,BLKSIZE=0),
// DISP=(,CATLG,DELETE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC FIELDS=(1,29,SEQNUM,7,ZD)
SORT FIELDS=COPY
/*
//*
//SORT2 EXEC PGM=SYNCSORT
//SORTIN DD *
123 1998-09-08
123 1997-05-06
234 1996-07-08
456 1995-09-02
456 1994-01-01
/*
//SORTOUT DD DSN=SORT2.TEMPFILE,
// DCB=(RECFM=FB,LRECL=36,BLKSIZE=0),
// DISP=(,CATLG,DELETE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC FIELDS=(1,29,SEQNUM,7,ZD)
SORT FIELDS=COPY
/*
//*
//STEP0030 EXEC PGM=SYNCSORT
//SORTJNF1 DD DSN=SORT1.TEMPFILE,
// DISP=SHR
//SORTJNF2 DD DSN=SORT2.TEMPFILE,
// DISP=SHR
//SORTOUT DD DSN=SORT3.OUTFILE,
// DCB=(RECFM=FB,LRECL=29,BLKSIZE=0),
// DISP=(,CATLG,DELETE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,
FIELDS=(001,003,A,
030,007,A) SEQUENCE NUMBER
JOINKEYS FILE=F2,
FIELDS=(001,003,A,
030,007,A) SEQUENCE NUMBER
REFORMAT FIELDS=(F1:01,13,F2:05,10,F1:24,6)
SORT FIELDS=COPY
/*
//*
//

Posted: **Wed Nov 16, 2011 2:25 am**

Hello,

It makes things easier for everyone if JCL, Control Statements, Code, Data, etc are posted using the "Code" tag. This will preserve alignment and improve readability.

I'd suggest pursuing the one-step solution. On my system, normal volumes are 10s and 100s of millions of records, so passing them multiple times is most unattractive. . . 8-)

I'm not able to test just now, but i believe posted JOINKEYS is rather close. Time permitting later tonight, i'll see if i can find more info.

IBM Mainframe Forum

Please help with below senario

Please help with below senario

Re: Please help with below senario

Re: Please help with below senario

Re: Please help with below senario

Re: Please help with below senario