IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Two Syncsort joinkeys in the same step
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
joseph murphy

New User


Joined: 24 Nov 2008
Posts: 17
Location: USA
PostPosted: Tue Mar 17, 2009 11:47 pm
 Reply with quote
Code:

//SORT1 EXEC PGM=SYNCSORT
//SORTJNF1 DD DISP=SHR,DSN=your.input.file1
//SORTJNF2 DD DISP=SHR,DSN=your.input.file2
//SORTOF01 DD DSN=UNMATCHED.RECORDS.FILE1,DISP=(NEW,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SORTOF02 DD DSN=UNMATCHED.RECORDS.FILE2,DISP=(NEW,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SORTOF03 DD DSN=MATCHED.RECORDS,DISP=(NEW,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
  JOINKEYS FILES=F1,FIELDS=(20,5,A)
  JOINKEYS FILES=F2,FIELDS=(20,5,A)
  REFORMAT FIELDS=(F1:1,360,F2:1,360),FILL=X'FF'
  JOIN UNPAIRED
  SORT FIELDS=COPY
  OUTFIL FILES=01,INCLUDE=(361,1,BI,EQ,X'FF'),OUTREC=(1,360)
  OUTFIL FILES=02,INCLUDE=(1,1,BI,EQ,X'FF'),OUTREC=(361,360)
  OUTFIL FILES=03,INCLUDE=(1,1,BI,NE,X'FF',AND,361,1,CH,NE,X'FF')


In the above code, can I use another join keys like

JOINKEYS FILES=F1,FIELDS=(20,5,A,75,5,A)

JOINKEYS FILES=F2,FIELDS=(20,5,A,75,5,A)


I tried ,but it didn't work, I need the solution.
I have to use two keys in the same step,is it possible?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Mar 18, 2009 12:00 am
 Reply with quote
Quote:
I tried ,but it didn't work, I need the solution
Can you explain this?. Did your job fail? If yes, post the SYSOUT messages. If no, post the output you are getting and the expected output.

Quote:
In the above code, can I use another join keys like

JOINKEYS FILES=F1,FIELDS=(20,5,A,75,5,A)

JOINKEYS FILES=F2,FIELDS=(20,5,A,75,5,A)
Yes you can.
Back to top
View user's profile Send private message
joseph murphy

New User


Joined: 24 Nov 2008
Posts: 17
Location: USA
PostPosted: Wed Mar 18, 2009 12:19 am
 Reply with quote
RC=0,but output file1 is having all the rows as unmatched,because of the second key field and

output file 2 is having zero length record that is no match rows from F1&F2,this is not a correct result,i tried as a single join it is working,but i want to know can i use two keyfields like a normal join ?

Code:
//SORT1 EXEC PGM=SYNCSORT
//SORTJNF1 DD DISP=SHR,DSN=your.input.file1
//SORTJNF2 DD DISP=SHR,DSN=your.input.file2
//SORTOF01 DD DSN=UNMATCHED.RECORDS.FILE1,DISP=(NEW,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
  JOINKEYS FILES=F1,FIELDS=(20,5,A,75,5,A)
  JOINKEYS FILES=F2,FIELDS=(20,5,A,75,5,A)
  REFORMAT FIELDS=(F1:1,360,F2:1,360)
    SORT FIELDS=COPY
  OUTFIL FILES=01,OUTREC=(1,360)


this is possible i know,

but in the above code we r getting,matching rows of F1&F2 and F1 unmatched,F2 unmatched,is this possible in this case............
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Mar 18, 2009 2:02 am
 Reply with quote
Quote:
can i use two keyfields like a normal join ?
As I have already told you, it IS possible.
Quote:
RC=0,but output file1 is having all the rows as unmatched,because of the second key field and

output file 2 is having zero length record that is no match rows from F1&F2,this is not a correct result,i tried as a single join it is working,but i want to know can i use two keyfields like a normal join ?
I really have no clues on what exactly is your problem here. What do you mean by 'zero length record'? And how do you know that the problem is due to the second key field? icon_rolleyes.gif
Back to top
View user's profile Send private message
joseph murphy

New User


Joined: 24 Nov 2008
Posts: 17
Location: USA
PostPosted: Wed Mar 18, 2009 9:01 am
 Reply with quote
If i am comapring the files using single key values i am getting the correct answer,if i use the double key values i am not getting the correct answer.

JOINKEYS FILES=F1,FIELDS=(20,5,A,75,5,A)
JOINKEYS FILES=F2,FIELDS=(20,5,A,75,5,A) -not working

JOINKEYS FILES=F1,FIELDS=(20,5,A)
JOINKEYS FILES=F2,FIELDS=(20,5,A)-this is working fine

JOINKEYS FILES=F1,FIELDS=(75,5,A)
JOINKEYS FILES=F2,FIELDS=(75,5,A)-this is also working fine,

can u understand the exact problem,i hope u will send the solution
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Wed Mar 18, 2009 9:41 am
 Reply with quote
Hello,

It may help if you post the complete informational output from the execution of a join that works correctly and the one that is giving the undesired output.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Mar 18, 2009 12:31 pm
 Reply with quote
Adding to what Dick has asked above,

It would be better if you post a few sample input records, the expected output and the actual output you are getting currently. It would n't be proper to offer suggestions/solutions without really knowing what exactly is your requirement/problem.
Back to top
View user's profile Send private message
joseph murphy

New User


Joined: 24 Nov 2008
Posts: 17
Location: USA
PostPosted: Sat Mar 28, 2009 2:57 pm
 Reply with quote
//SORT1 EXEC PGM=SORT
//SORTJNF1 DD DISP=SHR,DSN=your.input.file1
//SORTJNF2 DD DISP=SHR,DSN=your.input.file2
//SORTOF01 DD DSN=UNMATCHED.RECORDS.FILE1,DISP=(NEW,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SORTOF02 DD DSN=UNMATCHED.RECORDS.FILE2,DISP=(NEW,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SORTOF03 DD DSN=MATCHED.RECORDS,DISP=(NEW,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(20,5,A)
JOINKEYS FILES=F2,FIELDS=(20,5,A)
REFORMAT FIELDS=(F1:1,360,F2:1,360),FILL=X'FF'
JOIN UNPAIRED
SORT FIELDS=COPY
OUTFIL FILES=01,INCLUDE=(361,1,BI,EQ,X'FF'),OUTREC=(1,360)
OUTFIL FILES=03,INCLUDE=(1,1,BI,NE,X'FF',AND,361,1,CH,NE,X'FF')

FILE-1

emp-id name ORGN_ID
------- -------- ---------
100 JACK 100
101 ROSE 101
102 PINKY 102
103 VERO 103
104 JOE 104

FILE-2
emp-id ORGN_ID JOB-TITLE
-------- ----------- ----------
100 100 SE
101 101 ANALYST
102 102 TL

JOINKEYS FILES=F1,FIELDS=(1,3,A,9,3,A)
JOINKEYS FILES=F2,FIELDS=(1,3,A,4,3,A)

EXPECTED RESULT
-------------------

MATCHED
----------
100 100 SE
101 101 ANALYST
102 102 TL

UNMATCHES F1
---------------
103
104

how to code to get this result,i tried,but if i use second key fields i am not getting the result,

f1 unmatches file(f1)
--------------
all the rows from f1 file

f1&f2 match file(f2)
----------------
no rows,empty data set

but if i use single key fields like

JOINKEYS FILES=F1,FIELDS=(1,3,A)
JOINKEYS FILES=F2,FIELDS=(1,3,A)

i am getting the result,my question is cant we use two key fields to get matched and unmatched files?
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
PostPosted: Sat Mar 28, 2009 8:16 pm
 Reply with quote
Your alignment stinks........
Code:
FILE-1

emp-id       name           ORGN_ID
-------      --------         ---------
100           JACK               100
101           ROSE              101
102           PINKY            102
103           VERO             103
104           JOE                104

FILE-2
     emp-id                  ORGN_ID        JOB-TITLE
     --------              -----------         ----------
            100                       100              SE
            101                      101               ANALYST
            102                     102                TL

JOINKEYS FILES=F1,FIELDS=(1,3,A,9,3,A)
JOINKEYS FILES=F2,FIELDS=(1,3,A,4,3,A)           
 
EXPECTED RESULT
-------------------
   
MATCHED
----------
100                       100              SE
101                      101               ANALYST
102                     102                TL

UNMATCHES F1
---------------
103
104

SyncSort for z/OS 1.3 Programmer’s Guide wrote:
The number of JOINKEYS fields and their lengths and sorted order (A or D) must be the same for both files, although their starting positions need not be the same.
The maximum number of JOINKEYS fields is 64 and all JOINKEYS fields will be treated as binary, though bit fields are not permitted.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
PostPosted: Sat Mar 28, 2009 8:23 pm
 Reply with quote
Joseph,
Please learn to use BBCode (see FAQ).
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Return codes-Normal & Abnormal te... JCL & VSAM 7
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts PD not working for unsigned packed JO... DFSORT/ICETOOL 5
No new posts Def PD not working for unsigned packe... JCL & VSAM 3
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us