generate runstats command

db2girl · #1 (**permalink**) 01-25-12, 14:55

I used the following to generate the runstats command for all tables:

db2 -x "select 'runstats on table '|| rtrim(tabschema) || '.' || rtrim(tabname)||' with distribution on key columns and detailed indexes all;' from syscat.tables where type = 'T'" > runstats.sql

But it doesn't work for tables such as CANDLE.KLZ_CPU_Averages_M (mixed case) or CANDLE.Application_Transaction/Program_Long-Term_History (special char).

The following generates the runstats command that works but would like to know if there is a better way to accomplish the same:

db2 -x "select 'runstats on table '|| rtrim(tabschema) || '.\' || '\"' || rtrim(tabname)|| '\' || '\"' ||' with distribution on key columns and detailed indexes all;' from syscat.tables where type = 'T'" > runstats.sql

This generates:

runstats on table CANDLE.\"KLZ_CPU_Averages_M\" with distribution on key columns and detailed indexes all;

Can you please suggest a better way to generate the runstats command?

db2girl · #2 (**permalink**) 01-25-12, 15:14

Actually, for it to work with db2 -tvf, I just need the double quotes:
runstats on table CANDLE."KLZ_CPU_Averages_M" with distribution on key columns and detailed indexes all;

But with ksh, I need:
db2 runstats on table CANDLE.\"KLZ_CPU_Averages_M\" with distribution on key columns and detailed indexes all

Please suggest how to improve the following:

db2 -x "select 'db2 runstats on table '|| rtrim(tabschema) || '.\' || '\"' || rtrim(tabname)|| '\' || '\"' ||' with distribution on key columns and
detailed indexes all' from syscat.tables where type = 'T'" > runstats.sql

Marcus_A · #3 (**permalink**) 01-25-12, 17:02

At first glance your generate runstats SQL looks fine.

I would find the person who created the case-sensitive table names and send them to Singapore for caning.
LiveLeak.com - Caning in Singapore

stolze · #4 (**permalink**) 01-25-12, 19:56

Or you get used to the fact that all database systems allow delimited identifiers these days and start to always use the double-quotes. ;-)

dr_te_z · #5 (**permalink**) 01-26-12, 02:58

Tut mir leid Knut, I vote for the Singapore option

db2girl · #6 (**permalink**) 01-26-12, 09:48

There is no better way to generate: \" and \" ?

These tables are part of Tivoli Data Warehouse product. Caning is way too severe and I read it can't be used for women and men over 50.

stolze · #7 (**permalink**) 01-26-12, 13:44

I would probably wrap the formatting into a simple SQL UDF, which does the concatenation and wrapping into ". Then you call this UDF:

Code:

SELECT 'db2 runstats on table '|| format_table_name(tabschema, tabname) || ' with distribution on key columns and detailed indexes all'
FROM syscat.tables where type = 'T'

db2girl · #8 (**permalink**) 01-26-12, 20:14

Quote:

Originally Posted by stolze

I would probably wrap the formatting into a simple SQL UDF, which does the concatenation and wrapping into ".

I've never written one before. Could you please show an example of format_table_name?

db2girl · #9 (**permalink**) 01-26-12, 20:29

This site has some bug. This is a new thread, but it's got so many Views...

stolze · #10 (**permalink**) 01-26-12, 21:50

I don't have a DB2 instance at hand right now. So here is an untested version:

Code:

CREATE FUNCTION format_table_name(schemaName VARCHAR(128), tableName VARCHAR(128))
   RETURNS VARCHAR(261)
   LANGUAGE SQL
   DETERMINISTIC
   CONTAINS SQL
   RETURN '"' || RTRIM(schemaName) || '"."' || RTRIM(tableName) || '"'

Marcus_A · #11 (**permalink**) 01-27-12, 07:05

I personally don't think it is a good idea for any DBA maint scripts to rely on custom functions, views, etc that are not part of the DB. This is especially true for databases created by vendor packages.

Internet Services

Web Development

Digital Marketing

Consumer Tech