从表中查看MERGE - DB2 LUW V8 FP12 [英] MERGE from TABLE INTO VIEW - DB2 LUW V8 FP12

问题描述

大家好：


我需要对MERGE声明做一些澄清。数据库在V8上

FP12（AIX）64位。


源表是tableA。

目标是一个视图" FACT"与UNION ALL因为512 Gig

的限制。


将这个视图合并为FACT ？从TableA的工作


跑了合并的说明和解释失败：

SQL0150N目标全查询，视图，类型表，具体化查询

表，

或INSERT，DELETE，UPDATE或MERGE语句中的登台表是

目标

for哪个请求的操作是不允许的。 SQLSTATE = 42807


视图定义为：

创建视图FACT select * from FactA UNION ALL select * from FactB with

行程运动


在这种情况下是否支持合并？


谢谢。


Vijay

解决方案

UDBDBA写道：

大家好：


我需要对MERGE声明做一些澄清。数据库在V8上

FP12（AIX）64位。


源表是tableA。

目标是一个视图" FACT"与UNION ALL因为512 Gig

的限制。


将这个视图合并为FACT ？从TableA的工作


跑了合并的说明和解释失败：

SQL0150N目标全查询，视图，类型表，具体化查询

表，

或INSERT，DELETE，UPDATE或MERGE语句中的登台表是

目标

for哪个请求的操作是不允许的。 SQLSTATE = 42807


视图定义为：

创建视图FACT select * from FactA UNION ALL select * from FactB with

ROW MOVEMENT


在这种情况下是否支持合并？

我刚刚在DB2 9上重现，所以答案是：显然不是。但是我

找不到记录的限制....

当然你可以使用INSTEAD OF触发器来解决，但那会是

缓慢INSERTs和UPDATEs。

从技术上讲，你可以打开PMR，但结果将是doc-APAR而不是
a代码APAR。由于DB2 9具有范围分区和大型rowid，因此对客户或IBM来说，没有什么动力可以改善UNION ALL对

视图的支持。


干杯

Serge

-

Serge Rielau

DB2解决方案开发

IBM多伦多实验室


IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/

Serge ：


我不相信这个（唉！哎哟！）......


只是为了澄清我昏昏欲睡的脑袋...... .. UNION ALL不支持合并

VIEWS。


正确吗？


我可以转换合并声明进入Upsert逻辑？


谢谢！


Vijay

Serge Rielau写道：

是否支持合并是这样的吗？

我刚刚在DB2 9上重现，所以答案是：显然不是。但是我

找不到记录的限制....

当然你可以使用INSTEAD OF触发器来解决，但那会是

缓慢INSERT和UPDATE下降。

UDBDBA写道：

哔叽：


我不相信这个（唉！哎哟！）...


只是为了澄清我昏昏欲睡的头...... UNION ALL不支持合并/>
VIEWS。


正确吗？

号的限制涉及WITH ROW运动，而不是UNION ALL此。

只要你没有必要允许洗牌你的查看分区键你好

还可以（我只是测试了没有ROW MOVEMENT的视图而且它有效）。
带有ROW MOVEMENT的
非常复杂，MERGE也是如此。我不是很好

我们很惊讶（很可能我自己打电话了，我只是不记得; b $ b记住;-)决定它是'不值得给出范围的努力

分区即将来临。

我可以将合并语句转换为Upsert逻辑吗？

当然。就像在古代一样。


干杯

Serge

-

Serge Rielau

DB2解决方案开发

IBM多伦多实验室


IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/

Hi All:

I need some clarification on a MERGE statement. The database is on V8
FP12 (AIX) 64bit.

The source table is tableA.
The target is a View "FACT" with UNION ALL because of the 512 Gig
restriction.

Will a MERGE into this view "FACT" from tableA work?

Ran an explain on the merge and the explain fails with:
SQL0150N The target fullselect, view, typed table, materialized query
table,
or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a
target
for which the requested operation is not permitted. SQLSTATE=42807

The view definition is:
create view FACT select * from FactA UNION ALL select * from FactB with
ROW MOVEMENT

Is merge supported in this case?

Thanks.

Vijay

解决方案

UDBDBA wrote:

Hi All:

I need some clarification on a MERGE statement. The database is on V8
FP12 (AIX) 64bit.

The source table is tableA.
The target is a View "FACT" with UNION ALL because of the 512 Gig
restriction.

Will a MERGE into this view "FACT" from tableA work?

Ran an explain on the merge and the explain fails with:
SQL0150N The target fullselect, view, typed table, materialized query
table,
or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a
target
for which the requested operation is not permitted. SQLSTATE=42807

The view definition is:
create view FACT select * from FactA UNION ALL select * from FactB with
ROW MOVEMENT

Is merge supported in this case?

I just reproduced on DB2 9, so the answer is: "apparently not". But I
can''t find the limitation documented....
Of course you can work around using INSTEAD OF triggers, but that would
slow INSERTs and UPDATEs down.
Technically you could open a PMR, but the outcome will be a doc-APAR not
a code APAR. Since DB2 9 has range partitioning and large rowids there
is little incentive to customers or IBM to improve UNION ALL support for
views.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Serge:

I don''t believe this (Argh! Ouch!)...

Just to clarify my sleepy head..... Merge is NOT supported on UNION ALL
VIEWS.

Correct?

Can I convert the merge statements into Upsert logic?

Thanks!

Vijay
Serge Rielau wrote:

Is merge supported in this case?

I just reproduced on DB2 9, so the answer is: "apparently not". But I
can''t find the limitation documented....
Of course you can work around using INSTEAD OF triggers, but that would
slow INSERTs and UPDATEs down.

UDBDBA wrote:

Serge:

I don''t believe this (Argh! Ouch!)...

Just to clarify my sleepy head..... Merge is NOT supported on UNION ALL
VIEWS.

Correct?

No. The limitation relates to WITH ROW MOVEMENT, not UNION ALL as such.
As long as you don''t allow shuffling your "view partitioning keys" you
are OK (I just tested with a view with NO ROW MOVEMENT and it worked).
WITH ROW MOVEMENT is pretty complex and so is MERGE. I''m not too
surprised that we (chances are I made the call myself and I just don''t
remember ;-) decided that it''s not worth the effort given range
partitioning was around the corner.

Can I convert the merge statements into Upsert logic?

Of course. Like in the olden days.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

这篇关于从表中查看MERGE - DB2 LUW V8 FP12的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！