Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

ALTER View after updating the table structure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Tue May 06, 2008 2:20 pm    Post subject: ALTER View after updating the table structure
Reply with quote

Hi all,

I have two tables A & B and a view which is created based on A & B. Now am altering the table to add one column in Table A and other in Table B. Now how could i make my view to reflect this change (2 new columns) and how will the data get refreshed when we load the values for Tables A & B for those columns.

Regards
SSR
Back to top
View user's profile Send private message

the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Tue May 06, 2008 2:51 pm    Post subject:
Reply with quote

whats the problem in recreating the view?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 06, 2008 3:19 pm    Post subject:
Reply with quote

Hi SSR,

Keep in mind that unlike a table data is populated in a view when you refer the view ..
here as gautam said drop the view and then recreate it adding the extra column ..it will work normally ...


thanks,
ashimer
Back to top
View user's profile Send private message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Tue May 06, 2008 4:24 pm    Post subject:
Reply with quote

hi,

the_gautam wrote:
whats the problem in recreating the view?


There are no issues in dropping and recreating, want to know similar to addition of columns to the table, is there any way by which we could alter the view to reflect the change in columns?

Regards
Sundar
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 06, 2008 4:42 pm    Post subject:
Reply with quote

Hi SSR,

you do not have an ALTER privilege on a view ... you need to drop and recreate the view ..
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Tue May 06, 2008 4:43 pm    Post subject: hi
Reply with quote

SSR worte:
Quote:
and how will the data get refreshed when we load the values for Tables A & B for those columns


we do not have to bother about the refreshment of data in the view. It will do automatically. In layman terms a view is a mirror image of the actual table with the given constrains. So if we alter the actual table data the view will change accordingly.
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Tue May 06, 2008 4:57 pm    Post subject:
Reply with quote

Following are the RDBMS objects that can be modified using the ALTER command : TABLEINDEX, TABLE, TABLESPACE & STOGROUP.

So, you need to DROP and CREATE the VIEW according to your requirement.
Back to top
View user's profile Send private message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Tue May 06, 2008 5:00 pm    Post subject: Reply to: ALTER View after updating the table structure
Reply with quote

Thank you all for your prompt replies.

Regards
Sundar
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts REXX to view the PGM or PROC jackzhang75 CLIST & REXX 11 Tue Jun 20, 2017 1:20 am
No new posts How to see VIEW definition from BMC c... rohanthengal DB2 1 Thu May 04, 2017 1:50 pm
No new posts Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us