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 1)
- -p Password (opt. if exists in environment 1)
- -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
1 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-";
}
Comments
Comments are closed.