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

Extract records after comparing fields present in 2 diff fil
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Tue Nov 06, 2007 2:20 pm
 Reply with quote
Hi I would like to know whether this scenario can be handled by JCL?

Consider two files as i/p:

File 1:
AB07052AAAAAAAAAAAA
AB09188BBBBBBBBBBBB
AB08228SSSSSSSSSSSS
AB11328GGGGGGGGGGG

File 2:

C.P.DTPURGE.ARCHIVAL.D07031
C.P.DTPURGE.ARCHIVAL.D07059
C.P.DTPURGE.ARCHIVAL.D07090
C.P.DTPURGE.ARCHIVAL.D07120
C.P.DTPURGE.ARCHIVAL.D07151
C.P.DTPURGE.ARCHIVAL.D07181
C.P.DTPURGE.ARCHIVAL.D07212
C.P.DTPURGE.ARCHIVAL.D07243
C.P.DTPURGE.ARCHIVAL.D07273
C.P.DTPURGE.ARCHIVAL.D07304
C.P.DTPURGE.ARCHIVAL.D07334
C.P.DTPURGE.ARCHIVAL.D07365
C.P.DTPURGE.ARCHIVAL.D08031
C.P.DTPURGE.ARCHIVAL.D08060
C.P.DTPURGE.ARCHIVAL.D08091
C.P.DTPURGE.ARCHIVAL.D08121
C.P.DTPURGE.ARCHIVAL.D08152
C.P.DTPURGE.ARCHIVAL.D08182
C.P.DTPURGE.ARCHIVAL.D08213

Now I want to populate a o/p file with the record present in file 2 where the data present in col(3,5) i.e 07052 for 1st file is less than the value present in col(23,5) of 2nd file i.e i want A.B.KNOWLGE.ARCHIVAL.D07059 to be populated in the o/p file for 1st record of 1st i/p file. The record length of both the file is 80. The length of each record is the same but the number of records present in the two i/p files may vary. Can this be achieved through ICETOOL/DFSort
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
PostPosted: Tue Nov 06, 2007 2:25 pm
 Reply with quote
Learn,

Quote:
Now I want to populate a o/p file with the record present in file 2 where the data present in col(3,5) i.e 07052 for 1st file is less than the value present in col(23,5) of 2nd file i.e i want A.B.KNOWLGE.ARCHIVAL.D07059 to be populated in the o/p file for 1st record of 1st i/p file. The record length of both the file is 80. The length of each record is the same but the number of records present in the two i/p files may vary. Can this be achieved through ICETOOL/DFSort

Im confused. You need to be clear with your post.

Show expected O/P for above I/P with explanation.
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
PostPosted: Tue Nov 06, 2007 2:27 pm
 Reply with quote
Learncoholic,

Quote:
1st file is less than the value present in col(23,5) of 2nd file i.e i want A.B.KNOWLGE.ARCHIVAL.D07059 to be populated in the o/p file for 1st record of 1st i/p file


How do you want to compare? is it a 1-1 or you want to compare the first record's date with all the records in the second file? Please clarify.
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Tue Nov 06, 2007 2:54 pm
 Reply with quote
Let me make it clearer with a sample o/p file. The o/p file in this case should be:

C.P.DTPURGE.ARCHIVAL.D07031
C.P.DTPURGE.ARCHIVAL.D09212
C.P.DTPURGE.ARCHIVAL.D08244
C.P.DTPURGE.ARCHIVAL.D11365

I would also like to mention the point though
C.P.DTPURGE.ARCHIVAL.D09212
C.P.DTPURGE.ARCHIVAL.D08244
C.P.DTPURGE.ARCHIVAL.D11365
are not show to be present in o/p file consider it to be present. I didn't show it due to space constraint.
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Tue Nov 06, 2007 3:06 pm
 Reply with quote
Now let me take an example. I take 2nd record of 1st i/p file namely "AB09188BBBBBBBBBBBB". Now I extract the required field col(3,5) namely "09188". now I start comparing this value with the value present in 2nd i/p file in position col(23,5) name values " '07031', '07059', '07090' etc". Whenever I come across a value in 2nd file which is greater than "09188", I write that particular record from 2nd file into an o/p file. I do this for all the records present in ist i/p file. The total number of records present in 1st i/p file is may vary.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India
PostPosted: Tue Nov 06, 2007 4:25 pm
 Reply with quote
Quote:
now I start comparing this value with the value present in 2nd i/p file in position col(23,5)

Do you have any condition for comparison, mean to say are you comparing using any key from both the Files?
OR blindly compare each record in 1st i/p file with all the records in second i/p file?
[A many to many comparison?]
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Tue Nov 06, 2007 4:44 pm
 Reply with quote
The fields present in the two i/p files are actually dates. The first 2 digits represent the year and the last 3 digits represent days passed. The specified field in 1st file can represent any day in any year. But the the dates represented in 2nd file is allways the last day of a month i.e
"07031" represent "1st Jan of 07"
"07059" represent "28th Feb of 07" and so on. Now I want to find the find the value present in file 1 falls in which month. There are no keys with which I compare both the files.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India
PostPosted: Tue Nov 06, 2007 5:54 pm
 Reply with quote
Learncoholic
Can i take the query like this...
The most smallest date in FILE-1 is 07052
So, the O/p should have all the records from FILE-2 which are greater than 07052?
Am i right?
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Tue Nov 06, 2007 6:05 pm
 Reply with quote
Hi krisprems,

The o/p should contain only the first record from file 2 which is greater than the date field for file 1. I have posted a sample o/p file in one of my previous responses.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India
PostPosted: Tue Nov 06, 2007 6:44 pm
 Reply with quote
You showed 07031 in the o/p here(which is lesser than the FILE-1 value(07052)
Learncoholic wrote:
Let me make it clearer with a sample o/p file. The o/p file in this case should be:

C.P.DTPURGE.ARCHIVAL.D07031
C.P.DTPURGE.ARCHIVAL.D09212
C.P.DTPURGE.ARCHIVAL.D08244
C.P.DTPURGE.ARCHIVAL.D11365


But at the begining you said you want the one's which are greater(07059) than the FILE-1 value(07052)
Quote:
07052 for 1st file is less than the value present in col(23,5) of 2nd file i.e i want A.B.KNOWLGE.ARCHIVAL.D07059 to be populated in the o/p file


Its confusing icon_confused.gif
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Tue Nov 06, 2007 6:49 pm
 Reply with quote
Sorry krisprems icon_sad.gif

The 1st record should have been C.P.DTPURGE.ARCHIVAL.D07059. The rest are the same.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India
PostPosted: Tue Nov 06, 2007 6:58 pm
 Reply with quote
Here is the SORT/ICETOOL JCL for your requiremnt
Code:
//*******************************************************               
//STEP001  EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD *                                                         
AB07052AAAAAAAAAAAA                                                     
AB09188BBBBBBBBBBBB                                                     
AB08228SSSSSSSSSSSS                                                     
AB11328GGGGGGGGGGG                                                     
/*                                                                     
//IN2      DD *                                                         
C.P.DTPURGE.ARCHIVAL.D07031                                             
C.P.DTPURGE.ARCHIVAL.D07059                                             
C.P.DTPURGE.ARCHIVAL.D07090                                             
C.P.DTPURGE.ARCHIVAL.D07120                                             
C.P.DTPURGE.ARCHIVAL.D07151                                             
C.P.DTPURGE.ARCHIVAL.D07181                                             
C.P.DTPURGE.ARCHIVAL.D07212                                             
C.P.DTPURGE.ARCHIVAL.D07243                                             
C.P.DTPURGE.ARCHIVAL.D07273                                             
C.P.DTPURGE.ARCHIVAL.D07304                                             
C.P.DTPURGE.ARCHIVAL.D07334                                             
C.P.DTPURGE.ARCHIVAL.D07365                                             
C.P.DTPURGE.ARCHIVAL.D08031                                             
C.P.DTPURGE.ARCHIVAL.D08060                                             
C.P.DTPURGE.ARCHIVAL.D08091                                             
C.P.DTPURGE.ARCHIVAL.D08121                                             
C.P.DTPURGE.ARCHIVAL.D08152                                             
C.P.DTPURGE.ARCHIVAL.D08182                                             
C.P.DTPURGE.ARCHIVAL.D08213                                             
/*                                                                     
//CP02CNTL DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA 
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 COPY FROM(IN1)  TO(CP02CNTL) USING(CP01)                               
 COPY FROM(IN2)  TO(OUT) USING(CP02)                                   
/*                                                                     
//CP01CNTL DD   *                                                       
  OUTFIL NODETAIL,REMOVECC,OVERLAY=(80:X),                             
  TRAILER1=(X,C'INCLUDE COND=(23,5,ZD,GT,',MIN=(3,5,ZD,EDIT=(TTTTT)),   
              C')')                                                     
/*                                                                     



OUT contains
Code:
C.P.DTPURGE.ARCHIVAL.D07059   
C.P.DTPURGE.ARCHIVAL.D07090   
C.P.DTPURGE.ARCHIVAL.D07120   
C.P.DTPURGE.ARCHIVAL.D07151   
C.P.DTPURGE.ARCHIVAL.D07181   
C.P.DTPURGE.ARCHIVAL.D07212   
C.P.DTPURGE.ARCHIVAL.D07243   
C.P.DTPURGE.ARCHIVAL.D07273   
C.P.DTPURGE.ARCHIVAL.D07304   
C.P.DTPURGE.ARCHIVAL.D07334   
C.P.DTPURGE.ARCHIVAL.D07365   
C.P.DTPURGE.ARCHIVAL.D08031   
C.P.DTPURGE.ARCHIVAL.D08060   
C.P.DTPURGE.ARCHIVAL.D08091   
C.P.DTPURGE.ARCHIVAL.D08121   
C.P.DTPURGE.ARCHIVAL.D08152   
C.P.DTPURGE.ARCHIVAL.D08182   
C.P.DTPURGE.ARCHIVAL.D08213   
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Wed Nov 07, 2007 11:46 am
 Reply with quote
I want only the first record from file 2 which is greater than that of file 1. I said earlier that I didn't give the entire record of file 2 due to space constraint. Well now I give it.
C.P.DTPURGE.ARCHIVAL.D07031
C.P.DTPURGE.ARCHIVAL.D07059
C.P.DTPURGE.ARCHIVAL.D07090
C.P.DTPURGE.ARCHIVAL.D07120
C.P.DTPURGE.ARCHIVAL.D07151
C.P.DTPURGE.ARCHIVAL.D07181
C.P.DTPURGE.ARCHIVAL.D07212
C.P.DTPURGE.ARCHIVAL.D07243
C.P.DTPURGE.ARCHIVAL.D07273
C.P.DTPURGE.ARCHIVAL.D07304
C.P.DTPURGE.ARCHIVAL.D07334
C.P.DTPURGE.ARCHIVAL.D07365
C.P.DTPURGE.ARCHIVAL.D08031
C.P.DTPURGE.ARCHIVAL.D08060
C.P.DTPURGE.ARCHIVAL.D08091
C.P.DTPURGE.ARCHIVAL.D08121
C.P.DTPURGE.ARCHIVAL.D08152
C.P.DTPURGE.ARCHIVAL.D08182
C.P.DTPURGE.ARCHIVAL.D08213
C.P.DTPURGE.ARCHIVAL.D08244
C.P.DTPURGE.ARCHIVAL.D08274
C.P.DTPURGE.ARCHIVAL.D08305
C.P.DTPURGE.ARCHIVAL.D08335
C.P.DTPURGE.ARCHIVAL.D08366
C.P.DTPURGE.ARCHIVAL.D09031
C.P.DTPURGE.ARCHIVAL.D09059
C.P.DTPURGE.ARCHIVAL.D09090
C.P.DTPURGE.ARCHIVAL.D09120
C.P.DTPURGE.ARCHIVAL.D09151
C.P.DTPURGE.ARCHIVAL.D09181
C.P.DTPURGE.ARCHIVAL.D09212
C.P.DTPURGE.ARCHIVAL.D09243
C.P.DTPURGE.ARCHIVAL.D09273
C.P.DTPURGE.ARCHIVAL.D09304
C.P.DTPURGE.ARCHIVAL.D09334
C.P.DTPURGE.ARCHIVAL.D09365
C.P.DTPURGE.ARCHIVAL.D10031
C.P.DTPURGE.ARCHIVAL.D10059
C.P.DTPURGE.ARCHIVAL.D10090
C.P.DTPURGE.ARCHIVAL.D10120
C.P.DTPURGE.ARCHIVAL.D10151
C.P.DTPURGE.ARCHIVAL.D10181
C.P.DTPURGE.ARCHIVAL.D10212
C.P.DTPURGE.ARCHIVAL.D10243
C.P.DTPURGE.ARCHIVAL.D10273
C.P.DTPURGE.ARCHIVAL.D10304
C.P.DTPURGE.ARCHIVAL.D10334
C.P.DTPURGE.ARCHIVAL.D10365
C.P.DTPURGE.ARCHIVAL.D11031
C.P.DTPURGE.ARCHIVAL.D11059
C.P.DTPURGE.ARCHIVAL.D11090
C.P.DTPURGE.ARCHIVAL.D11120
C.P.DTPURGE.ARCHIVAL.D11151
C.P.DTPURGE.ARCHIVAL.D11181
C.P.DTPURGE.ARCHIVAL.D11212
C.P.DTPURGE.ARCHIVAL.D11243
C.P.DTPURGE.ARCHIVAL.D11273
C.P.DTPURGE.ARCHIVAL.D11304
C.P.DTPURGE.ARCHIVAL.D11334
C.P.DTPURGE.ARCHIVAL.D11365
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India
PostPosted: Thu Nov 08, 2007 8:25 am
 Reply with quote
Quote:
I want only the first record from file 2 which is greater than that of file 1

The first record greater than that of the file1, from your shown example is
Code:
C.P.DTPURGE.ARCHIVAL.D07059

So, u want only this record in the output?
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Fri Nov 09, 2007 10:09 am
 Reply with quote
Hi krisprems

"So, u want only this record in the output?"

No, not only this record. This is the first o/p record for first record of File 1. Similarly I want the corresponding o/p records for other records of file 1. i.e along with the record C.P.DTPURGE.ARCHIVAL.D07059 the records
C.P.DTPURGE.ARCHIVAL.D09212
C.P.DTPURGE.ARCHIVAL.D08244
C.P.DTPURGE.ARCHIVAL.D11365
shall also be present.[/quote]
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Sat Nov 10, 2007 3:16 am
 Reply with quote
Can the same record be selected for output more than once? For example, could the input for file1 be:

Code:

AB07052AAAAAAAAAAAA
AB07054AAAAAAAAAAAA
AB09188BBBBBBBBBBBB
AB08228SSSSSSSSSSSS
AB11328GGGGGGGGGGG
AB07053AAAAAAAAAAAA


and would the output then be:

Code:

C.P.DTPURGE.ARCHIVAL.D07059
C.P.DTPURGE.ARCHIVAL.D07059
C.P.DTPURGE.ARCHIVAL.D09212
C.P.DTPURGE.ARCHIVAL.D08244
C.P.DTPURGE.ARCHIVAL.D11365
C.P.DTPURGE.ARCHIVAL.D07059


What is the approximate maximum number of file1 records?

What is the RECFM and LRECL of file1?

What is the RECFM and LRECL of file2?
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India
PostPosted: Sun Nov 11, 2007 11:58 am
 Reply with quote
Learncoholic
Here is the SORT/ICETOOL JCL for your requirement.
Code:
//*******************************************************           
//STEP001  EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN1      DD *                                                     
AB07052AAAAAAAAAAAA                                                 
AB09188BBBBBBBBBBBB                                                 
AB08228SSSSSSSSSSSS                                                 
AB11328GGGGGGGGGGG                                                 
/*                                                                 
//IN2      DD *                                                     
C.P.DTPURGE.ARCHIVAL.D07031                                         
C.P.DTPURGE.ARCHIVAL.D07059                                         
C.P.DTPURGE.ARCHIVAL.D07090                                         
.
.
.
.
.
and so on
/*                                                                     
//CP02CNTL DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA 
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 SORT FROM(IN1)  TO(CP02CNTL) USING(CP01)                               
 SELECT FROM(IN2)  TO(OUT) ON(81,5,CH) FIRST USING(CP02)               
/*                                                                     
//CP01CNTL DD   *                                                       
  SORT FIELDS=(3,5,ZD,A)                                               
  OUTREC OVERLAY=(81:SEQNUM,5,PD)                                       
  OUTFIL REMOVECC,IFTHEN=(WHEN=(81,5,PD,EQ,1),                         
         BUILD=(C' OUTFIL OMIT=(81,5,CH,EQ,C''',C' '')',/,             
         C' INREC IFTHEN=(WHEN=(23,5,ZD,GE,',3,5,                       
         C'),OVERLAY=(81:C''',3,5,C'''',C'),HIT=NEXT),')),             
  IFTHEN=(WHEN=NONE,                                                   
   BUILD=(2:C'IFTHEN=(WHEN=(23,5,ZD,GE,',3,5,                           
          C'),OVERLAY=(81:C''',3,5,C'''',C'),HIT=NEXT),')),             
  TRAILER1=(C' IFTHEN=(WHEN=NONE,OVERLAY=(81:5X))')                     
/*                                                                     


OUT contains:
Code:
C.P.DTPURGE.ARCHIVAL.D07059
C.P.DTPURGE.ARCHIVAL.D08244
C.P.DTPURGE.ARCHIVAL.D09212
C.P.DTPURGE.ARCHIVAL.D11334


    Note
      The output i have shown is in sorted order of the date field(however you can get the original order of the i/p records if needed).
      The last record is in my o/p is C.P.DTPURGE.ARCHIVAL.D11334 , but you have shown C.P.DTPURGE.ARCHIVAL.D11365 in your o/p
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India
PostPosted: Wed Nov 14, 2007 12:22 pm
 Reply with quote
Hi krisprems
That is the required o/p I wanted.

Thanks all for your time and support.
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 -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us