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
 

 

Extract records after comparing fields present in 2 diff fil

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Tue Nov 06, 2007 2:20 pm    Post subject: Extract records after comparing fields present in 2 diff fil
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: 1439
Location: Bangalore,India

PostPosted: Tue Nov 06, 2007 2:25 pm    Post subject:
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: 1288
Location: Chennai, India

PostPosted: Tue Nov 06, 2007 2:27 pm    Post subject: Reply to: Extract records after comparing fields present in
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: 73
Location: India

PostPosted: Tue Nov 06, 2007 2:54 pm    Post subject:
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: 73
Location: India

PostPosted: Tue Nov 06, 2007 3:06 pm    Post subject:
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    Post subject:
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: 73
Location: India

PostPosted: Tue Nov 06, 2007 4:44 pm    Post subject:
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    Post subject:
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: 73
Location: India

PostPosted: Tue Nov 06, 2007 6:05 pm    Post subject:
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    Post subject:
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: 73
Location: India

PostPosted: Tue Nov 06, 2007 6:49 pm    Post subject:
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    Post subject:
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: 73
Location: India

PostPosted: Wed Nov 07, 2007 11:46 am    Post subject:
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    Post subject:
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: 73
Location: India

PostPosted: Fri Nov 09, 2007 10:09 am    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Nov 10, 2007 3:16 am    Post subject:
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    Post subject:
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: 73
Location: India

PostPosted: Wed Nov 14, 2007 12:22 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am


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