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
 
Two Syncsort joinkeys in the same step

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: Two Syncsort joinkeys in the same step
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: 2285
Location: @my desk

PostPosted: Wed Mar 18, 2009 12:00 am    Post subject:
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    Post subject:
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: 2285
Location: @my desk

PostPosted: Wed Mar 18, 2009 2:02 am    Post subject:
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    Post subject:
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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 18, 2009 9:41 am    Post subject:
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: 2285
Location: @my desk

PostPosted: Wed Mar 18, 2009 12:31 pm    Post subject:
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    Post subject:
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: 3158
Location: Tucson AZ

PostPosted: Sat Mar 28, 2009 8:16 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Weirdo results with JOINKEYS Balaryan DFSORT/ICETOOL 4 Wed Oct 18, 2017 9:04 pm
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am
No new posts JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts it is possible output a SET value fro... Martylin JCL & VSAM 5 Fri Sep 22, 2017 7:08 am
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