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 

Capture matching records from 2 vb files

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


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Fri Jul 20, 2007 4:32 pm    Post subject: Capture matching records from 2 vb files Reply with quote
I have two files from which i need only the common records.

Code:

//STEP01   EXEC PGM=SORT                       
//SORTLIB   DD DSN=SYS1.SORTLIB,DISP=SHR         
//SYSPRINT DD SYSOUT=*                         
//SYSUDUMP DD SYSOUT=*                         
//SYSOUT     DD SYSOUT=*                         
//SORTIN    DD DSN=AV.E100.IIRS.CNREC.A, 
//              DISP=SHR                         
//              DD DSN=AV.E40011A.EXTRGS.CNEX, 
//              DISP=SHR                         
//SORTOUT  DD DUMMY                             
//SORTXSUM DD DSN=AV.E400.EXTRGS.CNEX.A,   
//            DISP=(NEW,CATLG,DELETE),         
//            SPACE=(CYL,(10,75),RLSE),         
//            RECFM=VB,LRECL=2004               
//SYSIN    DD *                                 
 SORT FIELDS=(73,13,CH,A)                       
 SUM FIELDS=NONE,XSUM                           
/*                                             



Both input files are VB seq files with lrecl = 2004.

AV.E100.IIRS.CNREC.A
Code:

214A010012405214A010012                01
31472187658243147218765                02
31472187658243147218765                03
31472187658243147218765                04
31472187658243147218765                05
31472187658243147218765                06
31472187658243147218765                07
31472187658243147218765                08
31472187658243147218765                09


AV.E40011A.EXTRGS.CNEX

Code:

...214A010012405214A010012405003214A010012405
...210A270000402210A270000402003210A270000402
...214A270001928214A270001928003214A270001928
...214A270003046214A270003046003214A270003046
...214A270005333214A270005333003214A270005333
...214A270007074214A270007074003214A270007074
...214A270010634214A270010634003214A270010634


Both have common fields starting at (69+4) 73 location. with the above SORT Job
Code:

...214A010012405214A010012405003214A010012405
31472187658243147218765                03
31472187658243147218765                04
31472187658243147218765                05
31472187658243147218765                06
31472187658243147218765                07
31472187658243147218765                08
31472187658243147218765                09


Here I don't know why the second record is missing from the first input file.

Actually, the desired output I need is just the first record.

...214A010012405214A010012405003214A010012405

All other matching records in the file2 should be filtered off.
I appreciate any help. thanks,.
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Fri Jul 20, 2007 10:18 pm    Post subject: Reply with quote
Vak255,

1. I just love your Catchy 1 word subject "SORT -", but I do not understand what you are trying to accomplish from that. Wink

2. Sort XSUM will not help you out to get matching records. You either need to use SPLICE with ALLDUPS or Use Inrec to append a char '01' at the end of layout and SORT On key, Sum on '01' and use OUTFIL INCLUDE to write only records whose '01' sum becomes '02'.

Better Check this link
http://www.mvsforums.com/helpboards/viewtopic.php?t=5399

Hope this helps,

Thanks,
Phantom
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Sat Jul 21, 2007 12:54 am    Post subject: Reply with quote
Phantom, sorry for the confusion and that 1 word subject line. i thought of updating it later but forgot to.....if i could able to edit it ..i would have corrected it.

I have some accounts in AV.E100.IIRS.CNREC.A, and some in AV.E40011A.EXTRGS.CNEX. I like to pick only the accounts from AV.E40011A.EXTRGS.CNEX which are there in AV.E100.IIRS.CNREC.A.

i.e here the only matching acct is 214A010012405 so i want only this record in the output file.
Quote:

...214A010012405214A010012405003214A010012405


Hope i am clear now.
Thanks for the links.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Jul 23, 2007 9:31 am    Post subject: Reply with quote
Quote:

I have some accounts in AV.E100.IIRS.CNREC.A, and some in AV.E40011A.EXTRGS.CNEX. I like to pick only the accounts from AV.E40011A.EXTRGS.CNEX which are there in AV.E100.IIRS.CNREC.A.


vak255,

Do you have any duplicates in either of the files? What version of syncsort are you running ? ( You can find this in the first line of your sysout)

If you are running syncsort version 1.2 and higher then you can use JOIN feature to capture the matching records.

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


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Mon Jul 23, 2007 10:10 am    Post subject: Reply with quote
Vak255,

Try this sample JCL - (you need to customize to your requirements - field positions and LRECL). This will work with older versions of syncsort that supports SPLICE (v 1.1 and above).

In this example, my input file is of LRECL=500 and RECFM=FB. My Key is entire Record (1,500) itself. Here I append a hardcoded literal 'DD', 'UU', '11', '22' at the end of the record (501,2). You need to manipulate these field positions to meet your requirements.

Code:

//S3 EXEC PGM=ICETOOL                                         
//TOOLMSG DD SYSOUT=*                                         
//DFSMSG  DD SYSOUT=*                                         
//IN1     DD DSN=my.input.file1,DISP=SHR
//IN2     DD DSN=my.input.file2,DISP=SHR
//F1      DD DSN=&&F1,UNIT=SYSDA,SPACE=(CYL,(5,5)),           
//           DISP=(MOD,PASS)                                   
//T1      DD DSN=&&TX,UNIT=SYSDA,SPACE=(CYL,(5,5)),           
//           DISP=(MOD,PASS)                                   
//OUT12   DD SYSOUT=* NAMES IN FILE1 AND FILE2                 
//OUT1    DD SYSOUT=* NAMES IN FILE1 ONLY                     
//OUT2    DD SYSOUT=* NAMES IN FILE2 ONLY                     
//TOOLIN  DD *                                                 
  SELECT FROM(IN1) TO(F1)   ON(1,500,CH) FIRST                 
  SELECT FROM(IN2) TO(F1)   ON(1,500,CH) FIRST                 
  SELECT FROM(F1)  TO(T1)   ON(1,500,CH) FIRSTDUP USING(CTL1) 
  SELECT FROM(F1)  TO(T1)   ON(1,500,CH) NODUPS USING(CTL2)   
  COPY   FROM(IN1) TO(T1)   USING(CTL3)                       
  SPLICE FROM(T1)  TO(OUT1) ON(1,500,CH) -                     
         WITHALL WITH(1,501) USING(CTL5)                       
/*                                                             
//CTL1CNTL DD *                                                 
  OUTFIL FNAMES=T1,OUTREC=(1,500,501:C'DD')                     
/*                                                             
//CTL2CNTL DD *                                                 
  OUTFIL FNAMES=T1,OUTREC=(1,500,501:C'UU')                     
/*                                                             
//CTL3CNTL DD *                                                 
  OUTFIL FNAMES=T1,OUTREC=(1,500,501:C'11')                     
/*                                                             
//CTL4CNTL DD *                                                 
  OUTFIL FNAMES=T1,OUTREC=(1,500,501:C'22')                     
/*                                                             
//CTL5CNTL DD *                                                 
  OUTFIL FNAMES=OUT1,INCLUDE=(501,2,CH,EQ,C'1U'),               
  OUTREC=(1,500)                                               
  OUTFIL FNAMES=OUT2,INCLUDE=(501,2,CH,EQ,C'2U'),               
  OUTREC=(1,500)                                               
  OUTFIL FNAMES=OUT12,SAVE,                                     
  OUTREC=(1,500)                                               
/*         


Thanks,
Phantom
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Mon Jul 23, 2007 10:51 am    Post subject: Reply with quote
Kolusu, There are no duplicates in the input file. we use SYNCTOOL RELEASE 1.5.3.

thanks Phantom, i will try using yours.
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Mon Jul 23, 2007 10:57 am    Post subject: Reply with quote
Vak255,

The version of Syncsort (PGM=SORT) and Synctool will be different. The correct version of sort can be found only in PGM=SORT. Please check the sysout and get back to us on the version.

Thanks,
Phantom
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Mon Jul 23, 2007 11:18 am    Post subject: Reply with quote
I could not get the SORT version number.

I have set up the Job, now its working fine. All I did is using SORT I removed duplicate acct numbers from input file AV.E100.IIRS.CNREC.A.

In another step, I used the STEP01 job which I mentioned in my first post. its working now.


Thanks Phantom and Kolusu.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Jul 23, 2007 11:25 am    Post subject: Reply with quote
Quote:

I could not get the SORT version number.

vak255,

If you are running a synctool job then check the first line in your DFSMSG sysout. It shows you the version of sort you are running

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


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Mon Jul 23, 2007 11:47 am    Post subject: Reply with quote
The first line in sysout " SYNCSORT FOR Z/OS 1.2.3.0R U.S. PATENTS: 4210961, 5117495"

DFSMSG - i don't find this.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Jul 23, 2007 11:54 am    Post subject: Reply with quote
Quote:

The first line in sysout " SYNCSORT FOR Z/OS 1.2.3.0R U.S. PATENTS: 4210961, 5117495"

vak255,

Your syncsort version does support JOIN feature. It can deal with duplicates and can be done in a single pass. Search the board for JOINKEYS and you will find numerous examples

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


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Mon Jul 23, 2007 1:20 pm    Post subject: Reply with quote
Vak255,

The following link details the JOIN feature in Syncsort

http://www.mvsforums.com/helpboards/viewtopic.php?p=22582#22582

Hope this helps,

Thanks,
Phantom
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Mon Jul 23, 2007 4:45 pm    Post subject: Reply with quote
Thanks a lot you good people.
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