MODE

Use the MODE option to tell BMC AMI Unload whether to execute the SELECT clause under the Db2 full-function SQL.


Related topics

Option descriptions—UNLD syntax
UNLD-syntax-diagrams
UNLD-SELECT 

MODE_syntax.png

You can specify one of the following keywords for the MODE option:

You should place the MODE option after the UNLD DATA options, but before the SELECT statement.

Important

If you specify MODE(DB2SQL) or MODE(AUTO), you must specify a SELECT statement.

Using any of the following options with MODE(DB2SQL) or MODE(AUTO) results in a syntax error:

  • COPYDDN
  • COPYDSN
  • LOADINDD
  • SYSCOPY
  • PART
  • PARTS
  • CCSID
  • ASCII
  • FORMAT UNLOAD
  • FORMAT UNL
  • UNICODE
  • EBCDIC
  • BYPART
  • DIGITS CDB
  • SPANNED YES

DIRECT

Use MODE(DIRECT), the default, to tell BMC AMI Unload to operate under the BMC AMI Utilities direct method.

Unless you have complex data selections that require MODE(DB2SQL), such as joins or column functions, BMC recommends that you use MODE(DIRECT). This mode is the most efficient mode in terms of elapsed time and CPU time.

In this mode, you can code the SELECT, WHERE, FROM, or ORDER BY clauses anywhere and in any order within the scope of an BMC AMI Unload statement. 

Example
UNLD MODE(DIRECT)
   ORDER BY 1 FORMAT DSN
   WHERE C1=C2
   SELECT * PAD FROM TBL1

DB2SQL

Use MODE(DB2SQL) to tell BMC AMI Unload to extract the SQL statement from an BMC AMI Unload statement and process it using the IBM Db2 SQL processor.

The following considerations apply to MODE(DB2SQL):

  • BMC AMI Unload formats the output to BMC AMI Unload formats, including DSNTIAUL, EXTERNAL, ASCIIDEL, and DELIMITED.
  • BMC AMI Unload supports all data types, including large objects (LOBs). However, BMC AMI Unload truncates LOB and LONG VARCHAR columns to fit the maximum record length of 32,752 bytes. If there is more than one LOB column, the truncation occurs evenly across all LOB columns. You cannot specify SPANNED YES with MODE(DB2SQL).
  • If you use dynamic allocation under MODE(DB2SQL), BMC AMI Unload uses the MAXROWS value to estimate the output data set primary space allocation. If you do not specify the MAXROWS value, BMC AMI Unload uses the real-time statistics (RTS) table to determine the number of rows per partition.

  • For all applicable formats, BMC AMI Unload reformats the DATE, TIME, and TIMESTAMP fields based on the specifications of the options shown in the following table:

    Field

    Option

    DATE

    TIME

    TIMESTAMP

    Important

    BMC AMI Unload does not use the global formatting options specified in the ULDPARMS DD statement to reformat DATE, TIME, or TIMESTAMP fields.

  • You cannot use the TABLE keyword with the FROM option when you specify MODE(DB2SQL).
Example

The following is an example of an UNLD statement that uses MODE(DB2SQL):

UNLD DATA FORMAT(EXTERNAL) OUTDDN(OUTDSET) MODE(DB2SQL) SELECT CHAR(DT_EFF, USA), CHAR(DT_STAT, ISO), CHAR(DT_TERM, EUR) FROM CORP.AR_RECORDS

AUTO

Use MODE(AUTO) to tell BMC AMI Unload to automatically switch to MODE(DB2SQL) when encountering an SQL statement that MODE(DIRECT) does not support. The following conditions cause BMC AMI Unload to automatically switch to MODE(DB2SQL):

  • The SELECT or WHERE clause contains unsupported syntax
  • The SELECT clause results in an incompatible data type or nullability attribute with MODE(DB2SQL)
  • A complex view definition exists.
  • An output row size exceeding 32,756 bytes exists
  • An ORDER BY key length exceeding 4,090 bytes exists
  • A complex expression exists

If it does not encounter any of these conditions, BMC AMI Unload processes the statement using the BMC AMI Utilities direct method.

When BMC AMI Unload selects the BMC AMI Utilities direct method under MODE(AUTO), BMC AMI Unload changes the following processing options to maintain compatibility with DB2SQL mode:

  • +DIGITS(IBM) is enforced. This forces the BMC AMI Utilities SQL functions DIGITS and CHAR to produce output compatible with Db2 SQL functions DIGITS and CHAR.
  • BMC AMI Unload always includes a selected ROWID column in the data, whether defined with the GENERATED ALWAYS attribute or by default.

When you perform an unload under DB2SQL, BMC AMI Unload issues the following message: NGTN049 UNLOAD VIA DB2SQL

Warning

For both modes (DB2SQL or AUTO), do not place the SELECT clause in parentheses and ensure that no BMC AMI Unload options follow it. That is, all BMC AMI Unload keywords must precede the SELECT keyword.

MRF

Use this keyword to tell BMC AMI Unload to use a multi-row fetch for a specified number of rows. This can significantly improve performance by reducing CPU time. Use this keyword only when you have specified MODE(DB2SQL) or MODE(AUTO). You can specify an integer between 0 and 32767. BMC AMI Unload is shipped without a value for this option. BMC recommends that you specify a value of 100.

 

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