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
 

 

Write if key1 is found and key2 is not found - No Joinkeys

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

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Thu Aug 16, 2012 12:12 pm    Post subject: Write if key1 is found and key2 is not found - No Joinkeys
Reply with quote

Hello,

I have the below input files. first column is key1 and second column is key2. The sample output should have the records if key1 in file 1 is found in file 2 and the corresponding key2 in file 1 is not found in file 2.

key1 - can have duplicates
key2 - can have duplicates
key1 + key2 combination - is unique.

My idea was a two step solution:
step 1: find the unmatched records from file1 with file2, key = key1 + key2
step 2: find the matched records from output of step 1 with file 2, key = key 1.

Problem:
THe DFSORT PTF we have is July 2008 and hence we can't use JOINKEYS.
Both the files are FB, LRECL = 80.
Could any of you guide me in getting the expected result.

sample input File 1:
Code:
A 01 SSS
A 02 SSS
B 03 TTT
B 02 TTT
B 01 TTT
C 03 RRR
D 03 EEE
Z 03 WER
Z 05 WER


sample input File 2:
Code:
A 01 SSS
A 05 SSS
B 99 TTT
B 96 TTT
B 97 TTT
B 09 TTT
C 03 RRR
E 03 EEE
E 04 EEE
F 04 EEE

Expected Output:
Code:
A 02 SSS
B 03 TTT
B 02 TTT
B 01 TTT


Code'd
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7255

PostPosted: Thu Aug 16, 2012 12:30 pm    Post subject: Reply to: Write if key1 is found and key2 is not found - No
Reply with quote

See if this recent topic helps.
Back to top
View user's profile Send private message
senjay

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Thu Aug 16, 2012 5:02 pm    Post subject: Reply to: Write if key1 is found and key2 is not found - No
Reply with quote

Bill,
Thanks to the pointer, Now I got the solution.

The solution is posted below as it might be of some help to others. THe LRECL is assumed to be 20.

Code:

//STEP010  EXEC  PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN1      DD DSN=ABC.INP1,DISP=SHR                               
//IN2      DD DSN=ABC.INP2,DISP=SHR                               
//MOD1     DD DSN=&&MOD1,                   
//            DISP=(MOD,PASS),
//            SPACE=(CYL,(X,Y),RLSE)                               
//OUT1     DD DSN=ABC.OUT1,                                       
//            DISP=(,CATLG,DELETE),                                   
//            SPACE=(CYL,(2,5),RLSE),                                 
//            DCB=(RECFM=FB,LRECL=20)                                 
//OUTX     DD SYSOUT=*                                               
//TOOLIN   DD *                                                       
 COPY FROM(IN1) TO(MOD1) USING(ICE1)                                 
 COPY FROM(IN2) TO(MOD1) USING(ICE2)                                 
 SELECT FROM(MOD1) TO(OUT1) ON(1,1,CH) ON(3,2,CH) NODUPS USING(ICE3) 
//ICE1CNTL DD *                                         
 INREC OVERLAY=(21:C'A')                               
//ICE2CNTL DD *                                         
 INREC OVERLAY=(21:C'B')                               
//*                                                     
//ICE3CNTL DD *                                         
 OUTFIL INCLUDE=(21,1,CH,EQ,C'A'),OUTREC=(1,20)         
//*                                                     
//STEP020  EXEC  PGM=ICETOOL                           
//TOOLMSG  DD SYSOUT=*                                 
//DFSMSG   DD SYSOUT=*                                 
//OUT1     DD DSN=ABC.OUT1,DISP=SHR                 
//IN2      DD DSN=ABC.INP2,DISP=SHR                 
//MOD2     DD DSN=&&MOD2,DISP=(MOD,PASS),
//             SPACE=(CYL,(X,Y),RLSE)           
//OUT2     DD DSN=ABC.OUT2,                         
//            DISP=(,CATLG,DELETE),                     
//            SPACE=(CYL,(2,5),RLSE),                   
//            DCB=(RECFM=FB,LRECL=20)                     
//OUTX     DD SYSOUT=*                                     
//TOOLIN   DD *                                           
 SELECT FROM(IN2) TO(MOD2) ON(1,1,CH) FIRST USING(ICE1)   
 COPY FROM(OUT1) TO(MOD2) USING(ICE2)                     
 SPLICE FROM(MOD2) TO(OUT2) ON(1,1,CH) WITHALL -           
 WITH(1,21) KEEPNODUPS KEEPBASE USING(ICE3)               
//ICE1CNTL DD *                                           
 INREC OVERLAY=(21:C'RR')                                 
//ICE2CNTL DD *                                           
 INREC OVERLAY=(21:C'MM')                                 
//*                                                       
//ICE3CNTL DD *                                           
 OUTFIL INCLUDE=(21,2,CH,EQ,C'MR'),BUILD=(1,20)           
//*     


Explanation:
In step010, key is KEY1 + Key2 and the unmatched record from file 1 with file 2 is written in to OUT1 file.

In step020, the records from OUT1 file that don't have key1 in INP2 file should be rejected. since INP2 file records are not written in to output file, we remove the duplicates based on KEY1 from INP2 file. Now we have a master file (OUT1 ddname) with duplicates on it (key = KEY1) and Reference file with no duplicates (Key = KEY1). Then SPLICE mentioned in the pointer is applied to get the required output.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Aug 16, 2012 9:54 pm    Post subject:
Reply with quote

senjay,

You really don't need that many passes of data, Here is 2 pass solution. Your both input files are FB and lrecl=80 , so we can actually concatenate a fixed header to identify the records from each files using that header.

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN       DD *                                                     
$$$$                                                               
//         DD *                                                     
A 01 SSS                                                           
A 02 SSS                                                           
B 03 TTT                                                           
B 02 TTT                                                           
B 01 TTT                                                           
C 03 RRR                                                           
D 03 EEE                                                           
Z 03 WER                                                           
Z 05 WER                                                           
//         DD *                                                     
$$$$                                                               
//         DD *                                                     
A 01 SSS                                                           
A 05 SSS                                                           
B 99 TTT                                                           
B 96 TTT                                                           
B 97 TTT                                                           
B 09 TTT                                                           
C 03 RRR                                                           
E 03 EEE                                                           
E 04 EEE                                                           
F 04 EEE                                                           
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  SORT FROM(IN) USING(CTL1)                                         
  SORT FROM(T1) USING(CTL2)                                         
//CTL1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,4,CH,EQ,C'$$$$'),PUSH=(81:ID=1))
  SORT FIELDS=(1,4,CH,A),EQUALS                                     
  SUM FIELDS=(81,1,ZD)                                             
  OUTFIL FNAMES=T1                                                 
//*                                                                 
//CTL2CNTL DD *                                                     
  SORT FIELDS=(1,1,CH,A,81,1,ZD,D),EQUALS                           
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(83:SEQNUM,4,ZD,RESTART=(1,1))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(83,4,ZD,EQ,1),PUSH=(82:81,1))           
  OUTFIL FNAMES=OUT,BUILD=(1,80),INCLUDE=(81,2,SS,EQ,C'12,13')       
//*


This will produce the following output
Code:

A 02 SSS
B 01 TTT
B 02 TTT
B 03 TTT
Back to top
View user's profile Send private message
senjay

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Fri Aug 24, 2012 9:11 am    Post subject: Reply to: Write if key1 is found and key2 is not found - No
Reply with quote

Hi Skolusu,

Thanks for the efficient one, This gives me some more ideas.

Thanks again,
Senthil
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 Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts Joinkeys operation betwen VB and FB file Susanta SYNCSORT 4 Sun Sep 25, 2016 9:49 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts how to DYNAMICALLY write sort cond to... mohamedmubee COBOL Programming 13 Fri Aug 26, 2016 11:46 pm


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