CNTLCARDS

CNTLCARDS DB2LOAD is the default when your JCL contains a SYSCNTL DD statement. This value tells BMC AMI Unload to generate only Db2 LOAD utility control statements in the SYSCNTL data set. 

Additional considerations

The following considerations apply to CNTLCARDS DB2LOAD:

• When you specify ENUMROWS as the first keyword in one of the CNTLCARDS, BMC AMI Unload generates an ENUMROWS value 'ENUMROWS nn' during the unload phase. This value indicates the number of rows that BMC AMI Unload will unload.

• When you specify SPANNED YES, BMC AMI Unload adds the FORMAT SPANNED YES option to the control cards that it generates. 
• When you are unloading row ID columns and DIRECT NO is in effect, BMC AMI Unload generates load control statements for those columns as if they were defined as GENERATED BY DEFAULT. For more information, see DIRECT NO.
• When you are unloading a system-period temporal object, BMC AMI Unload generates field specification control cards for temporal-specific fields.
• When you are unloading application-period temporal data, BMC AMI Unload generates field specification control cards for business-begin and business-end fields.

If you have specified certain overrides in the CNTLCARDS DB2LOAD or BMCLOAD data strings, the BMC AMI Unload utility parses the overrides and generates the real column name for any override.

The following table provides a summary of overrides with their reserved names that BMC AMI Unload supports:

Use this option to specify a string of additional LOAD syntax to insert into the IBM Db2 LOAD utility or BMC LOADPLUS for Db2 product control statements. You can specify multiple strings of up to 72 bytes each. Enclose each string in single quotation marks. To embed a quoted string (a string enclosed by single quotation marks) within a string, add a single quotation mark before and after the quoted string (for example, 'CITY=''AUSTIN''').

BMCLOAD

CNTLCARDS BMCLOAD generates a LOAD statement containing the FORMAT CSV option to enable compatibility with syntax created in LOADPLUS for Db2.

The following consideration applies to CNTLCARDS BMCLOAD:

• When you specify ENUMROWS as the first keyword in one of the CNTLCARDS, BMC AMI Unload generates an ENUMROWS value 'ENUMROWS nn' during the unload phase. This value indicates the number of rows that BMC AMI Unload might unload.

The following table provides a summary of the ROWCHANGE override with its reserved name that BMC AMI Unload supports:

Specifying CNTLCARDS DB2 tells BMC AMI Unload to generate both the CREATE TABLE DDL and LOAD CNTL cards in the SYSCNTL output file.

This operand changes only the content of the SYSCNTL output file, without changing the allocation of the SYSCNTL file.

CNTLCARDS DB2 creates the following supported UNLOAD formats:

• DSNTIAUL
• EXTERNAL
• STANDARD
• DELIMITED

Differences between BMC AMI Unload and UNLOAD PLUS

• In BMC AMI Utilities the SPANNED YES and DIRECT YES keywords support unloading LOB columns. With this specification, the CNTLCARDS DDL for LOB columns is valid.
• With DIRECT YES and without the SPANNED YES keyword, BMC AMI Utilities do not process LOB columns and omits them from the UNLOAD output. Unloading LOB columns is fully supported with the keywords DIRECT YES and SPANNED YES.
• With DIRECT NO, the UNLOAD work is forwarded to DSNUTILB for processing. The resulting length of the LOB column in the SYSCNTLCARD DDL might not conform to the IBM standard.
• For BMC AMI Unload DIRECT YES processing, the DEFAULT column specification in the Db2 catalog is used for the CNTLCARD support. In contrast, DIRECT NO processing runs under Db2 SQL and uses the DEFAULT column specification supported under SQL.

• The CNTLCARDS support in BMC AMI Utilities preserve the table name when the Unload statement uses built-in functions. In contrast, UNLOAD PLUS uses a nonstandard table name during DDL creation when referencing Db2 catalog tables. For more information, see CNTLCARDS in the UNLOAD PLUS space.

• When the SELECT clause includes a Db2 function, BMC AMI Utilities CNTLCARDS creates a temporary column name that differs from the one UNLOAD PLUS generated.
  

• In BMC AMI Utilities CNTLCARDS support, the database name, table name, and column names are enclosed by quotation marks. In contrast, the UNLOAD PLUS utility provides quotations marks only when the table qualifier, table name, or column(s) would require quotation marks for successful DDL processing. For more information, see CNTLCARDS.

• BMC AMI Utilities CNTLCARDS support generates the CREATE TABLE DDL and the LOAD DATA statement for each table together. In the SYSCNTL file output, one CREATE TABLE DDL/LOAD DATA statement pair is followed by the next pair. These CREATE TABLE DDL statements and LOAD DATA statements are the same in BMC AMI Unload and UNLOAD PLUS. However, the order in which they are written to the SYSCNTL file is different.
• For BMC AMI Unload, CNTLCARDS support includes limited IDENTITY column support. BMC AMI Unload does not generate field specification control cards for temporal-specific fields. This is a limitation of BMC AMI Unload functionality when unloading IDENTITY columns.

The following consideration applies to CNTLCARDS Db2:

• When you specify ENUMROWS as the first keyword in one of the CNTLCARDS, BMC AMI Unload generates an ENUMROWS value 'ENUMROWS nn' during the unload phase. This value indicates the number of rows that BMC AMI Unload might unload.

Specifying CNTLCARDS DB2DDL tells BMC AMI Unload to generate only the CREATE TABLE DDL in the SYSCNTL output file.

This operand changes only the content of the SYSCNTL output file, without changing the allocation of the SYSCNTL file.

CNTLCARDS DB2DDL creates the following supported UNLOAD formats:

• DSNTIAUL
• CSV
• EXTERNAL
• STANDARD
• DELIMITED

Differences between BMC AMI Unload and UNLOAD PLUS

• In BMC AMI Utilities the SPANNED YES and DIRECT YES keywords support unloading LOB columns. With this specification, the CNTLCARDS DDL for LOB columns is valid.
• With DIRECT YES and without the SPANNED YES keyword, BMC AMI Utilities do not process LOB columns and omits them from the UNLOAD output. Unloading LOB columns is fully supported with the keywords DIRECT YES and SPANNED YES.
• With DIRECT NO, the UNLOAD work is forwarded to DSNUTILB for processing. The resulting length of the LOB column in the SYSCNTLCARD DDL might not conform to the IBM standard.
• For BMC AMI Unload DIRECT YES processing, the DEFAULT column specification in the Db2 catalog is used for the CNTLCARD support. In contrast, DIRECT NO processing runs under Db2 SQL and uses the DEFAULT column specification supported under SQL.

• The CNTLCARDS support in BMC AMI Utilities preserve the table name when the Unload statement uses built-in functions. In contrast, UNLOAD PLUS uses a nonstandard table name during DDL creation when referencing Db2 catalog tables. For more information, see CNTLCARDS in the UNLOAD PLUS space.

• When the SELECT clause includes a Db2 function, BMC AMI Utilities CNTLCARDS creates a temporary column name that differs from the one UNLOAD PLUS generated.

• In BMC AMI Utilities CNTLCARDS support, the database name, table name, and column names are enclosed by quotation marks. In contrast, the UNLOAD PLUS utility provides quotations marks only when the table qualifier, table name, or column(s) would require quotation marks for successful DDL processing. For more information, see CNTLCARDS.

• BMC AMI Utilities CNTLCARDS support generates the CREATE TABLE DDL and the LOAD DATA statement for each table together. In the SYSCNTL file output, one CREATE TABLE DDL/LOAD DATA statement pair is followed by the next pair. These CREATE TABLE DDL statements and LOAD DATA statements are the same in BMC AMI Unload and UNLOAD PLUS. However, the order in which they are written to the SYSCNTL file is different.
• For BMC AMI Unload, CNTLCARDS support includes limited IDENTITY column support. BMC AMI Unload does not generate field specification control cards for temporal-specific fields. This is a limitation of BMC AMI Unload functionality when unloading IDENTITY columns.