www.ruban.de – DB2 experts web site: Shell script xmpDB2All.sh to execute commands at all DB2 instances Do you need to execute one ore more commands at all of your DB2 instances? Then you should deploy xmpDB2all.sh! This script … locates all DB2 instances using profiles.reg (DB2 9.1) or global.reg (9.7 or later) executes commands passed as arguments xmpDB2All.sh – execute commands at all DB2 instances code for Linux/Unix/Windows » www.ruban.de

Category » code for Linux/Unix/Windows

Shell script xmpDB2All.sh to execute commands at all DB2 instances

Do you need to execute one ore more commands at all of your DB2 instances? Then you should deploy xmpDB2all.sh!

This script …

locates all DB2 instances using profiles.reg (DB2 9.1) or global.reg (9.7 or later)
executes commands passed as arguments

#!/bin/ksh
################################################################################
# xmpDB2All.sh - DB2 Command an allen DB2 Instanzen eines Systems ausführen
# ------------------------------------------------------------------------------
# Parameter: db2cmd         	- DB2 Command (je Instanz)
#            regfile         	- Pfad zu DB2 Instance Registry (optional)
#
# Calls ...: xmpLog.pl          - Logs events
#
# Comment  : Die Instanzen eines DB2 Systems sind unter Unix im Pfad /var/db2/vn
#            gespeichert. Mit dem Namen kann dann die /etc/passwd nach Instance
#            Home Directories durchsucht werden. Hier setzt man dann mit dem Cmd
#            db2profile die Umgebung und kann dann beliebige DB2 Commands 
#	         ausführen.  
#	         Dieses Script eignet sich besonders für das Stoppen und Starten
#	         aller Instanzen auf einem System, wobei die Instanzen nicht 
#            bekannt sein müssen.
#
# ------------------------------------------------------------------------------
# Autor      G.Ruban /18.09.00  - erste Version V1.1
#            G.Ruban /20.02.01  - V1.2 Protokollierung per xmpLog.pl Perl Script
#            G.Ruban /10.07.01  - V1.3 Perl Path (whence) in PATH setzen
#            G.Ruban /13.01.03  - V1.4 Output überarbeitet
#            G.Ruban /05.12.06  - V2.0: Some amendments necessary for DB2 V9.1
#            G.Ruban /29.07.11  - V2.1: Some amendments necessary for DB2 V9.7,
#                                       profiles.reg not longer supported,
#                                       function library xmpFunktion.sh removed
################################################################################

#-------------------------------------------------------------------------------
# Init 
#-------------------------------------------------------------------------------
  PROG=`basename $0`
  VER="2.1"

#-------------------------------------------------------------------------------
# Pfad der DBA's Administrationsfunktion, -steuerdaten festlegen 
#-------------------------------------------------------------------------------
# der Defaultwert ist : 
  xmpDbaDir="/opt/xmpdba"
  export xmpDbaDir
  xmpDbaDir="$xmpDbaDir"

#-------------------------------------------------------------------------------
# Perl Library in der Umgebung gefunden? Wenn nicht, setzen!
#-------------------------------------------------------------------------------
  if [ ! "$PERL5LIB" ]; then       	
     PERL5LIB=":/usr/local/bin:$xmpDbaDir/bin"
     export PERL5LIB
  else
     if [ -z `echo $PERL5LIB | grep "[.]*$xmpDbaDir/bin[.]*"` ]; then
        PERL5LIB="$PERL5LIB:/usr/local/bin:$xmpDbaDir/bin"
        export PERL5LIB
     fi    
  fi

#-------------------------------------------------------------------------------
# Determine db2ls command to inquire installed software
#-------------------------------------------------------------------------------
  db2lspath=`which db2ls`
  rc=$?
  if [ $rc -ne 0 ] ; then
     echo ">>> $PROG: DB2 Utility db2ls not found in current path."
	 db2lspath="/usr/local/bin/db2ls"
	 echo ">>> Execution path set to $db2lspath."
  fi

#-------------------------------------------------------------------------------
# Inquire latest installation path of DB2 (provides 1 item)
#-------------------------------------------------------------------------------  
  db2sw=`$db2lspath | grep db2 | awk '{print $1}' | tail -1`
  rc=$?
  if [ $rc -ne 0 ] ; then
     echo "Neither DB2 V9.7 nor V9.1 found, continue searching older versions ..."
	 echo "abends with RC=16"
 	 exit 16	
  fi 
  if [ ! "$db2sw" ]; then
	echo "$0: DB2 command db2ls found but no installed DB2 products found!"
	echo "abends with RC=16"
 	exit 16	
  fi   

#-------------------------------------------------------------------------------
# For archive reasons only: Determination of profiles.reg and global.reg
# < V9.1: /tmp/profiles.reg
# = V9.1: /opt/IBM/db2/V9.1/profiles.reg
# = V9.7: /var/[opt/]db2/global.reg
# regfile=`find /var/db2/ -name 'global.reg' -type f -print;find /var/opt/db2/ -name 'global.reg' -type f -print;find /var/db2/ -name 'profiles.reg' -type f -print;find $db2ip -name 'profiles.reg' -type f -print`
#-------------------------------------------------------------------------------  

#-------------------------------------------------------------------------------
# Inquire all instances from DB2 registry, list instance name and path only
#-------------------------------------------------------------------------------  
  db2greg=$db2sw"/bin/db2greg -dump"
  echo ">>> $db2greg"
  ilist=`$db2greg | awk -F, '/I,DB2/ {print $4":"$5}'`
  echo ">>> $ilist"

#-------------------------------------------------------------------------------
# Info Block ausgeben
#-------------------------------------------------------------------------------
  echo $PROG " - V$VER"
  echo "======================================================="
  echo "Current User ................: "`id`
  echo "Current Date/Time ...........: "`date +%Y-%m-%d-%H.%M.%S`
  echo "System ......................: "`uname -n`
  echo "DB2 Command .................: $1"

#-------------------------------------------------------------------------------
# Argumente Prüfen
#-------------------------------------------------------------------------------
  if [ $# -lt 1 or $1 = ? ] ; then
	echo "Syntax für $0 db2cmd regfile"
	echo "... where db2cmd  = DB2 Command, e.g. db2stop"
	echo "Abend mit RC=4"
 	exit 4
  fi

#-------------------------------------------------------------------------------
# Perl Bin Path in PATH setzen
#-------------------------------------------------------------------------------
  perlpath=`whence perl`
  if [ $? -ne 0 ] ; then
     if [ -x "/usr/local/bin/perl" ]; then
	PATH="$PATH:/usr/local/bin"
     	export PATH
     else
        if [ -x "/usr/bin/perl" ]; then
	   PATH="$PATH:/usr/bin"
     	   export PATH
        else
           echo "$0: Perl Binary not found, possibly cannot execute Perl."
        fi
     fi
  else
     perlpath=`dirname $perlpath`
     PATH="$PATH:$perlpath"
     export PATH
  fi
# echo "PATH=$PATH"

#-------------------------------------------------------------------------------
# Liste der Instanzen aus profiles.reg abarbeiten
#-------------------------------------------------------------------------------
  maxrc=0
  for i in $ilist
  do
	#---------------------------------------------------------------------------
	# split instance name and instance path
	#---------------------------------------------------------------------------
	instname=${i%:*}
	instpath=${i#*:}
	#echo ">>>instname=$instname  instpath=$instpath"

	#---------------------------------------------------------------------------
	# Prüfen, ob db2profile Script vorhanden ist
	#---------------------------------------------------------------------------
	echo "--------------------------------------------------------------------------------"
	if [ -x "$instpath/db2profile" ]; then
		echo "- Instance ..................: $instname"	
		echo "  - Home Directory ..........: $instpath"	
		out=`ksh -c ". $instpath/db2profile; $1; exit"`
		rc=$?
		echo "  - Requested Command - RC ..: $rc"	
		echo "  - Command Output ..........: $out"	
		if [ $rc -gt maxrc ]; then
			maxrc=$rc
		fi
		#---------------------------------------
		# Protokollierung
		#---------------------------------------
		perl ${xmpDbaDir}/bin/xmpLog.pl -a "xmpDB2All.sh" -s "I" -t "RC=$rc CMD=$1"
	else
		echo "- Instance ..................: $instname"	
		echo "  - Home Directory ..........: $instpath"	
		echo "    Warning! IBM-supplied script 'db2profile' not found in DB2 path."
        rc=4
		if [ $rc -gt maxrc ]; then
			maxrc=$rc
		fi
	fi
 done

#-------------------------------------------------------------------------------
# *** ENDE ***
#-------------------------------------------------------------------------------
  if [ $maxrc -ne 0 ]; then
	WarningMsg "$0 finished. (RC=$maxrc)"
  else
	echo "$0 finished. (RC=$maxrc)"
  fi
  perl ${xmpDbaDir}/bin/xmpLog.pl -a "xmpDB2All.sh" -s "I" -t "MAXRC=$maxrc" 

exit $maxrc

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

#!/bin/ksh

################################################################################

# xmpDB2All.sh - DB2 Command an allen DB2 Instanzen eines Systems ausführen

# ------------------------------------------------------------------------------

# Parameter: db2cmd - DB2 Command (je Instanz)

# regfile - Pfad zu DB2 Instance Registry (optional)

# Calls ...: xmpLog.pl - Logs events

# Comment : Die Instanzen eines DB2 Systems sind unter Unix im Pfad /var/db2/vn

# gespeichert. Mit dem Namen kann dann die /etc/passwd nach Instance

# Home Directories durchsucht werden. Hier setzt man dann mit dem Cmd

# db2profile die Umgebung und kann dann beliebige DB2 Commands

# ausführen.

# Dieses Script eignet sich besonders für das Stoppen und Starten

# aller Instanzen auf einem System, wobei die Instanzen nicht

# bekannt sein müssen.

# ------------------------------------------------------------------------------

# Autor G.Ruban /18.09.00 - erste Version V1.1

# G.Ruban /20.02.01 - V1.2 Protokollierung per xmpLog.pl Perl Script

# G.Ruban /10.07.01 - V1.3 Perl Path (whence) in PATH setzen

# G.Ruban /13.01.03 - V1.4 Output überarbeitet

# G.Ruban /05.12.06 - V2.0: Some amendments necessary for DB2 V9.1

# G.Ruban /29.07.11 - V2.1: Some amendments necessary for DB2 V9.7,

# profiles.reg not longer supported,

# function library xmpFunktion.sh removed

################################################################################

#-------------------------------------------------------------------------------

# Init

#-------------------------------------------------------------------------------

PROG=`basename $0`

VER="2.1"

#-------------------------------------------------------------------------------

# Pfad der DBA's Administrationsfunktion, -steuerdaten festlegen

#-------------------------------------------------------------------------------

# der Defaultwert ist :

xmpDbaDir="/opt/xmpdba"

export xmpDbaDir

xmpDbaDir="$xmpDbaDir"

#-------------------------------------------------------------------------------

# Perl Library in der Umgebung gefunden? Wenn nicht, setzen!

#-------------------------------------------------------------------------------

if [ ! "$PERL5LIB" ]; then

PERL5LIB=":/usr/local/bin:$xmpDbaDir/bin"

export PERL5LIB

else

if [ -z `echo $PERL5LIB | grep "[.]*$xmpDbaDir/bin[.]*"` ]; then

PERL5LIB="$PERL5LIB:/usr/local/bin:$xmpDbaDir/bin"

export PERL5LIB

#-------------------------------------------------------------------------------

# Determine db2ls command to inquire installed software

#-------------------------------------------------------------------------------

db2lspath=`which db2ls`

rc=$?

if [ $rc -ne 0 ] ; then

echo ">>> $PROG: DB2 Utility db2ls not found in current path."

db2lspath="/usr/local/bin/db2ls"

echo ">>> Execution path set to $db2lspath."

#-------------------------------------------------------------------------------

# Inquire latest installation path of DB2 (provides 1 item)

#-------------------------------------------------------------------------------

db2sw=`$db2lspath | grep db2 | awk '{print $1}' | tail -1`

rc=$?

if [ $rc -ne 0 ] ; then

echo "Neither DB2 V9.7 nor V9.1 found, continue searching older versions ..."

echo "abends with RC=16"

exit 16

if [ ! "$db2sw" ]; then

echo "$0: DB2 command db2ls found but no installed DB2 products found!"

echo "abends with RC=16"

exit 16

#-------------------------------------------------------------------------------

# For archive reasons only: Determination of profiles.reg and global.reg

# < V9.1: /tmp/profiles.reg

# = V9.1: /opt/IBM/db2/V9.1/profiles.reg

# = V9.7: /var/[opt/]db2/global.reg

# regfile=`find /var/db2/ -name 'global.reg' -type f -print;find /var/opt/db2/ -name 'global.reg' -type f -print;find /var/db2/ -name 'profiles.reg' -type f -print;find $db2ip -name 'profiles.reg' -type f -print`

#-------------------------------------------------------------------------------

# Inquire all instances from DB2 registry, list instance name and path only

#-------------------------------------------------------------------------------

db2greg=$db2sw"/bin/db2greg -dump"

echo ">>> $db2greg"

ilist=`$db2greg | awk -F, '/I,DB2/ {print $4":"$5}'`

echo ">>> $ilist"

#-------------------------------------------------------------------------------

# Info Block ausgeben

#-------------------------------------------------------------------------------

echo $PROG " - V$VER"

echo "======================================================="

echo "Current User ................: "`id`

echo "Current Date/Time ...........: "`date +%Y-%m-%d-%H.%M.%S`

echo "System ......................: "`uname -n`

echo "DB2 Command .................: $1"

#-------------------------------------------------------------------------------

# Argumente Prüfen

#-------------------------------------------------------------------------------

if [ $# -lt 1 or $1 = ? ] ; then

echo "Syntax für $0 db2cmd regfile"

echo "... where db2cmd = DB2 Command, e.g. db2stop"

echo "Abend mit RC=4"

exit 4

#-------------------------------------------------------------------------------

# Perl Bin Path in PATH setzen

#-------------------------------------------------------------------------------

perlpath=`whence perl`

if [ $? -ne 0 ] ; then

if [ -x "/usr/local/bin/perl" ]; then

PATH="$PATH:/usr/local/bin"

export PATH

else

if [ -x "/usr/bin/perl" ]; then

PATH="$PATH:/usr/bin"

export PATH

else

echo "$0: Perl Binary not found, possibly cannot execute Perl."

else

perlpath=`dirname $perlpath`

PATH="$PATH:$perlpath"

export PATH

# echo "PATH=$PATH"

#-------------------------------------------------------------------------------

# Liste der Instanzen aus profiles.reg abarbeiten

#-------------------------------------------------------------------------------

maxrc=0

for i in $ilist

#---------------------------------------------------------------------------

# split instance name and instance path

#---------------------------------------------------------------------------

instname=${i%:*}

instpath=${i#*:}

#echo ">>>instname=$instname instpath=$instpath"

#---------------------------------------------------------------------------

# Prüfen, ob db2profile Script vorhanden ist

#---------------------------------------------------------------------------

echo "--------------------------------------------------------------------------------"

if [ -x "$instpath/db2profile" ]; then

echo "- Instance ..................: $instname"

echo " - Home Directory ..........: $instpath"

out=`ksh -c ". $instpath/db2profile; $1; exit"`

rc=$?

echo " - Requested Command - RC ..: $rc"

echo " - Command Output ..........: $out"

if [ $rc -gt maxrc ]; then

maxrc=$rc

#---------------------------------------

# Protokollierung

#---------------------------------------

perl ${xmpDbaDir}/bin/xmpLog.pl -a "xmpDB2All.sh" -s "I" -t "RC=$rc CMD=$1"

else

echo "- Instance ..................: $instname"

echo " - Home Directory ..........: $instpath"

echo " Warning! IBM-supplied script 'db2profile' not found in DB2 path."

rc=4

if [ $rc -gt maxrc ]; then

maxrc=$rc

done

#-------------------------------------------------------------------------------

# *** ENDE ***

#-------------------------------------------------------------------------------

if [ $maxrc -ne 0 ]; then

WarningMsg "$0 finished. (RC=$maxrc)"

else

echo "$0 finished. (RC=$maxrc)"

perl ${xmpDbaDir}/bin/xmpLog.pl -a "xmpDB2All.sh" -s "I" -t "MAXRC=$maxrc"

exit $maxrc

(Some comments are in german language. If you need assistence, please contact!)

This script may helps administrators to …

first stop all application services
then stop all DB2 instances
to do their maintenance, e.g. full system backups, hardware or software maintenance
use it in rc2.d to start/stop DB2, so help starting/stopping a server

Starting and stopping an CLI and JDBC trace in a shell environment

######################################################################
# DB2CJTrace-Start.sh
######################################################################
clear
echo "Start DB2 CLI+JDBC Trace ..........."
echo "- enter path to trace file: (e.g. /db2inst1/db2/db2diag/$DB2INSTANCE)"
read tfp
datestr=`date +%Y-%m-%d-%H.%M.%S`
CLIFile=$tfp/CLI-Trace-$datestr.txt
JDBCFile=$tfp/JDBC-Trace-$datestr.txt
echo "* CLI Trace File : $CLIFile"
echo "* JDBC Trace File: $JDBCFile"
#
db2 UPDATE CLI CFG FOR SECTION COMMON USING TracePathName NULL
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFileName $CLIFile
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceComm 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlush 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceTimeStamp 3
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlushOnError 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 1
#
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTracePathName NULL
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTraceFileName $JDBCFile
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTraceFlush 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTrace 1
echo "DB2 CLI+JDBC Trace activated:"
db2 get cli cfg

######################################################################

# DB2CJTrace-Start.sh

######################################################################

clear

echo "Start DB2 CLI+JDBC Trace ..........."

echo "- enter path to trace file: (e.g. /db2inst1/db2/db2diag/$DB2INSTANCE)"

read tfp

datestr=`date +%Y-%m-%d-%H.%M.%S`

CLIFile=$tfp/CLI-Trace-$datestr.txt

JDBCFile=$tfp/JDBC-Trace-$datestr.txt

echo "* CLI Trace File : $CLIFile"

echo "* JDBC Trace File: $JDBCFile"

db2 UPDATE CLI CFG FOR SECTION COMMON USING TracePathName NULL

db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFileName $CLIFile

db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceComm 1

db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlush 1

db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceTimeStamp 3

db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlushOnError 1

db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 1

db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTracePathName NULL

db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTraceFileName $JDBCFile

db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTraceFlush 1

db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTrace 1

echo "DB2 CLI+JDBC Trace activated:"

db2 get cli cfg

######################################################################
# DB2CJTrace-Stop.sh
######################################################################
clear
echo "Stop DB2 CLI+JDBC Trace ..........."
db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 0
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTrace 0
echo "DB2 CLI+JDBC Trace stopped:"
db2 get cli cfg

######################################################################

# DB2CJTrace-Stop.sh

######################################################################

clear

echo "Stop DB2 CLI+JDBC Trace ..........."

db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 0

db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTrace 0

echo "DB2 CLI+JDBC Trace stopped:"

db2 get cli cfg

Perl Script xmpTBSCStmt.pl to generate useful tablespace statements

xmpTBSCStmt.pl provides following functions:

creates CREATE TABLESPACE statements for existing database
creates REDIRECTED RESTORE statements for a database you possibly want to or need to recover at a remote site
creates ALTER TABLESPACE statements to reduce tablespace size and/or to lower the high water mark. Action depends on type of tablespace: Automatic storage, SMS and DMS tablespaces are supported.

Options supported: (Invoke script w/o parms will show all possible parameters)

[perl] xmpTBSCStmt.pl -d db [-u parm -p parm] -f file [-t {RESTORE|CREATE|AUTOSIZE} -o {CURRENT|HWM}] [-g nn] [-s pattern] [-ns pattern]

-d database
-u User (opt. if exists in environment ¹)
-p Password (opt. if exists in environment ¹)
-f output file receiving all the generated statements
-t type of statement to generate (opt)
-o current allocation or HWM (High Water Mark) (opt.)
-g fill-meter (AUTOSIZE target usage %) or growth (additional uplift % on HWM) (opt.)
-s tablespace name pattern (opt.)
-ns single tablespace name exclude pattern (opt.),

Using patterns, examples and usage comments:

-ns patt Do not use replace chars like ‘*’ or ‘%’\n”.
-s ‘patt1|patt2′ Imbed multiple patterns in (double) quotes
-ns patt1 -s patt2 Paired patterns work like OR condition

¹ See xmpPerl.pm library for details on how to optionally deposit user and password, used in perl scripts, in user’s profile.

#!/usr/local/bin/perl -w
################################################################################
# xmpTBSCStmt.pl - Generate Tablespace/Container SQL Statements
# Called by - Command Level oder DBA Scripts
# Calls     - xmpPerl.pm   - xmp DBA Perl Package
#             Getopt       - Argument Verwaltung
#             Env          - Zugriff auf Umgebungsvariablen
#             English      - Aliase für Perl Variablen
# Optionen  - xmpTBSCStmt.pl -d database	Datenbank-Name
#                            -u user -p psw   User und Password          
#                            -f file          File für Statement Generierung
#			       			 -t CREATE|RESTORE|AUTOSIZE
#                            -o CURRENT|HWM   Size: Current Alloc or High Water Mark
#                            -g nn            Plus nn % growth or Fill-Meter
#                            -s tablespace    Name Pattern
#                            -ns tablespace   Name Pattern to exclude
#-------------------------------------------------------------------------------
# Autor     - GR /28.03.01 - V1.0 first version, copied from xmpTBSCList.pl
# Changes   - GR /16.05.01 - V1.1 Access to DB2 Catalog for BPool and changes,
#                                 e.g. for calculation of HWM page sizes
#             GR /16.05.01 - V1.2 Minimum Requirements added to HWM page size
#             GR /06.07.01 - V1.3 new parm -g nn for 'growth'
#             GR /02.11.01 - V1.4 writes more comments to generated script 
#             GR /22.07.03 - V2.0 generates ALTER TABLESPACE RESIZE statements
#             GR /22.07.03 - V2.0.1 Fixed problem new target percentage
#             GR /14.04.04 - V2.1 Parm opt_s became opt_o, new parm opt_s for 
#                                 specific tablespaces, resizing adjusted to next integer
#             GR /03.05.04 - V2.1.1 Problem with opt_s fixed
#             GR /07.06.04 - V2.2 New parameter DOWNSIZE for V8.x objects
#             GR /08.03.05 - V2.3 New space must at least cover high water mark 
#                                 while downsizing space
#             GR /06.04.05 - V2.4 New 'exclude tablespace' parameter -ns
#             GR /20.04.05 - V3.0 Code restructured, several amendments
#             GR /02.05.05 - V3.0.1 Small correction concerning RESTORE/CREATE,
#             GR /06.05.05 - V3.1 Correction in DOWNSIZE formulas, CREATE/RESTORE section
#             GR /06.06.05 - V3.2 Add 1 Extent for min. page requested for new V8.2 DB's
#                                 (see PMR 8144), reactivate -g for option "HWM"
#             GR /05.07.05 - V4.0 New V8.2 technique to access data via SQL,
#                                 RESIZE/DOWNSIZE merged to AUTOSIZE
#             GR /07.11.05 - V4.1 Determine AUTORESIZE options for CREATE statement
#                                 using SNAP_GET_TBSP_PART (Table Function)         
#             GR /25.04.06 - V4.2 Provide PREFETCHSIZE information for CREATE's
#             GR /18.08.06 - V4.3 Allow multiple -s/-ns patterns
#             GR /12.02.07 - V4.4 New Default is LARGE TABLESPACE (Type 1) und
#                                 USER TEMPORARY (3)
#             GR /26.02.07 - V4.4.1 Handle ts.total_pages=0 in SELECT, min=1
#             GR /19.03.07 - V4.4.2 consider FILE SYSTEM CACHING 
#             GR /17.08.10 - V5.0 Consider AUTOMATIC STORAGE, deprecated SQL 
#                                 administrative routines SNAPSHOT_TBS_CFG and
#                                 SNAPSHOT_CONTAINER replaced by views
#             GR /01.08.11 - V5.1 Amendments for DB2 V9.7
#             GR /10.10.11 - V5.1.1 Wait a while between ALTER HIGH WATER MARK and REDUCE
#                                   to avoid SQL0290N
#-------------------------------------------------------------------------------
# Comments  - PERL5LIB environment variable must point to Perl module path!
################################################################################

################################################################################
# Modules 
################################################################################
use strict;
use Getopt::Long;													# V2.4
use vars qw($opt_d $opt_u $opt_p $opt_f $opt_t $opt_o $opt_g);		# V2.4
use vars qw($opt_s $opt_ns);										# V2.4
use xmpPerl;
use Env;
use English;

use DBI;
use DBD::DB2::Constants;
use DBD::DB2;

################################################################################
# Declare/Init 
################################################################################
my $VER     	= "5.1.1";

my $work    	= "";					# for TS List
my @work;								# for TS List

my ($dbh, $sth);						# DB2 Access Handles
my $stmt	= "";

my $f_select;							# true/false
my $f_changed;							# true/false

my $ts		= 0;  my $f0;				# no. of Tablespaces
my %ts_id	= (); my $f1;				# Tablespace Structure
my %ts_name	= (); my $f2;
my %ts_type     = (); my $f3;			# 0=SMS 1=DMS
my %ts_content  = (); my $f4;			# 0=ANY 1=LARGE 2=SYSTEMP 3=USRTEMP
my %ts_state    = (); my $f5;
my %ts_pgtotal  = (); my $f6;
my %ts_pguseable= (); my $f7;
my %ts_pgused   = (); my $f8;
my %ts_pgfree   = (); my $f9;
my %ts_pghwm    = (); my $f10;			# High Water Mark
my %ts_pgsize   = (); my $f11;			# Page Size
my %ts_extsize  = (); my $f12;			# Extent Size
my %ts_pfsize   = (); my $f13;			# Prefetch Size
my %ts_fscache  = (); my $f25;			# File System Caching		V4.4.2
my %ts_autostg  = (); my $f26;          # Automatic Storage         V4.5
my %ts_container= (); my $f14;			# no. of containers
my %ts_tsc_id	= (); my $f15;			# Container ID
my %ts_tsc_name	= (); my $f16;			# Container Name
my %ts_tsc_type = (); my $f17;			# Container Type
my %ts_tsc_pgtotal = (); my $f18;
my %ts_tsc_pguseable=(); my $f19;
my %ts_tsc_pgaccess=();  my $f20;
my %ts_pgusage	= ();	 my $f21;		# Usage in Percent
my %ts_tsc_ext_total= (); my $f22;		# number of total container extents
my %ts_tsc_ext_used= (); my $f23;		# number of container extents used
my %ts_tsc_ext_hwm= ();	 my $f24;		# number of HWM container extents

my $ts_total_prev 	= 0;				# Current Total Allocation
my $ts_total_used 	= 0;				# Current Allocation Used
my $ts_total_hwm   	= 0;				# HWM Allocation
my $ts_total_new	= 0;				# new to allocate

my $hFile;								# File Handle
my $new_target		= 0;				# new number pages calculated
my $bpool 			= "";				# Bufferpool Name

my $dbcfg_logpath	= "";				# DB Log Path
my $dbcfg_dftdbpath	= "";				# Default DB Path

my $autoresize		= "";				# AUTORESIZE	V4.1

################################################################################
# Argumente/Options prüfen 
################################################################################
GetOptions("d=s","u:s","p:s","f:s","t:s","o:s",		# V2.4
           "g:i","s:s","ns:s");						# V2.4

if (! defined $opt_d )								# database check 
	{print "$0 - database not specified\n";
         SaySyntax();
	}

if (! defined $opt_u )								# user check
	{$opt_u = xmpPerl::getDBAUser();
       	if ($opt_u eq "")
		{print "$0 - user not specified and lP_LUS env variable not defined\n";
        	 SaySyntax();  }
	}

if (! defined $opt_p )								# User Password Check
	{$opt_p = xmpPerl::getDBAPsw();
	if ($opt_p eq "")
		{print "$0 - missing user's password fehlt and lP_LPW env variable not defined\n";
        	 SaySyntax();  }
	}

if (! defined $opt_f )								# File für Statement Generierung
	{print "$0 - missing name of file to generate\n";
       	 SaySyntax();
	}

if (! defined $opt_t )								# Type
	{$opt_t = "RESTORE";
	}

if (! defined $opt_o )								# Option = Current Allocation Size
	{$opt_o = "CURRENT";
	}

if (defined $opt_s)	{								# V2.1: Tablespace
	$opt_s = uc $opt_s;								# V2.1: uppercase
}

if (defined $opt_ns)	{							# V2.4: Exclude Tablespace
	$opt_ns = uc $opt_ns;							# V2.4: uppercase
}

################################################################################
$opt_d = uc $opt_d;
$opt_o = uc $opt_o;
$opt_t = uc $opt_t;

################################################################################
# Support downwards compatiblity to V3.1
################################################################################
if ($opt_t eq "DOWNSIZE")	{$opt_t = "AUTOSIZE";	}
if ($opt_t eq "RESIZE")		{$opt_t = "AUTOSIZE";	}

################################################################################
# Check arrugments passed
################################################################################
if (  ($opt_t ne "RESTORE") 			# option check
    && ($opt_t ne "CREATE") 
    && ($opt_t ne "AUTOSIZE")	) {
	print "$0 - Type invalid, please choose either CREATE, RESTORE or AUTOSIZE!\n";
	SaySyntax(); 
}

if (($opt_o ne "CURRENT") && ($opt_o ne "HWM") ) {
	print "$0 - Options -o invalid value, use CURRENT or HWM only!\n";
	SaySyntax();
}

if (! defined $opt_g )								# Growth/Fill-Meter Percentage
	{$opt_g = 0;
}
if (($opt_g < 0) | ($opt_g > 1000) ) {
	print "$0 - Option -g invalid value, use 0 - 1000 only!\n";
	SaySyntax();
}

################################################################################
# Check DB2 Existenz und Informationsblock ausgeben
################################################################################
xmpPerl::chkDB2();
xmpPerl::prtHeaderDB2("$0 - Generating Tablespace Statements - V$VER");

print "Database / DB2 User ID ......: $opt_d / $opt_u\n";
print "Name of file to generate ....: $opt_f\n";
print "Type of generation ..........: $opt_t\n";
print "Object Size to consider .....: $opt_o\n";
if ($opt_o eq "CURRENT") 	{
	print "Fill-meter in percent .......: $opt_g % (target usage in percent)\n";}
else {	print "Object Growth ...............: $opt_g % (increase in percent)\n";}
if (defined $opt_s) {
	print "Tablespace name pattern .....: $opt_s\n";
}
if (defined $opt_ns) {
	print "Tablespace exclude pattern ..: $opt_ns\n";
}

################################################################################
# DBI Switches 
################################################################################
my $switch = DBI->internal;
$switch->debug(0);                     	# 1=normal debug, 2=detailed handle trace
#print "DBI Version and Attributes ..: $switch->{'Attribution'}, $switch->{'Version'}\n";
#print "DBI Drivers .................: ",join(", ",DBI->available_drivers()),"\n";

################################################################################
# Connect to DWH Source Database holding Delta Processing Control Table
################################################################################
$dbh = DBI->connect("dbi:DB2:$opt_d", 
                    $opt_u, 
                    $opt_p,
                    { PrintError => 0, # don't print error by default
                      RaiseError => 0, # don't print error and die by default
                      AutoCommit => 0, # don't commit after each statement
                      ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types
                    } )
        || die ">>>$0 Problem mit Connect to $opt_d\n>>>".$DBI::errstr;

################################################################################
# Access physical Tablespace and Tablespace Container attributes and information
################################################################################
$stmt = "WITH TBSC (SNAPSHOT_TIMESTAMP, TBSP_NAME, TBSP_ID, TBSP_TYPE, TBSP_CONTENT_TYPE,
           TBSP_PAGE_SIZE, TBSP_EXTENT_SIZE, TBSP_PREFETCH_SIZE, FS_CACHING,
           TBSP_USING_AUTO_STORAGE, TBSP_AUTO_RESIZE_ENABLED,
           CONTAINER_NAME, CONTAINER_ID, CONTAINER_TYPE, TOTAL_PAGES,
           USABLE_PAGES, ACCESSIBLE, STRIPE_SET,
           TBSP_STATE, TBSP_TOTAL_PAGES, TBSP_USABLE_PAGES, TBSP_USED_PAGES,
           TBSP_FREE_PAGES, TBSP_PENDING_FREE_PAGES, TBSP_PAGE_TOP,
           TBSP_NUM_CONTAINERS, TBSP_INITIAL_SIZE, TBSP_CURRENT_SIZE,
           TBSP_MAX_SIZE, TBSP_INCREASE_SIZE, TBSP_INCREASE_SIZE_PERCENT,
           TBSP_LAST_RESIZE_TIME, TBSP_LAST_RESIZE_FAILED, TABLESPACE_PGUSAGE)
		AS (SELECT TS.SNAPSHOT_TIMESTAMP, TS.TBSP_NAME, TS.TBSP_ID, TS.TBSP_TYPE, 
           TS.TBSP_CONTENT_TYPE, TS.TBSP_PAGE_SIZE, TS.TBSP_EXTENT_SIZE,
           TS.TBSP_PREFETCH_SIZE, TS.FS_CACHING, TS.TBSP_USING_AUTO_STORAGE,
           TS.TBSP_AUTO_RESIZE_ENABLED, TSC.CONTAINER_NAME, TSC.CONTAINER_ID,
           TSC.CONTAINER_TYPE, TSC.TOTAL_PAGES, TSC.USABLE_PAGES, TSC.ACCESSIBLE,
           TSC.STRIPE_SET,
           TBSP.TBSP_STATE, TBSP.TBSP_TOTAL_PAGES, TBSP.TBSP_USABLE_PAGES,
           TBSP.TBSP_USED_PAGES, TBSP.TBSP_FREE_PAGES, TBSP.TBSP_PENDING_FREE_PAGES,
           TBSP.TBSP_PAGE_TOP, TBSP.TBSP_NUM_CONTAINERS, TBSP.TBSP_INITIAL_SIZE,
           TBSP.TBSP_CURRENT_SIZE, TBSP.TBSP_MAX_SIZE, TBSP.TBSP_INCREASE_SIZE,
           TBSP.TBSP_INCREASE_SIZE_PERCENT, TBSP.TBSP_LAST_RESIZE_TIME,
           TBSP.TBSP_LAST_RESIZE_FAILED,
	   DECIMAL ((TBSP.TBSP_USED_PAGES * 100.00) / (CASE TBSP.TBSP_TOTAL_PAGES 
                                                           WHEN 0 THEN 1 
                                                           ELSE  TBSP.TBSP_TOTAL_PAGES END ), 5, 2) 
                    AS USAGE
	FROM   SYSIBMADM.SNAPTBSP AS TS, 
	       SYSIBMADM.SNAPCONTAINER AS TSC, 
	       SYSIBMADM.SNAPTBSP_PART AS TBSP
	WHERE TS.TBSP_ID   = TSC.TBSP_ID 
	AND   TS.TBSP_NAME = TSC.TBSP_NAME
        AND   TS.TBSP_ID   = TBSP.TBSP_ID
	AND   TS.TBSP_NAME = TBSP.TBSP_NAME
	) 
SELECT 	TBSP_ID, TBSP_NAME, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_STATE, 
	TBSP_TOTAL_PAGES, TBSP_USABLE_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES,
	TBSP_PAGE_TOP,
	TBSP_PAGE_SIZE, TBSP_EXTENT_SIZE, TBSP_PREFETCH_SIZE, FS_CACHING, 
	TBSP_USING_AUTO_STORAGE, TBSP_NUM_CONTAINERS,
	CONTAINER_ID, CONTAINER_NAME, CONTAINER_TYPE,	
	TOTAL_PAGES, USABLE_PAGES, ACCESSIBLE,
 	TABLESPACE_PGUSAGE,
        (CEIL(1.00 *      TOTAL_PAGES / TBSP_EXTENT_SIZE)) AS CONTAINER_EXTENTS_TOTAL,
        (CEIL(1.00 * TBSP_USED_PAGES / TBSP_NUM_CONTAINERS / TBSP_EXTENT_SIZE) +1 ) AS CONTAINER_EXTENTS_USED,
        (CEIL(1.00 * TBSP_PAGE_TOP / TBSP_NUM_CONTAINERS / TBSP_EXTENT_SIZE) +1 ) AS CONTAINER_EXTENTS_HWM
FROM TBSC 
ORDER BY TbSC.TBSP_ID, TBSC.CONTAINER_ID";

$sth = $dbh->prepare($stmt) || die "Statement #1a: $stmt\n".$dbh->errstr;
$sth->execute() || die "Statement #1b: $stmt\n".$dbh->errstr;
$ts++;
while ( ((
	  $ts_id{$ts},
	  $ts_name{$ts},
	  $ts_type{$ts},
	  $ts_content{$ts},	  
	  $ts_state{$ts}, 
	  $ts_pgtotal{$ts},
	  $ts_pguseable{$ts},
	  $ts_pgused{$ts},
	  $ts_pgfree{$ts},
	  $ts_pghwm{$ts},
	  $ts_pgsize{$ts},
	  $ts_extsize{$ts},
	  $ts_pfsize{$ts},
	  $ts_fscache{$ts},	  
	  $ts_autostg{$ts},	  
	  $ts_container{$ts},
	  $ts_tsc_id{$ts},
	  $ts_tsc_name{$ts},
	  $ts_tsc_type{$ts},
	  $ts_tsc_pgtotal{$ts},
	  $ts_tsc_pguseable{$ts},
	  $ts_tsc_pgaccess{$ts},
  	  $ts_pgusage{$ts},
 	  $ts_tsc_ext_total{$ts},
	  $ts_tsc_ext_used{$ts},
	  $ts_tsc_ext_hwm{$ts}	  ) = $sth->fetchrow()) )
{
  $ts++;
}

if ( $dbh->err )
{
  die "$0 Statement #1c: $stmt \n".
	"abended with $dbh->errstr";
}

$ts--; 

################################################################################
# Disconnect from DWH Target Database
################################################################################
$sth->finish()   || die $dbh->errstr;
$dbh->commit     || die $dbh->errstr;
$dbh->disconnect || die $dbh->errstr;

################################################################################
# File für Statements öffnen, falls vorhanden
################################################################################
if (! open(hFile, "> $opt_f") ) 
{  die "$0 cannot write to file $opt_f.\n".
	    "abends"; 
}

################################################################################
# Create Header for Redirected RESTORE DATABASE Statement
################################################################################
if ($opt_t eq "RESTORE" ) {

	$work = xmpPerl::doCmdOut("db2 get db cfg for $opt_d  | grep 'Path to log files'");
	($work,$dbcfg_logpath)	= split("=",$work);
	$dbcfg_logpath 		= xmpPerl::trim($dbcfg_logpath);
	$dbcfg_dftdbpath 	= xmpPerl::getDBMCfgValue("DFTDBPATH");

	print hFile "UPDATE COMMAND OPTIONS USING S ON Z ON $opt_f.out V ON;\n";
	print hFile "SET CLIENT ATTACH_NODE 0;\n";
	print hFile "SET CLIENT CONNECT_NODE 0;\n";
	$work = xmpPerl::getTimestamp();

	print hFile "ECHO \@--------------------------------------------------V$VER---\@;\n";	
	print hFile "ECHO \@Script created ........: $work \@;\n";
	print hFile "ECHO \@Script File ...........: $opt_f \@;\n";
	print hFile "ECHO \@Database to be restored: $opt_d (Redirected Restore) \@;\n";
	print hFile "ECHO \@Database Object Sizing : $opt_o \@;\n";
	print hFile "ECHO \@Object Growth Percent .: $opt_g %  (0=not expanding) \@;\n";
	print hFile "ECHO \@Disclaimer: Check/edit this script to avoid loss of data! \@;\n";
	print hFile "----------------------------------------------------------------\n";
	print hFile "RESTORE DB $opt_d \n".
		    "        FROM /back-up-path\n".
		    "        TAKEN AT yyyymmddhhmmss\n".
		    "        TO   $dbcfg_dftdbpath\n".
		    "        INTO new-db\n".
		    "--      NEWLOGPATH $dbcfg_logpath\n".
		    "--      WITH 2 BUFFERS BUFFER 2048\n".
		    "        REDIRECT;\n";
	print hFile "----------------------------------------------------------------\n";
	print hFile "-- Comments: DO NOT CHANGE THE NAME IN 'RESTORE DB $opt_d'!\n".
		    "--           DO NOT CHANGE THE NAME IN 'RESTORE DB $opt_d CONTINUE'!\n".
		    "--           /back-up-path = path or location of back-up file \n".
		    "--           yyymmddhhmmss = back-up start timestamp, part of backup-file name\n".
		    "--           /new-db-path  = database path, see CONTAINER statement below\n".
		    "--           new-db        = original database name or new target db name\n".
		    "--           /new-logpath  = path to log files, you may alter it afterwards\n".
		    "--                           with UPDATE DB CFG FOR ... USING NEWLOGPATH ...\n";
	print hFile "----------------------------------------------------------------\n";
	print hFile "-- RESTORE DB $opt_d ABORT;\n";
}

################################################################################
# Create Header for CREATE TABLESPACE Statement
################################################################################
if ($opt_t eq "CREATE" ) {
	$work = xmpPerl::getTimestamp();
	print hFile "---------------------------------------------------------V$VER---\n";	
	print hFile "-- DDL created ........: $work\n";
	print hFile "-- DDL File ...........: $opt_f\n";
	print hFile "-- Database ...........: $opt_d\n";
        print hFile "-- Object Sizing ......: $opt_o\n";
        print hFile "-- Object Growth % ....: $opt_g %\n";
	print hFile "-- Disclaimer: Check/edit this DDL script to avoid loss of data!\n";
	print hFile "----------------------------------------------------------------\n";

	############################################################################
	# DBI Switches 
	########################################################################
	my $switch = DBI->internal;
	$switch->debug(0);                     # 1=normal debug, 2=detailed handle trace
	#print "DBI Version and Attributes ..: $switch->{'Attribution'}, $switch->{'Version'}\n";
	#print "DBI Drivers .................: ",join(", ",DBI->available_drivers()),"\n";

	########################################################################
	# Connect to Database
	########################################################################
	$dbh = DBI->connect("dbi:DB2:$opt_d", 
	                       $opt_u, 
	                       $opt_p,
	                    { PrintError => 0, # don't print error by default
	                      RaiseError => 0, # $ts_id{$ts}don't print error and die by default
	                      AutoCommit => 0, # don't commit after each statement
	                      ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types
	                    } )
	        || die ">>>$0 Problem mit Connect to $opt_d\n>>>".$DBI::errstr;
}

################################################################################
# Create Header for CREATE TABLESPACE Statement
################################################################################
if ($opt_t eq "AUTOSIZE") 	{
	$work = xmpPerl::getTimestamp();
	print hFile "---------------------------------------------------------V$VER---\n";	
	print hFile "-- DDL created ........: $work\n";
	print hFile "-- DDL File ...........: $opt_f\n";
	print hFile "-- Database ...........: $opt_d\n";
        print hFile "-- Object Sizing ......: $opt_o\n";
        print hFile "-- Space Usage Percent : $opt_g %\n";
	print hFile "-- Disclaimer: Check/edit this DDL script to avoid loss of data!\n";
	print hFile "----------------------------------------------------------------\n";
}

################################################################################
# Request is to generate CREATE or RESTORE statements
################################################################################
if (   ($opt_t eq "CREATE") 
    or ($opt_t eq "RESTORE") )	 {
	############################################################################
	# Work on basis of tablespace container files
	############################################################################
	$work  = $ts;
	for ($ts = 1; $ts <= $work; $ts++) {
		$f1 = $ts_id{$ts};
		$f2 = $ts_tsc_id{$ts};
		$f3 = $ts_name{$ts};
		$f4 = $ts_tsc_type{$ts};
		$f8 = $ts_tsc_name{$ts};
		########################################################################
		# Summarize current Page Counts and KBytes: Total and HWM
		########################################################################
		$ts_total_prev 	= $ts_total_prev + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );
		$ts_total_used	= $ts_total_used + ($ts_tsc_ext_used{$ts}  * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );
		if ($ts_type{$ts} ne "SMS") {		# not SMS managed
			$ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_hwm{$ts}   * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}
		else {	$ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}
		#print "TS-ID=$ts_id{$ts} Name=$ts_name{$ts} c-ext-total=$ts_tsc_ext_total{$ts} c-ext-used=$ts_tsc_ext_used{$ts} c-ext-hwm=$ts_tsc_ext_hwm{$ts}\n";
		#print "Auto Storage=$ts_autostg{$ts}\n";

		########################################################################
		# New target by default is the number of currently allocate extents
		########################################################################
		if ($ts_tsc_id{$ts} == 0)	{	# 1st container 
			$new_target = $ts_tsc_ext_total{$ts};
		}
		########################################################################
		# If calculation on base of HWM requested (not valid for SMS)
		########################################################################
		if (($opt_o eq "HWM") && ($ts_type{$ts} ne "SMS") ) { 
			$new_target = $ts_tsc_ext_hwm{$ts};
			#print "Adjusted HWM, new target=$new_target\n";
		}
		########################################################################
		# Consider percentage of growth
		########################################################################
		if (($opt_g > 0) && ($ts_type{$ts} ne "SMS") ) {
			$new_target = int(($new_target * ((100+$opt_g)/100)) + 0.9999);	
			#print "Adjusted growth, new target=$new_target\n";
		}
		########################################################################
		# Select tablespace: TRUE or FALSE?	 
		########################################################################
		$f_select = 0;			
		if (   (!defined $opt_s)
		    or (  (defined $opt_s) 		
			&& ($f3 =~ /(\w+)$opt_s/g )  ) ) {	# Name equals
			$f_select = 1;
		}		
		if (!defined $opt_ns)	{}		# Name differs
		else {	if ($f3 =~ /(\w+)$opt_ns/g )   	{
				$f_select = 0;}		
		}
		#print "TS=$f3/$f2 selected=$f_select\n";
		########################################################################
		# Summarize Page Counts and KBytes	
		########################################################################
		if ($f_select) {
			$ts_total_new = $ts_total_new + ( $new_target * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );
		}
		########################################################################
		# Create Redirected RESTORE DATABASE Statement, Tablespace Sections
		########################################################################
		if ( ($opt_t eq "RESTORE" ) && $f_select ) {
			####################################################################
			# Create Container
			####################################################################
			if ($ts_tsc_id{$ts} == 0)	{	# 1st container 
				################################################################
				# Format output lines
				################################################################
				if ($ts gt 1) {							# end-up previous item	
					if ( $ts_autostg{$ts-1} == 0 ) {	# only if previous item
						print hFile "    );\n";			# is not of type 
					}									# automatic storage
				}
				print hFile "----------------------------------------------------***\n";
				if ($ts_type{$ts} eq "DMS") {
					print hFile "--  TS ID $f1 / $f3 - Space managed by Database\n";}
				else {	print hFile "--  TS ID $f1 / $f3 - Space managed by System\n";}
				if ( $ts_type{$ts} eq "DMS" ) {
					print hFile "--  Pages Allocated / Used / HWM: ".$ts_pgtotal{$ts}.
						" / ".$ts_pgused{$ts}." / ".$ts_pghwm{$ts}."\n";
				}
				else  {	print hFile "--  Space Allocated / Used .....: ".$ts_pgtotal{$ts}.
						" / ".$ts_pgused{$ts}." pages\n";}
 	         		print hFile "--  No. Container / Extent Size : ".$ts_container{$ts}.
						" file(s) / ".$ts_extsize{$ts}." pages\n";
				print hFile "-------------------------------------------------------\n";
				################################################################
				# Automatic Storage: Yes, needs no allocation
				################################################################
				if ( $ts_autostg{$ts} == 1 ) {
					print hFile "--  Automatic Storage: $f8\n";
				}
				else {
					print hFile "SET TABLESPACE CONTAINERS FOR $f1 USING (\n";
					#print "F4=$f4 C-Type=$ts_tsc_type{$ts}\n";
					if ($f4 =~ "FILE") {print hFile "    FILE $f8 ".$new_target*$ts_extsize{$ts}."  \n";}
					if ($f4 eq "PATH") {print hFile "    PATH $f8        \n";}
					if ($f4 =~ "DISK") {print hFile "    DEVICE $f8 ".$new_target*$ts_extsize{$ts}."\n";}
				}
			}
			else	{
				################################################################
				# Automatic Storage: Yes, needs no allocation
				################################################################
				if ( $ts_autostg{$ts} == 1 ) {
					print "--  Automatic Storage: $f8\n";
				}
				else {			
					if ($f4 =~ "FILE") {print hFile "   ,FILE $f8 ".$new_target*$ts_extsize{$ts}."  \n";}
					if ($f4 eq "PATH") {print hFile "   ,PATH $f8        \n";}
					if ($f4 =~ "DISK") {print hFile "   ,DEVICE $f8 ".$new_target*$ts_extsize{$ts}."\n";}
				}
			}
		}

		########################################################################
		# Create CREATE TABLESPACE Statement
		########################################################################
		if (($opt_t eq "CREATE" ) && $f_select) {
			########################################################
			# Access to DB2 Catalog to get Bufferpool
			########################################################
			$stmt =	"select b.bpname ".
				"from   sysibm.systablespaces a, ".
				"       sysibm.sysbufferpools b ".
				"where  a.bufferpoolid = b.bufferpoolid ".
				"and    a.tbspace = '$f3' ".
				"and    a.tbspaceid = $f1 ";	
			$sth = $dbh->prepare($stmt);
			$sth->execute() || die $dbh->errstr;
			if ( $dbh->err ) {
				die "$0 abended with $dbh->errstr";
			}						
			$bpool = $sth->fetchrow();
			if ( $dbh->err ) {
				$bpool = "BPCannotCreate $dbh->errstr";
			}
			####################################################################
			# Create Tablespace
			####################################################################
			if ($ts_tsc_id{$ts} == 0)	{	# 1st container 
				################################################################
				# Format output lines
				################################################################
				print hFile "----------------------------------------------------***\n";
				print hFile "-- TS $f1 $f3: ".$ts_pgtotal{$ts}." total pages, HWM ".$ts_pghwm{$ts}." pages\n";
				print hFile "-- DROP TABLESPACE $f3;\n";
				if ($ts_content{$ts} eq "USRTEMP") {print hFile "   CREATE USER TEMPORARY TABLESPACE $f3\n";}	# V4.4
				if ($ts_content{$ts} eq "SYSTEMP") {print hFile "   CREATE TEMPORARY TABLESPACE $f3\n";}
				if ($ts_content{$ts} eq "LARGE")   {print hFile "   CREATE LARGE TABLESPACE $f3\n";}			# V4.4		
				if ($ts_content{$ts} eq "ANY")     {print hFile "   CREATE REGULAR TABLESPACE $f3\n";}
				print hFile "          PAGESIZE ".( $ts_pgsize{$ts}/1024) ."K \n";
				################################################################
				# SMS or DMS (includes Automatic Storage
				################################################################
				if ($ts_type{$ts} eq "DMS") {
					if ( $ts_autostg{$ts} == 1 ) {
						print hFile "          MANAGED BY DATABASE\n";
					}
					else	{
						print hFile "          MANAGED BY DATABASE\n";
					}
				}
				else {	
					print hFile "          MANAGED BY SYSTEM\n";
				}
				if ( $ts_autostg{$ts} == 0 ) {
					if ($f4 =~ "FILE") {print hFile "          USING (FILE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}
					if ($f4 eq "PATH") {print hFile "          USING ('$f8' \n";}
					if ($f4 =~ "DISK") {print hFile "          USING (DEVICE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}
				}
			}
			else				{
				if ( $ts_autostg{$ts} == 0 ) {
					if ($f4 =~ "FILE") {print hFile "                ,FILE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}
					if ($f4 eq "PATH") {print hFile "                ,'$f8'    \n";}
					if ($f4 =~ "DISK") {print hFile "                ,DEVICE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}
				}
			}
			####################################################################
			# Last Container? Add additional attributes at end of current cmd
			####################################################################
			if ($ts_tsc_id{$ts}+1 == $ts_container{$ts})	{
				################################################################
				# Prefetch Size
				################################################################
				if ($ts_pfsize{$ts} > 0) {
					print hFile "          ) EXTENTSIZE ".$ts_extsize{$ts}." PREFETCHSIZE ".$ts_pfsize{$ts}."\n";}
				if ($ts_pfsize{$ts} == 0) {
				   	print hFile "          ) EXTENTSIZE ".$ts_extsize{$ts}."\n";}
				if ($ts_pfsize{$ts} == -1) {
				   	print hFile "          ) EXTENTSIZE ".$ts_extsize{$ts}." PREFETCHSIZE AUTOMATIC\n";}

				################################################################
				# Autoresize
				################################################################
				$autoresize = GetAutoresizeOptions($f3,$f1);
				if ($autoresize ne "") 	{
					print hFile "          ".$autoresize."\n";
				}
				################################################################
				# Bufferpool
				################################################################
				print hFile "          BUFFERPOOL $bpool\n";
				################################################################
				# File System Caching									V4.4.2
				################################################################
				if ($ts_fscache{$ts} == 1) {
				   	print hFile "          NO FILE SYSTEM CACHING\n";
				}
				print hFile ";\n";
			}
		}
	}	
	$ts--;
}

################################################################################
# Request is to AUTOSIZE (right size) tablespace allocation in pages
################################################################################
if ($opt_t eq "AUTOSIZE")	{
	############################################################################
	# Work on basis of tablespaces
	############################################################################
	$work  = $ts;
	for ($ts = 1; $ts <= $work; $ts++) {
		$f1 = $ts_id{$ts};				# Tablespace Structure
		$f2 = $ts_name{$ts};
		$f4 = $ts_content{$ts};			# Content Type
		$f6 = $ts_pgtotal{$ts};
		$f7 = $ts_pguseable{$ts};
		$f8 = $ts_pgused{$ts};
		$f9 = $ts_pgfree{$ts};
		$f10 = $ts_pghwm{$ts};			# High Water Mark		
		$f11 = $ts_pgsize{$ts};			# Page Size
		$f12 = $ts_extsize{$ts};		# Extent Size
		$f13 = $ts_pfsize{$ts};			# Prefetch Size
		$f14 = $ts_container{$ts};		# no. of containers
		$f21 = $ts_pgusage{$ts};		# Tablespace Usage
		$f26 = $ts_autostg{$ts};		# Autoamtic Storage Indicator
		########################################################################
		# Summarize current Page Counts and KBytes: Total and HWM
		########################################################################
		$ts_total_prev 	= $ts_total_prev + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );
		$ts_total_used	= $ts_total_used + ($ts_tsc_ext_used{$ts}  * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );
		if ($ts_type{$ts} ne "SMS") {		# not SMS managed
			$ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_hwm{$ts}   * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}
		else {	$ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}
		#print "TS-ID=$ts_id{$ts} Name=$ts_name{$ts} c-ext-total=$ts_tsc_ext_total{$ts} c-ext-used=$ts_tsc_ext_used{$ts} c-ext-hwm=$ts_tsc_ext_hwm{$ts}\n";
		########################################################################
		# New target by default is the number of currently allocate extents
		########################################################################
		if ($ts_tsc_id{$ts} == 0)	{	# 1st container 
			$new_target = $ts_tsc_ext_total{$ts};
		}
		########################################################################
		# Select tablespace: TRUE or FALSE?	 
		########################################################################
		$f_changed = 0;
		$f_select = 0;			
		if (	(  (!defined $opt_s)
		    	&& ($ts_tsc_id{$ts} == 0) 	# 1st container 
			)
		    or (   (defined $opt_s) 		
		    	&& ($ts_tsc_id{$ts} == 0)	# 1st container 
			&& ($f2 =~ /(\w+)$opt_s/g )  ) 
			) 			{	# Name equals
			$f_select = 1;
		}		
		if (!defined $opt_ns)	{}
		else {
			if  ( $f2 =~ /(\w+)$opt_ns/g )	{
				$f_select = 0;
			}	
		}
		########################################################################
		# Tablespace is of type "DMS"  and qualified for processing
		########################################################################
		#print "TS=$f2 TSC-ID=$ts_tsc_id{$ts} Usage=$f21 type=$ts_type{$ts} ctype=$ts_tsc_type{$ts} selected=$f_select\n";
		if  (   ($ts_type{$ts} ne "SMS")
     	     	     && ($ts_tsc_type{$ts} =~ "FILE") 
     		     && ($f_select)	  		   )	{
			################################################################
			# If calculation on base of HWM requested (not valid for SMS)
			################################################################
			if (  ($opt_o eq "HWM") 
			   && ($ts_tsc_ext_hwm{$ts} != $ts_tsc_ext_total{$ts})	)	{ 
				$new_target = $ts_tsc_ext_hwm{$ts};
				$f_changed = 1;
				#print "Adjusted HWM, new target=$new_target\n";
			}
			################################################################	
			# Consider percentage of growth for HWM
			################################################################
			if (	($opt_g > 0) 
			   && 	($opt_o eq "HWM")
			   && 	($ts_type{$ts} ne "SMS") ) {
				$new_target = int(($new_target * ((100+$opt_g)/100)) + 0.9999);					
				$f_changed = 1;
				#print "Adjusted HWM growth, new target=$new_target\n";
			}
			################################################################	
			# Consider percentage of usage 
			################################################################
			if (	($opt_g > 0) 
			   && 	($opt_o ne "HWM")
			   && 	($ts_type{$ts} ne "SMS") ) {
				$new_target = int(($ts_tsc_ext_used{$ts} / ($opt_g/100)) + 0.9999);	
				if ($new_target < $ts_tsc_ext_hwm{$ts}) {
					$new_target = $ts_tsc_ext_hwm{$ts};
				}
				$f_changed = 1;
				#print "Adjusted usage, new target=$new_target\n";
			}
			########################################################
			# Calculated space equals current space = do nothing
			########################################################
			if ($new_target == $ts_tsc_ext_total{$ts})	{
				$f_changed = 0;
			}
		}
		########################################################################
		# Tablespace size changed?
		########################################################################
		if ($f_changed)	{
			####################################################################
			# Write information on console and to file
			####################################################################
			print "TS $f1 $f2: Pages Total=$f6, Useable=$f7, Used=$f8\n".
			      "   HWM=$f10, Free=$f9, Containers=$f14, Ext.Size=$f12, PageSize=$f11\n";
			print hFile "----------------------------------------------------***\n";
			print hFile "-- TS $f1 $f2: Pages Total=$f6, Useable=$f7, Used=$f8\n".
			            "--    HWM=$f10, Free=$f9, Containers=$f14, Ext.Size=$f12, PageSize=$f11\n";
    			print hFile "----------------------------------------------------***\n";
			####################################################################
			# Write information on console and to file
			####################################################################
			print "   -> Usage $f21 %, adjust to new TS target of ".
				($new_target*$ts_extsize{$ts}*$ts_container{$ts})." pages\n";
			print hFile "-- -> Usage $f21 %, adjust to new TS target of ".
				($new_target*$ts_extsize{$ts}*$ts_container{$ts})." pages\n";
			if ( $f26 == 1 )	{				# Automatic Storage
				#print       "   -> automtic storage, alteration not necessary\n";
				#print hFile "-- -> automtic storage, alteration not necessary\n";
				if ($ts_pgtotal{$ts}-($new_target*$ts_extsize{$ts}) >0) {
					print "   -> lower HWM and reduce by ".
						($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts})." pages\n";
					print hFile "-- -> lower HWM and reduce by ".
						($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts})." pages\n";	
				}
				else {
					#print "   -> cannot extend automatic storage tablespace by ".
					#	(($new_target*$ts_extsize{$ts})-$ts_pgtotal{$ts})." pages\n";
					#print hFile "-- -> cannot extend automatic storage tablespace by ".
					#	(($new_target*$ts_extsize{$ts})-$ts_pgtotal{$ts})." pages\n";	
				}
			}
			else	{
				print "   -> resizing $f14 containers each with ".
					$new_target*$ts_extsize{$ts}." pages\n";
				print hFile "-- -> resizing $f14 containers each with ".
					$new_target*$ts_extsize{$ts}." pages\n";
			}

			####################################################################
			# Write ALTER statement on console and to file
			####################################################################
			if ( $f26 == 1 )	{				# Automatic Storage
				if ($ts_pgtotal{$ts}-($new_target*$ts_extsize{$ts}) >0) {
				    print hFile "   ALTER TABLESPACE $f2 LOWER HIGH WATER MARK;\n";
					print hFile "!  sleep ".(10+int(($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts})/1000)).";\n";
					print hFile "   ALTER TABLESPACE $f2\n";
					print hFile "         REDUCE ".
						($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts}).";\n";
				}
				else { $new_target = $ts_pgtotal{$ts}/$ts_extsize{$ts}; }
			}
			else	{
				print hFile "   ALTER TABLESPACE $f2\n";
				print hFile "         RESIZE (ALL CONTAINERS ".
					$new_target*$ts_extsize{$ts}.");\n";
			}
		}
		########################################################################
		# Summarize new space consumpion
		########################################################################
		$ts_total_new = $ts_total_new + ( $new_target * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );
		#print "Total new=$ts_total_new\n";
	}
	$ts--;
}

################################################################################
# Create Footer for Redirected RESTORE DATABASE Statement
################################################################################
if ($opt_t eq "RESTORE" ) {
	print hFile "   );\n";
	print hFile "----------------------------------------------------***\n";
	print hFile "ECHO \@Restored Database Size : $ts_total_new KBytes \@;\n";
	print hFile "----------------------------------------------------***\n";
	print hFile "RESTORE DB $opt_d CONTINUE;\n";
	print hFile "-- ROLLFORWARD DATABASE $opt_d TO END OF LOGS;\n";
	print hFile "-- ROLLFORWARD DATABASE $opt_d STOP;\n";
	print hFile "----------------------------------------------------------------\n";
	print hFile "-- Comments: Use ROLLFORWARD TO END OF LOGS, if log files will\n".
		    "--           contain important information which will bring the\n".
		    "--           database to latest consistent state. Use STOP to\n".
		    "--           ignore log files at all. Do not use CANCEL keyword!\n";
	print hFile "----------------------------------------------------------------\n";
}
################################################################################
# Create Footer for CREATE Statement
################################################################################
if ($opt_t eq "CREATE" ) {
	print hFile "-- \n";
        print hFile "-- Database Total Size : $ts_total_new KBytes\n";
        print hFile "-- ***END***\n";
	########################################################################
	# Close Statement, Disconnect from Database
	########################################################################
	$sth->finish()   || die $dbh->errstr;
	$dbh->commit     || die $dbh->errstr;
	$dbh->disconnect || die $dbh->errstr;
}

################################################################################
# Close File and set File Access Mode
################################################################################
close(hFile); 
chmod 0775,$opt_f;

print "Current Total allocated .....: $ts_total_prev kBytes\n";
print "Current Space used ..........: $ts_total_used kBytes\n";
print "High Water Mark set at ......: $ts_total_hwm kBytes\n";
print "(New) Space to be allocated  : $ts_total_new kBytes\n";
print "Tablespaces $opt_t File successfully created for $opt_d!\n";

print "***Ende***\n";

exit 0;
################################################################################
# SUBROUTINES AND FUNCTIONS ---------------------------------------------------#
################################################################################
# GetAutoresizeOptions: Provides new TABLESPACE AUTORESIZE options  	V4.1
#                       Comment: With DB2 V8.2 data only avaible in snapshots!
################################################################################
sub GetAutoresizeOptions {

   my $autoresize = "";
   my $ts;
   my $tsid;
   my $autoresize_enabled;
   my $max_size;
   my $increase_size;
   my $increase_percent;

	($ts,$tsid) = @_;

	########################################################################
	# Build and execute statement to determine AUTORESIZE option
	########################################################################
	$stmt = "SELECT COALESCE(TBSP.TBSP_AUTO_RESIZE_ENABLED,0), ".
	        "       COALESCE(TBSPP.TBSP_MAX_SIZE,0), ".
	        "       COALESCE(TBSPP.TBSP_INCREASE_SIZE,0), ".
	        "       COALESCE(TBSPP.TBSP_INCREASE_SIZE_PERCENT,0) ".		
	        "FROM   TABLE(SNAP_GET_TBSP('',-1)) AS TBSP, ".
	        "       TABLE(SNAP_GET_TBSP_PART('',-1)) AS TBSPP  ".
		"WHERE  TBSP.TBSP_NAME = '$ts' ".
		"AND    TBSP.TBSP_ID   = $tsid ".		
		"AND    TBSP.TBSP_NAME = TBSPP.TBSP_NAME ".
		"AND    TBSP.TBSP_ID   = TBSPP.TBSP_ID ";
	#print "$stmt\n";
	if ( ! ($sth = $dbh->prepare($stmt)) ) {
		print "Problem with SQL statement '$stmt' (a):\n";
		print "SQLCODE=".$dbh->err."\n";
		print $dbh->errstr."\n";
		die ">>>$0 Needs clarification!";
	}	
	if ( ! ($sth->execute()) ) {
		print "Problem with SQL statement '$stmt' (b):\n";
		print "SQLCODE=".$dbh->err."\n";
		print $dbh->errstr."\n";
		die ">>>$0 Needs clarification!";
	}
	if ( ! (($autoresize_enabled, 
		 $max_size, 
		 $increase_size, 
		 $increase_percent) = $sth->fetchrow()) ) {
		if (!defined $autoresize_enabled) 	{
			$autoresize_enabled = 0;	}	
		else	{print "Problem with SQL statement '$stmt' (c):\n";
			print "SQLCODE=".$dbh->err."\n";
			print $dbh->errstr."\n";
			die ">>>$0 Needs clarification!";
		}
	}

	#########################################################################
	# Close Statement
	#########################################################################
	$sth->finish()   || die $dbh->errstr;
	$dbh->commit     || die $dbh->errstr;
	#print "$autoresize_enabled $max_size $increase_size  $increase_percent\n";
	#########################################################################
	# Determine AUTORESIZE options
	#########################################################################
	if ($autoresize_enabled == 1)	{
		$autoresize = "AUTORESIZE YES";
		#################################################################
		# Maximum Size MAXSIZE specified?
		#################################################################
		if ($max_size == -1)	{$autoresize = $autoresize." MAXSIZE NONE"}
		if ($max_size > 0)	{$autoresize = $autoresize." MAXSIZE ".$max_size." K"}
		#################################################################
		# Increase Size or Percent INCREASESIZE specified?
		#################################################################
		if ($increase_size > 0)	{
			$autoresize .= " INCREASESIZE ".$increase_size." K"}
		if ($increase_percent > 0)	{
			$autoresize .= " INCREASESIZE ".$increase_percent." PERCENT"}		
	}
	#print "AUTORESIZE string=$autoresize\n";
	return $autoresize;
}

#################################################################################
# SaySyntax
#################################################################################
sub SaySyntax {
	die "Usage: [perl] $0 -d db [-u parm -p parm] -f file [-t {RESTORE|CREATE|AUTOSIZE} -o {CURRENT|HWM}] [-g nn] [-s pattern] [-ns pattern]\n".
		"		-d database \n".
		"		-u User (opt)\n".
		"		-p Password (opt)\n".
		"		-f file for statement generation\n".
		"		-t type of statement to generate (opt).\n".
		"		-o current allocation or HWM (High Water Mark) (opt.)\n".
		"		-g fill-meter (AUTOSIZE target usage %) \n".
		"			or growth (additional uplift % on HWM) (opt.)\n".
		"		-s tablespace name pattern (opt.)\n".
		"		-ns single tablespace name exclude pattern (opt.)\n".
		"	Using patterns,	examples and usage comments:\n".
		"	-ns patt		Do not use replace chars like '*' or '%'\n".
		"	-s 'patt1|patt2'	Imbed multiple patterns in (double) quotes\n".
		"	-ns patt1 -s patt2	Paired patterns work like OR condition\n".
		"abends.\n".
		"See also script xmpTBSCList.pl for container files and tablespace options-";
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

#!/usr/local/bin/perl -w

################################################################################

# xmpTBSCStmt.pl - Generate Tablespace/Container SQL Statements

# Called by - Command Level oder DBA Scripts

# Calls - xmpPerl.pm - xmp DBA Perl Package

# Getopt - Argument Verwaltung

# Env - Zugriff auf Umgebungsvariablen

# English - Aliase für Perl Variablen

# Optionen - xmpTBSCStmt.pl -d database Datenbank-Name

# -u user -p psw User und Password

# -f file File für Statement Generierung

# -t CREATE|RESTORE|AUTOSIZE

# -o CURRENT|HWM Size: Current Alloc or High Water Mark

# -g nn Plus nn % growth or Fill-Meter

# -s tablespace Name Pattern

# -ns tablespace Name Pattern to exclude

#-------------------------------------------------------------------------------

# Autor - GR /28.03.01 - V1.0 first version, copied from xmpTBSCList.pl

# Changes - GR /16.05.01 - V1.1 Access to DB2 Catalog for BPool and changes,

# e.g. for calculation of HWM page sizes

# GR /16.05.01 - V1.2 Minimum Requirements added to HWM page size

# GR /06.07.01 - V1.3 new parm -g nn for 'growth'

# GR /02.11.01 - V1.4 writes more comments to generated script

# GR /22.07.03 - V2.0 generates ALTER TABLESPACE RESIZE statements

# GR /22.07.03 - V2.0.1 Fixed problem new target percentage

# GR /14.04.04 - V2.1 Parm opt_s became opt_o, new parm opt_s for

# specific tablespaces, resizing adjusted to next integer

# GR /03.05.04 - V2.1.1 Problem with opt_s fixed

# GR /07.06.04 - V2.2 New parameter DOWNSIZE for V8.x objects

# GR /08.03.05 - V2.3 New space must at least cover high water mark

# while downsizing space

# GR /06.04.05 - V2.4 New 'exclude tablespace' parameter -ns

# GR /20.04.05 - V3.0 Code restructured, several amendments

# GR /02.05.05 - V3.0.1 Small correction concerning RESTORE/CREATE,

# GR /06.05.05 - V3.1 Correction in DOWNSIZE formulas, CREATE/RESTORE section

# GR /06.06.05 - V3.2 Add 1 Extent for min. page requested for new V8.2 DB's

# (see PMR 8144), reactivate -g for option "HWM"

# GR /05.07.05 - V4.0 New V8.2 technique to access data via SQL,

# RESIZE/DOWNSIZE merged to AUTOSIZE

# GR /07.11.05 - V4.1 Determine AUTORESIZE options for CREATE statement

# using SNAP_GET_TBSP_PART (Table Function)

# GR /25.04.06 - V4.2 Provide PREFETCHSIZE information for CREATE's

# GR /18.08.06 - V4.3 Allow multiple -s/-ns patterns

# GR /12.02.07 - V4.4 New Default is LARGE TABLESPACE (Type 1) und

# USER TEMPORARY (3)

# GR /26.02.07 - V4.4.1 Handle ts.total_pages=0 in SELECT, min=1

# GR /19.03.07 - V4.4.2 consider FILE SYSTEM CACHING

# GR /17.08.10 - V5.0 Consider AUTOMATIC STORAGE, deprecated SQL

# administrative routines SNAPSHOT_TBS_CFG and

# SNAPSHOT_CONTAINER replaced by views

# GR /01.08.11 - V5.1 Amendments for DB2 V9.7

# GR /10.10.11 - V5.1.1 Wait a while between ALTER HIGH WATER MARK and REDUCE

# to avoid SQL0290N

#-------------------------------------------------------------------------------

# Comments - PERL5LIB environment variable must point to Perl module path!

################################################################################

# Modules

################################################################################

use strict;

use Getopt::Long; # V2.4

use vars qw($opt_d $opt_u $opt_p $opt_f $opt_t $opt_o $opt_g); # V2.4

use vars qw($opt_s $opt_ns); # V2.4

use xmpPerl;

use Env;

use English;

use DBI;

use DBD::DB2::Constants;

use DBD::DB2;

################################################################################

# Declare/Init

################################################################################

my $VER = "5.1.1";

my $work = ""; # for TS List

my @work; # for TS List

my ($dbh, $sth); # DB2 Access Handles

my $stmt = "";

my $f_select; # true/false

my $f_changed; # true/false

my $ts = 0; my $f0; # no. of Tablespaces

my %ts_id = (); my $f1; # Tablespace Structure

my %ts_name = (); my $f2;

my %ts_type = (); my $f3; # 0=SMS 1=DMS

my %ts_content = (); my $f4; # 0=ANY 1=LARGE 2=SYSTEMP 3=USRTEMP

my %ts_state = (); my $f5;

my %ts_pgtotal = (); my $f6;

my %ts_pguseable= (); my $f7;

my %ts_pgused = (); my $f8;

my %ts_pgfree = (); my $f9;

my %ts_pghwm = (); my $f10; # High Water Mark

my %ts_pgsize = (); my $f11; # Page Size

my %ts_extsize = (); my $f12; # Extent Size

my %ts_pfsize = (); my $f13; # Prefetch Size

my %ts_fscache = (); my $f25; # File System Caching V4.4.2

my %ts_autostg = (); my $f26; # Automatic Storage V4.5

my %ts_container= (); my $f14; # no. of containers

my %ts_tsc_id = (); my $f15; # Container ID

my %ts_tsc_name = (); my $f16; # Container Name

my %ts_tsc_type = (); my $f17; # Container Type

my %ts_tsc_pgtotal = (); my $f18;

my %ts_tsc_pguseable=(); my $f19;

my %ts_tsc_pgaccess=(); my $f20;

my %ts_pgusage = (); my $f21; # Usage in Percent

my %ts_tsc_ext_total= (); my $f22; # number of total container extents

my %ts_tsc_ext_used= (); my $f23; # number of container extents used

my %ts_tsc_ext_hwm= (); my $f24; # number of HWM container extents

my $ts_total_prev = 0; # Current Total Allocation

my $ts_total_used = 0; # Current Allocation Used

my $ts_total_hwm = 0; # HWM Allocation

my $ts_total_new = 0; # new to allocate

my $hFile; # File Handle

my $new_target = 0; # new number pages calculated

my $bpool = ""; # Bufferpool Name

my $dbcfg_logpath = ""; # DB Log Path

my $dbcfg_dftdbpath = ""; # Default DB Path

my $autoresize = ""; # AUTORESIZE V4.1

################################################################################

# Argumente/Options prüfen

################################################################################

GetOptions("d=s","u:s","p:s","f:s","t:s","o:s", # V2.4

"g:i","s:s","ns:s"); # V2.4

if (! defined $opt_d ) # database check

{print "$0 - database not specified\n";

SaySyntax();

}

if (! defined $opt_u ) # user check

{$opt_u = xmpPerl::getDBAUser();

if ($opt_u eq "")

{print "$0 - user not specified and lP_LUS env variable not defined\n";

SaySyntax(); }

}

if (! defined $opt_p ) # User Password Check

{$opt_p = xmpPerl::getDBAPsw();

if ($opt_p eq "")

{print "$0 - missing user's password fehlt and lP_LPW env variable not defined\n";

SaySyntax(); }

}

if (! defined $opt_f ) # File für Statement Generierung

{print "$0 - missing name of file to generate\n";

SaySyntax();

}

if (! defined $opt_t ) # Type

{$opt_t = "RESTORE";

}

if (! defined $opt_o ) # Option = Current Allocation Size

{$opt_o = "CURRENT";

}

if (defined $opt_s) { # V2.1: Tablespace

$opt_s = uc $opt_s; # V2.1: uppercase

}

if (defined $opt_ns) { # V2.4: Exclude Tablespace

$opt_ns = uc $opt_ns; # V2.4: uppercase

}

################################################################################

$opt_d = uc $opt_d;

$opt_o = uc $opt_o;

$opt_t = uc $opt_t;

################################################################################

# Support downwards compatiblity to V3.1

################################################################################

if ($opt_t eq "DOWNSIZE") {$opt_t = "AUTOSIZE"; }

if ($opt_t eq "RESIZE") {$opt_t = "AUTOSIZE"; }

################################################################################

# Check arrugments passed

################################################################################

if ( ($opt_t ne "RESTORE") # option check

&& ($opt_t ne "CREATE")

&& ($opt_t ne "AUTOSIZE") ) {

print "$0 - Type invalid, please choose either CREATE, RESTORE or AUTOSIZE!\n";

SaySyntax();

}

if (($opt_o ne "CURRENT") && ($opt_o ne "HWM") ) {

print "$0 - Options -o invalid value, use CURRENT or HWM only!\n";

SaySyntax();

}

if (! defined $opt_g ) # Growth/Fill-Meter Percentage

{$opt_g = 0;

}

if (($opt_g < 0) | ($opt_g > 1000) ) {

print "$0 - Option -g invalid value, use 0 - 1000 only!\n";

SaySyntax();

}

################################################################################

# Check DB2 Existenz und Informationsblock ausgeben

################################################################################

xmpPerl::chkDB2();

xmpPerl::prtHeaderDB2("$0 - Generating Tablespace Statements - V$VER");

print "Database / DB2 User ID ......: $opt_d / $opt_u\n";

print "Name of file to generate ....: $opt_f\n";

print "Type of generation ..........: $opt_t\n";

print "Object Size to consider .....: $opt_o\n";

if ($opt_o eq "CURRENT") {

print "Fill-meter in percent .......: $opt_g % (target usage in percent)\n";}

else { print "Object Growth ...............: $opt_g % (increase in percent)\n";}

if (defined $opt_s) {

print "Tablespace name pattern .....: $opt_s\n";

}

if (defined $opt_ns) {

print "Tablespace exclude pattern ..: $opt_ns\n";

}

################################################################################

# DBI Switches

################################################################################

my $switch = DBI->internal;

$switch->debug(0); # 1=normal debug, 2=detailed handle trace

#print "DBI Version and Attributes ..: $switch->{'Attribution'}, $switch->{'Version'}\n";

#print "DBI Drivers .................: ",join(", ",DBI->available_drivers()),"\n";

################################################################################

# Connect to DWH Source Database holding Delta Processing Control Table

################################################################################

$dbh = DBI->connect("dbi:DB2:$opt_d",

$opt_u,

$opt_p,

{ PrintError => 0, # don't print error by default

RaiseError => 0, # don't print error and die by default

AutoCommit => 0, # don't commit after each statement

ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types

} )

|| die ">>>$0 Problem mit Connect to $opt_d\n>>>".$DBI::errstr;

################################################################################

# Access physical Tablespace and Tablespace Container attributes and information

################################################################################

$stmt = "WITH TBSC (SNAPSHOT_TIMESTAMP, TBSP_NAME, TBSP_ID, TBSP_TYPE, TBSP_CONTENT_TYPE,

TBSP_PAGE_SIZE, TBSP_EXTENT_SIZE, TBSP_PREFETCH_SIZE, FS_CACHING,

TBSP_USING_AUTO_STORAGE, TBSP_AUTO_RESIZE_ENABLED,

CONTAINER_NAME, CONTAINER_ID, CONTAINER_TYPE, TOTAL_PAGES,

USABLE_PAGES, ACCESSIBLE, STRIPE_SET,

TBSP_STATE, TBSP_TOTAL_PAGES, TBSP_USABLE_PAGES, TBSP_USED_PAGES,

TBSP_FREE_PAGES, TBSP_PENDING_FREE_PAGES, TBSP_PAGE_TOP,

TBSP_NUM_CONTAINERS, TBSP_INITIAL_SIZE, TBSP_CURRENT_SIZE,

TBSP_MAX_SIZE, TBSP_INCREASE_SIZE, TBSP_INCREASE_SIZE_PERCENT,

TBSP_LAST_RESIZE_TIME, TBSP_LAST_RESIZE_FAILED, TABLESPACE_PGUSAGE)

AS (SELECT TS.SNAPSHOT_TIMESTAMP, TS.TBSP_NAME, TS.TBSP_ID, TS.TBSP_TYPE,

TS.TBSP_CONTENT_TYPE, TS.TBSP_PAGE_SIZE, TS.TBSP_EXTENT_SIZE,

TS.TBSP_PREFETCH_SIZE, TS.FS_CACHING, TS.TBSP_USING_AUTO_STORAGE,

TS.TBSP_AUTO_RESIZE_ENABLED, TSC.CONTAINER_NAME, TSC.CONTAINER_ID,

TSC.CONTAINER_TYPE, TSC.TOTAL_PAGES, TSC.USABLE_PAGES, TSC.ACCESSIBLE,

TSC.STRIPE_SET,

TBSP.TBSP_STATE, TBSP.TBSP_TOTAL_PAGES, TBSP.TBSP_USABLE_PAGES,

TBSP.TBSP_USED_PAGES, TBSP.TBSP_FREE_PAGES, TBSP.TBSP_PENDING_FREE_PAGES,

TBSP.TBSP_PAGE_TOP, TBSP.TBSP_NUM_CONTAINERS, TBSP.TBSP_INITIAL_SIZE,

TBSP.TBSP_CURRENT_SIZE, TBSP.TBSP_MAX_SIZE, TBSP.TBSP_INCREASE_SIZE,

TBSP.TBSP_INCREASE_SIZE_PERCENT, TBSP.TBSP_LAST_RESIZE_TIME,

TBSP.TBSP_LAST_RESIZE_FAILED,

DECIMAL ((TBSP.TBSP_USED_PAGES * 100.00) / (CASE TBSP.TBSP_TOTAL_PAGES

WHEN 0 THEN 1

ELSE TBSP.TBSP_TOTAL_PAGES END ), 5, 2)

AS USAGE

FROM SYSIBMADM.SNAPTBSP AS TS,

SYSIBMADM.SNAPCONTAINER AS TSC,

SYSIBMADM.SNAPTBSP_PART AS TBSP

WHERE TS.TBSP_ID = TSC.TBSP_ID

AND TS.TBSP_NAME = TSC.TBSP_NAME

AND TS.TBSP_ID = TBSP.TBSP_ID

AND TS.TBSP_NAME = TBSP.TBSP_NAME

)

SELECT TBSP_ID, TBSP_NAME, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_STATE,

TBSP_TOTAL_PAGES, TBSP_USABLE_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES,

TBSP_PAGE_TOP,

TBSP_PAGE_SIZE, TBSP_EXTENT_SIZE, TBSP_PREFETCH_SIZE, FS_CACHING,

TBSP_USING_AUTO_STORAGE, TBSP_NUM_CONTAINERS,

CONTAINER_ID, CONTAINER_NAME, CONTAINER_TYPE,

TOTAL_PAGES, USABLE_PAGES, ACCESSIBLE,

TABLESPACE_PGUSAGE,

(CEIL(1.00 * TOTAL_PAGES / TBSP_EXTENT_SIZE)) AS CONTAINER_EXTENTS_TOTAL,

(CEIL(1.00 * TBSP_USED_PAGES / TBSP_NUM_CONTAINERS / TBSP_EXTENT_SIZE) +1 ) AS CONTAINER_EXTENTS_USED,

(CEIL(1.00 * TBSP_PAGE_TOP / TBSP_NUM_CONTAINERS / TBSP_EXTENT_SIZE) +1 ) AS CONTAINER_EXTENTS_HWM

FROM TBSC

ORDER BY TbSC.TBSP_ID, TBSC.CONTAINER_ID";

$sth = $dbh->prepare($stmt) || die "Statement #1a: $stmt\n".$dbh->errstr;

$sth->execute() || die "Statement #1b: $stmt\n".$dbh->errstr;

$ts++;

while ( ((

$ts_id{$ts},

$ts_name{$ts},

$ts_type{$ts},

$ts_content{$ts},

$ts_state{$ts},

$ts_pgtotal{$ts},

$ts_pguseable{$ts},

$ts_pgused{$ts},

$ts_pgfree{$ts},

$ts_pghwm{$ts},

$ts_pgsize{$ts},

$ts_extsize{$ts},

$ts_pfsize{$ts},

$ts_fscache{$ts},

$ts_autostg{$ts},

$ts_container{$ts},

$ts_tsc_id{$ts},

$ts_tsc_name{$ts},

$ts_tsc_type{$ts},

$ts_tsc_pgtotal{$ts},

$ts_tsc_pguseable{$ts},

$ts_tsc_pgaccess{$ts},

$ts_pgusage{$ts},

$ts_tsc_ext_total{$ts},

$ts_tsc_ext_used{$ts},

$ts_tsc_ext_hwm{$ts} ) = $sth->fetchrow()) )

{

$ts++;

}

if ( $dbh->err )

{

die "$0 Statement #1c: $stmt \n".

"abended with $dbh->errstr";

}

$ts--;

################################################################################

# Disconnect from DWH Target Database

################################################################################

$sth->finish() || die $dbh->errstr;

$dbh->commit || die $dbh->errstr;

$dbh->disconnect || die $dbh->errstr;

################################################################################

# File für Statements öffnen, falls vorhanden

################################################################################

if (! open(hFile, "> $opt_f") )

{ die "$0 cannot write to file $opt_f.\n".

"abends";

}

################################################################################

# Create Header for Redirected RESTORE DATABASE Statement

################################################################################

if ($opt_t eq "RESTORE" ) {

$work = xmpPerl::doCmdOut("db2 get db cfg for $opt_d | grep 'Path to log files'");

($work,$dbcfg_logpath) = split("=",$work);

$dbcfg_logpath = xmpPerl::trim($dbcfg_logpath);

$dbcfg_dftdbpath = xmpPerl::getDBMCfgValue("DFTDBPATH");

print hFile "UPDATE COMMAND OPTIONS USING S ON Z ON $opt_f.out V ON;\n";

print hFile "SET CLIENT ATTACH_NODE 0;\n";

print hFile "SET CLIENT CONNECT_NODE 0;\n";

$work = xmpPerl::getTimestamp();

print hFile "ECHO \@--------------------------------------------------V$VER---\@;\n";

print hFile "ECHO \@Script created ........: $work \@;\n";

print hFile "ECHO \@Script File ...........: $opt_f \@;\n";

print hFile "ECHO \@Database to be restored: $opt_d (Redirected Restore) \@;\n";

print hFile "ECHO \@Database Object Sizing : $opt_o \@;\n";

print hFile "ECHO \@Object Growth Percent .: $opt_g % (0=not expanding) \@;\n";

print hFile "ECHO \@Disclaimer: Check/edit this script to avoid loss of data! \@;\n";

print hFile "----------------------------------------------------------------\n";

print hFile "RESTORE DB $opt_d \n".

" FROM /back-up-path\n".

" TAKEN AT yyyymmddhhmmss\n".

" TO $dbcfg_dftdbpath\n".

" INTO new-db\n".

"-- NEWLOGPATH $dbcfg_logpath\n".

"-- WITH 2 BUFFERS BUFFER 2048\n".

" REDIRECT;\n";

print hFile "----------------------------------------------------------------\n";

print hFile "-- Comments: DO NOT CHANGE THE NAME IN 'RESTORE DB $opt_d'!\n".

"-- DO NOT CHANGE THE NAME IN 'RESTORE DB $opt_d CONTINUE'!\n".

"-- /back-up-path = path or location of back-up file \n".

"-- yyymmddhhmmss = back-up start timestamp, part of backup-file name\n".

"-- /new-db-path = database path, see CONTAINER statement below\n".

"-- new-db = original database name or new target db name\n".

"-- /new-logpath = path to log files, you may alter it afterwards\n".

"-- with UPDATE DB CFG FOR ... USING NEWLOGPATH ...\n";

print hFile "----------------------------------------------------------------\n";

print hFile "-- RESTORE DB $opt_d ABORT;\n";

}

################################################################################

# Create Header for CREATE TABLESPACE Statement

################################################################################

if ($opt_t eq "CREATE" ) {

$work = xmpPerl::getTimestamp();

print hFile "---------------------------------------------------------V$VER---\n";

print hFile "-- DDL created ........: $work\n";

print hFile "-- DDL File ...........: $opt_f\n";

print hFile "-- Database ...........: $opt_d\n";

print hFile "-- Object Sizing ......: $opt_o\n";

print hFile "-- Object Growth % ....: $opt_g %\n";

print hFile "-- Disclaimer: Check/edit this DDL script to avoid loss of data!\n";

print hFile "----------------------------------------------------------------\n";

############################################################################

# DBI Switches

########################################################################

my $switch = DBI->internal;

$switch->debug(0); # 1=normal debug, 2=detailed handle trace

#print "DBI Version and Attributes ..: $switch->{'Attribution'}, $switch->{'Version'}\n";

#print "DBI Drivers .................: ",join(", ",DBI->available_drivers()),"\n";

########################################################################

# Connect to Database

########################################################################

$dbh = DBI->connect("dbi:DB2:$opt_d",

$opt_u,

$opt_p,

{ PrintError => 0, # don't print error by default

RaiseError => 0, # $ts_id{$ts}don't print error and die by default

AutoCommit => 0, # don't commit after each statement

ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types

} )

|| die ">>>$0 Problem mit Connect to $opt_d\n>>>".$DBI::errstr;

}

################################################################################

# Create Header for CREATE TABLESPACE Statement

################################################################################

if ($opt_t eq "AUTOSIZE") {

$work = xmpPerl::getTimestamp();

print hFile "---------------------------------------------------------V$VER---\n";

print hFile "-- DDL created ........: $work\n";

print hFile "-- DDL File ...........: $opt_f\n";

print hFile "-- Database ...........: $opt_d\n";

print hFile "-- Object Sizing ......: $opt_o\n";

print hFile "-- Space Usage Percent : $opt_g %\n";

print hFile "-- Disclaimer: Check/edit this DDL script to avoid loss of data!\n";

print hFile "----------------------------------------------------------------\n";

}

################################################################################

# Request is to generate CREATE or RESTORE statements

################################################################################

if ( ($opt_t eq "CREATE")

or ($opt_t eq "RESTORE") ) {

############################################################################

# Work on basis of tablespace container files

############################################################################

$work = $ts;

for ($ts = 1; $ts <= $work; $ts++) {

$f1 = $ts_id{$ts};

$f2 = $ts_tsc_id{$ts};

$f3 = $ts_name{$ts};

$f4 = $ts_tsc_type{$ts};

$f8 = $ts_tsc_name{$ts};

########################################################################

# Summarize current Page Counts and KBytes: Total and HWM

########################################################################

$ts_total_prev = $ts_total_prev + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );

$ts_total_used = $ts_total_used + ($ts_tsc_ext_used{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );

if ($ts_type{$ts} ne "SMS") { # not SMS managed

$ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_hwm{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}

else { $ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}

#print "TS-ID=$ts_id{$ts} Name=$ts_name{$ts} c-ext-total=$ts_tsc_ext_total{$ts} c-ext-used=$ts_tsc_ext_used{$ts} c-ext-hwm=$ts_tsc_ext_hwm{$ts}\n";

#print "Auto Storage=$ts_autostg{$ts}\n";

########################################################################

# New target by default is the number of currently allocate extents

########################################################################

if ($ts_tsc_id{$ts} == 0) { # 1st container

$new_target = $ts_tsc_ext_total{$ts};

}

########################################################################

# If calculation on base of HWM requested (not valid for SMS)

########################################################################

if (($opt_o eq "HWM") && ($ts_type{$ts} ne "SMS") ) {

$new_target = $ts_tsc_ext_hwm{$ts};

#print "Adjusted HWM, new target=$new_target\n";

}

########################################################################

# Consider percentage of growth

########################################################################

if (($opt_g > 0) && ($ts_type{$ts} ne "SMS") ) {

$new_target = int(($new_target * ((100+$opt_g)/100)) + 0.9999);

#print "Adjusted growth, new target=$new_target\n";

}

########################################################################

# Select tablespace: TRUE or FALSE?

########################################################################

$f_select = 0;

if ( (!defined $opt_s)

or ( (defined $opt_s)

&& ($f3 =~ /(\w+)$opt_s/g ) ) ) { # Name equals

$f_select = 1;

}

if (!defined $opt_ns) {} # Name differs

else { if ($f3 =~ /(\w+)$opt_ns/g ) {

$f_select = 0;}

}

#print "TS=$f3/$f2 selected=$f_select\n";

########################################################################

# Summarize Page Counts and KBytes

########################################################################

if ($f_select) {

$ts_total_new = $ts_total_new + ( $new_target * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );

}

########################################################################

# Create Redirected RESTORE DATABASE Statement, Tablespace Sections

########################################################################

if ( ($opt_t eq "RESTORE" ) && $f_select ) {

####################################################################

# Create Container

####################################################################

if ($ts_tsc_id{$ts} == 0) { # 1st container

################################################################

# Format output lines

################################################################

if ($ts gt 1) { # end-up previous item

if ( $ts_autostg{$ts-1} == 0 ) { # only if previous item

print hFile " );\n"; # is not of type

} # automatic storage

}

print hFile "----------------------------------------------------***\n";

if ($ts_type{$ts} eq "DMS") {

print hFile "-- TS ID $f1 / $f3 - Space managed by Database\n";}

else { print hFile "-- TS ID $f1 / $f3 - Space managed by System\n";}

if ( $ts_type{$ts} eq "DMS" ) {

print hFile "-- Pages Allocated / Used / HWM: ".$ts_pgtotal{$ts}.

" / ".$ts_pgused{$ts}." / ".$ts_pghwm{$ts}."\n";

}

else { print hFile "-- Space Allocated / Used .....: ".$ts_pgtotal{$ts}.

" / ".$ts_pgused{$ts}." pages\n";}

print hFile "-- No. Container / Extent Size : ".$ts_container{$ts}.

" file(s) / ".$ts_extsize{$ts}." pages\n";

print hFile "-------------------------------------------------------\n";

################################################################

# Automatic Storage: Yes, needs no allocation

################################################################

if ( $ts_autostg{$ts} == 1 ) {

print hFile "-- Automatic Storage: $f8\n";

}

else {

print hFile "SET TABLESPACE CONTAINERS FOR $f1 USING (\n";

#print "F4=$f4 C-Type=$ts_tsc_type{$ts}\n";

if ($f4 =~ "FILE") {print hFile " FILE $f8 ".$new_target*$ts_extsize{$ts}." \n";}

if ($f4 eq "PATH") {print hFile " PATH $f8 \n";}

if ($f4 =~ "DISK") {print hFile " DEVICE $f8 ".$new_target*$ts_extsize{$ts}."\n";}

}

else {

################################################################

# Automatic Storage: Yes, needs no allocation

################################################################

if ( $ts_autostg{$ts} == 1 ) {

print "-- Automatic Storage: $f8\n";

}

else {

if ($f4 =~ "FILE") {print hFile " ,FILE $f8 ".$new_target*$ts_extsize{$ts}." \n";}

if ($f4 eq "PATH") {print hFile " ,PATH $f8 \n";}

if ($f4 =~ "DISK") {print hFile " ,DEVICE $f8 ".$new_target*$ts_extsize{$ts}."\n";}

}

########################################################################

# Create CREATE TABLESPACE Statement

########################################################################

if (($opt_t eq "CREATE" ) && $f_select) {

########################################################

# Access to DB2 Catalog to get Bufferpool

########################################################

$stmt = "select b.bpname ".

"from sysibm.systablespaces a, ".

" sysibm.sysbufferpools b ".

"where a.bufferpoolid = b.bufferpoolid ".

"and a.tbspace = '$f3' ".

"and a.tbspaceid = $f1 ";

$sth = $dbh->prepare($stmt);

$sth->execute() || die $dbh->errstr;

if ( $dbh->err ) {

die "$0 abended with $dbh->errstr";

}

$bpool = $sth->fetchrow();

if ( $dbh->err ) {

$bpool = "BPCannotCreate $dbh->errstr";

}

####################################################################

# Create Tablespace

####################################################################

if ($ts_tsc_id{$ts} == 0) { # 1st container

################################################################

# Format output lines

################################################################

print hFile "----------------------------------------------------***\n";

print hFile "-- TS $f1 $f3: ".$ts_pgtotal{$ts}." total pages, HWM ".$ts_pghwm{$ts}." pages\n";

print hFile "-- DROP TABLESPACE $f3;\n";

if ($ts_content{$ts} eq "USRTEMP") {print hFile " CREATE USER TEMPORARY TABLESPACE $f3\n";} # V4.4

if ($ts_content{$ts} eq "SYSTEMP") {print hFile " CREATE TEMPORARY TABLESPACE $f3\n";}

if ($ts_content{$ts} eq "LARGE") {print hFile " CREATE LARGE TABLESPACE $f3\n";} # V4.4

if ($ts_content{$ts} eq "ANY") {print hFile " CREATE REGULAR TABLESPACE $f3\n";}

print hFile " PAGESIZE ".( $ts_pgsize{$ts}/1024) ."K \n";

################################################################

# SMS or DMS (includes Automatic Storage

################################################################

if ($ts_type{$ts} eq "DMS") {

if ( $ts_autostg{$ts} == 1 ) {

print hFile " MANAGED BY DATABASE\n";

}

else {

print hFile " MANAGED BY DATABASE\n";

}

else {

print hFile " MANAGED BY SYSTEM\n";

}

if ( $ts_autostg{$ts} == 0 ) {

if ($f4 =~ "FILE") {print hFile " USING (FILE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}

if ($f4 eq "PATH") {print hFile " USING ('$f8' \n";}

if ($f4 =~ "DISK") {print hFile " USING (DEVICE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}

}

else {

if ( $ts_autostg{$ts} == 0 ) {

if ($f4 =~ "FILE") {print hFile " ,FILE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}

if ($f4 eq "PATH") {print hFile " ,'$f8' \n";}

if ($f4 =~ "DISK") {print hFile " ,DEVICE '$f8' ".$new_target*$ts_extsize{$ts}."\n";}

}

####################################################################

# Last Container? Add additional attributes at end of current cmd

####################################################################

if ($ts_tsc_id{$ts}+1 == $ts_container{$ts}) {

################################################################

# Prefetch Size

################################################################

if ($ts_pfsize{$ts} > 0) {

print hFile " ) EXTENTSIZE ".$ts_extsize{$ts}." PREFETCHSIZE ".$ts_pfsize{$ts}."\n";}

if ($ts_pfsize{$ts} == 0) {

print hFile " ) EXTENTSIZE ".$ts_extsize{$ts}."\n";}

if ($ts_pfsize{$ts} == -1) {

print hFile " ) EXTENTSIZE ".$ts_extsize{$ts}." PREFETCHSIZE AUTOMATIC\n";}

################################################################

# Autoresize

################################################################

$autoresize = GetAutoresizeOptions($f3,$f1);

if ($autoresize ne "") {

print hFile " ".$autoresize."\n";

}

################################################################

# Bufferpool

################################################################

print hFile " BUFFERPOOL $bpool\n";

################################################################

# File System Caching V4.4.2

################################################################

if ($ts_fscache{$ts} == 1) {

print hFile " NO FILE SYSTEM CACHING\n";

}

print hFile ";\n";

}

$ts--;

}

################################################################################

# Request is to AUTOSIZE (right size) tablespace allocation in pages

################################################################################

if ($opt_t eq "AUTOSIZE") {

############################################################################

# Work on basis of tablespaces

############################################################################

$work = $ts;

for ($ts = 1; $ts <= $work; $ts++) {

$f1 = $ts_id{$ts}; # Tablespace Structure

$f2 = $ts_name{$ts};

$f4 = $ts_content{$ts}; # Content Type

$f6 = $ts_pgtotal{$ts};

$f7 = $ts_pguseable{$ts};

$f8 = $ts_pgused{$ts};

$f9 = $ts_pgfree{$ts};

$f10 = $ts_pghwm{$ts}; # High Water Mark

$f11 = $ts_pgsize{$ts}; # Page Size

$f12 = $ts_extsize{$ts}; # Extent Size

$f13 = $ts_pfsize{$ts}; # Prefetch Size

$f14 = $ts_container{$ts}; # no. of containers

$f21 = $ts_pgusage{$ts}; # Tablespace Usage

$f26 = $ts_autostg{$ts}; # Autoamtic Storage Indicator

########################################################################

# Summarize current Page Counts and KBytes: Total and HWM

########################################################################

$ts_total_prev = $ts_total_prev + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );

$ts_total_used = $ts_total_used + ($ts_tsc_ext_used{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );

if ($ts_type{$ts} ne "SMS") { # not SMS managed

$ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_hwm{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}

else { $ts_total_hwm = $ts_total_hwm + ($ts_tsc_ext_total{$ts} * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );}

#print "TS-ID=$ts_id{$ts} Name=$ts_name{$ts} c-ext-total=$ts_tsc_ext_total{$ts} c-ext-used=$ts_tsc_ext_used{$ts} c-ext-hwm=$ts_tsc_ext_hwm{$ts}\n";

########################################################################

# New target by default is the number of currently allocate extents

########################################################################

if ($ts_tsc_id{$ts} == 0) { # 1st container

$new_target = $ts_tsc_ext_total{$ts};

}

########################################################################

# Select tablespace: TRUE or FALSE?

########################################################################

$f_changed = 0;

$f_select = 0;

if ( ( (!defined $opt_s)

&& ($ts_tsc_id{$ts} == 0) # 1st container

)

or ( (defined $opt_s)

&& ($ts_tsc_id{$ts} == 0) # 1st container

&& ($f2 =~ /(\w+)$opt_s/g ) )

) { # Name equals

$f_select = 1;

}

if (!defined $opt_ns) {}

else {

if ( $f2 =~ /(\w+)$opt_ns/g ) {

$f_select = 0;

}

########################################################################

# Tablespace is of type "DMS" and qualified for processing

########################################################################

#print "TS=$f2 TSC-ID=$ts_tsc_id{$ts} Usage=$f21 type=$ts_type{$ts} ctype=$ts_tsc_type{$ts} selected=$f_select\n";

if ( ($ts_type{$ts} ne "SMS")

&& ($ts_tsc_type{$ts} =~ "FILE")

&& ($f_select) ) {

################################################################

# If calculation on base of HWM requested (not valid for SMS)

################################################################

if ( ($opt_o eq "HWM")

&& ($ts_tsc_ext_hwm{$ts} != $ts_tsc_ext_total{$ts}) ) {

$new_target = $ts_tsc_ext_hwm{$ts};

$f_changed = 1;

#print "Adjusted HWM, new target=$new_target\n";

}

################################################################

# Consider percentage of growth for HWM

################################################################

if ( ($opt_g > 0)

&& ($opt_o eq "HWM")

&& ($ts_type{$ts} ne "SMS") ) {

$new_target = int(($new_target * ((100+$opt_g)/100)) + 0.9999);

$f_changed = 1;

#print "Adjusted HWM growth, new target=$new_target\n";

}

################################################################

# Consider percentage of usage

################################################################

if ( ($opt_g > 0)

&& ($opt_o ne "HWM")

&& ($ts_type{$ts} ne "SMS") ) {

$new_target = int(($ts_tsc_ext_used{$ts} / ($opt_g/100)) + 0.9999);

if ($new_target < $ts_tsc_ext_hwm{$ts}) {

$new_target = $ts_tsc_ext_hwm{$ts};

}

$f_changed = 1;

#print "Adjusted usage, new target=$new_target\n";

}

########################################################

# Calculated space equals current space = do nothing

########################################################

if ($new_target == $ts_tsc_ext_total{$ts}) {

$f_changed = 0;

}

########################################################################

# Tablespace size changed?

########################################################################

if ($f_changed) {

####################################################################

# Write information on console and to file

####################################################################

print "TS $f1 $f2: Pages Total=$f6, Useable=$f7, Used=$f8\n".

" HWM=$f10, Free=$f9, Containers=$f14, Ext.Size=$f12, PageSize=$f11\n";

print hFile "----------------------------------------------------***\n";

print hFile "-- TS $f1 $f2: Pages Total=$f6, Useable=$f7, Used=$f8\n".

"-- HWM=$f10, Free=$f9, Containers=$f14, Ext.Size=$f12, PageSize=$f11\n";

print hFile "----------------------------------------------------***\n";

####################################################################

# Write information on console and to file

####################################################################

print " -> Usage $f21 %, adjust to new TS target of ".

($new_target*$ts_extsize{$ts}*$ts_container{$ts})." pages\n";

print hFile "-- -> Usage $f21 %, adjust to new TS target of ".

($new_target*$ts_extsize{$ts}*$ts_container{$ts})." pages\n";

if ( $f26 == 1 ) { # Automatic Storage

#print " -> automtic storage, alteration not necessary\n";

#print hFile "-- -> automtic storage, alteration not necessary\n";

if ($ts_pgtotal{$ts}-($new_target*$ts_extsize{$ts}) >0) {

print " -> lower HWM and reduce by ".

($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts})." pages\n";

print hFile "-- -> lower HWM and reduce by ".

($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts})." pages\n";

}

else {

#print " -> cannot extend automatic storage tablespace by ".

# (($new_target*$ts_extsize{$ts})-$ts_pgtotal{$ts})." pages\n";

#print hFile "-- -> cannot extend automatic storage tablespace by ".

# (($new_target*$ts_extsize{$ts})-$ts_pgtotal{$ts})." pages\n";

}

else {

print " -> resizing $f14 containers each with ".

$new_target*$ts_extsize{$ts}." pages\n";

print hFile "-- -> resizing $f14 containers each with ".

$new_target*$ts_extsize{$ts}." pages\n";

}

####################################################################

# Write ALTER statement on console and to file

####################################################################

if ( $f26 == 1 ) { # Automatic Storage

if ($ts_pgtotal{$ts}-($new_target*$ts_extsize{$ts}) >0) {

print hFile " ALTER TABLESPACE $f2 LOWER HIGH WATER MARK;\n";

print hFile "! sleep ".(10+int(($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts})/1000)).";\n";

print hFile " ALTER TABLESPACE $f2\n";

print hFile " REDUCE ".

($ts_pgtotal{$ts}-$new_target*$ts_extsize{$ts}).";\n";

}

else { $new_target = $ts_pgtotal{$ts}/$ts_extsize{$ts}; }

}

else {

print hFile " ALTER TABLESPACE $f2\n";

print hFile " RESIZE (ALL CONTAINERS ".

$new_target*$ts_extsize{$ts}.");\n";

}

########################################################################

# Summarize new space consumpion

########################################################################

$ts_total_new = $ts_total_new + ( $new_target * $ts_extsize{$ts} * $ts_pgsize{$ts} / 1024 );

#print "Total new=$ts_total_new\n";

}

$ts--;

}

################################################################################

# Create Footer for Redirected RESTORE DATABASE Statement

################################################################################

if ($opt_t eq "RESTORE" ) {

print hFile " );\n";

print hFile "----------------------------------------------------***\n";

print hFile "ECHO \@Restored Database Size : $ts_total_new KBytes \@;\n";

print hFile "----------------------------------------------------***\n";

print hFile "RESTORE DB $opt_d CONTINUE;\n";

print hFile "-- ROLLFORWARD DATABASE $opt_d TO END OF LOGS;\n";

print hFile "-- ROLLFORWARD DATABASE $opt_d STOP;\n";

print hFile "----------------------------------------------------------------\n";

print hFile "-- Comments: Use ROLLFORWARD TO END OF LOGS, if log files will\n".

"-- contain important information which will bring the\n".

"-- database to latest consistent state. Use STOP to\n".

"-- ignore log files at all. Do not use CANCEL keyword!\n";

print hFile "----------------------------------------------------------------\n";

}

################################################################################

# Create Footer for CREATE Statement

################################################################################

if ($opt_t eq "CREATE" ) {

print hFile "-- \n";

print hFile "-- Database Total Size : $ts_total_new KBytes\n";

print hFile "-- ***END***\n";

########################################################################

# Close Statement, Disconnect from Database

########################################################################

$sth->finish() || die $dbh->errstr;

$dbh->commit || die $dbh->errstr;

$dbh->disconnect || die $dbh->errstr;

}

################################################################################

# Close File and set File Access Mode

################################################################################

close(hFile);

chmod 0775,$opt_f;

print "Current Total allocated .....: $ts_total_prev kBytes\n";

print "Current Space used ..........: $ts_total_used kBytes\n";

print "High Water Mark set at ......: $ts_total_hwm kBytes\n";

print "(New) Space to be allocated : $ts_total_new kBytes\n";

print "Tablespaces $opt_t File successfully created for $opt_d!\n";

print "***Ende***\n";

exit 0;

################################################################################

# SUBROUTINES AND FUNCTIONS ---------------------------------------------------#

################################################################################

# GetAutoresizeOptions: Provides new TABLESPACE AUTORESIZE options V4.1

# Comment: With DB2 V8.2 data only avaible in snapshots!

################################################################################

sub GetAutoresizeOptions {

my $autoresize = "";

my $ts;

my $tsid;

my $autoresize_enabled;

my $max_size;

my $increase_size;

my $increase_percent;

($ts,$tsid) = @_;

########################################################################

# Build and execute statement to determine AUTORESIZE option

########################################################################

$stmt = "SELECT COALESCE(TBSP.TBSP_AUTO_RESIZE_ENABLED,0), ".

" COALESCE(TBSPP.TBSP_MAX_SIZE,0), ".

" COALESCE(TBSPP.TBSP_INCREASE_SIZE,0), ".

" COALESCE(TBSPP.TBSP_INCREASE_SIZE_PERCENT,0) ".

"FROM TABLE(SNAP_GET_TBSP('',-1)) AS TBSP, ".

" TABLE(SNAP_GET_TBSP_PART('',-1)) AS TBSPP ".

"WHERE TBSP.TBSP_NAME = '$ts' ".

"AND TBSP.TBSP_ID = $tsid ".

"AND TBSP.TBSP_NAME = TBSPP.TBSP_NAME ".

"AND TBSP.TBSP_ID = TBSPP.TBSP_ID ";

#print "$stmt\n";

if ( ! ($sth = $dbh->prepare($stmt)) ) {

print "Problem with SQL statement '$stmt' (a):\n";

print "SQLCODE=".$dbh->err."\n";

print $dbh->errstr."\n";

die ">>>$0 Needs clarification!";

}

if ( ! ($sth->execute()) ) {

print "Problem with SQL statement '$stmt' (b):\n";

print "SQLCODE=".$dbh->err."\n";

print $dbh->errstr."\n";

die ">>>$0 Needs clarification!";

}

if ( ! (($autoresize_enabled,

$max_size,

$increase_size,

$increase_percent) = $sth->fetchrow()) ) {

if (!defined $autoresize_enabled) {

$autoresize_enabled = 0; }

else {print "Problem with SQL statement '$stmt' (c):\n";

print "SQLCODE=".$dbh->err."\n";

print $dbh->errstr."\n";

die ">>>$0 Needs clarification!";

}

#########################################################################

# Close Statement

#########################################################################

$sth->finish() || die $dbh->errstr;

$dbh->commit || die $dbh->errstr;

#print "$autoresize_enabled $max_size $increase_size $increase_percent\n";

#########################################################################

# Determine AUTORESIZE options

#########################################################################

if ($autoresize_enabled == 1) {

$autoresize = "AUTORESIZE YES";

#################################################################

# Maximum Size MAXSIZE specified?

#################################################################

if ($max_size == -1) {$autoresize = $autoresize." MAXSIZE NONE"}

if ($max_size > 0) {$autoresize = $autoresize." MAXSIZE ".$max_size." K"}

#################################################################

# Increase Size or Percent INCREASESIZE specified?

#################################################################

if ($increase_size > 0) {

$autoresize .= " INCREASESIZE ".$increase_size." K"}

if ($increase_percent > 0) {

$autoresize .= " INCREASESIZE ".$increase_percent." PERCENT"}

}

#print "AUTORESIZE string=$autoresize\n";

return $autoresize;

}

#################################################################################

# SaySyntax

#################################################################################

sub SaySyntax {

die "Usage: [perl] $0 -d db [-u parm -p parm] -f file [-t {RESTORE|CREATE|AUTOSIZE} -o {CURRENT|HWM}] [-g nn] [-s pattern] [-ns pattern]\n".

" -d database \n".

" -u User (opt)\n".

" -p Password (opt)\n".

" -f file for statement generation\n".

" -t type of statement to generate (opt).\n".

" -o current allocation or HWM (High Water Mark) (opt.)\n".

" -g fill-meter (AUTOSIZE target usage %) \n".

" or growth (additional uplift % on HWM) (opt.)\n".

" -s tablespace name pattern (opt.)\n".

" -ns single tablespace name exclude pattern (opt.)\n".

" Using patterns, examples and usage comments:\n".

" -ns patt Do not use replace chars like '*' or '%'\n".

" -s 'patt1|patt2' Imbed multiple patterns in (double) quotes\n".

" -ns patt1 -s patt2 Paired patterns work like OR condition\n".

"abends.\n".

"See also script xmpTBSCList.pl for container files and tablespace options-";

}

Perl Script xmpTerm.pl to list or terminate DB2 connections

xmpTerm.pl provides following functions …

lists all databases (local and remote) at current DB2 instance
lists all connections at the DB2 instance, or only those connected to a specified database
terminates all connections, or only those connected to a specified database, or a specific thread id

Options supported:

-l: (deflt. option) display all threads
-d {dbname|*}: display threads on distinct or all databases
-t {id|*}: terminate distinct thread id or all threads (on database or instance)
-a LOCKS: display locks

#!/usr/local/bin/perl -w
################################################################################
# xmpTerm.pl - Display or Terminate DB2 Application Threads
# Called by - Command Level or DBA scripts
# Calls     - xmpPerl.pm   - xmp DBA Perl Package
#             Getopt       - Argument Administration
#             Env          - Access to environment variables
#             English      - aliases for perl variables and identifiers
# Optionen  - xmpTerm.pl   -d db|*   Display threads on distinct database
#			   			   -l        (deflt.) Display  all threads
#                          -t id|*   Terminate all or distinct Thread(s)
#                          -a action [LOCKS]
#-------------------------------------------------------------------------------
# Autor     - GR /05.01.00 - V1.0 first version
# Changes   - GR /23.08.00 - V1.1 terminate * now results in DEACTIVATE DB
#             GR /11.09.00 - V1.2 tiny amendment
#             GR /26.01.01 - V1.3 try to terminate threads only of local databases,
#                                 changes in comments
#             GR /17.07.01 - V1.4 adapted to DB2 V7.1, new xmpPerl.pm
#             GR /11.12.01 - V1.5 Logging bei -t 
#             GR /13.01.03 - V2.0 Retry while deactivating databases, no "die" 
#                                 (rc=1) when system w/o databases (SQL1031N)
#             GR /29.01.03 - V2.1 Correction on loops/retries for deactivation
#             GR /29.01.03 - V3.0 Provide more information
#             GR /30.07.03 - V3.1 Provide more information after FORCE looped 3 times
#             GR /12.08.03 - V4.0 New option -a LOCKS
#             GR /13.01.04 - V4.1 adapted to DB2 V8.1, new xmpPerl.pm 
#             GR /18.06.04 - V4.2 Show long table and tablespace names
#             GR /28.12.06 - V4.3 Amendments for DB2 Version 9
#             GR /14.07.11 - V4.4 Amendments for DB2 Version 9.7
#                            (Infomation: db2 list applications show detail does not provide
#                             the correct operating system process id (pid).)
#             GR /21.09.11 - V4.4.1 Show also system components
#-------------------------------------------------------------------------------
# Comments  - PERL5LIB environment variable must point to perlauf perl modules!
#           - Check correctness for every software (db2, perl) release change!
#           - Do not use * ! Use '*' instead to qualifiy -d database name or -t
#             thread id. 
################################################################################

################################################################################
# Modules 
################################################################################
use strict;
use Getopt::Std;
use vars qw($opt_d $opt_l $opt_t $opt_a);
use xmpPerl;
use Env;
use English;

################################################################################
# Declare/Init 
################################################################################
my $VER		= "4.4.1";

my $work    = "";						# for TS List
my @work;								# for TS List
my $work2   = "";						# for Container List
my @work2;								# for Container List

my $var;
my $val;
my $f_found;							# Flag: found
my $loop	= 0;						# retry counter   V2.1

my $db		= 0;						# no. of Databases
my %dbname	= ();						# Databases
my %dbtype	= ();						# Entry Type

my $t		= 0;						# no. of Threads
my %t_authid    = (); my $f1="";		# Auth ID
my %t_applname  = (); my $f2="";		# Application Name
my %t_id		= (); my $f3="";		# Thread ID
my %t_applid	= (); my $f4;			# Application ID
my %t_seq       = (); my $f5;			# Sequence
my %t_agentno   = (); my $f6;			# Number of Agents
my %t_node 		= (); my $f7;			# Coordinator Node
my %t_pid		= (); my $f8;			# Coordinator PID
my %t_status	= (); my $f9;			# Status
my %t_statchgtm	= (); my $fa;			# Status Change Time
my %t_dbname	= (); my $fb;			# Database Name
my %t_path	= (); my $fc;				# DB Path
my $DB2Version  = xmpPerl::getDB2Version;		# akt. DB2 Version
my $OS 		= ucfirst $^O;				# operating system

my %t_pcpu      = (); my $x1;			# CPU usage percentage
my %t_pmem      = (); my $x2;			# Memory usage percentage
my %t_args      = (); my $x3;			# Command and arguments
my %t_time      = (); my $x4;			# execution time
my %t_etime     = (); my $x5;			# start time
my %t_stat		= (); my $x6;			# process status

my %t_L_nolocks	= ();   				# Number of locks
my %t_L_objtype = (); my $l1;			# Lock Object Type
my %t_L_ts	= (); my $l2;				# Tablespace
my %t_L_table	= (); my $l3;			# Schema and Name
my %t_L_mode    = (); my $l4;			# Lock Mode
my %t_L_status	= (); my $l5;			# Lock Status
my %t_L_escal	= (); my $l6;			# Lock Escalation
my %t_L_objid   = (); my $l7;			# Lock Object ID

################################################################################
# Ausgabe-Formate
################################################################################
format KOPF =
--------------------------------------------------------------------------------
------------------------ LIST OF DB2 THREADS -----------------------------------
DBNAME--------------------------------------------------------------------------
.

#@<<<<<<<*ID.....: @<<<<<<< Path..: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
#$fb,              $f3,             $fc,
#         PID ...: @<<<<<<< Node..: @>>>> Seq-No: @>>>   Agents: @<<<<<<<<
#                  $f8,             $f7,          $f5,           $f6,
#         Auth-ID: @<<<<<<< Applic: @<<<<<<<<<<<<<<<<<<< Status: @<<<<<<<<<<<<<<<
#		  $f1,             $f2,                         $f9,
#	 Appl-ID: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Status Change: @<<<<<<<<<<<<<<<
#                  $f4,                                          $fa,
#         CPU%...: @<<<<<<< Mem% .: @<<<< Cmd ..: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
#                  $x1,             $x2,          $x3
#	 Time ..: @<<<<<<< Start : @<<<<<<<<<<<<<       Status: @<<<<<
#		  $x4,             $x5,                         $x6

format ZEILE =
@<<<<<<<*ID.....: @<<<<<<< Path....: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$fb,              $f3,               $fc,
         Auth-ID: @<<<<<<< Appl-ID.: @<<<<<<<<<<<<<<<<<<<<<<<<< Node..: @<<<<<<<
                  $f1,               $f4,                               $f7,
         Agents.: @<<<<<<< Applicat: @<<<<<<<<<<<<<<<<<<<<<<<<< Seq-No: @<<<<<<<
		  $f6,               $f2,                                $f5,
         Status.: @<<<<<<<<<<<<<<<<< Change: @<<<<<<<<<<<<<<<<< Active: A or S
                  $f9,                       $fa,
         PID....: @<<<<<<< Command.: @<<<<<<<<<<<<<<<<<<<<<<<<< Status: @<<<<<<<
                  $f8,               $x3,                                $x6,
         CPU %..: @<<<<<<< Memory %: @<<<<   Elapsed: @<<<<<<<< Time..: @<<<<<<<
                  $x1,               $x2,             $x5,              $x4
.

format LOCKSKOPF = 
         -------- List of Locks for current Agent ------------------------------
.

#        *Lock...: @<<<<<<< Table...: @<<<<<<<<<<<<<<<<<<<<<<<<< TSpace: @<<<<<<<
#                  $l1,               $l3,                               $l2,
#         Obj-ID.  @<<<<<<< Status..: @<<<<<< Escalat: @<<<<<<<  Mode..: @<<<<<<<
#		  $l7,               $l4,             $l6,              $l5

format LOCKSZEILE =
        *Lock...: @<<<<<<< Obj-ID..: @<<<<<<<<<<<<<<< Mode....: @<<<<<<<<<<<<<<<
                  $l1,               $l7,             $l5,
	 Table..: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< TSpace..: @<<<<<<<<<<<<<<<
                  $l3,                                        $l2,
         Status.: @<<<<<< Escalat'n: @<<<<<<
		  $l4,               $l6
.

################################################################################
# Argumente/Options prüfen 
################################################################################
getopts("d:lt:a:");

if (( defined $ARGV[0] ) and			# needs help?		    V2.1
   (( $ARGV[0] eq "?") or
     ( $ARGV[0] eq "-h")))	{
        SaySyntax();
}

if (! defined $opt_d )		{			# Datenbank
	$opt_d = "*";
}

if ((! defined $opt_l )	and				# List Option
    (! defined $opt_t )	   )	{		# Term Option
	$opt_l = "";					# List Option ist Default
}

if ((defined $opt_t )   and 			# Terminate requested
    ($opt_t ne "*")     and				# kein * drin
    ($opt_t !~ /[1234567890]/)  ) {		# nicht numerisch
	print "$0 - specify -t '*'|nn (all threads or specific thread)\n";
        SaySyntax();
}

if (defined $opt_a )	{				# ACTION option		V4.0
   if (  ($opt_a !~ /[LlOoCcKkSs]/)   	# not LOCKS
      or (length($opt_a) != 5)	)   {	# not 5 bytes		V4.3
	print "$0 - specify option -a LOCKS\n";	#				V4.0
        SaySyntax();					#					V4.0
   }									#					V4.0
   else {$opt_a = uc $opt_a;}			# uppercase option 	V4.0
}										#					V4.0

$opt_d = uc $opt_d;						# uppercase database

################################################################################
# Check DB2 existence, write information on DB2 
################################################################################
xmpPerl::chkDB2();
xmpPerl::prtHeaderDB2("$0 - List/Terminate DB2 Threads - Version $VER");

print "Database ....................: $opt_d\n";
if (defined $opt_t) {
	print "Terminate Threads ...........: $opt_t\n";
}

################################################################################
# Check DB2 Instance Database Directory                                     V1.6
################################################################################
$work = xmpPerl::doCmdOut("db2 list db directory");
@work = split("\n",$work);
if ($work =~ "SQL1031N") {
	print ">>> DB2 Instance has no database directory (e.g. like DB2 Admin Server). RC=0\n";
print "***Ende***\n";
	exit 0;
}

################################################################################
# DB2 List Database Directory wg. Name
################################################################################
#$work = xmpPerl::doCmdOut("db2 list db directory | grep -i 'database alias' | sort");
$work = xmpPerl::doCmdOut("db2 list db directory | egrep -i 'database alias|entry type'");
undef $/;
@work = split("\n",$work);
#print "$#work lines returned from DB2 List db dir Command.\n";

if (  ($work =~ "not found")
   or ($work =~ "nicht gefunden") ) {
	die ">>> DB2 Commands (z.B. list db dir) nicht ausführbar!\n".
		">>> Meldung: $work\n".
		">>> ABEND ";
}

print "Databases im Directory ......: ";
foreach $work (@work) {
	($var,$val) = split(" = ",$work);
	if ($var =~ "alias") {
		$db++;
		$dbname{$db} = xmpPerl::trim($val);	# Datenbank in Stack legen
#		print $dbname{$db}." ";
	}
	if ($var =~ "entry type") {
		$dbtype{$db} = lc xmpPerl::trim($val);	# DB Type in Stack legen
		print $dbname{$db}."(".$dbtype{$db}.") ";
	}

}
print "\n";

if ($db == 0) {
	print ">>> This DB2 instance does not contain databases! RC=0\n".
	print "***End***\n";
	exit 0;
}

################################################################################
# Wenn Database-Name als Argument angegeben, Namen prüfen
################################################################################
if ($opt_d ne "*") {
	$work 	 = $db;
	$f_found = 0;
	for ($db = 1; $db <= $work; $db++) {
		if ($dbname{$db} eq $opt_d) {
			$f_found = 1;
		}
	}
	if ($f_found==0) {
		die ">>> Database $opt_d does not exist at current DB2 instance!\n".
		">>> ABEND ";
	}
}

################################################################################
# Display Threads
################################################################################
if (defined $opt_l) {
	GetThreads();
	print "Current Threads at Instance  : $t\n";
	ReportThreads();
}

################################################################################
# Terminate Threads
################################################################################
if (defined $opt_t) {
	############################################################################
	# Write informational message to log file 
	############################################################################
        xmpPerl::LogLine("DBA I ".$0." $< $opt_d $opt_t");
	############################################################################
	# Current Threads
	############################################################################
	GetThreads();
	############################################################################
	# Alle Threads abarbeiten
	############################################################################
	$work = $t;
	for ($t = 1; $t <= $work; $t++) {
		########################################################################
		# Bestimmter Thread oder alle Threads in allen DB's oder bestimmter DB
		########################################################################
		#print "XXX ".$t_id{$t}." ".$t_dbname{$t}." XXXX\n";
		if (	(  ($opt_t eq "*") 
		   	or ($t_id{$t} == $opt_t) )
		   and  (  ($opt_d eq "*")
			or ($t_dbname{$t} eq $opt_d ) ) )	{	
			ForceThread($t_id{$t});
		}
	}

	############################################################################
	# Wenn alle Threads forced (alle überhaupt oder einer DB)
	############################################################################
	if ($opt_t eq "*")	{
		DeactivateDatabase();
	}

	############################################################################
	# Buffer Flush
	############################################################################
	$work = xmpPerl::doCmdOut("db2 terminate");	
	#print "AAAAA $work\n";
}

################################################################################
# Display Threads erneut nach Terminate Threads
################################################################################
if ((defined $opt_l) and
    (defined $opt_t)	) {
	GetThreads();
	ReportThreads();
}

print "***Ende***\n";

exit 0;

################################################################################
########################################################## Sub Routines ########
# SaySyntax ####################################################################
sub SaySyntax {
	die "Usage: [perl] $0 -d [db|'*'] [-l] [-t id|*] [-a {LOCKS}]\n".
		"		-d db|'*' 	database name\n".
		"		-l      	display thread(s) (default option)\n".
		"		-t id|'*'	terminate thread(s)\n".
		"		-a LOCKS	display lock information\n".
		"abends";
}

################################################################################
# Alle oder nur best. DB-Threads ermitteln und Daten intern speichern
################################################################################
sub GetThreads {
   my $work;
   my $work2;
   my $startpos = 9;					# Dflt. Start

	$t = 0;

	############################################################################
    # Starting position in DB2 command output is version-dependent!
	############################################################################
	if ($DB2Version =~ "5") {$startpos=9}		# Version 6  17.07.2001		
	if ($DB2Version =~ "6") {$startpos=9}		# Version 6  17.07.2001		
	if ($DB2Version =~ "7") {$startpos=31}		# Version 7  17.07.2001
	if ($DB2Version =~ "8") {$startpos=31}		# Version 8  13.01.2004
	if ($DB2Version =~ "9") {$startpos=31}		# Version 9  28.12.2006	
	if ($DB2Version =~ "9.7") {$startpos=129}	# Version 9.7  14.07.2011

	############################################################################
    # DB2 List Applications show detail
	############################################################################
	$work = xmpPerl::doCmdOut("db2 list applications show detail");
	undef $/;
	@work = split("\n",$work);
	#print "$#work lines returned from DB2 List Applications Command.\n";

	if ($#work == 0) {return}

	$f_found = 0;

	foreach $work (@work) {

		#($f1,$f2,$f3,$f4,$f5,$f6,$f7,$f7,$f9,$fa,$fb,$fc) = split(" ",$work);
		if (length($work) > 0) {
			####################################################################
			# Diese folgenden Bedingungen sind Versions-abhängig!    
			####################################################################
			$f1 = substr($work,0,8);					# Authid
			$f2 = substr($work,$startpos,20);			# Appl. Name
			$f3 = substr($work,$startpos+21,10);		# Appl. Handle
			$f4 = substr($work,$startpos+32,30);		# Appl. ID
			$f5 = substr($work,$startpos+63,4);			# Seq. No
			$f6 = substr($work,$startpos+68,10);		# No Agents.
			if ($DB2Version =~ "9")		{				# 28.12.2006
				$f4 = substr($work,$startpos+32,62);	# Appl. ID			
				$f5 = substr($work,$startpos+95,5);		# Seq. No
				$f6 = substr($work,$startpos+101,10);	# No Agents.
				$f7 = substr($work,$startpos+112,16);	# Coord. Node
				$f8 = substr($work,$startpos+129,15);	# Coord PID/Thread
				if (length($work) >= $startpos+145) {
					$f9 = substr($work,$startpos+145,30)}	# Status
				else {	$f9 = "n/a" }
				if (length($work) >= $startpos+176) {			
					$fa = substr($work,$startpos+176,26)}	# Status Change	
				else {	$fa = "n/a" }
				if (length($work) >= $startpos+203) {
					$fb = substr($work,$startpos+203,8)}	# DBNAME
				else {	$fb = "n/a" }
				if (length($work) >= $startpos+212) {
					$fc = substr($work,$startpos+212)}	# DB Path
				else {	$fc = "n/a" }
			}
			elsif ($DB2Version =~ "8")		{			# 13.01.2004
				$f7 = substr($work,$startpos+79,16);	# Coord. Node
				$f8 = substr($work,$startpos+92,15);	# Coord PID/Thread
				if (length($work) >= $startpos+112) {
					$f9 = substr($work,$startpos+112,30)}	# Status
				else {	$f9 = "n/a" }
				if (length($work) >= $startpos+143) {			
					$fa = substr($work,$startpos+143,26)}	# Status Change	
				else {	$fa = "n/a" }
				if (length($work) >= $startpos+170) {
					$fb = substr($work,$startpos+170,8)}	# DBNAME
				else {	$fb = "n/a" }
				if (length($work) >= $startpos+179) {
					$fc = substr($work,$startpos+179)}	# DB Path
				else {	$fc = "n/a" }
			}			
			else 	{
				$f7 = substr($work,$startpos+79,12);	# Coord. Node
				$f8 = substr($work,$startpos+92,15);	# Coord PID/Thread
				if (length($work) >= $startpos+108) {
					$f9 = substr($work,$startpos+108,30)}	# Status
				else {	$f9 = "n/a" }
				if (length($work) >= $startpos+139) {			
					$fa = substr($work,$startpos+139,26)}	# Status Change
				else {	$fa = "n/a" }
				if (length($work) >= $startpos+166) {
					$fb = substr($work,$startpos+166,8)}	# DBNAME
				else {	$fb = "n/a" }
				if (length($work) >= $startpos+175) {
					$fc = substr($work,$startpos+175)}	# DB Path
				else {	$fc = "n/a" }
			}
		}
		#print length($work)."$f1 $f2 $f3\n";
		#print "$f7 # $f8 # $f9 # $fa # $fb # $fc #\n";

		########################################################################
		# Wenn Ausgabedaten lokalisiert werden konnten
		########################################################################
		#	&& ($f5 ne "00001") )	{		# no systems components		V4.4.1
		if ($f_found) {
			#print "eingetragen\n";
			$t++;
			$t_authid{$t} 	= $f1;			# Auth ID
			$t_applname{$t} = $f2;			# Application Name
			$t_id{$t} 	= xmpPerl::trim($f3);	# Thread ID
			$t_applid{$t} 	= $f4;			# Application ID
			$t_seq{$t} 	= $f5;				# Sequence
			$t_agentno{$t}	= $f6;			# Number of Agents
			$t_node{$t} 	= $f7;			# Coordinator Node
			$t_pid{$t} 	= xmpPerl::trim($f8);	# Coordinator PID
			$t_status{$t} 	= $f9;			# Status
			$t_statchgtm{$t}= $fa;			# Status Change Time
			$t_dbname{$t} 	= xmpPerl::trim($fb);	# Database Name
			$t_path{$t} 	= $fc;			# DB Path

			####################################################################
			# retrieve operating system information   
			####################################################################
			$t_pcpu{$t}	= getPSval($t_pid{$t},'pcpu');
			$t_pmem{$t}	= getPSval($t_pid{$t},'pmem');
			$t_args{$t}	= getPSval($t_pid{$t},'comm');
			$work2		= getPSval($t_pid{$t},'args');
			if ($t_args{$t} ne $work2) {$t_args{$t} .= " ".$work2;}
			$t_time{$t}	= getPSval($t_pid{$t},'time');
			$t_etime{$t}	= getPSval($t_pid{$t},'etime');	
			$t_stat{$t}	= "";
			if ($OS eq "Aix")     { $t_stat{$t}=getPSval($t_pid{$t},'stat'); }
			if ($OS eq "Solaris") { $t_stat{$t}=getPSval($t_pid{$t},'s'); }

			####################################################################
			# retrieve lock information if requested	V4.0
			####################################################################
			if (  (defined $opt_a)
			   && ($opt_a eq "LOCKS") )	{
				getLocks($t_id{$t});
			}
		}

		########################################################################
		# Jetzt gehts mit echten Daten los
		########################################################################
		if ($f1 eq "--------") {
			$f_found = 1;
		}
	}		
}

################################################################################
# Write formated list of threads
################################################################################
sub ReportThreads {
   my $work;
   my $work2;

	$FORMAT_NAME 		= "ZEILE"; 			# $~
	$FORMAT_TOP_NAME 	= "KOPF";			# $^
	$FORMAT_FORMFEED	= "";				# $^L 
	$FORMAT_LINES_PER_PAGE	= ($t*8)+3;		# $=, plus Lines für Kopf
	$FORMAT_LINES_LEFT      = 0;			# vorhergeh. Format

	$work = $t;
	for ($t = 1; $t<=$work ; $t++) {
		$FORMAT_NAME 		= "ZEILE"; 		# $~
		$FORMAT_TOP_NAME 	= "KOPF";		# $^

		$f1 =	$t_authid{$t};				# Auth ID
		$f2 =	$t_applname{$t};			# Application Name
		$f3 =	$t_id{$t};					# Thread ID
		$f4 =	$t_applid{$t};				# Application ID
		$f5 =	$t_seq{$t};					# Sequence
		$f6 =	$t_agentno{$t};				# Number of Agents
		$f7 =	$t_node{$t};				# Coordinator Node
		$f8 =	$t_pid{$t};					# Coordinator PID
		$f9 =	$t_status{$t};				# Status
		$fa =	$t_statchgtm{$t};			# Status Change Time
		$fb =	xmpPerl::trim($t_dbname{$t});	# Database Name
		$fc =	$t_path{$t};				# DB Path
		$x1 =   $t_pcpu{$t};				# % CPU
		$x2 =   $t_pmem{$t};				# % Memory
		$x3 =   $t_args{$t};				# command/args
		$x4 =   $t_time{$t};				# execution time
		$x5 =   $t_etime{$t};				# started time
		$x6 =   $t_stat{$t};				# process status
		if (  ($opt_d eq "*")
		   or ($opt_d eq $fb) ) {
			write;
			####################################################################
			# report lock information if requested		V4.0
			####################################################################	
			if (  (defined $opt_a)
			   && ($opt_a eq "LOCKS") )	{
				ReportLocks($t_id{$t});
			}
		}
	}
}
################################################################################
# Write formated list of locks						V4.0
################################################################################
sub ReportLocks {
   my $id = $_[0];
   my $work;
   my $work2;
   my $i = 0;
   my $t = 0;

	#print ">>> locks to report:".$t_L_nolocks{$id}."\n";
	if ($t_L_nolocks{$id}==0) {return;}		# nothing in table

	$FORMAT_NAME 		= "LOCKSZEILE"; 	# $~
	$FORMAT_TOP_NAME 	= "LOCKSKOPF";		# $^
	$FORMAT_FORMFEED	= "";			# $^L 
	#$FORMAT_LINES_PER_PAGE	= ($t_L_nolocks{$id}*2)+2;	# $=, plus Lines für Kopf
	$FORMAT_LINES_LEFT      = 0;			# vorhergeh. Format

	$work = ($id*1000) + $t_L_nolocks{$id};	# max range value
	for ($t = (($id*1000)+1); $t<=$work ; $t++) {
	#print ">>> reporting entry $t\n";
		$l1 = $t_L_objtype{$t};			# Lock Object Type
		$l2 = $t_L_ts{$t};				# Tablespace
		$l3 = $t_L_table{$t};			# Schema and Name
		$l4 = $t_L_mode{$t};			# Lock Mode
		$l5 = $t_L_status{$t};			# Lock Status
		$l6 = $t_L_escal{$t};			# Lock Escalation
		$l7 = $t_L_objid{$t};			# Lock Object ID
		#print ">>> TO WRITE: $l1 $l2 $l3 $l4 $l5 $l6 $l7 \n";
		write
	}
}

################################################################################
# Eine oder alle Datenbanken deaktivieren
################################################################################
sub DeactivateDatabase {
   my $work;
   my $work2;

	############################################################################
	# Distinct database or all?
	############################################################################
	if ($opt_d ne "*") {
		$work = xmpPerl::doCmdOut("db2 deactivate db $opt_d");	
		#print "YYYYYY $work\n";
		print "Database deactivated ........: ";
		if (($work =~ 'DB20000I') or
		    ($work =~ 'SQL1496W') or
		    ($work =~ 'DB21024I')    )	 {
			print "$opt_d successful\n";
			$loop = 0;
		}
		else {
			####################################################################
			# Deactivation failed                               V2.0
			####################################################################
			$loop++;
			print "$opt_d unsuccessful ($loop. pass)\n";
			print "$work\n";
			GetThreads();					  # V3.1
			print "Current Threads at Instance  : $t\n";	  # V3.1
			ReportThreads();				  # V3.1
			####################################################################
			# Try 3 times                                       V2.0
			####################################################################
			if ($loop <=2) {
				DeactivateDatabase();
			}
		}
	}
	else {
		########################################################################
		# All databases in DB drectory
		########################################################################
		$work = $db;
		for ($db = 1; $db <= $work; $db++) {
			####################################################################
			# Local database?
			####################################################################
			if ($dbtype{$db} eq "indirect") 	{
				$work2 = xmpPerl::doCmdOut("db2 deactivate db $dbname{$db}");	
				#print "ZZZZZZZ $work\n";
				print "Database deactivated ........: ";
				if (($work2 =~ 'DB20000I') or 
				    ($work2 =~ 'SQL1496W') or
				    ($work2 =~ 'DB21024I')    )	 {
					print "$dbname{$db} successful\n";
					$loop = 0;
				}
				else {
			        ############################################################
					# Deactivation failed               V2.0
					############################################################
					$loop++;
					print "$dbname{$db} unsuccessful ($loop. pass)\n";
					print "$work2\n";
					GetThreads();			  # V3.1
					print "Current Threads at Instance  : $t\n"; # V3.1
					ReportThreads();		  # V3.1
					############################################################
					# Try 3 times                       V2.0
					############################################################
					if ($loop <=2) {
						DeactivateDatabase();
					}
				}
			}
			else {
				print "Database not deactivated ....: ".$dbname{$db}." is not local\n";
			}
		}
	}
}

################################################################################
# Force Distinct Thread
################################################################################
sub ForceThread {
   my $work;
   my $work2;

	$work2 = "db2 force application '(' ".$_[0]." ')'";
	#print "FFFFFFF $work2\n";
	$work = xmpPerl::doCmdOut($work2);	
	#print "GGGGGG $work\n";
	print "Thread ID=".$_[0]." terminated ....: ";
	if (($work =~ 'DB20000I') or
	    ($work =~ 'DB21024I')  ) {
		print "successful\n";
	}
	else {
		print "unsuccessful\n";
		print "$work\n";
	}
}

################################################################################
# getPSval: provides operating system information for specific PID
################################################################################
sub getPSval {
my $pid = $_[0];
my $req = $_[1];
my $val = "";
my $awk = " ";

	############################################################################
	# awk: print 2nd word if 1st word contains process id
	############################################################################
	$awk = "awk '\$1 ~ /$pid/ {print \$2}'";
	############################################################################
	# ps: print process id and any other option for just one specific pid
	############################################################################
	$val = xmpPerl::doCmdOut("ps -efo 'pid $req' | grep $pid | $awk");
	$val = xmpPerl::trim($val);
	return $val;
}

################################################################################
# getLocks: Start SNAPSHOT for lock information 			V4.0
################################################################################
sub getLocks {
my $id = $_[0];
my $work;
my @work;
my $f_found = 0;
my $t;
	############################################################################
    # DB2 SNAPSHOT
	############################################################################
	$work = xmpPerl::doCmdOut("db2 get snapshot for locks for application agentid $id");
	undef $/;
	@work = split("\n",$work);
	#print "$#work lines returned from DB2 SNAPSHOT Command.\n";

	############################################################################
        # DB2 SNAPSHOT w/o result
	############################################################################
	if ($#work == 1) {
		return;
	}

	$t_L_nolocks{$id}= 0;
	$t 				= $id * 1000;			# beginn range
	$t_L_objtype{$id} = "";
	$t_L_ts{$id}	= "";
	$t_L_table{$id}	= "";
	$t_L_mode{$id}	= "";
	$t_L_status{$id}= "";
	$t_L_escal{$id}	= "";

	############################################################################
    # Process DB2 SNAPSHOT output
	############################################################################
nextline: foreach $work (@work) {
		########################################################################
		# Split line, remove leading and trailing blanks
		########################################################################
		($var,$val) = split("=",$work);
		if (! defined $var) {$var = "";}
		if (! defined $val) {$val = "";}
		if (length($var) > 60) {		
			($var,$val) = split(":",$work);
			if (! defined $var) {$var = "";}
			if (! defined $val) {$val = "";}
		}
		if ( ($var eq "") and ($val eq "") ) 	{next nextline};
		if ( $work =~ "SQL1611W") 		{next nextline}; # no data 
		$var = uc xmpPerl::trim($var);
		$val = xmpPerl::trim($val);
		#print "$var=$val\n";

		########################################################################
		# Try to detect and extract specific information
		########################################################################
		if ( $var =~ "LOCKS HELD") 	{
			$t_L_nolocks{$id} = $val;
			next nextline;
		}
		if ( $var =~ "OBJECT NAME") 	{
			$t++;
			$t_L_objid{$t} = $val;
			#print ">>> Extracted: $t \n";
			next nextline;
		}
		if ( $var =~ "OBJECT TYPE") 	{
			$t_L_objtype{$t} = $val;
			next nextline;
		}
		if ( $var =~ "TABLESPACE") 	{
			$t_L_ts{$t} = $val;
			next nextline;
		}
		if ( $var =~ "TABLE SCHEMA") 	{
			$t_L_table{$t} = xmpPerl::trim($val);
			next nextline;
		}
		if ( $var =~ "TABLE NAME") 	{
			$t_L_table{$t} = $t_L_table{$t}.".".xmpPerl::trim($val);
			next nextline;
		}
		if ( $var =~ "MODE") 	{
			$t_L_mode{$t} = $val;
			next nextline;
		}
		if ( $var =~ "STATUS") 	{
			$t_L_status{$t} = $val;
			next nextline;
		}
		if ( $var =~ "ESCALATION") 	{
			$t_L_escal{$t} = $val;
			next nextline;
		}
	}
	#print ">>>AT END OF EXTRACTION: $t\n";
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

#!/usr/local/bin/perl -w

################################################################################

# xmpTerm.pl - Display or Terminate DB2 Application Threads

# Called by - Command Level or DBA scripts

# Calls - xmpPerl.pm - xmp DBA Perl Package

# Getopt - Argument Administration

# Env - Access to environment variables

# English - aliases for perl variables and identifiers

# Optionen - xmpTerm.pl -d db|* Display threads on distinct database

# -l (deflt.) Display all threads

# -t id|* Terminate all or distinct Thread(s)

# -a action [LOCKS]

#-------------------------------------------------------------------------------

# Autor - GR /05.01.00 - V1.0 first version

# Changes - GR /23.08.00 - V1.1 terminate * now results in DEACTIVATE DB

# GR /11.09.00 - V1.2 tiny amendment

# GR /26.01.01 - V1.3 try to terminate threads only of local databases,

# changes in comments

# GR /17.07.01 - V1.4 adapted to DB2 V7.1, new xmpPerl.pm

# GR /11.12.01 - V1.5 Logging bei -t

# GR /13.01.03 - V2.0 Retry while deactivating databases, no "die"

# (rc=1) when system w/o databases (SQL1031N)

# GR /29.01.03 - V2.1 Correction on loops/retries for deactivation

# GR /29.01.03 - V3.0 Provide more information

# GR /30.07.03 - V3.1 Provide more information after FORCE looped 3 times

# GR /12.08.03 - V4.0 New option -a LOCKS

# GR /13.01.04 - V4.1 adapted to DB2 V8.1, new xmpPerl.pm

# GR /18.06.04 - V4.2 Show long table and tablespace names

# GR /28.12.06 - V4.3 Amendments for DB2 Version 9

# GR /14.07.11 - V4.4 Amendments for DB2 Version 9.7

# (Infomation: db2 list applications show detail does not provide

# the correct operating system process id (pid).)

# GR /21.09.11 - V4.4.1 Show also system components

#-------------------------------------------------------------------------------

# Comments - PERL5LIB environment variable must point to perlauf perl modules!

# - Check correctness for every software (db2, perl) release change!

# - Do not use * ! Use '*' instead to qualifiy -d database name or -t

# thread id.

################################################################################

# Modules

################################################################################

use strict;

use Getopt::Std;

use vars qw($opt_d $opt_l $opt_t $opt_a);

use xmpPerl;

use Env;

use English;

################################################################################

# Declare/Init

################################################################################

my $VER = "4.4.1";

my $work = ""; # for TS List

my @work; # for TS List

my $work2 = ""; # for Container List

my @work2; # for Container List

my $var;

my $val;

my $f_found; # Flag: found

my $loop = 0; # retry counter V2.1

my $db = 0; # no. of Databases

my %dbname = (); # Databases

my %dbtype = (); # Entry Type

my $t = 0; # no. of Threads

my %t_authid = (); my $f1=""; # Auth ID

my %t_applname = (); my $f2=""; # Application Name

my %t_id = (); my $f3=""; # Thread ID

my %t_applid = (); my $f4; # Application ID

my %t_seq = (); my $f5; # Sequence

my %t_agentno = (); my $f6; # Number of Agents

my %t_node = (); my $f7; # Coordinator Node

my %t_pid = (); my $f8; # Coordinator PID

my %t_status = (); my $f9; # Status

my %t_statchgtm = (); my $fa; # Status Change Time

my %t_dbname = (); my $fb; # Database Name

my %t_path = (); my $fc; # DB Path

my $DB2Version = xmpPerl::getDB2Version; # akt. DB2 Version

my $OS = ucfirst $^O; # operating system

my %t_pcpu = (); my $x1; # CPU usage percentage

my %t_pmem = (); my $x2; # Memory usage percentage

my %t_args = (); my $x3; # Command and arguments

my %t_time = (); my $x4; # execution time

my %t_etime = (); my $x5; # start time

my %t_stat = (); my $x6; # process status

my %t_L_nolocks = (); # Number of locks

my %t_L_objtype = (); my $l1; # Lock Object Type

my %t_L_ts = (); my $l2; # Tablespace

my %t_L_table = (); my $l3; # Schema and Name

my %t_L_mode = (); my $l4; # Lock Mode

my %t_L_status = (); my $l5; # Lock Status

my %t_L_escal = (); my $l6; # Lock Escalation

my %t_L_objid = (); my $l7; # Lock Object ID

################################################################################

# Ausgabe-Formate

################################################################################

format KOPF =

--------------------------------------------------------------------------------

------------------------ LIST OF DB2 THREADS -----------------------------------

DBNAME--------------------------------------------------------------------------

#@<<<<<<<*ID.....: @<<<<<<< Path..: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

#$fb, $f3, $fc,

# PID ...: @<<<<<<< Node..: @>>>> Seq-No: @>>> Agents: @<<<<<<<<

# $f8, $f7, $f5, $f6,

# Auth-ID: @<<<<<<< Applic: @<<<<<<<<<<<<<<<<<<< Status: @<<<<<<<<<<<<<<<

# $f1, $f2, $f9,

# Appl-ID: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Status Change: @<<<<<<<<<<<<<<<

# $f4, $fa,

# CPU%...: @<<<<<<< Mem% .: @<<<< Cmd ..: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

# $x1, $x2, $x3

# Time ..: @<<<<<<< Start : @<<<<<<<<<<<<< Status: @<<<<<

# $x4, $x5, $x6

format ZEILE =

@<<<<<<<*ID.....: @<<<<<<< Path....: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

$fb, $f3, $fc,

Auth-ID: @<<<<<<< Appl-ID.: @<<<<<<<<<<<<<<<<<<<<<<<<< Node..: @<<<<<<<

$f1, $f4, $f7,

Agents.: @<<<<<<< Applicat: @<<<<<<<<<<<<<<<<<<<<<<<<< Seq-No: @<<<<<<<

$f6, $f2, $f5,

Status.: @<<<<<<<<<<<<<<<<< Change: @<<<<<<<<<<<<<<<<< Active: A or S

$f9, $fa,

PID....: @<<<<<<< Command.: @<<<<<<<<<<<<<<<<<<<<<<<<< Status: @<<<<<<<

$f8, $x3, $x6,

CPU %..: @<<<<<<< Memory %: @<<<< Elapsed: @<<<<<<<< Time..: @<<<<<<<

$x1, $x2, $x5, $x4

format LOCKSKOPF =

-------- List of Locks for current Agent ------------------------------

# *Lock...: @<<<<<<< Table...: @<<<<<<<<<<<<<<<<<<<<<<<<< TSpace: @<<<<<<<

# $l1, $l3, $l2,

# Obj-ID. @<<<<<<< Status..: @<<<<<< Escalat: @<<<<<<< Mode..: @<<<<<<<

# $l7, $l4, $l6, $l5

format LOCKSZEILE =

*Lock...: @<<<<<<< Obj-ID..: @<<<<<<<<<<<<<<< Mode....: @<<<<<<<<<<<<<<<

$l1, $l7, $l5,

Table..: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< TSpace..: @<<<<<<<<<<<<<<<

$l3, $l2,

Status.: @<<<<<< Escalat'n: @<<<<<<

$l4, $l6

################################################################################

# Argumente/Options prüfen

################################################################################

getopts("d:lt:a:");

if (( defined $ARGV[0] ) and # needs help? V2.1

(( $ARGV[0] eq "?") or

( $ARGV[0] eq "-h"))) {

SaySyntax();

}

if (! defined $opt_d ) { # Datenbank

$opt_d = "*";

}

if ((! defined $opt_l ) and # List Option

(! defined $opt_t ) ) { # Term Option

$opt_l = ""; # List Option ist Default

}

if ((defined $opt_t ) and # Terminate requested

($opt_t ne "*") and # kein * drin

($opt_t !~ /[1234567890]/) ) { # nicht numerisch

print "$0 - specify -t '*'|nn (all threads or specific thread)\n";

SaySyntax();

}

if (defined $opt_a ) { # ACTION option V4.0

if ( ($opt_a !~ /[LlOoCcKkSs]/) # not LOCKS

or (length($opt_a) != 5) ) { # not 5 bytes V4.3

print "$0 - specify option -a LOCKS\n"; # V4.0

SaySyntax(); # V4.0

} # V4.0

else {$opt_a = uc $opt_a;} # uppercase option V4.0

} # V4.0

$opt_d = uc $opt_d; # uppercase database

################################################################################

# Check DB2 existence, write information on DB2

################################################################################

xmpPerl::chkDB2();

xmpPerl::prtHeaderDB2("$0 - List/Terminate DB2 Threads - Version $VER");

print "Database ....................: $opt_d\n";

if (defined $opt_t) {

print "Terminate Threads ...........: $opt_t\n";

}

################################################################################

# Check DB2 Instance Database Directory V1.6

################################################################################

$work = xmpPerl::doCmdOut("db2 list db directory");

@work = split("\n",$work);

if ($work =~ "SQL1031N") {

print ">>> DB2 Instance has no database directory (e.g. like DB2 Admin Server). RC=0\n";

print "***Ende***\n";

exit 0;

}

################################################################################

# DB2 List Database Directory wg. Name

################################################################################

#$work = xmpPerl::doCmdOut("db2 list db directory | grep -i 'database alias' | sort");

$work = xmpPerl::doCmdOut("db2 list db directory | egrep -i 'database alias|entry type'");

undef $/;

@work = split("\n",$work);

#print "$#work lines returned from DB2 List db dir Command.\n";

if ( ($work =~ "not found")

or ($work =~ "nicht gefunden") ) {

die ">>> DB2 Commands (z.B. list db dir) nicht ausführbar!\n".

">>> Meldung: $work\n".

">>> ABEND ";

}

print "Databases im Directory ......: ";

foreach $work (@work) {

($var,$val) = split(" = ",$work);

if ($var =~ "alias") {

$db++;

$dbname{$db} = xmpPerl::trim($val); # Datenbank in Stack legen

# print $dbname{$db}." ";

}

if ($var =~ "entry type") {

$dbtype{$db} = lc xmpPerl::trim($val); # DB Type in Stack legen

print $dbname{$db}."(".$dbtype{$db}.") ";

}

print "\n";

if ($db == 0) {

print ">>> This DB2 instance does not contain databases! RC=0\n".

print "***End***\n";

exit 0;

}

################################################################################

# Wenn Database-Name als Argument angegeben, Namen prüfen

################################################################################

if ($opt_d ne "*") {

$work = $db;

$f_found = 0;

for ($db = 1; $db <= $work; $db++) {

if ($dbname{$db} eq $opt_d) {

$f_found = 1;

}

if ($f_found==0) {

die ">>> Database $opt_d does not exist at current DB2 instance!\n".

">>> ABEND ";

}

################################################################################

# Display Threads

################################################################################

if (defined $opt_l) {

GetThreads();

print "Current Threads at Instance : $t\n";

ReportThreads();

}

################################################################################

# Terminate Threads

################################################################################

if (defined $opt_t) {

############################################################################

# Write informational message to log file

############################################################################

xmpPerl::LogLine("DBA I ".$0." $< $opt_d $opt_t");

############################################################################

# Current Threads

############################################################################

GetThreads();

############################################################################

# Alle Threads abarbeiten

############################################################################

$work = $t;

for ($t = 1; $t <= $work; $t++) {

########################################################################

# Bestimmter Thread oder alle Threads in allen DB's oder bestimmter DB

########################################################################

#print "XXX ".$t_id{$t}." ".$t_dbname{$t}." XXXX\n";

if ( ( ($opt_t eq "*")

or ($t_id{$t} == $opt_t) )

and ( ($opt_d eq "*")

or ($t_dbname{$t} eq $opt_d ) ) ) {

ForceThread($t_id{$t});

}

############################################################################

# Wenn alle Threads forced (alle überhaupt oder einer DB)

############################################################################

if ($opt_t eq "*") {

DeactivateDatabase();

}

############################################################################

# Buffer Flush

############################################################################

$work = xmpPerl::doCmdOut("db2 terminate");

#print "AAAAA $work\n";

}

################################################################################

# Display Threads erneut nach Terminate Threads

################################################################################

if ((defined $opt_l) and

(defined $opt_t) ) {

GetThreads();

ReportThreads();

}

print "***Ende***\n";

exit 0;

################################################################################

########################################################## Sub Routines ########

# SaySyntax ####################################################################

sub SaySyntax {

die "Usage: [perl] $0 -d [db|'*'] [-l] [-t id|*] [-a {LOCKS}]\n".

" -d db|'*' database name\n".

" -l display thread(s) (default option)\n".

" -t id|'*' terminate thread(s)\n".

" -a LOCKS display lock information\n".

"abends";

}

################################################################################

# Alle oder nur best. DB-Threads ermitteln und Daten intern speichern

################################################################################

sub GetThreads {

my $work;

my $work2;

my $startpos = 9; # Dflt. Start

$t = 0;

############################################################################

# Starting position in DB2 command output is version-dependent!

############################################################################

if ($DB2Version =~ "5") {$startpos=9} # Version 6 17.07.2001

if ($DB2Version =~ "6") {$startpos=9} # Version 6 17.07.2001

if ($DB2Version =~ "7") {$startpos=31} # Version 7 17.07.2001

if ($DB2Version =~ "8") {$startpos=31} # Version 8 13.01.2004

if ($DB2Version =~ "9") {$startpos=31} # Version 9 28.12.2006

if ($DB2Version =~ "9.7") {$startpos=129} # Version 9.7 14.07.2011

############################################################################

# DB2 List Applications show detail

############################################################################

$work = xmpPerl::doCmdOut("db2 list applications show detail");

undef $/;

@work = split("\n",$work);

#print "$#work lines returned from DB2 List Applications Command.\n";

if ($#work == 0) {return}

$f_found = 0;

foreach $work (@work) {

#($f1,$f2,$f3,$f4,$f5,$f6,$f7,$f7,$f9,$fa,$fb,$fc) = split(" ",$work);

if (length($work) > 0) {

####################################################################

# Diese folgenden Bedingungen sind Versions-abhängig!

####################################################################

$f1 = substr($work,0,8); # Authid

$f2 = substr($work,$startpos,20); # Appl. Name

$f3 = substr($work,$startpos+21,10); # Appl. Handle

$f4 = substr($work,$startpos+32,30); # Appl. ID

$f5 = substr($work,$startpos+63,4); # Seq. No

$f6 = substr($work,$startpos+68,10); # No Agents.

if ($DB2Version =~ "9") { # 28.12.2006

$f4 = substr($work,$startpos+32,62); # Appl. ID

$f5 = substr($work,$startpos+95,5); # Seq. No

$f6 = substr($work,$startpos+101,10); # No Agents.

$f7 = substr($work,$startpos+112,16); # Coord. Node

$f8 = substr($work,$startpos+129,15); # Coord PID/Thread

if (length($work) >= $startpos+145) {

$f9 = substr($work,$startpos+145,30)} # Status

else { $f9 = "n/a" }

if (length($work) >= $startpos+176) {

$fa = substr($work,$startpos+176,26)} # Status Change

else { $fa = "n/a" }

if (length($work) >= $startpos+203) {

$fb = substr($work,$startpos+203,8)} # DBNAME

else { $fb = "n/a" }

if (length($work) >= $startpos+212) {

$fc = substr($work,$startpos+212)} # DB Path

else { $fc = "n/a" }

}

elsif ($DB2Version =~ "8") { # 13.01.2004

$f7 = substr($work,$startpos+79,16); # Coord. Node

$f8 = substr($work,$startpos+92,15); # Coord PID/Thread

if (length($work) >= $startpos+112) {

$f9 = substr($work,$startpos+112,30)} # Status

else { $f9 = "n/a" }

if (length($work) >= $startpos+143) {

$fa = substr($work,$startpos+143,26)} # Status Change

else { $fa = "n/a" }

if (length($work) >= $startpos+170) {

$fb = substr($work,$startpos+170,8)} # DBNAME

else { $fb = "n/a" }

if (length($work) >= $startpos+179) {

$fc = substr($work,$startpos+179)} # DB Path

else { $fc = "n/a" }

}

else {

$f7 = substr($work,$startpos+79,12); # Coord. Node

$f8 = substr($work,$startpos+92,15); # Coord PID/Thread

if (length($work) >= $startpos+108) {

$f9 = substr($work,$startpos+108,30)} # Status

else { $f9 = "n/a" }

if (length($work) >= $startpos+139) {

$fa = substr($work,$startpos+139,26)} # Status Change

else { $fa = "n/a" }

if (length($work) >= $startpos+166) {

$fb = substr($work,$startpos+166,8)} # DBNAME

else { $fb = "n/a" }

if (length($work) >= $startpos+175) {

$fc = substr($work,$startpos+175)} # DB Path

else { $fc = "n/a" }

}

#print length($work)."$f1 $f2 $f3\n";

#print "$f7 # $f8 # $f9 # $fa # $fb # $fc #\n";

########################################################################

# Wenn Ausgabedaten lokalisiert werden konnten

########################################################################

# && ($f5 ne "00001") ) { # no systems components V4.4.1

if ($f_found) {

#print "eingetragen\n";

$t++;

$t_authid{$t} = $f1; # Auth ID

$t_applname{$t} = $f2; # Application Name

$t_id{$t} = xmpPerl::trim($f3); # Thread ID

$t_applid{$t} = $f4; # Application ID

$t_seq{$t} = $f5; # Sequence

$t_agentno{$t} = $f6; # Number of Agents

$t_node{$t} = $f7; # Coordinator Node

$t_pid{$t} = xmpPerl::trim($f8); # Coordinator PID

$t_status{$t} = $f9; # Status

$t_statchgtm{$t}= $fa; # Status Change Time

$t_dbname{$t} = xmpPerl::trim($fb); # Database Name

$t_path{$t} = $fc; # DB Path

####################################################################

# retrieve operating system information

####################################################################

$t_pcpu{$t} = getPSval($t_pid{$t},'pcpu');

$t_pmem{$t} = getPSval($t_pid{$t},'pmem');

$t_args{$t} = getPSval($t_pid{$t},'comm');

$work2 = getPSval($t_pid{$t},'args');

if ($t_args{$t} ne $work2) {$t_args{$t} .= " ".$work2;}

$t_time{$t} = getPSval($t_pid{$t},'time');

$t_etime{$t} = getPSval($t_pid{$t},'etime');

$t_stat{$t} = "";

if ($OS eq "Aix") { $t_stat{$t}=getPSval($t_pid{$t},'stat'); }

if ($OS eq "Solaris") { $t_stat{$t}=getPSval($t_pid{$t},'s'); }

####################################################################

# retrieve lock information if requested V4.0

####################################################################

if ( (defined $opt_a)

&& ($opt_a eq "LOCKS") ) {

getLocks($t_id{$t});

}

########################################################################

# Jetzt gehts mit echten Daten los

########################################################################

if ($f1 eq "--------") {

$f_found = 1;

}

################################################################################

# Write formated list of threads

################################################################################

sub ReportThreads {

my $work;

my $work2;

$FORMAT_NAME = "ZEILE"; # $~

$FORMAT_TOP_NAME = "KOPF"; # $^

$FORMAT_FORMFEED = ""; # $^L

$FORMAT_LINES_PER_PAGE = ($t*8)+3; # $=, plus Lines für Kopf

$FORMAT_LINES_LEFT = 0; # vorhergeh. Format

$work = $t;

for ($t = 1; $t<=$work ; $t++) {

$FORMAT_NAME = "ZEILE"; # $~

$FORMAT_TOP_NAME = "KOPF"; # $^

$f1 = $t_authid{$t}; # Auth ID

$f2 = $t_applname{$t}; # Application Name

$f3 = $t_id{$t}; # Thread ID

$f4 = $t_applid{$t}; # Application ID

$f5 = $t_seq{$t}; # Sequence

$f6 = $t_agentno{$t}; # Number of Agents

$f7 = $t_node{$t}; # Coordinator Node

$f8 = $t_pid{$t}; # Coordinator PID

$f9 = $t_status{$t}; # Status

$fa = $t_statchgtm{$t}; # Status Change Time

$fb = xmpPerl::trim($t_dbname{$t}); # Database Name

$fc = $t_path{$t}; # DB Path

$x1 = $t_pcpu{$t}; # % CPU

$x2 = $t_pmem{$t}; # % Memory

$x3 = $t_args{$t}; # command/args

$x4 = $t_time{$t}; # execution time

$x5 = $t_etime{$t}; # started time

$x6 = $t_stat{$t}; # process status

if ( ($opt_d eq "*")

or ($opt_d eq $fb) ) {

write;

####################################################################

# report lock information if requested V4.0

####################################################################

if ( (defined $opt_a)

&& ($opt_a eq "LOCKS") ) {

ReportLocks($t_id{$t});

}

################################################################################

# Write formated list of locks V4.0

################################################################################

sub ReportLocks {

my $id = $_[0];

my $work;

my $work2;

my $i = 0;

my $t = 0;

#print ">>> locks to report:".$t_L_nolocks{$id}."\n";

if ($t_L_nolocks{$id}==0) {return;} # nothing in table

$FORMAT_NAME = "LOCKSZEILE"; # $~

$FORMAT_TOP_NAME = "LOCKSKOPF"; # $^

$FORMAT_FORMFEED = ""; # $^L

#$FORMAT_LINES_PER_PAGE = ($t_L_nolocks{$id}*2)+2; # $=, plus Lines für Kopf

$FORMAT_LINES_LEFT = 0; # vorhergeh. Format

$work = ($id*1000) + $t_L_nolocks{$id}; # max range value

for ($t = (($id*1000)+1); $t<=$work ; $t++) {

#print ">>> reporting entry $t\n";

$l1 = $t_L_objtype{$t}; # Lock Object Type

$l2 = $t_L_ts{$t}; # Tablespace

$l3 = $t_L_table{$t}; # Schema and Name

$l4 = $t_L_mode{$t}; # Lock Mode

$l5 = $t_L_status{$t}; # Lock Status

$l6 = $t_L_escal{$t}; # Lock Escalation

$l7 = $t_L_objid{$t}; # Lock Object ID

#print ">>> TO WRITE: $l1 $l2 $l3 $l4 $l5 $l6 $l7 \n";

write

}

################################################################################

# Eine oder alle Datenbanken deaktivieren

################################################################################

sub DeactivateDatabase {

my $work;

my $work2;

############################################################################

# Distinct database or all?

############################################################################

if ($opt_d ne "*") {

$work = xmpPerl::doCmdOut("db2 deactivate db $opt_d");

#print "YYYYYY $work\n";

print "Database deactivated ........: ";

if (($work =~ 'DB20000I') or

($work =~ 'SQL1496W') or

($work =~ 'DB21024I') ) {

print "$opt_d successful\n";

$loop = 0;

}

else {

####################################################################

# Deactivation failed V2.0

####################################################################

$loop++;

print "$opt_d unsuccessful ($loop. pass)\n";

print "$work\n";

GetThreads(); # V3.1

print "Current Threads at Instance : $t\n"; # V3.1

ReportThreads(); # V3.1

####################################################################

# Try 3 times V2.0

####################################################################

if ($loop <=2) {

DeactivateDatabase();

}

else {

########################################################################

# All databases in DB drectory

########################################################################

$work = $db;

for ($db = 1; $db <= $work; $db++) {

####################################################################

# Local database?

####################################################################

if ($dbtype{$db} eq "indirect") {

$work2 = xmpPerl::doCmdOut("db2 deactivate db $dbname{$db}");

#print "ZZZZZZZ $work\n";

print "Database deactivated ........: ";

if (($work2 =~ 'DB20000I') or

($work2 =~ 'SQL1496W') or

($work2 =~ 'DB21024I') ) {

print "$dbname{$db} successful\n";

$loop = 0;

}

else {

############################################################

# Deactivation failed V2.0

############################################################

$loop++;

print "$dbname{$db} unsuccessful ($loop. pass)\n";

print "$work2\n";

GetThreads(); # V3.1

print "Current Threads at Instance : $t\n"; # V3.1

ReportThreads(); # V3.1

############################################################

# Try 3 times V2.0

############################################################

if ($loop <=2) {

DeactivateDatabase();

}

else {

print "Database not deactivated ....: ".$dbname{$db}." is not local\n";

}

################################################################################

# Force Distinct Thread

################################################################################

sub ForceThread {

my $work;

my $work2;

$work2 = "db2 force application '(' ".$_[0]." ')'";

#print "FFFFFFF $work2\n";

$work = xmpPerl::doCmdOut($work2);

#print "GGGGGG $work\n";

print "Thread ID=".$_[0]." terminated ....: ";

if (($work =~ 'DB20000I') or

($work =~ 'DB21024I') ) {

print "successful\n";

}

else {

print "unsuccessful\n";

print "$work\n";

}

################################################################################

# getPSval: provides operating system information for specific PID

################################################################################

sub getPSval {

my $pid = $_[0];

my $req = $_[1];

my $val = "";

my $awk = " ";

############################################################################

# awk: print 2nd word if 1st word contains process id

############################################################################

$awk = "awk '\$1 ~ /$pid/ {print \$2}'";

############################################################################

# ps: print process id and any other option for just one specific pid

############################################################################

$val = xmpPerl::doCmdOut("ps -efo 'pid $req' | grep $pid | $awk");

$val = xmpPerl::trim($val);

return $val;

}

################################################################################

# getLocks: Start SNAPSHOT for lock information V4.0

################################################################################

sub getLocks {

my $id = $_[0];

my $work;

my @work;

my $f_found = 0;

my $t;

############################################################################

# DB2 SNAPSHOT

############################################################################

$work = xmpPerl::doCmdOut("db2 get snapshot for locks for application agentid $id");

undef $/;

@work = split("\n",$work);

#print "$#work lines returned from DB2 SNAPSHOT Command.\n";

############################################################################

# DB2 SNAPSHOT w/o result

############################################################################

if ($#work == 1) {

return;

}

$t_L_nolocks{$id}= 0;

$t = $id * 1000; # beginn range

$t_L_objtype{$id} = "";

$t_L_ts{$id} = "";

$t_L_table{$id} = "";

$t_L_mode{$id} = "";

$t_L_status{$id}= "";

$t_L_escal{$id} = "";

############################################################################

# Process DB2 SNAPSHOT output

############################################################################

nextline: foreach $work (@work) {

########################################################################

# Split line, remove leading and trailing blanks

########################################################################

($var,$val) = split("=",$work);

if (! defined $var) {$var = "";}

if (! defined $val) {$val = "";}

if (length($var) > 60) {

($var,$val) = split(":",$work);

if (! defined $var) {$var = "";}

if (! defined $val) {$val = "";}

}

if ( ($var eq "") and ($val eq "") ) {next nextline};

if ( $work =~ "SQL1611W") {next nextline}; # no data

$var = uc xmpPerl::trim($var);

$val = xmpPerl::trim($val);

#print "$var=$val\n";

########################################################################

# Try to detect and extract specific information

########################################################################

if ( $var =~ "LOCKS HELD") {

$t_L_nolocks{$id} = $val;

next nextline;

}

if ( $var =~ "OBJECT NAME") {

$t++;

$t_L_objid{$t} = $val;

#print ">>> Extracted: $t \n";

next nextline;

}

if ( $var =~ "OBJECT TYPE") {

$t_L_objtype{$t} = $val;

next nextline;

}

if ( $var =~ "TABLESPACE") {

$t_L_ts{$t} = $val;

next nextline;

}

if ( $var =~ "TABLE SCHEMA") {

$t_L_table{$t} = xmpPerl::trim($val);

next nextline;

}

if ( $var =~ "TABLE NAME") {

$t_L_table{$t} = $t_L_table{$t}.".".xmpPerl::trim($val);

next nextline;

}

if ( $var =~ "MODE") {

$t_L_mode{$t} = $val;

next nextline;

}

if ( $var =~ "STATUS") {

$t_L_status{$t} = $val;

next nextline;

}

if ( $var =~ "ESCALATION") {

$t_L_escal{$t} = $val;

next nextline;

}

#print ">>>AT END OF EXTRACTION: $t\n";

}

Any suggestions – don’t hesitate to contact me!

How to retrieve JCC version

You need to check the version of your Java Combined Client (JCC) installed? Try this way:

// The following command will retrieve the JCC driver version if executed from the command line: 
java com.ibm.db2.jcc.DB2Jcc -version 

//Or for drivers that are not yet installed: 
java -cp ./db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version 

//Content of db2jcc JAR file:
jar -tvf db2jcc.jar

// The following command will retrieve the JCC driver version if executed from the command line:

java com.ibm.db2.jcc.DB2Jcc -version

//Or for drivers that are not yet installed:

java -cp ./db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version

//Content of db2jcc JAR file:

jar -tvf db2jcc.jar

All DB2 JDBC Driver versions, see here!

More information on DB2, Java, JDBC and JCC.

Perl Script xmpPerlDB2Test.pl to verify DBI and DBD::DB2 installation

This Perl script verifies the interface to DB2 whicht consists of the general database interface, called DBI, and the DB2-specific supplement, called DBD::DB2.

This script uses the xmpPerl.pm library, which is also available at this site.

#!/usr/local/bin/perl -w
use strict;

print "Perl Search Path INC ........: @INC\n";

####use lib  qw(/usr/opt/db2_08_01/lib64);
use Getopt::Std;
use vars qw($opt_d $opt_l $opt_t $opt_a);
use Env;
use Env qw (@PATH @LIBPATH @LD_LIBRARY_PATH @CLASSPATH);
use English;
use DBI;
use DBD::DB2::Constants;
use DBD::DB2;
use xmpPerl;

#################################################################################
print "PATH ........................: @PATH\n";
print "LIBPATH .....................: @LIBPATH\n";
print "LD_LIBRARY_PATH .............: @LD_LIBRARY_PATH\n";
print "CLASSPATH ...................: @CLASSPATH\n";

#################################################################################
use Getopt::Long;		
use vars qw($opt_d $opt_u $opt_p);
GetOptions("d=s","u:s","p:s");

if (! defined $opt_d )
	{print "$0 - Database nicht angegeben\n";
         SaySyntax();  }

if (! defined $opt_u ) {			# User Check
	$opt_u = xmpPerl::getDBAUser();;
	if ($opt_u eq "")
		{print "$0 - User nicht angegeben und keine lP_LUS Variable definiert\n";
        	 SaySyntax();  }
	}

if (! defined $opt_p )				# User Password Check
	{$opt_p = xmpPerl::getDBAPsw();
	if ($opt_p eq "")
		{print "$0 - User's Password fehlt und keine lP_LPW Variable definiert\n";
        	 SaySyntax();  }
	}
#################################################################################

my $stmt = "SELECT DISTINCT CREATOR FROM SYSIBM.SYSTABLES";

unlink 'dbitrace.log' if -e 'dbitrace.log';
DBI->trace( 15, 'dbitrace.log' );
print "DBI Trace Log ...............: written to dbitrace.log (current path)\n";

my $switch = DBI->internal;
$switch->debug(0);                     # 1=normal debug, 2=detailed handle trace
print "DBI Version and Attributes ..: $switch->{'Attribution'}, $switch->{'Version'}\n";
print "DBI Drivers .................: ".join(", ",DBI->available_drivers())."\n";
DBI->available_drivers();
print "Installed Versions ..........: ".join(", ",DBI->installed_versions)."\n";
DBI->installed_versions;
print "DB2 Data Sources ............: ".DBI->data_sources('DB2')." (DB2 Databases)\n";
my @data_sources = DBI->data_sources('DB2');
print "                               @data_sources\n";

my $dbh = DBI->connect("dbi:DB2:$opt_d", $opt_u, $opt_p,
                    { PrintError => 1, #       print error by default
                      RaiseError => 1, #       print error and die by default
                      AutoCommit => 0, # don't commit after each statement
                      ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types
                    } );

#print "DBMS Name ...................: ".$dbh->get_info( SQL_DBMS_NAME )."\n";
#print "DBMS Version ................: ".$dbh->get_info(18)."\n";

my $sth = $dbh->prepare($stmt);
$sth->execute() || die $dbh->errstr;

print "SQL Test Statement ..........: $stmt\n";
while ( (my @row = $sth->fetchrow()) )
{print "@row\n";}

# Close Statement, Disconnect from Database ####################################
$sth->finish()   || die $dbh->errstr;
$dbh->commit     || die $dbh->errstr;
$dbh->disconnect || die $dbh->errstr;

exit 0;

sub SaySyntax {
	die "Usage: [perl] $0 -d db [-u ... -p ..] \n".
		"	-d database \n".
		"	-u user\n".
		"	-p password\n".
		"abends";
}

#!/usr/local/bin/perl -w

use strict;

print "Perl Search Path INC ........: @INC\n";

####use lib qw(/usr/opt/db2_08_01/lib64);

use Getopt::Std;

use vars qw($opt_d $opt_l $opt_t $opt_a);

use Env;

use Env qw (@PATH @LIBPATH @LD_LIBRARY_PATH @CLASSPATH);

use English;

use DBI;

use DBD::DB2::Constants;

use DBD::DB2;

use xmpPerl;

#################################################################################

print "PATH ........................: @PATH\n";

print "LIBPATH .....................: @LIBPATH\n";

print "LD_LIBRARY_PATH .............: @LD_LIBRARY_PATH\n";

print "CLASSPATH ...................: @CLASSPATH\n";

#################################################################################

use Getopt::Long;

use vars qw($opt_d $opt_u $opt_p);

GetOptions("d=s","u:s","p:s");

if (! defined $opt_d )

{print "$0 - Database nicht angegeben\n";

SaySyntax(); }

if (! defined $opt_u ) { # User Check

$opt_u = xmpPerl::getDBAUser();;

if ($opt_u eq "")

{print "$0 - User nicht angegeben und keine lP_LUS Variable definiert\n";

SaySyntax(); }

}

if (! defined $opt_p ) # User Password Check

{$opt_p = xmpPerl::getDBAPsw();

if ($opt_p eq "")

{print "$0 - User's Password fehlt und keine lP_LPW Variable definiert\n";

SaySyntax(); }

}

#################################################################################

my $stmt = "SELECT DISTINCT CREATOR FROM SYSIBM.SYSTABLES";

unlink 'dbitrace.log' if -e 'dbitrace.log';

DBI->trace( 15, 'dbitrace.log' );

print "DBI Trace Log ...............: written to dbitrace.log (current path)\n";

my $switch = DBI->internal;

$switch->debug(0); # 1=normal debug, 2=detailed handle trace

print "DBI Version and Attributes ..: $switch->{'Attribution'}, $switch->{'Version'}\n";

print "DBI Drivers .................: ".join(", ",DBI->available_drivers())."\n";

DBI->available_drivers();

print "Installed Versions ..........: ".join(", ",DBI->installed_versions)."\n";

DBI->installed_versions;

print "DB2 Data Sources ............: ".DBI->data_sources('DB2')." (DB2 Databases)\n";

my @data_sources = DBI->data_sources('DB2');

print " @data_sources\n";

my $dbh = DBI->connect("dbi:DB2:$opt_d", $opt_u, $opt_p,

{ PrintError => 1, # print error by default

RaiseError => 1, # print error and die by default

AutoCommit => 0, # don't commit after each statement

ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types

} );

#print "DBMS Name ...................: ".$dbh->get_info( SQL_DBMS_NAME )."\n";

#print "DBMS Version ................: ".$dbh->get_info(18)."\n";

my $sth = $dbh->prepare($stmt);

$sth->execute() || die $dbh->errstr;

print "SQL Test Statement ..........: $stmt\n";

while ( (my @row = $sth->fetchrow()) )

{print "@row\n";}

# Close Statement, Disconnect from Database ####################################

$sth->finish() || die $dbh->errstr;

$dbh->commit || die $dbh->errstr;

$dbh->disconnect || die $dbh->errstr;

exit 0;

sub SaySyntax {

die "Usage: [perl] $0 -d db [-u ... -p ..] \n".

" -d database \n".

" -u user\n".

" -p password\n".

"abends";

}

Perl statements to generate (DB2) timestamp

Do you want to generate a timestamp in external DB2 format YYYY-MM-DD-HH.MM.SS.ssssss?

Use either following functions which is already part of the xmpPerl.pm function library:

       $TS = `date +%Y-%m-%d-%H.%M.%S`;        # yyyy-mm-dd-hh.mm.ss
       $TS = &trim($TS);
       $TS.= ".000000";                        # milliseconds = 0
       return $TS;
}
# Unix does no not support milliseconds.
# Code not portable e.g. to windows!

$TS = `date +%Y-%m-%d-%H.%M.%S`; # yyyy-mm-dd-hh.mm.ss

$TS = &trim($TS);

$TS.= ".000000"; # milliseconds = 0

return $TS;

}

# Unix does no not support milliseconds.

# Code not portable e.g. to windows!

Or try following Perl statements which are compatible to POSIX and are portable to other platforms:

sub getTimestamp {
my $TS;
use Time::localtime;
$tm = localtime;                

# structure with 9 elements:     
#   $tm->$sec   
#   $tm->$min        
#   $tm->$hours        
#   $tm->$mday       # 1-31        
#   $tm->$month      # 1-12
#   $tm->$year       # 1-138 + 1900        
#   $tm->$wday       # 0-6, 0=Sunday
#   $tm->$yday       # 1-366
#   $tm->$sec        # 0 or 1, true=daylight saving time

# or:
($D,$MONAT,$J) = (localtime) [3,4,5];   # element 0 = $SEC

sub getTimestamp {

my $TS;

use Time::localtime;

$tm = localtime;

# structure with 9 elements:

# $tm->$sec

# $tm->$min

# $tm->$hours

# $tm->$mday # 1-31

# $tm->$month # 1-12

# $tm->$year # 1-138 + 1900

# $tm->$wday # 0-6, 0=Sunday

# $tm->$yday # 1-366

# $tm->$sec # 0 or 1, true=daylight saving time

# or:

($D,$MONAT,$J) = (localtime) [3,4,5]; # element 0 = $SEC

Perl module xmpPerl.pm – function package used by DBA Perl scripts

This perl module contains all functions need by xmp perl scripts, published at www.ruban.de. You should easily adapt some constant values to your pesonal needs.

Some comments are in german. If you need assistence, don’t hesitate to contact me.

#!/usr/local/bin/perl -w
################################################################################
# DBA Perl function package:
# - deploy with "use xmpPerl;"
# - execute a function by "xmpPerl::fn(...);", where fn=function listed below
#-------------------------------------------------------------------------------
# @|$ = trim(@|$);				Cut-off Leading/Trailing Blanks
# $ = doCmdOut($);				Run External Command and return Output
# $ = doDB2CmdOut(db u p cmd);  Run DB2 Command and return Output, incl. Connect
# $ = getSqllibPath();          DB2 Path suchen in PATH und z’ckgeben
# $ = getTimestamp();           Datum/Uhrzeit im DB2 Timestamp Format
# $ = getTime();					Uhrzeit
# - = LogLine(@|$);             Meldung in Log protokollieren, ruft ...File|Table
# - = LogLineFile(@|$);         Meldung in Log File protokollieren
# - = LogLineTable(@|$);        Meldung in Log Table protokollieren
# $ = getProcDescr();           Für Process ID den Process Namen und Argumente
# $ = getDBMCfgValue(var);      Liefert Wert eines DBM Config Parameters
# $ = getDBCfgValue(db,var);    Liefert Wert eines Database Config Parameters
# $ = getDBAUser();             Liefert Wert der lP_LUS Umgebungsvariablen
# $ = getDBAPsw();              Liefert Wert der lP_LPW Umgebungsvariablen
# $ = getDBALog();              Liefert Wert der lP_LOG Umgebungsvariablen oder Default
# $ = getDBALDB();              Liefert Wert der lP_LDB Umgebungsvariablen oder Default
# $ = getCTRL();                Liefert Wert der lP_CTRL Umgebungsvariablen oder Default
# - = prtHeaderDB2($);          Gibt einen Informationblock aus für Scripts mit DB2
# - = prtHeader($);             Prints block of information for non-DB2 scripts
# - = chkDB2();                 Checks existence of DB2
# $ = getDB2Version();          Provides DB2 version and release number
# $ = MailText();               Sends text via sendmail IP command
# $ = getDB2AddressMode();		Provides addressing mode (32 or 64 bit)
#-------------------------------------------------------------------------------
# Changes   - GR / 10.01.00    erste Version
# G.Ruban / 02.02.2000	V1.1	neue Funktion getDBCfgValue und getDBMCfgValue,
#                               getDBALog
# G.Ruban / 02.02.2000	V1.1	in LogLineTable den Empfang der Argumente korrigiert
# G.Ruban / 29.05.2000	V1.2	getDBAUser/getDBAPsw: User und Passwort aus Umgebungs-
#                               variablen, doDB2CmdOut um Parms -u und -p erweitert,
# G.Ruban / 31.05.2000	V1.3	neue Funktion prtHeader(), getINSTHOME, chkDB2
#                               und getDBALDB angelegt, LogLine...'s überarbeitet
# G.Ruban / 13.07.2000	V1.3.1	Logging in DBA Control DB problematisch, deswegen
#                               nur Logging in File, getProcDescr geändert
# G.Ruban / 28.08.2000	V1.3.2	Log Path für mydbadmin.log aus ENV Variable, falls vorh.
# G.Ruban / 29.08.2000	V1.3.2	chmod für mydbadmin.log wg. Permissions, sysopen wirkungslos
# G.Ruban / 17.07.2001	V1.3.3	neue Funktion getDB2Version()
# G.Ruban / 05.12.2001	V1.3.4	neue Funktion MailText(), neue Variable lP_CTRL
# G.Ruban / 11.12.2001	V1.3.5	neue Funktion getTime()
# G.Ruban / 19.12.2001  V1.3.5 Korrektur beim Einlesen von Recipient Lists
# G.Ruban / 30.01.2002  V1.3.6 Delete temporary file if work has been done
# G.Ruban / 12.02.2002  V1.3.6 Fixed problem with temp files
# G.Ruban / 21.01.2003  V1.3.7  New function prtHeader()
# G.Ruban / 21.07.2003  V1.3.8  chkDB2 extended with new checks
# G.Ruban / 13.01.2004  V1.4    Adapted to DB2 V8.1
# G.Ruban / 14.04.2004  V1.4.1  Adaptation to DB2 V8.1 correct for DB2Version+AddressMode
# G.Ruban / 15.06.2010  V1.4.2 LogLineTable set into comments (currently not used)
#-------------------------------------------------------------------------------
# Comments  - $drh = DBI Driver Handle
#             $dbh = DBD Database Handle
#             $sth = SQL Statement Handle
#           - PERL5LIB Environment Variable muss auf Perl Module Pfad zeigen,
#             damit dieses Modul in Perl Scripts eingebunden werden kann.
################################################################################

package xmpPerl;

use 	vars qw(@ISA @EXPORT @EXPORT_OK);
use 	Exporter;
use 	Fcntl;				# V1.3.2 File Constants

use 	strict;				# strikte semantische/syntaktische Prüf.
use     File::Basename;		# V1.3.6

################################################################################
# Konstanten {Prefix lP für alle Package Variablen}
################################################################################
my $lP_VER = "1.4.2";		# Package Version
my $lP_PRG = "xmpPerl.PM";  # Program Name

							# Logging Variablen: ###################
my $lP_LTB = "TLX90000";	# Logging Table               by package
my $lP_LTC = "xmpDBA";		# Logging Table Creator         function
my $lP_LOG = "/opt/dba/log/mydbadmin.log";	# Logging File
my $lP_CTRL= "/opt/dba/ctrl";				# Control File Path

################################################################################
# Export von Variablen (würden die Funktionsnamen ebensfalls aufgeführt, dann
#        könnte in den nutzenden Moduln das Prefix "xmpPerl::" entfallen!
################################################################################
@ISA 	= ('Exporter');
@EXPORT = qw($lP_VER $lP_PRG $lP_LDB $lP_LTB $lP_LTC $lP_LUS $lP_LPW $lP_LOG $lP_CTRL);

################################################################################
# getDBALog: liefert Inhalt der Variablen lP_LOG zurück (Pfad für DBA-Meldungen)
################################################################################
sub getDBALog {
   my $OS = ucfirst $^O;
	if (! defined $ENV{lP_LOG}) {
		if (! defined $ENV{xmpDBADIR})
			{ if ($OS eq "Aix")     { return "/opt/dba/log/mydbadmin.log";}
			  if ($OS eq "Solaris") { return "/opt/dba/log/mydbadmin.log";}
		}
		else { return trim($ENV{xmpDBADIR}."/log/mydbadmin.log") }
	}
	else { return trim($ENV{lP_LOG});
	}
}

################################################################################
# getCTRL: liefert Inhalt der Variablen lP_CTRL zurück (Pfad für Control Files)
################################################################################
sub getCTRL {
   my $OS = ucfirst $^O;
	if (! defined $ENV{lP_CTRL}) {
		if (! defined $ENV{xmpDBADIR})
			{ if ($OS eq "Aix")     { return "/opt/dba/ctrl";}
			  if ($OS eq "Solaris") { return "/opt/dba/ctrl";}
		}
		else { return trim($ENV{xmpDBADIR}."/ctrl") }
	}
	else { return trim($ENV{lP_CTRL});
	}
}

################################################################################
# getDBAUser: liefert Inhalt der Variablen lP_LUS (autorisierte DB2 User) zurück
################################################################################
sub getDBAUser {
	if (! defined $ENV{lP_LUS})
		{ return "";	}
	else
		{ return trim($ENV{lP_LUS});	}
}

################################################################################
# getDBAPsw: liefert Inhalt der Variablen lP_LPW (Password DB2 User) zurück
################################################################################
sub getDBAPsw {
	if (! defined $ENV{lP_LPW})
		{ return "";	}
 	else
		{ return trim($ENV{lP_LPW});	}
}

################################################################################
# getDBALDB: liefert Inhalt der Variablen lP_LDB (DBA Logging Database) zurück
################################################################################
sub getDBALDB {
	if (! defined $ENV{lP_LDB})
		{ return "";	}
	else
		{ return trim($ENV{lP_LDB});	}
}

################################################################################
# trim: Cut-off Leading/Trailing Blanks
################################################################################
sub trim {
  my @out = @_;
	for (@out) {
		s/^\s+//;
		s/\s+$//;
	}
	return wantarray ? @out : $out[0];
}

################################################################################
# doCmdOut: Run External Command and return Output
################################################################################
sub doCmdOut {
  my $output;
	open(xmptmp,"@_ |") or die ">>>Kann externen Command nicht ausführen!\n".
                                   "RC=$!\nCMD=@_\n";
	while() {
		$output .= $_;     		# concat Output Lines
	}
	close(xmptmp);
	return $output;
}

################################################################################
# doDB2CmdOut: Run DB2 Command and return Output, Connect to DB if necessary
################################################################################
sub doDB2CmdOut {
  my ($opt_d, $opt_u, $opt_p, $opt_c);
  my $output;
  my $connect = 0;
  my $work;
	################################################################################
	# get database and db2 command
	################################################################################
	($opt_d , $opt_u, $opt_p, $opt_c ) = @_;
	################################################################################
	# already connected or not
	################################################################################
	$work = &doCmdOut("db2 connect");
	if ( $work =~ "SQLSTATE=08003") {		# connection does not exist
		$work = "db2 connect to $opt_d user $opt_u using $opt_p";
		$output = &doCmdOut($work);
		if ($output !~ "Connection Information") {
			return $output;	}		# connect failed
	}
	else {	$connect = 1 }				# already connected
	################################################################################
	# execute DB2 command
	################################################################################
	$output = &doCmdOut($opt_c);
	################################################################################
	# disconnect, if necessary
	################################################################################
	if ($connect == 1) {$work = &doCmdOut("db2 connect reset")}
	return $output;
}

################################################################################
# getSqllibPath: DB2 Library Path ermitteln
################################################################################
sub getSqllibPath {
  use Env;
  my $work;
  my @work;
  my $LP = "";
	$work = trim($ENV{PATH});		# Environment Variable set by DB2
	@work = split(/:/,$work);		# remove separator
	foreach $work (@work) {			# search list for sqllib
		if ($work =~ /sqllib/) {
		      $LP = $work;
		      last;
		}
	}
	return $LP;				# undefined if not found
}

################################################################################
# getTimestamp: Datum im DB2 Timestamp Format ermitteln
################################################################################
sub getTimestamp {
  my $TS;
	$TS = `date +%Y-%m-%d-%H.%M.%S`;	# yyyy-mm-dd-hh.mm.ss
	$TS = &trim($TS);
	$TS.= ".000000";			# milliseconds = 0
	return $TS;
}

################################################################################
# getTime: Datum im DB2 Timestamp Format ermitteln
################################################################################
sub getTime {
  my ($s,$m,$h);
	($s,$m,$h) = (localtime)[0,1,2];	# Local Time
	$s = sprintf("%02u",$s);		# rechtsbündig mit 0
	$m = sprintf("%02u",$m);		# rechtsbündig mit 0
	$h = sprintf("%02u",$h);		# rechtsbündig mit 0
	return "$h:$m:$s";			# hh:mm:ss
}

################################################################################
# LogLine: Message in DBA Log File und Table schreiben
################################################################################
sub LogLine {
  	&LogLineFile(@_);
#	&LogLineTable(@_);			# inaktiviert 11.07.2000/GR

}

################################################################################
# getDB2AddressMode: Provide DB2 Addressing Mode 23/64 Bit              13.01.04
################################################################################
sub getDB2AddressMode {
  my $V="";
  my $Vl;
  my @VA;

	&chkDB2();				# Check DB2 existence 

  	$V  = &doCmdOut("db2level");
	@VA = split(" ",$V);
	$V  = &trim($VA[5]);			# 6th word
	if ($V =~ "bits") { 			# indicator
		$V  = &trim($VA[4]);		# 5th word
		$Vl = length($V);		# V1.4.1 [moved]
		$V  = substr($V,1,$Vl-2);	# V1.4.1: skip 1, cut off 1(+1)
	}
	else 	{$V = "n/a"		}	# unknown/not available

	#$Vl = length($V);
	#$V  = substr($V,1,$Vl-2);		# skip 1, cut off 1(+1)
	return $V;				# returns >32< or >64< } ################################################################################ # getDB2Version: Provide DB2 Release and Version Information            17.07.01 ################################################################################ sub getDB2Version {   my $V="";   my $Vl;   my @VA;   my $flag = 0;					# V1.4.1 	 	&chkDB2();				# Check DB2 existence V1.3.8 	# db2licm -v works fine with V6 and V7, but not with V5   	$V  = &doCmdOut("db2level"); 	if ($V =~ "bits")	{		# V1.4.1: new with V8.x 		$flag = 1;			# V1.4.1: set flag 	} 	@VA = split(" ",$V);			# split string into words 	 	if ($flag)	{			# V1.4.1: indicates V8.x 		$V  = &trim($VA[19])	}	# 20th word 	else	{ 		$V  = &trim($VA[16])	}	# 17th word 	#$V  = &trim($VA[16]);			# 17th word 	#if ($V !~ "DB2 v") { 			# V1.4 does not contain version prefix 	#	$V  = &trim($VA[19]);		# 20th word 	#} 	$Vl = length($V); 	$V  = substr($V,1,$Vl-3);		# skip 1, cut off 2(+1) 	return $V; } ################################################################################ # LogLineFile: Message in DBA Log File schreiben ################################################################################ sub LogLineFile {     my $hMSGLOG;     				# Log Datei Handle   my $UDT; 	# Auf Message Log schreiben, sofern möglich ############################ 	$UDT = &getTimestamp(); 	$UDT = substr($UDT,0,19); 	if ( open(hMSGLOG, ">> $lP_LOG") ) {
		print hMSGLOG "$UDT @_\n";
		close(hMSGLOG);
		chmod 0664,$lP_LOG;
		return 0;}
	else {  print ">>> $lP_PRG Es kann nicht in $lP_LOG geschrieben werden.\n";
		print ">>> $lP_PRG Letzte Meldung lautete: $UDT @_\n";
		return 8;}
}

################################################################################
# LogLineTable: Message in DBA Log Table schreiben
# Arguments   : (1) Application
#               (2) State
#               (3) Message
################################################################################
#sub LogLineTable {  					# V 1.4.2 deactivated
#
#  # Modules ####################################################################
#  use DBI;
#  use DBD::DB2::Constants;
#  use DBD::DB2;
#  use Sys::Hostname;
#
#  my ($hostname,$udt,$stmt);
#
#  use Getopt::Std;
#  use vars qw($opt_a $opt_s $opt_t $opt_d $opt_u $opt_p);
#
#	# Argumente ############################################################
#	getopts("a:s:t:d:u:p:");
#	if ( (! defined $opt_a )			# Application
#	or   (! defined $opt_s )     			# Severity
#	or   (! defined $opt_d )    			# DBA Database
#	or   (! defined $opt_u )    			# User
#	or   (! defined $opt_p ) )  	{		# Password
#		&LogLineFile(">>> $lP_PRG Problem mit Connect to $opt_d");
#		&LogLineFile(">>> $lP_PRG ".$DBI::errstr);
#		&LogLineFile("@_");
#		return 8;
#	}
#
#	# DBI Switches #########################################################
#	my $switch = DBI->internal;
#	$switch->debug(0);                     # 1=normal debug, 2=detailed handle trace
#
#	# Connect to Database ##################################################
#	my $dbh = DBI->connect("dbi:DB2:$opt_d",
#       	               $opt_u,
#              	       $opt_p,
#	                    { PrintError => 0, # don't print error by default
#        	              RaiseError => 0, # don't print error and die by default
#	                      AutoCommit => 1, # don't commit after each statement
#	                      ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types
#        	            } );
#	if (!$dbh) {
#		&LogLineFile(">>> $lP_PRG Problem mit Connect to $opt_d");
#		&LogLineFile(">>> $lP_PRG ".$DBI::errstr);
#		&LogLineFile("@_");
#		return 8;
#	}
#	else {
#		# Build and Execute SQL Statement ######################################
#		$hostname = hostname();
#		$udt      = &getTimestamp();
#		$stmt 	  = "INSERT INTO $lP_LTC.$lP_LTB
#				(HOST, APPLICATION, STATE,
#	                         MESSAGE, TIMESTAMP)
#                           VALUES ('$hostname','$opt_a', '$opt_s','$opt_t','$udt')";
#		my $sth = $dbh->prepare($stmt);
#		$sth->execute();
#		if ( $dbh->err ) {
#			&LogLineFile(">>> $lP_PRG Problem mit Insert into $opt_d");
#			&LogLineFile(">>> $lP_PRG ".$DBI::errstr);
#			&LogLineFile("@_");
#			return 8;
#		}
#		# Disconnect from Database #####################################
#		$sth->finish();
#		$dbh->disconnect();
#	}
#}

################################################################################
# getProcDescr: zu einer Process ID den Command und Argument verschaffen
################################################################################
sub getProcDescr {
  my $id;
	# execute PS command, ignore Output line containing the word COMMAND ###
	$id = &doCmdOut("ps -p " . getppid . " -f | grep -v CMD");
	$id = "PS=".&trim($id);
	return $id;
}

################################################################################
# getDBMCfgValue: zu einem DBM Config Parameter den Wert ermitteln
################################################################################
sub getDBMCfgValue {
  my $work;
  my @work;
  my $var;
  my $val;
	################################################################################
	# Beschaffen der Datenbank Manager Configurationsvariablen
	################################################################################
	$work = &doCmdOut("db2 get dbm cfg");
	@work = split("\n",$work);
#	print "$#work lines returned from DB2 GET DBM CFG Command.\n";

	foreach $work (@work) {
		($var,$val) = split("=",$work);
		if (! defined $var) {$var = "";}
		if (! defined $val) {$val = "";}
		$var = uc &trim($var);
#		$val = uc &trim($val);
		#######################################################################
		if ($var =~ "@_")       {	# gesuchte Variable gefunden
			$val = &trim($val);	# nochmals Blanks entfernen
			return $val;		# Ja, denn Wert zur’ckgeben
		}
	}
	return;
}

################################################################################
# getDBCfgValue: zu einer Datenbank von einem Config Parameter den Wert ermitteln
################################################################################
sub getDBCfgValue {
  my $work;
  my @work;
  my $var;
  my $val;
  my ($opt_d, $opt_v);
	################################################################################
	# Argument sind Database und Database Cfg Variable (z.B. MAXAPPLS)
	################################################################################
	($opt_d , $opt_v ) = @_;

	################################################################################
	# Beschaffen der Datenbank Configurationsvariablen
	################################################################################
	$work = &doCmdOut("db2 get db cfg for $opt_d");
	@work = split("\n",$work);
#	print "$#work lines returned from DB2 GET DB CFG FOR $opt_d Command.\n";

	foreach $work (@work) {
		($var,$val) = split("=",$work);
		if (! defined $var) {$var = "";}
		if (! defined $val) {$val = "";}
		$var = uc &trim($var);
#		$val = uc &trim($val);
		#######################################################################
		if ($var =~ "$opt_v")       {	# gesuchte Variable gefunden
			$val = &trim($val);	# nochmals Blanks entfernen
			return $val;		# Ja, denn Wert zur’ckgeben
		}
	}
	return;
}

################################################################################
# prtHeaderDB2: Gibt Informationsblock für Scripts mit DB2 Nutzung aus
################################################################################
sub prtHeaderDB2 {
   use Env;
   use Sys::Hostname;
   my $DB2INSTANCE;
   my $DB2Version;						# 17.07.2001
   my $DB2AMode;						# 13.01.2004
   my $UDT;
   my $SSID;
   my $HOST;
   my $OS;

	########################################################################
	# Environment and Shell Variables
	########################################################################
	$UDT         = &trim(getpwuid($>>Cannot locate path to an DB2 Instance e.g. .../sqllib/...\n".
		">>>Please add DB2 instance PATH (see db2profile script).\n".
		">>>Cannot execute DB2 Perl script as requested - ABEND";
	   }
	########################################################################
	# Kann db2 im Pfad lokalisiert werden?
	########################################################################
	if (&doCmdOut("which db2") =~ "no db2 in")
	   {die ">>>Cannot locate 'db2' in current enviroment.\n".
		">>>Please add DB2 instance PATH (see db2profile script).\n".
		">>>Cannot execute DB2 Perl script as requested - ABEND";
	   }
}

################################################################################
# MailText: Sends Text (File) using Recipient (List File)
#           returns recipient(s) ID
################################################################################
sub MailText {
   my  ($opt_a, $opt_v, $opt_t);
   my  ($rlist, $text, $dmr, $dmt);
   my  $tempfile;
   my  $work;
   my  $hFile;
   my  ($pak,$script,$line) = caller(0);

	########################################################################
	# Get arguments: Application Name, Recipient (File), Text (File)
	########################################################################
	($opt_a, $opt_v , $opt_t ) = @_;
	#print "a=$opt_a v=$opt_v t=$opt_t\n";
	if ( (! defined $opt_a )			# Application
	or   (! defined $opt_v ) 			# Recipient (File)
	or   (! defined $opt_t ) 			# Text (File)
	or   ($opt_a eq "" )				# Application
	or   ($opt_v eq "" )	)   {			# Recipient (File)
	   print ">>>xmpPerm.pm: Invalid options specified for function MailText()\n";
	   exit 16;
	}
	########################################################################
	# Recipient is single ID or name of a recipient file
	########################################################################
	$dmr    = "";
	if (-r $opt_v) {
	   $dmr = $opt_v;				# correct file name
	}
	else {
	   #####################################################################
	   # File exists in /opt/dba/ctrl ?
	   #####################################################################
	   if (-r &getCTRL()."/".$opt_v) {		# file in /opt/dba/ctrl?
	      $dmr = &getCTRL()."/".$opt_v;
	   }
	}
	#print "opt_v->$dmr\n";
	########################################################################
	# Text is a string or name of a text file
	########################################################################
	$text = $opt_t;
	$dmt   = "";
	if (-r $opt_t) {
	   $dmt = $opt_t;				# correct file name
	}
	########################################################################
	# Open temporary file to create mail text file, add recipient(s)
	########################################################################
	$script = basename($script);
        $tempfile="/tmp/$script.$$-0";
	#print "==> TEMPFILE=$tempfile\n";

	if (! open(hFile, "> $tempfile") ) {
	   die ">>>$0 cannot write into $tempfile. \n".
		"abends";
	}
	########################################################################
	# Argument contains recipient file name or single recipient mail id
	########################################################################
	if ($dmr ne "") {				# Yes, it is a file
	   #####################################################################
	   # Read recipient id's from file
	   #####################################################################
	   $/ = "\n";					# input record separator
	   if (! open(xmptmp,") {
		next if /#/;				# skip comment line
		next if /^$/;				# skip blank line
		print hFile "$_";			# print line to text file
		$rlist .= $_;				# concat lines
	   }
	   close(xmptmp);
	   if  ($rlist eq "") {
		print ">>>$0 read $dmr successfully\n".
		    ">>>but did not determine any E-Mail ID - please check file!\n".
		    ">>>Program sendmail will fail with 'No recipient addresses found in header'\n";
	   }
	   #####################################################################
	   # Attention: Do not allow blank lines between To:, Cc:, Bcc:, Subject:
	   #####################################################################
	}
	else {
		print hFile "To:$opt_v\n";		# print id to text file
		$rlist = "To:$opt_v";
	}

	########################################################################
	print hFile "Subject: ". uc hostname().": '$opt_a' at ".&getTimestamp()."\n";
	print hFile "-----------------------------------------------------------\n";
	print hFile "Automatic generated Mail Messsage from ...\n";
	print hFile "Host ..........: ". uc hostname()."\n";
	print hFile "Application ...: $opt_a\n";
	print hFile "Created at ....: ".&getTimestamp()."\n";
	print hFile "-----------------------------------------------------------\n";

	########################################################################
	# Text is a string or name of a text file
	########################################################################
	if ($dmt ne "") {				# Yes, it is a file
	   open(xmptmp,") {
	      print hFile "$_";     			# each line
	   }
	   close(xmptmp);
	}
	else {
	   print hFile "$text\n";			# only text passed
	}
	print hFile ".\n";				# end of mail
	close(hFile);					# close tempfile

	########################################################################
	# Send mail using sendmail program
	########################################################################
	system("/usr/lib/sendmail -t < $tempfile"); 	die ">>>xmpPerl.pm: /usr/lib/sendmail -t < $tempfile ends with RC=$?\n" unless $?==0;
	unlink($tempfile);
	return $rlist;
}

1;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

www.ruban.de – DB2 experts web site

Search

DB2 forum

Topics

Ads

Category » code for Linux/Unix/Windows

Shell script xmpDB2All.sh to execute commands at all DB2 instances

Starting and stopping an CLI and JDBC trace in a shell environment

Perl Script xmpTBSCStmt.pl to generate useful tablespace statements

Perl Script xmpTerm.pl to list or terminate DB2 connections

How to retrieve JCC version

Perl Script xmpPerlDB2Test.pl to verify DBI and DBD::DB2 installation

Perl statements to generate (DB2) timestamp

Perl module xmpPerl.pm – function package used by DBA Perl scripts