Reverse-engineer catalog statistics collection?

jim.szabo

Reverse-engineer catalog statistics collection?
Environs: DB2/OS 390 V5

We are starting to investigate the use of "enhanced" DB2 catalog statistics to improve access to our DW and other tables. By "enhanced", I mean specifying non-default options for RUNSTATS or the third-party equivalent: TABLE, INDEX, COLUMN, KEYCARD, even use of the DSTATS program and manually inserting data.

My problem comes when these tables/indexes have to undergo a structural change that involves dropping and recreating the objects that statistics have been collected on.

We own a third-party change management product, that builds a script to handle the drop and recreation, and even generates a RUNSTATS/third-party step - but, the options specified as a default for stats collection will not be complete for tables with "enhanced" statistics.

How do others handle this issue? External documentation about statistics collection for the object is great, if I could get each of my DBAs to read and update it.

I believe that our use of "enhanced" statistics will provide us with some big performance wins, but fear that one slipup in not collecting the same degree of statistics post-change will hurt us, and turn us from performance heroes into goats.

Has anyone attempted to "reverse engineer" catalog statistics collection, checking to see what stats exist for the object, and building the appropriate RUNSTATS/third-party utility command from what you find? I realize that this would not catch those stats manually inserted into the STATS tables, but it would be a lot closer to nirvana than what I get now. My third-party tool vendor commented "that's an interesting idea", but made no commitment.

Ideas?

Jim Szabo

jim.szabo

Fw: Reverse-engineer catalog statistics collection?
(in response to jim.szabo)
This is a repost, as I got no responses the first time.



Environs: DB2/OS 390 V5

We are starting to investigate the use of "enhanced" DB2 catalog statistics to improve access to our DW and other tables. By "enhanced", I mean specifying non-default options for RUNSTATS or the third-party equivalent: TABLE, INDEX, COLUMN, KEYCARD, even use of the DSTATS program and manually inserting data.

My problem comes when these tables/indexes have to undergo a structural change that involves dropping and recreating the objects that statistics have been collected on.

We own a third-party change management product, that builds a script to handle the drop and recreation, and even generates a RUNSTATS/third-party step - but, the options specified as a default for stats collection will not be complete for tables with "enhanced" statistics.

How do others handle this issue? External documentation about statistics collection for the object is great, if I could get each of my DBAs to read and update it.

I believe that our use of "enhanced" statistics will provide us with some big performance wins, but fear that one slipup in not collecting the same degree of statistics post-change will hurt us, and turn us from performance heroes into goats.

Has anyone attempted to "reverse engineer" catalog statistics collection, checking to see what stats exist for the object, and building the appropriate RUNSTATS/third-party utility command from what you find? I realize that this would not catch those stats manually inserted into the STATS tables, but it would be a lot closer to nirvana than what I get now. My third-party tool vendor commented "that's an interesting idea", but made no commitment.

Ideas?

Jim Szabo

Paul A Redhead

Fw: Reverse-engineer catalog statistics collection?
(in response to jim.szabo)


Jim,
What some sites (including us) do is to use their Production statistics to
update those columns that are updateable in the catalog in other environments.
We do this by using a SELECT statement on the various catalog tables to produce
UPDATE statements that we then apply to whatever environment. You could use this
same technique to produce UPDATE statements PRIOR to performing the change so
that POST change you could modify the UPDATE statements as required (missing/new
columns) and reapply them after the change.

Hope this helps.

Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 16/12/99
13:09 ---------------------------


"jim.szabo" <[login to unmask email]> on 16/12/99 12:50:15

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Fw: Reverse-engineer catalog statistics collection?




This is a repost, as I got no responses the first time.



Environs: DB2/OS 390 V5

We are starting to investigate the use of "enhanced" DB2 catalog statistics to
improve access to our DW and other tables. By "enhanced", I mean specifying
non-default options for RUNSTATS or the third-party equivalent: TABLE, INDEX,
COLUMN, KEYCARD, even use of the DSTATS program and manually inserting data.

My problem comes when these tables/indexes have to undergo a structural change
that involves dropping and recreating the objects that statistics have been
collected on.

We own a third-party change management product, that builds a script to handle
the drop and recreation, and even generates a RUNSTATS/third-party step - but,
the options specified as a default for stats collection will not be complete for
tables with "enhanced" statistics.

How do others handle this issue? External documentation about statistics
collection for the object is great, if I could get each of my DBAs to read and
update it.

I believe that our use of "enhanced" statistics will provide us with some big
performance wins, but fear that one slipup in not collecting the same degree of
statistics post-change will hurt us, and turn us from performance heroes into
goats.

Has anyone attempted to "reverse engineer" catalog statistics collection,
checking to see what stats exist for the object, and building the appropriate
RUNSTATS/third-party utility command from what you find? I realize that this
would not catch those stats manually inserted into the STATS tables, but it
would be a lot closer to nirvana than what I get now. My third-party tool
vendor commented "that's an interesting idea", but made no commitment.

Ideas?

Jim Szabo


*************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads, or
of Queensland Transport. If you have received this electronic
mail message in error, please immediately notify the sender
and delete the message from your computer.