Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need help in Joining 2 files on 3 key fields.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
avashisht

New User


Joined: 16 Sep 2005
Posts: 11

PostPosted: Fri Jul 15, 2011 12:50 am    Post subject: Need help in Joining 2 files on 3 key fields.
Reply with quote

Hi!
I am trying to join 2 Fixed Block files using JOINKEYS. Both the files have the 3 key fields I want to join on.

First file:

Key1: pos-11, length - 3 bytes (numeric)
Key2: pos-14, length - 15 bytes (alphanumeric)
Key3: pos-29, length - 3 bytes (numeric)

Code:
----+----1----+----2----+----3-
    ------------------------------
   0892208001     008900365A077
444FFFFFFFFFF44444FFFFFFFFFCFFF
0000892208001000000089003651077
    ------------------------------
   0893560001     008900640A059
444FFFFFFFFFF44444FFFFFFFFFCFFF
0000893560001000000089006401059
    ------------------------------
   0893560001     008900640A077
444FFFFFFFFFF44444FFFFFFFFFCFFF
0000893560001000000089006401077


Second file:

Key1: pos-02, length - 3 bytes (numeric)
Key2: pos-05, length - 15 bytes (alphanumeric)
Key3: pos-20, length - 3 bytes (numeric)

Code:
----+----1----+----2----+--
    --------------------------
V001         00930669A22401...
EFFF444444FFFFFFFFCFFFFF000
50010000000093066912240110C
    --------------------------
C001     000294152611702.e.
CFFF44444FFFFFFFFFFFFFFF080
30010000000029415261170205C
    --------------------------
C001     000294152630402...
CFFF44444FFFFFFFFFFFFFFF010
30010000000029415263040205C
[/img]

I want to keep all the data of file 1 in the result and only 1 field from file 2.

I have been trying following code to try it out, but its not working. I am getting an empty file when I dont try to normalize the keys. I get an error when I try to normalize the keys (because one of the keys is actually a CH type, I am getting syntax error on the conversion to ZDF or ZDC for that matter).


Code:
//SORT03   EXEC PGM=SORT                                       
//SYMNAMES DD *                                                 
IN1_KEY1,11,03,ZD                                               
IN1_KEY2,14,15,CH                                               
IN1_KEY3,29,03,ZD                                               
IN2_KEY1,02,03,ZD                                               
IN2_KEY2,05,15,CH                                               
IN2_KEY3,20,03,ZD                                               
//FILE1 DD  DSN=xxxxxxx,                   
//             DISP=SHR                                         
//FILE2 DD  DSN=xxxxxxx,                   
//             DISP=SHR                                         
//*                          CONTAINS FILES AND SNAP COMMANDS   
//SYSIN    DD  *                                               
  JOINKEYS F1=FILE1,FIELDS=(11,3,A,14,15,A,29,3,A)             
  JOINKEYS F2=FILE2,FIELDS=(02,3,A,05,15,A,20,3,A)             
  REFORMAT FIELDS=(F1:1,31,F2:23,5,F1:32,65)                   
  SORT FIELDS=COPY                                             
/*                                                         
//JNF1CNTL DD *                                             
   INCLUDE COND=(29,3,CH,EQ,C'059',OR,29,3,CH,EQ,C'077',OR,
                 29,3,CH,EQ,C'300',OR,29,3,CH,EQ,C'304',OR,
                 29,3,CH,EQ,C'306')                         
   INREC OVERLAY=(IN1_KEY1:IN1_KEY1,TO=ZDF,LENGTH=3,       
                  IN1_KEY2:IN1_KEY2,TO=ZDF,LENGTH=15,       
                  IN1_KEY3:IN1_KEY3,TO=ZDF,LENGTH=3)       
//JNF2CNTL DD *                                             
   INCLUDE COND=(20,3,CH,EQ,C'059',OR,20,3,CH,EQ,C'077',OR,
                 20,3,CH,EQ,C'300',OR,20,3,CH,EQ,C'304',OR,
                 20,3,CH,EQ,C'306')                         
   INREC OVERLAY=(IN2_KEY1:IN2_KEY1,TO=ZDF,LENGTH=3,       
                  IN2_KEY2:IN2_KEY2,TO=ZDF,LENGTH=15,       
                  IN2_KEY3:IN2_KEY3,TO=ZDF,LENGTH=3)       
//SORTOUT  DD  DSN=xxxxxxxxxxxxxxxxx,         
//             DISP=(NEW,CATLG,CATLG),                     
//             SPACE=(80,(2000,1000),RLSE),                 
//             UNIT=SYSDA


Amit
Back to top
View user's profile Send private message

sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Jul 15, 2011 1:13 am    Post subject:
Reply with quote

avashisht,
Which Sort product are you using?
Quote:
I have been trying following code to try it out, but its not working. I am getting an empty file when I dont try to normalize the keys. I get an error when I try to normalize the keys (because one of the keys is actually a CH type, I am getting syntax error on the conversion to ZDF or ZDC for that matter).
Based on SYMNAMES, if keys in both the files are in same format(CH or ZD), why would you need to convert them? Are you saying you need to convert them for matching?

Based on the test data you provided, I don't see any records matching on key fields from both the files.

Thanks,
Back to top
View user's profile Send private message
avashisht

New User


Joined: 16 Sep 2005
Posts: 11

PostPosted: Fri Jul 15, 2011 1:37 am    Post subject:
Reply with quote

I am using Z/OS DFSORT V1R5.

I was converting them because the documentation for JOINKEYS say -

Quote:
The keys will be treated as binary, so they must be "normalized". For example, if the keys are actually zoned decimal, they must have all C and D signs, or all F and D signs. You can use an INREC statement in JNF1CNTL and/or JNF2CNTL to normalize the keys for the F1 file and/or F2 file, respectively, if appropriate.


Also, I am putting some matching records from file 2 here for reference:

Code:
----+----1----+----2----+--
    --------------------------
V001     008900640A04108...
EFFF44444FFFFFFFFFCFFFFF010
50010000000890064010410800C
    --------------------------
V001     008900640A05908...
EFFF44444FFFFFFFFFCFFFFF010
50010000000890064010590800C
    --------------------------
V001     008900640A07708...
EFFF44444FFFFFFFFFCFFFFF000
50010000000890064010770805C
    --------------------------
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jul 15, 2011 1:58 am    Post subject:
Reply with quote

avashisht,

For the data you show, you can treat the CH fields as ZD fields. Just change your SYMNAMES to:

Code:

//SYMNAMES DD *                   
IN1_KEY1,11,03,ZD                 
IN1_KEY2,14,15,ZD                 
IN1_KEY3,29,03,ZD                 
IN2_KEY1,02,03,ZD                 
IN2_KEY2,05,15,ZD                 
IN2_KEY3,20,03,ZD                 


You can then normalize the keys to ZDF and you will get matching records for output.
Back to top
View user's profile Send private message
avashisht

New User


Joined: 16 Sep 2005
Posts: 11

PostPosted: Fri Jul 15, 2011 2:15 am    Post subject:
Reply with quote

I tried that, it does match the records, but then in the output my CH fields are messed-up. like.. ' XXXXXXXXXX' becomes '00000XXXXXXXXXX'. I dont want these zeroes appended at the start of my CH field.

Also, I was wondering if there is way in the same step for me to change the first key in file 1 to be changed in the resulting output from 001 to abc or 002 to xyz like that.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jul 15, 2011 2:38 am    Post subject:
Reply with quote

I'd try to help you more but I really don't understand the form of your "CH" values. For example, you show:

Code:

      00930669A
444444FFFFFFFFC
000000009306691


This is a very strange number as it appears to have leading blanks instead of leading zeros, and a C sign instead of an F sign for plus. If this is actually equivalent to 0009306691, then it must be normalized. If it is just 00930669A, then it doesn't have to be normalized.

JOINKEYS uses binary keys. If your CH keys all use the same conventions, then you don't have to normalize (that is, convert) the keys and the output will look the same as the input. But you said that using the keys without normalizing doesn't give you what you want. So you need to normalize the keys for JOINKEYS. If you want the output to look the same as the input, then you have to convert the normalized keys back to the form you want (or save the keys and restore them). You can use INREC, OUTREC or OUTFIL in SYSIN to change the fields any way you like after you've joined the records with REFORMAT.

Quote:
Also, I was wondering if there is way in the same step for me to change the first key in file 1 to be changed in the resulting output from 001 to abc or 002 to xyz like that.


You can do that with DFSORT's IFTHEN or CHANGE function. You'd have to be more specific about what you want to do exactly for me to show you how to do it.
Back to top
View user's profile Send private message
avashisht

New User


Joined: 16 Sep 2005
Posts: 11

PostPosted: Fri Jul 15, 2011 2:53 am    Post subject:
Reply with quote

Frank,

Thanks for your help. I actually followed your advice and converted the other 2 keys to ZDF and left the Alphanumeric key as it is.. That did the trick.

Thanks a lot for everyone's help. This was my first post and sorry if I wasn't clear about my questions. Will try to articulate it better next time. Thanks!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jul 15, 2011 2:59 am    Post subject:
Reply with quote

Ok.

It appears that your ZD keys are already normalized (they all have F for the plus sign), so you really don't need to convert them either.

Just FYI, if you wanted to convert the keys for JOINKEYS but keep them the same for output, you could save and restore them like this (I assumed your input file had RECFM=FB and LRECL=100):

Code:

//SORT03   EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//FILE1 DD  DSN=...  input file1
//FILE2 DD  DSN=...  input file2
//*                          CONTAINS FILES AND SNAP COMMANDS
//SYSIN    DD  *
   JOINKEYS F1=FILE1,FIELDS=(111,3,A,114,15,A,129,3,A)
   JOINKEYS F2=FILE2,FIELDS=(102,3,A,105,15,A,120,3,A)
   REFORMAT FIELDS=(F1:1,31,F2:23,5,F1:32,65)
   SORT FIELDS=COPY
/*
//JNF1CNTL DD *
  INCLUDE COND=(29,3,CH,EQ,C'059',OR,29,3,CH,EQ,C'077',OR,29,3,CH,EQ,
                C'300',OR,29,3,CH,EQ,C'304',OR,29,3,CH,EQ,C'306')
  INREC OVERLAY=(111:11,3,ZD,TO=ZDF,LENGTH=3,
                 114:14,15,ZD,TO=ZDF,LENGTH=15,
                 129:29,3,ZD,TO=ZDF,LENGTH=3)
//JNF2CNTL DD *
  INCLUDE COND=(20,3,CH,EQ,C'059',OR,20,3,CH,EQ,C'077',OR,20,3,CH,EQ,
                C'300',OR,20,3,CH,EQ,C'304',OR,20,3,CH,EQ,C'306')
  INREC OVERLAY=(102:2,3,ZD,TO=ZDF,LENGTH=3,
                 105:5,15,ZD,TO=ZDF,LENGTH=15,
                 120:20,3,ZD,TO=ZDF,LENGTH=3)
//SORTOUT  DD  DSN=... output file
Back to top
View user's profile Send private message
avashisht

New User


Joined: 16 Sep 2005
Posts: 11

PostPosted: Fri Jul 15, 2011 3:01 am    Post subject:
Reply with quote

Awesome!! Thats even better. Thanks a lot!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us