JCL SORT to compare and update a field in a file



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

JCL SORT to compare and update a field in a file

Postby RonaldCDcosta » Thu Feb 11, 2010 2:32 am

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.
RonaldCDcosta
 
Posts: 14
Joined: Thu Feb 11, 2010 1:58 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby dick scherrer » Thu Feb 11, 2010 2:34 am

Hello,

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

If yes, which sort product is used on your system?
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: JCL SORT to compare and update a field in a file

Postby RonaldCDcosta » Thu Feb 11, 2010 2:46 am

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)
.....
RonaldCDcosta
 
Posts: 14
Joined: Thu Feb 11, 2010 1:58 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Frank Yaeger » Thu Feb 11, 2010 3:10 am

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))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

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

Postby RonaldCDcosta » Thu Feb 11, 2010 5:01 am

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?
RonaldCDcosta
 
Posts: 14
Joined: Thu Feb 11, 2010 1:58 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Frank Yaeger » Thu Feb 11, 2010 5:37 am

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
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

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

Postby Frank Yaeger » Thu Feb 11, 2010 11:03 pm

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.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

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

Postby RonaldCDcosta » Thu Feb 11, 2010 11:12 pm

Can you please tell the solution using SYNCSORT?
RonaldCDcosta
 
Posts: 14
Joined: Thu Feb 11, 2010 1:58 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Frank Yaeger » Fri Feb 12, 2010 2:10 am

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.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times


Return to JCL

 


  • Related topics
    Replies
    Views
    Last post