view release on metacpan or  search on metacpan

DB2.pod  view on Meta::CPAN

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.