SET SCHEMA command sometimes ignored?

aj

DB2 v8 FP5
Red Hat AS 2.1

We are encountering a strange error where the default schema name
that is specified w/ a SET SCHEMA command is being inconsistently
ignored, causing all table references to be prefixed with the
connecting username, and throwing many SQL -204 SQLSTATE 42704
exceptions.

There does not appear to be a pattern to this, except that heavy
DB usage makes it more frequent.

The connection comes from a pooling mechanism written in Java,
whereby each new connection added to the pool gets a SET SCHEMA
done against it. This pooling mechanism has worked flawlessly
for over a year against DB2 v7 - the behavior only started when
I upgraded to DB2 v8 FP5. I am not using the JDBC2
com.ibm.db2.jdbc.DB2PooledConnection pooling, but rather the
com.ibm.db2.jcc.DB2Driver class.

A thread entitled "Default schema in db2cli.ini intermittantly ignored"
dated 2003-04-17 mentions the same behavior, but not really a resolution.

Anyone have any thoughts?
I have rebound the db2schema.bnd, db2ubind.lst and db2cli.lst packages.
This database was a clean migration from a v7 offline RESTORE.
db2dart and inspect check database comes up clean...

PS - whats the diff between SET SCHEMA and SET CURRENT SCHEMA?

TIA

aj

Nov 12 '05 #1

Serge Rielau

AJ,

The difference between SET SCHEMA and SET CURRENT SCHEMA is the parser
token CURRENT. That's it. There is none. Same code. Just syntax
toleration. likely one is SQL Standard, the other DB2 SQL.

Now one major piece of work in V8 was the complete rework of the clients
to use DRDA. Possibly your Java code was unwittingly exploiting some
quirk in the V7 client behaviour that the V8 client doesn't possess
anymore. Pure guesswork.
I would be surprised if this would be solved within this group.
I would start revisiting your pooling code to ensure it's correct and
then open a PMR if needed.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2