How do I use connect to DB2 with DBI and mod_perl?

Question

I'm having issues with getting DBI's IBM DB2 driver to work with mod_perl. My test script is:

#!/usr/bin/perl

use strict;
use CGI;
use Data::Dumper;
use DBI;

{
    my $q;
    my $dsn;
    my $username;
    my $password;
    my $sth;
    my $dbc;
    my $row;

    $q = CGI->new;
    print $q->header;
    print $q->start_html();

    $dsn = "DBI:DB2:SAMPLE";
    $username = "username";
    $password = "password";

    print "<pre>".$q->escapeHTML(Dumper(\%ENV))."</pre>";

    $dbc = DBI->connect($dsn, $username, $password);

    $sth = $dbc->prepare("SELECT * FROM SOME_TABLE WHERE FIELD='SOMETHING'");
    $sth->execute();
    $row = $sth->fetchrow_hashref();
    print "<pre>".$q->escapeHTML(Dumper($row))."</pre>";

    print $q->end_html;
}

This script works as CGI but not under mod_perl. I get this error in apache's error log:

DBD::DB2::dr connect warning: [unixODBC][Driver Manager]Data source name not found, and no default driver specified at /usr/lib/perl5/site_perl/5.8.8/Apache/DBI.pm line 190.
DBI connect('SAMPLE','username',...) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified at /data/www/perl/test.pl line 15

First of all, why is it using ODBC? The native DB2 driver is installed (hence it works as CGI).

Running Apache 2.2.3, mod_perl 2.0.4 under RHEL5.

This guy had the same problem as me: http://www.mail-archive.com/[email protected]/msg22909.html But I have no idea how he fixed it. What does mod_php4 have to do with mod_perl?

Any help would be greatly appreciated, I'm having no luck with google.

Update:

As james2vegas pointed out, the problem has something to do with PHP: I disable PHP all together I get the a different error:

 Total Environment allocation failure! Did you set up your DB2 client environment?

I believe this error is to do with environment variables not being set up correctly, namely DB2INSTANCE. However, I'm not able to turn off PHP to resolve this problem (I need it for some legacy applications). So I now have 2 questions:

1. How can I fix the original issue without disabling PHP all together?
2. How can I fix the environment issue?

I've set DB2INSTANCE, DB2_PATH and SQLLIB variables correctly using SetEnv and PerlSetEnv in httpd.conf, but with no luck.

Note: I've edited the code to determine if the problem was to do with Global Variable Persistence.

Answer 1

Plain old DBI won't work in mod_perl; it goes all wibbly when your process forks and you try to use your db handle again. You need to use Apache::DBI (it's a drop-in replacement for DBI), or even better, use a post-modern DBI wrapper like DBIx::Connector.

You can read more details here, at Apache's guide to using mod_perl with relational databases.

Answer 2

This appears solved, for reference: disable PHP's ODBC support in /etc/php.d/pdo_odbc.ini, set the environment variables and preload DBD::DB2 in mod_perl's startup script, though it might be possible to use PERL_DL_NONLAZY set to 1 to force loading of the correct library.