Temporary index is not taking in the Stored procedure

[Guru]

Hi All

I am using a Global Temporary table in the Stored procedure and i am
creating index for a column in that temporary table.When i am executing
it. It is not taking that index. I checked using explain plan. But if i
run the runstats in the CLP for that temporary table it is taking the
index. How i have to implement in the Stored procedure itself?
Warm Regards
Guru

[Mark A]

"Guru" <gu*********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hi All

I am using a Global Temporary table in the Stored procedure and i am
creating index for a column in that temporary table.When i am executing
it. It is not taking that index. I checked using explain plan. But if i
run the runstats in the CLP for that temporary table it is taking the
index. How i have to implement in the Stored procedure itself?
Warm Regards
Guru

After creating the Global Temporary table, use the following command to
encourage index usuage:

ALTER TABLE table-name VOLATILE;

[Serge Rielau]

Guru wrote:

Hi All

I am using a Global Temporary table in the Stored procedure and i am
creating index for a column in that temporary table.When i am executing
it. It is not taking that index. I checked using explain plan. But if i
run the runstats in the CLP for that temporary table it is taking the
index. How i have to implement in the Stored procedure itself?
Warm Regards
Guru

I can only think of two reasons:
* If you create the index after the query is run the first time it won't
be picked up in later runs since adding an index does not invalidate
existing palns.

* WHen you try from CLP do you use constants where the query in the
procedure used variables? That would change the query sufficiently to
get a different plan. Use parameter markers in CLP (with a CAST(? AS
<variabletype>) if needed to compare apples to apples.
If that turns out to be the difference you can experiment with the
SELECTIVITY clause on the predicates to increase the filter factor or
you can use the REOPT(ONCE) prep option to use the varibale values on
first run to determine filtering.
In DB2 V8.2 I think there is a stored procedure to set the prep options
for SQL Procedures.

Cheers
Serge
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Guru]

Thanks for ur response...

We cannot use the DDL commands on the Temporary tables....

Warm Regards
Guru
Mark A wrote:

"Guru" <gu*********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hi All

I am using a Global Temporary table in the Stored procedure and i am creating index for a column in that temporary table.When i am executing it. It is not taking that index. I checked using explain plan. But if i run the runstats in the CLP for that temporary table it is taking the index. How i have to implement in the Stored procedure itself?
Warm Regards
Guru
After creating the Global Temporary table, use the following command

to encourage index usuage:

ALTER TABLE table-name VOLATILE;

[Guru]

Be specified in an ALTER, COMMENT, GRANT, LOCK, RENAME or REVOKE
statement (SQLSTATE 42995).