Default Schema for a User

Kevin Frey

unread,

Mar 21, 2003, 1:14:22 AM3/21/03

to

Is there a way to assign a "default" schema to a database user so that
when the user connects to the database they will automatically use
that "default" schema.

For example, if we have created a series of tables belonging to the
ASSETMAN schema, can we somehow advise the database server that when
user JOHN connects to this database his default schema is ASSETMAN?

Or do we have to issue a SET SCHEMA command explicitly (presumably at
the beginning of the connection)?

On a related note, in terms of DB2 is it common practice to use a
"default" schema and not explicitly qualify the individual SQL
queries, or is better practice to explicitly qualify every table
reference with the schema name?

Allen

unread,

Mar 21, 2003, 9:39:51 AM3/21/03

to

Kevin Frey wrote:
>
> Is there a way to assign a "default" schema to a database user so that
> when the user connects to the database they will automatically use
> that "default" schema.

I looked for a while and found no way to do this.

> For example, if we have created a series of tables belonging to the
> ASSETMAN schema, can we somehow advise the database server that when
> user JOHN connects to this database his default schema is ASSETMAN?

Luckily, we are using only Java here. So what I did was create a Java
DB2 database connection class that wraps the JDBC connection. I decided
that my default schema should be the same as the database name. Under
the covers, the Java DB2 connection class, after it connects, issues a
"SET SCHEMA <DB>". Works like a charm, and most of the programmers
neither know nor care what schema they are in.

Incidentally, this class also provides connection pooling, and thru a
bit
of NIS black magic, allows for anonymous connections (why hard code your
login and passwd?)

> Or do we have to issue a SET SCHEMA command explicitly (presumably at
> the beginning of the connection)?
> On a related note, in terms of DB2 is it common practice to use a
> "default" schema and not explicitly qualify the individual SQL
> queries, or is better practice to explicitly qualify every table
> reference with the schema name?

Some databasey people here swear that its better to be specific and
explicitly use the schema name. I think its too much typing tho...

Cheers

Allen

PM (pm3iinc-nospam)

unread,

Mar 21, 2003, 11:35:51 AM3/21/03

to

...

> Some databasey people here swear that its better to be specific and
> explicitly use the schema name. I think its too much typing tho...
>
> Cheers
>
> Allen

Using no schema is better because it makes the code more 'dynamic'.

PM

PM (pm3iinc-nospam)

unread,

Mar 21, 2003, 11:34:16 AM3/21/03

to

How do you access db2?
jdbc, odbc, cli, embedded c, ... ?

you may look into db2cli.ini or driver parameters/attributes (connection
url), connection attribute, ...
it depends

PM

Kevin Frey

unread,

Mar 23, 2003, 7:10:12 PM3/23/03

to

> How do you access db2?
> jdbc, odbc, cli, embedded c, ... ?

We are using SQLAPI under C++. We have the choice of accessing DB2
through either an ODBC DSN or via the native library. So far we're
just using ODBC wrapped by SQLAPI.

> you may look into db2cli.ini or driver parameters/attributes (connection
> url), connection attribute, ...

I'll check that out.

Thanks

Kevin.

Mark Yudkin

unread,

Mar 26, 2003, 2:08:56 AM3/26/03

to

For embedded SQL under C++, use the QUALIFIER option on the PREP command.

For ODBC everything is dynamic - there is no hidden communication - and this
case is no exception. You need to issue the SET command from your program,
and you have to code it yourself.

"Kevin Frey" <kevin_...@hotmail.com> wrote in message
news:e5d907ad.03032...@posting.google.com...

PM (pm3iinc-nospam)

unread,

Mar 26, 2003, 12:12:23 PM3/26/03

to

> For ODBC everything is dynamic - there is no hidden communication - and
this
> case is no exception. You need to issue the SET command from your program,
> and you have to code it yourself.

What about ...
CURRENTSCHEMA
Specify the schema used in a SET CURRENT SCHEMA statement upon a successful
connection.

CURRENTSQLID
Specify the ID used in a SET CURRENT SQLID statement sent to the DBMS upon a
successful connection.
This option is only relevant when connecting to those DB2 DBMSs where SET
CURRENT SQLID is supported (such as DB2 for OS/390 and z/OS).

... used in cli/odbc datasources setup in the Config. Assistant?

Aren't they used by odbc?

PM