Oppolzer - Informatik / Blog

R.K. wrote:

Bernd,

no, these 2 indexes have all been there all the time ...

the table itself has 6 indices, and we changed one of them (neither baddie nor
goodie), and then I rebound all packages referencing the TABLE, not only those
that needed to be rebound, and out of the blue we had tremendous performance
issues not pertaining to the index-change itself but to that particular package
that decided to take a different route.

We then rebound using SWITCH PREVIOUS and the world was blue again, and some
weeks later the package had to be changed (an “ANDö condition in the SQL
had to be removed and strangely enough the column referenced in the
AND-condition is not part of any of the indexes) and again the access path went
astray.

This time we could not go the SWITCH PREVIOUS track, but decided to manually
lower NLEVELS to 2 instead, dynamically explained the package, found the result
to be okay and then rebound the package.

And we haven't come up with any plausible explanation.

Regards

Rüdiger




Von: Bernd Oppolzer [mailto:bernd.oppolzer@t-online.de]
Gesendet: Donnerstag, 10. April 2014 10:57
An: db2-l@lists.idug.org
Betreff: [DB2-L] - RE: [DB2 for z/OS] Index with NULLable columns only

Strange idea:

could something have caused the good index to disappear temporarily
and then an automatic rebind occured, because the application has been run?
The package will not be rebound, if the index re-appears ...

We had this before :-)

Kind regards

Bernd



Am 10.04.2014 09:22, schrieb R.K.:

Hi Bernd,

yes, you got it right -

and here goes:

< Baddie >
CREATE
INDEX PR.INPASAB56
ON PR.TAPASAB
(
PA_ID ASC
,ROSPA_ID ASC
,PASAB_HIST_TSTMP ASC
,PASAB_EING_TSTMP ASC
,SFALLS_ID ASC
)
USING STOGROUP SG000000
PRIQTY 25200
SECQTY -1
ERASE NO
FREEPAGE 0
PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP8K3
CLOSE YES
COPY NO
PIECESIZE 1G
COMPRESS NO
;

< Goodie >
CREATE
INDEX PR.INPASAB60
ON PR.TAPASAB
(
PASAB_P_SFALLS_ID ASC
,PASAB_P_EXT_ID ASC
,PASAB_P_PA_ID ASC
,PASAB_P_SCH_ID ASC
,PASAB_P_ANPART_ID ASC
,PASAB_P_SANPOS_LFD ASC
,PASAB_P_LEIART_ID ASC
,PASAB_P_SLEIS_LFD ASC
)
CLUSTER
PARTITIONED
PARTITION BY RANGE (
PARTITION 1
< . . . >
PARTITION 21
USING STOGROUP SG000000
PRIQTY 576000
SECQTY -1
ERASE NO
FREEPAGE 0
PCTFREE 10
GBPCACHE CHANGED

BUFFERPOOL BP8K3
CLOSE YES
COPY NO
COMPRESS NO ;


UPDATE TAPASAB
SET PASAB_HIST_TSTMP = :PASAB_HIST_TSTMP :PASAB_HIST_TSTMP
WHERE PASAB_P_SFALLS_ID = :SFALLS_ID2
AND PASAB_P_EXT_ID = :SVORGS_EXT_ID2
AND PASAB_P_PA_ID = :PA_ID2
AND PASAB_P_SCH_ID = :SCH_ID2
AND PASAB_P_ANPART_ID = :SDANPART_ID
AND PASAB_P_SANPOS_LFD = :SANPOS_LFDNR
AND PASAB_P_LEIART_ID = :SDLEIART_ID
AND PASAB_P_SLEIS_LFD = :SLEIS_LFDNR
AND ROSPA_ID = :ROSPA_ID
AND PA_ID = :PA_ID
AND (PA_ID3 = 0
OR PA_ID3 IS NULL)
AND PASAB_HIST_TSTMP = '9999-12-31-24.00.00.000000'


Right now, everything's fine and we are keeping a watchful eye on the whole
thing, but we are trying to understand what was going on.

Thanks a bundle for your efforts and suggestions

Rüdiger


-----End Original Message-----