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
 

 

Joinkeys conditional selection of records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Joinkeys conditional selection of records
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

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 13, 2013 7:51 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Nov 13, 2013 7:53 pm    Post subject: Reply to: Joinkeys conditional selection of records
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Nov 13, 2013 8:50 pm    Post subject: Reply to: Joinkeys conditional selection of records
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    Post subject:
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    Post subject:
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    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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 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 Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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