IBM Mainframe Forum

by **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)

Select all

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...

by **skolusu** » Mon Apr 08, 2013 10:37 pm

lakshmipathik,

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

Select all

//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)
//*

by **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.

by **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:

Select all

//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:

Select all

AAA 123 N III
BBB 234 Y KKK
CCC 125 N III

Output I wanted:

Select all

AAA 123 N III
BBB 234 Y KKK
CCC 125 N III
DDD 300 N XXX

by **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?

by **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.

Select all

//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)
//*

by **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)

Select all

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)

Select all

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:

Select all

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:

Select all

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.

Select all

//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,

by **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

by **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.

by **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.

IBM Mainframe Forum

How to compare a filed of file1 with values in file2

How to compare a filed of file1 with values in file2

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

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

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

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

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

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

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

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

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