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
 

 

Matching two files, matched and unmatched
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Tue Oct 30, 2012 7:18 pm    Post subject: Matching two files, matched and unmatched
Reply with quote

Hi friends,

Input1 -
FB
LRECL = 60
Key = 27,8,CH
INCLUDE COND=(38,1,CH,EQ,C'N')

Input2 -
FB
LRECL = 32
Key = 3,8,CH
INCLUDE COND=(1,2,CH,EQ,C'13')

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)?

Many thanks,
zh_lad
Back to top
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Tue Oct 30, 2012 7:22 pm    Post subject:
Reply with quote

If you want any help use code tags. I do that only once.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue Oct 30, 2012 7:34 pm    Post subject: Reply to: Matching two files, matched and unmatched
Reply with quote

Forget SPLICE unless your DFSORT is not up-to-date.

Can you post the ICE201I message from any Sort step and we can see?

JOINKEYS is what you want.

In the JNFnCNTL files you can do your INCLUDEs for the separate files.

The matched/unmatched is simple using the ? (match marker) on the REFORMAT record.

You need two OUTFILs.

Do you have duplicates by key on either file?
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Tue Oct 30, 2012 7:49 pm    Post subject:
Reply with quote

Thanks for your reply. I am returning to Mainframe, JCL and SORT after 3 years gap, was doing Non Mainframe related work.

My files can have duplicates on Key, but with INCLUDE COND in both files, I will have only one record (1 to 1).

Input1:
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
***************************** Top of Data *******************
HDR,2012-06-26,                                             
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,           
DTL,2012-06-23,00:00:0000,000010  ,C,R,2012-06-26,           
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,           
TRL,00000009 



Input2:
Code:
----+----1----+----2----+----3--
***************************** To
----+----1----+----2----+----3--
***************************** To
13000001  CB2012-10-22         
13000002  CB2012-10-22         
13000007  CB2012-10-22         
13000010  CB2012-10-22         
14000010  CB2012-10-22         
13000011  CB2012-10-22         



Expected Output1:
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,     



My JCL is giving this output1:

Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6
****** ***************************** Top of Data ******************
000001 DTL,2012-06-23,00:00:0000,000007  ,C,N,2012-06-26,         
000002 DTL,2012-06-23,00:00:0000,000010  ,C,N,2012-06-26,         
000003 DTL,2012-06-23,00:00:0000,000011  ,C,N,2012-06-26,         
000004 DTL,2012-06-23,00:00:0000,000012  ,C,N,2012-06-26,         
000005 DTL,2012-06-23,00:00:0000,000013  ,C,N,2012-06-26,         
000006 DTL,2012-06-23,00:00:0000,000015  ,C,N,2012-06-26,         
000007                                                             
000008                                                             
000009                                                             
000010                                                             
000011
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue Oct 30, 2012 7:54 pm    Post subject: Reply to: Matching two files, matched and unmatched
Reply with quote

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.
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Tue Oct 30, 2012 7:55 pm    Post subject:
Reply with quote

When I use JOINKEYS.

Code:
//STEP001  EXEC PGM=SORT                                   
//SORTJNF1 DD DSN=TTMA.L.NMA0306.#N.MREGCSV.RV4,DISP=SHR   
//SORTJNF2 DD DSN=TTMA.N.NMA0125.#1.MADBMFNX.O6,DISP=SHR   
//SORTOF01 DD DSN=TTMA.N.NMA0306.#N.MREGCSV.NOTIFIED.FOUND,
//             RECFM=FB,LRECL=60,DISP=(,CATLG),           
//             DATACLAS=DSIZE100                           
//SORTOF02 DD DSN=TTMA.N.NMA0306.#N.MREGCSV.NOTIFIED.NOTFD,
//             RECFM=FB,LRECL=60,DISP=(,CATLG),           
//             DATACLAS=DSIZE100                           
//SYSOUT   DD SYSOUT=*                                     
//SYSUDUMP DD SYSOUT=*                                     
//SYSIN    DD *                                           
  JOINKEYS FILE=F1,FIELDS=(27,8,A)                       
  JOINKEYS FILE=F2,FIELDS=(3,8,A)                         
  JOIN UNPAIRED,F1                                       
  REFORMAT FIELDS=(F1:1,60,F2:1,1)                       
  SORT FIELDS=COPY                                       
  OUTFIL FILES=01,INCLUDE=(61,1,CH,NE,X'40'),BUILD=(1,60)
  OUTFIL FILES=02,INCLUDE=(61,1,CH,EQ,X'40'),BUILD=(1,60)
/*                                                       


I am getting following output:

Output1:
Code:
000001 DTL,2012-06-23,00:00:0000,000007  ,C,N,2012-06-26,
000002 DTL,2012-06-23,00:00:0000,000010  ,C,N,2012-06-26,
000003 DTL,2012-06-23,00:00:0000,000010  ,C,N,2012-06-26,
000004 DTL,2012-06-23,00:00:0000,000010  ,C,R,2012-06-26,
000005 DTL,2012-06-23,00:00:0000,000010  ,C,R,2012-06-26,
000006 DTL,2012-06-23,00:00:0000,000011  ,C,N,2012-06-26,


Output2:
Code:
HDR,2012-06-26,                                   
TRL,00000009                                     
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,


Please advice, what is wrong in my JCL?

Updated later - Apology as I have not used INCLUDE COND on both inputs.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue Oct 30, 2012 8:01 pm    Post subject: Reply to: Matching two files, matched and unmatched
Reply with quote

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?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Tue Oct 30, 2012 8:03 pm    Post subject:
Reply with quote

Hi zh_lad,

What is your expected output in both the output files?

If you could say that someone will be able to tell you
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Tue Oct 30, 2012 8:12 pm    Post subject: Re: Reply to: Matching two files, matched and unmatched
Reply with quote

Bill Woodger wrote:
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
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Oct 30, 2012 8:23 pm    Post subject: Re: Reply to: Matching two files, matched and unmatched
Reply with quote

zh_lad wrote:
I do not know the syntax to use INCLUDE COND on JOINKEYS

What is that?

In that case, you may wish to read the fine manual on JOINKEYS.
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Tue Oct 30, 2012 8:23 pm    Post subject:
Reply with quote

Input1 (driver):
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
HDR,2012-06-26,                                             
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,           
DTL,2012-06-23,00:00:0000,000010  ,C,R,2012-06-26,           
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,000013  ,C,R,2012-06-26,           
DTL,2012-06-23,00:00:0000,000015  ,C,N,2012-06-26,           
TRL,00000010                                                 

While matching use INCLUDE COND=(38,1,CH,EQ,C'N'),
FB
LRECL = 60
Key = 27,8,CH

Input2:
Code:
----+----1----+----2----+----3--
----+----1----+----2----+----3--
13000001  CB2012-10-22         
13000002  CB2012-10-22         
13000007  CB2012-10-22         
13000010  CB2012-10-22         
14000010  CB2012-10-22         
13000011  CB2012-10-22         
14000011  CB2012-10-22         
13000019  CB2012-10-22   


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,     
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue Oct 30, 2012 8:28 pm    Post subject: Reply to: Matching two files, matched and unmatched
Reply with quote

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".
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Tue Oct 30, 2012 8:40 pm    Post subject:
Reply with quote

Thanks. My latest code:

Code:
//SYSIN    DD *                                           
  JOINKEYS FILE=F1,FIELDS=(27,8,A)                       
  JOINKEYS FILE=F2,FIELDS=(3,8,A)                         
  JOIN UNPAIRED,F1                                       
  REFORMAT FIELDS=(F1:1,60,F2:1,1)                       
  SORT FIELDS=COPY                                       
  OUTFIL FILES=01,INCLUDE=(61,1,CH,NE,X'40'),BUILD=(1,60)
  OUTFIL FILES=02,INCLUDE=(61,1,CH,EQ,X'40'),BUILD=(1,60)
/*                                                       
//JNF1CNTL DD *                                           
  INCLUDE COND=(38,1,CH,EQ,C'N')                         
/*                                                       
//JNF2CNTL DD *                                           
  INCLUDE COND=(1,2,CH,EQ,C'13')                         
/*                                                       


Input1:
Code:
HDR,2012-06-26,                                   
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,
DTL,2012-06-23,00:00:0000,000010  ,C,R,2012-06-26,
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,
TRL,00000009                                       


Input2:
Code:
13000001  CB2012-10-22
13000002  CB2012-10-22
13000007  CB2012-10-22
13000010  CB2012-10-22
14000010  CB2012-10-22
13000011  CB2012-10-22


Output1:
Code:
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,
DTL,2012-06-23,00:00:0000,000011  ,C,N,2012-06-26,


Output2:
Code:
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,


Many thanks for your help.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue Oct 30, 2012 8:54 pm    Post subject: Reply to: Matching two files, matched and unmatched
Reply with quote

No problem. I still think you should look at the ? and the SAVE.
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Tue Oct 30, 2012 9:46 pm    Post subject:
Reply with quote

Code:
//SYSIN    DD *                                         
  JOINKEYS FILE=F1,FIELDS=(27,8,A)                       
  JOINKEYS FILE=F2,FIELDS=(3,8,A)                       
  JOIN UNPAIRED,F1                                       
  REFORMAT FIELDS=(F1:1,60,?)                           
  SORT FIELDS=COPY                                       
  OUTFIL FILES=01,INCLUDE=(61,1,CH,NE,X'40'),BUILD=(1,60)
  OUTFIL FILES=02,SAVE,BUILD=(1,60)                     
/*                                                       
//JNF1CNTL DD *                                         
  INCLUDE COND=(38,1,CH,EQ,C'R')                         
/*                                                       
//JNF2CNTL DD *                                         
  INCLUDE COND=(1,2,CH,EQ,C'14')                         
/*                                                       


Code:
REFORMAT FIELDS=(F1:1,60,?)
does not work. I am getting Empty file in SAVE.

Do I need to modify INCLUDE as you said Indicator can have: 'B', '1' or '2'.
Code:
INCLUDE=(61,1,CH,NE,X'40')
to
Code:
INCLUDE=(61,1,CH,EQ,'B')
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Tue Oct 30, 2012 10:01 pm    Post subject: Reply to: Matching two files, matched and unmatched
Reply with quote

Yep. The indicator in your case will be B for on both files or 1 for a record on File 1 only (you are not interested in File 2 mismatches).

So, change the INCLUDE and it should all be there.
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Wed Oct 31, 2012 2:51 pm    Post subject:
Reply with quote

I am getting this error:

Code:
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 09:17 ON WED OC
            JOINKEYS FILE=F1,FIELDS=(27,8,A)                                   
            JOINKEYS FILE=F2,FIELDS=(3,8,A)                                     
            JOIN UNPAIRED,F1                                                   
            REFORMAT FIELDS=(F1:1,60,?)                                         
            SORT FIELDS=COPY                                                   
            OUTFIL FILES=01,INCLUDE=(61,1,CH,EQ,'B'),BUILD=(1,60)               
                                                £                               
ICE113A E COMPARISON FIELD ERROR                                               
            OUTFIL FILES=02,INCLUDE=(61,1,CH,EQ,'1'),BUILD=(1,60)               
                                                £                               
ICE113A E COMPARISON FIELD ERROR                                               


I have tried without quotes as well. Is format of indictor is other than CH?

Thanks.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 31, 2012 2:56 pm    Post subject:
Reply with quote

FM,

CH,EQ,'B'
should be
CH,EQ,C'B'
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Wed Oct 31, 2012 2:57 pm    Post subject: Reply to: Matching two files, matched and unmatched
Reply with quote

You specify a character literal by prefixing with C and bounding by single quotation marks:

Code:
C'B'


You already saw a "hex" literal (X'40') which you could also have specified as C' '.
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 79
Location: UK

PostPosted: Wed Oct 31, 2012 3:19 pm    Post subject:
Reply with quote

Many thanks, and apology for not resolving my own.
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm


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