Help-Site Computer Manuals
Software
Hardware
Programming
Networking
  Algorithms & Data Structures   Programming Languages   Revision Control
  Protocols
  Cameras   Computers   Displays   Keyboards & Mice   Motherboards   Networking   Printers & Scanners   Storage
  Windows   Linux & Unix   Mac

DB2::Admin
Support for DB2 Administrative API from perl

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


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


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

The default is version-less packages and any binding type.

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.


AUTHOR

Hildo Biersma


SEE ALSO

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

Programminig
Wy
Wy
yW
Wy
Programming
Wy
Wy
Wy
Wy