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
 

 

Compare 2 files and select matching records from 1 file

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

New User


Joined: 14 Apr 2009
Posts: 11
Location: Sacramento, CA

PostPosted: Fri Apr 17, 2009 9:35 pm    Post subject: Compare 2 files and select matching records from 1 file
Reply with quote

I'm confused with using SELECT of SPLICE for this requirement.

I need to extract records from FILE1 where a field from FILE1 matches a field in FILE2 and another field in FILE2 equals a specific value.

IN FILE1 (Variable Blocked), will have dups of field

xxxxxxxxx9001xxxxx....
xxxxxxxxx9002xxxxx....
xxxxxxxxx9002xxxxx....
xxxxxxxxx9003xxxxx....
xxxxxxxxx9001xxxxx....
xxxxxxxxx9003xxxxx....

IN FILE2 - (Fixed Blocked), will not have dups of field

9001P
9002F
9003P

Requirement: Write to output if field matches from FILE1 to FILE2 and FILE2 has a 'P' in position 4.

OUTPUT should look like this, keep original order:

xxxxxxxxx9001xxxxx....
xxxxxxxxx9003xxxxx....
xxxxxxxxx9001xxxxx....
xxxxxxxxx9003xxxxx....

Thanks in advance for helping.
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri Apr 17, 2009 10:23 pm    Post subject: Re: Compare 2 files and select matching records from 1 file
Reply with quote

kellaurin wrote:
I'm confused with using SELECT of SPLICE for this requirement.
What have you tried so far?
Back to top
View user's profile Send private message
kellaurin

New User


Joined: 14 Apr 2009
Posts: 11
Location: Sacramento, CA

PostPosted: Fri Apr 17, 2009 10:39 pm    Post subject:
Reply with quote

I'm progressing into the final solution, since I don't understand all of the syntax of the SELECT and SPLICE. I also changed both files to VB since I couldn't get past the length error problem.

I have used SELECT like this, this only gives me one of the matched records.

//TOOLIN DD *

COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(OUT) ON(14,4,CH) FIRSTDUP
/*
//CTL1CNTL DD *
INREC BUILD=(1:1,4,5:C'1',6:5)
/*
//CTL2CNTL DD *
INREC BUILD=(1:1,4,5:C'2',14:5,5)
/*
//CTL3CNTL DD
OUTFIL FNAMES=OUT,INCLUDE=(5,1,CH,EQ,C'1'),
BUILD=(1,4,5:6)
//*
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 17, 2009 10:45 pm    Post subject:
Reply with quote

kellaurin,

You need to use SPLICE, not SELECT.

What is the LRECL of file1? What is the LRECL of file2?

Can FILE1 have a key (e.g. 9004) that does not appear in FILE2? If so, you wouldn't want that record in the output file - right?
Back to top
View user's profile Send private message
kellaurin

New User


Joined: 14 Apr 2009
Posts: 11
Location: Sacramento, CA

PostPosted: Fri Apr 17, 2009 10:50 pm    Post subject:
Reply with quote

Frank,

LRECL of FILE1 is VB 4712
LRECL of FILE2 is FB 5

Your statement is correct, FILE 1 will have records with keys that are not in FILE 2 and those records are not wanted in the output file.

Thanks
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 17, 2009 11:07 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/4712)
//IN2 DD DSN=...  input file2 (FB/5)
//***> Use LRECL=4722 AND MOD FOR T1
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=4722
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  ouput file (VB/4712)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(24,4,CH) WITHALL VLENOVLY -
  WITH(5,1) WITH(7,4716) USING(CTL3)
SORT FROM(T2) TO(OUT) USING(CTL4)
/*
//CTL1CNTL DD *
  INCLUDE COND=(5,1,CH,EQ,C'P')
  OUTFIL FNAMES=T1,FTOV,BUILD=(C'BB',20:1,4)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,5:C'VV',7:SEQNUM,8,ZD,15:5)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=T2,INCLUDE=(5,2,CH,EQ,C'VB')
/*
//CTL4CNTL DD *
  SORT FIELDS=(7,8,ZD,A)
  OUTREC BUILD=(1,4,5:15)
/*
Back to top
View user's profile Send private message
kellaurin

New User


Joined: 14 Apr 2009
Posts: 11
Location: Sacramento, CA

PostPosted: Sat Apr 18, 2009 12:07 am    Post subject:
Reply with quote

Thanks Frank for taking the time to help. I think I have everything the same but the output file is empty. In fact I saved the temp files and T1 only has VV records not BB records. Here is what I have:

IN1 (Sample Records, not all records) VB 4712
rrrræ99N9104600426501041
rrrræ99N9104600426502041
m ...41N9095395300101071
m ...41N9095395300201071
°.äÉ.19P9104600504701051
°.äÉ.19P9104600505601051
°.äÉ.19P9104600505602051

IN2 (All Records) FB 5
9088P
9092P
9093P
9095P
9097P
9099P
9100P
9102P
9103P
9104F

T1 (not all records) VB 4722
VV00000001rrrræ99N9104600426501041
VV00000002rrrræ99N9104600426502041
VV00000003m ...41N9095395300101071
VV00000004m ...41N9095395300201071
VV00000005°.äÉ.19P9104600504701051
VV00000006°.äÉ.19P9104600505601051
VV00000007°.äÉ.19P9104600505602051

T2(empty)

OUT(empty)

TOOLIN
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)

COPY FROM(IN1) TO(T1) USING(CTL2)

SPLICE FROM(T1) TO(T2) ON(24,4,CH) WITHALL VLENOVLY -
WITH(5,1) WITH(7,4716) USING(CTL3)

SORT FROM(T2) TO(OUT) USING(CTL4)

/*
//CTL1CNTL DD *
INCLUDE COND=(5,1,CH,EQ,C'P')
OUTFIL FNAMES=T1,FTOV,BUILD=(C'BB',20:1,4)
/*
//CTL2CNTL DD *
INREC BUILD=(1,4,5:C'VV',7:SEQNUM,8,ZD,15:5)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=T2,INCLUDE=(5,2,CH,EQ,C'VB')
/*
//CTL4CNTL DD *
SORT FIELDS=(7,8,ZD,A)
OUTREC BUILD=(1,4,5:15)
/*
Back to top
View user's profile Send private message
kellaurin

New User


Joined: 14 Apr 2009
Posts: 11
Location: Sacramento, CA

PostPosted: Sat Apr 18, 2009 12:47 am    Post subject:
Reply with quote

Looking in T1 there are no BB or VB recs. The only records that got written to T1 are records from IN1, not IN2.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Apr 18, 2009 1:03 am    Post subject:
Reply with quote

Kellaurin,

That would happen if you didn't have MOD for T1. Did you miss this in my post?

Code:

//***> Use LRECL=4722 AND MOD FOR T1
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=4722


If you do have MOD for T1, then show me your complete JCL, TOOLMSG and DFSMSG output.
Back to top
View user's profile Send private message
kellaurin

New User


Joined: 14 Apr 2009
Posts: 11
Location: Sacramento, CA

PostPosted: Sat Apr 18, 2009 2:18 am    Post subject:
Reply with quote

Frank,
I found the problem. My REAL IN1 file only has 8 bytes before the key and I had shown 9 bytes in my original sample. This means that when I ran with the REAL data the key from IN1 was in position 19 and the key from IN2 was in position 20, which is why you had ON(24,4,CH).

I have modified it to align the keys in T1 and it now works perfectly.

Thanks again for your help. I am trying to convince others in our shop to use SORT or ICETOOL instead of coding COBOL programs, but I need to be able to solve some problems like this one by myself.

Thanks,
Kelly
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Apr 18, 2009 3:36 am    Post subject:
Reply with quote

Ok, good. I'm glad you figured it out.
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm


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