Back with the same DB2/PHP problem

[Robert Stearns]

After working well for most of a week, my application with the
mysterious php/db2 problem is now failing again. No one has worked on
the php code this week.

We're running DB2 8.1.5, php 4.3.6 with db2 and apache options, Apache
and Linux.

We've been working on the database itself, recreating it several times
from its original sequential files to incorporate both new data and some
obscure structural changes. During this entire time we received no error
messages or application failures. This morning it began to fail
regularly. I tried rebooting the server, rebuilding the database,
looking for orphan sql processes/applications with list applications,
looked for orphan db2 tasks with ps -fe. All showed nothing. Found a few
orphans with:

db2 get snapshot for database on animals | grep oldest

and cleaned them up with:

db2 force application \(<number>\)

It did not affect the problem. I an still getting:

Warning: odbc_connect(): SQL error: Ä–ÿ¿|궰—ÿ¿Û, SQL state
ýÿÿÿ”_&ýÿÿÿÜ^& \_&ýÿÿÿøšÿ¿K/·D›ÿ¿ in SQLConnect in
/var/www/html/GEM/db.php on line 32

Added by my routine:

The odbc_connect parameters were:[animals] [xxxxxxxx] [xxxxxxxx]

[ýÿÿÿ”_][strlen(ýÿÿÿ”_)]

Error number ýÿÿÿ”_=X'fdffff ff945f';
[Ä–ÿ¿|궰—ÿ¿Û][strlen(Ä–ÿ¿|ê¶ °—ÿ¿Û)]

Error message Ä–ÿ¿|궰—ÿ¿Û= X'c496ffbf7cea1 cb6b097ffbfdb03 ';
could not connect to animals

The routine and its support routines are below:

<?php
// vim: set ts=4 sw=4 ai:
function hex($s) {
if($s=='') { return ''; }
echo "<br>[$s][strlen($s)]<br>";
$h = '';
for($i=0;$i<str len($s);$i++) {
$t = dechex(ord($s[$i]));
if(strlen($t)== 1) { $h .= '0'; }
$h .= $t;
}
return $h;
}

function showerror() {
$t = odbc_error();
echo "<br>Error number ".$t."=X'".hex( $t)."';";
$t = odbc_errormsg() ;
echo "<br>Error message ".$t."=X'".hex( $t)."';<br>";
}

function conn() {
global $schema, $database;
$schema = "is3";
$database = "animals";
$dbuser = "xxxxxxxx";
$dsn = "Driver=DB2;Ser ver=localhost;D atabase=$databa se";
$dsn = $database;
$dbpwd = "xxxxxxx";
$dbConn = odbc_connect($d sn,$dbuser,$dbp wd);
if ( $dbConn==False) {
printf("<br>The odbc_connect parameters were:[%s] [%s] [%s]<br>\n",
$dsn,$dbuser,$d bpwd);
showerror();
odbc_close_all( );
die("could not connect to $database");
}
return $dbConn;
}
?>

FWIW db2 seems to be functioning well, both locally and remotely.
Similarly php as long as I stay away from the db2 ODBC routines. Any
further suggestions would be greatly appreciated, I'm getting desperate.

[Dan Scott]

Hi Robert:

It looks like the newsgroup server dropped my post, so this is a really
late reply. But I replied earlier, honest!

1. Are you still using the same instance? For example, if you modified
/etc/init.d/httpd to include ". /home/db2inst1/sqllib/db2profile" but
your instance is now located under /home/instance/ you could run into
problems. It's also possible that a Red Hat update might have replaced
the /etc/init.d/httpd file, wiping out your change -- so it's worth
checking the file again :)

2. Can you connect to the [animals] database using the same user ID as
you are using in your PHP script? It's possible that someone might have
revoked the privileges for that user on the database tables. Try logging
on to the Linux box as the instance-owning user and connecting
directly from the command line to rule that out as a possibility.

3. Does /home/db2inst1/sqllib/db2profile (adjust for whatever instance
you're using) still exist and still have permissions that allow other
users to read the file? It's possible that an over-zealous sysadmin
tightened up the security for the user due to an audit without
understanding the implications of that action.

4. What does your php.ini file look like, specifically the ODBC section?
Are you using persistent connections?

5. Does your application issue odbc_disconnect ()? It's possible that
your application is using up all of the available connections to the
database, which would certainly prevent you from being able to
connect--although list applications should show that.

Items 4 and 5 are more reflective of problems that you would see
developing over time, rather than seeing the problem immediately on
reboot. And since nobody touched the PHP code, it's unlikely that the
problem lies there, which leaves only the database and system
environment as the real suspects.

Robert Stearns wrote:

After working well for most of a week, my application with the
mysterious php/db2 problem is now failing again. No one has worked on
the php code this week.

We're running DB2 8.1.5, php 4.3.6 with db2 and apache options, Apache
and Linux.

We've been working on the database itself, recreating it several times
from its original sequential files to incorporate both new data and some
obscure structural changes. During this entire time we received no error
messages or application failures. This morning it began to fail
regularly. I tried rebooting the server, rebuilding the database,
looking for orphan sql processes/applications with list applications,
looked for orphan db2 tasks with ps -fe. All showed nothing. Found a few
orphans with:

db2 get snapshot for database on animals | grep oldest

and cleaned them up with:

db2 force application \(<number>\)

It did not affect the problem. I an still getting:

Warning: odbc_connect(): SQL error: Ä–ÿ¿|궰—ÿ¿Û, SQL state
ýÿÿÿ”_&ýÿÿÿÜ^& \_&ýÿÿÿøšÿ¿K/·D›ÿ¿ in SQLConnect in
/var/www/html/GEM/db.php on line 32

Added by my routine:

The odbc_connect parameters were:[animals] [xxxxxxxx] [xxxxxxxx]

[ýÿÿÿ”_][strlen(ýÿÿÿ”_)]

Error number ýÿÿÿ”_=X'fdffff ff945f';
[Ä–ÿ¿|궰—ÿ¿Û][strlen(Ä–ÿ¿|ê¶ °—ÿ¿Û)]

Error message Ä–ÿ¿|궰—ÿ¿Û= X'c496ffbf7cea1 cb6b097ffbfdb03 ';
could not connect to animals

The routine and its support routines are below:

<?php
// vim: set ts=4 sw=4 ai:
function hex($s) {
if($s=='') { return ''; }
echo "<br>[$s][strlen($s)]<br>";
$h = '';
for($i=0;$i<str len($s);$i++) {
$t = dechex(ord($s[$i]));
if(strlen($t)== 1) { $h .= '0'; }
$h .= $t;
}
return $h;
}

function showerror() {
$t = odbc_error();
echo "<br>Error number ".$t."=X'".hex( $t)."';";
$t = odbc_errormsg() ;
echo "<br>Error message ".$t."=X'".hex( $t)."';<br>";
}

function conn() {
global $schema, $database;
$schema = "is3";
$database = "animals";
$dbuser = "xxxxxxxx";
$dsn = "Driver=DB2;Ser ver=localhost;D atabase=$databa se";
$dsn = $database;
$dbpwd = "xxxxxxx";
$dbConn = odbc_connect($d sn,$dbuser,$dbp wd);
if ( $dbConn==False) {
printf("<br>The odbc_connect parameters were:[%s] [%s] [%s]<br>\n",
$dsn,$dbuser,$d bpwd);
showerror();
odbc_close_all( );
die("could not connect to $database");
}
return $dbConn;
}
?>

FWIW db2 seems to be functioning well, both locally and remotely.
Similarly php as long as I stay away from the db2 ODBC routines. Any
further suggestions would be greatly appreciated, I'm getting desperate.

[Dan Scott]

Dan Scott wrote:

Hi Robert:

It looks like the newsgroup server dropped my post, so this is a really
late reply. But I replied earlier, honest!

1. Are you still using the same instance? For example, if you modified
/etc/init.d/httpd to include ". /home/db2inst1/sqllib/db2profile" but
your instance is now located under /home/instance/ you could run into
problems. It's also possible that a Red Hat update might have replaced
the /etc/init.d/httpd file, wiping out your change -- so it's worth
checking the file again :)

2. Can you connect to the [animals] database using the same user ID as
you are using in your PHP script? It's possible that someone might have
revoked the privileges for that user on the database tables. Try logging
on to the Linux box as the instance-owning user and connecting directly
from the command line to rule that out as a possibility.

3. Does /home/db2inst1/sqllib/db2profile (adjust for whatever instance
you're using) still exist and still have permissions that allow other
users to read the file? It's possible that an over-zealous sysadmin
tightened up the security for the user due to an audit without
understanding the implications of that action.

4. What does your php.ini file look like, specifically the ODBC section?
Are you using persistent connections?

5. Does your application issue odbc_disconnect ()? It's possible that
your application is using up all of the available connections to the
database, which would certainly prevent you from being able to
connect--although list applications should show that.

Items 4 and 5 are more reflective of problems that you would see
developing over time, rather than seeing the problem immediately on
reboot. And since nobody touched the PHP code, it's unlikely that the
problem lies there, which leaves only the database and system
environment as the real suspects.

Robert Stearns wrote:

After working well for most of a week, my application with the
mysterious php/db2 problem is now failing again. No one has worked on
the php code this week.

We're running DB2 8.1.5, php 4.3.6 with db2 and apache options, Apache
and Linux.

We've been working on the database itself, recreating it several times
from its original sequential files to incorporate both new data and
some obscure structural changes. During this entire time we received
no error messages or application failures. This morning it began to
fail regularly. I tried rebooting the server, rebuilding the database,
looking for orphan sql processes/applications with list applications,
looked for orphan db2 tasks with ps -fe. All showed nothing. Found a
few orphans with:

db2 get snapshot for database on animals | grep oldest

and cleaned them up with:

db2 force application \(<number>\)

It did not affect the problem. I an still getting:

Warning: odbc_connect(): SQL error: Ä–ÿ¿|궰—ÿ¿Û, SQL state
ýÿÿÿ”_&ýÿÿÿÜ^& \_&ýÿÿÿøšÿ¿K/·D›ÿ¿ in SQLConnect in
/var/www/html/GEM/db.php on line 32

Added by my routine:

The odbc_connect parameters were:[animals] [xxxxxxxx] [xxxxxxxx]

[ýÿÿÿ”_][strlen(ýÿÿÿ”_)]

Error number ýÿÿÿ”_=X'fdffff ff945f';
[Ä–ÿ¿|궰—ÿ¿Û][strlen(Ä–ÿ¿|ê¶ °—ÿ¿Û)]

Error message Ä–ÿ¿|궰—ÿ¿Û= X'c496ffbf7cea1 cb6b097ffbfdb03 ';
could not connect to animals

The routine and its support routines are below:

<?php
// vim: set ts=4 sw=4 ai:
function hex($s) {
if($s=='') { return ''; }
echo "<br>[$s][strlen($s)]<br>";
$h = '';
for($i=0;$i<str len($s);$i++) {
$t = dechex(ord($s[$i]));
if(strlen($t)== 1) { $h .= '0'; }
$h .= $t;
}
return $h;
}

function showerror() {
$t = odbc_error();
echo "<br>Error number ".$t."=X'".hex( $t)."';";
$t = odbc_errormsg() ;
echo "<br>Error message ".$t."=X'".hex( $t)."';<br>";
}

function conn() {
global $schema, $database;
$schema = "is3";
$database = "animals";
$dbuser = "xxxxxxxx";
$dsn = "Driver=DB2;Ser ver=localhost;D atabase=$databa se";
$dsn = $database;
$dbpwd = "xxxxxxx";
$dbConn = odbc_connect($d sn,$dbuser,$dbp wd);
if ( $dbConn==False) {
printf("<br>The odbc_connect parameters were:[%s] [%s]
[%s]<br>\n",
$dsn,$dbuser,$d bpwd);
showerror();
odbc_close_all( );
die("could not connect to $database");
}
return $dbConn;
}
?>

FWIW db2 seems to be functioning well, both locally and remotely.
Similarly php as long as I stay away from the db2 ODBC routines. Any
further suggestions would be greatly appreciated, I'm getting desperate.

Sorry for following up on my own post, but for the archives the solution
turned out to be the ". /home/db2inst1/sqllib/db2profile" line had been
added to one of the Apache start-up scripts (apachectl, I believe), but
not to /etc/init.d/httpd -- which actually bypasses apachectl. Once
/etc/init.d/httpd had been updated to include that line, PHP was able to
connect to DB2 without problems.

Note that on Red Hat Enterprise Linux the safest place to put such a
line might be in a new file in the /etc/httpd/conf.d/ directory; e.g.
/etc/httpd/conf.d/db2.conf. In RHEL 3.0 /etc/init.d/httpd reads the
contents of all files in the /etc/httpd/conf.d/ directory, and you're
guaranteed not to run into a conflict if you have to update your Apache
(er, httpd) RPMs.

Dan