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

Join Keys - Dynamic key is possible?


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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

Global Moderator


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

PostPosted: Tue May 14, 2013 1:03 am
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

Moderator Emeritus


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

PostPosted: Tue May 14, 2013 1:06 am
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
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
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

Moderator Emeritus


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

PostPosted: Tue May 14, 2013 12:15 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
No new posts Use input file with OMIT rcd keys? DFSORT/ICETOOL 15
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts JCL Dynamic System Symbols JCL & VSAM 3
Search our Forums:

Back to Top