Match ON Key, and match only those records which satisfy INCLUDE COND
Expected:
Output1 - Matched records
Output2 - Unmatched records
For Output1, I have tried following SPLICE JCL:
Code:
//STEP001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=MYINPUT1
//IN2 DD DSN=MYINPUT2
//TEMP1 DD DSN=&&TEMP1,DISP=(,PASS),SPACE=(CYL,(1,5)),UNIT=SYSDA
//TEMP2 DD DSN=&&TEMP2,DISP=(,PASS),SPACE=(CYL,(1,5)),UNIT=SYSDA
//CONCAT DD DSN=*.TEMP1,VOL=REF=*.TEMP1,DISP=(OLD,PASS)
// DD DSN=*.TEMP2,VOL=REF=*.TEMP2,DISP=(OLD,PASS)
//OUTPUT1 DD DSN=matchind output1.........
//TOOLIN DD *
COPY FROM(IN1) TO(TEMP1) USING(CTL1)
COPY FROM(IN2) TO(TEMP2) USING(CTL2)
SPLICE FROM(CONCAT) TO(OUTPUT1) ON(61,8,CH) WITH(69,32) -
KEEPNODUPS USING(CTL3)
/*
//CTL1CNTL DD *
SORT FIELDS=(27,8,CH,A)
INCLUDE COND=(38,1,CH,EQ,C'N')
OUTREC FIELDS=(1,60, INPUT1 DATA
61:27,8, ADD KEY ON RIGHT
69:32X) ADD SPACES TO MATCH WITH TEMP2
/*
//CTL2CNTL DD *
SORT FIELDS=(3,8,CH,A)
INCLUDE COND=(1,2,CH,EQ,C'13')
OUTREC FIELDS=(61:1,8, PUT MERCHANT ID RHS
69:1,32) ENTIRE INPUT
/*
//CTL3CNTL DD *
OPTION COPY
OUTFIL FNAMES=OUTPUT1,OUTREC=(1,60)
/*
After running, I am getting lots of records which have spaces. I am also not sure if my SPLICE is working as I am running the JCL with lots of records.
Can you please advice, how can I create output2 (unmatched)?
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
OK. But....
When you run your job you get messages in the Sysout. One of which is ICE201I. I asked if you could show us that, because unless you have no option (due to out-of-date product) then SPLICE is not the way to go, and I, for one, am not going to look at a SPLICE for problems unless there is a reason to.
From what you have described, the task is very simple with JOINKEYS. The ICE201I will tell us if you have JOINKEYS.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You have a "hit" on 7 and 11, a "miss" with 12, 13 and 15, and four "hits" for 10 because, despite what you thought, you have two 10s on your input and two on your output.
You haven't done the INCLUDEs.
These should be in the JNFnCNTL files, which you aren't using.
You have a "hit" on 7 and 11, a "miss" with 12, 13 and 15, and four "hits" for 10 because, despite what you thought, you have two 10s on your input and two on your output.
You haven't done the INCLUDEs.
These should be in the JNFnCNTL files, which you aren't using.
You are not using the ? (match marker).
You have not posted the ICE201I.
Do you have SyncSort?
I do not know the syntax to use INCLUDE COND on JOINKEYS
What is that?
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
I do not know but I can say this supports both SPLICE and JOINKEYS
while matching use INCLUDE COND=(1,2,CH,EQ,C'13')
FB
LRECL = 32
Key = 3,8,CH
Expected Output1 (Matched from Input1):
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
***************************** Top of Data *******************
DTL,2012-06-23,00:00:0000,000007 ,C,N,2012-06-26,
DTL,2012-06-23,00:00:0000,000010 ,C,N,2012-06-26,
Expected Output2 (unmatched from Input1):
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
***************************** Top of Data *******************
DTL,2012-06-23,00:00:0000,000011 ,C,N,2012-06-26,
DTL,2012-06-23,00:00:0000,000012 ,C,N,2012-06-26,
DTL,2012-06-23,00:00:0000,000013 ,C,N,2012-06-26,
DTL,2012-06-23,00:00:0000,000015 ,C,N,2012-06-26,
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
OK, you're pretty up-to-date (that's the H bit in the message).
Like the "CNTL" files for ICETOOL, JOINKEYS has "CNTL" files. JNF1CNTL (for the first file on the JOINKEYS) and JNF2CNTL (for the second).
So, for testing
Code:
//JNF1CNTL DD *
INCLUDE COND=(whatever for file 1
//JNF2CNTL DD *
INCLUDE COND=(whatever for file 2
Once out of unit-testing, I'd hope they'd be on a PDSE/PDS, but that is another story.
On your REFORMAT try this:
Code:
REFORMAT FIELDS=(F1:1,60,?)
The ? (you code it like that) is the "match marker". On the REFORMAT record, it will have a value of "B" for Both files, 1 for File 1 only and 2 for File 2 only (which you won't get with your control cards).
On the OUTFIL, rather than negating the test, you can use SAVE, which says "everything else you haven't already written to an OUTFIL".
SORT Manual says:
The REFORMAT statement indicates the fields from the F1 file and/or the F2 file you want to include in the joined records, and the order in which you want the fields to appear. You can also include an indicator of where the key was found in the joined records ('B' for key found in F1 and F2, '1' for key found in F1 only, or '2' for key found in F2 only).
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
The Fn:1,1 and then test for space is a SyncSort technique because SyncSort does not have the ? marker. You need to choose a location which cannot possibly have that value, so doesn't need to be 1,1, you can change the fill character (used to create "no data" for a mismatch) to another value as well.
Yes, your REFORMAT can contain data from both files. Most often it will. Where there is a mismatch, the data from the file which did not match gets set to a "pad" value.
Yes, the ? indicator will still tell you the "condition" of the JOIN. B, 1 or 2, and this is 100% reliable without expending effort choosing a location/different pad value.