MVSFORUMS.com :: View topic - Capture matching records from 2 vb files

Capture matching records from 2 vb files

through

[^/[Print]\]

MVSFORUMS.com -> Utilities

#1: Capture matching records from 2 vb files Author: vak255,

Posted: Fri Jul 20, 2007 4:32 pm
—
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,.

#2: Author: Phantom, Location: The Blue Planet

Posted: Fri Jul 20, 2007 10:18 pm
—
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

#3: Author: vak255,

Posted: Sat Jul 21, 2007 12:54 am
—
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.

#4: Author: kolusu, Location: San Jose

Posted: Mon Jul 23, 2007 9:31 am
—

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

#5: Author: Phantom, Location: The Blue Planet

Posted: Mon Jul 23, 2007 10:10 am
—
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

#6: Author: vak255,

Posted: Mon Jul 23, 2007 10:51 am
—
Kolusu, There are no duplicates in the input file. we use SYNCTOOL RELEASE 1.5.3.

thanks Phantom, i will try using yours.

#7: Author: Phantom, Location: The Blue Planet

Posted: Mon Jul 23, 2007 10:57 am
—
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

#8: Author: vak255,

Posted: Mon Jul 23, 2007 11:18 am
—
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.

#9: Author: kolusu, Location: San Jose

Posted: Mon Jul 23, 2007 11:25 am
—

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

#10: Author: vak255,

Posted: Mon Jul 23, 2007 11:47 am
—
The first line in sysout " SYNCSORT FOR Z/OS 1.2.3.0R U.S. PATENTS: 4210961, 5117495"

DFSMSG - i don't find this.

#11: Author: kolusu, Location: San Jose

Posted: Mon Jul 23, 2007 11:54 am
—

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

#12: Author: Phantom, Location: The Blue Planet

Posted: Mon Jul 23, 2007 1:20 pm
—
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

#13: Author: vak255,

Posted: Mon Jul 23, 2007 4:45 pm
—
Thanks a lot you good people.

MVSFORUMS.com -> Utilities

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1