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
 

 

How to get matched records from three input files.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: How to get matched records from three input files.
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    Post subject:
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 Moderator


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

PostPosted: Fri Apr 27, 2007 7:37 am    Post subject:
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    Post subject:
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: 3158
Location: Tucson AZ

PostPosted: Fri Apr 27, 2007 5:23 pm    Post subject:
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 Moderator


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

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

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

http://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:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
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 How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts Need input in Scheduling Benchwarmer CA Products 5 Tue Jun 06, 2017 1:44 am
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm


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