Please help with below senario



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Please help with below senario

Postby Ashok19 » 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
Ashok19
 
Posts: 2
Joined: Mon Jun 13, 2011 1:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Please help with below senario

 

Re: Please help with below senario

Postby NicC » 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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Please help with below senario

Postby Ashok19 » Thu Jun 16, 2011 3:35 pm

Hi Nic,

Thanks for your suggestion.

Below is the JCL i have used.
//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,

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.
Ashok19
 
Posts: 2
Joined: Mon Jun 13, 2011 1:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Please help with below senario

Postby ectgunner64 » 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.

//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                                   
/*                                                                 
//*                                                                 
//
ectgunner64
 
Posts: 8
Joined: Wed Sep 07, 2011 8:30 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Please help with below senario

Postby dick scherrer » 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to Syncsort/Synctool