Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

JCL SORT to split the file and put record count in trailer

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Wed Feb 17, 2016 4:17 pm    Post subject: JCL SORT to split the file and put record count in trailer
Reply with quote

Hi
I am trying to write a sort in which I have to split the file from particular record identifier (IVR000000) and then count the records(i.e. IVR000000 not the number of rows) and put the count at last in trailer record (e.g. )

My File looks like below:

PTX000000~C5408461~N~MTF~FEL~49948558~Mackenzie Saxon
PTX000000~K6157615~N~MTF~NL~38766490~Mawer Balanced
IVR000000~2036~9567183~E~Mrs.~Syt Est~~C (RECORD STARTING)
IVRADV000~2036~Ptes~Mte~~(416) 555-
PLNSUM000~~CI Investments~46429602~330.28~0.00~0.06~-16.52
PLNSUM000~SDRSP~SLFISI~K5837557~124034.53~4400.0449.30~6014.
IVRSUM000~124718.99~4400.00~0.00~135095.83~5976.84
PTX000000~K5837557~N~MTF~DSC~7291202~CI Harbou
PTX000000~47790432~N~MTF~DSC~7291202~CI Har (RECORD END)
IVR000000~6248~9762202~E~~Jltes~~Cmtes (NEW RECORD STARTING)
IVRADV000~6248~Mctestm Ctestmc~Whtestwht~~(519
IVR999999~000000013 (TRAILER RECORD)

The record starts with the 'IVR000000' so when the split happens upto the RECORD END the rows should be in that file only and the new record must start from 'IVR000000' only.

I am not sure if it can be done from SORT or ICETOOL or some other utility?
Please help!!
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Wed Feb 17, 2016 4:52 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Can you show your input again, in the code-tags, not with bolding or other highlighting?

Can you also show the output you expect for that data, not just say something woolly and general? In the code-tags again, please.
Back to top
View user's profile Send private message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Wed Feb 17, 2016 7:43 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Thanks for your quick reply.

My sample input file is attached with 4 records (i.e. IVR000000)

If the above input file is splitted into 2 files (File1-3 records and File2- 1 record), the output files should be like the attached Output file1 and Output file2.

Hope this clears my query.. please let me know if more clarification required.

Thanks a ton
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Wed Feb 17, 2016 8:00 pm    Post subject:
Reply with quote

Yogesh Jaiswal,
Please use code tags, not attachments.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1714
Location: UK

PostPosted: Wed Feb 17, 2016 8:33 pm    Post subject:
Reply with quote

Still not clear - your first example shows a trailer saying 13 whereas there are only 2 IVR000000 records present. Your second example shows only one set being extracted whereas it is implied ALL IVR000000 sets are to be selected. If you want only one set then what is the criteria for distinguishing that set from the other sets?
Back to top
View user's profile Send private message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Wed Feb 17, 2016 11:56 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Sorry for the confusion and using attachments.

Actually in first example, the trailer should be only 2 not 13 (mistake).
In second example, I have given only 4 IVR000000 record sets, if splitted in two files like 3 sets in File1 and 1 set in File2 then their record count must also be calculated and inserted as trailer in the file which is 3 and 1 respectively.

In real, I have more than 3-4 lacs IVR000000 record sets which I need to split into 4-5 files, the record sets should be inserted in files such that the file1 contains 1 lac record set, file2 contains 1 lacs,....., nth file contains the rest of the record sets.

There is no criteria to select IVR000000 records sets (No record should be excluded only split the big file into 4-5 files), the only condition is that every new client record starts with this IVR000000 entry and its details are in subsequent lines till before the next IVR000000 is encountered. This all is a one set of record and must not get mixed with other IVR000000 details.

Hope this time I am clear. Thank you all for your patience.
If still there is any confusion, please let me know.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1714
Location: UK

PostPosted: Thu Feb 18, 2016 3:06 am    Post subject:
Reply with quote

What are/is 'lacs'? It does not seem to be a computing term.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Feb 18, 2016 3:11 am    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

What are/is 'lacs'? It does not seem to be a computing term.

some kind of indian unit ... 1 lac = 100000 IIRC
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Feb 18, 2016 7:42 am    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

You need to create groups with BEGIN=(1,6,CH,EQ,C'IVR000'). If you are not bothered about the order/sequencing of groups then, I think, you can do it by using WHEN=GROUP with ID=1.

ID value starts with 1 and incremented by 1 but since we have ID as 1 byte it can only have values 0 - 9.

Now you can send separate groups to separate output files depending on ID values.

The trailer with count can be added later with TRAILER1.

I haven't tried it but I believe it should be along the same lines.
.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Thu Feb 18, 2016 12:57 pm    Post subject:
Reply with quote

Try this. This splits the file into 2 files. Tailor it according to your needs and and use include NE C'IVR999999' for your last file.

Code:

//SUMKK    EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                               
PTX000000~C5408461~N~MTF~FEL~49948558~MACKENZIE SAXON         
PTX000000~K6157615~N~MTF~NL~38766490~MAWER BALANCED           
IVR000000~2036~9567183~E~MRS.~SYT EST~~C                     
IVRADV000~2036~PTES~MTE~~(416) 555-                           
PLNSUM000~~CI INVESTMENTS~46429602~330.28~0.00~0.06~-16.52   
PLNSUM000~SDRSP~SLFISI~K5837557~124034.53~4400.0449.30~6014. 
IVRSUM000~124718.99~4400.00~0.00~135095.83~5976.84           
PTX000000~K5837557~N~MTF~DSC~7291202~CI HARBOU               
PTX000000~47790432~N~MTF~DSC~7291202~CI HAR                   
IVR000000~6248~9762202~E~~JLTES~~CMTES                       
IVRADV000~6248~MCTESTM CTESTMC~WHTESTWHT~~(519               
IVR999999~000000013                                           
//SYSOUT   DD SYSOUT=*                                       
//SORTOF01  DD SYSOUT=*                                       
//SORTOF02  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                                   
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,9,CH,EQ,C'IVR000000'),           
                       PUSH=(81:ID=2))                               
  OUTFIL FILES=01,                                                   
  INCLUDE=(81,2,CH,EQ,C'01'),                                       
  OUTREC=(1:1,80),TRAILER1=('IVR999999~',11:COUNT-5=(M11,LENGTH=9)),
                    REMOVECC                                         
  OUTFIL FILES=02,                                                   
  INCLUDE=(81,2,CH,EQ,C'02',AND,1,9,CH,NE,C'IVR999999'),             
  OUTREC=(1:1,80),TRAILER1=('IVR999999~',11:COUNT-5=(M11,LENGTH=9)),
                    REMOVECC                                         
/*                                                                   
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Thu Feb 18, 2016 4:29 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Is your file made up entirely of IVR...PTX... records as the groups, plus the trailer, where you want to know the number of groups?

The you want to put approximately "n" records in each of three files, with the rest in a fourth file? The reason for "approximately" is that you don't want to split a group across files? And each file needs a trailer generated?
Back to top
View user's profile Send private message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Thu Feb 18, 2016 4:57 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Thank you so much all for the replies.

One more thing is that the file is VB file with LRECL=2052, so putting the indicator at the last bytes might not work.

Yes Bill, my entire file is made up of IVR,PTX,PLN,TRX,.... like prefixes with trailer record at the end. So, I want it to split in a way to keep all this in splitted files so that the split files can be feeded in the next job as this big file was feeded but now with less volume of records.

We plan to have 100,000 record sets in one file, and it could be the case that the last 2-3 files will not have any data if the records are less.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Thu Feb 18, 2016 11:35 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

For variable-length records, you extend at the "front" of the record.

If you have only one type of group, the only thing you have to worry about is accidentally including the trailer in a group. So you can OMIT COND= the trailer.

Add a sequence number using INREC and IFTHEN=(WHEN=INIT. Use IFTHEN=(WHEN=GROUP for your group-starter value. PUSH the sequence number to the position of the sequence number. This will give all the records in the group the same sequence number.

Have four OUTFILs. First can be LT 100000 in the sequence number. Second GE 100000 and LT 200000, third GE 200000 and LT 300000, fourth with SAVE (to catch any remainder).
Back to top
View user's profile Send private message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Fri Feb 19, 2016 4:56 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Thanks Bill for the suggestion but its not working, I also dig out some info and tried many things with the BEGIN, GROUP approach, but the file is not getting split edit: the word is 'split' not 'splitted' which does not exist

I am using the below code and also tried many other combinations:
Code:
//SYSIN    DD  *                                                     
  SORT FIELDS=COPY                                                   
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,8:5)),                           
        IFTHEN=(WHEN=GROUP,BEGIN=(8,16,CH,EQ,C'IVR000000'),           
                        PUSH=(5:SEQ=3))                               
   OUTFIL FILES=01,INCLUDE=(1,3,ZD,LT,3,OR,                           
                 8,9,CH,NE,C'IVR999999'),BUILD=(1,4,5:8),SPLITBY=3   
   OUTFIL FILES=02,INCLUDE=(1,3,ZD,GE,3,OR,                           
                 8,9,CH,NE,C'IVR999999'),BUILD=(1,4,5:8),SPLITBY=3   
/*                                                                   


The input file I am using is the same which I shared earlier with 4 'IVR000000' record set. In first OUTFIL I am getting all rows from Input file except the trailer and in 2nd OUTFIL I am getting the whole Input File including the trailer.
BUILD is required to eliminate the SEQ columns that were inserted otherwise the data is shifted by 3 columns.

Please correct if I missed something in code

Thanks again.
Back to top
View user's profile Send private message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Fri Feb 19, 2016 5:05 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Also tried this one and getting same result:
Code:
//SYSIN    DD  *                                           
  SORT FIELDS=COPY                                         
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,8:5)),               
        IFTHEN=(WHEN=GROUP,BEGIN=(8,9,CH,EQ,C'IVR000000'),
                        PUSH=(5:SEQ=3))                   
   OUTFIL FILES=01,INCLUDE=(5,3,ZD,LT,3,OR,               
                 8,9,CH,NE,C'IVR999999'),BUILD=(1,4,5:8)   
   OUTFIL FILES=02,INCLUDE=(5,3,ZD,GE,3,OR,               
                 8,9,CH,NE,C'IVR999999'),BUILD=(1,4,5:8)   
/*                                                         
Back to top
View user's profile Send private message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Mon Feb 22, 2016 6:40 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

Thank you all for looking into this.

Now I am able to split the files and also put the trailer at the end of each by using 2 steps as below:
Code:
INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,11:5)),               
      IFTHEN=(WHEN=GROUP,BEGIN=(11,9,CH,EQ,C'IVR000000'),
             PUSH=(5:ID=6))                             
SORT FIELDS=COPY                                         

I used ID=6 because I want to split in 100K record sets and rest 3 prefix zeroes hard coded.
Code:
SORT FIELDS=COPY                                           
OUTFIL FILES=01,                                           
INCLUDE=(5,6,CH,LE,C'000002',AND,11,9,CH,NE,C'IVR999999'), 
OUTREC=(1,4,5:11),TRAILER1=('IVR999999~000',5,6),           
                REMOVECC                                   
OUTFIL FILES=02,                                           
INCLUDE=(5,6,CH,GT,C'000002',AND,11,9,CH,NE,C'IVR999999'), 
OUTREC=(1,4,5:11),TRAILER1=('IVR999999~000',5,6),           
                REMOVECC                                   

The trailer in 2nd split file is having count 5 (because of Last ID for total 5 record sets) which should be 000002 (3 in first file and 2 in second file).
Code:
IVR999999~000000005

I am using Last ID as the trailer count, so for first split file it will be fine but after that the last ID will be the total ID's till that split file. So now if I can minus 3 (record sets in first file) from the Last ID of 2nd file I can get the desired output.

For example, if in split1 I put 1-100 records, split2 101-200, split3 201-270 then I should subtract 100 in split2 trailer and 200 in split3 trailer from the Last ID. Hope I am clear.
Please let me know if there is any other solution to get the desired result and issues if the last file produced is empty.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Mon Feb 22, 2016 9:10 pm    Post subject: Reply to: JCL SORT to split the file and put record count in trailer
Reply with quote

I'm not sure why you felt two steps would be needed.

Code:
 INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,11:5)),               
      IFTHEN=(WHEN=GROUP,BEGIN=(11,9,CH,EQ,C'IVR000000'),
             PUSH=(5:ID=6))                             
 SORT FIELDS=COPY                                         
 
 OUTFIL FILES=01,                                           
 INCLUDE=(5,6,CH,LE,C'000002',AND,11,9,CH,NE,C'IVR999999'), 
 OUTREC=(1,4,5:11),TRAILER1=('IVR999999~000',5,6),           
                REMOVECC                                   
 OUTFIL FILES=02,                                           
 INCLUDE=(5,6,CH,GT,C'000002',AND,11,9,CH,NE,C'IVR999999'), 
 OUTREC=(1,4,5:11),TRAILER1=('IVR999999~000',5,6),           
                REMOVECC   



You missed my suggestion to add a sequence number (WHEN=INIT in INREC) and to PUSH that sequence-number for your GROUP. Include an additional temporary extension which is one for your IVR-headers and zero for anything else. Then TOT/TOTAL in the TRAILER1 will grive you the count.

Isn't it easier just to OMIT the file-trailer right up front? Or do you want to check the values on it?


Change OUTREC in OUTFIL to BUILD.
Back to top
View user's profile Send private message
Yogesh Jaiswal

New User


Joined: 31 May 2015
Posts: 8
Location: India

PostPosted: Mon Feb 29, 2016 3:28 pm    Post subject:
Reply with quote

Thanks a lot Bill. I have achieved what I wanted with your suggestions and help in a single sort step.
As per your suggestion I tried the single OMIT instead of checking in every OUTFIL and also using the Sequence number to form the group but the results were not proper. May be because I am performing multiple things in as single sort step.
My final sort is as below:
Code:
INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,12:5)),               
      IFTHEN=(WHEN=GROUP,BEGIN=(12,9,CH,EQ,C'IVR000000'),
             PUSH=(5:SEQ=1,6:ID=6)),                     
      IFTHEN=(WHEN=(12,9,CH,EQ,C'IVR000000'),           
             OVERLAY=(5:C'1')),                         
      IFTHEN=(WHEN=NONE,OVERLAY=(5:C'0'))               
SORT FIELDS=COPY                                         
OUTFIL FILES=01,                                             
  INCLUDE=(6,6,CH,LE,C'100000',AND,12,9,CH,NE,C'IVR999999'),
  BUILD=(1,4,5:12),TRAILER1=('IVR999999~000',               
             TOT=(5,1,ZD,EDIT=(TTTTTT))),                   
                REMOVECC                                     
OUTFIL FILES=02,                                             
  INCLUDE=(6,6,CH,GT,C'10000',AND,6,6,CH,LE,C'200000',     
             AND,12,9,CH,NE,C'IVR999999'),                   
  BUILD=(1,4,5:12),TRAILER1=('IVR999999~000',               
             TOT=(5,1,ZD,EDIT=(TTTTTT))),                   
                REMOVECC                               
.............
OUTFIL FILES=06,                                       
  INCLUDE=(6,6,CH,GT,C'500000',AND,12,9,CH,NE,C'IVR999999'),
  BUILD=(1,4,5:12),TRAILER1=('IVR999999~000',         
             TOT=(5,1,ZD,EDIT=(TTTTTT))),             
                REMOVECC                               

I tried to find out if we can PUSH sequence number in (WHEN=INIT in INREC), like
Code:
INREC IFTHEN=(WHEN=INIT,BEGIN=(12,9,CH,EQ,C'IVR000000'),
         PUSH=(5:SEQ=1),BUILD=(1,4,12:5)),

Also tried many combinations but none worked. I would love to hear anything & optimize the sort if its possible in any ways.
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us