Using views

BMC AMI Unload enables you to use views when you unload data. 

Restrictions

BMC AMI Unload does not support:

• View definitions that exceed 32 kB
• Nested views and views with multiple dependents
• The fullselect of a view that is too complex or does not comply with BMC AMI Utilities SQL language rules

In these cases, BMC AMI Unload: 

• Stops view processing
• Prints errors to SYSERROR, which will then send you one final error message
• If DIRECT AUTO is specified, switches processing the unload from DIRECT YES to DIRECT NO

Unloading from a view (PTF BQU0592 applied)

BMC AMI Unload supports unloading data from a view on a Db2 table when the CREATE VIEW statement contains a view column name. 

BMC AMI Unload achieves this as follows:

1. It detects any view column names in the following elements of the CREATE VIEW statement: 

   CREATE VIEW <viewName> (<viewColumnName1>,<viewColumnName2>,...) AS SELECT <fullselect>

   • The view column names list within parentheses and separated by commas
   • The fullselect that follows AS SELECT

   If you specify view column names in both the view column names list and the fullselect, the view column names in the view column list override those in the fullselect. If you do not specify view column names in the CREATE VIEW statement, the view column name inherits the base table column name. 

   Important

   BMC AMI Unload does not support use of common table expressions in the CREATE VIEW statement.

2. It maps the view column names to the base table column names or valid SQL expressions specified in the CREATE VIEW statement.
3. It uses this mapping to substitute view column names in the SELECT, WHERE, or ORDER BY clauses under the UNLOAD (or UNLD) command with their corresponding elements in the result table.

The LOAD statement generated in SYSCNTL or SYSPUNCH references the view name and the view column names, not the base table name or base table column names.