DB2 Enterprise Stage - Insert Into UNION ALL View

[newsaur]

We have implemented pseudo range-partitioning in DB2 with UNION ALL views across multiple tables each capturing transactions of different months.

To make the ETL job less dependent on the underlying range-partitioning scheme, we wanted to insert records directly into the UNION ALL view using a DB2 Enterprise Stage.

When we do that we got the follow error:
<VIEW> does not exist or it is a view; you have to specify either a valid
table name or a valid alias; views are not supported for partitioning.

Is there any way to overcome / get around this such that DB2 Enterprise Stage will not try to working with the underlying DB2 table partitioning?

DB2 API stage will work, but I have heard that it does not perform very well. Is that true or is it comparable? (setting aside the fact that it doesn't leverage DB2 table partitioning).

Any help would be greatly appreciated. Thanks.

[Eric]

As I remember. the rules of SQL say You can't INSERT into a VIEW that is made from multiple tables. Is this still true?

[sud]

As I remember. the rules of SQL say You can't INSERT into a VIEW that is made from multiple tables. Is this still true?

Absolutely, the error message says it all - <VIEW> does not exist or it is a view.

[newsaur]

To Eric: Yep, DB2 allows inserting to a view under some conditions. Better yet the SQL optimizer will determine which underlying table is the target by looking at the predefined table column constraint, and therefore is able to prune the DML. This is a typical technique in DB2 to implement something smiilar to range-partitioning in Oracle.

To sud: Understood. Is there a way to get around this? E.g. some option that we can set to ask the DB2 Enterprise Stage not to doing any partitioning. (already tried to set the stage as "sequential" but it wouldn't work).

[Eric]

To Eric: Yep, DB2 allows inserting to a view under some conditions.

I beleive the condition is that the View has been created based on a Single Database Table.