Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see UNLOAD PLUS for DB2 13.1.

CNTLCARDS

The CNTLCARDS option specifies the type of control statements that UNLOAD PLUS writes to the SYSCNTL data set.

Related topics

DB2LOAD-option

BMCLOAD-option-for-LOADPLUS-for-DB2

DB2DDL-option

Control-statements-for-SAS-processing

Basic-processing-options

CNTLCARDS.png

For more information about generating control statements and about inserting additional syntax into control statements, see Generating-control-statements-for-Db2-or-other-software-products.

Additional considerations

The following requirements and restrictions apply to the CNTLCARDS option:

  • You must specify a SYSCNTL data set in your JCL.
  • The value that you specify for your CNTLCARDS option applies to all SELECT statements in the job.
  • If you specify FORMAT XML, UNLOAD PLUS ignores the CNTLCARDS option.

  • (SPE2010

    ) After applying (SPE2005)

    , control cards generated by UNLOAD PLUS follow these rules to determine the inclusion of quotation marks:

Table qualifiers and table names

Double quotation marks are retained around a table qualifier or table name if either of them contains any of the following identifiers:

  • Numerical digit as the first character
  • Lowercase characters
  • Embedded blanks
  • Special characters that are not in the following list:
    • $
    • @
    • #

Columns

  • Ordinary identifiers
    • Quotation marks are not added around column names when they include any of the following identifiers:
      • One or more uppercase letters (A–Z)
      • Numerical digits 1–9 (not in the first position of the name)
      • $
      • @
      • #
  • Delimited identifiers
    • Double quotation marks are added around column names containing the following identifiers:
      • Numerical digit as the first character
      • Lower case characters
      • Embedded blanks
      • Special characters that are not in the following list:
        • $
        • @
        • # 

DB2LOAD

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

Additional considerations

The following considerations are specific to CNTLCARDS DB2LOAD. For general considerations for the CNTLCARDS option, see consideration.

  • When you specify ENUMROWS as the first string in the CNTLCARDS, UNLOAD PLUS generates an ENUMROWS value 'ENUMROWS (, nn)' during the unload phase. This value indicates the number of rows that UNLOAD PLUS unloads.
  • When you specify SPANNED YES, UNLOAD PLUS adds the FORMAT SPANNED YES option to the control cards that it generates.
  • When you are unloading XML data, UNLOAD PLUS adds the PRESERVE WHITESPACE option to the field specification for the XML column.
  • When you are unloading row ID columns and DIRECT NO is in effect, UNLOAD PLUS generates load control statements for those columns as if they were defined as GENERATED BY DEFAULT.
  • The following considerations apply when you are unloading a system-period temporal object:
    • When DIRECT YES is in effect, UNLOAD PLUS does not generate field specification control cards for temporal-specific fields.
    • When DIRECT NO is in effect, UNLOAD PLUS generates field specification control cards for temporal-specific fields.
  • When you are unloading application-period temporal data, UNLOAD PLUS generates field specification control cards for business-begin and business-end fields.

'string'

Use this option to specify a string of additional LOAD syntax to insert into the IBM Db2 LOAD utility or BMC LOADPLUS product control statements. You can specify multiple strings of up to 72 bytes each. Enclose each string in single quotes. 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'''). To enable BMC LOADPLUS or IBM Db2 LOAD to override the loading of certain GENERATED ALWAYS data types use OVERRIDE(validoverride) in the supplied string. For more information, including examples, see Inserting-additional-command-options-into-utility-control-statements.

Important

You can also insert additional load syntax to follow the INTO option for the IBM Db2 LOAD or the BMC LOADPLUS generated utility control statements. For more information, see INTO.

Db2

CNTLCARDS Db2 tells UNLOAD PLUS to generate Db2 CREATE TABLE DDL and Db2 LOAD utility control statements in the SYSCNTL data set. UNLOAD PLUS does not generate EDITPROCs or FIELDPROCs.

Additional considerations

The following considerations are specific to CNTLCARDS Db2. For general considerations for the CNTLCARDS option, see consideration.

  • If you specify ENUMROWS as the first string in the CNTLCARDS, UNLOAD PLUS generates an ENUMROWS value 'ENUMROWS (,nn)' during the unload phase. This value indicates the number of rows that UNLOAD PLUS unloads.
  • When you are unloading inline LOB data and DIRECT YES is in effect, UNLOAD PLUS adds the INLINE LENGTH option to the column definition or field specification for the LOB column.
  • When you specify SPANNED YES, UNLOAD PLUS adds the FORMAT SPANNED YES option to the control cards that it generates.
  • When you are unloading XML data, UNLOAD PLUS adds the PRESERVE WHITESPACE option to the column definition or field specification for the XML column.
  • When you are unloading row ID columns and DIRECT NO is in effect, UNLOAD PLUS generates load control statements for those columns as if they were defined as GENERATED BY DEFAULT.
  • The following considerations apply when you are unloading a system-period temporal object:
    • When DIRECT YES is in effect, UNLOAD PLUS:
      • Generates temporal-specific column definitions for the system-begin, system-end, and transaction-start-ID fields
      • Generates the PERIOD SYSTEM_TIME definition
      • Does not generate field specification control cards for temporal-specific fields
    • When DIRECT NO is in effect, UNLOAD PLUS:
      • Generates standard TIMESTAMP column definitions for the system-begin, system-end, and transaction-start-ID fields, but does not include temporal-specific definitions
      • Does not generate the PERIOD SYSTEM_TIME definition
      • Generates field specification control cards for temporal-specific fields
  • When you are unloading application-period temporal data, UNLOAD PLUS generates column definition and field specification control cards for business-begin and business-end fields. UNLOAD PLUS generates the PERIOD BUSINESS_TIME definition only when DIRECT YES is in effect.

  • (SPE2104

    ) If you specify SCHEMA, UNLOAD PLUS generates the SET CURRENT SCHEMA statement before the Db2 CREATE TABLE DDL in the SYSCNTL data set when CNTLCARDS DB2DDL or CNTLCARDS DB2. The table name is unqualified in the Db2 CREATE TABLE DDL.

'string'

Use this option to specify a string of additional LOAD syntax to insert into the IBM Db2 LOAD utility or BMC LOADPLUS product control statements. You can specify multiple strings of up to 72 bytes each. Enclose each string in single quotes. 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'''). To enable BMC LOADPLUS or IBM Db2 LOAD to override the loading of certain GENERATED ALWAYS data types use OVERRIDE(validoverride) in the supplied string. For more information, including examples, see Inserting-additional-command-options-into-utility-control-statements.

Important

You can also insert additional load syntax to follow the INTO option for the IBM Db2 LOAD or the BMC LOADPLUS generated utility control statements. For more information, see INTO.

BMCLOAD

CNTLCARDS BMCLOAD tells UNLOAD PLUS to generate load control statements for LOADPLUS. You can use CNTLCARDS BMCLOAD to create INDSN load control statements when any of the primary unload data sets are dynamically allocated.

Restrictions

The following restrictions and considerations are specific to CNTLCARDS BMCLOAD. For general considerations for the CNTLCARDS option, see consideration.

UNLOAD PLUS terminates if you specify CNTLCARDS BMCLOAD and SPANNED NO is in effect when unloading LOB or XML columns.

Additional considerations

The following considerations apply to CNTLCARDS BMCLOAD:

  • If you specify ENUMROWS as the first string in the CNTLCARDS, UNLOAD PLUS generates an ENUMROWS value 'ENUMROWS (,nn)' during the unload phase. This value indicates the number of rows that UNLOAD PLUS unloads.
  • When you specify SPANNED YES, UNLOAD PLUS adds the FORMAT SPANNED YES option to the control cards that it generates.

  • If you specify CNTLCARDS BMCLOAD and the following conditions exist, also specify AUTOTAG YES to have UNLOAD PLUS generate one LOAD statement with multiple INTO clauses:

    • You are using a single SYSCNTL data set.
    • You specify multiple SELECT statements on tables that are in the same table space.

    Important

    If you also specify FORMAT BMCLOAD, you cannot specify AUTOTAG YES.

  • When you are unloading row ID columns and DIRECT NO is in effect, UNLOAD PLUS generates load control statements for those columns as if they were defined as GENERATED BY DEFAULT.
  • The following considerations apply when you are unloading a system-period temporal object:
    • When DIRECT YES is in effect, UNLOAD PLUS does not generate field specification control cards for temporal-specific fields.
    • When DIRECT NO is in effect, UNLOAD PLUS generates field specification control cards for temporal-specific fields.
  • When you are unloading application-period temporal data, UNLOAD PLUS generates field specification control cards for business-begin and business-end fields.

'string'

Use this option to specify a string of additional LOAD syntax to insert into the LOADPLUS control statements. You can specify multiple strings of up to 72 bytes each. Enclose each string in single quotes. 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'''). To enable BMC LOADPLUS or IBM Db2 LOAD to override the loading of certain GENERATED ALWAYS data types use OVERRIDE(validoverride) in the supplied string. For more information, including examples, see Inserting-additional-command-options-into-utility-control-statements.

Important

You can also insert additional load syntax to follow the INTO option for the BMC LOADPLUS generated utility control statements. For more information, see INTO.

DB2DDL

CNTLCARDS DB2DDL tells UNLOAD PLUS to generate Db2 CREATE TABLE DDL in the SYSCNTL data set. UNLOAD PLUS does not generate EDITPROCs or FIELDPROCs.

Considerations

The following considerations are specific to CNTLCARDS DB2DDL. For general considerations for the CNTLCARDS option, see consideration.

  • The following considerations apply when you are unloading a system-period temporal object:
    • When DIRECT YES is in effect, UNLOAD PLUS:
      • Generates temporal-specific column definitions for the system-begin, system-end, and transaction-start-ID fields
      • Generates the PERIOD SYSTEM_TIME definition
    • When DIRECT NO is in effect, UNLOAD PLUS:
      • Generates standard TIMESTAMP column definitions for the system-begin, system-end, and transaction-start-ID fields, but does not include temporal-specific definitions
      • Does not generate the PERIOD SYSTEM_TIME definition
  • When you are unloading application-period temporal data, UNLOAD PLUS generates column definition control cards for business-begin and business-end fields. UNLOAD PLUS generates the PERIOD BUSINESS_TIME definition only when DIRECT YES is in effect.

  • (SPE2104

    ) If you specify SCHEMA, UNLOAD PLUS generates the SET CURRENT SCHEMA statement before the Db2 CREATE TABLE DDL in the SYSCNTL data set when CNTLCARDS DB2DDL or CNTLCARDS DB2. The table name is unqualified in the Db2 CREATE TABLE DDL.

EASYTRIEVE

CNTLCARDS EASYTRIEVE tells UNLOAD PLUS to generate Easytrieve data definition statements in the SYSCNTL data set.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration

UNLOAD PLUS terminates if you specify CNTLCARDS EASYTRIEVE when unloading LOB or XML columns.

FOCUS

CNTLCARDS FOCUS tells UNLOAD PLUS to generate FOCUS file definition statements in the SYSCNTL data set.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration.

UNLOAD PLUS terminates if you specify CNTLCARDS FOCUS when unloading LOB or XML columns.

NOMAD

CNTLCARDS NOMAD tells UNLOAD PLUS to generate NOMAD data definition statements in the SYSCNTL data set.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration.

UNLOAD PLUS terminates if you specify CNTLCARDS NOMAD when unloading LOB or XML columns.

SAS

CNTLCARDS SAS tells UNLOAD PLUS to generate SAS external file INPUT statements in the SYSCNTL data set.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration.

UNLOAD PLUS terminates if you specify CNTLCARDS SAS when unloading LOB or XML columns.

SQL/DS

CNTLCARDS SQL/DS tells UNLOAD PLUS to generate SQL/DS CREATE TABLE DDL and SQL/DS DATA LOAD utility control statements in the SYSCNTL data set. UNLOAD PLUS does not generate EDITPROCs or FIELDPROCs.

Restrictions

The following restrictions are specific to CNTLCARDS SQL/DS. For general considerations for the CNTLCARDS option, see consideration.

  • UNLOAD PLUS terminates if you specify CNTLCARDS SQL/DS when unloading LOB or XML columns.
  • When you use this option, the only valid specifications for NULLTYPE and NULLCHAR are T1 and ? (the defaults), respectively. If you try to use any other combination of NULLTYPE and NULLCHAR values, UNLOAD PLUS issues message BMC51610E and terminates.

SQL/DS-DDL

CNTLCARDS SQL/DS-DDL tells UNLOAD PLUS to generate SQL/DS CREATE TABLE DDL in the SYSCNTL data set. UNLOAD PLUS does not generate EDITPROCs or FIELDPROCs.

Restrictions

The following restrictions are specific to CNTLCARDS SQL/DS-DDL. For general considerations for the CNTLCARDS option, see consideration.

  • UNLOAD PLUS terminates if you specify CNTLCARDS SQL/DS-DDL when unloading LOB or XML columns.
  • When you use this option, the only valid specifications for NULLTYPE and NULLCHAR are T1 and ? (the defaults), respectively. If you try to use any other combination of NULLTYPE and NULLCHAR values, UNLOAD PLUS issues message BMC51610E and terminates.

SQL/DS-LOAD

CNTLCARDS SQL/DS-LOAD tells UNLOAD PLUS to generate only SQL/DS DATA LOAD utility control statements in the SYSCNTL data set.

Restrictions

The following restrictions are specific to CNTLCARDS SQL/DS-LOAD. For general considerations for the CNTLCARDS option, see consideration.

  • UNLOAD PLUS terminates if you specify CNTLCARDS SQL/DS-LOAD when unloading LOB or XML columns.
  • When you use this option, the only valid specifications for NULLTYPE and NULLCHAR are T1 and ? (the defaults), respectively. If you try to use any other combination of NULLTYPE and NULLCHAR values, UNLOAD PLUS issues message BMC51610E and terminates.

TERADATA

CNTLCARDS TERADATA tells UNLOAD PLUS to generate all of the CREATE TABLE DDL and load utility control statements that are included in the related TERADATA options. You must edit the data set to indicate the utility to run.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration

UNLOAD PLUS terminates if you specify CNTLCARDS TERADATA when unloading LOB or XML columns.

TERADATA-BULK

CNTLCARDS TERADATA-BULK tells UNLOAD PLUS to generate CREATE TABLE DDL and load utility control statements in the SYSCNTL data set for use with the Teradata Bulk Data Load utility.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration.

UNLOAD PLUS terminates if you specify CNTLCARDS TERADATA-BULK when unloading LOB or XML columns.

TERADATA-FAST

CNTLCARDS TERADATA-FAST tells UNLOAD PLUS to generate CREATE TABLE DDL and load utility control statements in the SYSCNTL data set for use with the Teradata Fast Data Load utility.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration

UNLOAD PLUS terminates if you specify CNTLCARDS TERADATA-FAST when unloading LOB or XML columns.

TERADATA-MULT

CNTLCARDS TERADATA-MULT tells UNLOAD PLUS to generate CREATE TABLE DDL and load utility control statements in the SYSCNTL data set for use with the Teradata Multiload utility.

Restrictions

The following restriction is specific to this value. For general considerations for the CNTLCARDS option, see consideration.

UNLOAD PLUS terminates if you specify CNTLCARDS TERADATA-MULT when unloading LOB or XML columns.


 

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