runstats within open transaction

[Klemens]

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

[Serge Rielau]

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

[Klemens]

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

[Serge Rielau]

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

[Artur]

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