Back with the same DB2/PHP problem

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'fdffffff945f';
[Ä–ÿ¿|궰—ÿ¿Û][strlen(Ä–ÿ¿|궰—ÿ¿Û)]

Error message Ä–ÿ¿|궰—ÿ¿Û=X'c496ffbf7cea1cb6b097ffbfdb03';
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<strlen($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;Server=localhost;Database=$databas e";
$dsn = $database;
$dbpwd = "xxxxxxx";
$dbConn = odbc_connect($dsn,$dbuser,$dbpwd);
if ( $dbConn==False) {
printf("<br>The odbc_connect parameters were:[%s] [%s] [%s]<br>\n",
$dsn,$dbuser,$dbpwd);
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.

re: Back with the same DB2/PHP problem

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:
[color=blue]
> 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'fdffffff945f';
> [Ä–ÿ¿|궰—ÿ¿Û][strlen(Ä–ÿ¿|궰—ÿ¿Û)]
>
> Error message Ä–ÿ¿|궰—ÿ¿Û=X'c496ffbf7cea1cb6b097ffbfdb03';
> 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<strlen($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;Server=localhost;Database=$databas e";
> $dsn = $database;
> $dbpwd = "xxxxxxx";
> $dbConn = odbc_connect($dsn,$dbuser,$dbpwd);
> if ( $dbConn==False) {
> printf("<br>The odbc_connect parameters were:[%s] [%s] [%s]<br>\n",
> $dsn,$dbuser,$dbpwd);
> 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.
>[/color]

re: Back with the same DB2/PHP problem

Dan Scott wrote:
[color=blue]
> 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:
>[color=green]
>> 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'fdffffff945f';
>> [Ä–ÿ¿|궰—ÿ¿Û][strlen(Ä–ÿ¿|궰—ÿ¿Û)]
>>
>> Error message Ä–ÿ¿|궰—ÿ¿Û=X'c496ffbf7cea1cb6b097ffbfdb03';
>> 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<strlen($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;Server=localhost;Database=$databas e";
>> $dsn = $database;
>> $dbpwd = "xxxxxxx";
>> $dbConn = odbc_connect($dsn,$dbuser,$dbpwd);
>> if ( $dbConn==False) {
>> printf("<br>The odbc_connect parameters were:[%s] [%s]
>> [%s]<br>\n",
>> $dsn,$dbuser,$dbpwd);
>> 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.
>>[/color][/color]

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