467,171 Members | 1,187 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

runstats within open transaction

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
  • viewed: 1608
Share:
4 Replies
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
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
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
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.

Similar topics

3 posts views Thread by Jan Arickx | last post: by
7 posts views Thread by Todd McNeill | last post: by
4 posts views Thread by kenfar | last post: by
reply views Thread by RG | last post: by
6 posts views Thread by BobRoyAce | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.