Limitations of UNION in a View

Consider the following limitations to UNION in a view:

The view is read-only, which means you would have to use special program logic and possibly even dynamic SQL to perform inserts, updates, and deletes against the base tables. If you are using DB2 9, however, you could use INSTEAD OF triggers to provide this functionality.
Predicate transitive closure happens after the join distribution. So, if you are joining from a table to a UNION in a view and predicate transitive closure is possible from the table to the view, you have to code the redundant predicate.
DB2 can apply query block pruning for literal values and host variables, but not joined columns. For that reason, if you expect query block pruning on a joined column, code a programmatic join (this is the only case). Also, in some cases, the pruning does not always work for host variables, so you have to test.
When using UNION in a view, keep the number of tables in a query to a reasonable number. This recommendation is especially true for joining because DB2 distributes the join to each query block. This practice multiplies the number of tables in the query, which can increase bind time and execution time. Also, you could exceed the 225 table limit in which case DB2 will materialize the view.
In general, we recommend that you limit the number of tables UNIONed in the view to 15 or under.