HELP! reading syscat from php in db2 udb v8.1.9 Linux

Bob Stearns

I am trying to write a small app in php to make the 'COMMENT' statement
a bit more friendly, so we might do more documentation. My problem is a
set of error statements which have nothing to do with what I'm doing.
For instance when I try to run the trigger list with the code below, I
get the error message:

42832--[IBM][CLI Driver][DB2/LINUX] SQL0607N "UPDATE" is not defined for
system objects. SQLSTATE=42832

$sql = "SELECT trigname AS obj_name
FROM syscat.triggers
WHERE trigschema='$db_schema'
";
dosql($sql, "S OBJ", -1, "", $res, $n);

I get different, but similar, error messages when I try tables and views:

42808--[IBM][CLI Driver][DB2/LINUX] SQL0151N The column
"MAXFREESPACESEARCH" cannot be updated. SQLSTATE=42808

42808--[IBM][CLI Driver][DB2/LINUX] SQL0151N The column "SEQNO" cannot
be updated. SQLSTATE=42808

When I run the statements in my frontend, ADS, they all do just what I
expect. Since I don't know where the problem lies, I posted this
question to two newsgroups.

Mar 18 '06 #1

Serge Rielau

Bob Stearns wrote:

I am trying to write a small app in php to make the 'COMMENT' statement
a bit more friendly, so we might do more documentation. My problem is a
set of error statements which have nothing to do with what I'm doing.
For instance when I try to run the trigger list with the code below, I
get the error message:

42832--[IBM][CLI Driver][DB2/LINUX] SQL0607N "UPDATE" is not defined for
system objects. SQLSTATE=42832

$sql = "SELECT trigname AS obj_name
FROM syscat.triggers
WHERE trigschema='$db_schema'
";
dosql($sql, "S OBJ", -1, "", $res, $n);

I get different, but similar, error messages when I try tables and views:

42808--[IBM][CLI Driver][DB2/LINUX] SQL0151N The column
"MAXFREESPACESEARCH" cannot be updated. SQLSTATE=42808

42808--[IBM][CLI Driver][DB2/LINUX] SQL0151N The column "SEQNO" cannot
be updated. SQLSTATE=42808

When I run the statements in my frontend, ADS, they all do just what I
expect. Since I don't know where the problem lies, I posted this
question to two newsgroups.

Appears your client attached a FOR UPDATE clause.
Try this:
SELECT trigname AS obj_name
FROM syscat.triggers
WHERE trigschema='$db_schema'
FOR READ ONLY
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 19 '06 #2

Bob Stearns

Serge Rielau wrote:

Bob Stearns wrote:

I am trying to write a small app in php to make the 'COMMENT'
statement a bit more friendly, so we might do more documentation. My
problem is a set of error statements which have nothing to do with
what I'm doing. For instance when I try to run the trigger list with
the code below, I get the error message:

42832--[IBM][CLI Driver][DB2/LINUX] SQL0607N "UPDATE" is not defined
for system objects. SQLSTATE=42832

$sql = "SELECT trigname AS obj_name
FROM syscat.triggers
WHERE trigschema='$db_schema'
";
dosql($sql, "S OBJ", -1, "", $res, $n);

I get different, but similar, error messages when I try tables and views:

42808--[IBM][CLI Driver][DB2/LINUX] SQL0151N The column
"MAXFREESPACESEARCH" cannot be updated. SQLSTATE=42808

42808--[IBM][CLI Driver][DB2/LINUX] SQL0151N The column "SEQNO" cannot
be updated. SQLSTATE=42808

When I run the statements in my frontend, ADS, they all do just what I
expect. Since I don't know where the problem lies, I posted this
question to two newsgroups.

Appears your client attached a FOR UPDATE clause.
Try this:
SELECT trigname AS obj_name
FROM syscat.triggers
WHERE trigschema='$db_schema'
FOR READ ONLY

Thank you, again, for helping a learner. Does the 'FOR READ ONLY' clause
speed up things in general? Should I include whenever I am only
interested in reading the results? My php version, if it is important,
is 4.4.

Mar 19 '06 #3

Serge Rielau

Bob Stearns wrote:

Thank you, again, for helping a learner. Does the 'FOR READ ONLY' clause
speed up things in general? Should I include whenever I am only
interested in reading the results? My php version, if it is important,
is 4.4.

Unfortunately I'm not versed in PHP (yet).
What I do know is that telling what you want is always a good idea.
Some client interfaces always add FOR UPDATE clause to cursors.
Others even go as far as making cursors SCROLLABLE in utter disregard
for the impact for performance.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 19 '06 #4

Bob Stearns

Serge Rielau wrote:

Bob Stearns wrote:

Thank you, again, for helping a learner. Does the 'FOR READ ONLY'
clause speed up things in general? Should I include whenever I am only
interested in reading the results? My php version, if it is important,
is 4.4.

Unfortunately I'm not versed in PHP (yet).
What I do know is that telling what you want is always a good idea.
Some client interfaces always add FOR UPDATE clause to cursors.
Others even go as far as making cursors SCROLLABLE in utter disregard
for the impact for performance.

Cheers
Serge

Would you expect this statement to return -1 rather than 1 for
odbc_num_rows? The documentation I read is singularly silent on the matter.

Mar 19 '06 #5

Knut Stolze

Bob Stearns wrote:

Serge Rielau wrote:

Bob Stearns wrote:

Thank you, again, for helping a learner. Does the 'FOR READ ONLY'
clause speed up things in general? Should I include whenever I am only
interested in reading the results? My php version, if it is important,
is 4.4.

You might want to consider moving to PHP 5.x and to deploy the IBM Zend
Core. The original unixODBC is not the best choice to communicate from PHP
to DB2.
Unfortunately I'm not versed in PHP (yet).
What I do know is that telling what you want is always a good idea.
Some client interfaces always add FOR UPDATE clause to cursors.
Others even go as far as making cursors SCROLLABLE in utter disregard
for the impact for performance.

Would you expect this statement to return -1 rather than 1 for
odbc_num_rows? The documentation I read is singularly silent on the
matter.

odbc_num_rows() does not necessarily give you reliable results. And most of
the comments in the online-PHP documentation are really bad
performance-wise as they first fetch all rows and then re-execute the
query. The general approach is to not rely on a specific number of rows
being returned but instead to process all there is and do some bookkeeping
along the way (if necessary).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 20 '06 #6

Dan Scott

Rather than moving to PHP 5 with Zend Core, you can just use the
ibm_db2 extension (http://php.net/ibm_db2). It is an open source
extension that is dedicated to DB2, and therefore does things right.

Zend Core just bundles it up nicely for you :)

odbc_num_rows() and db2_num_rows() depend on the underlying
SQLNumRows() implementation, which DB2 implements according to the CLI
/ ODBC standards:
* INSERT / UPDATE / DELETE return the number of rows affected by that
particular statement.
* SELECT returns -1 because no rows were affected -- unless you use a
scrollable cursor, in which case a lock does affect those rows until
you dismiss the cursor and therefore you do get a non-negative number
back.

Mar 21 '06 #7