Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: generate runstats command

    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?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or you get used to the fact that all database systems allow delimited identifiers these days and start to always use the double-quotes. ;-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Tut mir leid Knut, I vote for the Singapore option

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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'
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by stolze View Post
    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?

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    This site has some bug. This is a new thread, but it's got so many Views...

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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) || '"'
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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