PHP and DB2

[Todd McNeill]

Hi-

I'm getting some rather strange behavior with PHP4 on Debian Linux
accessing a DB2 V8.1 box on AIX. I've recompiled PHP "--with-ibm-db2"
support. I'm able to connect to the database just fine, but when I
attempt to select data from a table "WITH UR", I get the following
error:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/AIX64] SQL0104N
An unexpected token "FOR UPDATE" was found following "naptime desc
with ur". Expected tokens may include: "<space>". SQLSTATE=42601 , SQL
state 42601 in SQLExecDirect in /var/www/recent.php on line 62

Here's the PHP code snippet I'm running:

--- Start Code ---
//Connect to database
$conn = odbc_connect($dsn,$user,$passwd,SQL_CUR_USE_DRIVER );

//Build SQL String
$SQLSTRING="select snaptime, count(*) from $schema.stats where group
by snaptime having count(*) > 5 order by snaptime desc with ur";

//Execute SQL
$resultset = odbc_Exec( $conn, $SQLSTRING); <-- Error occurs here.
--- End Code ---
It seems that for some reason, "FOR UPDATE" is being automagically
appended to my SQL String. I want to run with Uncommitted Read to
prevent lock waits on the stats table, which is being constantly
updated.

If anybody has any insights, I'd greatly appreciate it.

Thanks,
Todd

[Todd McNeill]

Correction- The SQL should be:

//Build SQL String
$SQLSTRING="select snaptime, count(*) from $schema.stats group by
snaptime having count(*) > 5 order by snaptime desc with ur";

Thanks,
Todd

to********@yahoo.com (Todd McNeill) wrote in message news:<9c*************************@posting.google.c om>...

Hi-

I'm getting some rather strange behavior with PHP4 on Debian Linux
accessing a DB2 V8.1 box on AIX. I've recompiled PHP "--with-ibm-db2"
support. I'm able to connect to the database just fine, but when I
attempt to select data from a table "WITH UR", I get the following
error:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/AIX64] SQL0104N
An unexpected token "FOR UPDATE" was found following "naptime desc
with ur". Expected tokens may include: "<space>". SQLSTATE=42601 , SQL
state 42601 in SQLExecDirect in /var/www/recent.php on line 62

Here's the PHP code snippet I'm running:

--- Start Code ---
//Connect to database
$conn = odbc_connect($dsn,$user,$passwd,SQL_CUR_USE_DRIVER );

//Build SQL String
$SQLSTRING="select snaptime, count(*) from $schema.stats where group
by snaptime having count(*) > 5 order by snaptime desc with ur";

//Execute SQL
$resultset = odbc_Exec( $conn, $SQLSTRING); <-- Error occurs here.
--- End Code ---
It seems that for some reason, "FOR UPDATE" is being automagically
appended to my SQL String. I want to run with Uncommitted Read to
prevent lock waits on the stats table, which is being constantly
updated.

If anybody has any insights, I'd greatly appreciate it.

Thanks,
Todd

[Dan Scott]

Todd McNeill wrote:

Correction- The SQL should be:

//Build SQL String
$SQLSTRING="select snaptime, count(*) from $schema.stats group by
snaptime having count(*) > 5 order by snaptime desc with ur";

Thanks,
Todd

to********@yahoo.com (Todd McNeill) wrote in message news:<9c*************************@posting.google.c om>...

Hi-

I'm getting some rather strange behavior with PHP4 on Debian Linux
accessing a DB2 V8.1 box on AIX. I've recompiled PHP "--with-ibm-db2"
support. I'm able to connect to the database just fine, but when I
attempt to select data from a table "WITH UR", I get the following
error:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/AIX64] SQL0104N
An unexpected token "FOR UPDATE" was found following "naptime desc
with ur". Expected tokens may include: "<space>". SQLSTATE=42601 , SQL
state 42601 in SQLExecDirect in /var/www/recent.php on line 62

Here's the PHP code snippet I'm running:

--- Start Code ---
//Connect to database
$conn = odbc_connect($dsn,$user,$passwd,SQL_CUR_USE_DRIVER );

//Build SQL String
$SQLSTRING="select snaptime, count(*) from $schema.stats where group
by snaptime having count(*) > 5 order by snaptime desc with ur";

//Execute SQL
$resultset = odbc_Exec( $conn, $SQLSTRING); <-- Error occurs here.
--- End Code ---
It seems that for some reason, "FOR UPDATE" is being automagically
appended to my SQL String. I want to run with Uncommitted Read to
prevent lock waits on the stats table, which is being constantly
updated.

If anybody has any insights, I'd greatly appreciate it.

Thanks,
Todd

My guess is that PHP is automatically set to a conservative isolation
level, which is why it tries to append "FOR UPDATE" to SELECT
statements. (I've seen this behaviour too.)

A user-contributed note to the odbc_exec function
(http://ca.php.net/manual/en/function.odbc-exec.php) suggests tacking on
"FOR READ ONLY" to the SELECT statement to prevent PHP from adding "FOR
UPDATE". So if you try:

$SQLSTRING="select snaptime, count(*) from $schema.stats where group
by snaptime having count(*) > 5 order by snaptime desc FOR READ ONLY
with ur";

-- this should work; I recreated your problem with a local database and
resolved it by adding the FOR READ ONLY clause in the appropriate spot.

Note that use of the FOR READ ONLY clause is generally recommended for
improving performance of queries that won't be used to perform updates
(http://publib.boulder.ibm.com/infoce.../r0000879.htm).
I suspect that if you specifiy FOR READ ONLY, you probably won't need to
specify WITH UR -- but that's just a suspicion.

Dan

[Dan Scott]

Todd McNeill wrote:

Correction- The SQL should be:

//Build SQL String
$SQLSTRING="select snaptime, count(*) from $schema.stats group by
snaptime having count(*) > 5 order by snaptime desc with ur";

Thanks,
Todd

to********@yahoo.com (Todd McNeill) wrote in message news:<9c*************************@posting.google.c om>...

Hi-

I'm getting some rather strange behavior with PHP4 on Debian Linux
accessing a DB2 V8.1 box on AIX. I've recompiled PHP "--with-ibm-db2"
support. I'm able to connect to the database just fine, but when I
attempt to select data from a table "WITH UR", I get the following
error:

Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/AIX64] SQL0104N
An unexpected token "FOR UPDATE" was found following "naptime desc
with ur". Expected tokens may include: "<space>". SQLSTATE=42601 , SQL
state 42601 in SQLExecDirect in /var/www/recent.php on line 62

Here's the PHP code snippet I'm running:

--- Start Code ---
//Connect to database
$conn = odbc_connect($dsn,$user,$passwd,SQL_CUR_USE_DRIVER );

//Build SQL String
$SQLSTRING="select snaptime, count(*) from $schema.stats where group
by snaptime having count(*) > 5 order by snaptime desc with ur";

//Execute SQL
$resultset = odbc_Exec( $conn, $SQLSTRING); <-- Error occurs here.
--- End Code ---
It seems that for some reason, "FOR UPDATE" is being automagically
appended to my SQL String. I want to run with Uncommitted Read to
prevent lock waits on the stats table, which is being constantly
updated.

If anybody has any insights, I'd greatly appreciate it.

Thanks,
Todd

My guess is that PHP is automatically set to a conservative isolation
level, which is why it tries to append "FOR UPDATE" to SELECT
statements. (I've seen this behaviour too.)

A user-contributed note to the odbc_exec function
(http://ca.php.net/manual/en/function.odbc-exec.php) suggests tacking on
"FOR READ ONLY" to the SELECT statement to prevent PHP from adding "FOR
UPDATE". So if you try:

$SQLSTRING="select snaptime, count(*) from $schema.stats where group
by snaptime having count(*) > 5 order by snaptime desc FOR READ ONLY
with ur";

-- this should work; I recreated your problem with a local database and
resolved it by adding the FOR READ ONLY clause in the appropriate spot.

Note that use of the FOR READ ONLY clause is generally recommended for
improving performance of queries that won't be used to perform updates
(http://publib.boulder.ibm.com/infoce.../r0000879.htm).
I suspect that if you specifiy FOR READ ONLY, you probably won't need to
specify WITH UR -- but that's just a suspicion.

Dan