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

JOINKEY statistics in SPOOL


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Wed Apr 11, 2012 10:17 am
Reply with quote

Is there any option or command (built in) in SORT to get this info in the spool as a separate sysout?

File1 records read count
File2 records read count
Matched records read count
Unmatched record count only in file1
Unmatched record count only in file2
Number of records written in o/p file.

Tried to find this in manual/web, but no luck...
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Apr 11, 2012 2:12 pm
Reply with quote

abdul.faras wrote:
Is there any option or command (built in) in SORT to get this info in the spool as a separate sysout?

File1 records read count
File2 records read count
Matched records read count
Unmatched record count only in file1
Unmatched record count only in file2
Number of records written in o/p file.

Tried to find this in manual/web, but no luck...


Do you mean that you have a need of these figures for a particular requirement, or in general?

What are "Matched records read"?
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Wed Apr 11, 2012 2:23 pm
Reply with quote

Bill Woodger wrote:

Do you mean that you have a need of these figures for a particular requirement, or in general?

What are "Matched records read"?


I need it just as a statistics in the spool, in case if business asks for todays stats.

"Matched records read" is a typo. Its "Matched records count".
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Apr 11, 2012 2:41 pm
Reply with quote

You can get counts in your Sysout. Can you post a sample of your sysout from a Joinkeys application.
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Wed Apr 11, 2012 3:20 pm
Reply with quote

I was not able to attach this as notepad, so pasting here:

Code:

1ICE411I 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=722                                                                           
 ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1                                                                 
 ICE162I 0 ICEIEXIT CHANGED ONE OR MORE OPTIONS IN EFFECT                                                               
 ICE751I 0 C5-K62149 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE   E5-K70685 E7-K70685                               
 ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                                                         
 ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE                                 
 ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 07:49 ON MON APR 09, 2012 -                           
0            JOINKEYS FILE=F1,FIELDS=(1,6,A),SORTED,NOSEQCK                        00260015                             
             JOINKEYS FILE=F2,FIELDS=(176,6,A),SORTED,NOSEQCK                      00270015                             
             JOIN UNPAIRED,F1                                                      00280015                             
             REFORMAT FIELDS=(F1:1,700,F2:176,6,21,1,91,14,?)                      00290015                             
             OPTION COPY                                                           00300015                             
                   INREC IFTHEN=(WHEN=(722,1,CH,EQ,C'B'),                          00310015                             
                                 OVERLAY=(524:708,14,ZD,MUL,+100,TO=PD,LENGTH=8),  00320015                             
                                 HIT=NEXT),                                        00321015                             
                         IFTHEN=(WHEN=(722,1,CH,EQ,C'1'),                          00330015                             
                                 OVERLAY=(524:X'000000000000000C'),                00340015                             
                                 HIT=NEXT),                                        00341015                             
                         IFTHEN=(WHEN=((707,1,CH,EQ,C'Y',AND,722,1,CH,EQ,C'B'),    00350015                             
                                       OR,722,1,CH,EQ,C'1'),                       00360015                             
                                 OVERLAY=(532:X'000000000000000C'),                00370015                             
                                 HIT=NEXT),                                        00371015                             
                         IFTHEN=(WHEN=(707,1,CH,NE,C'Y',AND,722,1,CH,EQ,C'B'),     00380015                             
                                 OVERLAY=(532:708,14,ZD,MUL,+100,TO=PD,LENGTH=8))  00390015                             
                   OUTREC BUILD=(1,700)                                            00400015                             
 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=722                                                                           
 ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1                                                                 
 ICE162I 0 ICEIEXIT CHANGED ONE OR MORE OPTIONS IN EFFECT                                                               
 ICE751I 0 C5-K62149 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE   E5-K70685 E7-K70685                               
 ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED                                         
 ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS                                                 
 ICE089I 0 BRYYY42J.PS060   .        , INPUT LRECL = 722, TYPE = F                                                       
 ICE093I 0 MAIN STORAGE = (MAX,6291456,6270976)                                                                         
 ICE156I 0 MAIN STORAGE ABOVE 16MB = (6207942,6207942)                                                                   
 ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256           
 ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT                   
 ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG     
 ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2                         
 ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0                                 
 ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=N,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N           
 ICE133I 0 OPTIONS: HIPRMAX=0      ,DSPSIZE=0   ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=0                           
 ICE235I 0 OPTIONS: NULLOUT=RC0                                                                                         
 ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y                                                                                 
 ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT                                                                           
 ICE751I 1 EF-BASE   F0-K66717 E8-K70685                                                                                 
 ICE421I 0 JOINED RECORDS: COUNT=5804156                                                                                 
 ICE090I 0 OUTPUT LRECL = 700, BLKSIZE = 32200, TYPE = FB                                                               
 ICE055I 0 INSERT 5804156, DELETE 0                                                                                     
 ICE054I 0 RECORDS - IN: 0, OUT: 5804156                                                                                 
 ICE052I 0 END OF DFSORT
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Apr 11, 2012 3:55 pm
Reply with quote

We are glad you weren't able to attach. Attachments tend to be frowned-upon.

Paste and in the Code tags is how we like them. Thank you.
Code:

 ICE421I 0 JOINED RECORDS: COUNT=5804156                                                                                 
 ICE090I 0 OUTPUT LRECL = 700, BLKSIZE = 32200, TYPE = FB                                                               
 ICE055I 0 INSERT 5804156, DELETE 0                                                                                     
 ICE054I 0 RECORDS - IN: 0, OUT: 5804156                                                                                 


Firstly, I may have appeared too "literal". When I said "sysout" I meant "sysout=whatever", not just the SYSOUT DD. You have counts of the two input files in your other two message datasets.

The thing then is, how do you break-down the "joined" count. I'm not sure (but I will be corrected) if this can be done in a "useful" manner by default, the reason being the potential, as far as JOINKEYS is concerned, for duplicates on either, or both, input files.

So, if you want a breakdown of the joined data, you have to do it yourself, with knowledge of your data.

While we are here:

Is your output going to tape?

Your sort cards could benefit from a bit of rationalisation.
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Wed Apr 11, 2012 4:10 pm
Reply with quote

Thats what I was looking for, some inbuilt feature or option in sort.
I know, we can get i/p file record count in the JNF1JMSG and JNF2JMSG, but not the matched and o/p file write count.

No, the output is not going to tape, can still it be benefitted with rationalisation?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Apr 11, 2012 4:20 pm
Reply with quote

Yes, the two things are not connected.

Your output seems, then, to have its own blocksize specified. 32,000 for a 3390 disk is not good, as it will leave a huge lump of each track empty. Either let DFSORT do the blocksize (best) or calculate a good blocksize manually.

The rationalisation of the sort cards revolves around the presence or otherwise of an actual match. The key from file 2 you do not need on the reformat record: it is either not present, or it is the same as on the F1. You test a flag extracted from F2, which only makes sense for a B, yet you also test for 1. The results will be the same, but things are confusing to the reader.

If you want some review of the cards, please provide the business description of the process (not just write down what the current cards are doing).

Back to your original question, input to the sub-task, input to the main task, output from the main task, including OUTFIL processing, all of those you have.

The breakdown of the matching process, you'd have to do yourself, I think.

Can there be duplicates on either input dataset?
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Wed Apr 11, 2012 5:26 pm
Reply with quote

No, there are no duplicates.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Apr 11, 2012 6:45 pm
Reply with quote

Code:
  OUTFIL FILES=F1,INCLUDE=(12,1,CH,EQ,C'1'),
         BUILD=(1,1),
         REMOVECC,NODETAIL

OUTREC BUILD=(1,700) 


You can try something like this to get you going.

You'll need SORTOFF1 in your JCL, I'd make it a DUMMY as nothing should get on to it.

The idea is to include all the the "1" values from the join marker, but then, with NODETAIL, to remove them from the output dataset. You will see the count for these in the SYSOUT dataset. You should then have the information you need to know all the figures, I think.

EDIT: Forgot to say, check back later/tomorrow for the best answers/solution from San Jose.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Thu Apr 12, 2012 11:10 am
Reply with quote

Hi,

here is one way of getting the results you are after in a more readable format
Code:
//STEP0100 EXEC PGM=SORT                           
//SYSOUT   DD SYSOUT=*                             
//INA      DD *                                     
AF1ONLY                                             
1F1ONLY                                             
2BOTH                                               
3F1ONLY                                             
4BOTH                                               
5BOTH                                               
/*                                                 
//INB      DD *                                     
2BOTH                                               
4BOTH                                               
5BOTH                                               
9F2ONLY                                             
CF2ONLY                                             
/*                                                 
//SORTOUT  DD SYSOUT=*                             
//TOTALS   DD SYSOUT=*                             
//SYSIN    DD *                                     
  JOINKEYS F1=INA,FIELDS=(1,1,A)                   
  JOINKEYS F2=INB,FIELDS=(1,1,A)                             
  JOIN UNPAIRED                                               
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                         
  OUTREC IFTHEN=(WHEN=(161,1,CH,EQ,C'B'),                     
         OVERLAY=(162:C'F1=000001 BO=000001 F2=000001')),     
         IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),                     
         OVERLAY=(162:C'F1=000001',192:C'F1O=000001')),       
         IFTHEN=(WHEN=(161,1,CH,EQ,C'2'),                     
         OVERLAY=(182:C'F2=000001',203:C'F2O=000001'))       
  SORT FIELDS=(161,1,BI,A)                                   
  OUTFIL FNAMES=SORTOUT,                                     
    INCLUDE=(161,1,SS,EQ,C'1,B'),BUILD=(1,80)                 
  OUTFIL FNAMES=TOTALS,REMOVECC,NODETAIL,                     
 TRAILER1=(C'          FILE1 REC COUNT=',                     
           27:TOT=(165,6,ZD,EDIT=(TTTTTT)),/,                 
*                                                             
           C'          FILE2 REC COUNT=',                     
           27:TOT=(185,6,ZD,EDIT=(TTTTTT)),/,         
*                                                     
           C'        MATCHED REC COUNT=',             
           27:TOT=(175,6,ZD,EDIT=(TTTTTT)),/,         
*                                                     
   C'UNMATCHED FILE1 REC COUNT=',                     
           27:TOT=(196,6,ZD,EDIT=(TTTTTT)),/,         
*                                                     
   C'UNMATCHED FILE2 REC COUNT=',                     
           27:TOT=(207,6,ZD,EDIT=(TTTTTT)),/,         
*                                                     
   C'        OUTPUT FILE COUNT=',                     
           27:TOT=(165,6,ZD,EDIT=(TTTTTT)))           
/*                                                   

This will only work when there are no duplicates in either file.

Also I've used two 80 byte files as input and the key is 1 byte in pos 1.



Gerry
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Thu Apr 12, 2012 4:23 pm
Reply with quote

@Bill, Gerry: Thanks a ton for the solutions provided.

San Jose / anyone from DFSORT team, is there any other solution?
Is there any built-in function in SORT available?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Thu Apr 12, 2012 11:13 pm
Reply with quote

Well, as they say - there are lies, damn lies and statistics.

I'm not sure what statistics exactly you're looking for or whether they make sense for every type of JOINKEYS operation (e.g. cartesian join).

Please show an example of the records in your input files for the cases you are interested in (dups in file1? dups in file2?) and show what you would expect the statistics to show (the actual numbers for your input examples). That might help you to figure out if what you want is actually meaningful. It would certainly help me figure out what you're trying to do exactly.
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Fri Apr 13, 2012 11:10 am
Reply with quote

Frank,
Thanks for the reply.

Let me give you some background. There are few old COBOL programs witch are doing file matching logic on very huge files, and consuming lots of CPU. So to reduce the CPU usage, I am trying to incarporate same logic in JOINKEY.
Now, cobol has displays of stats in SYSOUT and that is the reason I am looking for same in SORT.

Input & output files are FB.
There are no duplicates in both the files.
Number of records are around 5 million.
LRECL are as follows:
I/P 1- 700
I/P 2 - 200
O/P - 700 (same as I/P)

Sort Card:
Code:

JOINKEYS FILE=F1,FIELDS=(1,6,A),SORTED,NOSEQCK                       
JOINKEYS FILE=F2,FIELDS=(176,6,A),SORTED,NOSEQCK                     
JOIN UNPAIRED,F1                                                     
REFORMAT FIELDS=(F1:1,700,F2:176,6,21,1,91,14,?)                     
OPTION COPY                                                         
      INREC IFTHEN=(WHEN=(722,1,CH,EQ,C'B'),                         
                    OVERLAY=(524:708,14,ZD,MUL,+100,TO=PD,LENGTH=8),
                    HIT=NEXT),                                       
            IFTHEN=(WHEN=(722,1,CH,EQ,C'1'),                         
                    OVERLAY=(524:X'000000000000000C'),               
                    HIT=NEXT),                                       
            IFTHEN=(WHEN=((707,1,CH,EQ,C'Y',AND,722,1,CH,EQ,C'B'),   
                          OR,722,1,CH,EQ,C'1'),                     
                    OVERLAY=(532:X'000000000000000C'),               
                    HIT=NEXT),                                       
            IFTHEN=(WHEN=(707,1,CH,NE,C'Y',AND,722,1,CH,EQ,C'B'),   
                    OVERLAY=(532:708,14,ZD,MUL,+100,TO=PD,LENGTH=8))
      OUTREC BUILD=(1,700)                                           


My requirement is to get below stats in sysout:

File1 records read count -
File2 records read count -
Matched records count -
Unmatched record count only in file1 -
Unmatched record count only in file2 -
Number of records written in o/p file -
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Apr 13, 2012 12:37 pm
Reply with quote

Make sure you have addressed Frank's last paragraph.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Apr 13, 2012 10:28 pm
Reply with quote

Use the following DFSORT JCL which will give you the desired results

Code:

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//INA      DD DSN=Your Input File1,DISP=SHR
//INB      DD DSN=Your Input File2,DISP=SHR
//SORTOUT  DD SYSOUT=*                                         
//RPT      DD SYSOUT=*                                         
//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(001,6,A),SORTED,NOSEQCK             
  JOINKEYS F2=INB,FIELDS=(001,6,A),SORTED,NOSEQCK             
  JOIN UNPAIRED                                               
  REFORMAT FIELDS=(F1:1,700,F2:7,9,?)                         
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(712:C'000',X,C'00')),       
  IFTHEN=(WHEN=(710,1,CH,EQ,C'1'),OVERLAY=(712:C'1',716:C'1')),
  IFTHEN=(WHEN=(710,1,CH,EQ,C'2'),OVERLAY=(713:C'1',717:C'1')),
  IFTHEN=(WHEN=(710,1,CH,EQ,C'B'),OVERLAY=(712:C'111'))       

                                                                 
  OUTFIL IFOUTLEN=700,INCLUDE=(710,1,SS,EQ,C'1,B'),               
  IFTHEN=(WHEN=(710,1,CH,EQ,C'B'),OVERLAY=(524:701,8),HIT=NEXT), 
                                                                 
  IFTHEN=(WHEN=(710,1,CH,EQ,C'1'),                               
  OVERLAY=(524:+0,TO=PD,LENGTH=8,532:+0,TO=PD,LENGTH=8),HIT=NEXT),
                                                                 
  IFTHEN=(WHEN=(709,2,CH,EQ,C'YB'),                               
  OVERLAY=(532:+0,TO=PD,LENGTH=8),HIT=NEXT),                     
  IFTHEN=(WHEN=(709,1,CH,NE,C'Y',AND,710,1,CH,EQ,C'B'),           
  OVERLAY=(532:701,8))                                           
                                                                 
  OUTFIL FNAMES=RPT,REMOVECC,NODETAIL,BUILD=(80X),               
  TRAILER1=('FILE1 RECORDS READ COUNT              : ',           
            TOT=(712,1,ZD,M11,LENGTH=8),/,                       
            'FILE2 RECORDS READ COUNT              : ',           
            TOT=(713,1,ZD,M11,LENGTH=8),/,                       
            'MATCHED RECORDS COUNT                 : ',           
            TOT=(714,1,ZD,M11,LENGTH=8),/,                       
            'UNMATCHED RECORD COUNT ONLY IN FILE1  : ',           
            TOT=(716,1,ZD,M11,LENGTH=8),/,                       
            'UNMATCHED RECORD COUNT ONLY IN FILE2  : ',           
            TOT=(717,1,ZD,M11,LENGTH=8),/,                       
            'NUMBER OF RECORDS WRITTEN IN O/P FILE : ',           
            TOT=(712,1,ZD,M11,LENGTH=8))                         
//*
//JNF2CNTL DD *                                                   
  INREC BUILD=(176,6,91,14,ZD,MUL,+100,PD,LENGTH=8,21,1)         
//*


The statistics are written to the RPT DD
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Mon Apr 16, 2012 12:08 pm
Reply with quote

Thanks a lot Kolusu.

I got the result by some other way, yours seems to be more easy. icon_smile.gif
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts DB2 Statistics - Using EXPLAIN and qu... DB2 1
No new posts Search string in job at regular Spool... CLIST & REXX 0
No new posts ICETOOL with JOINKEY for Big record l... DFSORT/ICETOOL 12
No new posts Capturing logs from spool dd JCL & VSAM 6
No new posts Extracting Compression Statistics fro... PL/I & Assembler 2
Search our Forums:

Back to Top