db2/php random error

Note: deliberately xposted to the two newsgroups which seem applicable,
after reading several hundred messages from various php groups.

Randomly my application gets the message:

Warning: odbc_connect(): SQL error: O*@|VA@Â§Ã¿Â¿Ã›,
SQL state Ã½Ã¿Ã¿Ã¿|Ã½Ã¿Ã¿Ã¿Ã¼Ã˜\|Ã½Ã¿Ã¿Ã¿Ë†ÂªÃ¿Â¿ËœEW @Ã”ÂªÃ¿Â¿ in
SQLConnect in /var/www/html/GEM/db.php on line 15

db.php is a common module used throughout my application. The error
(with various random trash in the middle) sometime occurs just as the
application is entered for the first time, sometimes after many
minutes/hours of successful operation. The routine, slightly masked for
security appears below. I am running php v 4.3.4 (--with-ibm-db2) and
db2 v 8.1.5 under apache 2.0 on redhat v 8.0.

Any assistance (including RTFM with references, because I have RTFM
until I am cross-eyed) would be greatly appreciated.

<?php
// vim: set ts=4 sw=4 ai:
function showerror($dbConn) {
echo " ";
echo "Error ".odbc_error($dbConn)." ".odbc_errormsg($dbConn);
echo " ";
}
function conn() {
global $schema, $database;
$schema = "is3";
$database = "animalst";
$dbuser = "xxxxxxxx";
$dbpwd = "xxxxxxxx";
$dbConn = odbc_connect($database,$dbuser,$dbpwd);
if ( $dbConn===False) {
showerror($dbConn);
die("could not connect to $database");
}
return $dbConn;
}
?>

Robert Stearns wrote:
[color=blue]
> Note: deliberately xposted to the two newsgroups which seem applicable,
> after reading several hundred messages from various php groups.
>
> Randomly my application gets the message:
>
> Warning: odbc_connect(): SQL error: O*@|VA@Â§Ã¿Â¿Ã›,
> SQL state Ã½Ã¿Ã¿Ã¿|Ã½Ã¿Ã¿Ã¿Ã¼Ã˜\|Ã½Ã¿Ã¿Ã¿Ë†ÂªÃ¿Â¿ËœEW @Ã”ÂªÃ¿Â¿ in
> SQLConnect in /var/www/html/GEM/db.php on line 15
>
>
> db.php is a common module used throughout my application. The error
> (with various random trash in the middle) sometime occurs just as the
> application is entered for the first time, sometimes after many
> minutes/hours of successful operation. The routine, slightly masked for
> security appears below. I am running php v 4.3.4 (--with-ibm-db2) and
> db2 v 8.1.5 under apache 2.0 on redhat v 8.0.
>
> Any assistance (including RTFM with references, because I have RTFM
> until I am cross-eyed) would be greatly appreciated.
>
> <?php
> // vim: set ts=4 sw=4 ai:
> function showerror($dbConn) {
> echo " ";
> echo "Error ".odbc_error($dbConn)." ".odbc_errormsg($dbConn);
> echo " ";
> }
> function conn() {
> global $schema, $database;
> $schema = "is3";
> $database = "animalst";
> $dbuser = "xxxxxxxx";
> $dbpwd = "xxxxxxxx";
> $dbConn = odbc_connect($database,$dbuser,$dbpwd);
> if ( $dbConn===False) {
> showerror($dbConn);
> die("could not connect to $database");
> }
> return $dbConn;
> }
> ?>
>[/color]

could it be some number of connections allowable in DB2 or some resource
that you could be exhausting at the time of problem? You are generally
looking at a resource issue when things work "randomly". CPU, Memory,
database connections count, allowable sockets, Apache connections
available, locking in the database -- this list can go on for a very
long time.... You might also check your ODBC and any applicable GEM
parameters as well.

Not real clear which OS, but obviously some sort of ?***X.

I know that is not a lot of help, and you have probably exhausted your
brain by now, just a fresh perspective....

Michael Austin.
BTW, if you need an onsite consultant... :)

"Robert Stearns" <rstearns1241@charter.net> wrote in message
news:10ec559bvqbmcc8@corp.supernews.com...[color=blue]
> Note: deliberately xposted to the two newsgroups which seem applicable,
> after reading several hundred messages from various php groups.
>
> Randomly my application gets the message:
>
> Warning: odbc_connect(): SQL error: O*@|VA@Â§Ã¿Â¿Ã›,
> SQL state Ã½Ã¿Ã¿Ã¿|Ã½Ã¿Ã¿Ã¿Ã¼Ã˜\|Ã½Ã¿Ã¿Ã¿Ë†ÂªÃ¿Â¿ËœEW @Ã”ÂªÃ¿Â¿ in
> SQLConnect in /var/www/html/GEM/db.php on line 15
>
>
> db.php is a common module used throughout my application. The error
> (with various random trash in the middle) sometime occurs just as the
> application is entered for the first time, sometimes after many
> minutes/hours of successful operation. The routine, slightly masked for
> security appears below. I am running php v 4.3.4 (--with-ibm-db2) and
> db2 v 8.1.5 under apache 2.0 on redhat v 8.0.
>
> Any assistance (including RTFM with references, because I have RTFM
> until I am cross-eyed) would be greatly appreciated.
>
> <?php
> // vim: set ts=4 sw=4 ai:
> function showerror($dbConn) {
> echo " ";
> echo "Error ".odbc_error($dbConn)." ".odbc_errormsg($dbConn);
> echo " ";
> }
> function conn() {
> global $schema, $database;
> $schema = "is3";
> $database = "animalst";
> $dbuser = "xxxxxxxx";
> $dbpwd = "xxxxxxxx";
> $dbConn = odbc_connect($database,$dbuser,$dbpwd);[/color]

$dbConn = @odbc_connect($database,$dbuser,$dbpwd);

The @ at the start of the function will supress any error messages/warning
generated by the function.
[color=blue]
> if ( $dbConn===False) {[/color]

printf("[%s][%s][%s] \n" , $database,$dbuser,$dbpwd);

Check the parameters you are passing to connect function, as they are more
likely to show you what is going wrong. It's unlikely in this case as you've
set them just before calling the connect function.

Check your odbc/php setup, and increase the timeout settings for which ever
database you are using.

Consider using adodb http://adodb.sourceforge.net/ as your database
abstraction layer instead.

"CJ Llewellyn" <satest@tmslifeline.com> wrote in message
news:cc5mlt$acd$1@slavica.ukpost.com...[color=blue]
> "Robert Stearns" <rstearns1241@charter.net> wrote in message
> news:10ec559bvqbmcc8@corp.supernews.com...[/color]
-snip-[color=blue][color=green]
> > Any assistance (including RTFM with references, because I have RTFM
> > until I am cross-eyed) would be greatly appreciated.[/color][/color]

Actually thinking about it, you do need to RTFM!

http://uk2.php.net/manual/en/function.odbc-connect.php

DSN is not a database name as per the other types of database connection
string, but a string describing what database to connect to, and which
driver to use.

CJ Llewellyn wrote:[color=blue]
> "CJ Llewellyn" <satest@tmslifeline.com> wrote in message
> news:cc5mlt$acd$1@slavica.ukpost.com...
>[color=green]
>>"Robert Stearns" <rstearns1241@charter.net> wrote in message
>>news:10ec559bvqbmcc8@corp.supernews.com...[/color]
>
> -snip-
>[color=green][color=darkred]
>>>Any assistance (including RTFM with references, because I have RTFM
>>>until I am cross-eyed) would be greatly appreciated.[/color][/color]
>
>
> Actually thinking about it, you do need to RTFM!
>
> http://uk2.php.net/manual/en/function.odbc-connect.php
>
>
> DSN is not a database name as per the other types of database connection
> string, but a string describing what database to connect to, and which
> driver to use.
>
>
>[/color]
Actually, since I am using the compiled-in db2 on localhost (the
default), it would appear that the only value I need in DSN is the
database name, 'animalst'. And considering that it does succeed more
often than not, that is unlikely to be the cause of my problem. However,
when we grow to have separate web and db servers, I will have to revisit
this question and may need further help, since the exact value of the
DSN string seems to vary with the target db/system.

Thanks for such a quick reply.

CJ Llewellyn wrote:
[color=blue]
> "Robert Stearns" <rstearns1241@charter.net> wrote in message
> news:10ec559bvqbmcc8@corp.supernews.com...
>[color=green]
>>Note: deliberately xposted to the two newsgroups which seem applicable,
>>after reading several hundred messages from various php groups.
>>
>>Randomly my application gets the message:
>>
>>Warning: odbc_connect(): SQL error: O*@|VA@Â§Ã¿Â¿Ã›,
>>SQL state Ã½Ã¿Ã¿Ã¿|Ã½Ã¿Ã¿Ã¿Ã¼Ã˜\|Ã½Ã¿Ã¿Ã¿Ë†ÂªÃ¿Â¿ËœEW @Ã”ÂªÃ¿Â¿ in
>>SQLConnect in /var/www/html/GEM/db.php on line 15
>>
>>function conn() {
>> global $schema, $database;
>> $schema = "is3";
>> $database = "animalst";
>> $dbuser = "xxxxxxxx";
>> $dbpwd = "xxxxxxxx";
>> $dbConn = odbc_connect($database,$dbuser,$dbpwd);[/color]
>
>
> $dbConn = @odbc_connect($database,$dbuser,$dbpwd);
>
> The @ at the start of the function will supress any error messages/warning
> generated by the function.
>
>[color=green]
>> if ( $dbConn===False) {[/color]
>
>
> printf("[%s][%s][%s] \n" , $database,$dbuser,$dbpwd);
>[/color]
I will try these to see if they are corrupted by earlier code. I hope
not, finding where I corrupt constants will be a real challenge in an
interpreted language :-)
[color=blue]
> Check the parameters you are passing to connect function, as they are more
> likely to show you what is going wrong. It's unlikely in this case as you've
> set them just before calling the connect function.
>
> Check your odbc/php setup, and increase the timeout settings for which ever
> database you are using.
>[/color]
Which parameters? We are rank amateurs in setting the things up, and
have mostly taken the defaults, since our machine is reasonably fast,
their reasonably few users now, and the database is reasonably small
now. We thought to have to work on tuning parameters as we grew and had
more time.
[color=blue]
> Consider using adodb http://adodb.sourceforge.net/ as your database
> abstraction layer instead.
>[/color]
Is it less prone to these types of errors. Is it as powerful as raw
SQL/odbc? How much application code am I looking at having to replace
over the SQL/odbc abstraction layer?[color=blue]
>
>[/color]

"Robert Stearns" <rstearns1241@charter.net> wrote in message
news:10ede5tep2ufb1e@corp.supernews.com...
-snip-[color=blue]
> Actually, since I am using the compiled-in db2 on localhost (the
> default), it would appear that the only value I need in DSN is the
> database name, 'animalst'. And considering that it does succeed more
> often than not, that is unlikely to be the cause of my problem. However,
> when we grow to have separate web and db servers, I will have to revisit
> this question and may need further help, since the exact value of the
> DSN string seems to vary with the target db/system.
>[/color]

Try "DSN=animalst" as the DSN setting.

Robert Stearns wrote:[color=blue]
> Note: deliberately xposted to the two newsgroups which seem applicable,
> after reading several hundred messages from various php groups.
>
> Randomly my application gets the message:
>
> Warning: odbc_connect(): SQL error: O*@|VA@Â§Ã¿Â¿Ã›,
> SQL state Ã½Ã¿Ã¿Ã¿|Ã½Ã¿Ã¿Ã¿Ã¼Ã˜\|Ã½Ã¿Ã¿Ã¿Ë†ÂªÃ¿Â¿ËœEW @Ã”ÂªÃ¿Â¿ in
> SQLConnect in /var/www/html/GEM/db.php on line 15[/color]

99.999% of the time this is caused by not sourcing db2profile and
therefore your DB2 environment is not set. Most importantly, the
environment variable DB2INSTANCE must be set. Typically, this is done
by sourcing db2profile in your Apache init script (/etc/init.d/httpd or
apachectl, depending on how Apache is installed on your system):

Somewhere near the top of your init script or apachectl file, put the
following:

# Source DB2 environment
.. /home/db2inst1/sqllib/db2profile

Replace the path with that of your instance installation location and
then stop and start Apache (don't just restart, you need to kill the
currently running processes for these changes to take effect).

HTH,

Derek

"Robert Stearns" <rstearns1241@charter.net> wrote in message
news:10edfa2ckqkr002@corp.supernews.com...[color=blue]
> Thanks for such a quick reply.[/color]
-snip-[color=blue][color=green]
> > Check your odbc/php setup, and increase the timeout settings for which[/color][/color]
ever[color=blue][color=green]
> > database you are using.
> >[/color]
> Which parameters? We are rank amateurs in setting the things up, and
> have mostly taken the defaults, since our machine is reasonably fast,
> their reasonably few users now, and the database is reasonably small
> now. We thought to have to work on tuning parameters as we grew and had
> more time.[/color]

Look at any setting the deal with connection timeouts. There will be a
section for odbc connections in php.ini, and possibly one in the ODBC
connection driver.
[color=blue][color=green]
> > Consider using adodb http://adodb.sourceforge.net/ as your database
> > abstraction layer instead.
> >[/color]
> Is it less prone to these types of errors. Is it as powerful as raw
> SQL/odbc? How much application code am I looking at having to replace
> over the SQL/odbc abstraction layer?[/color]

It's supposed to be a M$ ADO replacement API. Which will give you a OOP
method of accessing the data. It should be more efficient and portable than
the ODBC interface as it will use native database drivers and connections
where possible, and fall back to ODBC where not.

You'd have to replace all your current odbc calls, but the SQL statements
should remain compatible.

Derek Battams wrote:[color=blue]
> Robert Stearns wrote:
>[color=green]
>> Note: deliberately xposted to the two newsgroups which seem
>> applicable, after reading several hundred messages from various php
>> groups.
>>
>> Randomly my application gets the message:
>>
>> Warning: odbc_connect(): SQL error: O*@|VA@Â§Ã¿Â¿Ã›,
>> SQL state Ã½Ã¿Ã¿Ã¿|Ã½Ã¿Ã¿Ã¿Ã¼Ã˜\|Ã½Ã¿Ã¿Ã¿Ë†ÂªÃ¿Â¿ËœEW @Ã”ÂªÃ¿Â¿
>> in SQLConnect in /var/www/html/GEM/db.php on line 15[/color]
>
>
> 99.999% of the time this is caused by not sourcing db2profile and
> therefore your DB2 environment is not set. Most importantly, the
> environment variable DB2INSTANCE must be set. Typically, this is done
> by sourcing db2profile in your Apache init script (/etc/init.d/httpd or
> apachectl, depending on how Apache is installed on your system):
>
> Somewhere near the top of your init script or apachectl file, put the
> following:
>
> # Source DB2 environment
> . /home/db2inst1/sqllib/db2profile
>
> Replace the path with that of your instance installation location and
> then stop and start Apache (don't just restart, you need to kill the
> currently running processes for these changes to take effect).
>
> HTH,
>
> Derek[/color]

I added the following lines to the apachectl file:

# pick up any necessary db2 environment variables
if test -f /db2home/db2inst1/sqllib/db2profile; then
. /db2home/db2inst1/sqllib/db2profile
fi

I then stopped httpd, made sure that all tasks ended, and then
started it back up.

That did not cure the problem, so I rebooted the server completely, to
no beneficial effect. I also printed as much hex of the message as I
could, in the (crushed) hope that it would mean something me. It is
appended below.

Error number ýÿÿÿ¼=X'fdffffffbc12';
Error message O*@|VA°¸ÿ¿Û=X'064f2a407c1a5641b0b8ffbfdb03';

Any further pointers would be greatly appreciated.

CJ Llewellyn wrote:
[color=blue]
> "Robert Stearns" <rstearns1241@charter.net> wrote in message
> news:10edfa2ckqkr002@corp.supernews.com...
>[color=green]
>>Thanks for such a quick reply.[/color]
>
> -snip-
>[color=green][color=darkred]
>>>Check your odbc/php setup, and increase the timeout settings for which[/color][/color]
>
> ever
>[color=green][color=darkred]
>>>database you are using.
>>>[/color]
>>
>>Which parameters? We are rank amateurs in setting the things up, and
>>have mostly taken the defaults, since our machine is reasonably fast,
>>their reasonably few users now, and the database is reasonably small
>>now. We thought to have to work on tuning parameters as we grew and had
>>more time.[/color]
>
>
> Look at any setting the deal with connection timeouts. There will be a
> section for odbc connections in php.ini, and possibly one in the ODBC
> connection driver.
>
>
>
>
>[/color]
After examining the output of phptest closely, I only found one timeout
involving databases:mysql.connect_timeout. Since it does not appear to
affect db2/odbc, I am loathe to change it. I saw no timeouts associated
with db2/odbc in php. Could there be something in apache?

Any further suggestions would be appreciated.

"Robert Stearns" <rstearns1241@charter.net> wrote in message
news:10ejbqjiribal78@corp.supernews.com...
-snip-[color=blue]
> After examining the output of phptest closely, I only found one timeout
> involving databases:mysql.connect_timeout. Since it does not appear to
> affect db2/odbc, I am loathe to change it. I saw no timeouts associated
> with db2/odbc in php. Could there be something in apache?
>
> Any further suggestions would be appreciated.[/color]

Write a loop to attempt to connect more than once before giving up

Robert Stearns wrote:[color=blue]
> Derek Battams wrote:
>[color=green]
>> Robert Stearns wrote:
>>[color=darkred]
>>> Note: deliberately xposted to the two newsgroups which seem
>>> applicable, after reading several hundred messages from various php
>>> groups.
>>>
>>> Randomly my application gets the message:
>>>
>>> Warning: odbc_connect(): SQL error: O*@|VA@Â§Ã¿Â¿Ã›,
>>> SQL state Ã½Ã¿Ã¿Ã¿|Ã½Ã¿Ã¿Ã¿Ã¼Ã˜\|Ã½Ã¿Ã¿Ã¿Ë†ÂªÃ¿Â¿ËœEW @Ã”ÂªÃ¿Â¿
>>> in SQLConnect in /var/www/html/GEM/db.php on line 15[/color]
>>
>>
>>
>> 99.999% of the time this is caused by not sourcing db2profile and
>> therefore your DB2 environment is not set. Most importantly, the
>> environment variable DB2INSTANCE must be set. Typically, this is done
>> by sourcing db2profile in your Apache init script (/etc/init.d/httpd
>> or apachectl, depending on how Apache is installed on your system):
>>
>> Somewhere near the top of your init script or apachectl file, put the
>> following:
>>
>> # Source DB2 environment
>> . /home/db2inst1/sqllib/db2profile
>>
>> Replace the path with that of your instance installation location and
>> then stop and start Apache (don't just restart, you need to kill the
>> currently running processes for these changes to take effect).
>>
>> HTH,
>>
>> Derek[/color]
>
>
> I added the following lines to the apachectl file:
>
> # pick up any necessary db2 environment variables
> if test -f /db2home/db2inst1/sqllib/db2profile; then
> . /db2home/db2inst1/sqllib/db2profile
> fi
>
> I then stopped httpd, made sure that all tasks ended, and then
> started it back up.
>
> That did not cure the problem, so I rebooted the server completely, to
> no beneficial effect. I also printed as much hex of the message as I
> could, in the (crushed) hope that it would mean something me. It is
> appended below.
>
> Error number ýÿÿÿ¼=X'fdffffffbc12';
> Error message O*@|VA°¸ÿ¿Û=X'064f2a407c1a5641b0b8ffbfdb03';
>
> Any further pointers would be greatly appreciated.[/color]

Sorry, the only time I've ever seen this type of behaviour is when the
DB2 environment is not set. Are you able to connect to the DB2 instance
from the CLP with no issues?

The only other thing I can think of is that perhaps you recently
upgraded your DB2 installation and did not recompile the PHP module to
use the upgraded DB2 client library? Other than that, I'd suggest
contacting DB2 support.

HTH,

Derek