Code Index:


ServDoc_0320udb - Documentation of your DB2- databases


$Id: ServDoc_0320udb,v 1.4 2003/09/04 16:00:49 uherbst Exp $


ServDoc_0320udb [-h|help] [-v|version] [--debug DB2,intensity]


ServDoc_0320udb describes your DB2 databases.

This will be documented for every database in every (running) instance:


This help.


--debug DB2,intensity
Turn on Debugging for that module.

The debug feature for that module is named ``DB2''.


Ulrich Herbst <>

#!/usr/bin/perl -w


# standard perl modules
use strict;                       # print error about unknown variables ...
use English;                      # long internal variable names;
use FindBin;                      # In which directory is ServDoc itself ?
                                  # There has to be the module and the lib dir!
use lib $FindBin::Bin. "/lib";    # Here are the ServDoc-perl-modules

# our own perl modules
use ServDoc;


my $options;
$options->{Version} = '$Id: ServDoc_0320udb,v 1.4 2003/09/04 16:00:49 uherbst Exp $';

# We need the cmdline to call the modules with the same debug options.
$options->{cmdline} = join " ", @ARGV;

sub debug { ServDoc_debug( "DB2", $options, shift, shift ); }

$options = &process_cmdline($options);

debug( 9, "ServDoc_0320udb is running" );


# Main

my $headings = "DB2 DB%+";

# Check, if we have a system with db2 running.
# Every DB2-Database has a db2dlock process running.
my $dummy=get_proc_info( "db2dlock" );

if (!defined($dummy)) { # No db2 running
  debug(9,"db2 doesn't run");
  exit 0;

my $pid;
my $dummyref;
my %instances;
###use Data::Dumper;
while (($pid,$dummyref) = each %$dummy) {
  $instances{scalar getpwuid($dummyref->{UID})}=1;

my $instance;

# loop over every instance
while (($instance,$dummy)= each %instances) {
  debug(9,"Instance: $instance\n");

  # We have to be the instance user or root (for su)

  if ($UID != 0 and getpwuid($UID) ne $instance) {
    debug(1,"$instance -- We aren't root and we aren't the instance user");
  my $inst_head = $headings . "Instance: $instance%+";

  # dbm cfg
  report_db2($instance,$inst_head."Instance Configuration",
	     "db2 get dbm cfg","",
	     'db2 get dbm cfg');

  # db2set
  report_db2($instance,$inst_head."Instance Configuration: db2set",

  # Node directory
  report_db2($instance,$inst_head."Node Directory",
	     "db2 list node directory","",
	     "db2 list node directory");

  # DB Directory
  my $dbdir=do_db2($instance,"db2 list db directory");
  report_string($inst_head."Database Directory",
		"db2 list db directory","",

  # process $dbdir to get a list of all databases;
  my @dbs;
  my $dbname;
  foreach (split /\n/,$dbdir) {
    if (/Database alias\s+= (.*)/) {$dbname=$1};
    if (/Directory entry type\s+= Indirect/) {
      push @dbs,$dbname;
  debug(9,"All DBs in $instance: @dbs\n");

  my $db;
  # Loop for all dbs
  foreach $db (@dbs) {
    my $db_head=$inst_head."DB: $db%+";

    # Is this database really existing ?
    # FIXME: This test maybe isn't fully correct
    my @dummy=ps_grep("$db");
    if ($#dummy<0) {
      debug(1,"Maybe db $db isn't really existing.");

	       "db2 connect to $db","",
	       "db2 connect to $db");

    # DB Configuration
    report_db2($instance,$db_head."DB Configuration",
	       "db2 get database configuration for $db","",
	       "db2 get database configuration for $db");

    # Tablespaces
    my $tablespaces=do_db2($instance,"db2 list tablespaces show detail");

		  "db2 list tablespaces show detail","",

    # get all Tablespace_IDs and Names
    my %tablespaces;
    my ($ts_id,$ts_name);
    foreach (split /\n/,$tablespaces) {
      if (/Tablespace ID\s+= (\d+)/) {$ts_id=$1};
      if (/Name\s+= (.*)/) {
	debug(9,"Tablespace: $1  - ID: $ts_id\n");

  # List containers for all tablespaces
    foreach $ts_name (sort keys %tablespaces) {
      $ts_id = $tablespaces{$ts_name};
      debug(2,"Tablespace: $ts_name; Tablespace #: $ts_name(ID: $ts_id)\n");
      report_db2($instance,$db_head."Tablespace: $ts_name%+Containers",
                 "db2 list tablespace containers for $ts_id show detail","",
                 "db2 list tablespace containers for $ts_id show detail");

    # Active Applications
    report_db2($instance,$db_head."Active Applications",
	       "db2 list applications","",
	       "db2 list applications");

    # Users with own tables
	       "db2 select tabschema from syscat.tables " .
	       "group by tabschema","",
	       "db2 select tabschema from syscat.tables " .
	       "group by tabschema");

    # and terminate connection
    do_db2 ($instance,"db2 terminate");
exit 0;

sub report_db2 {
  my $instance   = shift;
  my $title      = shift;
  my $short_desc = shift;
  my $long_desc  = shift;

  my $select     = shift;


sub do_db2 {
  my $instance   = shift;
  my $select     = shift;

  if ($UID == 0) {
    $select = "su - $instance -c \"$select\"";

  return do_cmd($select)