Dynamic Result Sets - Teradata Tools and Utilities

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

Teradata Tools and Utilities
Release Number
June 2021
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

Even if the procedure requests that the results of its SQL be reflected to the CALLer or the application through the use of either the SQL PROCEDURE’s WITH RETURN clause or the DBCAREA Return-result-to option, a CLIv2 routine so targeted must allow these results using the DBCAREA Result-sets-OK option. If this option was not specified, the results will not be returned there.

When results are propagated, they appear in the response with an incremented statement number after the statement that contained the SQL CALL. The statement number is contained in both the first parcel for the implicit statement, either Success, OK, StatementStatus; and the last parcel for the implicit statement: EndStatement. Such a Success, OK, or StatementStatus parcel will also contain an ActivityType of 176. The next parcel will be ResultSet, which identifies the procedure and provides the row number to which the procedure positioned the results. Any response parcels follow the ResultSet.

The options for the request making the CALL and the request issued by the stored procedure could differ: the response provided to the procedure, the caller, or the application would reflect the request options that each established. So a response honors that request’s DBCAREA Return-objects-as, Continued-characters-state, APH-response-OK, Return-statement-info, Max-decimal-returned, Return-identity-data options. If the procedure specified the DBCAREA MultipartIndicator Response-mode and selects a Large Object (LOB) but the CALLer or application specified a Response-mode that does not support LOBs, the procedure will receive an error and no response will be propagated.

Similarly, the response provided to the procedure, the caller, or the application honors the character set that each established. However, their collation is not. Instead, the collation used for all responses is the collation that was used when the stored procedure was compiled.

The procedure may use the DBCAREA Keep-response option to provide CLIv2 the same information that would be provided by SCROLL or NO SCROLL on the SQL DECLARE CURSOR statement for an SQL Stored Procedure. Specifically, a setting of 'Y' corresponds to NO SCROLL, indicating the propagated results are positioned to the next unfetched row, with fetched rows not propagated; a setting of 'P' corresponds to SCROLL, indicating the propagated results are positioned to the last row fetched, with earlier fetched rows propagated and available by explicit positioning using either the CLIv2 Fetch function specifying the DBCAREA Positioning-action, or the CLIv2 Rewind function. The Success, OK, or StatementStatus parcel ActivityCount field will indicate all available rows available, without regard to initial positioning.

Unlike positioning of the returned row, the procedure cannot position the offset of a Large Object (LOB) selected by locator. Any positioning by the procedure within the LOB is not reflected.