Sort only detail records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Sort only detail records

Postby ajuatsgp » Thu Sep 08, 2011 8:36 am

I have a file which have File Header(Record Type=00),Batch Header(Record Type=01),Detail Record(Record Type=03),Batch Trailer(Record Type=04),File Trailer(Record Type=99).
My file can have multiple Batch header and Batch Trailer but only 1 File Header and 1 File Trailer.
I have defined the file layout as:
File Record
File Header redefines File Record
Batch Header redefines File Record
Detail Record redefines File Record
Batch Trailer redefines File Record
File Trailer redefines File Record

Now my requirement is I need to sort the detail record based on two 3 bytes fields one starts at position 23 and the second field starts at position 26.
The Headers(file & batch) and Trailers(file & batch) have different type of data at the above postions 23 and 26.For which I can not put a simple sort on the fields.
Also in my output file I need only the details record to be sorted on the field specified keeping the header trailer at their respective place.

Please help to resolve this.
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort only detail records

Postby skolusu » Thu Sep 08, 2011 9:43 pm

ajuatsgp,

You haven't provided the LRECL and the RECFM of the input files. Assuming your input lrecl=80 and recfm=FB ,and your record-type indicator is in position the following DFSORT JCL will give you the desired results

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD *                                                 
00                    HEADER                                   
01                    ######                                   
03                    999999                                   
03                    222222                                   
03                    111111                                   
03                    333333                                   
04       FIRST BATCH TRAILER                                   
01                    @@@@@@                                   
03                    888888                                   
03                    444444                                   
03                    666666                                   
04      SECOND BATCH TRAILER                                   
99                    TRAILER                                   
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(89:1,2)),                   
  IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),PUSH=(81:ID=8)),   
  IFTHEN=(WHEN=(1,2,CH,EQ,C'03'),OVERLAY=(91:23,6))             

  SORT FIELDS=(81,16,CH,A),EQUALS                               
 OUTREC BUILD=(1,80)                                           
//*


The output from this job is
00                    HEADER
01                    ######
03                    111111
03                    222222
03                    333333
03                    999999
04       FIRST BATCH TRAILER
01                    @@@@@@
03                    444444
03                    666666
03                    888888
04      SECOND BATCH TRAILER
99                    TRAILER


PS: If your input is VB , you cannot use the same solution.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Sort only detail records

Postby ajuatsgp » Thu Sep 08, 2011 10:12 pm

Dear skolusu,

Sorry forgot to mention the lrecl.My file is a FB with lrecl=44.

I have few queries to your answer:
INREC IFTHEN=(WHEN=INIT,OVERLAY=(89:1,2)),
IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),PUSH=(81:ID=8)),
IFTHEN=(WHEN=(1,2,CH,EQ,C'03'),OVERLAY=(91:23,6))

SORT FIELDS=(81,16,CH,A),EQUALS
OUTREC BUILD=(1,80)

Would you mind explaining me the bold terms?

Thanks in advance.
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort only detail records

Postby skolusu » Thu Sep 08, 2011 10:48 pm

ajuatsgp,

IFTHEN=(when=Init is copying the record-type (00,01,03,04..) on to position 89
IFTHEN=(WHEN=GROUP is adding a 8 byte seqnum (1,2,3...) at position 81 to every batch of records which start with '01' in pos 1-2
IFTHEN=(WHEN=(1,2,CH,EQ,C'03') is copying the contents at pos 23 for 6 bytes of the detail records onto pos 91

8 seqnum + 2 byte record ind + 6 bytes of detail record key = 16 bytes

So we sort on the 16 bytes to get the detail records sorted.

After we are done sorting , we remove the temperorary fields we added at the end using OUTREC

if your lrecl is 44 and FB format then use the following control cards

//SYSIN    DD *                                               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(53:1,2)),                   
  IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),PUSH=(45:ID=8)), 
  IFTHEN=(WHEN=(1,2,CH,EQ,C'03'),OVERLAY=(55:23,6))           
  SORT FIELDS=(45,16,CH,A),EQUALS                             
  OUTREC BUILD=(1,44)                                         
//*



if you really want to understand how the INREC statement works run with the following control cards

//SYSIN    DD *                                               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(53:1,2)),                   
  IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),PUSH=(45:ID=8)), 
  IFTHEN=(WHEN=(1,2,CH,EQ,C'03'),OVERLAY=(55:23,6))           
  SORT FIELDS=COPY


Now take a look at the output from pos 45 and see how the key is created. this is how your input would look like before sorting.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/support/docview.wss? ... g3T7000080
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Sort only detail records

Postby ajuatsgp » Fri Sep 09, 2011 4:04 am

Dear skolusu,
Thanks for your help.I will definitely go through the link you have provided.
For the solution you have provided I have one query:
"IFTHEN=(WHEN=GROUP is adding a 8 byte seqnum (1,2,3...) at position 81 to every batch of records which start with '01' in pos 1-2"
Why are we doing this and why should we add a 8 byte seqnum?
Please explain .

Thanks
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort only detail records

Postby skolusu » Fri Sep 09, 2011 4:50 am

ajuatsgp wrote:Why are we doing this and why should we add a 8 byte seqnum?


ajuatsgp,

In your first post you said

ajuatsgp wrote:My file can have multiple Batch header and Batch Trailer but only 1 File Header and 1 File Trailer.


when you have multiple batches , you need to make sure that the detail records from each batch should stay within the same batch after the sort. In order to preserve the detail records from each batch you some how need to assign a unique value to each batch. If you don't do that then all your detail records will be clubbed together and you would never know from which batch the detail records came from. So we use the When=GROUP function to assign a unique seqnum (1,2,3...) to each batch.


As I mentione earlier if you really want to understand the job run the job the sample job I showed adding 1 statement at a time

//SYSIN    DD *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(53:1,2))
  SORT FIELDS=COPY
//*


Now go check the output and see what is written in pos 53. Now you know what the first statement did. Add the next statement and run the job like this

//SYSIN    DD *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(53:1,2)),
  IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),PUSH=(45:ID=8))
  SORT FIELDS=COPY
//*



Now go check the output and see what is written in pos 45. See how each batch is assigned an unique number (1,2,3...) Now you know what the second statement did. Add the next statement and run the job like this

//SYSIN    DD *                                               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(53:1,2)),                   
  IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),PUSH=(45:ID=8)),
  IFTHEN=(WHEN=(1,2,CH,EQ,C'03'),OVERLAY=(55:23,6))           
  SORT FIELDS=COPY
//*


Now go check the output and see what is written in pos 55. Now you know what the third statement did. Now run job with SORT FIELDS=(45,16,CH,A) and check how the data on the detail records is sorted.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Sort only detail records

Postby ajuatsgp » Fri Sep 09, 2011 9:50 am

Dear Skolusu,

Sorry I mis-typed the lrecl to 44 its actually 444.That's fine I understand where to modify the sort card.
But I forget to mention the Record Type field starts at position 6 so what should I use in the first INREC @ WHEN=INIT to select from position 6

"INREC IFTHEN=(WHEN=INIT,OVERLAY=(89:1,2)),"

Thanks
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort only detail records

Postby dick scherrer » Fri Sep 09, 2011 10:21 am

Hello,

As the IBMers are in the Pacific Time Zone n the US, they will probably not be available for several hours. . .

Suggest you do a bit of expetimenting in the meantime - it costs little and will not harm anythng. . .

From the info skolusu has already posted, you might adjust your process to meet your new definition. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Sort only detail records

Postby ajuatsgp » Fri Sep 09, 2011 11:25 am

Hello Sir,

Sorry if I am expecting more...I have already started trying from my end taking the help of the manual mentioned,but I am yet to get any solution so I thought may be if skolusu will be available he can help me.

Thanks
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort only detail records

Postby enrico-sorichetti » Fri Sep 09, 2011 12:35 pm

unless there is something untold , it does not seem overly complicated ...

instead of using position/offsets for 44 byte records
elucubrate how to provide the same for a 444 bytes record
-- most probably adding 400 is enough :ugeek:

as far as
INREC IFTHEN=(WHEN=INIT,OVERLAY=(53:1,2)),

it reasonable to infer that Koulsu sample had the <key> in position 1 for 2 bytes
it not unreasonable to suppose that for a key starting in position 6 You should use
INREC IFTHEN=(WHEN=INIT,OVERLAY=(53:6,2)),

obviously with the destination offset corrected to take into account the changed lrecl
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post