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

Related topic

BMC-AMI-Utilities-SQL-language

Important

When the CREATE VIEW clause includes a common table expression, BMC AMI Unload rejects the view definition.

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

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

Important

For information about CREATE VIEW statements, see the IBM Db2 documentation.

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.

Important

  • Specifying view column names under the UNLOAD command when you have not specified them under the CREATE VIEW command results in an error.
  • If you specify a WHERE clause in the view definition when there is another WHERE clause under the UNLOAD command, BMC AMI Unload treats the WHERE clauses as if they were joined by AND. Joining operations are not supported.

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.

 

  • 13.1
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*