IBM Mainframe Forum

by **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.

Select all

//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.

by **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:

Select all

//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:

Select all

0001 020131   REC1-991201
 

by **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).

Select all

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

Select all

20020131 REC1-19991201                 
20020131 REC2-20091231                 
 

what next now :?:

by **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.

by **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…

by **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.

by **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).

by **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.

Select all

//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'

IBM Mainframe Forum

To fetch records ageing more than 15 years using DFSORT

To fetch records ageing more than 15 years using DFSORT

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

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

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

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

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

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

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