It is not always possible or desirable to Explain
SQL against the Production system. However, if a DBA were to create
a test database “Clone” using a copy of the production
DDL, but without the production Catalog Statistics then, when
the SQL is explained against this “Clone”, the access
paths selected will be meaningless. This is because, when DB2
evaluates the SQL without production Catalog Statistics being
present, DB2 will use default statistics, which will bear no relationship
to the live environment at all. By migrating Catalog Statistics
from a Live system to the Test system, a DBA is able to create
an Optimizer Identical Clone of a production system, so that DBAs
could do “what if” type work on SQL and get the same
access paths as on production.
STATISTICS
How do you MODEL your production system in a
test environment to see what access paths your production queries will use?
How do you TEST the effect on Access Paths of
new statistics in a safe environment before implementing the
changes in Production ?
How do you COPY Production Statistics to Test/QA
environments when the DBNAMES and Table Owner/Creator names
may be different in the different systems ?
How do you easily BACKUP and RESTORE Statistics
on a system, after Loading Statistics from another system or
after running RUNSTATS ?
How do you easily VIEW and EDIT the Statistics
from a particular System while ensuring
required Correlations ?
EZ-STATS allows you to copy the DB2 catalog
statistics used for access path selection between DB2 systems,
allowing you to Explain SQL in a test environment and generate
the same access path as used in production.
EZ-STATS has the following features:-
Unloads Statistics from any DB2 subsystem to
the EZ-STATS Repository for one or more databases at
a time
Display and Edit Statistics in the EZ-STATS
Repository for a particular system and database
When a User manually edits Statistics, EZ-STATS
enforces required correlations in the System Catalog e.g,
COLCARDF and FIRSTKEYCARDF
Loads statistics from the EZ-STATS Repository
to a new DB2 System
DBNAMEs and table owner/creators may be different
on Live and Test systems.
EZ-STATS will automatically translate
DBNAME and entity Owner / Creator names as required when transferring
Statistics from one system to another.
In some environments (e.g. SAP R3), Index names
may vary for a given table in ® different environments.
EZ-STATS will automatically translate the index names
if necessary, recognizing corresponding indexes and updating
the Statistics for the appropriate index.
You may Load ALL Statistics for a given database,
or Select Statistics to Load for particular Table Space(s),
Table(s), Indexes etc.
Backup and Restore Statistics on a particular
system
Some example of the various Display/Edit panels
are shown below:-
List databases extracted to the EZ-STATS
repository. From this panel you can select the target DB2 subsystem
and DBNAME to be updated:
Systables Statistics - shows the SYSTABLES Statistics
for ALL tables for the selected database (DSNDB06):
DRILL on a table name to show the TABLE COLUMN
Statistics or the TABLE INDEX statistics:
SYSTABLES plus SYSCOLUMNS statistics for the Selected
table SYSIBM. SYSCOLUMNS:
SYSTABLES plus SYSINDEXES statistics for the Indexes
associated with the Selected Table:
DRILL on an Index name to show the SYSCOLUMNS
Statistics for the Index Columns:
These are only a few samples of the
various displays available to the user.
Download a product information bulletin on EZ-STATS [pdf]
Developed by
for TACT software
|
