I have a view which references two different tables at different times
say t_1 on certain days and t_2 on certain other days.
In oracle I have many many ways of doing this without having much
impact.
In DB2, if I define the view on t_1 and if I want to switch to t_2, I
have to drop and recreate the view and grant the privileges again. I
can not even rename the underlying table. If it allows atleast the
renaming of the tables, I could rename t_1 to t_x, t_2 to t_1 and then
t_x to t_1. So, I have to define the view only once (view v as select
* from t_1).
Also, I have other views with UNION ALL of three or more tables.
ex.
view v as
select * from t_1
union all
select * from t_2
The next day, after loading t_3, I will have the view pointing table
t_3 as in
view v as
select * from t_2
union all
select * from t_3
Questions?
Why does DB2 chose to make the views inoperable if any change is done
to the underlying table, rather than making them invalid and compile
when referred.
We recently switched from Oracle to DB2. Since DB2 UDB does not have
the range partition, we had to create N tables (1 table for each
range) and create a UNION ALL view (What a mess?). Then when rollig
over the tables, everytime drop and recreate the views, grant
priileges etc. This is so tedious. Admin tasks which takes less than a
few seconds in Oracle takes minutes and hours in DB2. (ex, rename/drop
column, move table/index to any tablespace etc)
Would Stinger or any upcoming db2, provide Range Partitioning? Would
they come up with "create or replace view/alias"? Also, would they
allow to rename a table referenced by the view and make the view
invalid rather than inoperable? (If allowed to rename the unerlying
table, then there is no need for the recreate view. )
Thanks,
Da