By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

runstats within open transaction

P: n/a
I want to do runstats within an open transaction to get best optimization
for current distribution but in the past I have recognized that runstats had
no effect on the next Statements within that transaction.
Is this behavior like it should be or could I expect the optimizer to use
the updated but not commited statisticts within this transaction?

Thanks
Klemens

May 11 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Klemens wrote:
I want to do runstats within an open transaction to get best optimization
for current distribution but in the past I have recognized that runstats had
no effect on the next Statements within that transaction.
Is this behavior like it should be or could I expect the optimizer to use
the updated but not commited statisticts within this transaction?

I think it's more likely that DB2 is re-using cached and compiled
statements. Is this interactive SQL or a procedure?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 11 '06 #2

P: n/a
This is from clp script with autocommit off.

Thanks
Klemens

"Serge Rielau" <sr*****@ca.ibm.com> schrieb im Newsbeitrag
news:4c*************@individual.net...
Klemens wrote:
I want to do runstats within an open transaction to get best optimization for current distribution but in the past I have recognized that runstats had no effect on the next Statements within that transaction.
Is this behavior like it should be or could I expect the optimizer to use the updated but not commited statisticts within this transaction?

I think it's more likely that DB2 is re-using cached and compiled
statements. Is this interactive SQL or a procedure?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

May 11 '06 #3

P: n/a
Klemens wrote:
This is from clp script with autocommit off.

Thanks
Klemens

"Serge Rielau" <sr*****@ca.ibm.com> schrieb im Newsbeitrag
news:4c*************@individual.net...
Klemens wrote:
I want to do runstats within an open transaction to get best optimization for current distribution but in the past I have recognized that runstats had no effect on the next Statements within that transaction.
Is this behavior like it should be or could I expect the optimizer to use the updated but not commited statisticts within this transaction?

I think it's more likely that DB2 is re-using cached and compiled
statements. Is this interactive SQL or a procedure?

Just for debugging purposes, after the runstats slightly modify the SQL
statement. E.g.
If you wrote:
SELECT * FROM SYSIBM.SYSDUMMY1
change it to:
SELECT * FROM SYSIBM.SYSDUMMY1
Thsi will make suer you et a new statement.
If that fixes the problem then the cache is your problem.
FLUSH PACKAGE CACHE should do the job then, but keep in mind it's global.
http://publib.boulder.ibm.com/infoce...n/r0007117.htm
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 11 '06 #4

P: n/a
Serge,

I'm just wondering if optimizer can read uncommitted data from system
catalogs?

Runstats in fact need commit, If after runstats rollback is issued, all
the system catalog statistics disappear.
-- Artur Wronski

May 11 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.