To fetch records ageing more than 15 years using DFSORT



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

To fetch records ageing more than 15 years using DFSORT

Postby Muhammed H » Tue Jan 31, 2017 5:16 pm

Hello There,

I got this requirement to retrieve records more than 15 years old. And have written the following DFSORT card but it is not fetching the desire result.

Please could anyone suggest what’s wrong.


//STEP001   EXEC PGM=SORT                                        
//SYSPRINT  DD   SYSOUT=*                                        
//SYSOUT    DD   SYSOUT=*                                        
//SORTIN    DD *                                                  
0001 REC1-991201                                                  
0002 REC2-091231                                                  
//SORTOUT   DD   SYSOUT=*                                        
//SYSIN    DD  *                                                  
 INREC IFTHEN=(WHEN=INIT,                                        
                BUILD=(01:01,04,                                  
                       06:DATE1,                                  
                       15:06,12)),                                
       IFTHEN=(WHEN=INIT,                                        
                OVERLAY=(06:06,08,Y4T,SUBYEARS,+15,TOGREG=Y2T,2X))
 SORT FIELDS=COPY                                                
 INCLUDE COND=(20,06,LT,06,06),FORMAT=Y2T                        
/*                                                                
 


The “date” field in the SORTIN card is in the format YYMMDD; starting at 11th position.
I’m getting the following result
0001 020131 REC1-991201
0002 020131 REC2-091231

While the desired result is only the first record as it refers to year 1999.
0001 020131 REC1-991201

Thanking you in anticipation.
Muhammed H
Muhammed H
 
Posts: 8
Joined: Thu Aug 21, 2014 8:38 pm
Location: Bangalore
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch records ageing more than 15 years using DFSORT

Postby Aki88 » Tue Jan 31, 2017 7:08 pm

Hello,

Reason for the undesired output in your posted solution is the order in which DFSORT statements are actually processed.
INCLUDE/OMIT are processed before INREC, hence the INCLUDE statement is executed prior INREC.
To get the desired solution, simply modify the 'INCLUDE COND' to 'OUTFIL INCLUDE', OUTFIL will be processed last, hence the record built by INREC will be brought into consideration, yielding a clean result.

Test run:


//STEP001   EXEC PGM=SORT                                        
//SYSOUT    DD   SYSOUT=*                                        
//SORTIN    DD *                                                  
0001 REC1-991201                                                  
0002 REC2-091231                                                  
//SORTOUT   DD   SYSOUT=*                                        
//SYSIN    DD  *                                                  
 INREC IFTHEN=(WHEN=INIT,                                        
                BUILD=(01:01,04,                                  
                       06:DATE1,                                  
                       15:06,12)),                                
       IFTHEN=(WHEN=INIT,                                        
                OVERLAY=(06:06,08,Y4T,SUBYEARS,+15,TOGREG=Y2T,2X))
 SORT FIELDS=COPY                                                
*INCLUDE COND=(20,06,LT,06,06),FORMAT=Y2T                        
 OUTFIL INCLUDE=(20,06,Y2T,LT,06,06,Y2T)                          
/*                                                                
 


Output:


0001 020131   REC1-991201
 
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: To fetch records ageing more than 15 years using DFSORT

Postby Muhammed H » Tue Jan 31, 2017 7:28 pm

Hello Aki88,

Thanks for your time and concern.
Actually I did try comparing with century in place. However it seems DFSORT doesn’t allow Y4T format comparison (Got a SYNTAX error).


ICE000I 1 - CONTROL STATEMENTS FOR 5650-ZOS, Z/OS DFSORT V2R2  - 16:43 ON TUE JA
           INREC IFTHEN=(WHEN=INIT,                                            
                          BUILD=(01:01,04,                                      
                                 06:DATE1,                                      
                                 15:06,12)),                                    
                 IFTHEN=(WHEN=INIT,                                            
                          OVERLAY=(06:06,08,Y4T,SUBYEARS,+15,TOGREG=Y4T,        
                                   20:20,06,Y2T,TOGREG=Y4T))                    
           SORT FIELDS=COPY                                                    
           INCLUDE COND=(20,08,LT,06,08),FORMAT=Y4T                            
                                                $                              
ICE018A 0 INVALID OR MISSING FIELD FORMAT - REASON CODE IS 01                  
 


And just for info, on excluding the ‘INCLUDE COND” statement; the output was as below suggesting record built for comparison are good


0001 20020131 REC1-19991201                
0002 20020131 REC2-20091231                
 


:?: what next now :?:
Muhammed H
Muhammed H
 
Posts: 8
Joined: Thu Aug 21, 2014 8:38 pm
Location: Bangalore
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch records ageing more than 15 years using DFSORT

Postby Aki88 » Tue Jan 31, 2017 7:38 pm

Hello,

Yes, DFSORT allows only Y2* date format comparisons; kindly refer my second post on this topic; I'd misread your original post.
Correct solution is shared in the second post.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: To fetch records ageing more than 15 years using DFSORT

Postby Muhammed H » Tue Jan 31, 2017 8:09 pm

Hi

hmmm…..well getting quite a dumb-nut feeling.
Anyhow many thanks and can I conclude that once INREC is tweaked; "INCLUDE COND" doesn’t work & the only way forward is with OUTFIL INCLUDE?


Regards…
Muhammed H
Muhammed H
 
Posts: 8
Joined: Thu Aug 21, 2014 8:38 pm
Location: Bangalore
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch records ageing more than 15 years using DFSORT

Postby Aki88 » Tue Jan 31, 2017 8:29 pm

Hello,

Muhammed H wrote:Hi

hmmm…..well getting quite a dumb-nut feeling.
Anyhow many thanks and can I conclude that once INREC is tweaked; "INCLUDE COND" doesn’t work & the only way forward is with OUTFIL INCLUDE?


Regards…


Yes, as per the DFSORT processing flow, 'INCLUDE COND' will be processed prior to INREC processing. Since most of the conversion here is happening in INREC, hence you'll have to find a post-processor which includes the newly re-formatted records. Simplest solution being an OUTFIL.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: To fetch records ageing more than 15 years using DFSORT

Postby BillyBoyo » Wed Feb 01, 2017 2:04 am

To emphasise. No matter what order you code DFSORT operands in, DFSORT is going to process them in the order that is documented. Just physically locating INCLUDE (or anything else) in a particular position in the control cards will not change that.

Is it the case that you want exactly 15 calendar years? Or something else? Exactly, because this is computers.

If you have a two-digit year, the "date window" takes account of the comparisons where the crossing of the century is concerned. Ensure that you have such a window, and that it covers all your dates (and a reasonable number (say 20) of years into the future).

Then a two-step process. Step one, generate a SYMNAMES-format file (fixed-length records, 80 bytes) containing a SORT Symbol, which you construct. This is to contain the date-in-the-past-that-is-the-cutoff.

Then, in the second step, simply use the symbol in a simple comparison in INCLUDE (or OMIT).
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: To fetch records ageing more than 15 years using DFSORT

Postby Muhammed H » Wed Feb 01, 2017 10:02 pm

Dear Billy,

Haven’t read your post till now but glad I did. Didn’t’ know any such option as SYMNAMES existed.


Actually using SYMNAMES is a much more optimized way of resolving the requirement I have in hand .i.e. instead of looping & reformatting each record with a date field & then loop again to segregate the required records.
With SYMNAMES in the first loop itself; I get my records. This is quite important as my input file has got some 12 million records.

BillyBoyo wrote:
Is it the case that you want exactly 15 calendar years? Or something else? Exactly, because this is computers.

If you have a two-digit year, the "date window" takes account of the comparisons where the crossing of the century is concerned. Ensure that you have such a window, and that it covers all your dates (and a reasonable number (say 20) of years into the future).



On my requirement, (For your first post above) is that to cover dates from 1960 & not exactly 15 calender years. Initially I didn't understand from your post on "date window" but later on during execution got to know.

So as usual it was quite insightful to read your post. And I'm just posting the new SORT CARD using SYMNAMES for other DFSORT enthusiast. Let me know if it can be made better.


//STEP001  EXEC PGM=SORT                                                
//SYSPRINT DD SYSOUT=*                                                  
//SYSOUT   DD SYSOUT=*                                                  
//SORTIN   DD *                                                        
RECORD                                                                  
/*                                                                      
//SORTOUT  DD DSN=&&SYMNAM1,DISP=(,PASS),                              
//            UNIT=SYSDA,SPACE=(TRK,(1,1)),                            
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=800)                      
//SYSIN    DD *                                                        
* Create DFSORT Symbol                                                  
* Year YYMMDD. Where YYMMDD is Today's Date - 15Years                  
  INREC OVERLAY=(21:DATE1,                                              
       1:C'Year,''',21,8,Y4T,SUBYEARS,+15,TOGREG=Y2T,C'''',21:60X)      
  SORT FIELDS=COPY                                                      
/*                                                                      
//*                                                                    
//STEP002  EXEC PGM=SORT                                                
//SYSPRINT DD SYSOUT=*                                                  
//SYSOUT   DD SYSOUT=*                                                  
//SYMNAMES DD DSN=&&SYMNAM1,DISP=(OLD,DELETE)                          
//SYMNOUT  DD SYSOUT=*                                                  
//SORTIN   DD *                                                        
0001 KEY1 991201                                                        
0002 KEY2 011231                                                        
0003 KEY3 091231                                                        
//SORTOUT  DD   SYSOUT=*                                                
//SYSIN    DD  *                                                        
 SORT FIELDS=COPY                                                      
 INCLUDE COND=(((11,02,CH,GT,C'60'),AND,    /*GT 60 Implies 19th Century
                (11,06,CH,GT,Year)),                                    
            OR,((11,02,CH,LE,C'60'),AND,    /*LE 60 Implies 20th Century
                (11,06,CH,LE,Year)))                                    
/*                                                                      
 


Output:
0001 KEY1 991201
0002 KEY2 011231


And SYMNOUT
------------------ SYMBOL TABLE -----------------
Year,C'020201'
Muhammed H
Muhammed H
 
Posts: 8
Joined: Thu Aug 21, 2014 8:38 pm
Location: Bangalore
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post