Perl programming with DB2 Universal Database

http://www.ibm.com/developerworks/data/library/techarticle/dm-0512greenstein/

ntroduction

IBM DB2 e-kit for Database Professionals

Learn how easy it is to get trained and certified for DB2 for Linux, UNIX, and Windows with the IBM DB2 e-kit for Database Professionals. Register now, and expand your skills portfolio, or extend your DBMS vendor support to include DB2.

Perl — Practical Extraction and Report Language -- is a powerful and easy-to-use programming language available for many operating systems. Perl is free. You can download the language (in source code or binary form) and use it without paying a license fee.

Perl is an increasingly popular choice. It contains aspects of the C programming language, as well as UNIX® commands such as awkand sed. An interpreted language, Perl can be used in stand-alone applications or with Apache to build Web applications.

You can use Perl to quickly manipulate large sets of data from files or RDBMSs. DBI, the standard for connecting Perl scripts to an RDBMS, was introduced in 1994. You can find the DBI driver source and its documentation at http://dbi.perl.org/.

IBM created the DB2 driver for Perl in 1995 and periodically updates it as the DBI specification evolves. The latest version of the driver (at the time of writing this article) is 0.78. You'll find the main DBD::DB2 (this is the naming technique within the Perl language) driver information page at http://www.ibm.com/software/data/db2/perl/.

This article shows you how to write simple Perl programs that extract or manipulate data stored in DB2 UDB. You'll go from the simple task of selecting a row from a database into a Perl program to more advance topics, including dealing with large objects and invoking stored procedures.

Get started

Figure 1 shows how the Perl environment interfaces with a database environment:

Figure 1. Perl environment 
 

As you can see from the diagram, a Perl program uses a standard API to communicate with DBI, the database interface module for Perl. The Perl DBI module supports only dynamic SQL. It defines a set of methods, variables, and conventions that provide a consistent database interface independent of the actual database being used. DBI gives the API a consistent interface to any database that the programmer wishes to use. DBD::DB2 is a Perl module which, when used in conjunction with DBI, allows Perl to communicate with DB2 UDB.

So, in the order to run a Perl script that has access to your DB2 database, you need the following components on your system:

• Perl language environment
• DBI driver (can be used for any RDBMS
• DBD::DB2 driver
• DB2 Runtime Client
• C compiler
• Connection information for the DB2 database server

You can find all installation and configuration instructions on the http://www.ibm.com/software/db2/perl/ Web site.

Connect to the DB2 database

In order for a Perl program to access a DB2 database, you need to establish connection to the database. To enable Perl to load the DBI module, you need to include the following into your Perl DB2 application:

use DBI;

The DBI module automatically loads the DBD::DB2 driver when you create a database handle using the DBI->connectstatement with the following syntax:

Listing 1. Create a database handle

use DBI; $dbh = DBI->connect ("dbi:DB2:dbalias", $UserId, $password);

Where:

• $dbh: represents the database handle returned by the connect statement
• dbalias: represents a DB2 alias cataloged in your DB2 database directory
• $userID: represents the user ID used to connect to the database
• $password: represents the password for the user ID

Listing 2 demonstrates a simple Perl program that establishes connection to the database SAMPLE and returns today's date. This program executes a dynamically prepared DB2 SQL statement to retrieve CURRENT DATE from the database. A value from the database is passed into a local variable using DBI -> bind_col method, which we will discuss later.

Listing 2. Connect to database and execute a statement

#!/usr/local/bin/perl -w use DBI; use strict; # Open a connection my $dbh = DBI->connect("dbi:DB2:sample", "DB2ADMIN", "db2admin", {RaiseError => 1}); # use VALUES to retrieve value from special register my $stmt = "Values CURRENT DATE"; my $sth = $dbh->prepare($stmt); $sth->execute(); # associate variables with output columns... my $col1; $sth->bind_col(1,\$col1); while ($sth->fetch) { print "Today is: $col1\n"; } $sth->finish(); $dbh->disconnect();

Error handling — SQLCODE and SQLSTATE

In order to return the SQLSTATE associated with a Perl DBI database handle or statement handle, you can invoke the state method. For example, to return the SQLSTATE associated with the database handle $dbhandle, include the following Perl statement in your application:

my $sqlstate = $dbhandle->state;

In order to return the SQLCODE associated with a Perl DBI database handle or statement handle, you can call the err method. For example, to return the SQLCODE associated with the database handle $dbhandle, include the following Perl statement in your application:

my $sqlcode = $dbhandle->err;

The errstr method returns a message for an SQLCODE associated with a Perl DBI database handle or statement handle. I recommend using this method, as it gives you more information about the failure of the SQL statement.

The example in Listing 3 demonstrates the use of that method:

Listing 3. The errstr method of returning error messages

$dbh = DBI->connect("dbi:DB2:sample","USERID","password") or die "Can't connect to sample database: $DBI::errstr"; $sth = $dbh->prepare("SQL statement") or die "Cannot prepare: " $dbh->errstr; $sth->>execute() or die "Cannot execute: " $sth->errstr;

Now, let's go together thru lab #1. It demonstrates a Perl program that connects to database SAMPLE with user ID and password that are passed to the program as parameters. When a valid user ID and password have been passed, a message indicating successful connection is returned. Here is the code for lab1.pl:

Listing 4. lab1.pl

#!/usr/local/bin/perl —w use DBI; $db2user = $ARGV[0]; $pasw = $ARGV[1]; # Open a connection $dbh = DBI->connect("dbi:DB2:sample", $db2user, $pasw) or "Can't connect to sample database: $DBI::errstr"; print "Connection is successful !!\n";

Figure 2 shows the result of executing this program with valid and invalid authentication:

Figure 2. Executing lab1.pl
 

Execute SQL statements

Let's write a program creates table PT_ADDR_BOOK in the database SAMPLE. To execute the SQL statement that is known at the time the application is written, we can use the $dbh->do method. The syntax for that method is as follows:

my $cnt = $dbh->do(SQL statement);

where $cnt is the number of rows affected by the SQL statement.

Using this method, our program to create a DB2 table is shown in Listing 5:

Listing 5. Program to create a DB2 table

#!/usr/local/bin/perl -w use DBI; use DBD::DB2::Constants; $dbh = DBI->connect("dbi:DB2:sample","","") or die "Can't connect to sample database: $DBI::errstr"; $rcount = $dbh->do ("CREATE TABLE PT_addr_book(name char(30), phone char(10))"); print "Returns: $rcount\n";

We can use the same do method to insert rows into the PT_addr_book table. Please note that the values for the inserted column are known at the time when this program is written and can be hard-coded.

Listing 6. Using do method to insert rows

#!/usr/local/bin/perl -w use DBI; use DBD::DB2::Constants; $dbh = DBI->connect("dbi:DB2:sample","","") or die "Can't connect to sample database: $DBI::errstr"; $rcount = $dbh-> do ("Insert into PT_ADDR_BOOK values ('Gregory Whales','9142712020'), ('Robert Moses', 2127652345')"); print "Returns: $rcount \n";

As you can see from this example, two rows are affected by our SQL statement. By running SELECT statement against this table from DB2 CLP, we can confirm that two rows have been inserted into the table.

Listing 7. test_do_exs.pl

$perl test_do_exs.pl Returns : 2 $db2 "select * from PT_ADDR_BOOK" NAME PHONE ------------------------------ ---------- Gregory Whales 9142712020 Robert Moses 2127652345 2 record(s) selected.

Please use the exercise from Lab #2 in the downloads to write and execute a simple Perl program to update PR_ADDR_BOOK table.

INSERT, UPDATE, and DELETE statements -- no placeholder

Execution of an SQL statement that is unknown at the time the application is written (dynamic SQL) requires a different technique and can be accomplished by using the $dbh->prepare method. Dynamic SQL is characterized by its ability to change columns, tables, and predicates during a program's execution. Dynamic SQL needs to be prepared execution by the UDB optimizer in order to create an access plan for the statement. If dynamic SQL does not have parameter markers (placeholders), it could be immediately executed after that step. Listing 8 shows the syntax for the $dbh->prepare and $sth->execute methods to embed SQL statements without placeholders into a Perl program:

Listing 8. Dynamic SQL prepare and execute

$stmt = "SQL Statement without placeholder"; $sth = $dbh->prepare($stmt); $sth->execute();

Please note that result of the $dbh->prepare method is SQL Statement handle.

The Perl program in Listing 9 demonstrates how to insert a row into the PT_ADDR_BOOK table using these methods:

Listing 9. Insert a row

#!/usr/local/bin/perl -w use DBI; use DBD::DB2::Constants; $dbh = DBI->connect("dbi:DB2:sample","","") or die "Can't connect to sample database: $DBI::errstr"; $stmt = "INSERT INTO PT_addr_book values ('JOHN SMITH','9145556677')"; $sth = $dbh->prepare($stmt); $sth->execute(); print "We inserted row into addr_book\n"; $sth->finish();

SQL statements with parameter markers

Now let's see how to execute an SQL statement which is unknown at the time the application is written and has parameter markers or placeholders; that is, a truly dynamic SQL statement. Please note that such SQL statements are recommended and yield performance and security advantages. From the performance perspective, dynamic SQL statement can be prepared once and executed multiple times, reusing the same database access plan. From the security perspective, placeholders ensure that only a single value gets bound into the statement, preventing SQL statement error.

In order for a PERL program to transform a dynamic SQL statement into executable form, you need to first use the preparemethod, and then bind parameters using the bind_param method. Only then can you execute this statement. To bind each parameter, you need to specify the number of parameter markers and local Perl variables that contains the values of the parameter markers. Listing 10 shows the syntax for the methods that allow Perl programs to execute dynamic SQL statements:

Listing 10. Methods for dynamic SQL

$stmt = "SQL Statement with parameter marker"; $sth = $dbh->prepare($stmt); $sth->bind_param(1,$parm,\% attr); $sth->execute();

Listing 11 demonstrates INSERT into table PT_ADDR_BOOK. Now, values for inserted columns are not hard-coded within the SQL statement, but are passed into that statement dynamically using parameter markers that bind into that statement using the$sth->bind_param method. Only after that, the statement is executed.

Listing 11. INSERT into table PT_ADDR_BOOK

#!/usr/local/bin/perl -w use DBI; use DBD::DB2::Constants; $dbh = DBI->connect("dbi:DB2:sample","","") or die "Can't connect to sample database :DBI::errstr"; $name ="STEVE BROWN"; $phone = "7184358769"; $stmt = "INSERT INTO PT_addr_book values (?,?)"; $sth = $dbh->prepare($stmt); $sth->bind_param(1,$name); $sth->bind_param(2,$phone); $sth->execute(); print "We inserted row into addr_book\n"; $sth->finish();

Retrieve data from a database -- single result

In order to get a value from a database into a Perl program, follow these steps:

1. Prepare the SELECT statement.
2. Execute the prepared statement.
3. associate a value for the column (or function) with a local Perl variable using the $sth->bind_col method.
4. Fetch a value into the local variable using $sth->fetch method.

The following pseudo-code demonstrates how single values can be retrieved from a database:

Listing 12. Retrieving single values from a database

$stmt = "SQL SELECT Statement to be executed"; $sth = $dbh->prepare($stmt); $sth->execute(); $result = $sth->bind_col(col, \variable [, \%attr ]); while ($sth->fetch){ process result of the fetch; }

The next example shows how you can retrieve the value for the aggregate function max for the salary column from the EMPLOYEE table:

Listing 13. Retrieving a value for the aggregate function

#!/usr/local/bin/perl -w use DBI; use DBD::DB2::Constants; $dbh = DBI->connect("dbi:DB2:sample","","") or die "Can't connect to sample database: $DBI::errstr"; $stmt = "SELECT max(salary) from EMPLOYEE"; $sth = $dbh->prepare($stmt); $sth->execute(); #associate variable with output columns... $sth->bind_col(1,\$max_sal); while ($sth->fetch) { print "The biggest salary is: $max_sal\n"; }

Here is the result of executing this Perl program:

Listing 14. Aggregate value results

$perl test_return_value.pl The biggest salary is: 52750.00

Retrieve data from a database -- multiple results

The technique to return a result set (or multiple results) from a database into a Perl program is pretty much the same. After an SQL statement returning multiple results is prepared and executed, and returned values (columns) bound to local variables, you would need to use the $sth->fetch method to retrieve those values. To demonstrate how it works, let's retrieve phone and name columns values from PT_ADDR_BOOK table. Listing 15 shows the use of the $sth_fetch method in the iterative way:

Listing 15. INSERT into table PT_ADDR_BOOK

$stmt = "SELECT name, phone from PT_ADDR_BOOK"; $sth = $dbh->prepare($stmt); $sth->execute(); #associate variables with output columns... $sth->bind_col(1,\$name); $sth->bind_col(2,\$phone); print "NAME PHONE \n"; print "------------------------- -----------\n"; while ($sth->fetch) { print $name ; print $phone; print "\n"; } print "DONE \n"; $sth->finish();

Here are the results of executing that code:

Listing 16. INSERT into table PT_ADDR_BOOK

C:\Dev_POT\PERL\Labs>perl multi.pl NAME PHONE ------------------------- ----------- JOHN SMITH 9145556677 STEVE BROWN 7184358769 DONE

You can also use method $sth->fetchrow to retrieve values from result set. While $sth ->fetch method returns each value as a separate entity, $sth ->fetchrow() method returns a row as an array with one value per column.

The same Perl program can be written using the fetchrow method as follows:

Listing 17. INSERT into table PT_ADDR_BOOK

$stmt = "SELECT name, phone from PT_ADDR_BOOK"; $sth = $dbh->prepare($stmt); $sth->execute(); print "NAME PHONE \n"; print "------------------------- -----------\n"; while (($name, $phone) = $sth->fetchrow()) { print "$name $phone\n"; }

Use the exercise from Lab #3 to write and execute a Perl program that will create a DB2 table, insert values into that table, and return the number of rows inserted into the table.

Call a stored procedure

Let's create a multi-step scenario to see how a DB2 stored procedure is called from a Perl program. First, let's create a simple stored procedure SP_GET_LOC that returns the location for a given department from the table ORG (step 1).

Listing 18. Step 1: Create stored procedure

create procedure sp_get_loc (in deptin int, out loc varchar(13)) begin select location into loc from org where deptnumb = deptin; end @

Please note that this procedure has one input and one output parameter. When we run this procedure from DB2 command line processor (CLP), it returns the location NEW YORK for department 10.

Listing 19. Step 2: Run stored procedure

$db2 "call sp_get_loc(10,?)" Value of output parameters -------------------------- Parameter Name : LOC Parameter Value : New York Return Status = 0

Now let's write a simple Perl program to invoke a stored procedure SP_GET_LOC (step 3, see Listing 20). Our dynamic SQL statement is, in fact, the same statement as the one we issue from DB2 CLP command: SP_GET_LOC(?,?), but instead of passing a hard-coded 10 (department number), we use a parameter marker. This way we can bind any value of the department number column from the ORG table.

After we compose our SQL statement, all the other steps are the same as for any dynamic statement with parameter markers. Prepare with $sth = $dbh->prepare method. Bind department number as input parameter with $sth->bind_param method, bind returned location as output parameter using $sth->bind_param_inout method, and then execute our dynamic SQL statement.

Here is that program:

Listing 20. Step 3: Invoke the stored procedure

#!/usr/bin/perl -w use DBI; use DBD::DB2::Constants; $dbh = DBI->connect("dbi:DB2:sample","","") or die "Can't connect to sample database: $DBI::errstr"; # Prepare our call statement $sth = $dbh->prepare( "CALL SP_GET_LOC(?,?)" ); # Bind input parameter for department number $sth->bind_param(1, 10); # Bind output parameter - location $sth->bind_param_inout (2, \$location, 13, db2_param_type=>SQL_PARAM_OUTPUT}); # Call the stored procedure $sth->execute(); printf("Stored procedure returned location: %s\n", $location); $sth->finish(); $dbh->disconnect;

If we execute this program, we will get back LOCATION for a given (10 in this case) department:

Listing 21. Step 4. Execute Perl program that calls a DB2 stored procedure

$perl test_call_sp.pl Stored Procedure returned location: New York

Large object manipulation

Working with files in Perl is much easier than in more complex languages, such as C or Java®. We will examine how we can populate data from files directly into DB2 large object data (LOB) columns. The most efficient way to insert large object data is to directly bind the file to an input parameter associated with a LOB column type in a DB2 table. The Perl driver will read directly from the file and transfer the data to the database server. To bind a file to an input LOB parameter, specify the { db2_file => 1} parameter attribute when you are using the bind_param method during an INSERT operation.

We have the table MAP in our database under schema POT. The table has column picture that contains an image of an area declared as BLOB. Here is the DDL for this table:

Listing 22. DDL for MAP table

create table POT.MAPS ( map_id INT, map_name VARCHAR(13), area INT , photo_format CHAR(3), picture BLOB) ;

Also, we have file pearcson.jpg, containing a map of Pearson Airport:

Figure 3. Sample map
 

Now, let's write a program that will insert a row into the table POT.MAP, including image from the JPG file into the column PICTURE. First, we will compose a dynamic SQL statement with five parameter markers. Then, we prepare that statement. Before binding our parameters to the prepared statement, we need to specify the name of the file that contains the image to be inserted, and assign it to a local Perl variable ($picture_file). Now we can bind all our parameters to values we need to insert into the MAP table. Please note that we specify attribute db2_file =>1 for the last parameter. The last step is to execute the INSERT statement. Here is the code for that program:

Listing 23. Inserting a LOB

#!/usr/bin/perl -w use DBI; use DBD::DB2::Constants; %conattr = ( AutoCommit => 1, # Turn Autocommit On db2_info_applname => 'Maps Module', ); # Identify this appl $dbh = DBI->connect("dbi:DB2:sample","", "",\%conattr) or die "$DBI::errstr"; $dbh->do("SET CURRENT SCHEMA POT"); $sql = "INSERT INTO MAPS(map_id, map_name, area, photo_format, picture) VALUES(?,?,?,?,?)"; $sth = $dbh->prepare($sql); $picture_file = "pearson.jpg"; # File containing our picture $sth->bind_param(1, 100); # map_id $sth->bind_param(2, "Pearson airport"); # map_name $sth->bind_param(3, 416); # area $sth->bind_param(4, "JPG"); # photo_format $sth->bind_param(5, $picture_file, {db2_file => 1}); $rows_affected = $sth->execute(); printf("%d rows affected", $rows_affected); $sth->finish(); $dbh->disconnect;

Reading the LOB from the database

You can retrieve LOB data using the standard fetch methods, such as fetchrow_array or fetchrow_arrayref. DBI lets you set a maximum number of bytes to retrieve on each fetch using the LongReadLen connection attribute. The default value is 32,700 bytes for LOB columns. To do that we need to perform the following steps:

1. Compose the SQL statement to select picture from the MAP table.
2. Prepare the SQL statement.
3. Assign a name for the file where the retrieved image will be stored.
4. Open that file.
5. Execute the SQL statement.
6. Use fetch method to fetch result into file.

Here is the code that demonstrates how you can retrieve a LOB from a database:

Listing 24. INSERT into table PT_ADDR_BOOK

#!/usr/bin/perl use DBI; use DBD::DB2::Constants; %conattr = ( AutoCommit => 1, # Turn Autocommit On db2_info_applname => 'Maps Module', # Identify this appl LongReadLen => 80000 # Don't retrieve LOBs ); # Connect to our database $dbh = DBI->connect("dbi:DB2:sample","", "",\%conattr) or die "$DBI::errstr"; # Set the current schema to 'POT' $dbh->do("SET CURRENT SCHEMA POT"); $sql = "SELECT picture FROM maps WHERE map_name ='Pearson airport'"; # Prepare the statement $sth = $dbh->prepare($sql); # Open output file $out_file = "mypic.jpg"; open(OUTPUT, ">$out_file") or die "Cannot open $out_file because $!"; binmode OUTPUT; $sth->execute; @row = $sth->fetchrow; print OUTPUT $row[0]; @row = ""; close(OUTPUT); print "Picture in the file $out_file\n"; $sth->finish(); $dbh->disconnect;

After you run this program, the image will be placed in mypic.jpg file.

Listing 25. INSERT into table PT_ADDR_BOOK

$perl test_lobread.pl Picture in the file mypic.jpg

Please use the exercise from Lab #4 to write and execute a Perl program that will retrieve a binary large object from a table into a file.

Conclusion

This article was developed for Perl programmers who have experience with a relational database and would like to learn how write Perl programs that access DB2 database. In the course of this article, you learned how to connect to a database, and how to manipulate database content via INSERT, UPDATE and DELETE statements. You also learned how to retrieve data from a database, along with more advanced topics including calling stored procedures and manipulating Large Data Objects (LOBs and BLOBs). Now you should be ready to set out on your own with your new Perl DB2 skills.

Back to top

Download

Description	Name	Size	Download method
Perl labs for this article	PERL_DB2_tutorial_labs.zip	89 KB	FTP

Information about download methods