Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: generate a nice table overview

    Sometimes you'll need info about a table:
    - in which tablespace does it reside?
    - are there any views/triggers/etc defined?
    - howmany rows?
    - are all indexes really required by DB2?

    So I created a "generate script SQL". You run this SQL, capture the output in a file and edit this file (prefix: db2 connect to .. & set current_schema/suffix: db2 connect reset) and save it as a script. When you execute this script (yeah, I know: long lines) the output will be a file for each table in your schema and that file will contain all kind of usefull (DBA)data about the table. The filename itself is also informative: the number of rows is part of the filename and if the file with the biggest size probably has the most DDL to carry around.
    Code:
    select varchar(    'db2look -d '
                    || current_server
                    || ' -t '
                    || rtrim(name)
                    || ' -e -p -o '
                    || rtrim(lcase(name))
                    || '.'
                    || rtrim(char(bigint(card))) concat '.out'
                    || ' && db2 -z '
                    ||  rtrim(lcase(name))
                    || '.'
                    || rtrim(char(bigint(card))) concat '.out'
                    || ' "describe table '
                    || rtrim(current_schema)
                    || '.'
                    || rtrim(name)
                    || ' "'
                    || ' && db2 -z '
                    || rtrim(lcase(name))
                    || '.'
                    || rtrim(char(bigint(card))) concat '.out'
                    || ' "describe indexes for table '
                    || rtrim(current_schema)
                    || '.'
                    || rtrim(name)
                    || ' show detail"'
                    ||  ' && db2pd -d '
                    || rtrim(current_server)
                    || ' -tcbstats all tbspaceid='
                    ||  char(tid)
                    ||  space(1)
                    || 'tableid='
                    ||  char(fid)
                    ||  space(1)
                    || 'file='
                    ||  rtrim(lcase(name))
                    || '.'
                    || rtrim(char(bigint(card))) concat '.out'
                    || ' && echo "Table '
                    || rtrim(current_schema)
                    || '.'
                    || rtrim(name)
                    || ', # of rows (according to latest RUNSTATS) :'
                    || rtrim(char(bigint(card)))
                    || '" >> ' 
                    || rtrim(lcase(name))
                    || '.'
                    || rtrim(char(bigint(card))) concat '.out' 
                 ,768) as line
      from sysibm.systables
      where type    = ucase('t')
        and creator = current_schema
      order by name
      ;
    This version should be portable for all V8 & V9 versions of DB2 and on all platforms.
    Please feel free to use it.

  2. #2
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Good job,

    On my ver -p switch is not supported (db2look).

    DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack

    Otherwise it is really useful.

    Thanks

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    So I noticed as welll on my V9.5.0 linux box. I've continued work on this query: as a programmer I did not like the "define the same code more than once" and I found a way to solve this in SQL. Here's the result
    Code:
    -------------------------------------------------
    ---- composedDSN values (single point of definition)
    -------------------------------------------------
    with var_dsn as
         ( select name
                , varchar(    'db2.'
                           || '$(hostname)'
                           || '.'
                           || rtrim(lcase(current_schema))
                           || '.' 
                           || rtrim(lcase(name))
                           || '.' 
                           || rtrim(char(bigint(card)))
                           || '.txt'
                          ,64)                                          as composedDSN
                , varchar(    rtrim(lcase(current_schema))
                           || '.' 
                           || rtrim(lcase(name))
                          ,64)                                          as qualName
             from sysibm.systables 
            where type    = ucase('t')
              and creator = current_schema
         )
    --------------------------
    ---- Commandline per table
    --------------------------
    select varchar(    'db2look -d '
                    || current_server
                    || ' -t '
                    || rtrim(v.name)
                    || ' -e -o '                      || rtrim(x.composedDSN)
                    || ' && db2 -z '                  || rtrim(x.composedDSN)
                    || ' "describe table '            || rtrim(x.qualName)
                    || ' "'
                    || ' && db2 -z '                  || rtrim(x.composedDSN)
                    || ' "describe indexes for table '
                    || rtrim(x.qualName)
                    || ' show detail"'
                    ||  ' && db2pd -d '
                    || rtrim(current_server)
                    || ' -tcbstats all tbspaceid='
                    ||  char(v.tid)
                    || ' tableid='
                    ||  char(v.fid)
                    || ' file='                       || rtrim(x.composedDSN)
                    || ' && echo "Table '             || rtrim(x.qualName)
                    || ', # of rows (according to latest RUNSTATS on '
                    || char(v.stats_time)
                    || ' ) :'
                    || rtrim(char(bigint(v.card)))
                    || '" >> '                        || rtrim(x.composedDSN)
                 ,768) as line
      from sysibm.systables  v 
      join var_dsn           x on v.name = x.name
      order                    by v.name

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks,
    I will try this soon.

Posting Permissions

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