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 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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.