Best way to tell when runstats is needed...

[shorti]

DB2 V9.5 on AIX...I am looking for a better way to tell when runstats
is needed. AUTO_MAINT AUTO RUNSTATS does not seem to do the trick so
we added code to at least look at the cardinality and if the
cardinality vs the true number of rows in the table (from COUNT(*) are
10% different we do a runstats.

However, that is good only for changes due to inserts/deletes. We
still have some issues where runstats is needed just from updates and
the card hasnt changed recently. The results end up being occasional
deadlocks when doing MERGES.

DB2 has advised us to do a runstats if the UDI value returned from the
db2pd -tcbstats command is > 50% of the value of cardinality. The
Support gal advised there is no system table that contains the UDI
value and its only held in the pd memory.

I can easily capture the value of UDI using a script but what I want
to do is retrieve the value from C code. OR find a different way to
determine whether runstats is needed. This is the current output of
the db2pd command:

> db2pd -tcbstats tbspaceid=5 tableid=6 -db houstondb

Database Partition 0 -- Database HOUSTONDB-- Active -- Up 0 days
19:13:05 -- Date 01/27/2011 23:58:13

TCB Table Information:
Address TbspaceID TableID PartID MasterTbs MasterTab
TableName SchemaNm ObjClass DataSize LfSize LobSize
XMLSize
0x07000000EF038858 5 6 n/a 5 6
EVALUES IMAIN Perm 178969 0 0
0

TCB Table Stats:
Address TableName SchemaNm Scans UDI
RTSUDI PgReorgs NoChgUpdts Reads FscrUpdates
Inserts Updates Deletes OvFlReads OvFlCrtes RowsComp
RowsUncomp CCLogReads StoreBytes BytesSaved
0x07000000EF038858 EVALUES IMAIN 6 67308
67308 0 5072 51292579 1112
10000 57308 0 0 0 0
0 57 - -

Sorry for the lousy formatting but its the UDI value (in this case
67308). As you can see the value can be big so returning it from a
script via a system call would not work either.

Any suggestions?

[sanjay kumar]

There is no such thing in DB2 afaik. You will have to use your own judgement tools based on facts. It could be periodic runstats (which works great for me, I run it every Sundays). DB2 doesn't track any details about number of UDI statements. Ofcourse it would have been good but if you need it, you can write your custom scripts to get this information. Try utilizing event monitors...

> On Thursday, January 27, 2011 7:02 PM shorti wrote:

> DB2 V9.5 on AIX...I am looking for a better way to tell when runstats
> is needed. AUTO_MAINT AUTO RUNSTATS does not seem to do the trick so
> we added code to at least look at the cardinality and if the
> cardinality vs the true number of rows in the table (from COUNT(*) are
> 10% different we do a runstats.
>
> However, that is good only for changes due to inserts/deletes. We
> still have some issues where runstats is needed just from updates and
> the card hasnt changed recently. The results end up being occasional
> deadlocks when doing MERGES.
>
> DB2 has advised us to do a runstats if the UDI value returned from the
> db2pd -tcbstats command is > 50% of the value of cardinality. The
> Support gal advised there is no system table that contains the UDI
> value and its only held in the pd memory.
>
> I can easily capture the value of UDI using a script but what I want
> to do is retrieve the value from C code. OR find a different way to
> determine whether runstats is needed. This is the current output of
> the db2pd command:
>
>

> Database Partition 0 -- Database HOUSTONDB-- Active -- Up 0 days
> 19:13:05 -- Date 01/27/2011 23:58:13
>
> TCB Table Information:
> Address TbspaceID TableID PartID MasterTbs MasterTab
> TableName SchemaNm ObjClass DataSize LfSize LobSize
> XMLSize
> 0x07000000EF038858 5 6 n/a 5 6
> EVALUES IMAIN Perm 178969 0 0
> 0
>
> TCB Table Stats:
> Address TableName SchemaNm Scans UDI
> RTSUDI PgReorgs NoChgUpdts Reads FscrUpdates
> Inserts Updates Deletes OvFlReads OvFlCrtes RowsComp
> RowsUncomp CCLogReads StoreBytes BytesSaved
> 0x07000000EF038858 EVALUES IMAIN 6 67308
> 67308 0 5072 51292579 1112
> 10000 57308 0 0 0 0
> 0 57 - -
>
> Sorry for the lousy formatting but its the UDI value (in this case
> 67308). As you can see the value can be big so returning it from a
> script via a system call would not work either.
>
> Any suggestions?

> Submitted via EggHeadCafe
> ASP.NET Base64 Image Encoding via the Data: protocol
> http://www.eggheadcafe.com/tutorials/aspnet/70f76817-e6d2-415d-8849-849d8c6c5385/aspnet-base64-image-encoding-via-the-data-protocol.aspx