CODEWITHSUNDEEP
unixdb2database-schemaaixcatalog
Ahmad
Ahmad

Reputation: 2190

specify default schema for a database in db2 client

Do we have any way to specify default schema in cataloged DBs in db2 client in AIX.

The problem is , when it's connecting to DB, it's taking user ID as default schema and that's where it's failing.

We have too many scripts that are doing transactions to DB without specifying schema in their db2 sql statements. So it's not feasible to change scripts at all.

Also we can't create users to match schema.

Upvotes: 4

Views: 11707

Answers (3)

AngocA
AngocA

Reputation: 7693

You can create alias in the new user schema that points to the tables with the other schema. Refer these links :

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000910.html

http://bytes.com/topic/db2/answers/181247-do-you-have-always-specify-schema-when-using-db2-clp

Upvotes: 1

AngocA
AngocA

Reputation: 7693

NOTE: Not sure if this work (I am without a DB2 database at the moment, but it seems that work) and depending on your DB2 version.

You can create a stored procedure that just changes the current schema and then set the SP as connect proc. You can test some conditions before make that schema change, for example if the stored procedure is executed from the AIX server directly with a given user.

You configure the database to use this SP each time a connection is established by modifying connect_proc

Upvotes: 2

ymog
ymog

Reputation: 161

You can try to type SET SCHEMA=<your schema> ; before executing your queries.

Upvotes: 4

Related Questions