Perl DB2 Interface

 

  1. Identify and include DB2 specific drivers

For Perl, DB2 support is included by specifying the "use DBI" and "use DBD::DB2" statements. DBD::DB2 is a Perl5 module which when used in conjunction with DBI allows Perl5 to communicate with DB2.

use DBI;

use DBD::DB2::Constants;

use DBD::DB2;

 

  1. Connect to DB2 database 

The syntax for the for the statement establishing a connection to a relational database from a Perl program is defined in the Perl DBI specification (see URL listed below). An example of connecting to a DB2 database from Perl  follows;

$dbh = DBI->connect("dbi:DB2:dsn", $user, $pass);

where:

$dbh  = connection id returned from driver

dsn   = DB2 database name

$user  = DB2 database user

$pass  = DB2 database user password

 

  1. Formulate  and Execute SQL statements with required parameters

With Perl, SQL requests are submitted to DB2 via DBI function calls. The sequence of the calls depends on whether placeholders (also known as parameter markers) are being used. When using placeholders, "?" is used in the statements to represent values being submitted.  An example of  executing 2 SQL statements  to DB2  from Perl  follow. The first does not use placeholders, the second does use placeholders;

# statement 1 - no placeholders

# Note in the following sequence, that the statement contains

# no parameter markers, which makes the execution sequence

# just prepare and execute.

$sql_stmt="select fname, lname from contacts where id  = $id";

$sth = $dbh->prepare($sql_stmt);

$sth->execute();

where:

$sql_stmt        = variable used to hold sql statement text

$dbh             = connection id 

$sth             = id returned from prepare

 

 

# statement 2 - with placeholder

# prepare statement, which contains parameter marker

$sql_stmt="select fname, lname from contacts where id  = ?";

$sth = $dbh->prepare($sql_stmt);

# use bind_param to tell DB2 where to find the variables

# containing the values for the parameters.

# One bind_param  call per parameter per execution. 

$sth->bind_param(1,$id);

$sth->execute();

where:

$sql_stmt        = variable used to hold sql statement text

$dbh             = connection id 

$sth             = id returned from prepare

 

  1. Disconnect from DB2 database

An example of  closing the result cursor and disconnecting from a  DB2 database follows;

# close selection criteria cursor

$sth->finish();

$dbh->disconnect();

 

where:

$sth            = id returned from prepare

$dbh            = connection id 

 

Links with related information:

IBM DB2 Perl Database Interface

Perl DBI

mod_perl guide

Symbolstone DBI List