How to compare a filed of file1 with values in file2



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

How to compare a filed of file1 with values in file2

Postby lakshmipathik » Sat Apr 06, 2013 2:27 am

Hi,
I have a file 1 with data like below

Name    code ind   CT
------  ----- ---  ---
aaa     123  N     ijk
bbb     234  y    lmn
ccc     125  n    opq

I have a file2 with data like below

ind  Min   Max     CT
---- ----  -----  ----
N   100    125   iii
Y   126    190   jjj
N   191    250   kkk

output file should like this: (i.e. CODE value in file1 should compare with 'Min' and 'Max' values of file2 and if it falls in that range, CT value from file2 should be taken and replace in CT value of file1)

Name     code ind  CT
------  ----- ---  ---
aaa     123  N     iii
bbb     234  y     kkk
ccc     125  n     iii

please let me know if it can be possible using dfsort...
lakshmipathik
 
Posts: 12
Joined: Thu May 08, 2008 11:14 am
Location: bangalore
Has thanked: 5 times
Been thanked: 0 time

Re: How to compare a filed of file1 with values in file2

Postby skolusu » Mon Apr 08, 2013 10:37 pm

lakshmipathik,

Use the following DFSORT JCL which will give you the desired results.

//STEP0100 EXEC PGM=SORT,REGION=0M                         
//SYSOUT   DD SYSOUT=*                                     
//INA      DD *                                             
----+----1----+----2----+----3----+----4----+----5----+----6
AAA     123  N   IJK                                       
BBB     234  Y   LMN                                       
CCC     125  N   OPQ                                       
//INB      DD *                                             
N   100    125   III                                       
Y   126    190   JJJ                                       
N   191    250   KKK                                       
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                             
  OPTION COPY                                               
  JOINKEYS F1=INA,FIELDS=(25,1,A),SORTED,NOSEQCK           
  JOINKEYS F2=INB,FIELDS=(25,1,A),SORTED,NOSEQCK           
  REFORMAT FIELDS=(F1:1,25,F2:5,20)                         
  INCLUDE COND=((9,3,ZD,GE,26,3,ZD),AND,(9,3,ZD,LE,33,3,ZD))
  INREC BUILD=(1,17,39,3)                                   
//*                                                         
//JNF1CNTL DD *                                             
  INREC BUILD=(1,24,X)                                     
//*                                                         
//JNF2CNTL DD *                                             
  INREC BUILD=(1,24,X)                                     
//*                                                         
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

These users thanked the author skolusu for the post:
lakshmipathik (Mon Apr 08, 2013 11:21 pm)
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: How to compare a filed of file1 with values in file2

Postby lakshmipathik » Mon Apr 08, 2013 11:16 pm

Hi skolusu,
Thank you so much for your assistance. COde is working fine.
So much thankful to you.
I will enhance this code to map to my requirement, as it is changing bit now.
regards,
...lakshmi pathi.
lakshmipathik
 
Posts: 12
Joined: Thu May 08, 2008 11:14 am
Location: bangalore
Has thanked: 5 times
Been thanked: 0 time

Re: How to compare a filed of file1 with values in file2

Postby lakshmipathik » Mon Apr 08, 2013 11:51 pm

Hi skolusu,
I have added one more record to above input file INA like below
DDD 300 N XXX
for which there is no record in the the file INB satisfying the condition to verify.

I got the output with only 3 records as you got in your example, which I am assuming that output is having records only if the condition is met.
what should I need to do if I want to display the records from file INA as it is if I dont find any match in file INB.
Here is my JCL:
//PAUSORT  JOB (AU00,11930B),'ABS',MSGCLASS=T,NOTIFY=QSDMCT6     
//STEP0100 EXEC PGM=SORT,REGION=0M                               
//SYSOUT   DD SYSOUT=*                                           
//INA      DD *                                                 
AAA     123  N   IJK                                             
BBB     234  Y   LMN                                             
CCC     125  N   OPQ                                             
DDD     300  N   XXX                                             
//INB      DD *                                                 
N   100    125   III                                             
Y   126    190   JJJ                                             
N   191    250   KKK                                             
//SORTOUT  DD SYSOUT=*                                           
//SYSIN    DD *                                                 
  OPTION COPY                                                   
  JOINKEYS F1=INA,FIELDS=(25,1,A),SORTED,NOSEQCK                 
  JOINKEYS F2=INB,FIELDS=(25,1,A),SORTED,NOSEQCK                 
  REFORMAT FIELDS=(F1:1,25,F2:5,20)                             
  INCLUDE COND=((9,3,ZD,GE,26,3,ZD),AND,(9,3,ZD,LE,33,3,ZD))     
  INREC BUILD=(1,17,39,3)                   
//*                                         
//JNF1CNTL DD *                             
  INREC BUILD=(1,24,X)                       
//*                                         
//JNF2CNTL DD *                             
  INREC BUILD=(1,24,X)                       
//*                                         


output for the above code:
AAA     123  N   III
BBB     234  Y   KKK
CCC     125  N   III


Output I wanted:
AAA     123  N   III
BBB     234  Y   KKK
CCC     125  N   III
DDD     300  N   XXX
lakshmipathik
 
Posts: 12
Joined: Thu May 08, 2008 11:14 am
Location: bangalore
Has thanked: 5 times
Been thanked: 0 time

Re: How to compare a filed of file1 with values in file2

Postby BillyBoyo » Tue Apr 09, 2013 12:26 am

It would have been really, really good to have included that in your original question.

Is this all that you now need?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to compare a filed of file1 with values in file2

Postby skolusu » Tue Apr 09, 2013 2:11 am

lakshmipathik wrote:Hi skolusu,
I have added one more record to above input file INA like below
DDD 300 N XXX
for which there is no record in the the file INB satisfying the condition to verify.

I got the output with only 3 records as you got in your example, which I am assuming that output is having records only if the condition is met.
what should I need to do if I want to display the records from file INA as it is if I dont find any match in file INB.


Well you need to be clear about all the requirement upfront. Use the following control cards.

//SYSIN    DD *                                               
  JOINKEYS F1=INA,FIELDS=(25,1,A),SORTED,NOSEQCK               
  JOINKEYS F2=INB,FIELDS=(25,1,A),SORTED,NOSEQCK               
  REFORMAT FIELDS=(F1:1,33,F2:5,20)                           
  INREC IFOUTLEN=29,                                           
  IFTHEN=(WHEN=((9,3,ZD,GE,34,3,ZD),AND,(9,3,ZD,LE,41,3,ZD)), 
  BUILD=(1,17,47,3,26,8,X)),                                   
  IFTHEN=(WHEN=NONE,BUILD=(1,20,26,8,C'1'))                   
                                                               
  SORT FIELDS=(21,9,CH,A)                                     
                                                               
  OUTFIL REMOVECC,NODETAIL,BUILD=(20X),                       
  SECTIONS=(21,8,HEADER3=(1,20))                               
//*                                                           
//JNF1CNTL DD *                                               
  INREC BUILD=(1,24,X,SEQNUM,8,ZD)                             
//*                                                           
//JNF2CNTL DD *                                               
  INREC BUILD=(1,24,X)                                         
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

These users thanked the author skolusu for the post:
lakshmipathik (Tue Apr 09, 2013 7:13 pm)
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: How to compare a filed of file1 with values in file2

Postby lakshmipathik » Sat Apr 13, 2013 7:05 am

Hi All,
Here is the update after implementing the above code to my requirement.

I have two files INFILE1 and INFILE2 and below are the details:

INFILE1: A.B.C – LRECL=1025 – (I have around 2,50,00,000 records like below with different values)
Columns:
123. . .30. . .373839. . .570. . 574575. . . .. . . .. . 960.  . .1025
Xxxxxxxxxxxxxxx605   xxxxxxxxxxxxxxxKL     xxxxxxxxxxxxxxxN  xxxxxxx
(ie. I have below values at the positions
Position value
37-39     605
574-575   KL
960       N  )


INFILE2: B.C.D – LRECL=63 and below is the data
(I have around 17000 records like below with different values)
Columns
1234567890123 . . . . . . . . . . . . . . . . . . . . . . . .63
N600692**  ABXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
(ie. I have below values at the positions
Position value
2-4       600
5-7       692
8-11     '**  '   and
12-13     AB  )
 


Note: There are no common fields between INFILE1 and INFILE2

My requirement is I have to compare the INFILE1 with INFILE2 based on below condition:
If INFILE1 POS 960 = ‘N’   and
   INFILE2 POS 8-11 = ‘**’ and
   (INFILE1 POS 37-39 >= INFILE 2 POS 2-4 and INFILE1 POS 37-39 <= INFILE 2 )
Take the value from INFILE2 POS 12-13 & keep the same in INFILE1 POS 574-575
 


I have written the below code based on the suggestion I have received for my earlier post and executed the same by taking only 10 records from INFILE1 and 17000 records from INFILE2 and it ran in 0.07 seconds.

OUTPUT:
Columns:
123. . .30. . .373839. . .570. . 574575. . . .. . . .. . 960.  . .1025
Xxxxxxxxxxxxxxx605   xxxxxxxxxxxxxxxAB     xxxxxxxxxxxxxxxN  xxxxxxx


Second time, I ran by taking 2,29,00,000 records from INFILE1 and 17000 records from INFILE2 and it is almost 2.5 hours and still the job is running.
Could anyone please suggest me how to reduce the time to run the below code, since I have another 3 conditions similar to like this for the same files which I have to write in other control cards.
//SAUSRT02 EXEC PGM=ICETOOL,                                           
//TOOLMSG  DD  SYSOUT=*                                     
//DFSMSG   DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//INFILE1  DD  DSN=A.B.C,DISP=SHR             
//INFILE2  DD  DSN=B.C.D,DISP=SHR           
//TEMP1    DD  DSN=C.D.E.TEMP1,DISP=(NEW,CATLG,DELETE),   
//             DCB=(SRCDSCB,RECFM=FB,LRECL=1025),                       
//             SPACE=(1025,(50,50),RLSE),AVGREC=K                       
//SORTWK01 DD  SPACE=(2048,(150,150),RLSE)                             
//SORTWK02 DD  SPACE=(2048,(150,150),RLSE)                             
//SORTWK03 DD  SPACE=(2048,(150,150),RLSE)                             
//SORTWK04 DD  SPACE=(2048,(150,150),RLSE)                             
//TOOLIN   DD  *                                                       
  COPY   FROM(INFILE1) TO(TEMP1)     USING(XBI0)                       
 /*                                                                     
//XBI0CNTL DD  *                                                       
  OPTION COPY                                                     
  JOINKEYS F1=TEMP1,FIELDS=(1026,1,A),SORTED,NOSEQCK               
  JOINKEYS F2=INFILE2,FIELDS=(64,1,A),SORTED,NOSEQCK               
  REFORMAT FIELDS=(F1:1,1025,F2:2,12)                             
  INCLUDE COND=((960,1,CH,EQ,C'N'),AND,(1032,4,SS,EQ,C'**'),AND,   
                (38,3,ZD,GE,1026,3,ZD),AND,(38,3,ZD,LE,1029,3,ZD))
  INREC BUILD=(1,574,1036,2,577,449)                               
/*                                                                 
//JNF1CNTL DD *                                                   
  INREC BUILD=(1,1025,X)                                           
//*                                                               
//JNF2CNTL DD *                                                   
  INREC BUILD=(1,63,X)                                             
//*                                                               
/*


Thanks in Advance,
regards,
lakshmipathik
 
Posts: 12
Joined: Thu May 08, 2008 11:14 am
Location: bangalore
Has thanked: 5 times
Been thanked: 0 time

Re: How to compare a filed of file1 with values in file2

Postby lakshmipathik » Sat Apr 13, 2013 7:43 am

Hi,
Also, I would like to write unmatched records into seperate file, how could I do that as well in the same JCL.

Thanks in Advance,
regards,
..lakshmipathi
lakshmipathik
 
Posts: 12
Joined: Thu May 08, 2008 11:14 am
Location: bangalore
Has thanked: 5 times
Been thanked: 0 time

Re: How to compare a filed of file1 with values in file2

Postby BillyBoyo » Sat Apr 13, 2013 12:15 pm

Why've you put it in an ICETOOL COPY? If there, why not with JKFROM?

If you have 25 million records, it is going to take some time, as each of those 25 million records will be compare with each of the 17 thousand records.

It is again something you should have mentioned at the start. We had no clue from the data you showed that you were going to do it with such volumes of data.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to compare a filed of file1 with values in file2

Postby enrico-sorichetti » Sat Apr 13, 2013 1:03 pm

IIRC there were some topics where Kolusu and Frank remarked that
for a JOINKEY application switching F1 and F2 would make quite a difference in performance
when there was a huge difference in the files being joined.
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
lakshmipathik (Mon Apr 15, 2013 3:19 am)
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post