StatementInformation - Call-Level Interface Version 2

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

Product
Call-Level Interface Version 2
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2418
lifecycle
previous
Product Category
Teradata Tools and Utilities

StatementInformation

Purpose

Returned in response to a successfully executed Teradata SQL statement when Return‑statement‑info 'Y' was specified (corresponding to the Options parcel (flavor 85) Return‑statement‑info 'Y' option). If more data needs be returned than will fit into a single StatementInformation parcel, multiple parcels will be returned.

Usage Notes

This parcel is generated by Teradata Database.

Parcel Data

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.

Note: 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

Note: 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:

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.Note: 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).

Note: To allow for future enhancement, lengths longer than expected, along with the additional data, must be ignored.

Full-layout

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). Table 21 shows the Full-layout fields:

 

Table 21: Full-layout Fields 

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.

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.

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 'Y' if the column is an Identity Column; 'N' otherwise. If an Identity column is not involved or the information is not available, an ASCII 'U' is set.

PBTIFDW

1 byte character

Set to an ASCII '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 ASCII '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 ASCII 'U' is set.

PBTIFMN

1 byte character

Set to an ASCII '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 ASCII 'U' is set.

PBTIFSR

1 byte character

Set to an ASCII '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 ASCII 'U' is set.

PBTIFWR

1 byte character

Set to an ASCII '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 Table 16 on page 262 for the possible data types in addition to those in Table 22 on page 336.

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 ASCII '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 ASCII 'U' is set.

PBTIFSN

1 byte unsigned character

Set to an ASCII '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 ASCII 'U' is set.

PBTIFK

1 byte unsigned character

Set to an ASCII '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 ASCII 'U' is set.

PBTIFU

1 byte unsigned character

Set to an ASCII '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 ASCII 'U' is set.

PBTIFE

1 byte unsigned character

Set to an ASCII '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 ASCII 'U' is set.

PBTIFSO

1 byte unsigned character

Set to an ASCII '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 ASCII 'U' is set.

Note: Due to the large number of variable-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 variable-length fields be inspected.

 

Table 22: 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

PERIOD (DATE)

832

833

1332

1333

1334

PERIOD (TIME)

836

837

1336

1337

1338

PERIOD (TIME WITH TIME ZONE

840

841

1340

1341

1342

PERIOD (TIMESTAMP

844

845

1344

1345

1346

PERIOD (TIMESTAMP WITH TIME ZONE)

848

849

1348

1349

1350

Note: These data types may not be used in DataInfo[X] (flavor 71 or 146) request parcels.

Limited-layout

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). Table 23 shows the Limited-layout fields.

 

Table 23: Limited-layout Fields 

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 Table 16 on page 262 for the possible data types in addition to those in Table 22 on page 336.

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 following table shows the Statistic-layout fields.

 

Table 24: 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.