specify default schema for a database in db2 client

Question

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.

Answer 1

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

Answer 2

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

• http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.config.doc/doc/r0057371.html
• http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/c0057372.html