Reformatting output file by including new field in Joinkeys
Select messages from
# through # FAQ
[/[Print]\]

MVSFORUMS.com -> Utilities

#1: Reformatting output file by including new field in Joinkeys Author: balaryanLocation: Boston PostPosted: Mon Mar 13, 2017 3:47 pm
    —
Hello,

I am having an issue in understanding the depth of JOINKEYS. I am trying to compare and merge two input files (each file having different layout) which are in SORTED order and populate the new output file with below condition.

a. Match fields fld#1, fld#2 (both file 1 and 2) and fld#3 (file1) with fld#4(file 2). If all three fields matching in both files, write a record from FILE 1 ONLY and discard file 2 record.
b. If no records matches, then write file 1 record as well file 2 records.
c. While writing file 2 records, we need to include all fields except FLD#3. Also new field should be included at end of the record with value '0.00' while writting records from file 2 to match the record layout of file 1.


Code:
File 1:
-------
Fld#1 Fld#2 Fld#3 Fld#4 Fld#5 Fld#6 Fld#7

111   991   Y   12.10   A   10   12.70
222   992   N   11.10   A   10   17.70
333   993   Y    8.10   D   11   11.88
444   994   N    2.23   D   12    6.12
555   995   Y   18.12   A   12   19.39

File 2:
-------
Fld#1 Fld#2 Fld#3 Fld#4 Fld#5 Fld#6 Fld#7

111   991   1-may   Y   12.10   A   10
222   992   6-Apr   N   11.10   A   10
333   996   8-may   Y   18.10   D   11
666   997   1-oct   N   21.23   D   12
777   998   4-Jun   Y   38.12   A   12

JOINKEYS FILE=F1,FIELDS=(1,3,A,5,3,A,8,1,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(1,3,A,5,3,A,14,1,A),SORTED,NOSEQCK
JOIN UNPAIRED,F1,F2

Output file:
------------
Fld#1 Fld#2 Fld#3 Fld#4 Fld#5 Fld#6 Fld#7

111   991   Y   12.10   A   10   12.70
222   992   N   11.10   A   10   17.70
333   993   Y    8.10   D   11   11.88
333   996   Y   18.10   D   11    0.00
444   994   N    2.23   D   12    6.12   
555   995   Y   18.12   A   12   19.39
666   997   N   21.23   D   12    0.00
777   998   Y   38.12   A   12    0.00


Is it possible to achieve it using JOINKEYS and REFORMATTING? Kindly help. Thanks in advance.

#2:  Author: kolusuLocation: San Jose PostPosted: Mon Mar 13, 2017 6:53 pm
    —
balaryan,

Quite simple. Use the following DFSORT JCL which will give you the desired results. I assumed that your input files have RECFM=FB and LRECL=80

Code:

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//INA      DD *                                                 
----+----1----+----2----+----3----+----4----+----5----+----6----
111 991Y   12.10   A   10 12.70                                 
222 992N   11.10   A   10 17.70                                 
333 993Y    8.10   D   11 11.88                                 
444 994N    2.23   D   12  6.12                                 
555 995Y   18.12   A   12 19.39                                 
//INB      DD *                                                 
111 991 1-MAYY   12.10   A   10                                 
222 992 6-APRN   11.10   A   10                                 
333 996 8-MAYY   18.10   D   11                                 
666 997 1-OCTN   21.23   D   12                                 
777 998 4-JUNY   38.12   A   12                                 
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                                 
  OPTION COPY                                                   
  JOINKEYS F1=INA,FIELDS=(1,3,A,5,3,A,08,1,A),SORTED,NOSEQCK   
  JOINKEYS F2=INB,FIELDS=(1,3,A,5,3,A,14,1,A),SORTED,NOSEQCK   
  JOIN UNPAIRED                                                 
  REFORMAT FIELDS=(F1:1,80,?,F2:1,80)                           
  INREC IFOUTLEN=80,                                           
  IFTHEN=(WHEN=(81,1,CH,EQ,C'2'),                               
  BUILD=(82,07,95,18,C'  0.00',113,49))                         
//*


The output from this is
Code:

111 991Y   12.10   A   10 12.70
222 992N   11.10   A   10 17.70
333 993Y    8.10   D   11 11.88
333 996Y   18.10   D   11  0.00
444 994N    2.23   D   12  6.12
555 995Y   18.12   A   12 19.39
666 997N   21.23   D   12  0.00
777 998Y   38.12   A   12  0.00

#3:  Author: balaryanLocation: Boston PostPosted: Tue Mar 14, 2017 4:00 pm
    —
Hi kolusu,

Your as usual amazing. Thanks for the solution. I wonder how you see things simple always. Smile

I also would like to take this opportunity in understanding the solution you provided.

Code:
  JOIN UNPAIRED   


This is as same as JOIN UNPAIRED F1, F2 which will extract the paired records (when JOINKEYS matches F1 and F2 record) from F1 ONLY along with unpaired records from F1 and F2. This is my requirement.

Code:
  REFORMAT FIELDS=(F1:1,80,?,F2:1,80) 


Reformatting fields with '?' indicates complete file content from files (F1 and F2) and 1-byte indicator at end of the file to show if key present in F1 or F2.

Code:
  INREC IFOUTLEN=80,   

Setting the output record length to 80.

Code:
  IFTHEN=(WHEN=(81,1,CH,EQ,C'2'),                               
  BUILD=(82,07,95,18,C'  0.00',113,49))   


We build the output file by modifying the records for file F2 by checking the 1 byte indicator value as '2'. It will add '0.00' in the position 26. Kindly correct me if my understanding was right. Highly appreciate your time and help. Thanks again for the simple solution. Nice learning.

#4:  Author: kolusuLocation: San Jose PostPosted: Tue Mar 14, 2017 4:39 pm
    —
balaryan wrote:
This is as same as JOIN UNPAIRED F1, F2 which will extract the paired records (when JOINKEYS matches F1 and F2 record) from F1 ONLY along with unpaired records from F1 and F2. This is my requirement.


Yes it is the same. You can find the detailed description here

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/4.4

balaryan wrote:

Reformatting fields with '?' indicates complete file content from files (F1 and F2) and 1-byte indicator at end of the file to show if key present in F1 or F2.


The reformat statement is building the records as follows
Code:

----------------------------------------------------------------
|BYTES 1 THU 80 FROM FILE-1 | IND ? | BYTES 1 THU 80 FROM FILE2 |
----------------------------------------------------------------


DFSORT Application Programming Guide wrote:

? indicates a 1-byte indicator is to be included in each joined record. The indicator will be set to one of the following values in each paired or unpaired joined record, as appropriate:

'B' - the key was found in F1 and F2.

'1' - the key was found in F1, but not in F2.

'2' - the key was found in F2, but not in F1.

The same is explained in detail here

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/4.5?

So for the matched records(IND = 'B'), you wanted the records from file-1 itself and the contents of File-1 are already in the first 80 bytes, so we do not need to re-arrange the data. The IFOUTLEN=80 will drop off any thing beyond position 80 (this has the indicator and file-2 data)

Similarly the unpaired file-1 records (IND = '1') you wanted the records from file-1 as is and the contents of File-1 are already in the first 80 bytes, so we do not need to re-arrange the data. The IFOUTLEN=80 will drop off any thing beyond position 80 (this has the indicator and 80 spaces)

So for the unpaired file 2 records(IND = '2'), the data is at postion 82 (file80+1 byte ind =81), So we pull the desired data from position 82 onward and remove the fields we do not want and at the same time also put the default values of 0.00

So we use a single IFTHEN statement and build the unpaired FILE-2 records.

#5:  Author: balaryanLocation: Boston PostPosted: Tue Mar 14, 2017 5:48 pm
    —
Thanks Kolusu for the detailed information which helped me in understanding the concept of JOINKEYS. Appreciate your help and time in providing valuable info. bonk

#6:  Author: balaryanLocation: Boston PostPosted: Thu Mar 16, 2017 11:02 am
    —
Hi Kolusu,

Apologies for using the same topic to post my query which was based on the initial problem. Is it possible to sort the output file as a result of this JOINKEYS with two fields in ASC and other fields in DESC order in the same step?

#7:  Author: kolusuLocation: San Jose PostPosted: Thu Mar 16, 2017 11:26 am
    —
balaryan wrote:
Hi Kolusu,

Apologies for using the same topic to post my query which was based on the initial problem. Is it possible to sort the output file as a result of this JOINKEYS with two fields in ASC and other fields in DESC order in the same step?


Yes it is possible. Replace the OPTION COPY statement under SYSIN to SORT FIELDS=(....) and that will perform a SORT after the JOIN operation is completed.

balaryan wrote:

I tried searched in IBM DFSORT manuals and no luck in finding any information.


Did you look at this ? Read under the section "Main Task for Joined Records"

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/4.1.1

#8:  Author: balaryanLocation: Boston PostPosted: Thu Mar 16, 2017 3:49 pm
    —
@Kolusu,

Thanks. I got it now and it workz.



MVSFORUMS.com -> Utilities


output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1

Powered by phpBB © 2001, 2005 phpBB Group