SYNOPSIS

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

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.

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:

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:

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.

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.

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:

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

or

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

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:

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:

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:

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:

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

Getting a List of Table Types

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.

As a valued partner and proud supporter of MetaCPAN, StickerYou is
happy to offer a 10% discount on all Custom Stickers,
Business Labels, Roll Labels,
Vinyl Lettering or Custom Decals. StickerYou.com
is your one-stop shop to make your business stick.
Use code METACPAN10 at checkout to apply your discount.