Extract records by comparing files



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

Extract records by comparing files

Postby MFsys » Thu Jul 22, 2010 6:44 pm

I am pretty new to DFSORT and have a REXX code which is currently doing

I have file 1 which has a list of datasets in this format :

1-3 6-50 (max dataset length 44)
xxx somedatasetname-1-hereupto44chars
xxx somedatasetname-4-hereupto44chars
xxx somedatasetname-7-hereupto44chars

and another file 2 also containing a list of datasets along with some other fields:

1-6 10-54 (upto 44 chars) 57-66 70-79 83-87 92-93
vvvvvv somedatasetname-1-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-2-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-3-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-4-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-5-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-6-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-7-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz


I want to compare these files and extract records from file 2 into a third file which has the dataset names as listed in file 1.

Outputfile:

vvvvvv somedatasetname-1-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-4-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz
vvvvvv somedatasetname-7-hereupto44chars dd/mm/yyyy dd/mm/yyyy xxxxx zz

I am currently using a REXX to do this, but that takes up quite a lot of time comparing each record one by one with the second field in file 2. I am new to DFSORT and want to see if DF/SORT can do it more quickly for me. Any ideas?
MFsys
 
Posts: 9
Joined: Thu Jul 22, 2010 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Extract records by comparing files

 

Re: Extract records by comparing files

Postby NicC » Thu Jul 22, 2010 7:17 pm

If you look at the document "DFSort - tricks.pds" and look at the trick "Pull records from a Master file" that would appear to do what you want. I cannot get onto my mainframe to code it up to test it. The document can be found here

http://www.ibm.com/support/docview.wss? ... g3T7000094
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Extract records by comparing files

Postby MFsys » Thu Jul 22, 2010 9:11 pm

Thanks for pointing that out, NicC. I have a copy of that PDF and I checked the trick. The trick seems to do pretty much what I want to accomplish. However, there is an important difference. The key (account no as in the example trick) is variable length in my case. It is a dataset name which can be of any length (upto 44 chars).

Sorry, but I am pretty new to DF/SORT - can you advise what changes would be required to accomodate this requirement into the job?
MFsys
 
Posts: 9
Joined: Thu Jul 22, 2010 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Extract records by comparing files

Postby NicC » Thu Jul 22, 2010 9:13 pm

Sorry, no I cannot. You will have to wait for someone who can!
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Extract records by comparing files

Postby skolusu » Thu Jul 22, 2010 9:54 pm

MFsys,

The following DFSORT JCL will give you the desired results


//STEP0100 EXEC PGM=SORT                         
//SYSOUT   DD SYSOUT=*                           
//FB50     DD DSN=Your input FB 50 byte file,DISP=SHR
//FB93     DD DSN=Your input FB 93 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                           
//SYSIN    DD *                                   
  OPTION COPY                                     
  JOINKEYS F1=FB50,FIELDS=(06,44,A)               
  JOINKEYS F2=FB93,FIELDS=(10,44,A)               
  JOIN UNPAIRED                                   
  REFORMAT FIELDS=(?,F2:1,93)                     
  OUTFIL INCLUDE=(1,1,CH,EQ,C'B'),BUILD=(2,93)   
//*
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: Extract records by comparing files

Postby MFsys » Thu Jul 22, 2010 11:20 pm

Thanks, skolusu. Unfortunately, we are pretty behind on our DF/SORT maintenance (running April, 2006 DFSORT functions) and JOINKEYS functionality isn't available at our site :( . I'll look into the possibility of getting upto date on our DF/SORT maintenance.
MFsys
 
Posts: 9
Joined: Thu Jul 22, 2010 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Extract records by comparing files

Postby skolusu » Fri Jul 23, 2010 12:06 am

MFsys wrote:Thanks, skolusu. Unfortunately, we are pretty behind on our DF/SORT maintenance (running April, 2006 DFSORT functions) and JOINKEYS functionality isn't available at our site :( . I'll look into the possibility of getting upto date on our DF/SORT maintenance.


Use the following DFSORT/ICETOOL JCL which will give you the desired results. I assumed that you don't have duplicates on the DSN name

//STEP0100 EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//FB50     DD DSN=Your input FB 50 byte file,DISP=SHR
//TM93     DD DSN=&&TM93,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)   
//FB93     DD DSN=Your input FB 93 byte file,DISP=SHR
//         DD DSN=&&TM93,DISP=SHR,VOL=REF=*.TM93               
//OUT      DD SYSOUT=*                                         
//TOOLIN   DD *                                                 
  COPY FROM(FB50) USING(CTL1)                                   
  SELECT FROM(FB93) TO(OUT) FIRSTDUP ON(10,44,CH)               
//CTL1CNTL DD *                                                 
  OUTFIL FNAMES=TM93,BUILD=(10:6,44,93:X)                       
//*
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: Extract records by comparing files

Postby MFsys » Fri Jul 23, 2010 1:35 am

That did the trick :D. Thanks skolusu.
MFsys
 
Posts: 9
Joined: Thu Jul 22, 2010 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Extract records by comparing files

Postby MFsys » Mon Jul 26, 2010 11:12 pm

I have been trying my luck with the solution above, and have been reading and learning about the DFSORT/ICETOOL instructions given here. However, there seems to be some problem here. Obviously, there might be some facts which I may have missed while asking the question.

File 1 has a total of 2505 records. To my knowledge these are not duplicate. However, when I ran a DFSORT job with following control cards I am left with 2438 records.

SORT FIELDS=(1,50,CH,A)
SUM FIELDS=NONE

So, I am concluding that there were probably some records which were duplicates and were removed with the above job.

I did the same with File 2, but the no. of records remain the same. Hence, I am presuming that there are no duplicates here. I now have 45706 records in File 2.

//FILE1  DD DISP=SHR,DSN=FILE1
//FILE2  DD DISP=SHR,DSN=FILE2
//TFILE1 DD DSN=&&TFILE1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//TFILE2 DD DSN=&&TFILE2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CONCAT   DD DSN=*.TFILE1,UNIT=SYSDA,DISP=(OLD,PASS),               
//         VOL=REF=*.TFILE1                                         
//         DD DSN=*.TFILE2,UNIT=SYSDA,DISP=(OLD,PASS),               
//         VOL=REF=*.TFILE2
//ALLRECS  DD SYSOUT=*  names in File1 only                           
//REPORT   DD DISP=OLD,DSN=REPORT SPACE=(CYL,(5,5))           
//TOOLIN   DD *                                                       
  COPY FROM(FILE1) USING(CTL1)                                       
  COPY FROM(FILE2) USING(CTL2)                                       
  COPY FROM(CONCAT) TO(ALLRECS)                                       
  SELECT FROM(CONCAT) TO(REPORT) ON(6,44,CH) LASTDUP                   
/*                                                                     
//CTL1CNTL DD *                                                       
  OUTFIL FNAMES=TFILE1,BUILD=(1,133)                                 
/*                                                         
//CTL2CNTL DD *                                             
  OUTFIL FNAMES=TFILE2,BUILD=(6:13,44,2X,4,6,2X,60,42,32X)
/*                                                         


Now, I expect the REPORT to contain a max of 2438 records. However, I end up with 2708 records. This doesn't seem to be correct?? I don't want to / CAN'T check each record individually to find out which are the extra records that have been included here and why. What am I missing here?
MFsys
 
Posts: 9
Joined: Thu Jul 22, 2010 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Extract records by comparing files

Postby MFsys » Mon Jul 26, 2010 11:26 pm

Record format of my FILE1 dataset :

3 char field1
2 blanks
44 char field for datasetname

=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
****** ***************************** Top of Data ******************************
000001 xxx  somedatasetnamehere
000002 yyy   anotherdatasetnamehere
000003 xxx  onemoredatasethere


Record format of my FILE2 dataset :

3 blanks
6 char field1
3 blanks
44 char field for dataset name (to be compared with FILE1)
3 blanks
10 chars for field3
3 blanks
10 chars for field4
3 blanks
5 char for field5
4 blanks
2 char for field6

Ideally I would want the output to be something like this:

3 char from field1 of FILE1
3 blanks
44 char field for datasetname
3 blanks
6 char field1 from FILE2
3 blanks
10 chars for field3
3 blanks
10 chars for field4
3 blanks
5 char for field5
4 blanks
2 char for field6
MFsys
 
Posts: 9
Joined: Thu Jul 22, 2010 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post