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.

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, 'Stype' => SQL_DECIMAL, 'Scale' => 2,
                                                        'Prec' => 31 };

  #$DBI::dbi_debug=9; # increase the debug output
  
  $dbh = DBI->connect("dbi:DB2:sample");
  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 inv.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".
  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]"; 
    $stmt = "select picture from inv.emp_photo where empno = ? and      
                photo_format = ? ;" ; 
    # prepare statement, which contains two parameter markers
    $pict_sth = $dbh->prepare($stmt);
    
    # 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],$attrib_char);
    $pict_sth->bind_param(2,$row[1],$attrib_char);
    $pict_sth->execute();

    # do a fetch to get the blob locator variable loaded.
    $pict_sth->fetch();
  
    # read the contents of the blob pointed to by field
    # one of the statement using a zero offset, reading
    # a chunk of 4096 bytes each time.
    while ($buff = $pict_sth->blob_read(1,0,4096)) { 
        print OUTPUT $buff;
       $buff = "";
    }
    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();

Attribute Hashes

The version of DB2 that this driver was originally built on did not support SQLDescribeParameter (a function which returns everything the database knows about a particular parameter). Without this function, the enduser would have been required to build the @bind_values array with eight pieces of information pertaining to the internal database usage and structure of the parameter in question.

This, of course, was deemed to be too radical a departure from the DBD::Oracle reference implementation. An attribute hash is simply a collection of information about particular types of data. Each attribute can be determined at compile time (see DB2.pm for list of predefined attribute hashes), created at run time, or modified at run time.

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

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

or simple create a complete new type:

  $attrib_char = { 'ParamT' => SQL_PARAM_INPUT,
                   'Ctype' => SQL_C_CHAR,
                   'Stype' => SQL_CHAR,
                   'Prec'  => 254,
                   'Scale' => 0,
                 };