IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

File compare, where file has header and detail


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Thu Nov 07, 2019 5:05 pm
Reply with quote

Can someone please help on the below sort requirement

I have an requirement to compare current file with last run's file and extract only adds and modifications.
Problem is i should compare only header lines (dont compare detail lines) but my file has headers and Details.

For the extracted Header , I need all the details linked to that header from the current file irrespective of changes/adds.



Below is the sample file.

In Header, PO number starts at 5th byte ( lenght of 16 bytes)
PO status starts at 22nd byte (length of 3 bytes )

In Detail, PO number starts at 15th byte ( lenght of 16 bytes)
PO status starts at 56nd byte (length of 3 bytes )


Code:
----+----1----+----2----+----3----+----4----+----5----+----
HDR|SS015000000180  |UPD|SST |    |0000004.000|0000000.000|
DTL|006769407|SS015000000180  |0000001|0000001|0000001|UPD|
DTL|006798646|SS015000000180  |0000001|0000001|0000001|UPD|
DTL|006769399|SS015000000180  |0000001|0000001|0000001|UPD|
DTL|006769406|SS015000000180  |0000001|0000001|0000001|UPD|
HDR|SS015000000181  |UPD|SST |    |0000000.000|0000002.000|
DTL|006710709|SS015000000181  |0000001|0000001|0000001|UPD|
DTL|006715969|SS015000000181  |0000001|0000001|0000001|UPD|



Below is the sort which will work for Headers but it will remove the details.

Code:
//SORTJOIN EXEC PGM=SORT,                                       
//             COND=(0,NE)                                       
//SORTJNF1 DD  DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS,         
//             DISP=SHR                                         
//SORTJNF2 DD  DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.BKUP(+0),
//             DISP=SHR                                         
//SORTOUT  DD  DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.NEW,     
//             DISP=(NEW,CATLG,DELETE),                         
//             SPACE=(00460,(100,10),RLSE),                     
//             AVGREC=K,                                         
//             RECFM=FB,                                         
//             LRECL=00460                                       
//SYSOUT   DD  SYSOUT=*                                         
//SYSIN    DD  *
  JOINKEYS F1=SORTJNF1,FIELDS=(1,650,A) 
  JOINKEYS F2=SORTJNF2,FIELDS=(1,650,A) 
  JOIN UNPAIRED,F1,ONLY                 
  REFORMAT FIELDS=(F1:1,650)             
  SORT FIELDS=COPY
/*


I Guess below solution will work but is there any optimised way to reduce number of steps

1) Split the Headers and Detail records to backup files. ( MOFDSS.HDR.BKUP(+1) and MOFDSS.DTL.BKUP(+1) )
2) Compare Current MOFDSS.HDR.BKUP(+1) and Previous MOFDSS.HDR.BKUP(0) Header files to extract only New and modified Headers. (MOFDSS.HDR.NEW)
3) Merge files MOFDSS.HDR.NEW and matching details from MOFDSS.DTL.BKUP(+1) based on PO number and PO status to create MOFDSS.FINAL.EXT
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Nov 07, 2019 5:41 pm
Reply with quote

Please use the code tags when presenting code and data. Fixed for you this time.

For the input you show, what is the expected output?
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Thu Nov 07, 2019 6:59 pm
Reply with quote

Sorry Nic, Since I am using this for the first time, I was not aware how to add the code tags. Now I have modified my entire query and added the required output file.


I have an requirement to compare current file with last run's file and extract only adds and modifications.
Problem is i should compare only headers but my file has headers and Details.

For the extracted Header , I need all the details linked to that header from the current file irrespective of changes/adds.



Below is the sample file.

In Header, PO number starts at 5th byte ( lenght of 16 bytes)
PO status starts at 22nd byte (length of 3 bytes )

In Detail, PO number starts at 15th byte ( lenght of 16 bytes)


Previous File:

WE have only two PO's SS048000003114 and SS048000003115
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114  |UPD|SST |    |2222222.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114  |0000002|0000000|0000002|UPD|             
DTL|005258615|SS048000003114  |0000001|0000000|0000001|UPD|             
DTL|005198615|SS048000003114  |0000002|0000000|0000002|UPD|             
HDR|SS048000003115  |UPD|SST |    |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|           


Current file:
We have two PO's from last run SS048000003114 and SS048000003115 and a new PO is added SS048000003116
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114  |UPD|SST |    |1111111.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114  |0000002|0000000|0000002|UPD|             
DTL|005258615|SS048000003114  |0000001|0000000|0000001|UPD|             
DTL|005198615|SS048000003114  |0000002|0000000|0000002|UPD|             
HDR|SS048000003115  |UPD|SST |    |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|   
HDR|SS048000003116  |UPD|SST |    |0000000.000|0000123.000|0000000.000|0
DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|   



OUTPUT file:
First PO# SS048000003114
In the previous file for the PO# SS048000003114 chill order quantity is 2222222.000
in the current file for the PO# SS048000003114 chill order quantity is 1111111.000 <-- write total set of header and detail into output file

Second PO# SS048000003115
There are no changes in the header in both the files, So skip the total set

Third PO# SS048000003116
This is newly added to the current file not exits in pervious file. <-- Write total set into output file

Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114  |UPD|SST |    |1111111.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114  |0000002|0000000|0000002|UPD|             
DTL|005258615|SS048000003114  |0000001|0000000|0000001|UPD|             
DTL|005198615|SS048000003114  |0000002|0000000|0000002|UPD|             
HDR|SS048000003116  |UPD|SST |    |0000000.000|0000123.000|0000000.000|0
DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|         




Below is the sort which will work for Headers but it will remove the details.

Code:
//SORTJOIN EXEC PGM=SORT,                                       
//             COND=(0,NE)                                       
//SORTJNF1 DD  DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS,         
//             DISP=SHR                                         
//SORTJNF2 DD  DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.BKUP(+0),
//             DISP=SHR                                         
//SORTOUT  DD  DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.NEW,     
//             DISP=(NEW,CATLG,DELETE),                         
//             SPACE=(00460,(100,10),RLSE),                     
//             AVGREC=K,                                         
//             RECFM=FB,                                         
//             LRECL=00460                                       
//SYSOUT   DD  SYSOUT=*                                         
//SYSIN    DD  *
  JOINKEYS F1=SORTJNF1,FIELDS=(1,650,A) 
  JOINKEYS F2=SORTJNF2,FIELDS=(1,650,A) 
  JOIN UNPAIRED,F1,ONLY                 
  REFORMAT FIELDS=(F1:1,650)             
  SORT FIELDS=COPY
/*



I Guess below solution will work but is there any optimised way to reduce number of steps

1) Split the Headers and Detail records to backup files. ( MOFDSS.HDR.BKUP(+1) and MOFDSS.DTL.BKUP(+1) )
2) Compare Current MOFDSS.HDR.BKUP(+1) and Previous MOFDSS.HDR.BKUP(0) Header files to extract only New and modified Headers. (MOFDSS.HDR.NEW)
3) Merge files MOFDSS.HDR.NEW and matching details from MOFDSS.DTL.BKUP(+1) based on PO number and PO status to create MOFDSS.FINAL.EXT[/code]
Code:
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Thu Nov 07, 2019 8:57 pm
Reply with quote

VINAY PALLELA wrote:

Your approach is completely wrong in general.
Not a minor understanding of JOIN operation.

This is a request to others, to do your job for you...
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Nov 07, 2019 9:22 pm
Reply with quote

Not the answer you are seeking but...
in the time I spent thinking about this (not long) I could have written a small rexx program to do what you want. It is, basically, a 2-file match.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Thu Nov 07, 2019 9:58 pm
Reply with quote

VINAY PALLELA wrote:
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114  |UPD|SST |    |2222222.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114  |0000002|0000000|0000002|UPD|             
DTL|005258615|SS048000003114  |0000001|0000000|0000001|UPD|             
DTL|005198615|SS048000003114  |0000002|0000000|0000002|UPD|             
HDR|SS048000003115  |UPD|SST |    |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|           

Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114  |UPD|SST |    |1111111.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114  |0000002|0000000|0000002|UPD|             
DTL|005258615|SS048000003114  |0000001|0000000|0000001|UPD|             
DTL|005198615|SS048000003114  |0000002|0000000|0000002|UPD|             
HDR|SS048000003115  |UPD|SST |    |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|   
HDR|SS048000003116  |UPD|SST |    |0000000.000|0000123.000|0000000.000|0
DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|   



You need to consider 4 categories:
1) Newly inserted groups
2) Deleted old groups
3) Matching groups with changed data
4) Matching groups with unchanged data (plan to ignore them)

You may be interested in the first 3 of them.

Stage 1: Create matching file
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
3 HDR|SS048000003114  |
4 HDR|SS048000003115  |
1 HDR|SS048000003116  |

(Type 2 is not present in your example)

Stage 2: Indicate Group IDs in both inputs:
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
SS048000003114  HDR|SS048000003114  |UPD|SST |    |2222222.000|0000000.000|0000000.000|0
SS048000003114  DTL|005200815|SS048000003114  |0000002|0000000|0000002|UPD|             
SS048000003114  DTL|005258615|SS048000003114  |0000001|0000000|0000001|UPD|             
SS048000003114  DTL|005198615|SS048000003114  |0000002|0000000|0000002|UPD|             
SS048000003115  HDR|SS048000003115  |UPD|SST |    |0000000.000|0000027.000|0000000.000|0
SS048000003115  DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
SS048000003115  DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
SS048000003115  DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|           

Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
SS048000003114  HDR|SS048000003114  |UPD|SST |    |1111111.000|0000000.000|0000000.000|0
SS048000003114  DTL|005200815|SS048000003114  |0000002|0000000|0000002|UPD|             
SS048000003114  DTL|005258615|SS048000003114  |0000001|0000000|0000001|UPD|             
SS048000003114  DTL|005198615|SS048000003114  |0000002|0000000|0000002|UPD|             
SS048000003115  HDR|SS048000003115  |UPD|SST |    |0000000.000|0000027.000|0000000.000|0
SS048000003115  DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
SS048000003115  DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
SS048000003115  DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|   
SS048000003116  HDR|SS048000003116  |UPD|SST |    |0000000.000|0000123.000|0000000.000|0
SS048000003116  DTL|009287835|SS048000003115  |0000001|0000001|0000001|UPD|             
SS048000003116  DTL|008717424|SS048000003115  |0000001|0000001|0000001|UPD|             
SS048000003116  DTL|009298821|SS048000003115  |0000001|0000001|0000001|UPD|   


Stage 3: Join the "Matching File" with each of "grouped" inputs, keeping in mind the '1'/'2'/'3'/'4' indicators set in the first of them.

Use either several JCL steps with SYNCSORT, or single JCL step with SYNCTOOL.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Nov 08, 2019 2:03 am
Reply with quote

Not a clean way but get you what you want.
Code:

//STEP01  EXEC PGM=SORT                                                 
//SORTLIST  DD SYSOUT=*                                                 
//SORTJNF1 DD *                                                         
HDR|SS048000003114  |UPD|SST |    |1111111.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
HDR|SS048000003115  |UPD|SST |    |2222222.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
DTL4                                                                   
//SORTJNF2 DD *                                                         
HDR|SS048000003114  |UPD|SST |    |1111112.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
HDR|SS048000003115  |UPD|SST |    |2222222.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
DTL4                                                                   
HDR|SS048000003116  |UPD|SST |    |3333333.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
DTL4                                                                   
//SORTOUT  DD  DISP=(NEW,PASS),                                         
//             UNIT=SYSDA,SPACE=(TRK,(10,10),RLSE),                     
//             DSN=&&MASTER                                             
//SYSIN    DD *                                                         
  SORT FIELDS=COPY                                                     
  INCLUDE COND=(1,3,CH,EQ,C'HDR')                                       
  JOINKEYS F1=SORTJNF1,FIELDS=(1,80,A)                                 
  JOINKEYS F2=SORTJNF2,FIELDS=(1,80,A)                                 
  JOIN UNPAIRED,F2,ONLY                                                 
  REFORMAT FIELDS=(F2:1,80,90,8)                                       
  OUTFIL BUILD=(1,88)                                                   
//*                                                                     
//JNF2CNTL  DD *                                                       
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(90:ID=8))     
//STEP02  EXEC PGM=SORT,REGION=500M                                     
//SORTLIST  DD SYSOUT=*                                                 
//SORTJNF1 DD DSN=&&MASTER,DISP=OLD                                     
//SORTJNF2 DD *                                                         
HDR|SS048000003114  |UPD|SST |    |1111112.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
HDR|SS048000003115  |UPD|SST |    |2222222.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
DTL4                                                                   
HDR|SS048000003116  |UPD|SST |    |3333333.000                         
DTL1                                                                   
DTL2                                                                   
DTL3                                                                   
DTL4                                                                   
//SORTOUT   DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  SORT FIELDS=COPY                                                     
  JOINKEYS F1=SORTJNF1,FIELDS=(81,8,A)                                 
  JOINKEYS F2=SORTJNF2,FIELDS=(91,8,A)                                 
  REFORMAT FIELDS=(F2:1,80)                                             
  OUTFIL BUILD=(1,80)                                                   
//*                                                                     
//JNF2CNTL  DD *                                                       
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(91:ID=8))     


Output
Code:
HDR|SS048000003114  |UPD|SST |    |1111112.000                                 
DTL1                                                                           
DTL2                                                                           
DTL3                                                                           
HDR|SS048000003116  |UPD|SST |    |3333333.000                                 
DTL1                                                                           
DTL2                                                                           
DTL3                                                                           
DTL4           
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Fri Nov 08, 2019 9:08 pm
Reply with quote

Thank you very much! Rohit Umarjikar

I tried your sort steps and its working as expected.

Thank you all for taking time to give the inputs.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Fri Nov 08, 2019 9:19 pm
Reply with quote

Only the COPY-AND-PASTE type of answers is working in this forum, despite of the name Expert Forum...

It's a pity, software development has changed to a sort of computer games - click the button Start, and get the result. Not even any attempt to think about the approach, or algorithm. I suspect most of TS did not even hear about words like "algorithm" icon_pray.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top