/ 
DB2-Admin-3.0
River stage zero No dependents
/ DB2::Admin

NAME

DB2::Admin - Support for DB2 Administrative API from perl

SYNOPSIS

  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.
  # The 'Load' and 'LoadQuery' commands require DB2 V8.2
  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('Database' => $db_name,
		                    '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', });

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 SetOptions method:

  • 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.

Many API calls take optional Version and Node parameters. These have the following meaning:

Version

The database monitor version, a string in the format SQLM_DBMON_VERSION8. The default is 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.

Node

The database node. This can be the string SQLM_CURRENT_NODE (the default), the string 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.

METHODS

The methods below are all intended for use by applications. The underlying low-level functions in the XS module are not documented.

SetOptions

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:

PrintError

When an error occurs, write it to STDERR using warn. This option is on by default.

PrintWarn

When a warning occurs, write it to STDERR using warn. This option is on by default.

RaiseError

When an error occurs, generate an exception using die. This option is off by default.

RaiseWarn

When a warning occurs, generate an exception using die. This option is off by default.

SetConnectAttributes

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:

ProgramName

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).

This attribute is silently ignored on DB2 V7.2.

ConnectTimeout

The connect (login) time-out, in seconds. The default is 60 seconds.

Attach

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:

Instance

The name of the instance to attach to. If omitted, the environment variable DB2INSTANCE must be set and will determine the instance instead.

Userid

The userid used to attach.

Password

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.

InquireAttach

This method describes the instance attached to. On success, it returns a hash reference with the following fields:

Country
CodePage
AuthId
NodeName
ServerId
AgentId
AgentIndex
NodeNum
Partitions

Detach

This method detaches from the database instance. It returns a boolean to indicate whether the operation succeeded.

Connect

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.

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.

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.

Disconnect

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.

GetMonitorSwitches

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.

This method takes two optional named parameters:

Version
Node

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.

UnitOfWork
Statement
Table
BufferPool
Lock
Sort
Timestamp

The TimeStamp key is only available for DB2 V8 and later.

SetMonitorSwitches

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:

Switches

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.

Version
Node

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.

ResetMonitor

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.

This method takes three optional named parameters:

Alias

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.

Version
Node

GetSnapshot

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.

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.

Version
Node
Class

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.

Health snapshots are only available with DB2 release 8 or higher, and if the health monitor is active.

Store

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.

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.

GetDbmConfig

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.

This method takes the following named parameters:

Param

The name of the configuration parameter; optionally, a reference to an array of configuration parameters. The names are case-insensitive.

Flag

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.

Version

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.

UpdateDbmConfig

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.

This method takes the following named parameters:

Param

A hash-reference with the fields Name, Value and optionally an entry-level flag (Automatic, Computed or Manual, see below).

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.

The entry-level flags are:

Automatic

Let DB2 set the value automatically. The value specified in this call is accepted but will be overriden by DB2.

Computed

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.

Manual

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.

Flag

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.

Version

This method returns true on success and false on failure.

GetDatabaseConfig

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 V8.1.

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.

This method takes the following named parameters:

Param

The name of the configuration parameter; optionally, a reference to an array of configuration parameters. The names are case-insensitive.

Flag

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.

Version

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.

UpdateDatabaseConfig

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 V8.1.

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.

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 takes the following named parameters:

Param

A hash-reference with the fields Name, Value and optionally an entry-level flag (Automatic, Computed or Manual, see below).

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.

The entry-level flags are:

Automatic

Let DB2 set the value automatically. The value specified in this call is accepted but will be overriden by DB2.

Computed

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.

Manual

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.

Flag

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.

Version

GetDatabaseDirectory

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.

This method takes one optional named parameter, Path. When omitted, the system database directory is retrieved.

CatalogDatabase

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:

Alias

The database alias name. This parameter is required. The database alias must be unique within the database directory.

Database

The database name. This parameter is required.

NodeName

This parameter is optional and used for remote databases. This should match an entry in the node directory.

Path

This parameter is optional and used for locally cataloged databases.

Comment

This parameter is optional and provides a comment describing the database.

DBType

This parameter is required and describes the database type. The following values are supported:

Indirect
Remote
DCE
Authentication

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 (some values are only supported in DB2 V8.2):

Server
Client
Kerberos
Not specified
DCE
DCS
Kerberos / Server Encrypt
DCS Encrypt
Server Encrypt
Server / Data Encrypted
GSS Plugin
GSS Plugin / Server Encrypt
Server / Optional Data Encrypted
Principal

The Kerberos principal for the database, if Kerberos authentication is used.

UncatalogDatabase

This method removes an entry from the database directory. It takes one named parameter, Alias.

GetNodeDirectory

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.

This method does not take any parameters.

CatalogNode

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:

NodeName

The node name. This parameter is required. The node alias must be unique within the node directory.

Comment

This parameter is optional and provides a comment describing the node.

Protocol

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:

TCPIP
TCP/IP

Alias for TCPIP matching GetNodeDirectory

TCPIP4
TCP/IPv4

Alias for TCPIP4 matching GetNodeDirectory; only on DB2 V9.

TCPIP6
TCP/IPv6

Alias for TCPIP6 matching GetNodeDirectory; only on DB2 V9.

SOCKS
SOCKS4
TCP/IPv4 using SOCKS

Alias for SOCKS4 matching GetNodeDirectory; only on DB2 V9.

Local
Local IPC

Alias for Local matching GetNodeDirectory

Hostname

This parameter is required for TCP/IP and SOCKS nodes and describes the hostname of the remote database.

ServiceName

This parameter is required for TCP/IP and SOCKS nodes and describes the port number or service name of the remote database.

InstanceName

This parameter is required for Local IPC nodes and describes the instance name.

UncatalogNode

This method removes an entry from the node directory. It takes one named parameter, NodeName.

GetDCSDirectory

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.

This method does not take any parameters.

CatalogDCSDatabase

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:

Database

The local database name. This parameter is required. The database name must be unique within the DCS directory.

Target

The target database name. This parameter is required.

Library

This parameter is optional and describes the application requester library to be used. When omitted, DB2 connect will be used.

Parameter

This parameter is optional and contains connect options for the DCS database.

Comment

This parameter is optional and provides a comment describing the DCS database.

UncatalogDCSDatabase

This method removes an entry from the DCS directory. It takes one named parameter, Database.

ForceApplications

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.

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.

ForceAllApplications

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.

Invoking this method may be career suicide when used on production instances. Use with care.

Export

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.

Database

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.

Schema

The schema name of the table to export. This parameter is required.

Table

The name of the table to export. This parameter is required.

Columns

An optional parameter with an array-reference of the columns to be exported.

Where

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.

FinalClauses

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.

FileType

This parameter is mandatory and specifies the type of output file: DEL for delimited files (CSV-style) or IXF for IXF files.

FileOptions

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.

CharDel

The delimiter around string fields.

CodePage

The code page (character set) modifier, e.g. 819 or 1208.

ColDel

The column delimiter.

DatesISO

Write out dates in ISO format, i.e. YYYY-MM-DD.

DecPlusBlank

Replace the leading + before a decimal number by a blank

LobsInFile

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.

NoCharDel

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.

StripZeros

Strip leading zeros before numbers

TimestampFormat

The timestamp format.

XmlInSepFiles

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).

OutputFile

This mandatory parameter specifies the name of the output file.

LogFile

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.

LobPath

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.

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.

LobFile

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.

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:

XmlSaveSchemas

This boolean option determines whether XML schema ids will be included in the output file or not.

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 XmlInSepFiles file option, the XmlSaveSchema export option and the XmlFile parameter.

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.

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.

XmlFile

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.

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 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.

Database

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.

Schema

The schema name of the table to import into. This parameter is required.

Table

The name of the table to import into. This parameter is required.

TargetColumns

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.

Operation

The import operation. Legal values are:

Insert

Insert rows into the table, appending to the existing data. Skip rows with duplicate keys.

Insert_Update

Insert rows into the table, appending to the existing data. Row with duplicate keys replace existing rows.

Replace

Replace the contents of the table (i.e. delete all existing rows before importing the data).

FileType

This parameter is mandatory and specifies the type of input file: DEL for delimited files (CSV-style) or IXF for IXF files.

FileOptions

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.

GeneratedIgnore
GeneratedMissing
IdentityIgnore
IdentityMissing
NoDefaults
UseDefaults
CharDel

The delimiter around string fields (DEL files only).

CodePage

The code page (character set) modifier, e.g. 819 or 1208.

ColDel

The column delimiter (DEL files only).

DateFormat

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'
DelPriorityChar

For DEL files: support embedded newlines in column values

ImpliedDecimal

A flag indicating the position of the decimal point is implied (DEL files only)

KeepBlanks

Keep leading and trailing blanks for character fields (DEL files only)

LobsInFile

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.

NoCharDel

Don't assume delimiters around character fields (DEL files only). This should be used only for import from other databases or products.

StripTBlanks

A flag indicating that trailing blanks need to be stripped. Yes, this flag has an ugly name - it really is spelled StripTBlanks.

TimeFormat

The time format (DEL files only)

TimestampFormat

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'
InputFile

This mandatory parameter specifies the name of the input file.

InputColumns

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.

LogFile

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.

ImportOptions

An optional hash reference with import options (those affecting the import operation itself, not describing the input file). The fields supported depend on the DB2 release; DB2 releases prior to V8.2 only support CommitCount and RestartCount, and do not support CommitCount 'Automatic'.

RowCount

The maximum number of rows to import

RestartCount

The number of rows to skip before starting; intended for use after a previous import operation failed partway through.

SkipCount

Functionally identical to RestartCount

CommitCount

How often import should commit. For DB2 V8.2, the default is 'Automatic'.

WarningCount

The maximum number of warnings before ending the import. The default is 0 (infinite).

Timeout

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.

AccessLevel

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'.

XmlParse

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.

LobPath

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.

XmlPath

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 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.

The return value is a hash reference with the following keys:

RowsRead
RowsInserted
RowsUpdated
RowsRejected
RowsSkipped
RowsCommitted

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 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, but 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.

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.

Database

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.

Schema

The schema name of the table to load into. This parameter is required.

Table

The name of the table to load into. This parameter is required.

TargetColumns

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.

Operation

The load operation. Legal values are:

Insert

Insert rows into the table, appending to the existing data. Skip rows with duplicate keys.

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".

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.

Replace ResetDictionary

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

Restart a previously partially completed load.

Terminate

Terminate a previously partially completed load.

SourceType

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.

FileLocation

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.

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.

FileOptions

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:

AnyOrder
GeneratedIgnore
GeneratedMissing
GeneratedOverride
IdentityIgnore
IdentityMissing
IdentityOverride
LobsInFile

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.

NoRowWarnings
UseDefaults
IndexFreespace
PageFreespace
TotalFreespace

Next, the options for DEL files:

CharDel
CodePage

The code page (character set) modifier, e.g. 819 or 1208.

ColDel
DateFormat

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

'DateFormat' => 'MMM DD YYYY'
DatesISO
DecPlusBlank
DecPt
DelPriorityChar

Support embedded newlines in column values

DumpFile

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.

DumpFileAccessAll

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.

ImpliedDecimal
KeepBlanks
NoCharDel

Don't assume delimiters around character fields (DEL files only). This should be used only for load from other databases or products.

TimeFormat
TimestampFormat

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:

ForceIn
NoCheckLengths
InputFile

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).

InputStatement

This parameter is required for SQL statements and specifies the SELECT statement to read the data to be loaded.

InputColumns

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 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.

CopyDirectory

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.

LogFile

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.

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>'.

TempFilesPath

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.

LoadOptions

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.

RowCount

The maximum number of rows to load.

UseTablespace

The tablespace to use to rebuild the index(es).

SaveCount

The number of rows to load before establishing a consistency point from which the load can be restarted.

DataBufferSize
SortBufferSize
WarningCount
HoldQuiesce

Boolean

CpuParallelism
DiskParallelism
NonRecoverable

Boolean. The default is false (recoverable).

IndexingMode

Legal values are:

AutoSelect
Rebuild
Incremental
Deferred
AccessLevel

Legal values are:

None
Read
LockWithForce

Boolean

CheckPending

Legal values are:

Immediate
Deferred
Statistics

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:

None
UseProfile
XmlParse

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.

DPFOptions

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.

OutputDBPartNums

An array reference with database partition numbers

PartitioningDBPartNums

An array reference with database partition numbers

MaxNumPartAgents

Integer

IsolatePartErrors

This can have the following string values:

SetupErrorsOnly
LoadErrorsOnly
SetupAndLoadErrors
NoIsolation
StatusInterval

Integer.

PortRange

An array reference with two port numbers

CheckTruncation

Boolean

Trace

Integer

Newline

Boolean

OmitHeader

Boolean

RunStatDBPartnum

Integer

ExceptionSchema

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.

ExceptionTable

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.

LobPath

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 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.

XmlPath

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 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 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.

The first return value has the following keys:

RowsRead
RowsSkipped
RowsLoaded
RowsRejected
RowsDeleted
RowsCommitted

The second return value is an empoty hash reference unless the DPFOptions input parameter is specified. If so, it has the following keys:

RowsRead
RowsRejected
RowsPartitioned
AgentInfo

A reference to an array of hash references, each with the following keys:

SQLCode
TableState
NodeNum
AgentType

LoadQuery

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.

This method takes the following named parameters, all mandatory:

Database

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.

Schema

The schema name of the table to load into. This parameter is required.

Table

The name of the table to load into. This parameter is required.

Messages

The amount of messages returned in the logfile. The following values may be specified:

All
None
New
LogFile

The name of the output file to write the messages to

This method returns a hash-reference on success and undef on failure.

Runstats

This method is used to collect statistics for a table and/or its indexes. This method requires DB2 V8. 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 method takes the following named parameters:

Database

This mandatory parameter specifies the database name. A connection to this database must already exist.

Schema

The mandatory parameter contains the table schema name; it is also the default schema name for any indexes specified.

Table

The mandatory parameter contains the table name.

Options

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).

Many options are only available in DB2 V8.2 and later.

AllColumns

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.

KeyColumns

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.

Distribution

This boolean option is used to collect distribution statistics. It can be combined with the 'AllColumns' and 'KeyColumns' options or the 'Columns' parameter.

AllIndexes

This boolean option is used to collect statistics for all indexes defined on the table. When used, the 'Indexes' parameter should be omitted.

DetailedIndexes

This boolean option is used to collect detailed statistics for table indexes. It can be combined with the 'AllIndexes' option or the 'Indexes' parameter.

SampledIndexes

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.

AllowRead

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.

BernoulliSampling

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'. It is only available with DB2 V8.2 and later.

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'. It is only available with DB2 V8.2 and later.

Repeatable

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 option is only available with DB2 V8.2 and later.

UseProfile

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 option is only available with DB2 V8.2 and later.

SetProfile

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 option is only available with DB2 V8.2 and later.

SetProfileOnly

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 option is only available with DB2 V8.2 and later.

UpdateProfile

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 option is only available with DB2 V8.2 and later.

UpdateProfileOnly

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 option is only available with DB2 V8.2 and later.

ExcludingXML

This boolean option is used to skip collecting statistics on XML columns.

This option is only available with DB2 V9.1 and later.

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.

This option is only available with DB2 V8.2 and later.

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.

This option is only available with DB2 V8.2 and later.

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).

This option is only available with DB2 V8.2 and later.

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 },
             }
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.

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 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.

Database

The database name or alias to list the history for. Required.

Action

The history action to list. The default is All. Valid actions are:

All
Backup
RollForward
Reorg
AlterTablespace
DropTable
Load

This selects load with and without copy

RenameTablespace
CreateTableSpace / DropTablespace

Either of these selects both types of events

ArchiveLog

The ArchiveLog action is only available with DB2 V8.1 and higher.

ObjectName

A filter to select the object of interest. This is either a tablespace name, or a fully qualified table name (schema + table).

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. 2005 for January 1 of 2004, 200502 for February 1 of 2005, 20050215 for midnight of February 15 of 2005, etc.

Rebind

This method is used to rebind a package. It takes the following named parameters:

Database

The database name. A connection to this database must exist.

Schema

The schema name of the package (may be 'NULLID' for nameless packages).

Package

The package name.

Options

An optional hash reference with rebind options. It may contain the following keys:

Version

The package version number (integer)

Resolve

The rebind semantics: "Any" or "Conservative"

ReOpt

The re-optimization semantics: "None", "Once" or "Always". This option requires DB2 V8.2 or later.

The default is version-less packages, any binding type and no re-optimization.

ListUtilities

This method lists the currently active utilities for the instance or the specified database. It is implemented using an instance snapshot and required DB2 V8.2. If attaching to the database instance requires a userid and password, an attachment must be established before calling this method.

This method has one optional named parameters, Database, which is used to select utilities for a specific database. The return value is a list of hash-references with the following keys:

Database

The database name

ID

The utility run ID

Utility

The utility type (e.g. 'RUNSTATS')

Description

A description of the utility or parameters for the utility

Priority

The utility priority (0 means unthrottled)

StartTime

The utility start time in text format

StartTimeVal

The utility start time, in numeric format suitable for use with localtime or gmtime.

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 sqleseti and sqleqryi functions.

This method takes the following parameters, all optional:

Database

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.

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.

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.

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.

AccountingString

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.

Backup

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.

This method takes four named parameters and returns a hash reference, described in more detail after the parameters.

Database

The database name or alias. This parameter is required.

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.

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.

Options

A required hash reference with backup options.

Type

The type of backup. This cna be Full, Incremental or Delta.

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 NoInterrupt (the default), Start, Continue, Terminate, DeviceTerminate, ParamCheck and ParamCheckOnly.

Nodes

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.

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.

Online

A boolean option specifying an online or offline backup. The default is an offline backup.

Compress

A boolean option specifying whether to compress the backup. The default is a non-compressed backup.

IncludeLogs

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

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.

ImpactPriority

An integer specifying the impact priority. When omitted, the backup runs unthrottled.

Parallelism

An integer specifying the degree of parallelism (number of buffer manipulators).

NumBuffers

An integer specifying the number of backup buffers to be used.

BufferSize

An integer specifying the size of the abckup buffer in 4K pages.

TargetType

The backup target type. The default is Local, i.e. a backup to a filesystem. Other options are XBSA, TSM, Snapshot and Other.

Userid

An optional connect userid.

Password

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:

ApplicationId
Timestamp
BackupSize

The size of the backup in megabytes

SQLCode
Message

The error message if the SQL code is not zero

State

The description if the SQL state, if available

NodeInfo

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):

NodeNum
BackupSize
SQLCode
Message
State

AUTHOR

Hildo Biersma

SEE ALSO

DB2::Admin::Constants(3), DB2::Admin::DataStream(3)