Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Update statistics for all tables in a schema

    We have a large time-stamped Datamart (UDB on Windows V9.5) and have the need to do a RUNSTATS on all tables in a particular schema at a certain point in our nightly batch cycle.

    I know I can use
    REORGCHK UPDATE STATISTICS ON SCHEMA myschema

    but this does reorg as well as runstats and takes too long to run.

    Today the way we do it is a script that has individual RUNSTATS commands for each table - but it is just another script to constantly maintain as we drop and add tables.

    Is there another way to do runstats on all tables in a schema?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Civilized DBA's have scripts that:
    1. Generate the runstats command for each table into a file (use -x to omit column headers)
    2. Execute the generated script
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Why do you have a need to run it on ALL tables? Are you rebuilding ALL tables daily? Are ALL of your tables change that much daily?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Dec 2007
    Posts
    288
    yes. every night, each table is loaded through an ETL process. after the tables are loaded, we do runstats and then do all of the daily reporting.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    EVERY SINGLE TABLE???? Are you adding just a small portion i.e. less then 5% of data, or are you doing a complete reload?

    And did you read what Marcus said? You can write a script that will take care of it all for you and never needs to be touched again.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Dec 2007
    Posts
    288
    every single table. it is a reporting datamart. Some are truncated and reloaded some are only updated with new data the % varies from day to day depending on the type of cycle.. Daily, payroll, weekend, Month End etc...

    I've scrubbed the internet for help with a script. I guess I'll keep looking.

    I'm a SQL Server DBA and in SQL Server I can use the msForEachdb System Stored Procedure and code it that way... I just don't know how to do it in UDB

    Thanks for your help.

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    hmm, I have been around reporting db's and never seen one that repopulated every single table on a daily basis. Oh well.

    Dude, it is not about UDB, db2 or SQL Server. It is done via unix script. a simple loop that will read catalog; generate runstats command; followed by running the file that has those commands.

    You can get funky in this script as much as your heart desires. Error checking, paging, ability to gracefully interrupt the script if need be.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Dec 2007
    Posts
    288
    I didn't write the ETL jobs.. I just need to do runstats on every table before we do reporting. The reporting queries seem to run so much faster on updated statistics : )

    anyway.. unix scriping on my windows box. maybe load cygwin? I'll have to buy a UNIX scripting book

    Thanks for your help and advice

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    if you are not on UNIX. you can use what ever scripting language you are using now to achieve the same.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Dec 2007
    Posts
    288
    Thanks. I just thought there was something like REORGCHK just for Runstats.. the answer is no. Individual runstats statements in a script file will have to suffice. thanks again

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by itsonlyme44 View Post
    I know I can use
    REORGCHK UPDATE STATISTICS ON SCHEMA myschema

    but this does reorg as well as runstats
    No it does not.

  12. #12
    Join Date
    Dec 2007
    Posts
    288
    I totally mis-spoke.. REORGCHK doesnt' do REORGs but is a utility to determine if a table needs to be REORG'ed, I think what it does is a RUNSTATS on each table before actually running the REORGCHK utility on it. When I put REORGCHK UPDATE STATISTICS ON SCHEMA myschema into our PRodcution Batch cycle, they made me yank it out because it ran so much longer than my individual runstats statements.. I think I'll just stick with the way I'm doing it now. I've beaten this dead horse enough for today.

Posting Permissions

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