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]

Re: A basic question about table addressing

"CT" <come_try@yahoo .com> wrote in message
news:f2ca5893.0 406150716.1cec9 d7d@posting.goo gle.com...[color=blue]
> 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.[/color]

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]

Re: A basic question about table addressing

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:
[color=blue]
> 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.[/color]

[Serge Rielau]

Re: A basic question about table addressing

CT wrote:
Objects in DB2 (and I think in teh SQL Standard) can consit of up to 4
parts:
<database>.<sch ema>.<table>.<c olumn>
(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]

Re: A basic question about table addressing

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

"Serge Rielau" <srielau@ca.e ye-be-em.com> wrote in message
news:can6hk$f62 $1@hanover.toro lab.ibm.com...[color=blue]
> CT wrote:
> Objects in DB2 (and I think in teh SQL Standard) can consit of up to 4
> parts:
> <database>.<sch ema>.<table>.<c olumn>
> (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[/color]

[Ian]

Re: A basic question about table addressing

N wrote:
[color=blue]
> Is there a way to turn this thing off ?
> Can we just do SELECT * FROM table
> instead of SELECT * FROM schema.table;
>[/color]

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]

Re: A basic question about table addressing

> N wrote:[color=blue]
>[color=green]
> > Is there a way to turn this thing off ?
> > Can we just do SELECT * FROM table
> > instead of SELECT * FROM schema.table;
> >[/color]
>
> Yes:
>
> set schema = your_schema;
> select * from table;
>[/color]
Also works without the schema name if the logon user id is the same as the
schema name.