Compare two files and add specific field in the output file



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

Compare two files and add specific field in the output file

Postby 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:-
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:-
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:-
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.
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

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

 

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

Postby Magesh23586 » Tue Jul 15, 2014 3:38 pm

Refer Joinkeys in DFSORT application programming guide.

Thanks
Magesh
Magesh23586
 
Posts: 35
Joined: Sat Jul 05, 2014 5:36 pm
Has thanked: 1 time
Been thanked: 3 times

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

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

//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
/*
//***
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

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

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

//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
Magesh23586
 
Posts: 35
Joined: Sat Jul 05, 2014 5:36 pm
Has thanked: 1 time
Been thanked: 3 times

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

Postby NicC » Wed Jul 16, 2014 1:08 am

gauravnnl
Please use the code tags when posting data, code, JCL, control cards etc.
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: Compare two files and add specific field in the output f

Postby 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
  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
Magesh23586
 
Posts: 35
Joined: Sat Jul 05, 2014 5:36 pm
Has thanked: 1 time
Been thanked: 3 times

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

Postby 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.
//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
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

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

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

   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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

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

Postby 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"
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

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

Postby 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,
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post