Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Compare 2 files and give the output using DFSORT/ICETOOL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ

PostPosted: Thu Feb 26, 2009 6:17 pm    Post subject: Compare 2 files and give the output using DFSORT/ICETOOL
Reply with quote

Hi Experts,

My requirement is as follows.

FILE1
Code:
Key(1-7)NAME(8-24)      PNo(25-32)
----+----1----+----2----+----3----+----4----+----5----+----6
AAC3    JOHN            E2432007
AAD     SAMUAL          E2345678
AAD1    NELSON          L5523376
AAD3    MAHA            E2432007
ACB     RAM             L7648596
AA121   RAJI            L2436622
BB45    NELSON          L5523376

FILE2
Code:
Key(1-7)NAME1(8-24)     PNo25-32NAME2(33-48)    PNO2(49-56)
----+----1----+----2----+----3----+----4----+----5----+----6
AAC3    JOHN            E2432007MAHA            E2431599
A       PRASHANTH       E6478390
AAD     PRIYA           E2431084SAMUAL          E2431599
AAD3                            NELSON          L5523376
AA121
AA192   RAJI            E2431599

Expected OUTPUT
Code:
Key(1-7)NAME(8-24)      PNo(25-32)
----+----1----+----2----+----3----+----4----+----5----+----6
AAC3    JOHN            E2432007
AAD     PRIYA           E2431084
AAD1    PRIYA           E2431084
AAD3    NELSON          L5523376
ACB     PRASHANTH       E6478390
AA121
BB45    NELSON          L5523376

Specification of the File:
File1 --> RECFM = FB, LRECL = 80
File2 --> RECFM = FB, LRECL = 80
Output --> RECFM = FB, LRECL = 80

Rules to achieve:

1, If both the FILE key matches --> and IF NAME1 and PHONE NO1 matches -> FILE1 values will be written in output. (EG: AAC3)
2, If both the FILE key matches --> and IF NAME1 and PHONE NO1 NOT matches -> Update NAME and PHONE NO of FILE2 file. (EG: AAD)
3, If both the FILE key matches --> and IF NAME1 and PHONE NO1 is spaces, check for NAME2 and PHONE NO2. (EG: AAD3)
4, If both the FILE key matches --> and IF NAME1 and PHONE NO1 is spaces and NAME2 and PHONE NO2 is also spaces --> move spaces in NAME and PHONE NO in output file (EG: AA121)
5, If Key is present in File1 but not present in File2 --> Check for the upperbound*. (EG: AAD1, ACB)
6, If Key is present in File1 but not present in File2 --> and the upperbound is also not present in File2 --> File1 value should be written in output file. (EG: BB45)
7, IF Key is not present in File1 but present in File2 --> Just ignore it. (EG: AA192)

Upperbound*
Eg: 'AAD1' is present in File1, but not present in File2. So the upperbound will be elimination of the last char of the KEY word. So it is 'AAD'. Now check this 'AAD' in File2 and replace with that corresponding value of File2 in output file.

Another EG: 'ACD' is not present in File2. So elimination of the last char, will give you 'AC' and this should be checked in file2. This is also not found. So eliminate the last char. So it will be 'A'. This is found in the File2. so corresponding value if 'A' in File2 is written in the output file.

NOTE: WE DON'T HAVE (NEW z/OS DFSORT V1R5 PTF UK90013 (July, 2008)) IN OUR SHOP.

If you need more clarification please let me know
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Thu Feb 26, 2009 6:23 pm    Post subject:
Reply with quote

nelson.pandian wrote:
NOTE: WE DON'T HAVE (NEW z/OS DFSORT V1R5 PTF UK90013 (July, 2008)) IN OUR SHOP.
Why not? It's a simple update and free to boot......
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ

PostPosted: Thu Feb 26, 2009 11:48 pm    Post subject:
Reply with quote

Quote:
Why not? It's a simple update and free to boot......


Yes, its a simple update. But our system support person is not ready to do this now and don't know when they will update it icon_sad.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Feb 27, 2009 12:59 am    Post subject: Reply to: Compare 2 files and give the output using DFSORT/I
Reply with quote

Nelson,

The following DFSORT/ICETOOL JCL will give you the desired results. The tricky part is to validate Upperbound case. I assumed that your file2 has NO duplicates on the key. I also assumed that your Name2 is 17 bytes as name1 is 17 bytes. you showed only 16 bytes for name2. The comparision would fail when checking for name1,phone1 combo.

The job is typical file match with expception that you need to implement upperbound case. So what we do is split the file1 key into 7 records, so that we will find a match for the upperbound case

ex if you have a key as follows
Code:

KKKKKKK


we would create 7 records like this
Code:

0KKKKKKK
1KKKKKK 
2KKKKK   
3KKKK   
4KKK     
5KK     
6K       


Once we create it like this it is easy for us to match with the key in file2. once matched we use a couple of include and get rid of the unnecessary records

Code:

//STEP0100 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN1      DD *                                                   
----+----1----+----2----+----3----+----4----+----5----+----6----+--
AAC3   JOHN             E2432007                                   
AAD    SAMUAL           E2345678                                   
AAD1   NELSON           L5523376                                   
AAD3   MAHA             E2432007                                   
ACB    RAM              L7648596                                   
AA121  RAJI             L2436622                                   
BB45   NELSON           L5523376                                   
//IN2      DD *                                                   
----+----1----+----2----+----3----+----4----+----5----+----6----+--
AAC3   JOHN             E2432007MAHA             E2431599         
A      PRASHANTH        E6478390                                   
AAD    PRIYA            E2431084SAMUAL           E2431599         
AAD3                            NELSON           L5523376         
AA121                                                             
AA192  RAJI             E2431599                                   
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)     
//T2       DD DSN=&&T2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  COPY FROM(IN2) USING(CTL1)                                       
  COPY FROM(IN1) USING(CTL2)                                       
  SPLICE FROM(T1) TO(T2) ON(89,7,CH) WITH(1,88) -                   
  WITHALL KEEPBASE KEEPNODUPS USING(CTL3)                           
  SORT FROM(T2) USING(CTL4)                                         
//CTL1CNTL DD *                                                     
  OUTREC IFTHEN=(WHEN=(8,25,CH,EQ,C' '),OVERLAY=(8:33,25))         
  OUTFIL FNAMES=T1,BUILD=(89:1,80,C'M')                             
/*                                                                 
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T1,                                                 
  BUILD=(1,80,1,7,C'0',1,7,74X,/,                                   
         1,80,1,7,C'1',1,6,75X,/,                                   
         1,80,1,7,C'2',1,5,76X,/,                                   
         1,80,1,7,C'3',1,4,77X,/,                                   
         1,80,1,7,C'4',1,3,78X,/,                                   
         1,80,1,7,C'5',1,2,79X,/,                                   
         1,80,1,7,C'6',1,1,80X)                                     
/*                                                                 
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=T2,OMIT=(1,88,CH,EQ,C' '),                         
  IFTHEN=(WHEN=(8,25,CH,NE,96,25,CH),OVERLAY=(8:96,25))             
/*                                                                 
//CTL4CNTL DD *                                                     
  OMIT COND=((88,1,ZD,GT,0,AND,81,7,CH,EQ,89,7,CH),OR,             
             (88,1,ZD,GT,0,AND,8,73,CH,EQ,C' '))                   
                                                                   
  INREC IFTHEN=(WHEN=(169,1,CH,EQ,C' '),OVERLAY=(88:C'9'))         
  SORT FIELDS=(81,8,CH,A)                                           
  OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,80,SEQNUM,1,ZD,RESTART=(1,7))) 
  OUTFIL FNAMES=OUT,INCLUDE=(81,1,ZD,EQ,1),BUILD=(1,80)             
/*
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ

PostPosted: Fri Feb 27, 2009 3:52 pm    Post subject:
Reply with quote

Hi kolusu,

The output which i got from the above code is:
Code:
Key(1-7)NAME(8-24)      PNo(25-32)
----+----1----+----2----+----3----+----4----+----5----+----6
AAC3    JOHN            E2432007
AAD     PRIYA           E2431084
AAD1    PRIYA           E2431084
AAD3    NELSON          L5523376
AA121
ACB     PRASHANTH       E6478390
BB45

But my expected output is:
Code:
Key(1-7)NAME(8-24)      PNo(25-32)
----+----1----+----2----+----3----+----4----+----5----+----6
AAC3    JOHN            E2432007
AAD     PRIYA           E2431084
AAD1    PRIYA           E2431084
AAD3    NELSON          L5523376 
AA121
ACB     PRASHANTH       E6478390
BB45    NELSON          L5523376

Rule 6: If Key is present in File1 but not present in File2 --> and the upperbound is also not present in File2 --> File1 value should be written in output file. (EG: BB45)

Please help me in solving this issue.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Feb 27, 2009 9:31 pm    Post subject: Reply to: Compare 2 files and give the output using DFSORT/I
Reply with quote

nelson.pandian,

Change your CTL3CNTL to the following

Code:

//CTL3CNTL DD *
  OUTFIL FNAMES=T2,OMIT=(1,88,CH,EQ,C' '),
  IFTHEN=(WHEN=(8,25,CH,NE,96,25,CH,AND,169,1,CH,EQ,C'M'),
  OVERLAY=(8:96,25))                                     
/*
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ

PostPosted: Mon Mar 02, 2009 11:41 pm    Post subject:
Reply with quote

Thanks Kolusu. Great work. Your solution helped me a lot icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts Incorrect output when trying to add n... monica1 PL/I & Assembler 10 Fri Jan 13, 2017 5:02 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us