IBM MAINFRAME HELP FORUMS for COBOL, JCL, CICS, DB2, IMS etc...

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Hi
I have two files with some fields that are common to them.
Is there a way of selecting only the records of one file that have the common fields equal to file 2?

If I have the 2 follwoing files:

FILE 1

aaaa 0123 XXXX 20080101 REC
bbbb 0132 YYYY 20080201 REC
cccc 0145 ZZZZ 20080301 REV
dddd 0143 VVVV 20080301 REX

File 2
aaaa 0123 213502415 20080101 REC
cccc 0145 565256456 20080301 REV

I want copy to the output file records 1 and 3 from file 1, because they have fields 1, 2 and 4 equal to those in file 2

Thanks

Frank Yaeger · Joined: 15 Feb 2005 Posts: 4415 Location: San Jose, CA

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed that your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes. I also assumed, as shown in your example, that there are no duplicate records within input file1 and no duplicate records within input file2.

Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN2 DD DSN=... input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=... input file1 (FB/80)
// DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
SELECT FROM(CON) TO(OUT) ON(1,9,CH) ON(16,8,CH) FIRSTDUP
/*
//CTL1CNTL DD *
INREC BUILD=(1,9,16:21,8,80:X)
/*

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Thanks Frank
I think that I might have duplicate records within one of the files but, I will try that.
We are no very familiar with ICETOOL. Can you give me the link to the manual, just to read it?

Thanks

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Sorry.
I just saw the links in the forum.

Frank Yaeger · Joined: 15 Feb 2005 Posts: 4415 Location: San Jose, CA

Quote: I think that I might have duplicate records within one of the files but, I will try that.

If you have duplicate records in one of the files, you will probably need a different DFSORT/ICETOOL solution. Show a better example (with duplicates) of the records in your input files and what you expect for output. Give the RECFM and LRECL of the input files. Give the starting position, length and format of each relevant field.

Quote: We are no very familiar with ICETOOL. Can you give me the link to the manual, just to read it?

DFSORT's ICETOOL is fully documented in Chapter 6 of "z/OS DFSORT Application Programming Guide" which you can access, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, from the link above.

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Quote: If you have duplicate records in one of the files, you will probably need a different DFSORT/ICETOOL solution. Show a better example (with duplicates) of the records in your input files and what you expect for output. Give the RECFM and LRECL of the input files. Give the starting position, length and format of each relevant field.

Input file 1 is FB and 535 in length.
Input file 2 is FB and 84 in length.

The matching fields for file 1 are, with displacement, length and type:
1,3,PD
50,9,PD
75,16,CH

and for file 2 :
18,4,CH
27,17,CH
45,16,CH

I think that, because the matching fields are of different types, they must be converted first, so the types are identical. Am I right?
And yes, I can have duplicate records on file 1 but, in that case I want all the matching records in the output, including the duplicated.

Frank Yaeger · Joined: 15 Feb 2005 Posts: 4415 Location: San Jose, CA

Quote: I think that, because the matching fields are of different types, they must be converted first, so the types are identical. Am I right?

Yes. You say that the first two fields are PD vs CH. PD is numeric. Are the CH fields actually ZD (numeric) or some other type of character values?

Please show a good example of the records in each input file (with duplicates where appropriate) and what you expect for output. Show the PD fields in hex or as P'd...d'. Explain the "rules" for getting from input to output.

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Yes the CH fields are numeric (ZD).
As I'm already home, I will get some concrete cases tomorrow as soon as I get to the office.
I took a look at the manual and, I really enjoyed it.
It can save you lots of time and effort in development.
Thanks

Frank Yaeger · Joined: 15 Feb 2005 Posts: 4415 Location: San Jose, CA

Quote: I took a look at the manual and, I really enjoyed it.
It can save you lots of time and effort in development.

Wow. You don't often hear somebody say they enjoyed an IBM manual. As the developer who wrote the DFSORT books and added many of the new functions over the years, I appreciate the kind words. :D

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Quote:
You don't often hear somebody say they enjoyed an IBM manual.
Yes, it's not very usual to year that. I see the reading of manuals as a way to get more knowledge on a particular matter. And if you find there some stuff that you really didn't know about and, additionally it saves you time and effort, it's great.

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Quote: Please show a good example of the records in each input file (with duplicates where appropriate) and what you expect for output.
I think this will illustrate what I need. The spaces between each field in file 1 are just for easy reading
Code: File 1
Fld1 Fld2 Fld3
018 04030010444444444 000000500 44444444 CCCDEDEC44444444
00C 315C001C000000000 00001000C 00000000 1243562300000000 Rec 1

018 04030010444444444 000000500 44444444 CCCDEDEC44444444
00C 315C001C000000000 00001000C 00000000 1243562300000000 Rec 2

018 04030010444444444 000003050 44444444 CCCDEDEC44444444
00C 315C001C000000000 00002000C 00000000 1243562300000000 Rec 3

016 04030010444444444 000000050 44444444 DDDCECEC44444444
01C 315C001C000000000 00000020C 00000000 3243532200000000 Rec 4

File 2
Fld1 Fld2 Fld3
00108 01230ADK3LF 00000000010050000 5862896 ABDLVOSC XXX Rec 1
00109 01230ADK3LF 00000000010050000 5862896 ABDLVOSC XXX Rec 2
00116 01230ADK3LF 00000000000002500 5862896 LKMCVCSB XXX Rec 3

I want records 1 and 2 from file 1, because all 3 fields match (Fld1, Fld2 and Fld3) with record 1 from file 2.
In record 3, Fld 2 doesn't match so, it won't pass. Record 4 should also be on the output file, because all 3 selection fields match those in record 3 from file 2.
I think that it may be difficult to do without programming.

Frank Yaeger · Joined: 15 Feb 2005 Posts: 4415 Location: San Jose, CA

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 file 1 (FB/535)
//IN2 DD DSN=... input file 2 (FB/84)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=... output file (FB/535)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,3,PD) ON(50,9,PD) ON(75,16,CH) -
WITHALL WITH(1,536) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1:18,4,ZD,TO=PD,LENGTH=3,
50:27,17,ZD,TO=PD,LENGTH=9,
75:45,16,536:C'BB')
/*
//CTL2CNTL DD *
INREC OVERLAY=(536:C'VV')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,INCLUDE=(536,2,CH,EQ,C'VB'),
BUILD=(1,535)
/*