IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

In a distributed environment how an alias identifies a table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
JashBr
Currently Banned

New User


Joined: 15 Feb 2007
Posts: 2
Location: Chennai

PostPosted: Thu Feb 15, 2007 8:32 am
Reply with quote

What does it mean that alias doesnt use location qualifier of a table ?
An alias is created even if the table doesnt exist. But how the table structure gets updated ?
In a distributed environment how an alias identifies a table ?

Thanks,
Jash
Back to top
View user's profile Send private message
JashBr
Currently Banned

New User


Joined: 15 Feb 2007
Posts: 2
Location: Chennai

PostPosted: Fri Mar 30, 2007 3:41 pm
Reply with quote

Hi all,
I'm very much disappointed that none of the viewers replied my post. Atleast send some links from which i could get the answers for above questions.

Thanks,
Jash
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Fri Mar 30, 2007 3:43 pm
Reply with quote

JashBr wrote:
Hi all,
I'm very much disappointed that none of the viewers replied my post. Atleast send some links from which i could get the answers for above questions.

Thanks,
Jash

What are we, your personal advice center ???
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 30, 2007 8:00 pm
Reply with quote

Quote:
An alias is created even if the table doesnt exist.



Please go thru the syntax atleast..!
How could anyone define an ALIAS on the object which is not existing.
DB2 does not allow you to do this..!

Alias can be defined on a TABLE/VIEW. If you are talking about the remote site. It should be accessed via a three part name.

Remote alias should be accessed as LOACTION.fully qualified name

Synonyms cannot be accessed remotely because it cannot be qualified.
If you create the synonym, Its only you and only you who can access it.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Mar 30, 2007 11:33 pm
Reply with quote

Hello Jash,

One reason for the lack of response is that your questions were not clear. Another was your statement "An alias is created even if the table doesnt exist." which caused even more confusion.

To get any responses (and especially meaningful ones), care must be given when presenting your question/statment. As i've mentioned several times, while a topic starter's thought/question is completely clear to them, it may not be to those who read it and would want to help.

Good luck icon_smile.gif
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Sat Mar 31, 2007 7:31 am
Reply with quote

vini_srcna wrote:


Please go thru the syntax atleast..!
How could anyone define an ALIAS on the object which is not existing.
DB2 does not allow you to do this..!



Hmm, as far as I remember, Aliases can be created on tables that don't exist yet.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Sat Mar 31, 2007 4:13 pm
Reply with quote

wanderer, You made me think.

I know the fact, that an alias would still be there when the table/view on which it is defined is dropped. I was not aware of this concept. Thank you.

I just tried to make sure. Here is the warning message..!
[img]CREATE ALIAS MYALIEAS FOR PILLAR;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT404I SQLCODE = 403, WARNING: THE LOCAL OBJECT REFERENCED BY THE CREATE
ALIAS STATEMENT DOES NOT EXIST
DSNT418I SQLSTATE = 01522 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICAL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 25 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000019' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------[/img]

Sorry for wrong information and thank you for correcting me. I always love to correct my mistakes icon_smile.gif
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Sat Mar 31, 2007 11:55 pm
Reply with quote

I am glad I helped. Cheers icon_smile.gif
Back to top
View user's profile Send private message
Jash_br

New User


Joined: 30 Mar 2007
Posts: 5
Location: Chennai

PostPosted: Tue Apr 03, 2007 10:39 am
Reply with quote

Wanderer,
So alias can be created even if the table doesn't exist. But how does the alias is updated as soon as we create the table.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Apr 03, 2007 2:16 pm
Reply with quote

Jash,

I don't think there is any updation happens and there is no need for such a thing as far as I understand. Why do you think alias needs to be updated when table is created?
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Tue Apr 03, 2007 2:20 pm
Reply with quote

Quote:
But how does the alias is updated as soon as we create the table...?.


When you access the alias it directs you to the base table on which it is defined. If the base table exists it's well and fine or else it will through you some negative code.
Back to top
View user's profile Send private message
Jash_br

New User


Joined: 30 Mar 2007
Posts: 5
Location: Chennai

PostPosted: Tue Apr 03, 2007 5:29 pm
Reply with quote

Hi,
I created an alias for a table which does not exist. I checked that the structure of the alias is empty. Then I created the table. Now again checked the structure of the alias. It got updated with the mentioned table's structure. So how does it happened.

Thanks for your replies icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Apr 03, 2007 7:20 pm
Reply with quote

Hello,

Let's use an anology.

Let's say there is a new housing project under construction (one of the streets is Maple St). Each lot also has a "tract id". All of the new addresses are known before construction begins. There is a reference to 1234 Maple St, but no house or people are there yet. This is like your alias for the table not yet created.

Now the house is built, but not occupied. This is like having a table definition only. Lastly, someone moves into the new house. This is like having data in the new table. You can see who they are, their ages, etc.

Without knowing all of the details and without knowing the tract id, the 123 Maple St address alias is used beacuse it is easier to work with (for many). Much like we would use an alias.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Apr 04, 2007 12:55 am
Reply with quote

Jash_br wrote:
Hi,
I created an alias for a table which does not exist. I checked that the structure of the alias is empty. Then I created the table. Now again checked the structure of the alias. It got updated with the mentioned table's structure. So how does it happened.



Can you tell how you check Alias structure?

And by structure do you mean the DDL of the underlying table?
Back to top
View user's profile Send private message
Jash_br

New User


Joined: 30 Mar 2007
Posts: 5
Location: Chennai

PostPosted: Wed Apr 04, 2007 12:45 pm
Reply with quote

Yes Wanderer I just meant DESC of the alias icon_smile.gif
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Apr 04, 2007 1:03 pm
Reply with quote

Is this DESC somekind of command you use in some third party tool?

Anyway, it doesn't matter. The whole Alias thing doesn't get updated anywhere if you create the associated table afterwards. Everytime you mention the Alias name, DB2 replaces it with the underlying table name if present based on your Alias definition, before processing your statements further. Hope that explains.
Back to top
View user's profile Send private message
Jash_br

New User


Joined: 30 Mar 2007
Posts: 5
Location: Chennai

PostPosted: Thu Apr 05, 2007 12:13 pm
Reply with quote

Got it icon_smile.gif Hope thats why Alias is called "Forward pointer" !

Thanks a lot !!!
Jash
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top