16.20 - StatementInformation—Parcel Data - Teradata Tools and Utilities

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

prodname
Teradata Tools and Utilities
vrm_release
16.20
created_date
September 2019
category
Programming Reference
featnum
B035-2417-108K

The following information applies to the StatementInformation parcel.

Flavor Parcel Body Length Parcel Body Fields
169 6 to maximum parcel size Self-defining extensions: Six or more bytes

One or more self-defining extensions are present to convey situation-dependent information. While the extensions may require more than one StatementInformation parcel, an extension will be wholly contained in one parcel. The extensions fall into two types: those that describe one or more items and those with an Information Layout of End-information that end related extensions of the first type.

When multiple extensions with different Information Ids are present, their order is undefined so may change in the future.

Each extension begins with the following fields:

Field Length Description
PBTILOUT 2-byte unsigned integer Information Layout identifies the layout of the data following the header as one of the following:
  1. Full-layout
  2. Limited-layout
  3. Statistic-layout
  4. End-information
  5. Untransformed limited-layout used only in requests, for details refer to the table below titled “PBTIFDT Additional Data Types.”
To allow for future enhancement, other values must be ignored.
PBTIID 2-byte unsigned integer Information Id identifies the type of extension as one of the following integers:
  • 1 – Parameter (used only when DBCAREA Request-processing-option is 'S' (Prepare mode supporting parameterized SQL) (corresponding to the Options parcel (flavor 85) Function 'S' option), which describes each item specified by a parameter (indicated by question mark) in the SQL statement.
  • 2 – Query, which describes each item (field or column) returned by an SQL SELECT statement.
  • 3 – Summary, which describes each summary item returned by a WITH clause of an SQL SELECT statement.
  • 4 – Identity-column, which describes each item (field or column) returned by an SQL INSERT statement, as requested by DBCAREA Return-identity-data (corresponding to Options parcel (flavor 85) IdentityColumnRetrieval)
  • 5 – Stored-procedure-Output, which describes each item returned by a stored-procedure OUT or INOUT parameter.
  • 6 – Stored-procedure-resultSet, which describes each row returned by a stored-procedure.
  • 7 – Estimated-processing, which provides an estimate of the execution time for the statement.
  • 8 – Data-attributes. Used only in requests. For more information, see StatementInformation.
To allow for future enhancement, other values must be ignored.
PBTILEN 2-byte unsigned integer Information Length specifies the length of subsequent data in the extension (does not include the length of the extension header).
To allow for future enhancement, lengths longer than expected, along with the additional data, must be ignored.

Full-layout for Responses

The Full-layout is used for Parameter, Query, Summary, Identity-column, Stored-procedure-output, and Stored-procedure-resultSet information, when DBCAREA Request-processing-option is 'P' (Prepare), 'S' (Prepare mode supporting parameterized SQL), or 'B' (Prepare and Execute) (corresponding to the Options parcel [flavor 85] Function 'P', 'S', and 'B' options). The table below describes the Full-layout fields specific to responses.

Field Length Description
PBTIFDB 2-byte unsigned integer plus the number of bytes specified by that integer Database-name consisting of the length in bytes of the name, followed by that name in characters from the current session character set. If the name does not apply in the SQL statement's context or is not available, the length is zero and no name is present. The maximum length of a name may be obtained using the DBCHQE SQL-limits query.
PBTIFTB 2-byte unsigned integer plus the number of bytes specified by that integer Table, View, or Procedure name consists of the length in bytes of the name, followed by that name in characters from the current session character set. If the name does not apply in the SQL statement's context or is not available, the length is zero and no name is present. The maximum length of a name may be obtained using the DBCHQE SQL-limits query.
PBTIFCN 2-byte unsigned integer plus the number of bytes specified by that integer Column, User-defined Function (UDF), User-defined Method (UDM), Stored-procedure, or parameter name consists of the length in bytes of the name, followed by that name in characters from the current session character set. If the name does not apply in the SQL statement's context or is not available, the length is zero and no name is present. The maximum length of a name may be obtained using the DBCHQE SQL-limits query.
PBTIFCP 2-byte unsigned integer The relative position of the column within the table, with the first column having a value of '1'. If the value does not apply in the SQL statement's context (for example, the item is an expression) or is not available, a zero is present
PBTIFAN 2-byte unsigned integer plus the number of bytes specified by that integer AS-name consists of the length in bytes of the column name as renamed by an SQL AS-clause, followed by that name in characters from the current session character set. If the name does not apply in the SQL statement's context or is not available, the length is zero and no name is present.
PBTIFT 2-byte unsigned integer plus the number of bytes specified by that integer Column Title consists of the length in bytes of the title, followed by that title in characters from the current session character set. If the title does not apply in the SQL statement's context (for example, the item is an expression) or is not available, the length is zero and no title is present. The maximum length is not externally provided by Teradata Database so the only limit available is 65535.
PBTIFF 2-byte unsigned integer plus the number of bytes specified by that integer Column Format consists of the length in bytes of the format, followed by that format in characters from the current session character set. If the format does not apply in the SQL statement's context (for example, the item is an expression) or is not available, the length is zero and no format is present. The maximum length is not externally provided by Teradata Database so the only limit available is 65535.
PBTIFDV 2-byte unsigned integer plus the number of bytes specified by that integer Default Value consists of the length in bytes of the default, followed by that default in characters from the current session character set. If the default does not apply in the SQL statement's context (for example, the item is an expression) or is not available, the length is zero and no default is present.
PBTIFIC 1-byte character Set to an EBCDIC 'Y' if the column is an Identity Column; 'N' otherwise. If an Identity column is not involved or the information is not available, an EBCDIC 'U' is set.
PBTIFDW 1-byte character Set to an EBCDIC 'Y' if the column is definitely writable (that is, the user has permission to update the column); 'N' otherwise (the item is not a column or the user's permission is insufficient).
PBTIFNL 1-byte character Set to an EBCDIC 'Y' if NULLs can be stored in item (columns not defined NOT NULL); 'N' otherwise (for example, the item is an expression). If this concept does not apply in the SQL statement's context or the information is not available, an EBCDIC 'U' is set.
PBTIFMN 1-byte character Set to an EBCDIC 'Y' if NULLs can be returned for the item; 'N' otherwise. If this concept does not apply in the SQL statement's context or the information is not available, an EBCDIC 'U' is set.
PBTIFSR 1-byte character Set to an EBCDIC 'Y' if the item is permitted in a WHERE SQL clause; 'N' otherwise (for example, the result is a Large Object (LOB)). If this concept does not apply in the SQL statement's context or the information is not available, an EBCDIC 'U' is set.
PBTIFWR 1-byte character Set to an EBCDIC 'Y' if the column is writable (the item is a modifiable column); 'N' otherwise (for example, the item is an expression).
PBTIFDT 2-byte unsigned integer Specifies the data type of the item returned. If the type is ambiguous (for example, the item is a parameter in an expression) or is not available, a zero is set. In addition to the data types available for the PrepInfo[X] (flavor 86 or 125) and DataInfo[X] (flavor 71 or 146) parcels, see Data Type for the possible data types. See the table below titled “PBTIFDT Additional Data Types” for additional data types that are also supported.
PBTIFUT 2-byte unsigned integer For user-defined types, a binary 1 will be set for structured types, 2 for distinct types, or 3 for internal types. If the type is ambiguous (for example, a parameter in an expression) or is not available, a zero is set.
PBTIFTY 2-byte unsigned integer plus the number of bytes specified by that integer Type Name consists of the length in bytes of the name, followed by that name in characters from the current session character set. If the name does not apply in the SQL statement’s context (for example, the type is not user-defined) or is not available, the length is zero and no name is present. The maximum length of a name may be obtained using the DBCHQE SQL-limits query.
PBTIFMI 2-byte unsigned integer plus the number of bytes specified by that integer Data Type Miscellaneous Information consists of the length in bytes of the information, followed by that information in characters from the current session character set. If the information does not apply in the SQL statement’s context (for example, the type has no information) or is not available, the length is zero and no information is present.
PBTIFMDL 8-byte unsigned integer The total number of bytes of data that might be returned for this item.
PBTIFND 2-byte unsigned integer The total number of digits if the item has a decimal or numeric data type. For other data types, a zero is returned.
PBTIFNID 2-byte unsigned integer The number of interval digits if the item is a temporal data type. For other data types, a zero is returned.
PBTIFNFD 2-byte unsigned integer The number of fractional digits if the item is a decimal data type (or the deprecated temporal types such as time, timestamp, interval...to second, and interval second). For other data types, a zero is returned.
PBTIFCT 1-byte unsigned integer The character set type of the item, as either 1 if Latin, 2 if Unicode, 3 if Japanese Shift-JIS, 4 if Graphic, or 5 if Japanese Kanji1. If not character data, a zero is returned.
PBTIFMNC 8-byte unsigned integer The total number of characters returned for this item. A value of zero is set if not character data.
PBTIFCS 1-byte unsigned character Set to an EBCDIC 'Y' if a character item is case sensitive (column defined CASESPECIFIC); 'N' if not or not a character item. If this concept does not apply in the SQL statement’s context or the information is not available, an EBCDIC 'U' is set.
PBTIFSN 1-byte unsigned character Set to an EBCDIC 'Y' if a numeric item is signed; 'N' if unsigned (the BYTE data type) or not a numeric item. If this concept does not apply in the SQL statement's context or the information is not available, an EBCDIC 'U' is set.
PBTIFK 1-byte unsigned character Set to an EBCDIC 'Y' if the columns uniquely describes the row; 'N' otherwise. If this concept does not apply in the SQL statement's context or the information is not available, an EBCDIC 'U' is set.
PBTIFU 1-byte unsigned character Set to an EBCDIC 'Y' if the column is the only member of a unique index; 'N' otherwise. If this concept does not apply in the SQL statement's context or the information is not available, an EBCDIC 'U' is set.
PBTIFE 1-byte unsigned character Set to an EBCDIC 'Y' if the item is an expression; 'N' otherwise. If this concept does not apply in the SQL statement’s context or the information is not available, an EBCDIC 'U' is set.
PBTIFSO 1-byte unsigned character Set to an EBCDIC 'Y' if the item is permitted in an ORDERBY SQL clause; 'N' otherwise (for example, the result is a Large Object (LOB)). If this concept does not apply in the SQL statement's context or the information is not available, an EBCDIC 'U' is set.
Due to the large number of varying-length fields (fields containing a length followed by that amount of data), great care is required to process a Full-layout extensions. To locate a field requires that the length of all previous varying-length fields be inspected.

When at least one more byte is present, the first such byte is defined as follows:

Field Length Description
PBTIFTP 1-byte character
Set to one of the following EBCDIC characters:
  • 'I' if the item is a stored-procedure IN parameter
  • 'O' if the item is a stored-procedure OUT parameter
  • 'B' if the item is a stored-procedure INOUT parameter

If the item is not a stored-procedure parameter or the information is not available, an EBCDIC 'U' set.

PBTIFDOS 2 bytes When Transforms are off for this type of item, the depth of this attribute of the item's structure. A value of zero indicates the item is not a structure or Transforms are not off. Other values indicate the nesting level of the structure
PBTIFTC 1-byte character Set to one of the following EBCDIC characters:
  • 'N' if the item is non-temporal
  • 'V' if the item is ValidTime
  • 'T' if the item is a TransactionTime
  • 'U' if the information is unavailable
PBTIFUA 2-byte unsigned integer plus the number of bytes specified by that integer When Transforms are off for this type of item, Untransformed Attribute Name consists of the length in bytes of the name of this attribute in the item's structure, followed by that name in characters from the current session character set. If the item is not a structure or Transforms are not off, the length is zero and no name is present. The maximum length of a name may be obtained using the DBCHQE SQL-limits query.
PBTIFTDT 2-byte unsigned integer When Transforms are off for this type of item, specifies the data type of the untransformed item. If the item is not a structure or Transforms are not off, the value is zero. In addition to the data types available for the PrepInfo[X] (flavor 86 or 125) and DataInfo[X] (flavor 71 or 146) parcels, which are defined in Data Type. "PBTIFDT Additional Data Types" below shows additional types that are supported.
PBTIFDT Additional Data Types 
Name Type if non-nullable Type if Nullable Type if IN parameter Type if INOUT parameter Type if OUT parameter
TIME 760 761 1260 1261 1262
TIMESTAMP 764 765 1264 1265 1266
TIME WITH TIME ZONE 768 769 1268 1269 1270
TIMESTAMP WITH TIME ZONE 772 773 1272 1273 1274
INTERVAL YEAR 776 777 1276 1277 1278
INTERVAL YEAR TO MONTH 780 781 1280 1281 1282
INTERVAL MONTH 784 785 1284 1285 1286
INTERVAL DAY 788 789 1288 1289 1290
INTERVAL DAY TO HOUR 792 793 1292 1293 1294
INTERVAL DAY TO MINUTE 796 797 1296 1297 1298
INTERVAL DAY TO SECOND 800 801 1300 1301 1302
INTERVAL HOUR 804 805 1304 1305 1306
INTERVAL HOUR TO MINUTE 808 809 1308 1309 1310
INTERVAL HOUR TO SECOND 812 813 1312 1313 1314
INTERVAL MINUTE 816 817 1316 1317 1318
INTERVAL MINUTE TO SECOND 820 821 1320 1321 1322
INTERVAL SECOND 824 825 1324 1325 1326
These data types may not be used in DataInfo[X] (flavor 71 or 146) request parcels.

Limited-layout for Responses

The Limited-layout is used for Query, Summary, Identity-column, Stored-procedure-output, and Stored-procedure-resultSet information, when DBCAREA Request-processing-option is 'E' (Execute) (corresponding to the Options parcel (flavor 85) Function 'E' option). The following table describes the Limited-layout fields specific to responses.

Field Length Description
PBTILDT 2 byte unsigned integer Specifies the data type of the item returned. If the type is ambiguous (for example, a parameter in an expression) or is not available, a zero is set. In addition to the data types available for the PrepInfo[X] (flavor 86 or 125) and DataInfo[X] (flavor 71 or 146) parcels, see Data Type for the possible data types. See the table titled "PBTIFDT Additional Data Types" in the previous section, “Full-layout for Responses” for additional data types that are also supported.
PBTILMDL 8 byte unsigned integer The total number of bytes of data that might be returned for this item.
PBTILND 2 byte unsigned integer The total number of digits if the item has a decimal or numeric data type. For other data types, a zero is returned.
PBTILNID 2 byte unsigned integer The number of interval digits if the item is a temporal data type. For other data types, a zero is returned.
PBTILNFD 2 byte unsigned integer The number of fractional digits if the item is a decimal data type (or the deprecated temporal types such as time, timestamp, interval...to second, and interval second). For other data types, a zero is returned.

Statistic-layout

The Statistic-layout is used for Estimated-processing information. The table below shows the Statistic-layout fields.

Statistic-layout fields 
Field Length Description
PBTISEE 8 byte unsigned integer The estimated execution time for the statement, in milliseconds.

End-information layout

The End-information layout indicates there are no more items for the current information (Parameter, Query, Summary, Identity-column, Stored-procedure-output, Stored-procedure-resultSet, or Estimated-processing). There is no data for End-information.