DB2-Admin
view release on metacpan or search on metacpan
lib/DB2/Admin.pm view on Meta::CPAN
# -AccountingString
#
sub ClientInfo {
my $class = shift;
my %params = validate(@_, { 'Database' => 0,
'ClientUserid' => 0,
'Workstation' => 0,
'Application' => 0,
'AccountingString' => 0,
},
);
my $dbname = delete $params{Database} || '';
my $retval = db2ClientInfo($dbname, \%params);
return %$retval;
}
#
# Backup a database, or specific tablespaces / nodes.
#
# Hash with named parameters, many optional:
# - Database
# - Target: directory/path or ref to array of same (optional for TSM)
# - Tablespaces: optional ref to list of tablespaces
# - Options (hash reference)
# - Type: Full / Incremental / Delta (default Full)
# - Action (Start, NoInterrupt, Continue, Terminate, DeviceTerminate,
# ParamCheck, ParamCheckOnly) (default: NoInterrupt)
# - Nodes: 'All', or reference to list of nodes (optional) (V9.5)
# - ExceptNodes: reference to list of nodes to skip (optional) (V9.5)
# - Online: boolean, default zero (offline)
# - Compress: boolean (default zero)
# - IncludeLogs: boolean (online only)
# - ExcludeLogs: boolean (online only)
# - ImpactPriority (0..100, default 50)
# - Parallelism: 1..1024 (default computed by DB2)
# - NumBuffers (integer, minimum 2)
# - BufferSize (integer, minimum 8)
# - TargetType (optional: Local, XBSA, Snapshot, TSM, Other; default Local)
# - Userid (optional)
# - Password (optional)
#
sub Backup {
my $class = shift;
my %params = validate(@_, { 'Database' => 1,
'Target' => 1,
'Tablespaces' => 0,
'Options' => HASHREF,
},
);
#
# The XS code expects a reference to an array; we support either
# a string or an array, and we translate this at the perl level.
#
unless (ref $params{Target}) {
$params{Target} = [ $params{Target} ];
}
#
# A full backup is indicated by an empty list of tablespaces.
#
$params{Tablespaces} ||= [];
#
# Handle default options
#
my $options = $params{Options};
$options->{Type} ||= 'Full';
$options->{Online} ||= 0;
my $rc = db2Backup($params{Database},
$params{Target},
$params{Tablespaces},
$params{Options},
);
$class->_handle_error("Backup");
return $rc;
}
#------------------------------------------------------------------------
#
# Handle an error: print a warning, die (depending on options)
#
sub _handle_error {
my ($class, $label) = @_;
my $code = sqlcode();
return if ($code == 0); # No error
my $level = ($code > 0 ? 'Warning' :'Error');
my $errmsg = sqlaintp() || '(no error message available)';
$errmsg =~ s!\s*$!!;
my $state = sqlogstt() || '(no state available)';
$state =~ s!\s*$!!;
my $msg = "$level in $label: $errmsg / $state\n";
#
# Handle PrintError/PrintWarning and RaiseError/RaiseWarning
#
if ($options{'Print' . $level}) {
warn $msg;
}
if ($options{'Raise' . $level}) {
die $msg;
}
return $msg;
}
#
# Decode the instance attach information returned by sqleatin,
# used by 'Attach' and 'InquireAttach'.
#
sub _decode_attach_info {
my ($class, $info) = @_;
my $retval = {};
@{$retval}{qw(Country CodePage AuthId NodeName ServerId
AgentId AgentIndex NodeNum Partitions)} =
lib/DB2/Admin.pm view on Meta::CPAN
'ConnectAttr' => { 'ProgramName' => 'myscript', },
);
DB2::Admin::->Disconnect('Database' => 'mydb');
# Get/set connection-level client information
DB2::Admin::->ClientInfo('Database' => 'mydb', 'ClientUserid' => 'remote_user');
%client_info = DB2::Admin::->ClientInfo('Database' => 'mydb');
# Export data. Requires a database connection. Example omits options.
DB2::Admin->Export('Database' => $db_name,
'Schema' => $schema_name,
'Table' => $table_name,
'OutputFile' => "/var/tmp/data-$schema_name-$table_name.del",
'FileType' => 'DEL');
# Import data. Requires a database connection. Example omits options.
DB2::Admin->Import('Database' => $db_name,
'Schema' => $schema_name,
'Table' => $table_name,
'InputFile' => "/var/tmp/data-$schema_name-$table_name.del",
'Operation' => 'Insert',
'FileType' => 'DEL');
# Load data. Requires a database connection. Example omits options.
my $rc = DB2::Admin->Load('Database' => $db_name,
'Schema' => $schema_name,
'Table' => $table_name,
'InputFile' => "/var/tmp/data-$schema_name-$table_name.del",
'Operation' => 'Insert',
'SourceType' => 'DEL');
my $state = DB2::Admin->LoadQuery('Schema' => $schema_name,
'Table' => $table_name,
'LogFile' => $logfile,
'Messages' => 'All');
# Run table statistics. Requires a database connection. Example
# omits options.
$rc = DB2::Admin->Runstats('Database' => $db_name,
'Schema' => $schema_name,
'Table' => $table_name);
# List history. Requires an attachemnet, not a database connection.
@history = DB2::Admin->
ListHistory('Database' => $db_name,
'Action' => 'Load', # Optional; default: all
'StartTime' => '20041201', # Optional; may also specify HHMMSS
'ObjectName' => 'MYSCHEMA.MYTABLE', # Optional
);
# List what utilities are currently running
my @utils = DB2::Admin->ListUtilities();
my @utils = DB2::Admin->ListUtilities('Database' => $db_name);
# Rebind a package. Requires a database connection. Example omits options.
DB2::Admin->Rebind('Database' => $db_name,
'Schema' => $schema_name,
'Package' => $pkg_name);
# Backup a database (or database partition)
DB2::Admin->Backup('Database' => $db_name,
'Target' => $backup_dir,
'Options' => { 'Online' => 1, 'Compress' => 1, });
# Backup all nodes of a DPF database (V9.5 only)
DB2::Admin->Backup('Database' => $db_name,
'Target' => $backup_dir,
'Options' => { 'Online' => 1, 'Nodes' => 'All', });
=head1 DESCRIPTION
This module provides perl language support for the DB2 administrative
API. This loosely corresponds to the non-SQL functions provided by
the DB2 Command Line Processor (CLP), the 'db2' program.
This function is complementary to the DBD::DB2 database driver. The
DBD::DB2 driver is intended for application developers and supports
SQL functions. The DB2::Admin module is intended for administrators and
supports non-SQL database functionality, such as snapshot monitoring,
directory/catalog management, event processing, getting/setting
configuration parameters and data import/export.
This module is incomplete: not all of the DB2 administrative API is
implemented. Features deemed useful will be added over time.
This module provides for two kinds of error handling, which can be
set using the C<SetOptions> method:
=over 4
=item *
Check return value of individual calls. This means all the error
checking is in the application using this module. The module
will print an error message by default, but that can be disabled.
=item *
Have the API throw an exception whenever an error occurs. The exception
can be caught using an C<eval> block if desired.
=back
Many API calls take optional C<Version> and C<Node> parameters. These
have the following meaning:
=over 4
=item Version
The database monitor version, a string in the format
C<SQLM_DBMON_VERSION8>. The default is C<SQLM_CURRENT_VERSION>.
This parameter should only be set if the database that is attached to
is of a lower DB2 release level than the DB2::Admin was compiled for,
e.g. if the DB2::Admin was compiled for DB2 release 8 and the database
attached to is of DB2 release 6.
=item Node
The database node. This can be the string C<SQLM_CURRENT_NODE> (the
default), the string C<SQLM_ALL_NODES>, or a node number.
This parameter should only be set for a partitioned database, and then
only if the API call should affect all database nodes, or a different
node than the one currently attached to.
lib/DB2/Admin.pm view on Meta::CPAN
the client machine from which the Export command is run, and must be
writable by the user issuing the Export command. If the resulting
files are intended to be loaded with the C<Load> command, the
directory name needs to be visible to the target database server - see
the documentation for the C<Load> C<LobPath> parameter for details.
=item LobFile
This optional parameter specifies the filename prefix for LOB files.
It can only be specified if the C<LobsInFile> file modifier and the
C<LobPath> parameter are present.
The C<LobFile> parameter may be a string or a reference to an array of
strings.
=item ExportOptions
This optional parameter is a reference to a hash with export options
and can only be used with DB2 V9.1 or later. The following export
options are defined:
=over 4
=item XmlSaveSchemas
This boolean option determines whether XML schema ids will be included
in the output file or not.
=back
=item XmlPath
This optional parameter can only be used with DB2 V9.1 or later and
specifies the name of a directory where XML data will be stored. This
may be combined with the C<XmlInSepFiles> file option, the
C<XmlSaveSchema> export option and the C<XmlFile> parameter.
The C<XmlPath> parameter may be a string or a reference to an array of
strings. In the latter case, DB2 will stripe XML data across multiple
directories.
The directory name(s) specified must already exist, must be defined on
the client machine from which the Export command is run, and must be
writable by the user issuing the Export command.
=item XmlFile
This optional parameter specifies the filename prefix for XML files.
It can only be specified if the C<XmlPath> parameter is present.
The C<XmlFile> parameter may be a string or a reference to an array of
strings.
=back
=head2 Import
This method is used to import a file into a table. Existing data can
be added to (insert mode), replaced (replace mode), or overwritten on
duplicate keys (insert_update mode). The import functions go through
the transaction log; no tablespace backup is required once the
operation succeeds.
Importing data is less efficient than the C<Load> method. IBM
recommends load over import for more than 50,000 rows or 50MB of data.
At this time, only a limited subset of DB2 import functionality is
supported; specifically, support for table hierarchies and XML
schema-related validation options is not provided. Additional
functionality will be added on request if deemed useful.
This method takes a large set of named parameters and returns a hash
reference with row information on success and C<undef> on failure.
=over 4
=item Database
The database name. This parameter is required. A connection to this
database must exist, i.e. the C<Connect> method must have been called
for this database.
=item Schema
The schema name of the table to import into. This parameter is
required.
=item Table
The name of the table to import into. This parameter is required.
=item TargetColumns
An optional array-reference with the names of the columns to load.
This should correspond to the input file column specification of the
C<InputColumns> parameter.
=item Operation
The import operation. Legal values are:
=over 4
=item Insert
Insert rows into the table, appending to the existing data. Skip rows
with duplicate keys.
=item Insert_Update
Insert rows into the table, appending to the existing data. Row with
duplicate keys replace existing rows.
=item Replace
Replace the contents of the table (i.e. delete all existing rows
before importing the data).
=back
=item FileType
lib/DB2/Admin.pm view on Meta::CPAN
=back
The return value is a hash reference with the following keys:
=over 4
=item RowsRead
=item RowsInserted
=item RowsUpdated
=item RowsRejected
=item RowsSkipped
=item RowsCommitted
=back
=head2 Load
This method is used to load a file into a table. Existing data can be
added to (insert mode) or replaced (replace mode), or overwritten on
duplicate keys (insert_update mode). The load functions do not go
through the transaction log and may not be recoverable (see the long
disclaimer further in this description).
Loading data is more efficient than the C<Import> method, but has a
higher startup cost. IBM recommends load over import for more than
50,000 rows or 50MB of data.
This method is only available for DB2 release 8.2 and higher (the LOAD
functions in previous DB2 releases has a substantially different API,
for which no perl wrapper has been implemented).
At this time, only a limited subset of DB2 load functionality is
supported; specifically, support for TSM media, DataLinks and table
hierarchies is not provided. Additional functionality will be added
on request if deemed useful.
Because the C<Load> functions bypass the transaction log, a loaded
table may not be usable after the load completes, and may not be
available after a database restart - unless the appropriate measures
are taken. Please see the DB2 LOAD documentation for full details. A
short summary (that omits a lot of details and caveats):
=over 4
=item *
Load is not subject to restrictions for databases configured to use
circular logging. Generally, only non-important test databases are
configured with circular logging; most databases have archive logging
enabled.
=item *
If the load is marked as non-recoverable, it is not subject to use
restrictions once the load completes. However, the table will be
unavailable if the database is restarted before a backup is taken.
This is different from Sybase, where the table will be available in
the pre-load state.
=item *
If the load is marked as recoverable (the default), either the loaded
data must be copied by the server (see the C<CopyDirectory> argument),
or a database or tablespace backup must be performed by the DBAs. If
this is not done, the table may be put in a mode where data can be
read but not updated.
=item *
If the load fails, a follow-up command may have to be issues to
continue or terminate the load. This command is I<not> issued
automatically, because there are cases where terminating a partially
failed load will make things worse (e.g. force index rebuilds).
=back
This method takes a large set of named parameters and returns a hash
reference with row information on success (optionally a pair of hash
references with row and DPF information) and C<undef> on failure.
=over 4
=item Database
The database name. This parameter is required. A connection to this
database must exist, i.e. the C<Connect> method must have been called
for this database.
=item Schema
The schema name of the table to load into. This parameter is
required.
=item Table
The name of the table to load into. This parameter is required.
=item TargetColumns
An optional array-reference with the names of the columns to load.
This should correspond to the input file column specification of the
C<InputColumns> parameter.
=item Operation
The load operation. Legal values are:
=over 4
=item Insert
Insert rows into the table, appending to the existing data. Skip rows
with duplicate keys.
=item Replace
Replace the contents of the table (i.e. delete all existing rows
before loading the data). On DB2 V9.5, this has the same effect as
"Replace KeepDictionary".
=item Replace KeepDictionary
This option is only valid on DB2 V9.5. For compressed tables, the
compression dictionary is retained. Unlike DB2 V9.1, a separate reorg
lib/DB2/Admin.pm view on Meta::CPAN
current current statistics settings.
=item ExcludingXML
This boolean option is used to skip collecting statistics on XML
columns.
This option is only available with DB2 V9.1 and later.
=item DefaultFreqValues
This numerical option is used to set the default number of frequent
values for the table. In the full Runstats API, this can be
overridden on a per-column basis, but this implementation does not
support that.
=item DefaultQuantiles
This numerical option is used to set the default number of quantiles
for the table. In the full Runstats API, this can be overridden on a
per-column basis, but this implementation does not support that.
=item ImpactPriority
This numerical option is used to set the impact of runstats. The
priority is between 0 and 100, with 0 being unthrottled and a number
between 1 and 100 indicating a low priority (1) to high priority
(100). The default when this option is omitted is 0 (unthrottled).
=back
=item Columns
This optional parameter contains a hash reference with column names as
keys and options as values. The option can be a non-zero value
(e.g. 1) to indicate the column is of interest, or a hash-reference
with the column options. The only option supported at this time is
'LikeStatistics', but that is expected to change in future DB2
releases. An example 'Columns' value is listed below:
'Columns' => { 'FirstName' => 1, # Collect stats
'LastName' => { 'LikeStatistics' => 1 },
'Salary' => 0, # Don't collect stats - same as omitting
'City' => { 'LikeStatistics' => 1 },
}
=item Indexes
This optional parameter contains an array reference with the name of
the table indexes to be used. Each index name must either be
qualified by a schema name, or must have the same schema specified for
the table.
This parameter should not be combined with the 'AllIndexes' option and
may be combined with the 'DetailedIndexes' or 'SampledIndexes' option.
=back
=head2 ListHistory
This method is used to query the history of backups, roll forwards,
loads, tablespace actions, etc. It applies to a database, but doesn't
require a database connection (just an instance attachment) - IBM is
not very consistent here. This method can be quite slow if selection
criteria are not specified. The selection criteria (action, object
name and start time) are logically ANDed.
This method specifies up to four named parameters, of which only
C<Database> is required. It returns an array with hash-references
describing the history in detail; use of C<Data::Dumper> to study the
results is recommended.
=over 4
=item Database
The database name or alias to list the history for. Required.
=item Action
The history action to list. The default is C<All>. Valid actions
are:
=over 4
=item All
=item Backup
=item RollForward
=item Reorg
=item AlterTablespace
=item DropTable
=item Load
This selects load with and without copy
=item RenameTablespace
=item CreateTableSpace / DropTablespace
Either of these selects both types of events
=item ArchiveLog
=back
=item ObjectName
A filter to select the object of interest. This is either a tablespace
name, or a fully qualified table name (schema + table).
=item StartTime
The date and time of the first history entry of interest. This is
specified in DB2 timestamp format, e.g. <200501311230'. A prefix can
specified, e.g. C<2005> for January 1 of 2004, C<200502> for February
lib/DB2/Admin.pm view on Meta::CPAN
=head2 ClientInfo
This method is used to get or set client information for a connection.
This cannot be used to override the information that the DB2 server
lists for a connection, but it can be used to provided additional
information that is recorded by the audit and monitoring tools. Under
the covers, this method calls the DB2 C<sqleseti> and C<sqleqryi>
functions.
This method takes the following parameters, all optional:
=over 4
=item Database
The database name for which the client information should be set. A
connection to this database must exist, i.e. C<Connect> must have been
called beforehand.
If no database name is provided, the client information applies to all
connections, existing and future, for which no connection-specific
client information has been set.
=item ClientUserid
The client userid. A useful case to set this is when the application
using the DB2::Admin module runs under a generic (production) userid, but
is performing an action for a known human userid. By setting the
ClientUserid option, DB2 monitoring data will list both the generic
and human userids.
Note that setting the ClientUserid does not change any DB2-level
permissioning or authorization. It only provides additional
monitoring information.
=item Workstation
The workstation name. A useful case to set this is when the application
using the DB2::Admin module is part of a three-tier application, and is
performing an action on behalf of a user at a specific known
workstation, e.g. a client desktop name or remote IP address.
=item Application
The application name. A useful case to set this is when the
application using the DB2::Admin module is part of a three-tier
application, and is performing an action on behalf of a known
requesting application, e.g. a specific web or client application.
=item AccountingString
The accounting string.
=back
The return value from this method is a hash with the same four fields,
all of which will be present only if the value is non-empty.
=head2 Backup
This method performs a database backup. For a DPF database, it backs
up the node specified in the C<DB2NODE> environment variable. In DB2
V9.5, it can back up all nodes of a DPF database.
This method takes four named parameters and returns a hash reference,
described in more detail after the parameters.
=over 4
=item Database
The database name or alias. This parameter is required.
=item Target
The database target. This can either be a string (a directory name)
or a reference to an array of directory names. This parameter is
required.
=item Tablespaces
An optional array reference with a lkist of tablespace names to back
up. Specifying this parameter switches from a database backup to a
tablespace backup.
=item Options
A required hash reference with backup options.
=over 4
=item Type
The type of backup. This cna be C<Full>, C<Incremental> or C<Delta>.
=item Action
The backup action. Technically, the abckup cna either eb fully
automated (the default), or it can go through multiple phases:
parameter check, start, promt, continue, etc. This parameter allows
the user to specify the backup type/stage. Supported values are
C<NoInterrupt> (the default), C<Start>, C<Continue>, C<Terminate>,
C<DeviceTerminate>, C<ParamCheck> and C<ParamCheckOnly>.
=item Nodes
This parameter is only valid on DB2 V9.5 and only for DPF databases.
It can be C<All> for a system-wide backup of all DPF nodes, or a
reference to an array of node numbers to back up. Use of this
parameter triggers the creation of the C<NodeInfo> field in the return
value. It is mutually exclusive with the C<ExceptNodes> parameter.
=item ExceptNodes
This parameter is only valid on DB2 V9.5 and only for DPF databases.
It is reference to an array of node numbers I<not> to back up. Use of
this parameter triggers the creation of the C<NodeInfo> field in the
return value. It is mutually exclusive with the C<Nodes> parameter.
=item Online
A boolean option specifying an online or offline backup. The default
is an offline backup.
=item Compress
A boolean option specifying whether to compress the backup. The
default is a non-compressed backup.
=item IncludeLogs
A boolean option specifying that database logs must be included. This
parameter is mutually exclusive with the C<ExcludeLogs> option.
Omitting both C<IncludeLogs> and C<ExcludeLogs> selects the default
for the backup type, which is to include logs for snapshot backups and
to exclude logs in all other cases.
=item ExcludeLogs
A boolean option specifying that database logs must be excluded. This
parameter is mutually exclusive with the C<IncludeLogs> option.
Omitting both C<IncludeLogs> and C<ExcludeLogs> selects the default
for the backup type, which is to include logs for snapshot backups and
to exclude logs in all other cases.
=item ImpactPriority
An integer specifying the impact priority. When omitted, the backup
runs unthrottled.
=item Parallelism
An integer specifying the degree of parallelism (number of buffer
manipulators).
=item NumBuffers
An integer specifying the number of backup buffers to be used.
=item BufferSize
An integer specifying the size of the abckup buffer in 4K pages.
=item TargetType
The backup target type. The default is C<Local>, i.e. a backup to a
filesystem. Other options are C<XBSA>, C<TSM>, C<Snapshot> and
C<Other>.
=item Userid
An optional connect userid.
=item Password
An optional password to be used with the connect userid.
=back
=back
The return value of the C<Backup> method is a reference to a hash with
the following entries:
=over 4
=item ApplicationId
=item Timestamp
=item BackupSize
The size of the backup in megabytes
=item SQLCode
=item Message
The error message if the SQL code is not zero
=item State
The description if the SQL state, if available
=item NodeInfo
An optional array reference with per-node information. This is only
available for DPF databases where the C<Nodes> or C<ExceptNodes>
option was specified. Each array element is a hash reference with the
following elements (C<Message> and C<State> are optional):
=over 4
=item NodeNum
=item BackupSize
=item SQLCode
=item Message
=item State
=back
=back
=head1 AUTHOR
Hildo Biersma
=head1 SEE ALSO
DB2::Admin::Constants(3), DB2::Admin::DataStream(3)
=cut
( run in 0.479 second using v1.01-cache-2.11-cpan-e1769b4cff6 )