>I guess I'm missing what the difference was at the times you called the >CL..... Did you orignally call it with no LFs built than L1, than L2 ? yes >Could you include the actual Messages from the Optimizer in another Post ? funny you should ask, see notes below (*) -L1 logical only (no selection criteria on logical) Access path of file SHP72PTDL1 was used by query. ... for reason code 2. 2 - Ordering/grouping criteria. -L2 logical only (selection criteria in logical): All access paths were considered for file SHP72PTD. Following each access path name in the list is a reason code which explains why the access path was not used. A reason code of 0 indicates that the access path was used to implement the query. RBTEST/SHP72PTDL2 4. ...... 4 - The cost to use this access path, as determined by the optimizer, was higher than the cost associated with the chosen access method. both L1 and L2: All access paths were considered for file SHP72PTD. Following each access path name in the list is a reason code which explains why the access path was not used. A reason code of 0 indicates that the access path was used to implement the query. RBTEST/SHP72PTDL1 0, RBTEST/SHP72PTDL2 5. 5 - The keys of the access path did not match the fields specified for the ordering/grouping criteria. For distributed file queries, the access path keys must exactly match the ordering fields if the access path is to be used when ALWCPYDTA(*YES or *NO) is specified. * interestingly enough, the first one also recommended building the following logical: FRMLC9, FRMLOC, SHPFOR. ---original message--------- hey all, I decided to do some bench testing on using opnqry with different types of logicals to see what access paths are chosen. i have a file - about 500,000 records, not keyed. My CL is as follows: PGM OVRDBF FILE(SHP72PTD) SHARE(*YES) OPNQRYF FILE((SHP72PTD)) QRYSLT('FRMLC9 = "9" *AND + FRMLOC = "81" *AND SHPFOR = "SHP"') + KEYFLD((FRMLC9) (FRMLOC) (TOLOC) (PRDCDE) + (SHPFOR)) /* shptestr does nothing but read every record in the file, input primary, by key */ CALL PGM(SHPTESTR) CLOF OPNID(SHP72PTD) DLTOVR FILE(*ALL) ENDPGM I have 2 logical file DDS members (as yet not built) * SHP72PTDL1 A R SHP72PTDR PFILE(SHP72PTD) A K FRMLC9 A K FRMLOC A K TOLOC A K PRDCDE A K SHPFOR and *SHP72PTDL2 A R SHP72PTDR PFILE(SHP72PTD) A K FRMLC9 A K FRMLOC A K TOLOC A K PRDCDE A K SHPFOR A S FRMLC9 COMP(EQ '9') A FRMLOC COMP(EQ '81') A SHPFOR COMP(EQ 'SHP') note, the L2 logical is exactly the sequence and selection criteria as the opnqryf. I ran the call the CL 3 times with debug turned on so I could see the query optimizer messages. 1. no logicals built: it created the access path, as expected. 2. L1 only built: it considered all paths and used the existing L1 path, as expected. 3. L2 only built. it considered all paths, and decided to create it's own. why didn't it use the L2 logical? it was exactly what it needed? I'm assuming it's because it won't consider existing access paths that have record selections, because the chances if it meeting the selection criteria is slim to none. I guess what I'm asking is, if you're going to do record selection, rather do it via opnqryf than by logical, because you'll end up having a ton of single purpose logicals, and if you do ever happen to use opnqryf, your logicals with record selection won't ever be considered - wasted access paths. Comments? BTW, I have the bench times involved too if anyone is interested. Thanks,
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.