Statistics on temporary tables?

[w.l.fischer]

Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.

[Serge Rielau]

w.*********@googlemail.com wrote:

Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.

Absolutely.
If you want to programatically collect statistics with a procedure
declaring a temp use the ADMIN_CMD() procedure

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[stefan.albert]

Ok, that works. Now I want the explain with detailed information:
Which subsection works on what part of the SQL? This is provided by
"dynexplain" or "db2expln". But: I can't run statistics and then make
the explain, because for "db2expln" in the -setup option no runstats
are supported.
So I have NO chance to see the "optimzed" distribution of the SQL into
subsections when using runstats on DGTTs.
This is very helpful for further analysis: Which part of the running
SQL causes this high CPU/MEM-usage in subsection 6 (e.g.). This
information is given by snapshot for application for a running SQL.
But with runstats NO chance to see the part of SQL which is processed
in subsection 6...
On Sep 13, 8:31*pm, Serge Rielau <[e-mail]:

[e-mail] wrote:

Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.

Absolutely.
If you want to programatically collect statistics with a procedure
declaring a temp use the ADMIN_CMD() procedure

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

On Sep 24, 1:40*am, "stefan.albert" <[e-mail]:

Ok, that works. Now I want the explain with detailed information:
Which subsection works on what part of the SQL? This is provided by
"dynexplain" or "db2expln". But: I can't run statistics and then make
the explain, because for "db2expln" in the -setup option no runstats
are supported.
So I have NO chance to see the "optimzed" distribution of the SQL into
subsections when using runstats on DGTTs.
This is very helpful for further analysis: Which part of the running
SQL causes this high CPU/MEM-usage in subsection 6 (e.g.). This
information is given by snapshot for application for a running SQL.
But with runstats NO chance to see the part of SQL which is processed
in subsection 6...

On Sep 13, 8:31*pm, Serge Rielau <[e-mail]:

[e-mail] wrote:

Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.

Absolutely.
If you want to programatically collect statistics with a procedure
declaring a temp use the ADMIN_CMD() procedure

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi, Stefan.

It's quite possible I'm misunderstanding the core problem you're
trying to solve, so please bear with me, but if the issue is that you
want to EXPLAIN against the DGTT (indeed, the SQL in stored
procedures), you'll want to set a precompile option, to wit:

CALL SET_ROUTINE_OPTS_GET_ROUTINE_OPTS()||' EXPLAIN ALL');

And then run, e.g., db2exfmt

If the issue is that you're just lamenting that only abridged
statistics are collected and displayed for DGTTs, you're right.

--Jeff

[stefan.albert]

Hi Jeff,

the problem is, that with db2exfmt I don't see the the subsections
involved. This is only available with db2expln.
And to do this, the runstats have to be put in the -setup section to
run, not to be explained (which is quite trivial).
But "runstats" is not supported in the -setup section... (but "declare
global temporary table..." is!)

Why I want to see the subsections:
When SQL runs, I see the CPU/RAM usage of the agents in all
subsections.
Sometimes one subsection uses very much CPU/RAM.
This I want to analyze and try to reduce this. Therefore I need to
know which part of the (complex) SQL is processed in this special
subsection... to change it and see what happens...

[jefftyzzer]

On Sep 25, 1:27*am, "stefan.albert" <[e-mail]:

Hi Jeff,

the problem is, that with db2exfmt I don't see the the subsections
involved. This is only available with db2expln.
And to do this, the runstats have to be put in the -setup section to
run, not to be explained (which is quite trivial).
But "runstats" is not supported in the -setup section... (but "declare
global temporary table..." is!)

Why I want to see the subsections:
When SQL runs, I see the CPU/RAM usage of the agents in all
subsections.
Sometimes one subsection uses very much CPU/RAM.
This I want to analyze and try to reduce this. Therefore I need to
know which part of the (complex) SQL is processed in this special
subsection... to change it and see what happens...

I see. Sounds like I was talking out of turn, as you appear to be
either DPF/EEE or z/OS, neither of which I've experience with. My bad.

--Jeff

[Ian]

stefan.albert wrote:

Hi Jeff,

the problem is, that with db2exfmt I don't see the the subsections
involved. This is only available with db2expln.
And to do this, the runstats have to be put in the -setup section to
run, not to be explained (which is quite trivial).
But "runstats" is not supported in the -setup section... (but "declare
global temporary table..." is!)

As serge suggested, you can use:

call sysproc.admin_cmd('RUNSTATS ON TABLE SESSION....')

[stefan.albert]

Hi Jeff, Ian,

we have an AIX box using DB2 v9.1 FP4 with DPF - thats our
environment.

I tried 3 different ways to run runstats on a DGTT:

1) "simple" runstats command
2) CALL ADMIN_CMD('... - as Ian mentioned that this Serge mentioned
3) we have also a stored procedure using an UDF with external C-
Programm for running runstats via API

All 3 ways fail with the same error.
I had PMR 78890.077.724 about this problem which lead to DCR
(DesignChangeRequest) MR0806087129.

No more informations about since then...
I posted this with the hope, that Serge or another IBMian could bring
a bit more activity to our problem.
Thanks.

[Serge Rielau]

stefan.albert wrote:

Hi Jeff, Ian,

we have an AIX box using DB2 v9.1 FP4 with DPF - thats our
environment.

I tried 3 different ways to run runstats on a DGTT:

1) "simple" runstats command
2) CALL ADMIN_CMD('... - as Ian mentioned that this Serge mentioned
3) we have also a stored procedure using an UDF with external C-
Programm for running runstats via API

All 3 ways fail with the same error.
I had PMR 78890.077.724 about this problem which lead to DCR
(DesignChangeRequest) MR0806087129.

No more informations about since then...
I posted this with the hope, that Serge or another IBMian could bring
a bit more activity to our problem.
Thanks.

If you don't think your PMR is getting the proper attention escalate it.
I'm a poor escalation process... ;-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Henry J.]

On Sep 26, 4:01*am, "stefan.albert" <[e-mail]:

Hi Jeff, Ian,

we have an AIX box using DB2 v9.1 FP4 with DPF - thats our
environment.

I tried 3 different ways to run runstats on a DGTT:

1) "simple" runstats command
2) CALL ADMIN_CMD('... - as Ian mentioned that this Serge mentioned
3) we have also a stored procedure using an UDF with external C-
Programm for running runstats via API

All 3 ways fail with the same error.
I had PMR 78890.077.724 about this problem which lead to DCR
(DesignChangeRequest) MR0806087129.

No more informations about since then...
I posted this with the hope, that Serge or another IBMian could bring
a bit more activity to our problem.
Thanks.

I'm curious that if you create index on a GTT using

create index ..... allow reverse scans collect statistics

will stats be collected automatically when the index is created?
Assume the above "create index" is run after the data are inserted and
before the GTT is used in queries.

Thanks.

[Athrarn]

Hi,

Yes, if you collect statistics on an index created after filling the
temp table of Data, you will have your index with current stats. But
Table statistics will be "out of date".

You will get the message:

Some statistics are in an inconsistent state. The newly collected
"INDEX" statistics are inconsistent with the existing "TABLE"
statistics