Hi,
Job has 2 steps:
1) Get matching unique key-values from File1 and File2.
File 1 has non-dup records and file2 can have duplicates. One to many records.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
1) Get matching unique key-values from File1 and File2.
It's not clear what this means. I can't tell which field you are matching on, sorting on, eliminating duplicates on, etc. What are the "rules" you're using to get from input to output for the first step? Please show which output values came from which input values (since the first field from the file2 values is not shown in the output values, I can't tell which input values you kept or why) and explain why those input values were kept.
Thanks Skolusu.
This worked fine. I understand we need to make these 2 files of equal length. Is there is any significance to have 5 spaces after 16th position or its just a random one?
For my step 2, Can I update KSDS using similar technique?
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
<Note: I posted this before I saw Kolusu's post>
Well, you really didn't explain what you wanted to do, but I played around with your job and figured it out. You want to match file1 and file2 on the first field, and for the resulting file2 records keep unique values for the second field in file2. The intermediate output you showed should have the AAAAA record, but you didn't show that record which threw me off until I figured it should be there.
A few problems with your job:
You don't have to SORT each file - you can COPY them and SPLICE will sort them.
You're putting an id in 16-17 and trying to INCLUDE on an id of '12', but you use WITH(1,15) so you'll never get an id of '12'. That's why you're not getting any output records. You should be using WITH(1,16) and checking for an id of '21'.
Your CTL4CNTL statements don't actually eliminate duplicates or add the 'Y'.
Here's a DFSORT/ICETOOL job that I think will do what you want.
matched records
When (1301,2,CH,EQ,C'12') then Y in 220th position.
Other records from KSDSfile as is when (1301,2,ch,eq,c'11') (Assuming 11 is for KSDS file)
Hope I am making clear. Otherwise please let me know. Appreciate your help as always.
Thanks,
Viji
where p is the starting position of the idenfier and id is the identifier (21 in my job) you want the 'Y' for.
Both Kolusu and I used INCLUDE to only get the matched records based on your original job. But it appears you also want the unmatched records.
Quote:
My final step is to update KSDS file instead of writing into a VSAM file.
Huh? the VSAM output file (//OUT) is the KSDS file. You can update the KSDS file this way as long as the keys defined for the KSDS file are in the correct binary order (which I assume they are).
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Well, I think I understand what you want so I'll give it a shot. Some caveats:
I wasn't sure if you wanted OUT1 as a separate file so I wrote it as one. If you don't need OUT1 as a separate file, you can remove the //OUT1 DD and the OUTFIL FNAMES=OUT1 statement from CTL4CNTL.
I also wasn't sure if you wanted to write the final output to the original VSAM input data set so I didn't. If you want to do that, you can use TO(IN3) in the second SPLICE and FNAMES=IN3 in CTL6CNTL, and remove the //OUT DD. However, if you use the same file (IN3) for input and output that way and something goes wrong with the job (e.g. I/O error), you could lose that file (so you should have a backup).
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/5)
//IN2 DD DSN=.... input file2 (FB/15)
//IN3 DD DSN=... input file3 (VSAM KSDS F/1300)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T3 DD DSN=&&T3,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//* Remove //OUT1 if you don't need OUT1 as a separate file
//OUT1 DD DSN=... output file from step1 (FB/10)
//* Remove //OUT if you want to use IN3 for the final output
//OUT DD DSN=... output file from step2 (VSAM KSDS F/1300)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,5,CH) -
WITHALL WITH(1,16) USING(CTL3)
SELECT FROM(T2) TO(T3) ON(1,10,CH) FIRST USING(CTL4)
COPY FROM(IN3) TO(T3) USING(CTL5)
* Change TO(OUT) to TO(IN3) if you want to use IN3 for the final output
SPLICE FROM(T3) TO(OUT) ON(1,10,CH) KEEPNODUPS -
WITH(1,1301) USING(CTL6)
/*
//CTL1CNTL DD *
INREC OVERLAY=(16:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(16:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=T2,INCLUDE=(16,2,CH,EQ,C'21'),
BUILD=(1:6,10)
/*
//CTL4CNTL DD *
OUTFIL FNAMES=T3,OVERLAY=(1301:C'BB')
* Remove next OUTFIL if you don't need OUT1 as
* a separate file
OUTFIL FNAMES=OUT1
/*
//CTL5CNTL DD *
INREC OVERLAY=(1301:C'VV')
//CTL6CNTL DD *
* Change FNAMES=OUT to FNAMES=IN3 if you want
* to use IN3 for the final output
OUTFIL FNAMES=OUT,INCLUDE=(1301,2,SS,EQ,C'VB,VV'),
IFOUTLEN=1300,
IFTHEN=(WHEN=(1301,2,CH,EQ,C'VB'),OVERLAY=(220:C'Y'))
/*