15.10 - Options - Call-Level Interface Version 2

Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems

prodname
Call-Level Interface Version 2
vrm_release
15.10
category
Programming Reference
featnum
B035-2417-035K

Specifies which statement options are used when a request is sent to the Teradata 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, or 18

RequestMode: 1 byte

Function: 1 byte

Selectdata: 1 byte

Continuedcharactersstate: 1 byte

APHresponse: 1 byte

Returnstatementinfo: 1 byte

TransformsOff: 1 byte

Maximum DECIMAL precision: 1 byte
(ignored if Field Response
mode)

IdentityColumnRetrieval: 1 byte

DynamicResultSets: 1 byte

If present, SPReturnResult: 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

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 Teradata 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 Teradata 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 will be returned instead of DataInfo[X] and PrepInfo[X] response parcels. This setting will be 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 Teradata 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 Teradata 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 Teradata 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 mail 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.