MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

How to get matched records from three input files.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Wed Apr 25, 2007 8:25 am    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
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed Apr 25, 2007 8:56 am    Post subject: Reply with quote
psmadhusudhan,

Do you have easytrieve in your shop? If don't have what sort product do you have ?

run the following job and show us the sysout

Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD *         
ABC                     
//SORTOUT  DD SYSOUT=*   
//SYSIN    DD *         
 SORT FIELDS=COPY       
/*


Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Thu Apr 26, 2007 12:07 am    Post subject: Reply with quote
Hi

I want to have the above submitted process done in Icetool using Splice, not easytrieve

Regarding the sysout of the job u had asked is as follows
Code:

BLOCKSET     COPY  TECHNIQUE SELECTED
VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES A
- CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 06:02 ON THU AP
 SORT FIELDS=COPY
RECORD TYPE IS F - DATA STARTS IN POSITION 1
C5-K90007 C6-K90007 C7-K90000 C8-K90007 E9-K90007 C9-BASE   E5-K18181
ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
SPLICE2@.STEP0100.        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
MAIN STORAGE = (MAX,6291456,6278238)
MAIN STORAGE ABOVE 16MB = (6200798,6200798)
OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y
OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ER
OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT
OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT
OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW
OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXIT
OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAM
OPTIONS: NULLOUT=RC0
BSAM ACCESS METHOD USED FOR SORTOUT
BSAM ACCESS METHOD USED FOR SORTIN
EF-K10929 F0-Q84357 E8-K19114
OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
INSERT 0, DELETE 0
RECORDS - IN: 1, OUT: 1
END OF DFSORT

_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
expat
Intermediate


Joined: 01 Mar 2007
Posts: 475
Topics: 9
Location: Welsh Wales
PostPosted: Thu Apr 26, 2007 6:15 am    Post subject: Reply with quote
psmadhusudhan

Quote:
I want to have the above submitted process done in Icetool using Splice, not easytrieve

I think that kolusu was trying to be helpful with the response given, and nowhere in your original post did you specify that this had to be achieved using SORT.

Perhaps it is my interpretation of your reply, but please show a little more gratitude to those that try to help you.

Maybe you will get the result you want from the other forum where you have posted this. But at least you did include your response to kolusu on the other forum, so hopefully no one will upset you again by trying to suggest an alternative solution.
_________________
If it's true that we are here to help others,
then what exactly are the others here for ?
Back to top
View user's profile Send private message
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Thu Apr 26, 2007 8:04 am    Post subject: Reply with quote
Dear expat

I am sorry that I forgot to ask in my question whether I want the solution using sort or not.

Regarding my reply to Kolosu, I was just straight, I think u had taken t in otherway. I am very happy that somebody is trying to help me. And I am very gratefull to allthose who will help me.

I didnt post this query in anyother topic. If possible can u try to give me
a solution.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
expat
Intermediate


Joined: 01 Mar 2007
Posts: 475
Topics: 9
Location: Welsh Wales
PostPosted: Thu Apr 26, 2007 8:37 am    Post subject: Reply with quote
From the output that you have posted, you have DFSORT installed which means that you can use ICETOOL.

I will try to give you some help, but I'm sure that Frank will come up with about half the amount of code that I've used.

First you need to reformat your records so that you can splice them, using the output above I see they are FB 80 records.

a) IN1 we will leave as is, as it has the blanks after the POLKEY
b) IN2 needs blanks to replace the 7 bytes at POS 46 and put the 7 bytes at POS 53
c) IN3 needs to put 14 blanks at POS 46 and the 7 bytes from POS 46 at POS 60

Code:

//SYSIN    DD *
 COPY FROM(IN1) TO(OUT) USING(CTL1)
 COPY FROM(IN1) TO(OUT) USING(CTL2)
 COPY FROM(IN1) TO(OUT) USING(CTL3)
 SPLICE FROM(OUT) TO(OUT2) ON(1,45,CH) WITHALL WITH(53,7) WITH(60,7)
 COPY FROM(OUT2) TO(OUT3) USING(CTL4) 
/*
//CTL1CNTL DD *
 OUTREC FIELDS=(1,80)
/*
//CTL2CNTL DD *
 OUTREC FIELDS=(1,45,7X,46,7)   
/*
//CTL3CNTL DD *
 OUTREC FIELDS=(1,45,14X,46,7)   
/*
//CTL4CNTL DD *
 OMIT COND=(45,7,CH,EQ,C'       ',OR,53,7,CH,EQ,C'       ',OR,60,7,CH,EQ,C'       ') 
/*


That should, hopefully give you your first output file.
_________________
If it's true that we are here to help others,
then what exactly are the others here for ?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Apr 26, 2007 1:18 pm    Post subject: Reply with quote
psmadhusudhan,

Finally got a chance to look at this, but your requirements and your output files does not match.

Output file 2 . you said
Quote:

Second output file should contain records of Policy file which doesn
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Thu Apr 26, 2007 9:05 pm    Post subject: Reply with quote
Madhu,

If I understand correctly what you want, then the output you show is consistent with what you said. Keys 2 and 3 of input file1 do not appear in all three files, so you want them in output file 2.

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)
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Fri Apr 27, 2007 1:09 am    Post subject: Reply with quote
Thank u very much Frank.
My problem is solved.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group