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.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
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.
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.
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.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
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.
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 ********************************
.jpg)
