MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Reformatting output file by including new field in Joinkeys

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
balaryan
Beginner


Joined: 10 Mar 2017
Posts: 13
Topics: 2
Location: Boston

PostPosted: Mon Mar 13, 2017 3:47 pm    Post subject: Reformatting output file by including new field in Joinkeys Reply with quote

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.
_________________
B@L@
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Mon Mar 13, 2017 6:53 pm    Post subject: Reply with quote

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

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
balaryan
Beginner


Joined: 10 Mar 2017
Posts: 13
Topics: 2
Location: Boston

PostPosted: Tue Mar 14, 2017 4:00 pm    Post subject: Reply with quote

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.
_________________
B@L@
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Tue Mar 14, 2017 4:39 pm    Post subject: Reply with quote

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.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
balaryan
Beginner


Joined: 10 Mar 2017
Posts: 13
Topics: 2
Location: Boston

PostPosted: Tue Mar 14, 2017 5:48 pm    Post subject: Reply with quote

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
_________________
B@L@
Back to top
View user's profile Send private message
balaryan
Beginner


Joined: 10 Mar 2017
Posts: 13
Topics: 2
Location: Boston

PostPosted: Thu Mar 16, 2017 11:02 am    Post subject: Reply with quote

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?
_________________
B@L@
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Mar 16, 2017 11:26 am    Post subject: Reply with quote

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
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
balaryan
Beginner


Joined: 10 Mar 2017
Posts: 13
Topics: 2
Location: Boston

PostPosted: Thu Mar 16, 2017 3:49 pm    Post subject: Reply with quote

@Kolusu,

Thanks. I got it now and it workz.
_________________
B@L@
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group