You can program an embedded SQL application to perform parallel request processing using more than one Teradata session. Such an application can transmit several requests simultaneously, one per each session.
A multisession application is more complicated to implement, debug, and maintain than a single session application, so before you implement multisession programming, you should determine whether multistatement requests on a single session satisfy your throughput and response time requirements.
If you decide the situation calls for multisession programming, then the preprocessor provides facilities to implement multisession applications.
How Multiple Sessions Work
| USE this statement … | TO … |
|---|---|
| CONNECT or LOGON with an AS session_id clause | uniquely name each of the Teradata sessions. This action differentiates the multiple sessions. When more than one session is to be used, the application must name each one explicitly. |
| SET CONNECTION | switch between each of the named sessions using the unique session identifier specified in the CONNECT or LOGON statements. |
| LOGOFF session_id | disconnect an application from a specific named session. |
| LOGOFF ALL | disconnect an application from all sessions. |
How Asynchronous Requests Work
| WHEN you … | THEN the session … |
|---|---|
| add an ASYNC clause to the executable SQL request | initiates a uniquely named request. The session returns control to the application without waiting for the completion of the asynchronous request. |
| use the WAIT statement | waits for the completion of ANY, ALL, or a list of asynchronous requests. |
| use the TEST statement | tests for the completion of the asynchronous request and returns the results of the request after it has completed. |
ASYNC Statement Modifier
Each asynchronous request can be identified to the preprocessor by using the unique asynchronous request identifier specified in the ASYNC statement modifier preceding the executable SQL request.
When the ASYNC modifier is added to the executable SQL request, the request is initiated on the current session and returns control back to the application without waiting for the completion of the asynchronous request.
WAIT Statement
An application program can have requests pending on several sessions simultaneously.
- An application can call an asynchronous wait using the WAIT ANY syntax.
The wait ends when any outstanding asynchronous request completes, and returns the session identifier and the asynchronous request identifier.
- An application can wait for all asynchronous requests to complete using the WAIT ALL syntax.
The wait ends when all outstanding asynchronous requests complete.
- An application can call a synchronous wait using the WAIT asynchronous_request_id_list syntax, specifying the asynchronous request identifier of any active asynchronous requests.
The wait ends when all specified requests complete.
TEST Statement
The TEST statement tests for the completion of an asynchronous request. Once an asynchronous request has completed, TEST is used to retrieve the status of the execution of the asynchronous request.
TEST can also be used to asynchronously test whether an outstanding asynchronous request has completed without having to wait for the request to complete. If the request has not completed, TEST returns an SQL ‘not yet complete’ message.
TEST can be executed only once against the asynchronous request, and only after the request has completed.
SET CONNECTION Statement
The SET CONNECTION statement permits an application to switch among multiple sessions.
Status Variables and Data Structures for Embedded SQL Applications
Embedded SQL applications use several standardized status variables and data structures to communicate between the application and the Teradata Database.
- SQLSTATE
- SQLCODE
The activity count, an enumeration of the number of rows returned by a query, is also useful for many applications. The activity count is reported in the third word in the SQLERRD array for embedded SQL applications and in the status variable declared as ACTIVITY_CODE for stored procedures.
Related Topics
- ASYNC modifier, see ASYNC Statement Modifier.
- WAIT, see WAIT.
- TEST, see TEST.
- SET CONNECTION, see SET CONNECTION.
- Standard host variables that receive completion and exception status codes, see Result Code Variables.
- SQLSTATE or SQLCODE, see SQLSTATE
- SQLDA, see SQL Descriptor Area (SQLDA).
- The Teradata Database analog of SQLCODE and SQLSTATE, called the SQL Communications Area (SQLCA), see SQL Communications Area (SQLCA).
- Activity counts, see ACTIVITY_COUNT and SQLSTATE Mappings.