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
 

 

Join Keys - Dynamic key is possible?

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

New User


Joined: 29 Jun 2005
Posts: 13
Location: USA

PostPosted: Tue May 14, 2013 12:48 am    Post subject: Join Keys - Dynamic key is possible?
Reply with quote

Hi,

Here is my requirement. I have transaction file and DB2 table. I am reading the input file and accessing DB2 table to fetch the data. As we are expecting huge data, we are looking for alternate options so that we can minimize the DB2 access.

We are planning to use SORT JOIN. I can use transaction file as F1 and DB2 unload as F2. I can match against the key and find matching and unmatching values.

I am having difficulty in transforming the below case logic in DB2 to equivalent SORT JOIN. IF BENEFIT_ID is space then we don't use it as part of key. We just Match BENEFIT_ID with BENEFIT_ID otherwise, we will use the input CMFMCW-BENEFIT-ID to match with DB2 table.
We can create one file with BENEFIT_ID as spaces and another file with BENEFIT_ID as valid values and create different sort join steps to match with BENEFIT_ID as key and without key. This will require additional steps.

I have 12 similar case statements in my DB2 query. So looking for better options.

Code:
WHERE PLAN_ID            = :CMFMCW-PLAN-ID             
  AND PLATFORM_CD        = :CMFMCW-PLATFORM-CD         
  AND BLK_BUS_CD         = :CMFMCW-BLK-BUS-CD         
  AND BENEFIT_ID         = (CASE BENEFIT_ID           
                            WHEN '  '                 
                            THEN BENEFIT_ID           
                            ELSE :CMFMCW-BENEFIT-ID   
                            END)                       
  AND MKTSRC_NBR         = (CASE MKTSRC_NBR           
                            WHEN '  '                 
                            THEN MKTSRC_NBR           
                            ELSE :CMFMCW-MKTSRC-NBR   
                            END)                       



I have written below SORT JOIN using the 5 keys.

Code:
//SYSIN    DD  *                                           
   JOINKEYS FILE=F1,FIELDS=(402,8,A,410,8,A,15,2,A,       
                            17,2,A,444,9,A)               
   JOINKEYS FILE=F2,FIELDS=(17,8,A,5,8,A,13,2,A,           
                            15,2,A,150,9,A)               
   JOIN UNPAIRED,F1,F2                                     
   REFORMAT FIELDS=(F1:1,1000,F2:1,164,?)                 
   OPTION COPY                                             
   OUTFIL FNAMES=F1ONLY,INCLUDE=(1165,1,CH,EQ,C'1'),       
     BUILD=(1,1000)                                       
   OUTFIL FNAMES=F2ONLY,INCLUDE=(1165,1,CH,EQ,C'2'),       
     BUILD=(1001,164)                                     
   OUTFIL FNAMES=F1MATCH,INCLUDE=(1165,1,CH,EQ,C'B'),     
     BUILD=(1,1000)                                       
   OUTFIL FNAMES=F2MATCH,INCLUDE=(1165,1,CH,EQ,C'B'),     
     BUILD=(1001,164)                                     
//*                                                       



Please let me know if you need additional details. Appreciate your help.
Back to top
View user's profile Send private message

Pandora-Box

Moderator


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

PostPosted: Tue May 14, 2013 1:03 am    Post subject:
Reply with quote

You have started well

You could use JNFnCNTL to filter the data that needs to be joined

Lots of samples in forum
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue May 14, 2013 1:06 am    Post subject: Reply to: Join Keys - Dynamic key is possible?
Reply with quote

How about showing some sample input data which covers your conditions, and the expected outputs?
Back to top
View user's profile Send private message
rmaruri

New User


Joined: 29 Jun 2005
Posts: 13
Location: USA

PostPosted: Tue May 14, 2013 1:17 am    Post subject:
Reply with quote

Pandora,

I thought about JNF*CNTL but I am not sure how it will populate the data from DB2 table unload into F1. I believe, the below code doesn't work as have to not yet build 1065 record when we are executing JNF*CNTL.

Code:
INREC IFTHEN=(WHEN=(39,2,CH,EQ,C'  '),
              OVERLAY=(39:1025,2))     


Bill Woodger,

In DB2 query, BENEFIT_ID is space we will use BENEFIT_ID means compare BENEFIT_ID = BENEFIT_ID. It is as same as not having selection creteria or key.

I am looking for way to populate F1 spaces with value from F2 so that both can match in case of space. Not sure how to do this in SORT JOIN.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue May 14, 2013 2:52 am    Post subject:
Reply with quote

rmaruri,

You need to match on just the static keys and then check dynamic fields in the maintask using IFTHEN
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue May 14, 2013 12:15 pm    Post subject: Reply to: Join Keys - Dynamic key is possible?
Reply with quote

rmaruri,

I'm not sure what was unclear about what I requested.

I know what it is doing. You explained once already, and showed the code. What I don't know is where this particular "key" field is in relation to the stuff which remains static, even.

Make sure that what you show includes sufficient to resolve the question of "what 12 similar" things you have.
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 How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Need to create file with Dynamic Name... kapil27 JCL & VSAM 4 Wed May 25, 2016 9:45 am


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