Re: Behavior of indexes after run a CLRPFM

Rui

If you have run a CLRPFM and then immediately do a SELECT, it isn't likely to use an index, since there are no records, and the optimizer knows that.

If you insert your records and get the same number as you had originally, and then you run a SELECT, conditions are similar to when the original plan was built.

But no one here can tell you with 100% certainty about these things. Chuck may be the closest to knowing what goes on. I do know that many things contribute to a new access plan - including number of jobs in the subsystem, total memory available, various details about the table, etc. Since the system can be very fluid in these matters, things can be different for how the optimizer determines its behavior.

I strongly recommend that you read the Query Performance and Optimization manual. It has somewhat different names at each release - at 6.1 it is called Database performance and query optimization and is located at http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/rzajq.pdf

And again, you really need to use Visual Explain in order to get more information. That is a part of iSeries Access (i Access, whatever name) - of the database component. It has been a part of iSeries Access for many years & releases now.

Regards
Vern

On 1/24/2011 5:53 AM, Rui Feliz wrote:

*Before I load the table, I run a CLRPFM over it.

Not sure what "load the table" means here, since there is no LOAD
TABLE statement in the DB2 for i SQL. Presumably however, an SQL SELECT
is implied.?*
**
I'm working on a migration process from one table to another one. Every time I
run the process for testing it, I clean the new table using a CLRPFM
instruction and then I insert the values using SQL Inserts.



*Can the CLRPFM be responsible for the non-use of the indexes?

The optimizer would normally not choose to use an index to implement
a query of an empty table, because an INDEX as a path to the data is one
level of indirection from the data; i.e. why look for data in an INDEX
when the last known condition of the TABLE is zero rows.? The optimizer
will automatically [re]build a new plan when the number of rows changes
"significantly"; i.e. changes enough from what was known to the original
plan, such that perhaps accessing the data via the INDEX versus
sequentially, might now have some value with respect to the cost of
accessing the separate INDEX object(s).
*
So after run a CLRPFM over the table, it is certain that the plan is rebuild
and the information to the indexes are "updated"?

Rui

On Thu, Jan 20, 2011 at 10:12 PM, CRPence<CRPbottle@xxxxxxxxx> wrote:

On 1/20/11 2:22 AM, Rui wrote:

Sometimes, when I run some SQL operations (like a SELECT), the SQL
Optimizer does not use the correct index.

There is not any one\specific "correct index". The optimizer may or
may not choose to implement a query using one or more access paths. SQL
does not support querying an INDEX, for which one might infer forcing
the use of a specific INDEX might be possible.

Before I load the table, I run a CLRPFM over it.

Not sure what "load the table" means here, since there is no LOAD
TABLE statement in the DB2 for i SQL. Presumably however, an SQL SELECT
is implied.?

Can the CLRPFM be responsible for the non-use of the indexes?

The optimizer would normally not choose to use an index to implement
a query of an empty table, because an INDEX as a path to the data is one
level of indirection from the data; i.e. why look for data in an INDEX
when the last known condition of the TABLE is zero rows.? The optimizer
will automatically [re]build a new plan when the number of rows changes
"significantly"; i.e. changes enough from what was known to the original
plan, such that perhaps accessing the data via the INDEX versus
sequentially, might now have some value with respect to the cost of
accessing the separate INDEX object(s).

The RGZPFM fix it?

The RGZPFM is not allowed on an empty member; i.e. CPF2995 for "no data".

Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.