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

Help on complex file compare


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Niki

Active User


Joined: 20 Sep 2008
Posts: 106
Location: Bangalore

PostPosted: Tue Jun 05, 2012 7:31 pm
Reply with quote

Hi All,

Can any one please help me in doing the complex file comparision.

Input File -1
---------
LRECL = 80, Fixed Block

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----
1000-Details
  Program name : XXXXXX
  Issue with the file.
  DATE : 01/01/2012
           Abended at para01
  Issue with the file.
XXXX-Details
          Abended at para01
          Job Details are
YYYY-Details.
  Program name : XXXXXX
ZZZZ-Details.
  Program name : XXXXXX


Input File -2
---------
LRECL = 80, Fixed Block

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----
1000-Details
  Program name : XXXXXX
  Issue with the file.
  DATE : 01/01/2012
           Abended at para01
  Issue with the file.
XXXX-Details
          Abended at para01
          SQL error code is: 100
          Job Details are
YYYY-Details.
  Program name : XXXXXX
AAAA-Details.
  The value is 005
  The key is : WS-KEY


Input file Key and details:

1) The records which starts with the first column is the header.
2) The detail records can start either at 3rd column or 11th column.


The output File
----------------

LRECL = 80, Fixed Block

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----
XXXX-Details
          SQL error code is: 100
AAAA-Details.
  The value is 005
  The key is : WS-KEY


Compare Condition:

1) It need to compare the file -1 header and details records with file-2. If the header and detail are same in both the files, no need to do anything in the output file.
2) If in file - 2, for a header, the detail has some additional records, then the header as well as the details needs to be populated.
3) If File - 1, has some extra records which file-2 dont. No need to do any anything.
4) If file -2 has some additional header and the corresponding details, the same needs to be populated.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 05, 2012 10:36 pm
Reply with quote

Niki,

Use the following DFSORT JCL which will give you the desired results
Code:

//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//INA      DD *                               
1000-DETAILS                                 
  PROGRAM NAME : XXXXXX                       
  ISSUE WITH THE FILE.                       
  DATE : 01/01/2012                           
           ABENDED AT PARA01                 
  ISSUE WITH THE FILE.                       
XXXX-DETAILS                                 
          ABENDED AT PARA01                   
          JOB DETAILS ARE                     
YYYY-DETAILS.                                 
  PROGRAM NAME : XXXXXX                       
ZZZZ-DETAILS.                                 
  PROGRAM NAME : XXXXXX                       
//INB      DD *                               
1000-DETAILS                                 
  PROGRAM NAME : XXXXXX                       
  ISSUE WITH THE FILE.                       
  DATE : 01/01/2012                           
           ABENDED AT PARA01                 
  ISSUE WITH THE FILE.                       
XXXX-DETAILS                                 
          ABENDED AT PARA01                   
          SQL ERROR CODE IS: 100             
          JOB DETAILS ARE                     
YYYY-DETAILS.                                 
  PROGRAM NAME : XXXXXX                       
AAAA-DETAILS.                                 
  THE VALUE IS 005                           
  THE KEY IS : WS-KEY                         
//SORTOUT  DD SYSOUT=*                       
//SYSIN    DD *                                   
  OPTION COPY                                     
  JOINKEYS F1=INA,FIELDS=(97,80,A,1,80,A)         
  JOINKEYS F2=INB,FIELDS=(97,80,A,1,80,A)         
  JOIN UNPAIRED,F2,ONLY                           
  REFORMAT FIELDS=(F2:1,176)                     
                                                 
  OUTFIL OMIT=(89,8,ZD,EQ,1),BUILD=(1,80),       
  REMOVECC,SECTIONS=(97,80,HEADER3=(97,80))       
//*                                               
//JNF1CNTL DD *                                   
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,GT,C' '),
        PUSH=(81:ID=8,SEQ=8,1,80))               
//*                                               
//JNF2CNTL DD *                                   
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,GT,C' '),
        PUSH=(81:ID=8,SEQ=8,1,80))               
//*


The output from the above job is
Code:

AAAA-DETAILS.                     
  THE KEY IS : WS-KEY             
  THE VALUE IS 005                 
XXXX-DETAILS                       
          SQL ERROR CODE IS: 100   
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Jun 05, 2012 10:54 pm
Reply with quote

Hi Kolusu,

Just for my own learning is it possible to achieve this solution without

Code:
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,GT,C' '),
        PUSH=(81:ID=8,SEQ=8,1,80))


I was breaking my head for past few hours to achieve this without PUSH but ultimately I was trying to group those sections together which I couldnt icon_redface.gif

Please let me know if thats possible
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue Jun 05, 2012 11:02 pm
Reply with quote

Why should Kolusu spend his valuable time coming up with a less optimal solution just for YOUR own learning?

Why would you want another solution?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Jun 05, 2012 11:10 pm
Reply with quote

No no I just wanted to know if its possible icon_smile.gif
and when he prefers to answer icon_smile.gif

Hope I have stated better

Another solution because I am curious to correct the error of similar push logic without push
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue Jun 05, 2012 11:13 pm
Reply with quote

Yes it is possible.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Jun 05, 2012 11:27 pm
Reply with quote

Thanks Dave
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 05, 2012 11:31 pm
Reply with quote

Pandora-Box wrote:
No no I just wanted to know if its possible icon_smile.gif
and when he prefers to answer icon_smile.gif

Hope I have stated better

Another solution because I am curious to correct the error of similar push logic without push


Well it is possible. You need to SPLICE the header record on to detailed records and there is a math trick involved to do it icon_lol.gif There is your hint for that. Now you can try.

WHEN=GROUP was precisely invented by Frank to overcome such tricks and make the programmer's life much easier.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Jun 05, 2012 11:35 pm
Reply with quote

Skolusu wrote:
Pandora-Box wrote:
No no I just wanted to know if its possible icon_smile.gif
and when he prefers to answer icon_smile.gif

Hope I have stated better

Another solution because I am curious to correct the error of similar push logic without push


Well it is possible. You need to SPLICE the header record on to detailed records and there is a math trick involved to do it icon_lol.gif There is your hint for that. Now you can try.

WHEN=GROUP was precisely invented by Frank to overcome such tricks and make the programmer's life much easier.


Thanks for the hint Kolusu icon_smile.gif

I am able visualise it now icon_smile.gif
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 106
Location: Bangalore

PostPosted: Wed Jun 06, 2012 6:20 pm
Reply with quote

Thank you Kolusu for the wonderful solution.

With the above control statements, the detail and the header records are getting resuffled. They are not coming in the order in which they are present in th e input file.
For e.g: -
With the above input details, the output file should come as:

Code:

XXXX-DETAILS                   
          SQL ERROR CODE IS: 100
AAAA-DETAILS.                                 
  THE VALUE IS 005                           
  THE KEY IS : WS-KEY           


But the output is coming like below:

Code:

AAAA-DETAILS.                   
  THE KEY IS : WS-KEY           
  THE VALUE IS 005             
XXXX-DETAILS                   
          SQL ERROR CODE IS: 100



Secondly, if the second file has more than one reocord of the same content as of file -1. the second records are getting supressed.
For e.g:

Input File -1

Code:

XXXX-DETAILS               
          ABENDED AT PARA01
          JOB DETAILS ARE 


Input File -2

Code:

XXXX-DETAILS                   
          ABENDED AT PARA02     
          ABENDED AT PARA01     
          SQL ERROR CODE IS: 100
          ABENDED AT PARA01     
          JOB DETAILS ARE       


The output file should look like below:

Code:

XXXX-DETAILS                   
          ABENDED AT PARA02     
          SQL ERROR CODE IS: 100
          ABENDED AT PARA01     


But with the card the output is coming like below :

Code:

XXXX-DETAILS                     
          ABENDED AT PARA02     
          SQL ERROR CODE IS: 100


Can you please help me for these scenarios.[/code]
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Jun 06, 2012 8:08 pm
Reply with quote

Slightly modified Kolusu's sort card Hope Kolusu wont slap me with a better card ( On a lighter note icon_biggrin.gif )

Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//INA      DD *
1000-DETAILS
  PROGRAM NAME : XXXXXX
  ISSUE WITH THE FILE.
  DATE : 01/01/2012
           ABENDED AT PARA01
  ISSUE WITH THE FILE.
XXXX-DETAILS
          ABENDED AT PARA01
          JOB DETAILS ARE
YYYY-DETAILS.
  PROGRAM NAME : XXXXXX
ZZZZ-DETAILS.
  PROGRAM NAME : XXXXXX
//INB      DD *
1000-DETAILS
  PROGRAM NAME : XXXXXX
  ISSUE WITH THE FILE.
  DATE : 01/01/2012
           ABENDED AT PARA01
  ISSUE WITH THE FILE.
XXXX-DETAILS
          ABENDED AT PARA01
          ABENDED AT PARA02
          SQL ERROR CODE IS: 100
          JOB DETAILS ARE
          ABENDED AT PARA03
          ABENDED AT PARA04
YYYY-DETAILS.
  PROGRAM NAME : XXXXXX
  PROGRAM NAME : XXXXXY
AAAA-DETAILS.
  THE VALUE IS 005
  THE KEY IS : WS-KEY
  THE KEY IS : WS-KEY2
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  JOINKEYS F1=INA,FIELDS=(97,80,A,1,80,A)
  JOINKEYS F2=INB,FIELDS=(97,80,A,1,80,A)
  JOIN UNPAIRED,F2,ONLY
  REFORMAT FIELDS=(F2:1,176)
  SORT FIELDS=(81,8,ZD,A,89,8,ZD,A)
  OUTFIL OMIT=(89,8,ZD,EQ,1),BUILD=(1,80),
  REMOVECC,SECTIONS=(97,80,HEADER3=(97,80))
//*
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,GT,C' '),
        PUSH=(81:ID=8,SEQ=8,1,80))
//*
//JNF2CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,GT,C' '),
        PUSH=(81:ID=8,SEQ=8,1,80))
//*


Output

Code:
XXXX-DETAILS
          ABENDED AT PARA02
          SQL ERROR CODE IS: 100
          ABENDED AT PARA03
          ABENDED AT PARA04
YYYY-DETAILS.
  PROGRAM NAME : XXXXXY
AAAA-DETAILS.
  THE VALUE IS 005
  THE KEY IS : WS-KEY
  THE KEY IS : WS-KEY2


Hope this helps icon_smile.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 06, 2012 10:13 pm
Reply with quote

Pandora-Box wrote:
Slightly modified Kolusu's sort card Hope Kolusu wont slap me with a better card ( On a lighter note icon_biggrin.gif )
Hope this helps icon_smile.gif


Well you just added a SORT statement to retain the order of the records but you did NOT take care of Duplicate records(Niki's scenario #2) which are missing from the output.

Niki,

If you have duplicate records then you need to presort the files and tag the duplicates and then perform the compare. So here is DFSORT/ICETOOL JCL which will give you the desired results.

Code:

//STEP0100 EXEC PGM=ICETOOL   
//TOOLMSG  DD SYSOUT=*       
//DFSMSG   DD SYSOUT=*       
//INA      DD DSN=Your file2 which you need to pull records from,DISP=SHR
//INB      DD DSN=Your file1 which you need to compare,DISP=SHR
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//T2       DD DSN=&&T2,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                           
  SORT FROM(INA) TO(T1) USING(CTL1)                       
  SORT FROM(INB) TO(T2) USING(CTL1)                       
  COPY JKFROM TO(OUT) USING(CTL2)                         
//*                                                       
//CTL1CNTL DD *                                           
  INREC IFTHEN=(WHEN=INIT,BUILD=(81:1,80)),               
  IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,GT,C' '),             
        PUSH=(81,80,169:ID=8,SEQ=8))                       
  SORT FIELDS=(1,160,CH,A),EQUALS                         

  OUTREC OVERLAY=(161:SEQNUM,8,ZD,RESTART=(81,88))         
//*                                                       
//CTL2CNTL DD *                                           
  JOINKEYS F1=T1,FIELDS=(1,168,A),SORTED,NOSEQCK           
  JOINKEYS F2=T2,FIELDS=(1,168,A),SORTED,NOSEQCK           
  JOIN UNPAIRED,F1,ONLY                                   
  REFORMAT FIELDS=(F1:1,160,169,16)                       
  SORT FIELDS=(161,16,CH,A)                               

  OUTFIL OMIT=(169,8,ZD,EQ,1),BUILD=(81,80),               
  REMOVECC,SECTIONS=(1,80,HEADER3=(1,80))                 
//*



The output from the above job is
Code:

XXXX-DETAILS                             
          ABENDED AT PARA02               
          SQL ERROR CODE IS: 100         
          ABENDED AT PARA01               
AAAA-DETAILS.                             
  THE VALUE IS 005                       
  THE KEY IS : WS-KEY                     
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Jun 06, 2012 10:16 pm
Reply with quote

Skolusu wrote:
Well you just added a SORT statement to retain the order of the records but you did NOT take care of Duplicate records(Niki's scenario #2) which are missing from the output.


Thanks yet again I missed it terribley
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Jun 06, 2012 11:45 pm
Reply with quote

Hello,

Quote:
Thanks yet again I missed it terribley
terribly icon_cool.gif

Suggest trading more thorough testing for the desire to quickly reply icon_wink.gif
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Jun 07, 2012 12:01 am
Reply with quote

Sure Dick
Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 106
Location: Bangalore

PostPosted: Mon Jun 11, 2012 4:53 pm
Reply with quote

Thanks kolusu and Pandora for all the great solutions.

Kolusu, your control card worked perfectly as per the issue.

It really feels great to be a part of forum where such talents are present.
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 7
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