Pre-set default schema for new user

[greatcanyon]

Hi,

When I create a new user in DB2, can I set the default schema to an
existing schema? The reason I want to do this is that I want to later
connect to db2 as this user and access tables WITHOUT the schema name
prefix. In addition to that, I want to use ADO.NET to connect as this
user and query tables in another schema without the prefix. (I use IBM
Ole DB Data provider in the connection string). The access is
currently set up properly. In other words, I can do everything I want
as long as I supply the prefix. I hope a "pre-set" can solve all my
problems. I know that you can do a "set current schema = xxx" after
you connect. But that is only good for that connection. Also, it is
difficult for me to make a change in the db access code which is a
third party software.
Thanks,

[Eugene F]

AFAIK, at least in DB2 for LUW, there is no way to preset the default
schema at connection time (there is no such an option in CONNECT
statement). The default schema for user A is always A right after you
get connection to your database. You can use aliases in order to avoid
qualifying non-default schema objects with a schema name.

-Eugene.

[Knut Stolze]

gr*********@yahoo.com wrote:

Hi,

When I create a new user in DB2, can I set the default schema to an
existing schema?
First, you don't create users in DB2. Users are created at the operating
system level or by the means mandated by your specific security plugin.
The reason I want to do this is that I want to later
connect to db2 as this user and access tables WITHOUT the schema name
prefix.

You should remember that the schema name is an integral part of the
qualified table name. Personally, I consider it as good coding style to
always fully qualify the table name with the proper schema name. It just
helps to avoid any problems with determining the proper table.

But you can have a look at the CLI configuration. It allows you to set a
default schema that shall be used for a particular database. But that
default will only apply to CLI applications (and whichever interface builds
on that). I don't know if there are other mechanisms available for other
binding styles.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

In Oracle, there is such a thing such a a LOGON TRIGGER, that is, a
TRIGGER that fires when the user logs in. Is there an equivalent for
DB2? That would allow a SET SCHEMA statement to be run upon every
logion automatically.

B.

[greatcanyon]

How do you use aliases to avoid qualifying non-default schema object
with a schema name?
Thanks,

[Eugene F]

I am connecting as user TS, that BTW has IMPLICIT_SCHEMA authority
granted (by default) to my db sandbox OAKL0 and do the following:

db2 => connect to oakl0 user ts
Enter current password for ts:

Database Connection Information

Database server = DB2/LINUX 8.2.1
SQL authorization ID = TS
Local database alias = OAKL0
db2 => values current schema

1

------------------------------

TS
1 record(s) selected.
db2 => create table x.t (i int) ----------------------> this creates
table and the schema X if the latter didn't exist
DB20000I The SQL command completed successfully.
db2 =>
db2 => list tables for schema ts

Table/View Schema Type Creation time
------------------------------- --------------- -----
--------------------------

0 record(s) selected.

db2 => list tables for schema x

Table/View Schema Type Creation time
------------------------------- --------------- -----
--------------------------
T X T
2006-01-11-13.27.23.025428

1 record(s) selected.
db2 => create alias t for x.t
DB20000I The SQL command completed successfully.
db2 =>
db2 => list tables for schema ts

Table/View Schema Type Creation time
------------------------------- --------------- -----
--------------------------
T TS A
2006-01-11-13.28.14.313375

1 record(s) selected.
db2 => insert into t values(1)
DB20000I The SQL command completed successfully.
db2 => select * from t

I
-----------
1

1 record(s) selected.

db2 => values current schema

1

----------------------------

TS
1 record(s) selected.

db2 =>
-Eugene

[Serge Rielau]

Brian Tkatch wrote:

In Oracle, there is such a thing such a a LOGON TRIGGER, that is, a
TRIGGER that fires when the user logs in. Is there an equivalent for
DB2? That would allow a SET SCHEMA statement to be run upon every
logion automatically.

B.

No log-on triggers in DB2. cli.ini is the standard answer to setting a
default schema. In many apps building up the connection isn't under end
user conbtrol anyway. So it can be easily wrapped.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[greatcanyon]

Thanks guys for the responses. I found a solution.

First of all, let me respond to the suggestions:
1. alias - Good workaround for the developer, not good for DBA. Shot
down by DBA.
2. including schema name as good coding practice - totally agree.
However, we are using a third party software that generates data
classes from the DB2 tables. Table names are hard-coded in these data
classes. Does anyone know a good code-generation software that allow
you to easily change the schema name?
3. cli.ini - Not sure how it works. Didn't see any mentioning of
user/pwd in this file. Does that mean the "current schema" setting
will be for anyone who logged on to DB2 database on this computer?
Also, what type of connection actually uses this file?

Now, my solution is to use the ibm.data.db2 which is the IBM's version
of DB2 .Net data provider. It allows you to set the currentschema in
the connection string. It is exactly what I wanted. The only thing is
that I have to modify the third-party source code (not the generated
data classes) so that it will use the ibm.data.db2 when it sees this
connection string. I was going to make this upgrade anyways.
(previoulsy we were using IBM's OleDB provider.) I read that the MS
Ole DB provider for DB2 takes a default schema parameter as well.
However, I don't really know how to setup the packages as required by
the connection string. The IBM's .Net provider connection string is
easy to set up and use.