Posted: Mon Mar 15, 2004 8:54 am Post subject: Sort to retrieve certain records - DFOSRT
Hello Gurus
Another slightly different requirement for DFSORT:
1. File A. Sort column (also the key) starts in position 11. Length 11. File length 477.
2. File B. Sort column (also the key) starts in position 1. Length 11. File length 11.
Requirement:
Output file should contain record from FileA for those keys present in both input files.
Note: I have seen Kolusu/Frank's sort that creates 3 files (1. Records unique to file A 2. Records unique to file B 3. Intersection of fileA and FileB). Here the requirements are different:
1. Sort columns are not same
2. File lengths are not same.
1. No duplicates in either files.
2. File A - 22,000 records. File B - 6000 records (around)
3. RECFM is fixed block.
4. I have already written a PL1 for this (sorted file logic). So Eazytieve is not necessary. Thanks anyway. I was curious how we go about this with DFSORT.
Joined: 26 Nov 2002 Posts: 12383 Topics: 75 Location: San Jose
Posted: Mon Mar 15, 2004 9:55 am Post subject:
Relaxing,
The following DFSORT/ICETOOL job will give you desired results. If you have syncsort, change the pgm name to synctool.
A brief explanation of the Job. We first take file B and pad it with spaces to length of file a and also move the key from pos1 to pos 11. By doing so we can concatenate the 2 files and extract the keys we need. when we concatenate the files after reformating any key present file B will be a duplicate in the concatenated file. so using select operator we select the first dup which is what you want.Make sure to have file A as the first file in the concatenation. Select operator has EQUALS as default , so you will get the first duplicate record which is from from file A
Code:
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=YOUR FILE B,
// DISP=SHR
//CON DD DSN=YOUR FILE A
// DISP=SHR
// DD DSN=*.T1,DISP=(OLD,PASS),VOL=REF=*.T1
//T1 DD DSN=&T1,SPACE=(CYL,(X,Y),RLSE),DISP=(,PASS)
//OUT DD DSN=YOUR OUTPUT FILE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=PROD,
// SPACE=(CYL,(X,Y),RLSE)
//TOOLIN DD *
COPY FROM(IN1) USING(CTL1)
SELECT FROM FROM(CON) TO(OUT) ON(11,11,CH) FIRSTDUP
//CTL1CNTL DD *
OUTFIL FNAMES=T1,OUTREC=(10X, $ PAD 10 SPACES
1,11, $ FILE B KEY IN POS11
477:X) $ PAD SPACES TO LENGTH OF FILE A
/*
Joined: 26 Nov 2002 Posts: 12383 Topics: 75 Location: San Jose
Posted: Tue Mar 16, 2004 3:50 am Post subject:
Ravi,
Quote:
Now I got the above solution. Assume that If file A have duplicates ! and file B is just unique records.. then how will be the approach? Is it same or will it be different.
well if you have duplicates the above solution is not going to work. You will probably need SPLICE operator to get the desired results.Frank has provided a solution using SPLICE operator. My shop has syncsort and it does not support SPLICE.
for you 2nd question .. is there any problem if max no: records in file B is large? And if yes what could be the limit?
It does not matter how big the second file is. If the no: of records are less then , I thought of an alternate solution. let us assume that you have few records (say <500) then we can generate INCLUDE statements for these records dynamically and extract records from the file A. In the above link take a look at the solution provided by me by generating dynamic control cards.
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
