Options - Teradata Tools and Utilities

Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems - 17.20
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Teradata Tools and Utilities
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-11-17
dita:mapPath
oxw1641281447623.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
vfj1470443597526
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

Specifies which statement options are used when a request is sent to the database.

Usage Notes

The Options parcel is generated by CLIv2 at the direction of an application, or by an advanced application building its own request parcels (DBCAREA option Request-mode=B).

Parcel Data

The following information applies to the Options parcel.

Flavor Parcel Body Length Parcel Body Fields
85 10, 11, 14, 15, 17, 18, or 21 RequestMode: 1 byte
Function: 1 byte
Select-data: 1 byte
Continued-characters-state: 1 byte
APH-response: 1 byte
Return-statement-info: 1 byte
TransformsOff: 1 byte
Maximum DECIMAL precision: 1 byte (ignored if Field Response-mode)
IdentityColumnRetrieval: 1 byte
DynamicResultSets: 1 byte
If present, SP-ReturnResult: 1 byte
If present, PeriodAsStructs: 1 byte
  • Extended-name Response: 1 byte
  • Trusted-request: 1 byte
If present, StatementError: 1 byte
If present, ArrayTransformsOff: 1 byte
If present, XMLformat: 1 byte
If present, Fast-fail: 1 byte
(If present, reserved: 1 byte)
(If present, reserved: 1 byte)
If present, Large-Rows: 1 byte

Field Notes

The following information applies to Options fields.

The following ten fields must be present for every use of the Options parcel.

Field Description
RequestMode
RequestMode indicates which mode is used to process a response. The settings are as follows:
  • F – Specifies Field Mode
  • R – Specifies Record Mode
  • I – Specifies Indicator Mode
  • M – MultipartIndicator Mode

If this field contains a binary zero, the database uses the mode specified elsewhere (in the Req, IndicReq, or FMReq parcel) in the request.

When two or more parcels in a request specify conflicting modes, the database uses the mode specified in the Options parcel.
Function Function corresponds to the Request-processing-option field of the DBCAREA, and indicates whether the intent is to prepare and execute, to prepare, or to execute the request. The settings are as follows:
  • E – Specifies that the request should be executed.
  • A binary zero in this field is interpreted as an E.
  • P – Specifies that a PrepInfo parcel is built for each statement in the request. The statements may not contain parameterized SQL.
  • S – Specifies that a PrepInfo parcel is built for each statement in the request. The statements may contain parameterized SQL.
  • B – Specifies that a PrepInfo parcel is built for each statement in the request and that the request should be executed. The statements may contain parameterized SQL. This setting is supported for both Indicator mode and Record mode. For SQL statements that return no data, such as Insert, Update, Delete and DDL statements, an “empty” PrepInfo parcel is returned. Empty means that the column count in the PreInfo parcel is set to zero.
Select-data Select-data specifies the way data associated with a Large Object is returned, chosen as one of the following EBCDIC characters:
  • I – If the actual data is to be returned in the initial response.
  • L – If a locator token associated with the data within the transaction is to be returned.
  • S – If a locator token associated with the data within the spool file is to be returned.

If the field contains a binary zero, a value of 'I' is assumed.
Continued-characters-state Continued-characters-state indicates whether character data crossing response parcels is well-formed within each parcel or only when all relevant parcels are considered. This option has effect only for MultipartIndicator Response-mode because only in that mode may data cross parcel boundaries. If not applicable, the option is ignored. The supported values are:
  • L – The default, specifies that character data crossing response parcels may be in the locked shift state
  • U – Specifies that character data crossing response parcels must be in the unlocked shift state
APH-response APH-response indicates whether response parcels may use the alternate parcel header. The supported values are:
  • Y – Specifies that alternate parcel headers may be used in response parcels
  • Binary zero – The default, if alternate parcel headers may not be used in response parcels
Return-statement-info Return-statement-info indicates whether response parcels may use the alternate parcel header. The supported values are:
  • 'Y' specifies that StatementInformation response parcels is returned instead of DataInfo[X] and PrepInfo[X] response parcels. This setting is rejected if the ResponseMode option specifies 'F' (Field mode).
  • 'N', the default, if StatementInformation response parcels may not be used.
TransformsOff
TransformsOff indicates whether SQL Structured User Defined Types (UDTs) are transformed into their external data type or left as their constituent attributes. The supported values are:
  • 'N', the default, if Structured UDTs are transformed into their external type.
  • 'Y' if Structured UDTs are not transformed.

When built by CLIv2, the value used is based on the DBCAREA Transforms-off option.
Maximum DECIMAL precision Maximum DECIMAL precision indicates the precision of decimal data types in responses. The supported values are:
  • A positive value up to a maximum obtained using the DBCHQE SQL-limits query.
  • Binary zero, the default, if the client default, 15, is to be used.
IdentityColumnRetrieval IdentityColumnRetrieval indicates whether data is returned in response to an SQL Insert operation when Identity columns are involved. The supported values are:
  • Binary zero, the default, that no fields are returned.
  • 'C' specifies that the values for any Identity columns are returned.
  • 'R' specifies that the values for all fields in an inserted row that contains Identity columns are returned.
DynamicResultSets DynamicResultSets indicates whether stored procedures may include the results of its SQL requests in the response to the response to the SQL CALL statement invoking the procedure. The supported values are:
  • 'N', the default, if such results may not be included.
  • 'Y' if such results may be included.

When the previous fields are present, the following field may be present also, as needed:

Field Description
SP-ReturnResult SP-ReturnResult indicates where the results of the associated request are to be returned. The supported values are:
  • 0, the default, if the results are returned to the stored procedure itself.
  • 1, if the results are to be returned to the client.
  • 2, if the results are to be returned to the CALLer of the stored procedure.
  • 3, if the results are to be returned to both the stored procedure and the client.
  • 4, if the results are to be returned to both the stored procedure and its CALLer.

If this field contains a binary zero, the database uses the mode specified elsewhere (in the Req, IndicReq, or FMReq parcel) in the request.

When two or more parcels in a request specify conflicting modes, the database uses the mode specified in the Options parcel.

When built by CLIv2, the value used is based on the DBCAREA Return-result-to option; when built by an application, whether this option is permitted by the database can be ascertained using the CLIv2 DBCHQE Result-sets-support query.

When the previous fields are present, if any of the following three fields are needed, all three must be present:

Field Description
PeriodAsStructs PeriodAsStructs indicates whether Period data types are treated as Structured UDTs and supply or return information about the constituent attributes. The supported values are:
  • 'N', the default, if Period data types are not transformed.
  • 'Y' if Period data types are transformed.

When built by CLIv2, the value used is based on the DBCAREA Period-as-struct option; whether this option is permitted by the database can be ascertained using the CLIv2 DBCHQE Transforms-off-usage query.
Extended-name Response Extended-name Response indicates whether varying-length column name, title, and format information in existing response parcels (Field (flavor 18), PrepInfo[X] (flavors 86 and 126), StmtInfo (flavor 169)) can be longer than the existing maximum. The supported values are:
  • 0, the default, indicates varying-length column name, title, and format information in existing response parcels cannot be longer than the existing maximum.
  • 1 indicates varying-length column name, title, and format information in existing response parcels can be longer than the existing maximum.

When built by CLIv2, the value used is based on the DBCAREA Column-info option; whether this option is permitted by the database can be ascertained using the CLIv2 DBCHQE Build-information query.
Trusted-request Trusted-request indicates whether the request is trusted to use the Teradata SQL SET QUERY_BAND='PROXYUSER=...' statement to change the session userid. The supported values are:
  • 'N', the default, indicates the request may not change the session userid.
  • 'Y' indicates the request may change the session userid.

When built by CLIv2, the value used is based on the DBCAREA Trusted-request option; whether this option is permitted by the database can be ascertained using the CLIv2 DBCHQE Trusted-request-support query.

When the previous fields are present, the following field may be present also, as needed:

Field Description
StatementError StatementError indicates whether each statement in a multi-statement request might reflect an error. The supported values are:
  • 'N', the default, if a problem with any statement in a multi-statement request ends processing of that request.
  • 'Y' if problems with a statement in a multi-statement request allows other statements to be processed.

When built by CLIv2, the value used is based on the DBCAREA Multi-statement-error option; when built by an application, whether this option is permitted by the database can be ascertained using the CLIv2 Build-information query.

When the previous fields are present, if either of the following two fields are needed, both must be present:

Field Description
ArrayTransformsOff ArrayTransformsOff indicates whether SQL Array Types are transformed into VARCHAR data or left as their internal type. The supported values are:
  • 'N', the default, if Array Types are not transformed.
  • 'Y', if Array Types are transformed into VARCHAR data.

When built by CLIv2, the value used is based on the DBCAREA DBRIATO option; when built by an application, whether this option is permitted by the database can be ascertained using the CLIv2 Build-information query.
XMLformat XML-response-format indicates the format in which XML data is returned. The supported values are:
  • 'C', the default, if XML data is returned as a character string.
  • 'B', if XML data is returned as binary data.

When built by CLIv2, the value used is based on the DBCAREA XML-response format option; when built by an application, whether this option is permitted by the database can be ascertained using the CLIv2 Build-information query.

When the previous fields are present, the following field may be present also, as needed:

Field Description
Fast-fail Fast-fail indicates whether or not the database delays due to database workload throttling cause the request to be failed. The supported values are:
  • 'Y', the default, if such Database conditions cause the request to be failed.
  • 'N', the default, if such database conditions cause the request to be delayed, though the database may fail the request due to other workload rules.

When built by CLIv2, the value used is based on the DBCAREA Wait-during-delay option; when built by an application, whether this option is permitted by the database can be ascertained using the CLIv2 Build-information query.

If the DBCAREA option Request-mode=B was specified and a request is being built that is similar to what CLIv2 builds, when present, the Options parcel must be the first parcel.

When the previous fields are present, if any of the following three fields are needed, all three must be present:

Field Description
(reserved) Always contain binary zeroes.
(reserved) Always contain binary zeroes.
Large-Rows Large Rows indicates whether response rows may be larger than 65535 bytes. The supported values are:
  • 0, the default, indicates response rows cannot be larger than 65535 bytes.
  • 1 indicates response rows can be larger than 65535 bytes.

When built by CLIv2, the value used is based on the DBCAREA Large-row-OK option. Applications may determine the size of the maximum response length using the DBCHQE CLIv2-limits query.