Results 1 to 4 of 4

Thread: DB2 Statistics

  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: DB2 Statistics

    Hello everyone

    I have few issues to solve..
    So i need your help and expertise advice.

    I'm in progress task of mimic our company's PRODUCTION db to another DB. But with only the statistics. The purpose here is, our software developers can run their queries against this Statistics DB and see their actual behavior and performance when it on real prod environment.

    So i have done this with following way.

    1. gather the tablespace/bufferpoo/database node group information
    db2look -d <dbname> -l -o storage.txt

    2. Collect the configuration and environment variable information
    db2look -d <dbname> -f -fd -o config.txt

    3. Collect the DDL information for all objects from production
    db2look -d <dbname> -e -a -m -o db2look.txt

    4. Run same query in both DBs with CURRENT EXPLAIN MODE EXPLAIN

    5. Collect the stats and then compare
    db2exfmt -d <dbname> -u <userid> <pwd> -g -o <outfile> -w -1 -n % -s % -# 0

    As far this works fine and both DBs gives almost 95 - 98 % same results.

    Here are the Questions i have for you.

    1.
    How can i maintain the statistics time to time. I want to sync the DB every day manually the changes in statistics. How do we gonna do this

    2.
    After success of 1st question i want to implement a script to detect the stat changes when i run this script and then implement it as a Cron JOB in server.
    Plz advice me on this....

    I know this kinda lengthy post..
    But hope u guys interested in answering this..

    Any suggestions solutions are welcome...

    Thanx

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ai_zaviour View Post
    1.
    How can i maintain the statistics time to time. I want to sync the DB every day manually the changes in statistics. How do we gonna do this
    You gonna use db2look -m as before.

    Quote Originally Posted by ai_zaviour View Post
    2.
    After success of 1st question i want to implement a script to detect the stat changes when i run this script and then implement it as a Cron JOB in server.
    You prolly gonna check out STATS_TIME in SYSCAT.TABLES.

  3. #3
    Join Date
    Sep 2011
    Posts
    85
    thanx for the reply..

    If we do the db2look -m option every day will this can be possiable...
    Cuz the db2look script include all table structure as well..
    So when we run it on statistics DB will it run without errors..??
    Cuz tables are already there...

    Next one is
    STATS_TIME in SYSCAT.TABLES.
    Can you give a like to start/ more illustrations

    Thanx again

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    ibm created a nice tool - available for anybody - called infocenter
    SYSCAT.TABLES catalog view - IBM DB2 9.7 for Linux, UNIX, and Windows
    when running an sql script and you want to ignore errors - do not specify -s when invoking
    description also available in infocenter
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •