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 

use of multiple sections and trailer3

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


Joined: 15 Jan 2016
Posts: 20
Topics: 7
PostPosted: Fri Apr 15, 2016 3:21 am    Post subject: use of multiple sections and trailer3 Reply with quote
What would be the optimal code to count 2 different values in a file, like :
Input
Code:

1-5  6-75
-------------
A1   VALUE1
A1   VALUE1
A1   VALUE1
A1   VALUE2
A1   VALUE2
A1   VALUE3
A1   VALUE3
A1   VALUE3
A1   VALUE3
A1   VALUE3
A2   INPUT1
A2   INPUT2
A2   INPUT2
A2   INPUT3
A2   UUUUUU
A2   UUUUUU
A2   UUUUUU
A2   UUUUUU
A2   UUUUUU
A2   UUUUUU

Output
Code:

1-5   6-75    81-88  89-96
-------------------------------
A1   VALUE1    10    3
A1   VALUE2    10    2
A1   VALUE3    10    5
A2   INPUT1    10    1
A2   INPUT2    10    2
A2   INPUT3    10    1
A2   UUUUUU    10    6

I tried :
Code:

//SYSIN    DD *                                                 
 SORT FIELDS=(1,5,CH,A,6,75,CH,A)                               
   OUTFIL REMOVECC,NODETAIL,                                   
    BUILD=(1,80,96:X),         * WE GO FROM LRECL=80 TO LRECL=96
    SECTIONS=(1,5,6,75,                                         
      TRAILER3=(1:1,5,6,75,                                     
               81:COUNT=(EDIT=(TTTTTTTT)),                     
               89:COUNT=(EDIT=(TTTTTTTT))))                     
/*                                                             

But it shows twice the same count. Kind Regards, Gerd.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Fri Apr 15, 2016 10:13 am    Post subject: Reply with quote
Gerd Hofmans,

You cannot have multiple key break counts with the SECTIONS. So you need to use the trick of JOINKEYS where you read the same file for both INA and INB and use the second file to generate the key count for your primary key. Once you got the primary key count then you can use the sections to generate the key counters.

Is your data already sorted? If so you don't need to use SORT FIELDS=(1,5,CH,A,6,75,CH,A) on the main task.

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

Code:

//STEP0100 EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//INA      DD DISP=SHR,DSN=Your input fb 80 byte file
//INB      DD DISP=SHR,DSN=Same input fb 80 byte file
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  JOINKEYS F1=INA,FIELDS=(1,5,A),SORTED,NOSEQCK                     
  JOINKEYS F2=INB,FIELDS=(1,5,A)                                     
  REFORMAT FIELDS=(F1:1,80,F2:6,4)
                                   
  SORT FIELDS=(1,5,CH,A,6,75,CH,A)                                   
  OUTREC OVERLAY=(81:81,4,PD,EDIT=(TTTTTTTT))                       

  OUTFIL REMOVECC,NODETAIL,                                         
  BUILD=(96X),                  * WE GO FROM LRECL=80 TO LRECL=96   
  SECTIONS=(01,80,                                                   
  TRAILER3=(01:01,88,                                               
            89:COUNT=(EDIT=(TTTTTTTT))))                             
//*       
//JNF2CNTL DD *                                             
  INREC BUILD=(1,5,             * PRIMARY KEY               
               X'0000001C')     * VALUE OF 1 IN PACKED FORMAT
                                                             
  SUM FIELDS=(6,4,PD)
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Gerd Hofmans
Beginner


Joined: 15 Jan 2016
Posts: 20
Topics: 7
PostPosted: Tue Apr 19, 2016 4:48 am    Post subject: Reply with quote
Thanks Kolusu!
After some testing, i decided to split the operations into 2 steps because in 1 step it was taking too much CPU (it's a verly large file, and i start with splitting it into 30 subfiles of > 90Million records each). I did this :
Code:

//COUNT1   EXEC PPSORT                                               
//SORTIN   DD DISP=(SHR,PASS),DSN=&&INRT01                           
//OUT1     DD DISP=(NEW,PASS),DSN=&&OURT1,LRECL=38                   
//OUT2     DD DISP=(NEW,PASS),DSN=&&OURT2,LRECL=9                   
//SYSIN DD *                                                         
  SORT FIELDS=(1,5,CH,A,6,29,CH,A)                                   
  OUTFIL REMOVECC,NODETAIL,FNAMES=OUT1,                             
         BUILD=(38X),                                               
         SECTIONS=(1,34,                                             
         TRAILER3=(1,34,COUNT=(TO=PD,LENGTH=4)))                     
  OUTFIL REMOVECC,NODETAIL,FNAMES=OUT2,                             
         BUILD=(09X),                                               
         SECTIONS=(1,05,                                             
         TRAILER3=(1,05,COUNT=(TO=PD,LENGTH=4)))                     
                                                                     
//JOIN1   EXEC PPSORT                                               
//SORTOUT  DD DISP=(NEW,PASS),DSN=&&MERG01                           
//IN1      DD DISP=(SHR,PASS),DSN=&&OURT1                           
//IN2      DD DISP=(SHR,PASS),DSN=&&OURT2                           
//SYSIN DD *                                                         
  JOINKEYS F1=IN1,FIELDS=(1,5,A),SORTED,NOSEQCK                     
  JOINKEYS F2=IN2,FIELDS=(1,5,A),SORTED,NOSEQCK                     
  REFORMAT FIELDS=(F1:1,34,F2:6,4,F1:35,4)                           
  SORT FIELDS=(1,5,CH,A,6,29,CH,A)                                   
  OUTREC BUILD=(1,34,35,4,PD,EDIT=(TTTTTTTT),39,4,PD,EDIT=(TTTTTTTT))

Btw. the example i gave first was just to indicate what i wanted to do, the input and output are different, but to my opinion, has no effect on the logic.
Again, thank you for your much appreciated effort and answer.
Kind Regards, Gerd.
Back to top
View user's profile Send private message
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0
PostPosted: Tue Apr 19, 2016 8:26 am    Post subject: Reply with quote
You still have the SORT in the main task. All your data is already in that order. Since you have a later BUILD, you could consider F1:1,38,F2:6,4 on the REFORMAT and changing the locations on the BUILD.

It is a pity that your original data is not in sequence. Your sample data shows it in sequence. Is it "somewhat in sequence" ie all the data within key contiguous and in sequence, just the main keys not in key sequence?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Apr 19, 2016 7:48 pm    Post subject: Reply with quote
Gerd Hofmans,

As William pointed out, you do NOT need a SORT on the JOINKEYS main task as the data is already sorted out. Also I do not see a point of having the count in PD format. I used the PD format as I was performing a sort and you don't have to.

Since your sort fields are contiguous fields, there is no point in splitting them. Simply sort it as a single field. Also use INREC to reduce the sortwk/memory required to just the data you need.

Use these control cards for COUNT1 step
Code:

//SYSIN DD *                       
  INREC BUILD=(1,34)
  SORT FIELDS=(1,34,CH,A)

  OUTFIL REMOVECC,NODETAIL,FNAMES=OUT1,                             
         BUILD=(42X),                                               
         SECTIONS=(1,34,                                             
         TRAILER3=(1,34,COUNT=(M11,LENGTH=8)))                     

  OUTFIL REMOVECC,NODETAIL,FNAMES=OUT2,                             
         BUILD=(13X),                                               
         SECTIONS=(1,05,                                             
         TRAILER3=(1,05,COUNT=(M11,LENGTH=8)))
//*


And these control cards for JOIN1 step.
Code:

//SYSIN DD *                                                         
  JOINKEYS F1=IN1,FIELDS=(1,5,A),SORTED,NOSEQCK                     
  JOINKEYS F2=IN2,FIELDS=(1,5,A),SORTED,NOSEQCK                     
  REFORMAT FIELDS=(F1:1,34,F2:6,8,F1:35,8)
  OPTION COPY
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Gerd Hofmans
Beginner


Joined: 15 Jan 2016
Posts: 20
Topics: 7
PostPosted: Wed Apr 20, 2016 8:01 am    Post subject: Reply with quote
Hi Kolusu & Bill,
Many thanks for your suggestions (that i carefully implemented).
to answer Bill's question : The input data is not sorted.
Just for the record : Sorting the entire input file uses a lot more CPU then splitting and sorting afterwards. i guess this has to do with the region i submit jobs in. Also, this file contains 2.5 Billion records, and needs to be sorted and counted. in a straightforward fashion, using the entire input file, the sorting and counting uses 31minutes of CPU. Splitting, sorting and counting uses 18 minutes of CPU.
Many thanks and kind regards, Gerd.
Back to top
View user's profile Send private message
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