IBM Mainframe Forum

by **gauravnnl** » Tue Jul 15, 2014 2:52 pm

Hi,

I have a requirement in which I have two files(A and B) of Record format- FB and record length- 20. I want all the records from file A and add the count of field which is at position 8-12 of file A to B and need this result in output file C.Below are the screen shot of files.

FILE A:-

Select all

BRANCH-ID CONTROLLER-ID BRANCH-COUNT
4/NUM 3/NUM 5/PS
(1-4) (5-7) (8-12)
1---------------- 2-------------------- 3-------------------
********************************* TOP OF DATA **************
0001 110 100
0002 125 200
0005 119 500
0006 104 600
0008 99 800
0009 219 900

FILE B:-

Select all

BRANCH-ID CONTROLLER-ID BRANCH-COUNT
4/NUM 3/NUM 5/PS
(1-4) (5-7) (8-12)
1---------------- 2-------------------- 3-------------------
********************************* TOP OF DATA **************
0001 110 1000
0005 119 5000
0006 104 6000
0009 219 9000

Output file C should be as below:-

Select all

BRANCH-ID CONTROLLER-ID BRANCH-COUNT
4/NUM 3/NUM 5/PS
(1-4) (5-7) (8-12)
1---------------- 2-------------------- 3-------------------
********************************* TOP OF DATA **************
0001 110 1100
0002 125 200
0005 119 5500
0006 104 6600
0008 99 800
0009 219 9900

Note- There are no duplicates in any of the input file.

by **Magesh23586** » Tue Jul 15, 2014 3:38 pm

Refer Joinkeys in DFSORT application programming guide.

Thanks
Magesh

by **gauravnnl** » Tue Jul 15, 2014 3:57 pm

Hi Magesh,

I tried joinkeys with DFSORT and even with ICETOOL but not getting the correct output.Below is sample code what i am using. If you can guide and help me with the syntax please.

Select all

//STEP10 EXEC PGM=ICETOOL,REGION=1024K
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=ABC.DEBTBL.EPOS,
// DISP=SHR
//IN2 DD DSN=PQR.DEBTBL.CATER,
// DISP=SHR
//REP1OUT DD DSN=XYZ.TEST.OUTPUT,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(1,4),RLSE),
// DCB=(LRECL=20,RECFM=FB,BLKSIZE=0)
//TOOLIN DD *
COPY JKFROM TO(REP1OUT) USING(JKF1)
//JKF1CNTL DD *
JOINKEYS F1=IN1,FIELDS(1,4,A)
JOINKEYS F2=IN2,FIELDS(1,4,A)
JOIN UNPAIRED,F2
REFORMAT FIELDS=(F1:001,20,F2:001,20)
OPTION COPY
/*
//***

by **Magesh23586** » Tue Jul 15, 2014 6:01 pm

Try this and let us know the results.

Note : I assumed the output file to be of Record length 12. If it is greater you need build records accordingly.

Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=Input1,DISP=SHR
//SORTJNF2 DD DSN=input2,DISP=SHR
//SORTOUT DD DSN=Output,DISP=(,CATLG,DELETE),
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,4,A)
JOINKEYS FILE=F2,FIELDS=(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,12,F2:8,20)
OPTION COPY
OUTFIL IFTHEN=(WHEN=(20,1,CH,EQ,C'A'),
BUILD=(1,7,8:8,5,PD,ADD,13,5,PD,TO=PD,LENGTH=5)),
IFTHEN=(WHEN=NONE,BUILD=(1,12))
/*
//JNF2CNTL DD *
INREC OVERLAY(15:C'A')
/*

Regards,
Magesh

by **NicC** » Wed Jul 16, 2014 1:08 am

gauravnnl
Please use the code tags when posting data, code, JCL, control cards etc.

by **Magesh23586** » Wed Jul 16, 2014 2:22 pm

Hi Magesh,

Thanks for your help. I managed to reach the output but not completely. Appreciate if you can give few more minutes. Actually My output file length should be 20 only and there are 3 more fields after 12th position(Apologies,I forget to tell that in my question). I have written below code and it worked till the 12th position for matching records in both files, but i need those fields from 13-20 as it is present in F1.I am not sure how to add this 13-20 position in BUILD statement. I tried many options but getting syntax error.Below is the code snippet..

//STEP10 EXEC PGM=SORT,REGION=1024K
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TPU233.DEBTBL.EPOS,
// DISP=SHR
//SORTJNF2 DD DSN=TPU233.DEBTBL.CATER,
// DISP=SHR
//SORTOUT DD DSN=TPU233.TEST.OUTPUT,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(1,4),RLSE),
// DCB=(LRECL=20,RECFM=FB,BLKSIZE=0)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS(1,4,A)
JOINKEYS FILE=F2,FIELDS(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:001,20,F2:008,5),FILL=C'A'
OPTION COPY
OUTFIL IFTHEN=(WHEN=(21,1,CH,NE,C'A'),
BUILD=(1,7,8:8,5,PD,ADD,21,5,PD,TO=PD,LENGTH=5)),
IFTHEN=(WHEN=NONE,BUILD=(1,20))
/*

Sending you a private message as I don't know how you guys are pasting that Mainframe screen here. As I am new here, If u can tell this as well.Thanks for your time !!!

Regards,
Gaurav

gauravnnl,

Please dont send anything in private message, Always post your problem here, so that any one has the same problem in future, this post will be useful.

When you post you message you can find some of the command buttons above the texbox like B I U Quote Code List List= [*] Img URL etc

Click the "Code" button you will get ["Code"] and clicking it again you will ["/code"]. Write your code between these two tags, Then press Preview button to see how your message looks like.

Try using the below code untested, and let us know the results

Select all

JOINKEYS FILE=F1,FIELDS=(1,4,A)
JOINKEYS FILE=F2,FIELDS=(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,20,F2:8,20)
OPTION COPY
OUTFIL IFTHEN=(WHEN=(28,1,CH,EQ,C'A'),
BUILD=(1,7,8:8,5,PD,ADD,21,5,PD,TO=PD,LENGTH=5,13,8)),
IFTHEN=(WHEN=NONE,BUILD=(1,20))

Regards,
Magesh

by **gauravnnl** » Wed Jul 16, 2014 3:52 pm

Thanks Magesh for your help and direction!!!

After few modification code works and I got the desired output. Below is the code if anyone wants to refer.

Select all

//STEP10 EXEC PGM=SORT,REGION=1024K
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=FILE.INPUT1,
// DISP=SHR
//SORTJNF2 DD DSN=FILE.INPUT2,
// DISP=SHR
//SORTOUT DD DSN=FILE.OUTPUT,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(1,4),RLSE),
// DCB=(LRECL=20,RECFM=FB,BLKSIZE=0)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS(1,4,A)
JOINKEYS FILE=F2,FIELDS(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:001,20,F2:008,5),FILL=C'A'
OPTION COPY
OUTFIL IFTHEN=(WHEN=(21,1,CH,NE,C'A'),
BUILD=(1,7,8:8,5,PD,ADD,21,5,PD,TO=PD,LENGTH=5,13,8)),
IFTHEN=(WHEN=NONE,BUILD=(1,20))
/*

This thread can be closed now. Thanks Nic as well, I will take care in future

Regards,
Gaurav

by **BillyBoyo** » Wed Jul 16, 2014 4:31 pm

DFSORT has a "match marker" for JOINKEYS: you don't need the FILL. The match marker has value of B (on both files), 1 (on F1 only) or 2 (on F2 only).

Select all

JOINKEYS FILE=F1,FIELDS(1,4,A)
JOINKEYS FILE=F2,FIELDS(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:001,20,F2:008,5,?)
OPTION COPY
OUTFIL IFOUTLEN=20,
IFTHEN=(WHEN=(26,1,CH,EQ,C'B'),
OVERLAY=(8:8,5,PD,ADD,21,5,PD,TO=PD,LENGTH=5))

You show both input files as in sequence already. If this is correct, you should specify SORTED,NOSEQCK for each file, otherwise they will be sorted and checked again.

The ? is how you specify the match marker in the REFORMAT statement.

Using OVERLAY, since only data at one position changes, saves a bit of CPU and using IFOUTLEN=20 (the output record length after IFTHEN processing) saves on an IFTHEN and a BUILD.

by **gauravnnl** » Wed Jul 16, 2014 5:55 pm

Thanks Billy... Its also working fine. I have included SORTED,NOSEQCK in my code now.
You are saying DFSORT has a "match marker" for JOINKEYS so why there is need to specify '?'. I am not getting what does '?' mean exactly. When I am running the code without '?' like specifying REFORMAT FIELDS=(F1:001,20,F2:008,5) I am getting error "FIELD BEYOND MAXIMUM RECORD LENGTH"

by **BillyBoyo** » Wed Jul 16, 2014 5:58 pm

The ? is the match marker. The ? inserts B, 1 or 2, the result of the join, at that position. If you take it out, your REFORMAT record is one byte shorter (so your failure) and you now would have to revert to checking for some particular value in the F2 part of the REFORMAT record to tell if you had a match,

IBM Mainframe Forum

Compare two files and add specific field in the output file

Compare two files and add specific field in the output file

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f

Re: Compare two files and add specific field in the output f