Perl DBD::DB2 DBI - So fun?

So I have some perl scripts written by someone else, targeted for AIX and I need to get them running on Linux 2.6.  Little did I know how much fun I would have.  I started having so much fun I decided to document it.  Really because if I was asked to do it again, I do not want to go through so much fun all over again!  Ha!  I should also mention that googling will get you the answers, but not all in one place.

Caveat:  I don't want to port these scripts.  I want to keep them as is as much as possible.

This is what the script does at one point:

$dbh = DBI->connect('DBD:DB2:db2_alias','user','pwd');

So the DBI / DB2 stuff was installed on AIX.  I need to install it on Linux to make this work.

Note About DB2 Client

Note the DB2 (IBM Data Server) has more than one type of client and it matters how you install it.  There is a bare bones "Runtime Client" and just a "Client".  The Runtime client does not have all the cool stuff like c/c++ include headers and nice config wizards.  I prefer the normal Client and installing it with all of the advanced features like the c/c++ libraries.

Step 1
Download DB2 Client for Linux 64.  You could try to use the cpan command to install DBI and DBD::DB2 first, but that would just tell you that you need the DB2 Client as a prereq.  You might get an error that it cannot find sqlcli.h.  (Not that I did this... ;)  Do not download the "Runtime Client" (RTCL) - download the "Client" (CLIENT).  I installed in the standard location, /opt/ibm/db2/V9.1.3.  Make sure you have the correct permissions on the files, folders.

Step 2
Use the perl cpan command (as root) to install DBD::DB2 like so.

cpan DBD::DB2


Say yes a few times and watch the madness of cpan.  It looks like the matrix to me.  Make sure your permissions are correct on the DB2.pm, Constants.pm, and do a find for Constants.so to make sure the permissions are ok on that too.  (I confess I did a chmod -R to get everything the way I wanted it).

Step 3
Sorry, but this is getting a bit foggy.  I've been through so many failed iterations, it's hard to remember what actually worked.  I think I downloaded DBD-DB2-1.82.tar.gz from cpan.  I think I used wget to get it right from linux.  Then I did a tar -xzvf DBD-DB2-1.82.tar.gz.  Then I did this:

perl Makefile.PL

make

make test

make install

Step 4
Now if you run a perl script that does the DBI->connect, you will get an error like, "Total environment allocation failure."  I like that error message.  Ha!
You need to create a user, catalog a database, etc. whatever.
Create a user with a new group.  The setup does not like it when you have the user in a group like "user".

sudo su - root

/usr/sbin/useradd -c DB2_Linux_User -d /home/db2lnx -m -g group some_group db2lnx

passwd db2lnx

exit # from root shell

su - db2lnx # to test the login and home dir.

Your commands may look quite a bit different depending on your security concerns.  I also had to add my user to a /etc/user.permits file.

Step 5
Go to /opt/ibm/db2/V9.1.3/instance and run db2isetup.  It's a nice wizard.  You already created the user, so choose the options to create a new database connection with an existing user.  Mine was successful, but it had one error saying it failed to do the db2icrt, which I want to think stands for Instance CReaTe.

Step 6
Run db2icrt.  If your user is messed up, you will get errors like: "DBI1088E Invalid access permission detected for directory /home/db2lnx".  A successful run will look like this:

$ sudo ./db2icrt tprslnx

DBI1070I Program db2icrt completed successfully.

Now you will have a /home/db2lnx/sqllib folder.

Step 7
Catalog the node and instance.  I'm assuming you have an existing DB2 database to connect to.  Follow the commands to catalog the node and catalog the database.  Then test the database.  Here is what I did:

login db2lnx
source /home/db2lnx/sqllib/db2profile
db2
catalog tcpip node abc remote abc.domain.com server 8080
DB20000I The CATALOG TCPIP NODE command completed successfully.
catalog database DBNAME as DBALIAS at node abc 
connect to DBALIAS user userid 
SQL8002N An attempt to connect to a host failed due to a missing DB2 Connect product or invalid license. SQLSTATE=42968

I found out later that db2ca is much easier to use than this command line tool.

Step 8
Obtain a license for the DB2 product.  You need a db2*.lic file.  Register the license file:

sudo /opt/ibm/db2/V9.1.3/db2licm -a /blah/db2/license/db2consv_ee.lic

If you try to connect and type the password wrong, you get this:


db2 => connect to db2mfg user cldb2
Enter current password for cldb2:
SQL30082N  Security processing failed with reason "15" ("PROCESSING FAILURE").
SQLSTATE=08001

If you use the CORRECT password, you get this:


   Database Connection Information

 Database server        = DB2 OS/blah version
 SQL authorization ID   = user
 Local database alias   = DBALIAS

db2 => quit

Step 9

Your perl script still may not work.  Make sure you have the correct instance set up like this.  Also make sure you run your db2profile.

$ENV{DB2INSTANCE} = "tprslnx";

Now my perl script works.  Thank goodness!

At one point I got this error trying to run the db2 command, but I can't remember which step it was. SQL10007N Message "-1390" could not be retrieved. Reason code: "3".

Follow up

Some stuff I've learned since.  Don't forget to run the db2profile before you run any of the db2 commands.

. /home/db2user/sqllib/db2profile.

Also, db2ca is an awesome tool for setting up database connections.  You may find it in /opt/IBM/db2/V9.7/instance.  A cool idea is to open db2ca on one machine, export all the database config data and import the config data on another machine.  It saves a lot of time trying to input all the database connection data for several databases.

See also my question about getting node.js connected to DB2.  Fun times!