By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,728 Members | 753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,728 IT Pros & Developers. It's quick & easy.

SET SCHEMA command sometimes ignored?

P: n/a
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

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 pooling, but rather the 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?


Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a

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.


Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.