QMF - Determining Index Use

Danny Creed

QMF - Determining Index Use
Depending on release there is a section in the QMF manual "Managing QMF"
chapter "Maintaining a DB2 Subsystem". In this section there is an additional
section entitled "Determining Index Use".

I tried the following from the manual to see if the system I am looking at is
using the index.

"5.10.3 Determining Index Use

QMF query performance can be affected if the QMF application plan is bound when
Q.OBJECT_DATA has
very few entries. Under these circumstances, the index on Q.OBJECT_DATA is not
being used by the
optimizer. (The optimizer is a DB2 function that determines the best ways to
access a row in a table.)
Instead, a table space scan is performed, affecting future performance when
Q.OBJECT_DATA
contains many entries. You need to rebind the plan so that the index is used.

To determine whether the index on Q.OBJECT_DATA is being used, run the following
query:


SELECT BCREATOR, BNAME
FROM SYSIBM.SYSPLANDEP
WHERE DNAME='QMF330'
AND BTYPE='I'


This query selects the owner (BCREATOR) and name (BNAME) of any indexes that the
QMF application plan
is dependent upon. Although QMF330 is the default plan name, use the name used
during QMF installation.
If the result does not indicate an entry for Q.OBJECT_OBJDATAX (Q.OBJECT_DATAX,
if you are migrated
from QMF V2R2), do the following:


Run RUNSTATS on table space DSQDBCTL.DSQTSCT3.
Rebind the QMF application plan. "

After running reorg, runstats, bind and performing the query above, it still
shows that the index is not required.

Question: Is this just a fact that indicates that there is not enough data for
db2 to determine that it needs
to utilize the index?

Runstats are as follows
DSNU614I -DSN1 DSNUSUTB - SYSTABLES CATALOG STATISTICS FOR Q.OBJECT_DATA
CARD = 45347
CARDF = 4.5347E+04
NPAGES = 31308
PCTPAGES = 99
PCTROWCOMP = 0
DSNU610I -DSN1 DSNUSUTB - SYSTABLES CATALOG UPDATE FOR Q.OBJECT_DATA
SUCCESSFUL
DSNU612I -DSN1 DSNUSUTS - SYSTABLESPACE CATALOG STATISTICS FOR
DSQDBCTL.DSQTSCT3
NACTIVE = 31312
DSNU610I -DSN1 DSNUSUTS - SYSTABLESPACE CATALOG UPDATE FOR DSQDBCTL.DSQTSCT3
SUCCESSFUL
DSNU618I -DSN1 DSNUSUIP - SYSINDEXPART CATALOG STATISTICS FOR Q.OBJECT_DATAX
PARTITION 0
CARD = 45347
CARDF = 4.5347E+04
NEAROFFPOS = 2
NEAROFFPOSF= 2.0E+00
FAROFFPOS = 0
FAROFFPOSF = 0.0E0
FAROFFPOSF = 0.0E0
LEAFDIST = 0
DSNU610I -DSN1 DSNUSUIP - SYSINDEXPART CATALOG UPDATE FOR Q.OBJECT_DATAX
SUCCESSFUL
DSNU617I -DSN1 DSNUSUIX - SYSINDEXES CATALOG STATISTICS FOR Q.OBJECT_DATAX
CLUSTERED = Y
CLUSTERRATIO = 100
FIRSTKEYCARD = 739
FIRSTKEYCARDF= 7.39E+02
FULLKEYCARD = 45347
FULLKEYCARDF = 4.5347E+04
NLEAF = 441
NLEVELS = 3
DSNU610I -DSN1 DSNUSUIX - SYSINDEXES CATALOG UPDATE FOR Q.OBJECT_DATAX
SUCCESSFUL
DSNU615I -DSN1 DSNUSUCO - SYSCOLUMNS CATALOG STATISTICS FOR OWNER
COLCARD = 739
COLCARDF = 7.39E+02
HIGH2KEY = X'E9E5C4D6C7D4F740'
LOW2KEY = X'7CC6C9E340404040'
DSNU610I -DSN1 DSNUSUCO - SYSCOLUMNS CATALOG UPDATE FOR Q.OBJECT_DATA
SUCCESSFUL
DSNU616I -DSN1 DSNUSUCD - SYSCOLDIST CATALOG STATISTICS FOR OWNER
FREQUENCY COLVALUE
--------- --------
8.4658301541447E-02 X'D3E4C2C5E2404040'
8.4349571085187E-02 X'D7D3C5C1C4E2C1E3'
5.3807308090943E-02 X'E6D4D7D2C7404040'
3.9208767944957E-02 X'D7C6D6D9E2404040'
3.5702472048867E-02 X'C7C1E2D9E5404040'
3.4776280680089E-02 X'E3C3C1D9C1E3E240'
3.3386993626921E-02 X'D7C5D9E2E8404040'
2.4036871237347E-02 X'D7D4C9C4C2404040'
2.0464418814916E-02 X'E3D9C5E2C4C2F240'
1.6186296778177E-02 X'C7C9E4C1D4404040'
DSNU610I -DSN1 DSNUSUCD - SYSCOLDIST CATALOG UPDATE FOR Q.OBJECT_DATAX
SUCCESSFUL

Thanks for any help in understanding.
Regards
Danny



Venkat (PCA) Pillay

Re: QMF - Determining Index Use
(in response to Danny Creed)
Have you tried running the following query

SELECT BCREATOR, BNAME
FROM SYSIBM.SYSPLANDEP
WHERE DNAME='QMF330'

without the BTYPE='I' ??? Do you still get something out there ?

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, December 16, 1999 9:25 AM
> To: [login to unmask email]
> Subject: QMF - Determining Index Use
>
> Depending on release there is a section in the QMF manual "Managing QMF"
> chapter "Maintaining a DB2 Subsystem". In this section there is an
> additional
> section entitled "Determining Index Use".
>
> I tried the following from the manual to see if the system I am looking at
> is
> using the index.
>
> "5.10.3 Determining Index Use
>
> QMF query performance can be affected if the QMF application plan is bound
> when
> Q.OBJECT_DATA has
> very few entries. Under these circumstances, the index on Q.OBJECT_DATA
> is not
> being used by the
> optimizer. (The optimizer is a DB2 function that determines the best ways
> to
> access a row in a table.)
> Instead, a table space scan is performed, affecting future performance
> when
> Q.OBJECT_DATA
> contains many entries. You need to rebind the plan so that the index is
> used.
>
> To determine whether the index on Q.OBJECT_DATA is being used, run the
> following
> query:
>
>
> SELECT BCREATOR, BNAME
> FROM SYSIBM.SYSPLANDEP
> WHERE DNAME='QMF330'
> AND BTYPE='I'
>
>
> This query selects the owner (BCREATOR) and name (BNAME) of any indexes
> that the
> QMF application plan
> is dependent upon. Although QMF330 is the default plan name, use the name
> used
> during QMF installation.
> If the result does not indicate an entry for Q.OBJECT_OBJDATAX
> (Q.OBJECT_DATAX,
> if you are migrated
> from QMF V2R2), do the following:
>
>
> Run RUNSTATS on table space DSQDBCTL.DSQTSCT3.
> Rebind the QMF application plan. "
>
> After running reorg, runstats, bind and performing the query above, it
> still
> shows that the index is not required.
>
> Question: Is this just a fact that indicates that there is not enough
> data for
> db2 to determine that it needs
> to utilize the index?
>
> Runstats are as follows
> DSNU614I -DSN1 DSNUSUTB - SYSTABLES CATALOG STATISTICS FOR
> Q.OBJECT_DATA
> CARD = 45347
> CARDF = 4.5347E+04
> NPAGES = 31308
> PCTPAGES = 99
> PCTROWCOMP = 0
> DSNU610I -DSN1 DSNUSUTB - SYSTABLES CATALOG UPDATE FOR Q.OBJECT_DATA
> SUCCESSFUL
> DSNU612I -DSN1 DSNUSUTS - SYSTABLESPACE CATALOG STATISTICS FOR
> DSQDBCTL.DSQTSCT3
> NACTIVE = 31312
> DSNU610I -DSN1 DSNUSUTS - SYSTABLESPACE CATALOG UPDATE FOR
> DSQDBCTL.DSQTSCT3
> SUCCESSFUL
> DSNU618I -DSN1 DSNUSUIP - SYSINDEXPART CATALOG STATISTICS FOR
> Q.OBJECT_DATAX
> PARTITION 0
> CARD = 45347
> CARDF = 4.5347E+04
> NEAROFFPOS = 2
> NEAROFFPOSF= 2.0E+00
> FAROFFPOS = 0
> FAROFFPOSF = 0.0E0
> FAROFFPOSF = 0.0E0
> LEAFDIST = 0
> DSNU610I -DSN1 DSNUSUIP - SYSINDEXPART CATALOG UPDATE FOR Q.OBJECT_DATAX
> SUCCESSFUL
> DSNU617I -DSN1 DSNUSUIX - SYSINDEXES CATALOG STATISTICS FOR
> Q.OBJECT_DATAX
> CLUSTERED = Y
> CLUSTERRATIO = 100
> FIRSTKEYCARD = 739
> FIRSTKEYCARDF= 7.39E+02
> FULLKEYCARD = 45347
> FULLKEYCARDF = 4.5347E+04
> NLEAF = 441
> NLEVELS = 3
> DSNU610I -DSN1 DSNUSUIX - SYSINDEXES CATALOG UPDATE FOR Q.OBJECT_DATAX
> SUCCESSFUL
> DSNU615I -DSN1 DSNUSUCO - SYSCOLUMNS CATALOG STATISTICS FOR OWNER
> COLCARD = 739
> COLCARDF = 7.39E+02
> HIGH2KEY = X'E9E5C4D6C7D4F740'
> LOW2KEY = X'7CC6C9E340404040'
> DSNU610I -DSN1 DSNUSUCO - SYSCOLUMNS CATALOG UPDATE FOR
> Q.OBJECT_DATA
> SUCCESSFUL
> DSNU616I -DSN1 DSNUSUCD - SYSCOLDIST CATALOG STATISTICS FOR OWNER
> FREQUENCY COLVALUE
> --------- --------
> 8.4658301541447E-02 X'D3E4C2C5E2404040'
> 8.4349571085187E-02 X'D7D3C5C1C4E2C1E3'
> 5.3807308090943E-02 X'E6D4D7D2C7404040'
> 3.9208767944957E-02 X'D7C6D6D9E2404040'
> 3.5702472048867E-02 X'C7C1E2D9E5404040'
> 3.4776280680089E-02 X'E3C3C1D9C1E3E240'
> 3.3386993626921E-02 X'D7C5D9E2E8404040'
> 2.4036871237347E-02 X'D7D4C9C4C2404040'
> 2.0464418814916E-02 X'E3D9C5E2C4C2F240'
> 1.6186296778177E-02 X'C7C9E4C1D4404040'
> DSNU610I -DSN1 DSNUSUCD - SYSCOLDIST CATALOG UPDATE FOR
> Q.OBJECT_DATAX
> SUCCESSFUL
>
> Thanks for any help in understanding.
> Regards
> Danny
>
>
>
>
>



Danny Creed

Re: QMF - Determining Index Use
(in response to Venkat (PCA) Pillay)
Yes I did,
And there are no rows for QMF330 in SYSPLANDEP. I am sure that that is the
PLAN that is used for QMF in this environment.

Just looking generic, I see the following:
---------+---------+---------+---------+------
SELECT
BNAME ,
BCREATOR ,
BTYPE ,
DNAME ,
IBMREQD
FROM SYSIBM.SYSPLANDEP
WHERE DNAME LIKE 'QMF%'
---------+---------+---------+---------+------
---------+---------+---------+---------+------
BNAME BCREATOR BTYPE DNAME IBM
---------+---------+---------+---------+------
DSQTBACK DSQDBCTL R QMFOBJB N
QMF_OBJECT_BACKUP Q T QMFOBJB N
OBJECT_DATAX Q I QMFOBJB N
DSQTSCT3 DSQDBCTL R QMFOBJB N
OBJECT_DATA Q T QMFOBJB N
OBJECT_REMARKSX Q I QMFOBJB N
DSQTSCT2 DSQDBCTL R QMFOBJB N
OBJECT_REMARKS Q T QMFOBJB N
OBJECT_DIRECTORYX Q I QMFOBJB N
DSQTSCT1 DSQDBCTL R QMFOBJB N
OBJECT_DIRECTORY Q T QMFOBJB N
QMF_OBJECT_INDEX Q I QMFOBJP N
DSQTBACK DSQDBCTL R QMFOBJP N
QMF_OBJECT_BACKUP Q T QMFOBJP N
OBJECT_REMARKSX Q I QMFOBJR N
DSQTSCT2 DSQDBCTL R QMFOBJR N
OBJECT_REMARKS Q T QMFOBJR N
OBJECT_DATAX Q I QMFOBJR N
DSQTSCT3 DSQDBCTL R QMFOBJR N
OBJECT_DATA Q T QMFOBJR N
OBJECT_DIRECTORYX Q I QMFOBJR N
DSQTSCT1 DSQDBCTL R QMFOBJR N
OBJECT_DIRECTORY Q T QMFOBJR N
QMF_OBJECT_INDEX Q I QMFOBJR N
DSQTBACK DSQDBCTL R QMFOBJR N
QMF_OBJECT_BACKUP Q T QMFOBJR N
NUMBER OF ROWS SELECTED 26

I am not sure what the plans are
QMFOBJB
QMFOBJP
QMFOBJR
(all bound in 1993)



Thanks for looking.
Regards
Danny





[login to unmask email] on 12/16/99 08:54:14 AM

To: [login to unmask email]
cc: Daniel S [login to unmask email]
Subject: RE: QMF - Determining Index Use



Have you tried running the following query

SELECT BCREATOR, BNAME
FROM SYSIBM.SYSPLANDEP
WHERE DNAME='QMF330'

without the BTYPE='I' ??? Do you still get something out there ?

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, December 16, 1999 9:25 AM
> To: [login to unmask email]
> Subject: QMF - Determining Index Use
>
> Depending on release there is a section in the QMF manual "Managing QMF"
> chapter "Maintaining a DB2 Subsystem". In this section there is an
> additional
> section entitled "Determining Index Use".
>
> I tried the following from the manual to see if the system I am looking at
> is
> using the index.
>
> "5.10.3 Determining Index Use
>
> QMF query performance can be affected if the QMF application plan is bound
> when
> Q.OBJECT_DATA has
> very few entries. Under these circumstances, the index on Q.OBJECT_DATA
> is not
> being used by the
> optimizer. (The optimizer is a DB2 function that determines the best ways
> to
> access a row in a table.)
> Instead, a table space scan is performed, affecting future performance
> when
> Q.OBJECT_DATA
> contains many entries. You need to rebind the plan so that the index is
> used.
>
> To determine whether the index on Q.OBJECT_DATA is being used, run the
> following
> query:
>
>
> SELECT BCREATOR, BNAME
> FROM SYSIBM.SYSPLANDEP
> WHERE DNAME='QMF330'
> AND BTYPE='I'
>
>
> This query selects the owner (BCREATOR) and name (BNAME) of any indexes
> that the
> QMF application plan
> is dependent upon. Although QMF330 is the default plan name, use the name
> used
> during QMF installation.
> If the result does not indicate an entry for Q.OBJECT_OBJDATAX
> (Q.OBJECT_DATAX,
> if you are migrated
> from QMF V2R2), do the following:
>
>
> Run RUNSTATS on table space DSQDBCTL.DSQTSCT3.
> Rebind the QMF application plan. "
>
> After running reorg, runstats, bind and performing the query above, it
> still
> shows that the index is not required.
>
> Question: Is this just a fact that indicates that there is not enough
> data for
> db2 to determine that it needs
> to utilize the index?
>
> Runstats are as follows
> DSNU614I -DSN1 DSNUSUTB - SYSTABLES CATALOG STATISTICS FOR
> Q.OBJECT_DATA
> CARD = 45347
> CARDF = 4.5347E+04
> NPAGES = 31308
> PCTPAGES = 99
> PCTROWCOMP = 0
> DSNU610I -DSN1 DSNUSUTB - SYSTABLES CATALOG UPDATE FOR Q.OBJECT_DATA
> SUCCESSFUL
> DSNU612I -DSN1 DSNUSUTS - SYSTABLESPACE CATALOG STATISTICS FOR
> DSQDBCTL.DSQTSCT3
> NACTIVE = 31312
> DSNU610I -DSN1 DSNUSUTS - SYSTABLESPACE CATALOG UPDATE FOR
> DSQDBCTL.DSQTSCT3
> SUCCESSFUL
> DSNU618I -DSN1 DSNUSUIP - SYSINDEXPART CATALOG STATISTICS FOR
> Q.OBJECT_DATAX
> PARTITION 0
> CARD = 45347
> CARDF = 4.5347E+04
> NEAROFFPOS = 2
> NEAROFFPOSF= 2.0E+00
> FAROFFPOS = 0
> FAROFFPOSF = 0.0E0
> FAROFFPOSF = 0.0E0
> LEAFDIST = 0
> DSNU610I -DSN1 DSNUSUIP - SYSINDEXPART CATALOG UPDATE FOR Q.OBJECT_DATAX
> SUCCESSFUL
> DSNU617I -DSN1 DSNUSUIX - SYSINDEXES CATALOG STATISTICS FOR
> Q.OBJECT_DATAX
> CLUSTERED = Y
> CLUSTERRATIO = 100
> FIRSTKEYCARD = 739
> FIRSTKEYCARDF= 7.39E+02
> FULLKEYCARD = 45347
> FULLKEYCARDF = 4.5347E+04
> NLEAF = 441
> NLEVELS = 3
> DSNU610I -DSN1 DSNUSUIX - SYSINDEXES CATALOG UPDATE FOR Q.OBJECT_DATAX
> SUCCESSFUL
> DSNU615I -DSN1 DSNUSUCO - SYSCOLUMNS CATALOG STATISTICS FOR OWNER
> COLCARD = 739
> COLCARDF = 7.39E+02
> HIGH2KEY = X'E9E5C4D6C7D4F740'
> LOW2KEY = X'7CC6C9E340404040'
> DSNU610I -DSN1 DSNUSUCO - SYSCOLUMNS CATALOG UPDATE FOR
> Q.OBJECT_DATA
> SUCCESSFUL
> DSNU616I -DSN1 DSNUSUCD - SYSCOLDIST CATALOG STATISTICS FOR OWNER
> FREQUENCY COLVALUE
> --------- --------
> 8.4658301541447E-02 X'D3E4C2C5E2404040'
> 8.4349571085187E-02 X'D7D3C5C1C4E2C1E3'
> 5.3807308090943E-02 X'E6D4D7D2C7404040'
> 3.9208767944957E-02 X'D7C6D6D9E2404040'
> 3.5702472048867E-02 X'C7C1E2D9E5404040'
> 3.4776280680089E-02 X'E3C3C1D9C1E3E240'
> 3.3386993626921E-02 X'D7C5D9E2E8404040'
> 2.4036871237347E-02 X'D7D4C9C4C2404040'
> 2.0464418814916E-02 X'E3D9C5E2C4C2F240'
> 1.6186296778177E-02 X'C7C9E4C1D4404040'
> DSNU610I -DSN1 DSNUSUCD - SYSCOLDIST CATALOG UPDATE FOR
> Q.OBJECT_DATAX
> SUCCESSFUL
>
> Thanks for any help in understanding.
> Regards
> Danny
>
>
>
>
>



David Seibert

Re: QMF - Determining Index Use
(in response to Danny Creed)
You should be able to find the information you want by looking in
SYSPACKDEP.

In SYSPACKDEP, Dname contains a package name.
At my installation, the packages in the QMF plan all begin with DSQ.
so change the query to go after SYSPACKDEP and look for DNAME like 'DSQ%'

David Seibert
Compuware Corporation File-AID product planner
[login to unmask email]



Danny Creed

Re: QMF - Determining Index Use
(in response to David Seibert)
Hi Dave,
Sorry to be so dense(wouldn't be the first time), but the query

QMFV3R3 (Managing QMF)

> SELECT BCREATOR, BNAME
> FROM SYSIBM.SYSPLANDEP
> WHERE DNAME='QMF330'
> AND BTYPE='I'

QMFV6 (Installing and Managing QMF on OS/390)
> SELECT BCREATOR, BNAME
> FROM SYSIBM.SYSPLANDEP
> WHERE DNAME='QMF610'
> AND BTYPE='I'

come from the IBM manual.

The statement (V6 sample) says that:
"If the result does not indicate an entry for Q.OBJECT_OBJDATAX
(Q.OBJECT_DATAX, if you are migrated from QMF V2R2), do the following:
1. Run the RUNSTATS on the table space DSQDBCTL.DSQTSCT3.
2. Rebind the QMF application plan."


I peformed a step 0: Reorg, 1. Runstats, 2. Rebind PLAN(QMF330)

Are you suggesting that I misinterperted step 2 Rebind? That I actually need to
rebind certain packages instead of just the individual plan?

Again, any help understanding is always welcomed (and usually needed)

Regards
Danny





[login to unmask email] on 12/16/99 12:15:20 PM

Please respond to [login to unmask email]

To: [login to unmask email]
cc: (bcc: Daniel S Creed/HI/CSC)
Subject: Re: QMF - Determining Index Use



You should be able to find the information you want by looking in
SYSPACKDEP.

In SYSPACKDEP, Dname contains a package name.
At my installation, the packages in the QMF plan all begin with DSQ.
so change the query to go after SYSPACKDEP and look for DNAME like 'DSQ%'

David Seibert
Compuware Corporation File-AID product planner
[login to unmask email]








David Seibert

Re: QMF - Determining Index Use
(in response to Danny Creed)
Hi Danny,
No I'm certainly not suggesting any error on your part.
I'm just pointing out that since I also couldn't find any Plandep
information for the QMF plan, I looked in Packdep and found them all.

I'm suggesting that for your shop and mine, the manual is incorrect. It
would be correct if the QMF DBRMs were bound directly into a plan rather
than into packages and then into the plan. Dependency data is stored in
SYSPLANDEP for plans made of DBRMS. For packages, that dependency data is
found in SYSPACKDEP.
I suspect that it's an old piece of doc. that didn't get updated when QMF
began using packages.

To find the packages in a plan, I used the following:
SELECT P.NAME
FROM SYSIBM.SYSPACKAGE P
, SYSIBM.SYSPACKLIST PL
WHERE PL.PLANNAME = 'QMF311'
AND P.COLLID = PL.COLLID

David Seibert
Compuware Corporation File-AID product planner
[login to unmask email]



Danny Creed

Re: QMF - Determining Index Use
(in response to David Seibert)
Thanks Dave,
That makes sense. I guess I will annoy IBM to see if they have any
document updates to show
which packages should be checked and/or rebound. I could probably look for
those that
contain the ts DSQDBCTL DSQTSCT3 and have the tb Q.OBJECT_DATA .

Again thanks
Danny






[login to unmask email] on 12/16/99 02:52:19 PM

Please respond to [login to unmask email]

To: [login to unmask email]
cc: (bcc: Daniel S Creed/HI/CSC)
Subject: Re: QMF - Determining Index Use



Hi Danny,
No I'm certainly not suggesting any error on your part.
I'm just pointing out that since I also couldn't find any Plandep
information for the QMF plan, I looked in Packdep and found them all.

I'm suggesting that for your shop and mine, the manual is incorrect. It
would be correct if the QMF DBRMs were bound directly into a plan rather
than into packages and then into the plan. Dependency data is stored in
SYSPLANDEP for plans made of DBRMS. For packages, that dependency data is
found in SYSPACKDEP.
I suspect that it's an old piece of doc. that didn't get updated when QMF
began using packages.

To find the packages in a plan, I used the following:
SELECT P.NAME
FROM SYSIBM.SYSPACKAGE P
, SYSIBM.SYSPACKLIST PL
WHERE PL.PLANNAME = 'QMF311'
AND P.COLLID = PL.COLLID

David Seibert
Compuware Corporation File-AID product planner
[login to unmask email]