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
The DB2 GIT Repo is available at: https://github.com/ibmdb/perl_DBD-DB2
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_NOCOMMITNot 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_NOCOMMITFor 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 belowFor 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_fileThe 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 parameterEven 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_INPUTor
db2_param_type => SQL_PARAM_OUTPUTNote 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 => 1In 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_XMLTo 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, SYNONYMEach 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' => ''} );or
$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"; }