Question on Alias

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rAinDeEr - 22 Jun 2006 06:29 GMT

Hi,

An alias is an alternate name for a table or a view. Now, suppose I
create an alias on a non existent table in the control centre , it
gives me a warning but it still creates the alias for an object which
is not even present.

create alias newalias for nonexistenttable;

[IBM][CLI Driver][DB2/NT] SQL0403W The newly defined alias
"TARIQ.newalias" resolved to the object "TARIQ.nonexistenttable" which
is
currently undefined. SQLSTATE=01522

My question is when an alias points to some thing which is present in
the database,
why does it allow to CREATE the alias when the object is not present?

Regards,
rAinDeEr

Reply to this Message

Mark A - 22 Jun 2006 06:42 GMT

> Hi,
>
[quoted text clipped - 16 lines]
> Regards,
> rAinDeEr

That is a design decision. One advantage is that if you drop a table and
recreate it, the alias does not need to be recreated.

Reply to this Message

rAinDeEr - 22 Jun 2006 07:14 GMT

Hi Mark,

> That is a design decision. One advantage is that if you drop a table and
> recreate it, the alias does not need to be recreated.

That doesnt answer my question. I dont have any table/view but still I
am able to create an object which is of no use to me. It should display
a database error, right?

Regards,
rAinDeEr

Reply to this Message

Mark A - 22 Jun 2006 07:38 GMT

> Hi Mark,
> That doesnt answer my question. I dont have any table/view but still I
[quoted text clipped - 3 lines]
> Regards,
> rAinDeEr

No, it does not give a database error. You can create the alias before the
table, or you can drop the table and the alias still exists.

Reply to this Message

Serge Rielau - 22 Jun 2006 12:07 GMT

>> Hi Mark,
>> That doesnt answer my question. I dont have any table/view but still I
[quoted text clipped - 6 lines]
> No, it does not give a database error. You can create the alias before the
> table, or you can drop the table and the alias still exists.

The "technical" reason why this works is that teh ALIAS contains no
information that requires the existence of the underlying objects.
It's just a forward pointer.
By contrast a view needs to be able to derive the column names and
column types, so it requires teh underlying table to exists.
Note that You can't use the alias in e.g. a view unless the underlying
object exists.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

rAinDeEr - 22 Jun 2006 16:37 GMT

> >> Hi Mark,
> >> That doesnt answer my question. I dont have any table/view but still I
[quoted text clipped - 23 lines]
> IOD Conference
> http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Hi,

Agree its a forward pointer. But if i Dont have any thg to point why
does it create a pointer....

<<Sigh>>...May be there is lot of Technical Design involved..lot of
advantages that I dont see...will come back 2morrow and think about
it...

Thanks Mark , Serge

Regards,
rAinDeEr

Reply to this Message

Ian - 22 Jun 2006 17:32 GMT

> Agree its a forward pointer. But if i Dont have any thg to point why
> does it create a pointer....
>
> <<Sigh>>...May be there is lot of Technical Design involved..lot of
> advantages that I dont see...will come back 2morrow and think about
> it...

Why is this a problem?

Realize that aliases are "special" objects. If you try and do a select
from an alias that points to nothing, you will get SQL0204N (table does
not exist), but the error message refers to the table referenced by the
alias, not the alias.

Also, if you say, "drop table my.alias", would you expect DB2 to drop
the alias or the table?

Reply to this Message