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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Updating a VSAM file with DISP=OLD sheersh JCL & VSAM 8 Tue Mar 14, 2017 6:14 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


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