A basic question about table addressing

[CT]

Lets say you have a query

select * from db2.employees;

here what is db2 - is it schema name, database name or table creator
name?

Also, does it make a difference of notation when you are in pc world
or mainframe zos world. I am using v8 on pc and v7 on zos.

Please explain.

Thanks.

T.

[Mark A]

"CT" <co******@yahoo.com> wrote in message
news:f2**************************@posting.google.c om...

Lets say you have a query

select * from db2.employees;

here what is db2 - is it schema name, database name or table creator
name?

Also, does it make a difference of notation when you are in pc world
or mainframe zos world. I am using v8 on pc and v7 on zos.

Please explain.

Thanks.

T.

It is the schema name. If the schema name is not included in a SQL
statement (create table or the select) , the schema is determined by the
authorization id of the user, or the "set schema" statement (if that has
been issued).

It basically works the same on z/OS but the terminology may be very slightly
different.

[Blair Adamache]

It would be the schema name. If there is no schema called db2, it would
be assumed to be the table creator name.

CT wrote:

Lets say you have a query

select * from db2.employees;

here what is db2 - is it schema name, database name or table creator
name?

Also, does it make a difference of notation when you are in pc world
or mainframe zos world. I am using v8 on pc and v7 on zos.

Please explain.

Thanks.

T.

[Serge Rielau]

CT wrote:
Objects in DB2 (and I think in teh SQL Standard) can consit of up to 4
parts:
<database>.<schema>.<table>.<column>
(in the OR world you can go further to the right with attributes)
In DB2 UDB for LUW the "current schema" can be freely set to any value.
The only connection between the USER and the SCHEMA is that SCHEMA is
initialized to USER.
Now on DB2 V7 for OS/390 I think SCHEMA is still called SQLID and DB2
for LUW accepts SQLID as a synonym for SCHEMA.

Now how do OWNER and DEFINER get into play here?
The DEFINER is the one who defined the object.
The OWNER may be conceptually be different form the definer.
E.g. I could define an object for you and pass ownership to you.
I don't think DB2 (any platform) supports this transfer at present.

So if you connect to the database and create a table without an explicit
schema name the SCHEMA, DEFINER, and OWNER will all have the same value.
But you can create a table in any schema you have CREATEIN privilege or
a new schema if you have CREATESCHEMA (I think) privilege.
You will still be OWNER and DEFINER of the table.

Bottomline: SCHEMA = directory, CURRENT SCHEMA = PWD, USER = whoami

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[N]

Is there a way to turn this thing off ?
Can we just do SELECT * FROM table
instead of SELECT * FROM schema.table;

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:ca**********@hanover.torolab.ibm.com...

CT wrote:
Objects in DB2 (and I think in teh SQL Standard) can consit of up to 4
parts:
<database>.<schema>.<table>.<column>
(in the OR world you can go further to the right with attributes)
In DB2 UDB for LUW the "current schema" can be freely set to any value.
The only connection between the USER and the SCHEMA is that SCHEMA is
initialized to USER.
Now on DB2 V7 for OS/390 I think SCHEMA is still called SQLID and DB2
for LUW accepts SQLID as a synonym for SCHEMA.

Now how do OWNER and DEFINER get into play here?
The DEFINER is the one who defined the object.
The OWNER may be conceptually be different form the definer.
E.g. I could define an object for you and pass ownership to you.
I don't think DB2 (any platform) supports this transfer at present.

So if you connect to the database and create a table without an explicit
schema name the SCHEMA, DEFINER, and OWNER will all have the same value.
But you can create a table in any schema you have CREATEIN privilege or
a new schema if you have CREATESCHEMA (I think) privilege.
You will still be OWNER and DEFINER of the table.

Bottomline: SCHEMA = directory, CURRENT SCHEMA = PWD, USER = whoami

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ian]

N wrote:

Is there a way to turn this thing off ?
Can we just do SELECT * FROM table
instead of SELECT * FROM schema.table;

Yes:

set schema = your_schema;
select * from table;

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Mark A]

> N wrote:

Is there a way to turn this thing off ?
Can we just do SELECT * FROM table
instead of SELECT * FROM schema.table;

Yes:

set schema = your_schema;
select * from table;

Also works without the schema name if the logon user id is the same as the
schema name.