Table Scans on Declared Temp Table with Indexes

Bill Gallagher

Table Scans on Declared Temp Table with Indexes
Hi,

We're running DB2 OS/390 v6 at fairly current RMU level.

We have an application that is testing a conversion from regular tables to
declared temporary tables for two work tables in their application. In
their programs, they are declaring the temp tables as well as a pair of
indexes on each of the tables, and these appear to be created successfully.
However, at execution time, I'm seeing an excessive amount of getpage
activity on the temp tablespace, far more than we used to see on the
regular tablespace where the old regular tables reside. I'm suspecting
that DB2 is performing table scans against the declared temp tables as
opposed to using the indexes, but I have no way of verifying this.

Some questions:

1) How can I verify that DB2 is using or not using the indexes on a
declared temp table at run time? Can I perform an explain? Or what should
I be looking for in my DB2 monitor?

2) Does anybody have any insight on how DB2 chooses when to use indexes as
opposed to table scans on declared temp tables?

Thanks,

-------------------------------------------------------
Bill Gallagher
Database Administrator
Information Technology
Infrastructure / Operations
Phoenix Life Insurance Company
860-403-1773
[login to unmask email]




***********************************************************************************
CONFIDENTIAL: This communication, including attachments, is intended only for
the exclusive use of addressee and may contain proprietary, confidential
and/or privileged information. If you are not the intended recipient, you are
hereby notified that you have received this document in error, and any use,
review, copying, disclosure, dissemination or distribution is strictly
prohibited. If you are not the intended recipient, please notify the sender
immediately by return e-mail, delete this communication and destroy any and
all copies of this communication.

***********************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Table Scans on Declared Temp Table with Indexes
(in response to Bill Gallagher)
Your monitor should be able to tell you whether you are reading the tablespace or the index AND the tablespace - that's the first clue

Also, thinking in real-time (not having had much to do with declared temp tables) I am wondering.....

All access to these is dynamic (there is no table at bind tike so the statements cannot be static). If you have a monitor (like Insight) that lets you trace IFCID 22 records, you will see the "explain" output of the prepares

Also, if you SELECT from one of these when they are empty, then you will (of course) get a tablespace scan. If you then populate the table and SELECT again, I bet DB2 will use a cached access path (if you have enabled dynamic statement caching). If you populate the table BEFORE selecting it, then Db2 should use internal statistics to determine the most cost effective access path (note the word "should".....)

For now, I'd look to see if there was any way I could get to those IFCID 22's - they will be very clear what is happing (if not the why!)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: 07 January 2004 13:01
To: [login to unmask email]
Subject: Table Scans on Declared Temp Table with Indexes


Hi,

We're running DB2 OS/390 v6 at fairly current RMU level.

We have an application that is testing a conversion from regular tables to
declared temporary tables for two work tables in their application. In
their programs, they are declaring the temp tables as well as a pair of
indexes on each of the tables, and these appear to be created successfully.
However, at execution time, I'm seeing an excessive amount of getpage
activity on the temp tablespace, far more than we used to see on the
regular tablespace where the old regular tables reside. I'm suspecting
that DB2 is performing table scans against the declared temp tables as
opposed to using the indexes, but I have no way of verifying this.

Some questions:

1) How can I verify that DB2 is using or not using the indexes on a
declared temp table at run time? Can I perform an explain? Or what should
I be looking for in my DB2 monitor?

2) Does anybody have any insight on how DB2 chooses when to use indexes as
opposed to table scans on declared temp tables?

Thanks,

-------------------------------------------------------
Bill Gallagher
Database Administrator
Information Technology
Infrastructure / Operations
Phoenix Life Insurance Company
860-403-1773
[login to unmask email]




***********************************************************************************
CONFIDENTIAL: This communication, including attachments, is intended only for
the exclusive use of addressee and may contain proprietary, confidential
and/or privileged information. If you are not the intended recipient, you are
hereby notified that you have received this document in error, and any use,
review, copying, disclosure, dissemination or distribution is strictly
prohibited. If you are not the intended recipient, please notify the sender
immediately by return e-mail, delete this communication and destroy any and
all copies of this communication.

***********************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bob Irving

Re: Table Scans on Declared Temp Table with Indexes
(in response to Phil Grainger)
Hi- The first part of the question - You need to cut/ paste all of the SQL
unti-of-work the SQL - DECLARE and CREATE INDEX and the INSERT / SELECT out of
the temp table statement and run a dynamic explain in SPUFI - It will show
you the anticipated access path chosen. In areas that we've used temp tables
and indexes we've indexed all columns in the temp table and have been pleased
with the performance results.
Without knowing the usage here, and if it is a batch or on-line app reusing
a temp table (delete old rows, insert the next set of rows) or dropping and
recreating the temp table every time, I cnanot comment, but we do know that IBM
has some APARS out there surrounding DTT's entries not being removed from the
EDM pool at task termination or drop, and also, DTT, if you are deleting and
reinserting rows over and over again, the space is not being reused,and the
temp dataset(s) get extended and additional allocations in your tempdb occur,
which can slow things down.
Hope this helps

Thanks.
Bob Irving
Fidelity Investments DB Services


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sushanta K Dash

Re: Table Scans on Declared Temp Table with Indexes
(in response to Bob Irving)
Hi Phil, here is a clarification I need. I m not sure for any new
improvements are coming up for this in V8 for dynamic cache. You had
mentioned that 'I bet DB2 will use a cached access path (if you have enabled
dynamic statement caching)'. But as far as v7 goes and we are using DTT,
then statements are executed as dynamic sql statement but dynamic cache can
not be used as the table does not exist at bind time. I m sure you must
have more experiences and felt it practically. Could you give us more
insights in to it.

With thanks
Dash

DB2 / IMS Database Administration Tel: 91 ( 44 )
28113801. X-2366.
India Solution Center Fax: 91 (44 )
28113790.
Chennai.
E-mail : [login to unmask email] Dash
____________________________________________________________________________
_____________
"The views expressed are my own and do not necessarily represent the views
or policy of my employer"

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Wednesday, 7 January 2004 7:23 PM
To: [login to unmask email]
Subject: Re: Table Scans on Declared Temp Table with Indexes

Your monitor should be able to tell you whether you are reading the
tablespace or the index AND the tablespace - that's the first clue

Also, thinking in real-time (not having had much to do with declared temp
tables) I am wondering.....

All access to these is dynamic (there is no table at bind tike so the
statements cannot be static). If you have a monitor (like Insight) that lets
you trace IFCID 22 records, you will see the "explain" output of the
prepares

Also, if you SELECT from one of these when they are empty, then you will (of
course) get a tablespace scan. If you then populate the table and SELECT
again, I bet DB2 will use a cached access path (if you have enabled dynamic
statement caching). If you populate the table BEFORE selecting it, then Db2
should use internal statistics to determine the most cost effective access
path (note the word "should".....)

For now, I'd look to see if there was any way I could get to those IFCID
22's - they will be very clear what is happing (if not the why!)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: 07 January 2004 13:01
To: [login to unmask email]
Subject: Table Scans on Declared Temp Table with Indexes


Hi,

We're running DB2 OS/390 v6 at fairly current RMU level.

We have an application that is testing a conversion from regular tables to
declared temporary tables for two work tables in their application. In
their programs, they are declaring the temp tables as well as a pair of
indexes on each of the tables, and these appear to be created successfully.
However, at execution time, I'm seeing an excessive amount of getpage
activity on the temp tablespace, far more than we used to see on the
regular tablespace where the old regular tables reside. I'm suspecting
that DB2 is performing table scans against the declared temp tables as
opposed to using the indexes, but I have no way of verifying this.

Some questions:

1) How can I verify that DB2 is using or not using the indexes on a
declared temp table at run time? Can I perform an explain? Or what should
I be looking for in my DB2 monitor?

2) Does anybody have any insight on how DB2 chooses when to use indexes as
opposed to table scans on declared temp tables?

Thanks,

-------------------------------------------------------
Bill Gallagher
Database Administrator
Information Technology
Infrastructure / Operations
Phoenix Life Insurance Company
860-403-1773
[login to unmask email]




****************************************************************************
*******
CONFIDENTIAL: This communication, including attachments, is intended only
for
the exclusive use of addressee and may contain proprietary, confidential
and/or privileged information. If you are not the intended recipient, you
are
hereby notified that you have received this document in error, and any use,
review, copying, disclosure, dissemination or distribution is strictly
prohibited. If you are not the intended recipient, please notify the sender
immediately by return e-mail, delete this communication and destroy any and
all copies of this communication.

****************************************************************************
*******

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can
be reached at [login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can
be reached at [login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Max Scarpa

Re: Table Scans on Declared Temp Table with Indexes
(in response to Sushanta K Dash)
There's an (very very) old PTF dealing with index usage (or non-usage),
UQ42667,but probably you've it as you said you're current with DB2
maintanance. But sometimes happens that not all PTFs are applied to correct
some bugs in some features you think you'll never use, who knows ?

Regards

Max Scarpa

DB2 sysprog
Storage administrator
WLM Admininistrator

CESVE - SPA

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm