Do you have to always specify schema when using db2 CLP?

[James Foreman]

We have 5 users of our database (DB2 UDB 8.1 on SuSE linux) :

db2inst1
bill
fred
sarah
jessica

The tables are created under db2inst1, which has the ability to
drop/create/alter/etc. bill, fred, sarah and jessica all have select
privileges on some of these tables.

If I go into the CLP and connect as db2inst1, I can write
SELECT * FROM authors
and get results back. If I connect as bill, then
SELECT * FROM authors
gives SQL0204N "BILL.AUTHORS" is an undefined name.
whereas
SELECT * from db2inst1.authors
executes correctly. To save my users having to refer to the schema
every time they mention a table name, is there a way to set the schema
in the CLP session to default to db2inst1 rather than
bill/fred/sarah/jessica?

[Anton Versteeg]

First issue: set current schema = 'DB2INST1'

James Foreman wrote:

We have 5 users of our database (DB2 UDB 8.1 on SuSE linux) :

db2inst1
bill
fred
sarah
jessica

The tables are created under db2inst1, which has the ability to
drop/create/alter/etc. bill, fred, sarah and jessica all have select
privileges on some of these tables.

If I go into the CLP and connect as db2inst1, I can write
SELECT * FROM authors
and get results back. If I connect as bill, then
SELECT * FROM authors
gives SQL0204N "BILL.AUTHORS" is an undefined name.
whereas
SELECT * from db2inst1.authors
executes correctly. To save my users having to refer to the schema
every time they mention a table name, is there a way to set the schema
in the CLP session to default to db2inst1 rather than
bill/fred/sarah/jessica?

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

[Almund Sebi]

Anton Versteeg <an************@nnll.iibbmm.com> wrote in message news:<40**************@nnll.iibbmm.com>...

First issue: set current schema = 'DB2INST1'

I suggest that, too. However, at a later point, if you decide to allow
your users creation of their own tables, they'll face the same
problem. So if your set of db2inst-tables remains mostly static, I
suggest you add views of your tables into each user's respective
schema (and leave the 'current schema' setting untouched).

hth

[James Foreman]

Thanks. We've granted SELECT privileges to bill on all tables in the
db2inst1 schema. But if I connect as bill, set schema to db2inst1 and
then try LIST TABLES I get no results returned. Do I need to
configure privileges elsewhere (eg at schema level?)

Anton Versteeg <an************@nnll.iibbmm.com> wrote in message news:<40**************@nnll.iibbmm.com>...

First issue: set current schema = 'DB2INST1'

[Anton Versteeg]

It would be easier to create aliases then views.
So let all users do for all tables and or views:

CREATE ALIAS x FOR DB2INST1.x
etc.

Almund Sebi wrote:

Anton Versteeg <an************@nnll.iibbmm.com> wrote in message news:<40**************@nnll.iibbmm.com>...

First issue: set current schema = 'DB2INST1'

I suggest that, too. However, at a later point, if you decide to allow
your users creation of their own tables, they'll face the same
problem. So if your set of db2inst-tables remains mostly static, I
suggest you add views of your tables into each user's respective
schema (and leave the 'current schema' setting untouched).

hth

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

[Anton Versteeg]

Try "list tables for schema db2inst1"

James Foreman wrote:

Thanks. We've granted SELECT privileges to bill on all tables in the
db2inst1 schema. But if I connect as bill, set schema to db2inst1 and
then try LIST TABLES I get no results returned. Do I need to
configure privileges elsewhere (eg at schema level?)

Anton Versteeg <an************@nnll.iibbmm.com> wrote in message news:<40**************@nnll.iibbmm.com>...

First issue: set current schema = 'DB2INST1'

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

[James Foreman]

Thanks. All working nicely

[Pierre Saint-Jacques]

As an alternative to this, any user has the privilege to define an alias for
any of the table one has access to.
db2 connect to <dbname> user bill using somepw
db2 select * from db2inst1.authors ==> This works
db2 create alias bill.myauthors for db2inst1.authors
db2 select * from myauthors ==> This will also work

The alias is: bill.myauthors and is valis as long as no other object in the
db is called bill.myauthors. (Note that the alias could also be
bill.authors as no other object is called bill.authors).
Your users then do not have to remember to set the current schema.
HTH, Pierre.
"Anton Versteeg" <an************@nnll.iibbmm.com> a écrit dans le message de
news:40**************@nnll.iibbmm.com...

First issue: set current schema = 'DB2INST1'

James Foreman wrote:

We have 5 users of our database (DB2 UDB 8.1 on SuSE linux) :

db2inst1
bill
fred
sarah
jessica

The tables are created under db2inst1, which has the ability to
drop/create/alter/etc. bill, fred, sarah and jessica all have select
privileges on some of these tables.

If I go into the CLP and connect as db2inst1, I can write
SELECT * FROM authors
and get results back. If I connect as bill, then
SELECT * FROM authors
gives SQL0204N "BILL.AUTHORS" is an undefined name.
whereas
SELECT * from db2inst1.authors
executes correctly. To save my users having to refer to the schema
every time they mention a table name, is there a way to set the schema
in the CLP session to default to db2inst1 rather than
bill/fred/sarah/jessica?

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands