Joinkeys issue in Syncsort



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

Joinkeys issue in Syncsort

Postby shirazi2589 » Tue May 22, 2012 8:20 am

We have 2 input files.
First File Contains employee id(length = 08) from position 10-17, and status field having 2 values(either 'DR' or 'DT' along with other fields.
2nd file Contains employee id(length = 08) from position 1-8, driver-status-field having only 2 values either 'Active' or 'Inactive') starting at position 1055 to 1063,along with various other fields.

We have to include all those records in the output file where.
If status is 'DR' and the driver-status = 'Active' ------include the record from file 1 to an output file
If status is 'DR' and the driver-status = 'inactive' ------omit the record.
If status is 'DT' and the driver-status = 'Active' ------omit the record.
If status is 'DT'' and the driver-status = 'inactive' ------include the record from file 1 to an output file

We used below Sort Card for the same, but didnt work out. Could you all please help ?

//STEP001  EXEC  PGM=SYNCSORT                               
//SORTJNF1 DD  DSN=G1BYAUT.FINAL.PUCVUC.FILE,DISP=SHR       
//SORTJNF2 DD  DSN=G1BYAUT.DRIVER.INPUT.FILE,DISP=SHR       
//SORTOUT  DD  DSN=G1BYAUT.FINAL.SORTED2,                   
//             DISP=(OLD,CATLG,DELETE),                     
//             UNIT=SYSDA,SPACE=(CYL,(2,1),RLSE),           
//             DCB=(BLKSIZE=0,LRECL=1192,RECFM=FB,DSORG=PS) 
//MAINSORT DD  DSN=G1BYAUT.FINAL.MAINSORT,                   
//             DISP=(OLD,CATLG,DELETE),                     
//             UNIT=SYSDA,SPACE=(CYL,(2,1),RLSE),           
//             DCB=(BLKSIZE=0,LRECL=92,RECFM=FB,DSORG=PS)   
//SYSIN    DD *                                                   
   JOINKEYS FILE=F1,FIELDS=(10,8,A)                                 
   JOINKEYS FILE=F2,FIELDS=(1,8,A)                                 
   REFORMAT FIELDS=(F1:1,92,F2:1,1100)                             
   OPTION COPY                                                     
   OUTFIL FNAMES=MAINSORT,INCLUDE=(3,2,CH,EQ,C'DR',AND,1147,5,CH,EQ,C'ACTIVE'),OUTREC=(1,92)   
 /*       


For testing purpose included only those records having status as 'DR' and the driver-status = 'Active'
shirazi2589
 
Posts: 15
Joined: Mon Nov 14, 2011 9:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joinkeys issue in Syncsort

Postby dick scherrer » Tue May 22, 2012 8:49 am

Hello,

but didnt work out.
Do NOT post "it didn't work". This is a complete waste of time.

When something doesn't work and you want help, you have to post the details of the problem. Did it abend, get an unwanted return code, generate unwanted output?

You need to post the informational/diagnostic messages generated by the problem run.

Also, when posting code, jcl, sort control, data, etc you should use the Code tag. This will improve readability and preserve alignment.

Could you all please help
Probably - when we understand the problem.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Joinkeys issue in Syncsort

Postby shirazi2589 » Tue May 22, 2012 10:01 am

So Sorry,
There are many records in the file which satisfies the above condition. i.e status having value 'DR' driver-status-field having value 'ACTIVE'.
So if the above conditions were to be satisfied it should have been written to the output file 'MAINSORT' but the file produced was empty (hence i wrote 'it didnt work')
Example of record in file 1:
DR 0000016455620120518AUTDED 7500

Corresponding record in file 2.
00164556MICHAEL ...//......(many other fields in between) ACTIVE

Have i missed any statements in SYSIN of sort card?
shirazi2589
 
Posts: 15
Joined: Mon Nov 14, 2011 9:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joinkeys issue in Syncsort

Postby BillyBoyo » Tue May 22, 2012 11:53 am

By my count, your file 1 key starts at 11, not 10.

Please use the Code tags to preserve spacing for data as well as for the JCL.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Joinkeys issue in Syncsort

Postby shirazi2589 » Tue May 22, 2012 7:35 pm

Hey Everybody,
//STEP001  EXEC  PGM=SYNCSORT                                       
//*SORTJNF1 DD  DSN=G1BYAUT.CONFIRMN.HPSTATUS.FILE,DISP=SHR         
//SORTJNF1 DD  DSN=G1BYAUT.FINAL.PUCVUC.FILE,DISP=SHR               
//SORTJNF2 DD  DSN=G1BYAUT.DRIVER.INPUT.FILE,DISP=SHR               
//SORTOUT  DD  DSN=G1BYAUT.FINAL.SORTED2,DISP=SHR                   
//*            DISP=(NEW,CATLG,DELETE),                             
//*            UNIT=SYSDA,SPACE=(CYL,(2,1),RLSE),                   
//*            DCB=(BLKSIZE=0,LRECL=92,RECFM=FB,DSORG=PS)           
//MAINSORT DD  DSN=G1BYAUT.FINAL.MAINSORT,DISP=SHR                   
//*            DISP=(NEW,CATLG,DELETE),                             
//*            UNIT=SYSDA,SPACE=(CYL,(2,1),RLSE),                   
//*            DCB=(BLKSIZE=0,LRECL=100,RECFM=FB,DSORG=PS)           
//SYSUDUMP DD  SYSOUT=*                                             
//SYSOUT   DD  SYSOUT=*                                             
//SYSUBOUT DD  SYSOUT=*                                             
//SYSIN    DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(10,8,A),SORTED                           
  JOINKEYS FILE=F2,FIELDS=(1,8,A),SORTED                             
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,92,F2:1,1100)                               
  OPTION COPY                                                       
  OUTFIL FNAMES=MAINSORT,                                           
  INCLUDE=(1148,5,CH,EQ,C'ACTIVE',AND,4,2,CH,EQ,C'DR'),             
  OUTREC=(1,92)                                                     
/*                                                                   
//SORTWK01 DD SPACE=(TRK,(10,5)),UNIT=SYSALLDA                       
//SORTWK02 DD SPACE=(TRK,(10,5)),UNIT=SYSALLDA                       


I wrote the above sort card so as to include all those records having 'STATUS = DR' and driver-order-status='ACTIVE'
Similarly i can write to include all those records having 'STATUS = DT' and driver-order-status='INACTIVE'

Besides these i also want to include all those records in the output file where the employee id's in both the file do not match.
i.e My output file should contain those records which satisfies the above condition and those records whose employee id are not present in file 2.

I tried writing JOIN UNPAIRED,F1
so as to include those records whose employee id are not present in file 2.
but all i got in the output file was the records which satisfied above condition and not records whose employee id are not present in file 2.

Can u suggest changes in my Sort Card?
shirazi2589
 
Posts: 15
Joined: Mon Nov 14, 2011 9:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joinkeys issue in Syncsort

Postby ibmmf4u » Wed May 23, 2012 8:29 am

Hi shirazi2589,

I am just a beginner. I can see based on your requirement you need to specify one more condition in include statement and sortcard along with one more file which conatins the unmatch records.

I just tried an example as per your requirement. Its working fine.Modify the positions as per your convenience.

Pasted below is the code i.e. working fine.

//STEP030  EXEC PGM=SORT     
//SYSPRINT DD   SYSOUT=*     
//SYSOUT   DD   SYSOUT=*     
//SORTJNF1 DD   *           
TEST1   100   ACTIVE         
TEST2   200   INACTIVE       
TEST3   300   NULL           
TEST4   400   INACTIVE       
TEST5   500   ACTIVE         
TEST6   600   ACTIVE         
//SORTJNF2 DD   *           
TEST1   DR                   
TEST8   DT                   
TEST2   DT                   
TEST3   DT                   
TEST7   DT                   
TEST8   DT                   
//MATCH    DD     DSN=MATCH.FILE,DISP=SHR
//UNMATCH  DD  DSN=UNMATCH.FILE,DISP=SHR
//SYSIN    DD  *                                               
  JOINKEYS FILE=F1,FIELDS=(1,5,A)                             
  JOINKEYS FILE=F2,FIELDS=(1,5,A)                             
  JOIN UNPAIRED,F1                                             
  REFORMAT FIELDS=(F1:1,25,F2:1,15)                           
  SORT FIELDS=COPY                                             
  OUTFIL FNAMES=MATCH,                                         
     INCLUDE=((34,2,CH,EQ,C'DR',AND,15,5,CH,EQ,C'ACTIVE'),OR, 
               (34,2,CH,EQ,C'DT',AND,15,7,CH,EQ,C'INACTIVE')),
      OUTREC=(1,25,34,2,53X)                                   
  OUTFIL FNAMES=UNMATCH,SAVE,                                 
      OUTREC=(1,25,55X)                                       
/*                                                             
                                       


Where the output will be displayed as follows:-

Matchfile:-
Matchfile:-
--------------
TEST1   100   ACTIVE     DR
TEST2   200   INACTIVE   DT


Unmatch file:-
Unmatch file:-
------------------
TEST3   300   NULL   
TEST4   400   INACTIVE
TEST5   500   ACTIVE 
TEST6   600   ACTIVE 


Thanks,
ibmmf4u
 
Posts: 65
Joined: Wed Dec 14, 2011 10:26 pm
Has thanked: 0 time
Been thanked: 1 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post