NAME

DBD::DB2 - DataBase Driver for DB2 UDB

DESCRIPTION

DBD::DB2 is a Perl5 module which when used in conjunction with DBI allows Perl5 to communicate with IBM's DB2 Universal Database.

In the generic sense, most of the functionality provided by any of the available DBDs is accessed indirectly through the DBI.

SYNOPSIS

use DBI;
use DBD::DB2::Constants;
use DBD::DB2;

$dbh = DBI->connect("dbi:DB2:db_name", $username, $password);

See DBI for more information.

The DBD::DB2 driver is supported by DB2 UDB V9 and later. See http://www.software.ibm.com/data/db2/perl for more information on supported environments.

The DB2 Information Center is available at: http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

EXAMPLE

#!/usr/local/bin/perl

use DBI;
use DBD::DB2::Constants;
use DBD::DB2 qw($attrib_int $attrib_char $attrib_float
                $attrib_date $attrib_ts);

# an extraneous example of the syntax for creating a new
# attribute type
$attrib_dec = { %$attrib_int,
               'db2_type'  => SQL_DECIMAL,
               'SCALE'     => 2,
               'PRECISION' => 31 };

#$DBI::dbi_debug=9; # increase the debug output

# Open a connection and set LongReadLen to maximum size of column
$dbh = DBI->connect("dbi:DB2:sample","","", { LongReadLen => 102400 } );
if (!defined($dbh)) { exit; }

# Note in the following sequence, that the statement contains
# no parameter markers, which makes the execution sequence
# just prepare and execute.
$stmt = "SELECT empno, photo_format FROM emp_photo WHERE
          photo_format = 'gif';";
$sth = $dbh->prepare($stmt);

$sth->execute();

# $row[0] is the empno from the database and $row[1] is the
# image type.  In this case, the type will always be "gif".
$stmt = "SELECT picture FROM emp_photo WHERE empno = ? AND
            photo_format = ? ;" ;
# prepare statement, which contains two parameter markers
$pict_sth = $dbh->prepare($stmt);
while( @row = $sth->fetchrow ) {
  # create an output file named empno.type in the current directory
  open(OUTPUT,">$row[0].$row[1]") || die "Can't open $row[0].$row[1]";
  binmode OUTPUT;

  # use bind_param to tell the DB2 code where to find the variables
  # containing the values for the parameters.  Additionally,
  # tell DB2 how to convert a perl value to a DB2 value based
  # on the contents of the $attrib_* hash.  One bind_param
  # call per parameter per execution.
  $pict_sth->bind_param(1,$row[0]);
  $pict_sth->bind_param(2,$row[1]);
  $pict_sth->execute();

  # do a fetch to get the blob
  @row = $pict_sth->fetchrow;

  print OUTPUT $row[0];
  @row = "";

  close(OUTPUT);
  # close the blob cursor
  $pict_sth->finish();
}
# redundantly close the blob cursor -- should be harmless
$pict_sth->finish();
# close selection criteria cursor
$sth->finish();
$dbh->disconnect();

Connection Attributes

The following DB2 connection attributes are supported. (For information on setting and querying connection attributes see the DBI guide.) Supported values are also shown: boolean refers to Perl true or false, tokens listed in uppercase are DB2 constants (be sure to include 'use DBD::DB2::Constants').

db2_access_mode      SQL_MODE_READ_ONLY or SQL_MODE_READ_WRITE
db2_clischema        Character string
db2_close_behavior   SQL_CC_NO_RELEASE or SQL_CC_RELEASE
db2_connect_node     Integer (must be set in DBI->connect method;
                     it cannot be modified afterwards)
db2_set_schema       Character string
db2_db2estimate      Integer
db2_db2explain       One of:
                       SQL_DB2EXPLAIN_OFF
                       SQL_DB2EXPLAIN_SNAPSHOT_ON
                       SQL_DB2EXPLAIN_MODE_ON
                       SQL_DB2EXPLAIN_SNAPSHOT_MODE_ON
db2_info_acctstr     Character string
db2_info_applname    Character string
db2_info_programname Character string
db2_info_userid      Character string
db2_info_wrkstnname  Character string
db2_longdata_compat  Boolean
db2_quiet_mode       Integer
db2_sqlerrp          Character string (read only)
db2_txn_isolation    One of the following:
                       SQL_TXN_READ_UNCOMMITTED
                       SQL_TXN_READ_COMMITTED
                       SQL_TXN_REPEATABLE_READ
                       SQL_TXN_SERIALIZABLE
                       SQL_TXN_NOCOMMIT

Not all the attributes are available in older versions of DB2. For further information on these attributes, refer to the DB2 Call Level Interface Guide and Reference, Chapter 5. CLI Functions, SQLSetConnectAttr. The attribute names listed above are similar to the CLI attributes documented (e.g. db2_access_mode is equivalent to SQL_ATTR_ACCESS_MODE).

Note: db2_set_schema can be used to set the current schema when setting up a connection.

Statement Attributes

The following DB2 statement attributes are supported. (For information on setting and querying statement attributes see the DBI guide.) Supported values are also shown: boolean refers to Perl true or false.

db2_concurrency               One of:
                                SQL_CONCUR_READ_ONLY
                                SQL_CONCUR_LOCK
                                SQL_CONCUR_VALUES
db2_cursor_hold               Boolean
db2_deferred_prepare          Boolean
db2_earlyclose                Boolean
db2_max_length                Integer
db2_call_return		Integer
db2_max_rows                  Integer
db2_more_results              Boolean (read only, see the section
                                below: Multiple Result Sets)
db2_noscan                    Boolean
db2_optimize_for_nrows        Integer
db2_prefetch                  Boolean
db2_rowcount_prefetch		Boolean
db2_query_optimization_level  Integer
db2_query_timeout             Integer (see note below)
db2_retrieve_data             Boolean
db2_row_number                Integer (read only)
db2_txn_isolation             One of the following:
                                SQL_TXN_READ_UNCOMMITTED
                                SQL_TXN_READ_COMMITTED
                                SQL_TXN_REPEATABLE_READ
                                SQL_TXN_SERIALIZABLE
                                SQL_TXN_NOCOMMIT

For further information on these attributes, refer to the DB2 Call Level Interface Guide and Reference, Chapter 5. CLI Functions, SQLSetStmtAttr. The attribute names listed above are similar to the CLI attributes documented (e.g. db2_deferred_prepare is equivalent to SQL_ATTR_DEFERRED_PREPARE).

Note: that some versions of the CLI Guide say SQL_ATTR_QUERY_TIMEOUT applies to Windows 3.1 only. This is incorrect, it works on all platforms. Later versions of the book have been corrected.

Data Source Names (DSNs)

Connection using the DBI->connect() method can be done in two different fashions.

Uncataloged database connections can be done by using the full connection string. For example:

my $string = "dbi:DB2:DATABASE=$db; HOSTNAME=$hostname; PORT=$port; PROTOCOL=TCPIP; UID=$user; PWD=$pass;";
my $dbh = DBI->connect($string, $user, $pass) || die "Connection failed with error: $DBI::errstr";

Cataloged database connections can be done by passing the database alias, username, and password as parameters. This method does not allow entering the host name, port number, etc but will require you to catalog the database (local or remote) through DB2. For example:

my $string = "cataloged_db_alias";
my $dbh = DBI->connect($string, $user, $pass) || die "Connection failed with error: $DBI::errstr";

To access a remote database, catalog the remote node, the DCS database (for AS/400, MVS and VM/VSE databases) and the database alias. See the DB2 Installation and Configuration Supplement for help with configuring client-to-server communications. For information on accessing host databases, see the DB2 Connect User's Guide.

DBI->data_sources('DB2') returns a list of all cataloged databases.

Binding Parameters

DBD::DB2 supports the following methods of binding parameters:

For input-only parameters:
$rc = $sth->bind_param($p_num, $bind_value);
$rc = $sth->bind_param($p_num, $bind_value, $bind_type);
$rc = $sth->bind_param($p_num, $bind_value, \%attr);

For input/output, output or input by reference:
$rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len);
$rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type);
$rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr)

Attributes

An attribute hash is a collection of information about particular types of data. Each attribute can be determined at compile time (see DB2.pm for a list of predefined attribute hashes), created at run time, or modified at run time.

The following attributes are supported by DBD::DB2:

TYPE            SQL_CHAR, SQL_BINARY, SQL_INTEGER etc.
PRECISION       Size of column
SCALE           Decimal digits
db2_param_type  SQL_PARAM_INPUT, SQL_PARAM_OUTPUT etc.
db2_c_type      SQL_C_CHAR or SQL_C_BINARY
db2_type        synonym for TYPE: SQL_CHAR, SQL_BINARY, SQL_INTEGER etc.
db2_file        Boolean value, see below

For backward compatibility, the following old attribute names are still supported. Note that these may not be supported in future releases of DBD::DB2 so it's a good idea to start using the new attribute names:

Stype   Same as db2_type
Prec    Same as PRECISION
Scale   Same as SCALE
ParamT  Same as db2_param_type
Ctype   Same as db2_c_type
File    Same as db2_file

The easiest method of creating a new attribute hash is to change an existing hash:

$new_type = { %$existing_type, 'db2_type' => SQL_"NewTYPE" };

or you can create a complete new type:

$attrib_char = { 'db2_param_type' => SQL_PARAM_INPUT,
                 'db2_c_type'     => SQL_C_CHAR,
                 'db2_type'       => SQL_CHAR,
                 'PRECISION'      => 254,
                 'SCALE'          => 0,
               };

Attributes are not generally required as the statement will be "described" and appropriate values will be used. However, attributes are required under the following conditions:

- Database server does not support SQLDescribeParam:
    - DB2 for MVS, versions earlier than 5.1.2
    - DB2 for VM
    - DB2 for AS/400
- Statement is a CALL to an unregistered stored procedure
- You desire non-default behaviour such as:
    - binding a file directly to a LOB parameter
    - binding an output-only parameter

Even though attributes are not always required, providing them can improve performance as it may make the "describe" step unnecessary. Specifically, 'db2_type' and 'SCALE' must either be provided in the attributes or must be obtained automatically via SQLDescribeParam.

Parameter Type (Input, Ouput and Input/Output)

bind_param() can only be used for input-only parameters and therefore the db2_param_type attribute is ignored. bind_param_inout() assumes input/output but a parameter can be designated as input-only or output-only via db2_param_type in the attribute hash:

db2_param_type => SQL_PARAM_INPUT

db2_param_type => SQL_PARAM_OUTPUT

Note that the 'maxlen' value provided to bind_param_inout() must be large enough for all possible input values as well as output values.

Binding Input Values By Reference using bind_param_inout()

This function - despite its name - can also be used to bind an input parameter variable once to allow repeated execution without rebinding. Consider the following example using bind_param():

$sth->prepare( "INSERT INTO MYTABLE (INTCOL) VALUES(?)" );
for(...)
{
  $int = ...;                  # get a new value
  $sth->bind_param( 1, $int ); # value set at bind time
  $sth->execute();
}

Each iteration binds a new value. This can be made more efficient as follows:

$sth->prepare( "INSERT INTO MYTABLE (INTCOL) VALUES(?)" );
$sth->bind_param_inout( 1,
                        \$input,
                        20, # 20 bytes is enough for any integer
                        { db2_param_type => SQL_PARAM_INPUT } );
for(...)
{
  $input = ...     # set a new value
  $sth->execute(); # new value read at execution time
}

Note that since the variable is bound by reference, the input value is deferred until execute time unlike bind_param() where the value is copied at bind time. The 'maxlen' value must be big enough for all expected input values.

Binding a File to an Input LOB Parameter

A file can be bound directly to a LOB parameter by specifying the attribute:

db2_file => 1

In this case the value passed to bind_param() is the file name.

For example, to insert a blob file "sample" into database blobtest:

my $stmt = "CREATE TABLE blobTest (id INTEGER, data BLOB)";
my $sth = $dbh->prepare($stmt);
$sth->execute();

$stmt = "INSERT INTO blobTest (id, data) values (?,?)";
my $sth = $dbh->prepare($stmt);

$sth->bind_param(1,1);
$sth->bind_param(2,sample,{'db2_file' => 1});
$sth->execute();

This is only valid for input and only for LOB parameters. The following predefined attribute hashes have been provided for convenience:

$attrib_blobfile
$attrib_clobfile
$attrib_dbclobfile

Example:

my $stmt = "CREATE TABLE blobTest (id INTEGER, data BLOB)";
my $sth = $dbh->prepare($stmt);
$sth->execute();

$stmt = "INSERT INTO blobTest (id, data) values (?,?)";
my $sth = $dbh->prepare($stmt);

$sth->bind_param(1,1);
$sth->bind_param(2,sample,$attrib_blobfile);
$sth->execute();

LongReadLen

The default value for LongReadLen is 32700, equivalent to the maximum size for SQL_LONG types. It only applies to fetched columns; it does not apply to output parameters. This option applies to the following column types:

SQL_LONGVARBINARY
SQL_LONGVARCHAR
SQL_LONGVARGRAPHIC
SQL_BLOB
SQL_CLOB
SQL_DBCLOB
SQL_XML

To change the value, provide it in the connection attributes:

$dbh = DBI->connect( $db, $user, $pw, { LongReadLen => 100 } );

or set it at any time after connecting:

$dbh->{LongReadLen} = 100;

Fetching LOB Data in Pieces

While LOB columns are fully supported by the normal methods of retrieving data, it can take a lot of memory as the whole LOB is retrieved at once (subject to the LongReadLen setting). An alternate method is to use:

$buf = $sth->blob_read( $field, $offset, $len );

Example:

$stmt =  "SELECT data FROM blobTest";
my $sth = $dbh->prepare($stmt);
$sth->execute();

my $offset = 0;
my $buff="";
$sth->fetch();
while( $buff = $sth->blob_read(1,$offset,1000000)){
       print $buff;
       $offset+=length($buff);
       $buff="";
}

This will return up to $len bytes from the given LOB field. 'undef' is returned when no more data is left to read. Despite the name this function works for all LOB types (BLOB, CLOB and DBCLOB). For maximum efficiency, set LongReadLen to 0 prior to execution so no LOB data is retrieved at all on the initial fetch (but remember that LongReadLen will affect all long fields).

The $offset parameter is currently ignored by DB2. Note that this function isn't officially documented in DBI yet so it is subject to change.

Multiple Result Sets

Multiple result sets can be processed using the db2_more_results statement attribute as follows:

do
{
  while( @row = $sth->fetchrow_array )
  {
    # process row data
  }
} while( $sth->{db2_more_results} );

Accessing this attribute closes the current result set and opens the new one. If there are no more result sets, the attribute returns false and sets the state to 02000.

Getting Table information

Both $dbh->tables and $dbh->table_info are supported. The table names returned by $dbh->tables are qualified, i.e. they are in the form <schema>.<table>. With DBI 1.14 or later, the following attributes can be used to narrow down the list:

TABLE_SCHEM  Schema name pattern, default is all schemas
TABLE_NAME   Table name pattern, default is all tables
TABLE_TYPE   Table type; one or more of the following,
             separated by commas, default is all types:
               TABLE, VIEW, SYSTEM TABLE, ALIAS, SYNONYM

Each pattern-value argument can contain:

- The underscore (_) character which stands for any single character.
- The percent (%) character which stands for any sequence of zero or
  more characters. Note that providing a pattern-value containing a
  single % is equivalent to passing an empty string for that argument.
- Characters which stand for themselves. The case of a letter is
  significant.

To treat the metadata characters (_, %) as themselves, precede the character with a backslash (\). The escape character itself can be specified as part of the pattern by including it twice in succession.

For example, to get a list of all tables and views for the schema 'CHOMSKY':

  @tables = $dbh->tables( { 'TABLE_SCHEM' => 'CHOMSKY',
                            'TABLE_TYPE'  => 'TABLE,VIEW',
                            'TABLE_NAME'  => '%'} );
			or
  @tables = $dbh->tables(undef, 'CHOMSKY', '%', 'TABLE,VIEW');

Getting a List of Schemas

To obtain a list of all schemas, the following special semantics can be used:

@schemas = $dbh->tables( {'TABLE_SCHEM' => '%',
                         'TABLE_TYPE' => '',
                         'TABLE_NAME' => ''} );

$sth = $dbh->table_info( undef, '%', '', '');

The result contains all the valid schemas in the data source. DBI 1.14 or later is required.

Getting a List of Table Types

To obtain a list of supported table types, the following special semantics can be used:

$sth = $dbh->table_info( { 'TABLE_TYPE'  => '%',
                           'TABLE_SCHEM' => '',
                           'TABLE_NAME'  => '' } );

The result contains all the valid table types for the data source. DBI 1.14 or later is required.

Getting Primary and Foreign Key information

The $dbh->primary_key, $dbh->primary_key_info, and $dbh->foreign_key_info are supported. Search patterns cannot be used to specify any of the arguments. Please see the DBI documentation for usage information.

To obtain the primary keys for the table HOYMICH.MYTABLE:

$sth = $dbh->primary_key_info( undef, 'HOYMICH', 'MYTABLE' );

To obtain all the primary key column names:

@key_column_names = $dbh->primary_key( $catalog, $schema, $table );

Getting Type information

Both $dbh->type_info_all and $dbh->type_info are supported. Please see the DBI documentation for usage information.

Getting driver and database system information (GetInfo)

The $dbh->get_info is supported. Please see the DBI documentation for usage information. Please see the CLI function, SQLGetInfo (link is available in the CAVEATS file), for the supported information types.

To obtain the name of the DBMS product being accessed:

$v = $dbh->get_info( SQL_DBMS_NAME );

To obtain the name of the DBMS product version being accessed:

$v = $dbh->get_info( SQL_DBMS_VER );

If information regarding an unsupported InfoType is requested, undef is returned. For a full list of supported InfoType codes, you may visit: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/r0000615.htm

Native XML support

DBD::DB2 version 0.9a supports native XML. The datatype xml is mapped to the sqltype SQL_XML and is stored in the database in a hierarchical structure. For all data manipulation purposes XML data is treated as a BLOB.

To create a table with XML data:

$stmt = "CREATE TABLE xmlTest (id INTEGER, data XML)";

To insert an XML file:

$stmt = "INSERT INTO xmlTest (id, data) values (?,?)"; my $sth = $dbh->prepare($stmt);

open(SAMPLE,"<sample") or die "Cannot open $path: $!"; binmode SAMPLE; while(my $record = <SAMPLE>){ $input = $input.$record; } close(SAMPLE);

$sth->bind_param(1,123456789); $sth->bind_param(2,$input); $sth->execute();

XML data can be retrieved in two ways:

1. As a BLOB:

$stmt = "SELECT data FROM xmlTest"; my $sth = $dbh->prepare($stmt); $sth->execute();

my $offset = 0; my $buff=""; $sth->fetch(); while( $buff = $sth->blob_read(1,$offset,1000000)){ print $buff; $buff=""; $offset+=length($buff); }

2. As a record:

$stmt = "SELECT data FROM xmlTest"; my $sth = $dbh->prepare($stmt); $sth->execute(); while( @row = $sth->fetchrow ) { print $row[0] . "\n"; }

To install DBD::DB2, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBD::DB2

CPAN shell

perl -MCPAN -e shell
install DBD::DB2

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)