DB2::Admin - Support for DB2 Administrative API from perl
use DB2::Admin; DB2::Admin::->SetOptions('RaiseError' => 1); DB2::Admin::->Attach('Instance' => 'FOO'); # Monitor switches and snapshot DB2::Admin::->SetMonitorSwitches('Switches' => { 'Table' => 1, 'UOW' => 0, }); my $retval = DB2::Admin::->GetSnapshot('Subject' => 'SQLMA_APPLINFO_ALL'); DB2::Admin::->ResetMonitorSwitches(); # Database manager configuration parameters my @options = DB2::Admin::-> GetDbmConfig('Param' => [ qw(maxagents maxcagents) ]); print "Max agents: $options[0]{Value}\n"; print "Max coord agents: $options[1]{Value}\n"; DB2::Admin::->UpdateDbmConfig('Param' => [ { 'Name' => 'jdk11_path', 'Value' => '/opt/ibm/db2/...', }, { 'Name' => 'intra_parallel', 'Value' => 1, }, ], 'Flag' => 'Delayed'); # Database configuration parameters @options = DB2::Admin::->GetDatabaseConfig('Param' => [ qw(dbheap logpath) ], 'Flag' => 'Delayed', 'Database' => 'sample', ); print "Database heap size: $options[0]{Value}\n"; print "Path to log files: $options[1]{Value}\n"; DB2::Admin::->UpdateDatabaseConfig('Param' => { 'Name' => 'autorestart', 'Value' => 0, }, 'Database' => 'sample', 'Flag' => 'Delayed'); DB2::Admin::->Detach(); # Database, node and DCS directories - no attach required my @db_dir = DB2::Admin::->GetDatabaseDirectory(); my @db_dir = DB2::Admin::->GetDatabaseDirectory('Path' => $dbdir_path); my @node_dir = DB2::Admin::->GetNodeDirectory(); my @dcs_dir = DB2::Admin::->GetDCSDirectory(); # Catalog or uncatalog a database DB2::Admin::->CatalogDatabase('Database' => 'PRICES', 'Alias' => 'TESTPRI', 'NodeName' => 'TESTNODE', 'Type' => 'Remote'); DB2::Admin::->UncatalogDatabase('Alias' => 'TESTPRI'); # Catalog or uncatalog a node DB2::Admin::->CatalogNode('Protocol' => 'TCP/IP', # Or SOCKS/Local 'NodeName' => 'TESTNODE', 'HostName' => 'testhost.example.com', 'ServiceName' => 3700); # Service name or port number DB2::Admin::->UncatalogNode('NodeName' => 'TESTNODE'); # Catalog or uncatalog a DCS database DB2::Admin::->CatalogDCSDatabase('Database' => 'PRICES', 'Target' => 'DCSDB'); DB2::Admin::->UncatalogDCSDatabase('Databases' => 'PRICES'); # Force applications - attach required. Use with care. DB2::Admin::->ForceApplications(@agent_ids); DB2::Admin::->ForceAllApplications(); # Connect to database / Disconnect from database DB2::Admin::->Connect('Database' => 'mydb', 'Userid' => 'myuser', 'Password' => 'mypass'); DB2::Admin::->SetConnectAttributes('ConnectTimeout' => 120); DB2::Admin::->Connect('Database' => 'mydb', 'Userid' => 'myuser', 'Password' => 'mypass', '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', });
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 SetOptions method:
SetOptions
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.
Have the API throw an exception whenever an error occurs. The exception can be caught using an eval block if desired.
eval
Many API calls take optional Version and Node parameters. These have the following meaning:
Version
Node
The database monitor version, a string in the format SQLM_DBMON_VERSION8. The default is SQLM_CURRENT_VERSION.
SQLM_DBMON_VERSION8
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.
The database node. This can be the string SQLM_CURRENT_NODE (the default), the string SQLM_ALL_NODES, or a node number.
SQLM_CURRENT_NODE
SQLM_ALL_NODES
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.
The methods below are all intended for use by applications. The underlying low-level functions in the XS module are not documented.
This method is used to set the options that determine how the DB2::Admin module performs error-handling. It takes a hash with option names and option values and uses these to change the options in effect. A hash with the full set of options is returned.
At this time, four options are defined, named after DBI connect options:
DBI
When an error occurs, write it to STDERR using warn. This option is on by default.
warn
When a warning occurs, write it to STDERR using warn. This option is on by default.
When an error occurs, generate an exception using die. This option is off by default.
die
When a warning occurs, generate an exception using die. This option is off by default.
This method is used to set default connect attributes. (These attributes can also be specified on the <Connect> call.) It takes a hash with connect attribute names and values and uses these to change the connect attributes in effect. A hash with the full set of connect attributes is returned.
At this time, two options are defined, named after <db2cli.ini> keywords:
The name under which the database connection will be listed in the DB2 "list applications" command, DB2 snapshots, etc. The default is the perl script name (the basename of $0).
$0
The connect (login) time-out, in seconds. The default is 60 seconds.
This method is used to attach to a database instance. If you need to attach to a remote instance, or need to provide a userid or password, this method must be called before any other API function (except SetOptions). If you attach to a local instance, this call can be omitted; the first call to an API function will perform an implicit local attach.
This method takes three optional named parameters:
The name of the instance to attach to. If omitted, the environment variable DB2INSTANCE must be set and will determine the instance instead.
DB2INSTANCE
The userid used to attach.
The password used to attach.
If Attach succeeds, it returns a hash reference with information on the instance attached to, in the same format as the InquireAtatch method. If Attach fails, it returns undef.
Attach
InquireAtatch
undef
This method describes the instance attached to. On success, it returns a hash reference with the following fields:
This method detaches from the database instance. It returns a boolean to indicate whether the operation succeeded.
This method is used to connect to a database.
A database connection is required for a small subset of functions provided by this module, most notably the Import and Export functions. For those developers used to the perl DBI, it is noteworthy that there is no <dbh> object: a database connection is not an input parameter to those functions. All that is required is that a database connection exists and that the database name is provided.
Import
Export
This method takes one required named parameter, Database, and three optional named parameters, Userid, Password and ConnectAttr. Inside the module the database connections are stored in a hash indexed by database name. If the same database is opened twice without a Disconnect call, a warning will be issued and the old database handle will be closed before a new one is created.
Database
Userid
Password
ConnectAttr
Disconnect
Up to 512 database connections to different databases can be made at the same time. The functions requiring database connections will automatically switch between these connections.
The optional ConnectAttr parameter is a referenece to a hash with connect attributes and overrides the defaults specified with the SetConnectAttributes method.
SetConnectAttributes
This method is used to disconnect from a database. It has one mandatory named parameter, Database.
If this method is not called before program termination, the END block in the DB2::Admin module will automatically disconnect from all databases and will issue a warning while doing so.
END
DB2::Admin
This method returns the monitor switches in effect for the current application. In the absence of a SetMonitorSwitches call, the monitor switches will be inherited from the database configuration. The monitor switches will affect the data returned by a GetSnapshot call.
SetMonitorSwitches
GetSnapshot
This method takes two optional named parameters:
On success, this method returns a hash with the keys listed below. The value will be 0 or 1, indicating whether the monitor is in effect or not. The same keys can be used for the SetMonitorSwitches method.
This method sets the monitor switches in effect for the current application. This will affect the data returned by a GetSnapshot call.
This method takes one required and two optional named parameters:
A reference to a hash with the switches that should be enabled or disabled. Any switch option not named will be kept at the current value. See the GetMonitorSwitches method for a list of switch names supported.
GetMonitorSwitches
The return value for this method is the list of switches that was in effect before the SetMonitorSwitches call, in the same format as returned by the GetMonitorSwitches method.
This method will reset the monitor data (e.g. counters) in effect for the current application. It can do so globally (for all active databases) or for a particular database.
The name of a database or alias to reset the monitor data for. In the absence of this parameter, monitor data will be reset for all active databases.
This method performs a database snapshot and returns the collected snapshot data. It can collect data in one or more monitoring areas, then returns a hash reference with decoded snapshot results.
This method takes the following named parameters, of which only Subject is required:
Subject
The area to be monitored. This can be either a single value, or a reference to an array of values. Each value can be a string with an object type, like SQLMA_APPLINFO_ALL, or a reference to an hash that contains a type, optional agent id, and optional object name.
SQLMA_APPLINFO_ALL
For example, to get lock snapshot data for databases 'FOO' and 'BAR', call this method with the following Subject parameter:
'Subject' => [ { 'Type' => 'SQLMA_DBASE_LOCKS' }, 'Object' => 'FOO' }, { 'Type' => 'SQLMA_DBASE_LOCKS' }, 'Object' => 'BAR' }, ];
To get lock snapshot data for a particular agent id, call this method with the following Subject parameter:
'Subject' => { 'Type' => 'SQLMA_APPL_LOCKS_AGENT_ID' }, 'AgentId' => 12345, },
In all cases, the Type is required, and Object and AgentId are optional and mutually exclusive.
Type
Object
AgentId
The snapshot class. This is a string that can be SQLM_CLASS_DEFAULT (a normal snapshot, which is the default), SQLM_CLASS_HEALTH, or SQLM_CLASS_HEALTH_WITH_DETAIL.
SQLM_CLASS_DEFAULT
SQLM_CLASS_HEALTH
SQLM_CLASS_HEALTH_WITH_DETAIL
Health snapshots are only available with DB2 release 8 or higher, and if the health monitor is active.
This boolean parameter indicates whether the snapshot results are to be stored at the DB2 server for viewing using SQL table functions. This is false by default.
The return value from this method is a reference to a hash with data in the DB2::Admin::DataStream format. When developing new applications, users are recommended to use the Data::Dumper module to study the output format.
DB2::Admin::DataStream
Data::Dumper
When called in array context, this function returns both the parsed data in DB2::Admin::DataStream format and the original binary data. This can be used to save the binary data for debugging or later analysis.
This method is used to inquire database manager configuration parameters. The parameters supported are taken from a configuration file, DB2::Admin/db2_config_params.pl, which is currently known to be incomplete and is extended on an as-needed basis.
DB2::Admin/db2_config_params.pl
This method takes the following named parameters:
The name of the configuration parameter; optionally, a reference to an array of configuration parameters. The names are case-insensitive.
An optional parameter that specifies where to get the configuration parameters. It can be set to Immediate, Delayed and Defaults. In the absence of this parameter, DB2 defaults to Immediate. If multiple flag values need to be combined (e.g. Delayed + Defaults), a hash-reference with the flag names as keys and a true value can be specified.
Immediate
Delayed
Defaults
The return value is an array of results, each a hash reference with Name and Value fields, and an optional Automatic or Computed field if the database manager configuration parameter is set automatically. The order of the results matches the order specified in the Name parameter.
Name
Value
Automatic
Computed
This method is used to update database manager configuration parameters. The parameters supported are taken from a configuration file, DB2::Admin/db2_config_params.pl, which is currently known to be incomplete and is extended on an as-needed basis.
A hash-reference with the fields Name, Value and optionally an entry-level flag (Automatic, Computed or Manual, see below).
Manual
Optionally, a reference to an array of hash-references of the same structure.
The Name field is case-insensitive. The Value field is required when the Flag is Immediate or Delayed, but not allowed when the Flag is Reset.
Flag
Reset
The entry-level flags are:
Let DB2 set the value automatically. The value specified in this call is accepted but will be overriden by DB2.
Let DB2 set the value once at start-up. The value specified in this call is accepted but will be overriden by DB2. This can only be used in DB2 V9.1 and then only for specific parameters such as 'database_memory' - see the DB2 documentation for details.
Keep the value computed by DB2 and switch to manual configuration, but don't override the current computed value. The value specified in this call is ignored. This can only be used in DB2 V9.1.
An optional parameter that specifies where to set the configuration parameters. It can be set to Immediate, Delayed and Reset. In the absence of this parameter, DB2 defaults to Immediate. If multiple flag values need to be combined (e.g. Reset + Immediate), a hash-reference with the flag names as keys and a true value can be specified.
WARNING: if a configuration parameter is only set immediately, and no separate call is made to set the delayed value, it may be lost when a new DB2 process is started.
This method returns true on success and false on failure.
This method is used to inquire database manager configuration parameters. The parameters supported are taken from a configuration file, DB2::Admin/db2_config_params.pl, which is complete for database configuration parameters up to DB2 release V9.7.
Querying delayed and default database parameters does not require an instance attach or database connection. Querying current database parameters (the 'Immediate' flag) requires a database connection has been established.
An optional parameter that specifies where to get the configuration parameters. It can be set to Immediate (the DB2 default), Delayed or Defaults. If multiple flag values need to be combined (e.g. Delayed + Defaults), a hash-reference with the flag names as keys and a true value can be specified.
The return value is an array of results, each a hash reference with Name and Value fields, and an optional Automatic field if the database configuration parameter is set automatically. The order of the results matches the order specified in the Name parameter.
This method is used to update database manager configuration parameters. The parameters supported are taken from a configuration file, DB2::Admin/db2_config_params.pl, which is complete for database configuration parameters up to DB2 release V9.7.
Updating delayed and default database parameters does not require an instance attach or database connection. Updating current database parameters (the 'Immediate' flag) requires a database connection has been established.
An optional parameter that specifies where to get the configuration parameters. It can be set to Immediate (the DB2 default), Delayed and Reset. If multiple flag values need to be combined (e.g. Reset + Delayed), a hash-reference with the flag names as keys and a true value can be specified.
This method does not require an instance attachment. It queries the database directory and returns an array of hash-references, each with fields like Database, Alias and Type. The fields available depends on the entry in the database directory; blank fields are not present in the hash. The names of the fields match those in the CatalogDatabase method used to add new entries to the database directory.
Alias
CatalogDatabase
This method takes one optional named parameter, Path. When omitted, the system database directory is retrieved.
Path
This method adds a new database to the database directory. No instance attachment or database connection is required.
This method takes named parameters that match the values returned by the GetDatabaseDirectory method:
GetDatabaseDirectory
The database alias name. This parameter is required. The database alias must be unique within the database directory.
The database name. This parameter is required.
This parameter is optional and used for remote databases. This should match an entry in the node directory.
This parameter is optional and used for locally cataloged databases.
This parameter is optional and provides a comment describing the database.
This parameter is required and describes the database type. The following values are supported:
This parameter is optional and used to describe the database authentication. Doing so is optional: when omitted (or set to the default of "Not specified"), the DB2 client will ask the server for its desired authentication method as part of the connection handshake. Setting the authentication in the database to a value conflicting with that at the database server will cause the client to fail to connect.
The following values are supported:
The Kerberos principal for the database, if Kerberos authentication is used.
This method removes an entry from the database directory. It takes one named parameter, Alias.
This method does not require an instance attachment. It queries the node directory and returns an array of hash-references, each with fields like HostName, NodeName and Protocol. The fields available depends on the entry in the node directory; blank fields are not present in the hash.
HostName
NodeName
Protocol
This method does not take any parameters.
This method adds a new node to the node directory. No instance attachment or database connection is required.
This method takes named parameters that match the values returned by the GetNodeDirectory method:
GetNodeDirectory
The node name. This parameter is required. The node alias must be unique within the node directory.
This parameter is optional and provides a comment describing the node.
This parameter is required and describes the protocol used to connect to the database. Only a subset of node types is supported: TCP/IP (including v4 and v6), SOCKS (including v4), and Local. The protocol can be specified in the same format as returned by getNodeDirectory or by a shorter name. The values supported are:
getNodeDirectory
Alias for TCPIP matching GetNodeDirectory
TCPIP
Alias for TCPIP4 matching GetNodeDirectory; only on DB2 V9.
TCPIP4
Alias for TCPIP6 matching GetNodeDirectory; only on DB2 V9.
TCPIP6
Alias for SOCKS4 matching GetNodeDirectory; only on DB2 V9.
SOCKS4
Alias for Local matching GetNodeDirectory
Local
This parameter is required for TCP/IP and SOCKS nodes and describes the hostname of the remote database.
This parameter is required for TCP/IP and SOCKS nodes and describes the port number or service name of the remote database.
This parameter is required for Local IPC nodes and describes the instance name.
This method removes an entry from the node directory. It takes one named parameter, NodeName.
This method does not require an instance attachment. It queries the DCS (gateway) directory and returns an array of hash-references, each with fields like Database, Target and Library. The fields available depends on the entry in the DCS directory; blank fields are not present in the hash.
Target
Library
This method adds a new DCS database to the DCS directory. No instance attachment or database connection is required.
This method takes named parameters that match the values returned by the GetDCSDirectory method:
GetDCSDirectory
The local database name. This parameter is required. The database name must be unique within the DCS directory.
The target database name. This parameter is required.
This parameter is optional and describes the application requester library to be used. When omitted, DB2 connect will be used.
This parameter is optional and contains connect options for the DCS database.
This parameter is optional and provides a comment describing the DCS database.
This method removes an entry from the DCS directory. It takes one named parameter, Database.
This method forces selected applications (specified by agent id) that are connected to the instance. Applications are forced asynchronously; please see the documentation for the sqlefrce API call for limitations and implementation.
sqlefrce
This method takes an array of numeric agent ids and returns a boolean. Note that the underlying API sometimes returns success even if one or more agent ids were invalid and could not be forced.
Invoking this method may be career suicide when used on production instances. Use with care.
This method forces all applications connected to the instance. Applications are forced asynchronously; please see the documentation for the sqlefrce API call for limitations and implementation.
This method takes no parameters and returns a boolean.
This method is used to export table data to a file. At this time, only a limited subset of DB2 export functionality is supported; specifically, support for column renames and table hierarchies is not provided. Additional functionality will be added on request if deemed useful.
This method takes a large set of named parameters and returns an integer with the number of rows exported on success and -1 on error.
The database name. This parameter is required. A connection to this database must exist, i.e. the Connect method must have been called for this database.
Connect
The schema name of the table to export. This parameter is required.
The name of the table to export. This parameter is required.
An optional parameter with an array-reference of the columns to be exported.
An optional parameter with the WHERE clause selecting the data to be exported. The WHERE keyword itself should not be included in this parameter. Placeholders in the DBI fashion are not supported; all selection values must be literals and strings must be quoted properly.
An optional parameter with SELECT clauses that follow the WHERE clause, i.e. optional ORDER BY, GROUP BY, HAVING, FETCH, and ISOLATION clauses. Placeholders are not supported.
This parameter is mandatory and specifies the type of output file: DEL for delimited files (CSV-style) or IXF for IXF files.
DEL
IXF
An optional parameter with a hash-reference of file export options. At this time, the options below are supported, all of which apply only to the DEL file type unless otherwise mentioned.
The delimiter around string fields.
The code page (character set) modifier, e.g. 819 or 1208.
The column delimiter.
Write out dates in ISO format, i.e. YYYY-MM-DD.
Replace the leading + before a decimal number by a blank
This option can be used with both IXF and DEL files. It specifies that the file will contain references to external file(s) with LOB information. This parameter must be combined with the LobPath parameter.
LobPath
Don't write delimiters around character fields. Note that DB2 will not be able to import the data unless the NoCharDel option is specified for the import or load operation -- use this only for export to other databases or products.
NoCharDel
Strip leading zeros before numbers
The timestamp format.
This option is relevant for DB2 V9.1 and later and applies to export of files with XML data. It needs to be combined with the XmlPath option.
The boolean XmlInSepFiles option determines whether each XML document (contents of an XML column in a single record) is written to a separate file, or whether all such XML data is written to a single file. The default is false (write all XML data to a single file).
This mandatory parameter specifies the name of the output file.
This optional parameter specifies the name of the error log file. This file is appended to when it already exists. If omitted, the log goes to /dev/null or nul:.
/dev/null
nul:
This optional parameter specifies the name of a directory where LOBs are stored. This must be combined with the LobsInFile file option and may be combined with the LobFile parameter.
LobsInFile
LobFile
The LobPath parameter may be a string or a reference to an array of strings. In the latter case, DB2 will stripe LOBs 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. If the resulting files are intended to be loaded with the Load command, the directory name needs to be visible to the target database server - see the documentation for the Load LobPath parameter for details.
Load
This optional parameter specifies the filename prefix for LOB files. It can only be specified if the LobsInFile file modifier and the LobPath parameter are present.
The LobFile parameter may be a string or a reference to an array of strings.
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:
This boolean option determines whether XML schema ids will be included in the output file or not.
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 XmlInSepFiles file option, the XmlSaveSchema export option and the XmlFile parameter.
XmlInSepFiles
XmlSaveSchema
XmlFile
The 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.
XmlPath
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.
This optional parameter specifies the filename prefix for XML files. It can only be specified if the XmlPath parameter is present.
The XmlFile parameter may be a string or a reference to an array of strings.
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 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 undef on failure.
The schema name of the table to import into. This parameter is required.
The name of the table to import into. This parameter is required.
An optional array-reference with the names of the columns to load. This should correspond to the input file column specification of the InputColumns parameter.
InputColumns
The import operation. Legal values are:
Insert rows into the table, appending to the existing data. Skip rows with duplicate keys.
Insert rows into the table, appending to the existing data. Row with duplicate keys replace existing rows.
Replace the contents of the table (i.e. delete all existing rows before importing the data).
This parameter is mandatory and specifies the type of input file: DEL for delimited files (CSV-style) or IXF for IXF files.
An optional parameter with a hash-reference of file import options (describing the input file, not the import operation). At this time, seven generic options are supported for all file types and two options are supported for the DEL file type.
The delimiter around string fields (DEL files only).
The column delimiter (DEL files only).
The format for date values (DEL files only). See the IBM documentation for details. A useful value to import Sybase-generated files with a date format like 'Apr 5 2005' is
'DateFormat' => 'MMM DD YYYY'
For DEL files: support embedded newlines in column values
A flag indicating the position of the decimal point is implied (DEL files only)
Keep leading and trailing blanks for character fields (DEL files only)
Don't assume delimiters around character fields (DEL files only). This should be used only for import from other databases or products.
A flag indicating that trailing blanks need to be stripped. Yes, this flag has an ugly name - it really is spelled StripTBlanks.
StripTBlanks
The time format (DEL files only)
The format for date/time values (DEL files only). See the IBM documentation for details. A useful value to import Sybase-generated files with a timestamp format like 'Apr 5 2005 11:59:59:000PM' is
'TimestampFormat' => 'MMM DD YYYY HH:MM:SS:UUUTT'
This mandatory parameter specifies the name of the input file.
This optional parameter is an array-reference that indicates which of the columns in the input file should be used for import. For IXF files, this is an array of column names, selecting which columns from the file are of interest. For DEL files, this is an array of column positions (starting at 1).
For example, if a DEL files contains 5 columns, and the second column must be skipped, specify:
InputColumns => [ 1, 3, 4, 5 ]
The related TargetColumns parameter allows you to specify which column names in the target table are to be loaded.
TargetColumns
An optional hash reference with import options (those affecting the import operation itself, not describing the input file).
The maximum number of rows to import
The number of rows to skip before starting; intended for use after a previous import operation failed partway through.
Functionally identical to RestartCount
RestartCount
How often import should commit. The default is 'Automatic'.
The maximum number of warnings before ending the import. The default is 0 (infinite).
A boolean parameter indicating whether the locktimeout parameter should be honored. When true, or if this option is omitted, lock timeouts are respected; when set to false, there is no timeout.
locktimeout
A string indicating the access level allowed while the import is in progress. The default is 'None' (import locks the table exclusively); the other allowed option is 'Write'.
A string indicating the way XML data should be parsed. Supported values are 'Preserve' (also 'PreserveWhitespace') and 'Skip' (also 'SkipWhitespace'). This option can only be specified with DB2 V9.1 and later.
This optional parameter specifies the name of a directory where LOBs are stored. This must be combined with the LobsInFile file option.
The LobPath parameter may be a string or a reference to an array of strings. It must match the LobPath parameter specified for the Export command that generated the data and LOB files.
The directory name(s) specified must already exist, must be defined on the client machine from which the Import command is run, and must be readable by the user issuing the Import command.
This optional parameter specifies the name of a directory where XML data is stored. This parameter is only valid in DB2 V9.1 and later.
The XmlPath parameter may be a string or a reference to an array of strings. It must match the XmlPath parameter specified for the Export command that generated the data and XML files.
The return value is a hash reference with the following keys:
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 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 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):
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.
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.
If the load is marked as recoverable (the default), either the loaded data must be copied by the server (see the 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.
CopyDirectory
If the load fails, a follow-up command may have to be issues to continue or terminate the load. This command is not issued automatically, because there are cases where terminating a partially failed load will make things worse (e.g. force index rebuilds).
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 undef on failure.
The schema name of the table to load into. This parameter is required.
The name of the table to load into. This parameter is required.
The load operation. Legal values are:
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".
This option is only valid on DB2 V9.5. For compressed tables, the compression dictionary is retained. Unlike DB2 V9.1, a separate reorg step is no longer required.
This option is only valid on DB2 V9.5. For compressed tables, a new compression dictionary is calculated. Unlike DB2 V9.1, a separate reorg step is no longer required.
Restart a previously partially completed load.
Terminate a previously partially completed load.
This parameter is mandatory and specifies the type of input data: DEL for delimited files (CSV-style), IXF for IXF files, SQL or Statement for a SQL statement. Note that DB2 does not support loading IXF files into DPF databases.
SQL
Statement
For data loaded from file (DEL / IXF), indicates whether the data is readable on the database server (Server) or only available on a remote client (Client). When omitted, this parameter defaults to the safe value of Client.
Server
Client
Specify Server when the load is invoked on the database server, or when the file is available on a network drive that has the same pathname on client machine and server host.
An optional parameter with a hash-reference of file load options (describing the input file, not the load operation). Please see the DB2 documentation for the meaning of these options; this documentation just lists them.
First, generic options for both IXF and DEL files:
Next, the options for DEL files:
The format for date values. See the IBM documentation for details. A useful value to load Sybase-generated files with a date format like 'Apr 5 2005' is
Support embedded newlines in column values
The name of the file to write records from the input file that cannot be parsed. This file is server-side, so for loads from the client you want to make sure to specify a filename on network filesystem that is visible to both client and server machine. See also the 'DumpFileAccessAll' parameter.
NOTE: the dumpfile may have at most one file extension, i.e. 'LOAD.FILE' is legal but 'LOAD.DUMP.FILE' is not. This restriction is imposed by DB2, not the perl API.
This boolean parameter can only be specified when 'DumpFile' is present. It indicates that the dumpfile should be globally readable. The default is to make the dump file readable only by the database server instance userid and the DB2 administrators group.
Don't assume delimiters around character fields (DEL files only). This should be used only for load from other databases or products.
The format for date/time values. See the IBM documentation for details. A useful value to load Sybase-generated files with a timestamp format like 'Apr 5 2005 11:59:59:000PM' is
'TimestampFormat' => 'MMM DD YYYY HH:MM:S:UUUTT'
Finally, the options for IXF files:
This parameter is required for IXF and DEL files and specifies the name of the input file.
For DEL files, you can specify either a string (one file) or a reference to an array of strings (multiple files).
For IXF files, you can only specify a string (one file).
This parameter is required for SQL statements and specifies the SELECT statement to read the data to be loaded.
This optional parameter is an array-reference that indicates which of the columns in the input file should be used for loading. For IXF files, this is an array of column names, selecting which columns from the file are of interest. For DEL files, this is an array of column positions (starting at 1).
For a recoverable load, the load functions can make a copy of the parsed input data on the database server (in internal DB2 format) before performing the load operation. Even though the loaded data is not in the transaction log, the database can recover the table by re-loading the copied files.
This parameter specify the server-side directory wheres such copy files will be stored. Always pick such a directory in conjunction with your DBA.
Morgan Stanley note: The Sybase::Xfer equivalent should pick this for the user according to a rule specified by the DBA, and this parameter should be a boolean: make a copy yes/no.
If you are loading into a partitioned (DPF) database, this file will be the basename; additional details will be found in files with the partition number and load phase appended. For example, if you specify the logfile '/var/tmp/load.out', additional log files will have names of the format '/var/tmp/load.out.<phase>.<partition>'.
This optional parameter specifies the name of the directory, on the database server, where the load operation will store temporary files (messages, consistency points, delete phase information). It can be safely omitted, in which case the database server will use a default directory for this.
An optional hash reference with load options (those affecting the load operation itself, not describing the input file). Please see the DB2 documentation for the meaning of these options; this documentation just lists them.
The maximum number of rows to load.
The tablespace to use to rebuild the index(es).
The number of rows to load before establishing a consistency point from which the load can be restarted.
Boolean
Boolean. The default is false (recoverable).
Legal values are:
This parameter determines whether to colelct statistics during load. This requires that a runstats profile has been previously set up for the table. Legal values are:
A string indicating the way XML data should be parsed. Supported values are 'Preserve' (also 'PreserveWhitespace') and 'Skip' (also 'SkipWhitespace'). This option can only be specified with DB2 V9.5 and later.
An optional hash reference with DPF (partitioned database) load options (those affecting the DPF aspects of the load operation itself, not describing the input file). Please see the DB2 documentation for the meaning of these options; this documentation just lists them.
The presence of this hash reference also triggers the extended return value (described below). In cases where you want to have the extended return value but do not want to set DPF options, just pass an empty hash reference.
An array reference with database partition numbers
Integer
This can have the following string values:
Integer.
An array reference with two port numbers
This optional parameter determines the schema name for the exception table (set by the 'ExceptionTable' parameter). If omitted, the default is to use the 'Schema' parameter.
This optional parameter determines the exception table. Rows that can be loaded into the table but violate index or foreign key constraints will be stored into this table. See also the 'ExceptionSchema' parameter.
The directory name(s) specified must be visible to the database server machine, and both the directory and the files it contains must be readable by the userid under which the database server is running. This generally means the LOB path should be on a network share (NFS) visible to both the client machine running the Export and the database server handling the Load; it may also require that the permission for LOB files be changed to world-readable.
This optional parameter specifies the name of a directory where XML data is stored. This parameter is only valid in DB2 V9.5 and later.
The directory name(s) specified must be visible to the database server machine, and both the directory and the files it contains must be readable by the userid under which the database server is running. This generally means the XML path should be on a network share (NFS) visible to both the client machine running the Export and the database server handling the Load; it may also require that the permission for XML files be changed to world-readable.
The return value is a pair of hash references, the first one with overall load results and the second with DPF-specific load results, or a single undef on failure. If wantarray is false, only the first hash reference is returned.
wantarray
The first return value has the following keys:
The second return value is an empoty hash reference unless the DPFOptions input parameter is specified. If so, it has the following keys:
DPFOptions
A reference to an array of hash references, each with the following keys:
This method is used to query the state of a load against a database table. It indicates the state of the table, the load phase, row counts, and messages. It requires a database connection; the database name itself is not specified as a parameter.
This method takes the following named parameters, all mandatory:
The amount of messages returned in the logfile. The following values may be specified:
The name of the output file to write the messages to
This method returns a hash-reference on success and undef on failure.
This method is used to collect statistics for a table and/or its indexes. This method requires a database connection.
At this time, only a subset of runstats features have been implemented; specifically, the column distribution options and columns group features are not supported. This may change in future releases.
This mandatory parameter specifies the database name. A connection to this database must already exist.
The mandatory parameter contains the table schema name; it is also the default schema name for any indexes specified.
The mandatory parameter contains the table name.
This optional parameter contains a hash reference that contains a mixture of flags (boolean values) and numerical values, as described below. Not every flag and option can be meaningfully combined with other flags and options; invalid combinations will lead to a DB2 error (the perl API does not check this).
This boolean option is used to collect statistics for all table columns. In the absence of any other option and the absence of the 'Columns' parameter, this is the default. See also the 'KeyCOlumns' option.
This boolean option is used to collect statistics for key table columns (those that make up all the indexes on the table).
This option is mutually exclusive with the 'AllColumns' option, unless the 'Distribution' option is also specified. In that case, basic statistics are collected for all columns and distribution statistics are computed for the key table columns.
This boolean option is used to collect distribution statistics. It can be combined with the 'AllColumns' and 'KeyColumns' options or the 'Columns' parameter.
This boolean option is used to collect statistics for all indexes defined on the table. When used, the 'Indexes' parameter should be omitted.
This boolean option is used to collect detailed statistics for table indexes. It can be combined with the 'AllIndexes' option or the 'Indexes' parameter.
This boolean option is used to collect sampled statistics for table indexes. It can be combined with the 'AllIndexes' option or the 'Indexes' parameter. It overrides the 'DetailedIndexes' option.
This boolean option is used to allow only read access on the table while statistics are being collected. The default is to allow both read and write access.
This numerical option enables Bernoulli sampling on the table data. This is the default sampling method (the other is 'SystemSampling'). The option value must be a percentage value (between 0 and 100).
This option is mutually exclusive with 'SystemSampling'.
This numerical option enables system sampling on the table data. This is the alternative sampling method (the default is 'BernoulliSampling'). The option value must be a percentage value (between 0 and 100).
This option is mutually exclusive with 'BernoulliSampling'.
This numerical option is used to make sampling of the table data repeatable. The option value is the sampling seed. This option can be combined with 'BernoulliSampling' or 'SystemSampling'.
This boolean option is used to collect statistics depending on a previously defined statistics profile for the table. When specified, the other options are ignore.
This boolean option is used to collect statistics and then set the statistics profile. Future Runstats calls with the 'UseProfile' option will re-use the current statistics settings.
This boolean option is used to set the statistics profile without actually collecting data. Future Runstats calls with the 'UseProfile' option will re-use the current statistics settings.
This boolean option is used to collect statistics and then update the statistics profile with the current settings. Future Runstats calls with the 'UseProfile' option will re-use the combination of existing and current current statistics settings.
This boolean option is used to update the statistics profile without actually collecting data. Future Runstats calls with the 'UseProfile' option will re-use the combination of existing and current current statistics settings.
This boolean option is used to skip collecting statistics on XML columns.
This option is only available with DB2 V9.1 and later.
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.
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.
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).
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 }, }
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.
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 Database is required. It returns an array with hash-references describing the history in detail; use of Data::Dumper to study the results is recommended.
The database name or alias to list the history for. Required.
The history action to list. The default is All. Valid actions are:
All
This selects load with and without copy
Either of these selects both types of events
A filter to select the object of interest. This is either a tablespace name, or a fully qualified table name (schema + table).
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. 2005 for January 1 of 2004, 200502 for February 1 of 2005, 20050215 for midnight of February 15 of 2005, etc.
2005
200502
20050215
This method is used to rebind a package. It takes the following named parameters:
The database name. A connection to this database must exist.
The schema name of the package (may be 'NULLID' for nameless packages).
The package name.
An optional hash reference with rebind options. It may contain the following keys:
The package version number (integer)
The rebind semantics: "Any" or "Conservative"
The re-optimization semantics: "None", "Once" or "Always".
The default is version-less packages, any binding type and no re-optimization.
This method lists the currently active utilities for the instance or the specified database. It is implemented using an instance snapshot. If attaching to the database instance requires a userid and password, an attachment must be established before calling this method.
This method has two optional named parameters, Database and Version. The Database option which is used to select utilities for a specific database; the Version allows use of a different database release level.
The return value is a list of hash-references with the following keys:
The database name
The utility run ID
The utility type (e.g. 'RUNSTATS')
A description of the utility or parameters for the utility
The utility priority (0 means unthrottled)
The utility start time in text format
The utility start time, in numeric format suitable for use with localtime or gmtime.
localtime
gmtime
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 sqleseti and sqleqryi functions.
sqleseti
sqleqryi
This method takes the following parameters, all optional:
The database name for which the client information should be set. A connection to this database must exist, i.e. 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.
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.
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.
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.
The accounting string.
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.
This method performs a database backup. For a DPF database, it backs up the node specified in the DB2NODE environment variable. In DB2 V9.5, it can back up all nodes of a DPF database.
DB2NODE
This method takes four named parameters and returns a hash reference, described in more detail after the parameters.
The database name or alias. This parameter is required.
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.
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.
A required hash reference with backup options.
The type of backup. This cna be Full, Incremental or Delta.
Full
Incremental
Delta
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 NoInterrupt (the default), Start, Continue, Terminate, DeviceTerminate, ParamCheck and ParamCheckOnly.
NoInterrupt
Start
Continue
Terminate
DeviceTerminate
ParamCheck
ParamCheckOnly
This parameter is only valid on DB2 V9.5 and only for DPF databases. It can be 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 NodeInfo field in the return value. It is mutually exclusive with the ExceptNodes parameter.
NodeInfo
ExceptNodes
This parameter is only valid on DB2 V9.5 and only for DPF databases. It is reference to an array of node numbers not to back up. Use of this parameter triggers the creation of the NodeInfo field in the return value. It is mutually exclusive with the Nodes parameter.
Nodes
A boolean option specifying an online or offline backup. The default is an offline backup.
A boolean option specifying whether to compress the backup. The default is a non-compressed backup.
A boolean option specifying that database logs must be included. This parameter is mutually exclusive with the ExcludeLogs option. Omitting both IncludeLogs and ExcludeLogs selects the default for the backup type, which is to include logs for snapshot backups and to exclude logs in all other cases.
ExcludeLogs
IncludeLogs
A boolean option specifying that database logs must be excluded. This parameter is mutually exclusive with the IncludeLogs option. Omitting both IncludeLogs and ExcludeLogs selects the default for the backup type, which is to include logs for snapshot backups and to exclude logs in all other cases.
An integer specifying the impact priority. When omitted, the backup runs unthrottled.
An integer specifying the degree of parallelism (number of buffer manipulators).
An integer specifying the number of backup buffers to be used.
An integer specifying the size of the abckup buffer in 4K pages.
The backup target type. The default is Local, i.e. a backup to a filesystem. Other options are XBSA, TSM, Snapshot and Other.
XBSA
TSM
Snapshot
Other
An optional connect userid.
An optional password to be used with the connect userid.
The return value of the Backup method is a reference to a hash with the following entries:
Backup
The size of the backup in megabytes
The error message if the SQL code is not zero
The description if the SQL state, if available
An optional array reference with per-node information. This is only available for DPF databases where the Nodes or ExceptNodes option was specified. Each array element is a hash reference with the following elements (Message and State are optional):
Message
State
Hildo Biersma
DB2::Admin::Constants(3), DB2::Admin::DataStream(3)
To install DB2::Admin, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DB2::Admin
CPAN shell
perl -MCPAN -e shell install DB2::Admin
For more information on module installation, please visit the detailed CPAN module installation guide.