IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to get matched records from three input files.


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
psmadhusudhan

New User


Joined: 26 Apr 2007
Posts: 1
Location: India

PostPosted: Thu Apr 26, 2007 2:36 pm
Reply with quote

I have first input file(policy file) as
Code:
POLICY NUMBER           |DATE1    |DATE2   |POLKEY
----+----1----+----2----+----3----+----4----+----5----+
***************************** Top of Data ***********
TRA010005001             17.01.200716.01.2007dasd123
TRA010005002             20.02.200719.02.2008da12413
TRA010005003             21.02.200720.02.2008gfsdg43
TRA010005005             16.01.200715.01.2007sdfsdf5
TRA010005008             21.02.200720.02.2008sdfsdf4
**************************** Bottom of Data *********

file length is 55 with Pol num:25, DATE1: 10, DATE2:10, Polkey:10

second input file(cover file) is
Code:
POLICY NUMBER           |DATE1    |DATE2   |COVKEY
----+----1----+----2----+----3----+----4----+----5----+
***************************** Top of Data *************
TRA010005001             17.01.200716.01.2007dasd123
TRA010005002             20.02.200719.02.2008da12413
TRA010005004             15.01.200715.01.2007sdfsdf6
TRA010005006             21.02.200720.02.2008sdfsdf4
TRA010005008             21.02.200720.02.2008sdfsdf4
**************************** Bottom of Data *********

file length is 55 with Pol num:25, DATE1: 10, DATE2:10, Cltkey:10

third input file(client file) is
Code:
POLICY NUMBER           |DATE1    |DATE2   |CLTKEY
----+----1----+----2----+----3----+----4----+----5----+
***************************** Top of Data *************
TRA010005001             17.01.200716.01.2007dasd123
TRA010005003             21.02.200720.02.2008gfsdg43
TRA010005004             15.01.200715.01.2007sdfsdf6
TRA010005007             21.02.200720.02.2008sdfsdf4
TRA010005008             21.02.200720.02.2008sdfsdf4
**************************** Bottom of Data ***********

file length is 55 with Pol num:25, DATE1: 10, DATE2:10, Covkey:10

I want to have my first output file as following which has matching policy number, date1 and date2 in all three files.

OUTPUTFILE1:
Code:
POLICY NUMBER              |DATE1    |DATE2  |POLKEY   |COVKEY   |CLTKEY
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+
***************************** Top of Data *********************************
TRA010005001             17.01.200716.01.2007dasd123   dasd123   dasd123
TRA010005008             21.02.200720.02.2008sdfsdf4   sdfsdf4   sdfsdf4
**************************** Bottom of Data *******************************

Second output file should contain records of Policy file which doesn?t have matching values of policy number, date1 and date2 either in Cover file or client file or both

OUTPUFILE2:
Code:
POLICY NUMBER            |DATE1    |DATE2   |POLKEY
----+----1----+----2----+----3----+----4----+----5----+
***************************** Top of Data ***********
TRA010005002             20.02.200719.02.2008da12413
TRA010005003             21.02.200720.02.2008gfsdg43
TRA010005005             16.01.200715.01.2007sdfsdf5
**************************** Bottom of Data *********

Third output file should contain records of Cover file which doesn?t have matching values of policy number, date1 and date2 either in Policy file or client file or both

OUTPUFILE3:
Code:
POLICY NUMBER            |DATE1    |DATE2   |COVKEY
----+----1----+----2----+----3----+----4----+----5----+
***************************** Top of Data *************
TRA010005002             20.02.200719.02.2008da12413
TRA010005004             15.01.200715.01.2007sdfsdf6
TRA010005006             21.02.200720.02.2008sdfsdf4
**************************** Bottom of Data *********

Fourth output file should contain records of Client file which doesn?t have matching values of policy number, date1 and date2 either in Policy file or Cover file or both

OUTPUFILE4:
Code:
POLICY NUMBER           |DATE1    |DATE2   |CLTKEY
----+----1----+----2----+----3----+----4----+----5----+
***************************** Top of Data *************
TRA010005003             21.02.200720.02.2008gfsdg43
TRA010005004             15.01.200715.01.2007sdfsdf6
TRA010005007             21.02.200720.02.2008sdfsdf4
**************************** Bottom of Data ***********

I want to have the above submitted process done in Icetool using Splice, not easytrieve
Back to top
View user's profile Send private message
gsbrar16

New User


Joined: 21 Dec 2006
Posts: 27
Location: india

PostPosted: Thu Apr 26, 2007 6:12 pm
Reply with quote

Hi Madhu,

This is a nice case.
To solve this You can try to use ICETOOL. First merge all 3 files using policy number, date1 and date2 as key.

To generate first file use OCCURS operator of ICETOOL with option HIGHER(2) on merged file.

For second file, use OCCURS operator with NODUPS option on merged file.

For third and fourth file generation, i dont have a idea right now. I will try and if got any idea, will post a reply again.

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

DFSORT Developer


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

PostPosted: Fri Apr 27, 2007 7:37 am
Reply with quote

Madhu,

I believe this DFSORT/ICETOOL job will do what you asked for based on the example you showed. If there are other variations you didn't show, then the job may have to be tweaked a bit to cover those.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  policy input file (FB/55)
//IN2 DD DSN=...  cover input file (FB/55)
//IN3 DD DSN=...  client input file (FB/55)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD DSN=...  output file1 (FB/75)
//OUT2 DD DSN=...  output file2 (FB/55)
//OUT3 DD DSN=...  output file3 (FB/55)
//OUT4 DD DSN=...  output file4 (FB/55)
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN3) TO(T1) USING(CTL3)
SPLICE FROM(T1) TO(OUT1) ON(1,45,CH) KEEPNODUPS -
  WITHEACH WITH(59,13) WITH(72,13) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,45,46:C' 1 ',46,10,
     59:C' 1 ',46,10,72:C' 1 ',46,10)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,45,46:C' 2 ',46,10,
     59:C' 2 ',46,10,72:C' 2 ',46,10)
/*
//CTL3CNTL DD *
  INREC BUILD=(1,45,46:C' 3 ',46,10,
     59:C' 3 ',46,10,72:C' 3 ',46,10)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT1,
    INCLUDE=(47,1,CH,EQ,C'1',AND,60,1,CH,EQ,C'2',AND,
      73,1,CH,EQ,C'3'),
    BUILD=(1,45,49,10,62,10,75,10)
  OUTFIL FNAMES=OUT2,
    INCLUDE=(47,1,CH,EQ,C'1',AND,(60,1,CH,NE,C'2',OR,
      73,1,CH,NE,C'3')),
    BUILD=(1,45,49,10)
  OUTFIL FNAMES=OUT3,
    INCLUDE=(47,1,CH,EQ,C'2',OR,(60,1,CH,EQ,C'2',AND,
      73,1,CH,NE,C'3')),
    IFTHEN=(WHEN=(47,1,CH,EQ,C'2'),BUILD=(1,45,49,10)),
    IFTHEN=(WHEN=NONE,BUILD=(1,45,62,10))
  OUTFIL FNAMES=OUT4,
    INCLUDE=(60,1,CH,EQ,C'3'),
    BUILD=(1,45,62,10)
/*
Back to top
View user's profile Send private message
gsbrar16

New User


Joined: 21 Dec 2006
Posts: 27
Location: india

PostPosted: Fri Apr 27, 2007 5:13 pm
Reply with quote

Simply great thinking from Frank.

Frank, can you please give me the link for some information on the functions of SPLICE operator with examples.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Fri Apr 27, 2007 5:23 pm
Reply with quote

gsbrar16 wrote:
can you please give me the link for some information on the functions of SPLICE operator with examples.
DFSORT/ICETOOL Reference Material
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Fri Apr 27, 2007 8:15 pm
Reply with quote

You can find complete information about the SPLICE operator of DFSORT's ICETOOL, with examples, at:

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

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, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
Search our Forums:

Back to Top