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?

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

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?

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.

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.

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.

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.

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

if you are not on UNIX. you can use what ever scripting language you are using now to achieve the same.

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

No it does not.

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.