generate a nice table overview

dr_te_z · #1 (**permalink**) 08-19-09, 07:50

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.

DBFinder · #2 (**permalink**) 08-19-09, 11:18

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

dr_te_z · #3 (**permalink**) 08-20-09, 08:41

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

DBFinder · #4 (**permalink**) 08-20-09, 14:21

Thanks,
I will try this soon.

Internet Services

Web Development

Digital Marketing

Consumer Tech