IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DFSORT dropping rows when OR statement across diff cols?


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Fri Apr 08, 2005 2:36 am
Reply with quote

When I use the following statement to sort a VB record I get rows missed:

SORT FIELDS=COPY
INCLUDE COND=((226,4,CH,EQ,C'RT10'),
OR,(033,4,CH,EQ,C'NC2D'))

I know this because when I submit the individual queries:

SORT FIELDS=COPY
INCLUDE COND=(226,4,CH,EQ,C'RT10')

and

SORT FIELDS=COPY
INCLUDE COND=(033,4,CH,EQ,C'NC2D')

I get say X and Y records, and X+Y is adding up to more records than I get in the query I list at the top that is the combination of these two smaller queries.

What am I doing wrong? Or is there a bug/limitation? I've tried all sorts of scenarios and I can see that sometimes the combined query will get all the records and other times it will not. It actually seems to depend on the volume or density of each record type in the file.

Any feedback appreciated.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Fri Apr 08, 2005 3:00 am
Reply with quote

You didn't give much to go on, so I can only guess. It might have something to do with "short records". Do you have VLSHRT in effect?
If so, then you're telling DFSORT to omit "short records". With the OR condition, that means DFSORT omits any records that are not at least 229 bytes long (226,4). So with the OR condition, if you have a record that is 36 bytes long with 'NC2D' in 33-36, it will be omitted because it's "short" in terms of the 226,4 field. With just the 33,4 condition, the record is not short and will not be omitted.

Try using:

Code:

   OPTION VLSCMP


That will tell DFSORT to temporarily pad the "short" records with binary zeros for comparision purposes. So with the OR condition your record that is 36 bytes long with 'NC2D' will NOT be omitted.

For more information on VLSCMP and VLSHRT, see:

www.ibm.com/servers/storage/support/software/sort/mvs/professor_sort/srtmasfl.html

If my guess is wrong, then you'll need to supply more information about what your records look like for me to figure out what's going on. Feel free to e-mail me (yaeger@us.ibm.com) the information if you like. Put "DFSORT" somewhere in your Subject line to catch my attention.
Back to top
View user's profile Send private message
somasundaran_k

Active User


Joined: 03 Jun 2003
Posts: 134

PostPosted: Fri Apr 08, 2005 3:09 am
Reply with quote

Just a thought.
You said when you run the job with individual INCLUDE conditions you got
X and Y number of records. But when you run the job with both the INCLUDE condition you got less number fo records,that is less than X+Y number reocrds right?
Since your INCLUDE fields are at different positions there are chances that some records will have a RT10 at 226 as well as a NC2D
at 33.
Quote:

SORT FIELDS=COPY
INCLUDE COND=((226,4,CH,EQ,C'RT10'),
OR,(033,4,CH,EQ,C'NC2D'))


To verify this scenario change the OR condition to AND and run the job. The number of records in the output could be the difference you are getting.

hth
-Som
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Fri Apr 08, 2005 3:38 am
Reply with quote

Som,

That's a good point. To illustrate, if T means the condition is True and F means the condition is false:

Code:

                       Count of included records
Field A  | Field B | Just A | Just B |  A OR B |
  T      |   T     |      1 |      1 |       1 |
  T      |   F     |      1 |      0 |       1 |
  F      |   T     |      0 |      1 |       1 |
  F      |   F     |      0 |      0 |       0 |

Total included count      2        2         3


The total record count for Just A + Just B is 4, whereas the total record count for A OR B is 3, so they don't match. This is due to the double counting for Just A and Just B.
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Fri Apr 08, 2005 6:56 pm
Reply with quote

Thanks everyone for your QUICK and helpful replies. Frank nailed it, adding 'OPTION VLSCMP' fixed the problem.

The other suggestion about records that satisfied both my inclusion criteria could have been true too, however I knew from what these fields meant (which you guys couldn't have because I didn't tell you) that no record could have both CA10 and AUTH.

There are 768,664 AUTH records, 17,791 CA10 records, and 2,493,783 records in total. Here's my before and after results, with and without OPTION VLSCMP:

Before
Code:

********************************* TOP OF DATA **********************************
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE000I 1 - CONTROL STATEMENTS FOR 5740-SM1, DFSORT REL 14.0 - 09:11 ON FRI APR
            SORT FIELDS=COPY                                                   
            INCLUDE COND=((226,4,CH,EQ,C'CA10'),                               
                       OR,(033,4,CH,EQ,C'AUTH'))                               
ICE201I 0 RECORD TYPE IS V - DATA STARTS IN POSITION 5                         
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED   
ICE088I 0 RJMG4030.SORT    .        , INPUT LRECL = 2030, BLKSIZE = 27998, TYPE
ICE093I 0 MAIN STORAGE = (MAX,4194304,4194304)                                 
ICE156I 0 MAIN STORAGE ABOVE 16MB = (4119453,4119453)                           
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,
ICE128I 0 OPTIONS: SIZE=4194304,MAXLIM=1048576,MINLIM=450560,EQUALS=Y,LIST=Y,ERE
ICE129I 0 OPTIONS: VIO=Y,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=
ICE131I 0 OPTIONS: TMAXLIM=4194304,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=
ICE132I 0 OPTIONS: VLSHRT=Y,ZDPRINT=N,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE    ,EXITC
ICE133I 0 OPTIONS: HIPRMAX=100    ,DSPSIZE=100 ,ODMAXBF=0,SOLRF=N,VLLONG=N,VSAMI
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT                                   
ICE084I 0 EXCP ACCESS METHOD USED FOR SORTIN                                   
ICE090I 0 OUTPUT LRECL = 2030, BLKSIZE = 27998, TYPE = VB                       
ICE055I 0 INSERT 0, DELETE 2475589                                             
ICE054I 0 RECORDS - IN: 2493783, OUT: 18194                                     
ICE751I 0 C5C6C7C8E9C9E5E7EFF0E8                                               
ICE052I 0 END OF DFSORT                                                         
******************************** BOTTOM OF DATA ********************************


After
Code:

********************************* TOP OF DATA **********************************
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE000I 1 - CONTROL STATEMENTS FOR 5740-SM1, DFSORT REL 14.0 - 09:12 ON FRI APR
            SORT FIELDS=COPY                                                   
            OPTION VLSCMP                                                       
            INCLUDE COND=((226,4,CH,EQ,C'CA10'),                               
                       OR,(033,4,CH,EQ,C'AUTH'))                               
ICE201I 0 RECORD TYPE IS V - DATA STARTS IN POSITION 5                         
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED   
ICE088I 0 RJMG4030.SORT    .        , INPUT LRECL = 2030, BLKSIZE = 27998, TYPE
ICE093I 0 MAIN STORAGE = (MAX,4194304,4194304)                                 
ICE156I 0 MAIN STORAGE ABOVE 16MB = (4119453,4119453)                           
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=Y,SZERO=Y,
ICE128I 0 OPTIONS: SIZE=4194304,MAXLIM=1048576,MINLIM=450560,EQUALS=Y,LIST=Y,ERE
ICE129I 0 OPTIONS: VIO=Y,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=
ICE131I 0 OPTIONS: TMAXLIM=4194304,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=
ICE132I 0 OPTIONS: VLSHRT=Y,ZDPRINT=N,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE    ,EXITC
ICE133I 0 OPTIONS: HIPRMAX=100    ,DSPSIZE=100 ,ODMAXBF=0,SOLRF=N,VLLONG=N,VSAMI
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT                                   
ICE084I 0 EXCP ACCESS METHOD USED FOR SORTIN                                   
ICE090I 0 OUTPUT LRECL = 2030, BLKSIZE = 27998, TYPE = VB                       
ICE055I 0 INSERT 0, DELETE 1707328                                             
ICE054I 0 RECORDS - IN: 2493783, OUT: 786455                                   
ICE751I 0 C5C6C7C8E9C9E5E7EFF0E8                                               
ICE052I 0 END OF DFSORT                                                         
******************************** BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Fri Apr 08, 2005 8:40 pm
Reply with quote

Good. Thanks for providing feedback.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts DFsort help with SUM() DFSORT/ICETOOL 12
Search our Forums:

Back to Top