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 

JOINKEYS Issue on SORTING

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


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Fri Feb 19, 2010 3:44 pm    Post subject: JOINKEYS Issue on SORTING Reply with quote
I have a strange issue and I am still learning JOINKEYS, so please excuse me if this comes out to be silly mistake.

I ran this job below, and it returned no rows. To my surprise it ommitted records in JNF1CNTL and JNF2CNTL. It selected no records!!!

Next, I deleted JNF1CNTL and JNF2CNTL and but it didn't write any records from output of SUBTASK1 and SUBTASK2!!.

Now, When I ran the another job with the same file but with routine SORT Job (NO JOINKEYS), it did returned expected number of records.

In all above cases, my job always completed with RC=0.

Code:
//S1       EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTJNF1 DD DISP=SHR,DSN=TSOID.MY.INPUT.FL1(0)                     
//SORTJNF2 DD DISP=SHR,DSN=TSOID.MY.INPUT.FL2(0)               
//SORTOUT  DD  DSN=TSOID.MY.JOINED.FILE.TEST,
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=SYSDA,                                             
//             RECFM=FB                                                 
//SYSIN    DD *                                                         
  JOINKEYS FILE=F1,FIELDS=(016,05,A,                                   
                           131,03,A,                                   
                           134,03,A)                                   
  JOINKEYS FILE=F2,FIELDS=(255,05,A,                                   
                           228,03,A,
                           142,03,A)                                   
  REFORMAT FIELDS=(F1:016,05,
                   F1:031,03,
                   F1:034,02,
                   F1:312,01,
                   F2:307,03,
                   F2:384,09,
                   F2:394,01,
                   F2:395,01)
  OPTION COPY                                                           
/*                                                                     
//JNF1CNTL DD *                                                         
 INCLUDE COND=(121,01,CH,EQ,C'C',OR,121,01,CH,EQ,C'D')                 
//*                                                                     
//JNF2CNTL DD *                                                         
 INCLUDE COND=(139,01,CH,EQ,C'X',OR,129,01,CH,EQ,C'Y')                 
//*                                                                     


JNF1JMSG
Code:
ICE080I 0 IN MAIN STORAGE SORT                                                 
ICE055I 0 INSERT 0, DELETE 77947                                               
ICE054I 0 RECORDS - IN: 77947, OUT: 0                                           
ICE134I 0 NUMBER OF BYTES SORTED: 545629000                                     
ICE253I 0 RECORDS SORTED - PROCESSED: 77947, EXPECTED: 77947                   
ICE165I 0 TOTAL WORK DATA SET TRACKS ALLOCATED: 24570 , TRACKS USED: 0         
ICE199I 0 MEMORY OBJECT STORAGE USED = 522M BYTES                               
ICE180I 0 HIPERSPACE STORAGE USED = 0K BYTES                                   
ICE188I 0 DATA SPACE STORAGE USED = 0K BYTES                                   
ICE052I 0 END OF DFSORT                                                         


JNF2JMSG
Code:
ICE080I 0 IN MAIN STORAGE SORT                                                 
ICE055I 0 INSERT 0, DELETE 1169011                                             
ICE054I 0 RECORDS - IN: 1169011, OUT: 0                                         
ICE134I 0 NUMBER OF BYTES SORTED: 883772316                                     
ICE253I 0 RECORDS SORTED - PROCESSED: 1169011, EXPECTED: 1169041               
ICE165I 0 TOTAL WORK DATA SET TRACKS ALLOCATED: 24570 , TRACKS USED: 0         
ICE199I 0 MEMORY OBJECT STORAGE USED = 857M BYTES                               
ICE180I 0 HIPERSPACE STORAGE USED = 0K BYTES                                   
ICE188I 0 DATA SPACE STORAGE USED = 0K BYTES                                   
ICE052I 0 END OF DFSORT                                                         


SYSOUT
Code:
ICE235I 0 OPTIONS: NULLOUT=RC0                                                 
ICE084I 0 EXCP ACCESS METHOD USED FOR SORTOUT                                   
ICE751I 1 EF-K49535 F0-K49038 E8-K51707                                         
[u]ICE421I 0 JOINED RECORDS: COUNT=0 [/u]                                             
ICE055I 0 INSERT 0, DELETE 0                                                   
ICE054I 0 RECORDS - IN: 0, OUT: 0                                               
ICE173I 0 NO RECORDS FOR THE SORTOUT DATA SET - RC=0                           
ICE052I 0 END OF DFSORT                                                         



Sample JCL, NO JOINKEYS

Code:
//STEP0100 EXEC PGM=SORT                                               
//SYSOUT    DD SYSOUT=*                                                 
//SORTDIAG  DD SYSOUT=*                                                 
//SORTIN    DD DISP=SHR,DSN=TSOID.MY.INPUT.FL1(0)                   
//SORTOUT   DD DSN=TSOID.MY.OUTPUT.FL1,
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=SYSDA,                                             
//             RECFM=FB                                                 
//SYSIN     DD *                                                       
 INCLUDE COND=(121,01,CH,EQ,C'C',OR,121,01,CH,EQ,C'D')                 
 SORT FIELDS=COPY                                                       
//*
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Feb 19, 2010 4:10 pm    Post subject: Reply with quote
Quote:
To my surprise it ommitted records in JNF1CNTL and JNF2CNTL. It selected no records!!!


Are you assuming it selected no records based on the JNF1JMSG message:

ICE054I 0 RECORDS - IN: 77947, OUT: 0

If so, that's a bad assumption. For JOINKEYS the ICE054I message in JNFxJMSG will always show 0. Since the records are passed to an E35, not directly to an output file, the ICE054I shows all of the records as "deleted" by the E35 (=0 output records) regardless of how many were actually kept by the E35. But actually, the selected F1 and F2 records are passed to E35s and those that match between the two files are passed to the main task as joined records.

Since SYSOUT shows:

ICE421I 0 JOINED RECORDS: COUNT=0

I'd guess you do NOT have any matches on all of the keys you specified in JOINKEYS FIELDS for F1 and F2 for the selected records. So there weren't any records to actually join and the count of joined records is 0.

I tried your job with records in F1 and F2 that did match on all of the keys and I got a non-zero COUNT in the ICE421I message indicating records were joined. Those joined/reformatted records were written to SORTOUT.

Here's a portion of my //SYSOUT messages:

Code:

ICE411I 0 THIS IS THE JOINKEYS MAIN TASK FOR JOINING F1 AND F2                 
ICE416I 0 JOINKEYS IS USING THE F1 SUBTASK FOR SORTJNF1 - SEE JNF1JMSG MESSAGES
ICE416I 1 JOINKEYS IS USING THE F2 SUBTASK FOR SORTJNF2 - SEE JNF2JMSG MESSAGES
ICE419I 0 JOINED RECORDS: TYPE=F, LENGTH=25
...
ICE421I 0 JOINED RECORDS: COUNT=2                       
ICE090I 0 OUTPUT LRECL = 25, BLKSIZE = 25, TYPE = FB     
ICE055I 0 INSERT 2, DELETE 0                             
ICE054I 0 RECORDS - IN: 0, OUT: 2                       
ICE052I 0 END OF  DFSORT                                                                       


Here's a portion of my //JNF1JMSG messages:

Code:

ICE055I 0 INSERT 0, DELETE 2       
ICE054I 0 RECORDS - IN: 2, OUT: 0 


Note that ICE054I has OUT: 0 even though records were in fact joined and output.

If you think you have matches between F1 and F2 for all of the keys of the selected records, I'll show you a way to disprove that. Smile
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Fri Feb 19, 2010 4:20 pm    Post subject: Reply with quote
Frank,
That was my assumption. Sad
I thought (in my assumption of course), that
ICE054I 0 RECORDS - IN: 77947, OUT: 0 were probably introduced to help us debug any issues until I checked the message below. I should have done that earlier.

Code:
ICE054I :- For a JOINKEYS subtask, 0 is always displayed for OUT. The number of joined records is displayed in message ICE421I for the main task.


About my not finding any match, I checked both the files in File-Aid and they have plenty of records that matched on all 3 key fields. But since these is one of my first test jobs with new PTF, I would double check my job.

Meanwhile, I am curisous to know about the another way you mentioned to find out if I have match or not. Hopefully this will help to debug the issue.

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


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Fri Feb 19, 2010 4:25 pm    Post subject: Reply with quote
Never mind, I did UNPAIRED,F1,ONLY and it returned all the F1 records.

I will have to revisit my job/file/logic.

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Feb 19, 2010 4:49 pm    Post subject: Reply with quote
Quote:
About my not finding any match, I checked both the files in File-Aid and they have plenty of records that matched on all 3 key fields. But since these is one of my first test jobs with new PTF, I would double check my job.


Then I suspect your INCLUDE statements filtered out those matching records.

Quote:
Meanwhile, I am curisous to know about the another way you mentioned to find out if I have match or not. Hopefully this will help to debug the issue.


This is the verification job I had in mind. It uses SELECT rather than JOINKEYS for a better "sanity" test. If OUT is empty, it proves there are no matching selected records.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN1) TO(T1) ON(1,11,BI) FIRST USING(CTL1)
SELECT FROM(IN2) TO(T1) ON(1,11,BI) FIRST USING(CTL2)
SELECT FROM(T1) TO(OUT) ON(1,11,BI) ALLDUPS
/*
//CTL1CNTL DD *
  INCLUDE COND=(121,01,CH,EQ,C'C',OR,121,01,CH,EQ,C'D')
  INREC BUILD=(16,5,131,3,134,3)
/*
//CTL2CNTL DD *
  INCLUDE COND=(139,01,CH,EQ,C'X',OR,129,01,CH,EQ,C'Y')
  INREC BUILD=(255,5,228,3,142,3)
/*


Quote:
Never mind, I did UNPAIRED,F1,ONLY and it returned all the F1 records.

I will have to revisit my job/file/logic.


Ok. Keep us posted.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Sun Feb 21, 2010 12:38 am    Post subject: Reply with quote
Hi,
I took one of the keys from the comparison, which I thought was causing mis-match due to different data types and both the files matched after that!!!
Thanks for all the help.

However, while testing I got one more question.
As mentioned in my earlier post, I tried to find the mismatch causing key using UNPAIRED,F1,ONLY. So, I ran below mentioned sort control card...

Code:
  JOINKEYS FILE=F1,FIELDS=(016,05,A)                                   
  JOINKEYS FILE=F2,FIELDS=(255,05,A)
  JOIN UNPAIRED,F1,ONLY                               
  REFORMAT FIELDS=(F1:016,05)
  OPTION COPY                                         
  OUTFIL NODETAIL,REMOVECC,                           
  TRAILER1=('RECORDS COUNT',COUNT=(M11,LENGTH=10))


This gave me error as below..

Code:
ICE419I 0 JOINED RECORDS: TYPE=F, LENGTH=5                               
ICE201I G RECORD TYPE IS F - DATA STARTS IN POSITION 1                   
ICE230A 0 23 BYTE HEADER/TRAILER RECORD EXCEEDS 5 BYTE LRECL FOR SORTOUT 


Per my understanding...After the join maintask creates file of LRECL based on REFORMAT FIELDS but TRAILER1 function
can't increase LRECL of the file but can actually shrink the file.

I went ahead and added Text using INREC BUILD as below.

Code:
JOINKEYS FILE=F1,FIELDS=(016,05,A)   
JOINKEYS FILE=F2,FIELDS=(255,05,A)   
JOIN UNPAIRED,F1,ONLY               
REFORMAT FIELDS=(F1:016,05)         
INREC BUILD=(C'            ',1,05)   
SORT FIELDS=COPY                     
OUTFIL NODETAIL,REMOVECC,           
TRAILER1=(COUNT=(M11,LENGTH=08))


Job ran successfully and gave me messages as below..

Code:
ICE419I 0 JOINED RECORDS: TYPE=F, LENGTH=15 
some more messages ....not displayed here...
Code:
ICE210I 0 SORTOUT  : BSAM USED, LRECL = 17, BLKSIZE = 17, TYPE = FB 
ICE421I 0 JOINED RECORDS: COUNT=356091                     
ICE055I 0 INSERT 356091, DELETE 0                           
ICE054I 0 RECORDS - IN: 0, OUT: 356091                     
ICE227I 0 SORTOUT  : DELETED = 356091, REPORT = 1, DATA = 0
ICE228I 0 SORTOUT  : TOTAL IN = 356091, TOTAL OUT = 1       
ICE174I 0 NO DATA RECORDS FOR AN OUTFIL DATA SET - RC=0     
ICE052I 0 END OF DFSORT                                     


SORTOUT Display :- 00356091

I would now change INREC BUILD with INREC OVERLAY and it worked too. So it it working with increased LRECL.

Now, I went back and ran my DFSort COUNT Job (No JOINKEYS) for which Input File's LRECL is 1 Byte only.
My job is as mentioned below and it abended with the same reason. SYSOUT given below.

Code:
//SORT01  EXEC PGM=SORT                                 
//SORTIN DD DISP=SHR,DSN=TSOID.IN.FILE1.RECLEN1   
//SORTOUT DD SYSOUT=*                                   
//SYSIN DD *                                             
 OPTION COPY                                             
 OUTFIL NODETAIL,REMOVECC,                               
 TRAILER1=('RECORDS COUNT',COUNT=(M11,LENGTH=12))       
//SYSOUT DD SYSOUT=*                                     


SYSOUT Messages
Code:
           OPTION COPY                                                       
           OUTFIL NODETAIL,REMOVECC,                                         
           TRAILER1=('RECORDS COUNT',COUNT=(M11,LENGTH=12))                   
ICE201I G RECORD TYPE IS F - DATA STARTS IN POSITION 1                       
ICE230A 0 25 BYTE HEADER/TRAILER RECORD EXCEEDS 1 BYTE LRECL FOR SORTOUT     
ICE751I 0 C5-K51707 C6-K51707 C7-K51707 C8-K51707 E9-K51707 E7-K51707         
ICE052I 3 END OF DFSORT                                                       


This may be my lack of understanding but does this mean, TRAILER1 can't actually increase the file size but
it can reduce it? Meaning, I can create 10 byte count output file from 80 byte input file but not vice versa?

Does it assume that the output file created with TRAILER1 is always less than input file but the reverse is not true?



I am still to test/learn NULL INDICATOR of DFSort but this is really interesting stuff.

Apologies for the long response.

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Mon Feb 22, 2010 1:38 pm    Post subject: Reply with quote
The length of the output file is determined by the data records, not the report records. The length of the data records must be greater than or equal to the length of the report records. If you want your TRAILER1 length to be 25 bytes, then your data records must be at least 25 bytes. An easy way to make this happen is to add an OVERLAY or BUILD operator to increase the length of the data records. For example:

Code:

     OPTION COPY                                                       
     OUTFIL NODETAIL,REMOVECC,
        OVERLAY=(25:X),                                         
        TRAILER1=('RECORDS COUNT',COUNT=(M11,LENGTH=12))   


The doc for the ICE230A message explains this.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
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