468,140 Members | 1,439 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,140 developers. It's quick & easy.

Statistics on temporary tables?

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.
Sep 13 '08 #1
10 5962
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
Sep 13 '08 #2
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 <srie...@ca.ibm.comwrote:
w.l.fisc...@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
Sep 24 '08 #3
On Sep 24, 1:40*am, "stefan.albert" <stefan.alb...@spb.dewrote:
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 <srie...@ca.ibm.comwrote:
w.l.fisc...@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

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
Sep 24 '08 #4
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...
Sep 25 '08 #5
On Sep 25, 1:27*am, "stefan.albert" <stefan.alb...@spb.dewrote:
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
Sep 25 '08 #6
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....')

Sep 25 '08 #7
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.
Sep 26 '08 #8
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
Sep 28 '08 #9
On Sep 26, 4:01*am, "stefan.albert" <stefan.alb...@spb.dewrote:
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.

Oct 19 '08 #10
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
Nov 17 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Chris Weston | last post: by
reply views Thread by Bucker | last post: by
2 posts views Thread by Ulrike Klusik | last post: by
3 posts views Thread by Otto Carl Marte | last post: by
27 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.