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

Joinkeys conditional selection of records


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

New User


Joined: 30 Jun 2010
Posts: 85
Location: Toronto, ON

PostPosted: Wed Nov 13, 2013 7:36 pm
Reply with quote

Hi,

I am trying to work on Joinkeys requirement and need some help.

SORTJNF1 - FB/LRECL - 102
SORTJNF1 file will be having unique occurence of the key.

SORTJNF2 - FB-LRECL - 834
SORTJNF2 file will be having multiple occurence of the key.

SORTOUT - FB/LRECL - 834

Code:

JOINKEYS FILE=F1,FIELDS=(1,16,A)           
JOINKEYS FILE=F2,FIELDS=(1,16,A)           
OPTION COPY                               
REFORMAT FIELDS=(F2:1,834,F1:1,102,?)     
INREC IFTHEN=(WHEN=((806,1,CH,EQ,C'S',OR, 
                     806,1,CH,EQ,C'E'),AND,
                     880,1,CH,EQ,C'S'),   
OVERLAY=(17:C'S'))                         
INCLUDE COND=(937,1,CH,EQ,C'B')           
OUTREC FIELDS=(1,834)                     


I am using the above code successfully to get the common matched OVERLAYED records at the output file.
However, my requirement is to OVERLAY the common matched records in the output file while write the unmatched records from SORTJNF2 file at the output as is.

Can I achieve this at one pass? Pls advise.
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 Nov 13, 2013 7:51 pm
Reply with quote

Use JOIN UNPAIRED F1,F2 and then OUTFIL INCLUDE + BUILD

So your code would become

Try this untested

Code:
JOINKEYS FILE=F1,FIELDS=(1,16,A)           
JOINKEYS FILE=F2,FIELDS=(1,16,A)       
JOIN UNPAIRED F1,F2                 
REFORMAT FIELDS=(F2:1,834,F1:1,102,?)     
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=((806,1,CH,EQ,C'S',OR, 
                     806,1,CH,EQ,C'E'),AND,
                     880,1,CH,EQ,C'S'),   
OVERLAY=(17:C'S'))                         
OUTFIL FNAMES=BOTHFS,INCLUDE=(937,1,CH,EQ,C'B'),BUILD=(1,834)
OUTFIL FNAMES=F2ONLY,INCLUDE=(937,1,CH,EQ,C'2'),BUILD=(1,102) 
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Nov 13, 2013 7:53 pm
Reply with quote

Add JOIN UNPAIRED,F2

Your IFTHEN will be false (the F1 data will all be blanks).

Your INCLUDE COND= is redundant in your code (you only get Bs) and is unnecessary also for getting Bs and 2s, as you'll only get given those from the above JOIN.

You can "cut down" the REFORMAT record with IFOUTLEN=834, so when the IFTHEN processing is finished, the records will be treated as 834 bytes.

Something like this should be close:

Code:
 JOINKEYS FILE=F1,FIELDS=(1,16,A)           
 JOINKEYS FILE=F2,FIELDS=(1,16,A)
 JOIN UNPAIRED,F2           
 OPTION COPY                               
 REFORMAT FIELDS=(F2:1,834,F1:1,102)     
 INREC IFOUTLEN=834,
       IFTHEN=(WHEN=((806,1,CH,EQ,C'S',OR,
                      806,1,CH,EQ,C'E'),AND,
                      880,1,CH,EQ,C'S'),   
                       OVERLAY=(17:C'S'))
Back to top
View user's profile Send private message
ksouren007

New User


Joined: 30 Jun 2010
Posts: 85
Location: Toronto, ON

PostPosted: Wed Nov 13, 2013 8:07 pm
Reply with quote

Thanks all for replying.

I went for Bill's option as I wanted one output file at a single pass..and It worked great.

Thanks a lot!
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Nov 13, 2013 8:50 pm
Reply with quote

Thanks for letting us know. You can drop the ? in the REFORMAT, as the content is no longer used. I missed that before. Updating the original.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Nov 13, 2013 9:20 pm
Reply with quote

Ksouren007,

You don't really need to sort the whole File 1 as you are only interested in just the key and 46th byte (validating byte 880 after reformat), so just build those 2 fields.

Also as rule of thumb always code your duplicate file as SORTJNF1 and unique file as SORTJNF2.

Use the following JCL
Code:

//STEP0100 EXEC PGM=SORT   
//SYSOUT   DD SYSOUT=*     
//SORTJNF1 DD DISP=SHR,DSN=Your 834 Byte Duplicate file
//SORTJNF2 DD DISP=SHR,DSN=Your 102 Byte Unique file
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                                   
  OPTION COPY                                                     
  JOINKEYS FILE=F1,FIELDS=(1,16,A)                               
  JOINKEYS FILE=F2,FIELDS=(1,16,A)                               
  JOIN UNPAIRED                                                   
  REFORMAT FIELDS=(F1:1,834,F2:17,1)                             
  INREC IFOUTLEN=834,                                             
        IFTHEN=(WHEN=((806,1,CH,EQ,C'S',OR,806,1,CH,EQ,C'E'),AND,
                       835,1,CH,EQ,C'S'),OVERLAY=(17:C'S'))       
//*                                                               
//JNF2CNTL DD *
  INREC BUILD=(1,16,46,1)
//*
Back to top
View user's profile Send private message
ksouren007

New User


Joined: 30 Jun 2010
Posts: 85
Location: Toronto, ON

PostPosted: Thu Nov 14, 2013 11:56 am
Reply with quote

Thanks for letting me know the trick Skolusu...the tweak went fine!
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts PD not working for unsigned packed JO... DFSORT/ICETOOL 5
No new posts Def PD not working for unsigned packe... JCL & VSAM 3
Search our Forums:

Back to Top