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.
DB2 Enterprise Stage - Insert Into UNION ALL View
Moderators: chulett, rschirm, roy
Absolutely, the error message says it all - <VIEW> does not exist or it is a view.Eric wrote: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?
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
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).
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).