DB2改进建议 [英] DB2 improvement suggestion

问题描述

我有一个视图，它在不同的时间引用两个不同的表

在某些日子说t_1，在某些其他日子t_2。


在oracle我有很多方法可以做到这一点而不会产生太多的影响。


在DB2中，如果我在t_1上定义视图并且我想切换到t_2 ，我/ b
必须删除并重新创建视图并再次授予权限。我

甚至无法重命名基础表。如果它允许至少重新命名表格，我可以将t_1重命名为t_x，t_2重命名为t_1，然后重新命名为
t_x至t_1。所以，我只需要定义一次视图（查看v作为选择

*来自t_1）。


另外，我还有UNION ALL的其他视图三张或更多张桌子。


ex。

查看v as

select * from t_1

工会全部

选择*来自t_2


第二天，加载t_3后，我会有视图指向表

t_3如在


查看v作为

从* t中选择*

联合所有

select * from t_3


问题？


如果做了任何更改，为什么DB2选择让视图无法运行

to基础表，而不是使它们无效并在提交时编译

。


我们最近从Oracle切换到DB2。由于DB2 UDB没有范围分区，因此我们必须创建N个表（每个

范围有1个表）并创建一个UNION ALL视图（真是一团糟？ ）。然后当桌子上滚动时，每次丢弃并重新创建视图，授予

priileges等等。这太乏味了。在Oracle中，管理任务花费的时间少于几美元，需要几分钟和几小时。 （例如，重命名/删除

列，将表/索引移动到任何表空间等）


Stinger或任何即将推出的db2会提供范围分区吗？

他们会想出创建或替换视图/别名吗？另外，他们是否允许重命名视图引用的表格并使视图

无效而不是无法操作？ （如果允许重命名无效的

表，则无需重新创建视图。）


谢谢，

Da

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

推荐答案

正在注意。放松对象依赖性的要求众所周知

并将在未来版本中解决。

Stinger在此方面没有重大变化。

Stinger确实有一个GUI（和程序）支持来改变表（添加，

drop，alter column，...），包括处理依赖对象。


这是一个可以用来滚动窗口的技巧，它应该给你熟悉的行为

。也许它可以适应你的其他用法。


CREATE TABLE sales_t1（isbn BIGINT NOT NULL，

date INTEGER NOT NULL，

金额INTEGER NOT NULL）;

CREATE TABLE sales_t2 LIKE sales_t1;


ALTER TABLE sales_t1 ADD CONSTRAINT H1_2003

CHECK（ date BETWEEN 20030101和20030630）;


ALTER TABLE sales_t2 ADD CONSTRAINT H2_2003

CHECK（20030701和20031231之间的日期）;


创建视图销售AS

SELECT * FROM sales_t1

UNION ALL

SELECT * FROM sales_t2;

滚动窗口：

（确保自动提交是关闭的）

ALTER TABLE sales_t1

激活不用空表记录;

ALTER TABLE sales_t1

DROP CONSTRAINT H1_2003

ADD CONSTRAINT H1_2004

CHECK（约会日期？2004-01-01？

AND？2004-06-31？）;

COMMIT;


如您所见，没有架构重新评估需要。

视图文字没有变化。


干杯

Serge

-

Serge Rielau

DB2 SQL编译器开发

IBM多伦多实验室

Duly noted. Teh requirement to relax object dependencies is well known
and will be addressed in a future release.
There are no significant changes on this front in Stinger.
Stinger does have a GUI (and procedure) support to alter table (add,
drop, alter column, ...) including dealing with dependent objects.

Here is a trick that you can use for rolling windows which should give
you familiar behaviour. Maybe it can be adapted for your other usages.

CREATE TABLE sales_t1(isbn BIGINT NOT NULL,
date INTEGER NOT NULL,
amount INTEGER NOT NULL);
CREATE TABLE sales_t2 LIKE sales_t1;

ALTER TABLE sales_t1 ADD CONSTRAINT H1_2003
CHECK (date BETWEEN 20030101 AND 20030630);

ALTER TABLE sales_t2 ADD CONSTRAINT H2_2003
CHECK (date BETWEEN 20030701 AND 20031231);

CREATE VIEW sales AS
SELECT * FROM sales_t1
UNION ALL
SELECT * FROM sales_t2;
Rolling window:
(ensure autocommit is OFF)
ALTER TABLE sales_t1
ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
ALTER TABLE sales_t1
DROP CONSTRAINT H1_2003
ADD CONSTRAINT H1_2004
CHECK (date BETWEEN ?2004-01-01?
AND ?2004-06-31?);
COMMIT;

As you see there is no schema revalidation needed.
The view text does not change.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

< db*****@yahoo.com>在留言中写道

news：90 ************************** @ posting.google.c om ...

<db*****@yahoo.com> wrote in message
news:90**************************@posting.google.c om...

我有一个视图，它在不同时间引用两个不同的表
在某些日子说t_1，在某些其他日子说t_2。

在oracle我有很多很多这样做的方式没有太大的影响。

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.

[snip]


您可以编写脚本来帮助简化管理员当然，但我觉得在这个上使用的方便性可能会更好。


Eg这个ksh脚本重新创建所有无效视图。


函数recreate_views {

for v in

[snip]

You can write scripts to help ease the admin of course, but I guess the ease
of use on this one could be better.

E.g. this ksh script recreates all invalid views.

function recreate_views {
for v in

（db2 -x" ; SELECT RTRIM（VIEWSCHEMA）||''。''|| VIEWNAME FROM

SYSCAT.VIEWS WHERE VALID<>''Y''"）;

做

db2 -v"

(db2 -x "SELECT RTRIM(VIEWSCHEMA) || ''.'' || VIEWNAME FROM
SYSCAT.VIEWS WHERE VALID <> ''Y''");
do
db2 -v "

这篇关于DB2改进建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！