split a single file into 2, based on date field



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

split a single file into 2, based on date field

Postby JeffJones » Fri Apr 06, 2012 8:07 pm

I would appreciate some advice on the sort parameters required for the following.

I need to split a single file into 2 files, based on a date value at offset 131. Not a big deal.
However, if the date value at offset 131 qualifies the record to be written to the 2nd file,
I also need to qualify all records where cols 5-12 in subsequent records equals cols 5-12 of the qualified date record.

Here's the structure of the file.

SORT FIELDS=(5,17,BI,A)
RECORD TYPE=VB

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
 ------------------------------------------------------------------------------
********************************* Top of Data **********************************
 ------------------------------------------------------------------------------
CHER...e......11.   .BAS          ÀØØØØ.ØØ110502073725110502073837110502073911..
CCCD0008000002FF04440CCE444444444468888088FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF00
38590035000026110000021200000000004000000011050207372511050207383711050207391100
 ------------------------------------------------------------------------------
CHER...e..4..ìcc                                                               
CCCD000800F005884444444444444444444444444444444444444444444444444444444444444444
38590035014008330000000000000000000000000000000000000000000000000000000000000000
 ------------------------------------------------------------------------------         


For example...

if cols 131-136 < '110101' write the record to file2
and all records where cols 5-12 = cols 5-12 of the data qualified record should be written to file2
else write the record to file1.
JeffJones
 
Posts: 4
Joined: Fri Jan 07, 2011 9:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: split a single file into 2, based on date field

 

Re: split a single file into 2, based on date field

Postby skolusu » Fri Apr 06, 2012 10:00 pm

JeffJones,

The following DFSORT JCL will give you the desired results. I assumed that the date you mentioned is Y2T format which is YYMMDD. I also assumed that your date pos 131 is inclusive of the RDW. Using WHEN=GROUP we Push the value at pos 5 for a length of 12 bytes and then use that in the include condition on OUTFIL.

Since your input is VB we just can't push the key value at the end of each record. we need to push it after the RDW so that we retain the original VB record as is.

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=Your Input VB File,DISP=SHR                                   
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                   
  OPTION Y2PAST=1980                                               
  SORT FIELDS=(5,17,BI,A)                                         
  OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,4,12X,5)),                     
  IFTHEN=(WHEN=GROUP,BEGIN=(143,6,Y2T,LT,Y'110101'),PUSH=(5:17,12))
                                                                   
  OUTFIL INCLUDE=(5,12,CH,EQ,17,12,CH),BUILD=(1,4,17)             
//*
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: split a single file into 2, based on date field

Postby JeffJones » Fri Apr 13, 2012 12:32 am

Hi Kolusu,
Thank you for your reply. Some further help please, as I'm getting RC=16 on the sort.

ICE218A 0 19 BYTE VARIABLE RECORD IS SHORTER THAN 21 BYTE MINIMUM FOR FIELDS

To confirm your assumptions, the date field is in YYMMDD format, and position 131 does include the RDW. The date field is actually in column 127 when browsing the file. So I'm confused how you arrived at BEGIN(143,6...).

To try and resolve the ICE218A, I tried using VLSHRT, but it had no effect. I've included the JCL and the SORTOUT below for your review.

Thanks,
Jeff

//SORT EXEC PGM=SORT
//STATOUT      DD SYSOUT=*                             
//SYSOUT        DD SYSOUT=*                           
//SORTWK01   DD UNIT=SYSDA,SPACE=(CYL,250)             
//SORTWK02   DD UNIT=SYSDA,SPACE=(CYL,250)             
//SORTWK03   DD UNIT=SYSDA,SPACE=(CYL,250)             
//SORTWK04   DD UNIT=SYSDA,SPACE=(CYL,250)             
//SYSIN DD *                                                       
  OPTION Y2PAST=1980,VLSHRT                                         
  SORT FIELDS=(5,17,BI,A)                                           
  OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,4,12X,5)),                     
  IFTHEN=(WHEN=GROUP,BEGIN=(143,6,Y2T,LT,Y'110101'),PUSH=(5:17,12))
                                                                   
  OUTFIL INCLUDE=(5,12,CH,EQ,17,12,CH),BUILD=(1,4,17)               
//*  SORTIN IS ARCHIVE PMAST                                       
//SORTIN      DD DISP=SHR,DSN=JSHEPHE.ARCH61X.CMNPMAST             
//*  SORTOUT CONTAINS THE QUALIFIED RECORDS                         
//SORTOUT  DD DISP=(NEW,CATLG,KEEP),                               
//            DSN=JJONES.ARCH61X.PRE.D110101.CMNPMAST,             
//            UNIT=SYSDA,SPACE=(CYL,(5,5)),                         
//            DCB=(RECFM=VB,LRECL=31996,BLKSIZE=32000)             


SORTOUT

1ICE201I H RECORD TYPE IS V - DATA STARTS IN POSITION 5
 ICE150I 0 VLSHRT NOT USED FOR SORT, MERGE, INCLUDE, OMIT OR SUM STATEMENT FIELDS
 ICE168I 0 CENTURY WINDOW FOR Y2 FORMAT FIELDS IS FROM 1980 TO 2079
 ICE751I 0 C5-K62149 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE   E5-K70685 E7-K70685
 ICE143I 0 BLOCKSET     SORT  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 - 11:51 ON THU APR 12, 2012 -
0            OPTION Y2PAST=1980,VLSHRT
             SORT FIELDS=(5,17,BI,A)
             OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,4,12X,5)),
             IFTHEN=(WHEN=GROUP,BEGIN=(143,6,Y2T,LT,Y'110101'),PUSH=(5:17,12))

             OUTFIL INCLUDE=(5,12,CH,EQ,17,12,CH),BUILD=(1,4,17)
 ICE201I H RECORD TYPE IS V - DATA STARTS IN POSITION 5
 ICE150I 0 VLSHRT NOT USED FOR SORT, MERGE, INCLUDE, OMIT OR SUM STATEMENT FIELDS
 ICE168I 0 CENTURY WINDOW FOR Y2 FORMAT FIELDS IS FROM 1980 TO 2079
 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
 ICE088I 0 JJONESA .SORT    .        , INPUT LRECL = 31996, BLKSIZE = 32000, TYPE = VB
 ICE093I 0 MAIN STORAGE = (MAX,6291456,6291456)
 ICE156I 0 MAIN STORAGE ABOVE 16MB = (6210308,6210308)
 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=Y,LIST=Y,ERET=RC16 ,MSGDDN=SYSOUT
 ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO   ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG
 ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,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=Y,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
 ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=2097152,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
 ICE235I 0 OPTIONS: NULLOUT=RC0
 ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y
 ICE084I 0 EXCP ACCESS METHOD USED FOR SORTIN
 ICE750I 0 DC 1567804 TC 0 CS DSVOO KSZ 21 VSZ 21
 ICE752I 0 FSZ=1567804 BC  IGN=0 E  AVG=15999 0  WSP=2037 C  DYN=0 0
0ICE805I 1 JOBNAME: JJONESA  , STEPNAME: SORT
 ICE802I 0 BLOCKSET     TECHNIQUE IN CONTROL
 ICE858I 0 LA=130900, DA=6144, AA=124756, BA=2004, CP=2, TA=2162
 ICE859I 0 LB=8044, DB=1000, AB=7044, BB=0, CP=0, RS=0, TB=20
 ICE860I 0 F=YN, P=2, M=N, B=2048
 ICE915I 0 MOFSZ=0,MOSZ=0,MOSYS=41(5),MOSTG=41,MEML=1946(1)
 ICE916I 0 MOFR=0702,MOVR=HH
 ICE996I 0 ESM=127360,ESO=127360,ESR=0,ESP=4096,ESS=16384,CES=127360,HSZ=1048576
 ICE997I 0 HWSP=536,HMAX=10509,HES=10496,ASV=10509,EQ=I1,HN=1
 ICE898I 0 OMAX=21018,NMAX=89520,ENQT=10509,CMAX=1280,HU=96,BUN=61408,MD=NK,M ,DU=0,DR=10000,HN=1
 ICE880I 0 QP=1 QA=1 HI=3 LI=3 MI=3 TZ=2317 N1=536 N2=536 SZ=2 HN=1
 ICE889I 0 CT=MAX     , SB=3, L=0, D=0000, CCW=1MAM
 ICE902I 0 O       I NS11
 ICE218A 0 19 BYTE VARIABLE RECORD IS SHORTER THAN 21 BYTE MINIMUM FOR          FIELDS
 ICE751I 1 D8-K58148 D4-K59452 E8-K70685
 ICE052I 0 END OF DFSORT
JeffJones
 
Posts: 4
Joined: Fri Jan 07, 2011 9:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: split a single file into 2, based on date field

Postby BillyBoyo » Fri Apr 13, 2012 3:57 am

So, you have a short record. What do you want to do with it, how do you want to treat it? What is it?

You need to start by looking up the ICE150I message, then the ICE218A.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: split a single file into 2, based on date field

Postby BillyBoyo » Fri Apr 13, 2012 12:35 pm

I forgot to ask if your input is on tape. No reason, except the wasteful blocksize if it is on DASD.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: split a single file into 2, based on date field

Postby JeffJones » Fri Apr 13, 2012 6:57 pm

It's not on tape. Thanks for the earlier advice. I need to find the short record before deciding what to do with it.
JeffJones
 
Posts: 4
Joined: Fri Jan 07, 2011 9:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: split a single file into 2, based on date field

Postby BillyBoyo » Fri Apr 13, 2012 7:04 pm

  OPTION COPY
  OMIT COND=(1,2,BI,TT,+21)


You can put that in a sort step, sortout to sysout=whatever, and list out any and all short ones.

Your input file is blocked at 32000, meaning a waste of over 20000 bytes per track.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: split a single file into 2, based on date field

Postby skolusu » Fri Apr 13, 2012 8:38 pm

JeffJones wrote:Hi Kolusu,
Thank you for your reply. Some further help please, as I'm getting RC=16 on the sort.

ICE218A 0 19 BYTE VARIABLE RECORD IS SHORTER THAN 21 BYTE MINIMUM FOR FIELDS

To confirm your assumptions, the date field is in YYMMDD format, and position 131 does include the RDW. The date field is actually in column 127 when browsing the file. So I'm confused how you arrived at BEGIN(143,6...).

To try and resolve the ICE218A, I tried using VLSHRT, but it had no effect. I've included the JCL and the SORTOUT below for your review.

Thanks,
Jeff

Jeffjones,

VLSHRT is ignored if you have INREC/OUTREC statement which is explained in the ICE150I message

http://publibz.boulder.ibm.com/cgi-bin/ ... 60/2.2.146?

If it is ok with eliminating such short records you can use an OMIT cond shown by Billyboyo but change the relational operator to LT instead of TT.

JeffJones wrote:To confirm your assumptions, the date field is in YYMMDD format, and position 131 does include the RDW. The date field is actually in column 127 when browsing the file. So I'm confused how you arrived at BEGIN(143,6...).


If you look at the first WHEN=INIT, I was padding 12 spaces right after the RDW to push the key value to be compared. So the input now starts from 17 instead of 5. So the date value is incremented by 12 , so the start pos is changed to 143 instead of 131.
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: split a single file into 2, based on date field

Postby BillyBoyo » Fri Apr 13, 2012 10:26 pm

Sorry about TThe TTypo. It was supposed to suggest a way to list the short record(s) to see what they are :-)

Edit: In which case, you'd need to subtract the 12 bytes as well, which I didn't do...
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: split a single file into 2, based on date field

Postby skolusu » Fri Apr 13, 2012 10:34 pm

BillyBoyo wrote:Sorry about TThe TTypo. It was supposed to suggest a way to list the short record(s) to see what they are :-)

Edit: In which case, you'd need to subtract the 12 bytes as well, which I didn't do...


Billyboyo,

Am I missing something? The 12 bytes are added on the OUTREC and OMIT COND is processed way before that. Why would you have to subtract 12 bytes? :?:
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

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post