For each key, copy the 1st greater record than the key.



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

For each key, copy the 1st greater record than the key.

Postby anuragdtsi » Mon Feb 13, 2012 9:00 pm

Hi,
I am new to this forum and this is my 1st post and i need some urgent help...

I have 2 input files. 1st file has a few hundred records and the 2nd has around 5 million records.
Now, I have to compare the 2 files based on a 22 byte key, and for every key value in the 1st file, i have to read/write 1 record from the 2nd file which is just greater than the key.
Note: both files are sorted on the key, and the keys would never match.

Input file 1:

1. 2222222222222222C110000
2. 5555555555555555C110000
3. 6666666666666666C110000
4. 7777777700000000C110000
5. 7777777799999999C119999
6. 8888888888888888C110221
.
.
.
.



Input file 2:


1. 1111111111111111C110000
2. 3333333333333333C110000
3. 5000000000000000C110000
4. 5111111111111111C110000
5. 7000000000000000C110000
6. 7777777700000000C110000
7. 8000000000000000C119999
8. 9999999999999999C110221
.
.
.
.


OUTPUT FILE:

1. 3333333333333333C110000
2. 7000000000000000C110000
3. 7000000000000000C110000
4. 8000000000000000C119999
5. 8000000000000000C119999
6. 9999999999999999C110221
.
.
.

Plz note that we have to find the 1st record from the 2nd file which is just greater than the 1st files key. And also that the we cant hardcode the key values in the JCL since there can be hundreds of key vales.

Any kind of hint/help would be really appreciated.
anuragdtsi
 
Posts: 5
Joined: Mon Feb 13, 2012 8:10 pm
Has thanked: 0 time
Been thanked: 0 time

Re: For each key, copy the 1st greater record than the key.

Postby skolusu » Mon Feb 13, 2012 9:26 pm

anuragdtsi,

It is not clear as to how the comparison is done. If you are looking for keys greater than file1, Isn't record # 2. 3333333333333333C110000 greater than 1. 2222222222222222C110000? Why isn't that picked? The greater comparison has to have some guidelines to compare. Do you need to compare a range of records?
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: For each key, copy the 1st greater record than the key.

Postby anuragdtsi » Mon Feb 13, 2012 10:45 pm

If you'd have a clear look at the output file again, then u would see that it has picked record no 2 i.e 3333333333333333C110000 from the 2nd file.
The greater comparision for each record should be such that amongst several records which are greater than the key, we need to copy only the 1st record. And this process needs to be followed for each key. So for 'N' keys, we should copy a total of 'N' records from the 2nd file. And each record to be copied must be the 1st record greater than the corresponding file 1 record.
e.g: if u woul'd see
for the 1st record in input file
'2222222222222222C110000' the output record should be, '3333333333333333C110000' since it is the 1st record greater than it,
and likewise for the 2nd record '5555555555555555C110000' from file 1, the output should be '7000000000000000C110000' since this is the 1st record which would be greater than it in file 2.
anuragdtsi
 
Posts: 5
Joined: Mon Feb 13, 2012 8:10 pm
Has thanked: 0 time
Been thanked: 0 time

Re: For each key, copy the 1st greater record than the key.

Postby anuragdtsi » Mon Feb 13, 2012 10:48 pm

And we don't have to compare a range of records but instead are required to compare individual records, i.e all the records in file 1 need to be compared.
anuragdtsi
 
Posts: 5
Joined: Mon Feb 13, 2012 8:10 pm
Has thanked: 0 time
Been thanked: 0 time

Re: For each key, copy the 1st greater record than the key.

Postby skolusu » Mon Feb 13, 2012 11:57 pm

anuragdtsi,

I made a mistake of identifying the key.

1. what is the LRECL and RECFM of both input files?
2. Is there a flag to identify the records from each file?
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: For each key, copy the 1st greater record than the key.

Postby anuragdtsi » Tue Feb 14, 2012 12:09 am

It is a FB of LRECL 23.
No flag, but we can add if we want, and each key of 22 bytes is unique in both files.
anuragdtsi
 
Posts: 5
Joined: Mon Feb 13, 2012 8:10 pm
Has thanked: 0 time
Been thanked: 0 time

Re: For each key, copy the 1st greater record than the key.

Postby skolusu » Tue Feb 14, 2012 12:51 am

anuragdtsi ,

Your output file had dups for 7000000000000000C110000 and 8000000000000000C119999. I am assuming that they are typos.

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                     
//SORTOUT  DD DSN=&&HDR,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)         
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                                 
  OUTFIL BUILD=(23X),REMOVECC,NODETAIL,HEADER1=('$$$')             
//*                                                                 
//STEP0200 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD DSN=Your input file1,DISP=SHR                                   
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD DSN=Your input file2,DISP=SHR                                   
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(24:ID=1))
  SORT FIELDS=(1,22,CH,A),EQUALS                                   
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(24,1,ZD,EQ,2),                   
  PUSH=(25:24,1,1,22,SEQ=8))                                       
  OUTFIL BUILD=(1,23),                                             
  INCLUDE=(24,2,CH,EQ,C'12',AND,                                   
          (1,22,CH,GT,26,22,CH),AND,                               
          (48,8,ZD,EQ,2),AND,26,3,CH,NE,C'$$$')                     
//*


The output from above job is
3333333333333333C110000
7000000000000000C110000
8000000000000000C119999
9999999999999999C110221
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: For each key, copy the 1st greater record than the key.

Postby anuragdtsi » Tue Feb 14, 2012 9:48 am

Hi, Kolusu,
The output file had dups for 7000000000000000C110000 and 8000000000000000C119999 because they will be selected twice since 7000000000000000C110000 is the 1st greater reocrod for both records 2nd and 3rd from file 1, and 8000000000000000C119999 was picked twice(once for the 4th record and then again for the 5th record of the 1st file).
Actually the whole purpose of showing dups in the output was that to let u know that a single record can be copied in output multiple times, if it is the 1s greater record for more than just one input records from file 1.
And will this Jcl work with this dups cases too?
anuragdtsi
 
Posts: 5
Joined: Mon Feb 13, 2012 8:10 pm
Has thanked: 0 time
Been thanked: 0 time

Re: For each key, copy the 1st greater record than the key.

Postby NicC » Tue Feb 14, 2012 12:28 pm

The JCL will work but don't know about the SORT CONTROL CARDS
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: For each key, copy the 1st greater record than the key.

Postby BillyBoyo » Tue Feb 14, 2012 2:58 pm

No, it won't work as you expect, becuase you didn't include that expectation in your description. Run it and see is the easiest way to confirm.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post