Page 1 of 1

JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 2:32 am
by RonaldCDcosta
Hi All,
I have 2 sequential files.
File1 = length 80, File2 = lenght 133

File 1:
..... 111111........AAAAA...
......222222........BBBBB...

File2:
.. 333333........CCCCC...
.. 222222........DDDDD...

I want to compare the position 20:6 of file1 with column 25:6 of file2. If match is found then update column(50:5) in FIle1 with column(45:5) from File 2.
i.e after comparing the above 2 files, the File1 must be updated as below:
File 1:
..... 111111........AAAAA... <------NO CHANGE AS NO MATCH IN FILE 2
......222222........DDDDD... <------ BBBBB UPDATED TO DDDDD AFTER COMPARING WITH FILE2.

Re: JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 2:34 am
by dick scherrer
Hello,

Do you want to use the sort product on your system for this?

If yes, which sort product is used on your system?

Re: JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 2:46 am
by RonaldCDcosta
Yes I want to use SORT product something like:
//STEP010 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
.
.
//SORTLIB DD DSN=...
// DISP=SHR
//SORTJNF1 DD DSN=.. DISP=SHR
//SORTJNF2 DD DSN=.... DISP=SHR
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(20,6,A)
JOINKEYS FILES=F2,FIELDS=(25,6,A)
.....

Re: JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 3:10 am
by Frank Yaeger
Here's a DFSORT JOINKEYS job that will do what you asked for:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTJNF1 DD DSN=... input file1 (FB/80)
//SORTJNF2 DD DSN=... input file2 (FB/133)
//SORTOUT DD DSN=...  output file (FB/80)
//SYSIN    DD    *
  JOINKEYS FILES=F1,FIELDS=(20,6,A)
  JOINKEYS FILES=F2,FIELDS=(25,6,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,80,?,F2:45,5)
  OPTION COPY
  OUTFIL IFOUTLEN=80,
    IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),OVERLAY=(50:82,5))
/*

Re: JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 5:01 am
by RonaldCDcosta
Some doubt:
1) what is '?' in REFORMAT FIELDS=(F1:1,80,?,F2:45,5)
2) why are we comparing with column 81 and using 82 in overlay. Our File1 is only 80 length.
IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),OVERLAY=(50:82,5))
3) Actually the value to be replaced is in column(50:5) in FIle1 with column(45:5) from File 2. The value in column(50:5) can be any name. Not necessary to start with B. So, how do we handle this?

Re: JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 5:37 am
by Frank Yaeger
1) what is '?' in REFORMAT FIELDS=(F1:1,80,?,F2:45,5)


The ? indicates that DFSORT is to put a flag at that position (81 in this case) indicating where the record came from. In this case, the flag will be 'B' when the record was found in both files (F1/F2) or '1' when the record was only found in file1 (F1).

2) why are we comparing with column 81 and using 82 in overlay. Our File1 is only 80 length.
IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),OVERLAY=(50:82,5))


The REFORMAT creates records like this:

|1-80 from F1|flag|45-49 from F2|
Len        80    1             5


For F1 only records, the REFORMAT records look like this:

|1-80 from F1|1|     |
Len        80 1     5


For F1/F2 records, the REFORMAT records look like this:

|1-80 from F1|B|45-49 from F2|
Len        80 1             5


Here's what the IFTHEN does:

If the flag in 81 is '1' (F1 only), no change is needed. If the flag in 81 is 'B' (F1/F2), we overlay positions 50-54 with 82-86 (45-49 from F2).

The IFOUTLEN=80 sets the final output record length to 80.

3) Actually the value to be replaced is in column(50:5) in FIle1 with column(45:5) from File 2. The value in column(50:5) can be any name. Not necessary to start with B. So, how do we handle this?


The 'B' is a flag inserted by DFSORT, not a name starting with 'B'. 50:5 is replaced by 45:5 in the F1/F2 records.

This DFSORT job gives the output you requested.

For more information on DFSORT's JOINKEYS function, see:

http://www.ibm.com/support/docview.wss? ... g3T7000174

Re: JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 11:03 pm
by Frank Yaeger
I noticed that you posted in the Syncsort Forum that this didn't work for you (with Syncsort). FYI, it does work with DFSORT, but Syncsort doesn't support the ? indicator.

Re: JCL SORT to compare and update a field in a file

PostPosted: Thu Feb 11, 2010 11:12 pm
by RonaldCDcosta
Can you please tell the solution using SYNCSORT?

Re: JCL SORT to compare and update a field in a file

PostPosted: Fri Feb 12, 2010 2:10 am
by Frank Yaeger
I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.