cloning catalog stats

william giannelli

cloning catalog stats

Hello,

I want to test a v12 migration in our sand box. But first I want to copy our production catalog stats into our sand box. How do I go about that? I do not want to copy the data.

thanks

Bill

Michael Hannan

RE: cloning catalog stats
(in response to william giannelli)

Bill,

It is documented which Db2 Catalog Stats are used by the Optimizer and are user updatable. You can get 3rd party products that do the job. I don't know if any of these products are cost effective value for money, if your shop does not have that product already.

Years ago I wrote my own queries in SQL to generate output formatted as DML statements Updates for most tables and Inserts for SYSCOLDIST.  Not being a DBA any more, I have not maintained it to cover SYSKEYTARGETS and Histogram Stats, and be able to do Unicode encoded columns. Not a massive job for me to update it but not a priority. Note that in the modern day, HIGH2KEY, LOW2KEY, and COLVALUE can have a length much long than 1 line of SQL text especially quoting values in Hexadecimal format, which safer for imbedded special characters. So concatenate a few shorter strings in the SQL.

Your process needs to convert CREATORs and DBNAMEs to naming convention for your target environment, and have Commits at some interval.

Building a Stats migrater is way less work than building a DDL generator, but still need to have a good understanding of the Stats. Also need to have authority to run the Stats updates in target environment, which I typically don't have any more. I have also built processes to  compare DB2 definitions between environments in the far past, which can compare stats too to validate they match, and recently to compare access paths from REBINDs to keep a REBIND history with indicator when statement access paths changed or not. 

In Reply to william giannelli:

Hello,

I want to test a v12 migration in our sand box. But first I want to copy our production catalog stats into our sand box. How do I go about that? I do not want to copy the data.

thanks

Bill

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Miquel Blanco

cloning catalog stats
(in response to william giannelli)
Hello Bill,

You can clone stats using IBM provided stored proc ADMIN_INFO_SQL or Data
Studio Query Environment Capture aka "Service SQL" ( which calls
ADMIN_INFO_SQL under the covers)

ADMIN_INFO_SQL was designed to gather DDL/STATS inserts/ZPARM for access
path problem analysis / recreates in IBM, so if you ever opened perfm
ticket with IBM, you may have it in place already.

This stored proc ADMIN_INFO_SQL requires as input parms, entries from
PLAN_TABLE, so you first need to do EXPLAIN on table that you are
interested in..
(you obviously can do EXPLAIN for.. SELECT * Table1... TableN)

More here ->
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sp_admininfosql.html
and how to install / run, here ->
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/trbshoot/src/tpc/db2z_collectservicesql.html

Michal

On Sat, Jul 6, 2019 at 9:02 AM william giannelli <[login to unmask email]>
wrote:

> Hello,
>
> I want to test a v12 migration in our sand box. But first I want to copy
> our production catalog stats into our sand box. How do I go about that? I
> do not want to copy the data.
>
> thanks
>
> Bill
>
> -----End Original Message-----
>

Ray Lopez

RE: cloning catalog stats
(in response to Miquel Blanco)

Hi Bill, 
If you use DB2 Admin you can use that to MIGRATE (Capture)  and Apply runstats.   Then you can take those jobs and put them in batch so you can run them when you need to.  We "copy" our prod stats to a lower environment so we can do EXPLAINS well before the code gets to Prod, yet give us a good reflection of what the Prod performance will be like.   We do this monthly.   If you like, you can email me at [login to unmask email] and I can forward you a short doc on our process. I have also uploaded the anonymized doc (i.e. BIGCO)  Ray Lopez

Attachments

  • BIGCO Runstats Copy Jobs.doc (675.5k)