=head1 NAME

DBD::DB2 - DataBase Driver for DB2 UDB


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

=head1 SYNOPSIS

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

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

See L<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

=head1 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();

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

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

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

=head1 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)

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

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

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

=head2 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();


=head1 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;

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

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

=head1 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');

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

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

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

=head1 Getting Type information

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

=head1 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

=head1 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";
}