Purpose
Pauses execution of the invoking program and waits for the completion of one or more asynchronous SQL statements.
Invocation
Executable.
Embedded SQL only.
Syntax
WAIT { { statement_spec [,...] | ALL } COMPLETION | ANY COMPLETION INTO [:] statement_variable [:] session_variable }
- statement_spec
-
{ async_statement_identifier | :async_statement_identifier_variable_name }
- async_statement_identifier
- A case-sensitive, application-supplied identifier for an asynchronously executed SQL statement assigned by the ASYNC modifier.
- async_statement_identifier_variable_name
- The name of a host variable that contains an asynchronous statement identifier to be passed to the WAIT statement.
- ALL
- Pauses execution for all current asynchronously executed SQL statements.
- statement_variable
- The name of the host variable into which the asynchronous statement identifier for the completed request is to be written.
- session_variable
- The name of the host variable into which the ID of the session in which async_statement_variable completed is to be written.
ANSI Compliance
WAIT is a Teradata extension to the ANSI/ISO SQL:2011 standard.
Authorization
None.
Rules
Each async_statement_identifier must be unique (up to 30 bytes) across all active connections and is case-sensitive.
- SQLCODE is set to -650.
- SQLSTATE is set to ‘04000’.
IF you specify this option … | THEN the WAIT statement returns when … |
---|---|
ALL | all asynchronous statements have finished. |
ANY COMPLETION INTO | any of the outstanding asynchronous statements finishes. The asynchronous statement identifier is returned to the host variable async_statement_variable in the INTO clause, and the session identifier is returned to the host variable session_variable. The host variables async_statement_variable and session_variable must be defined as a fixed or varying length character variable with a maximum length of 30 bytes. If the asynchronous statement identifier returned is longer than the length defined for the output host variable, then the exception condition “output host variable is too small to hold returned data” is raised.
|
- Cursor requests specified by the DECLARE CURSOR statement.
- Dynamic requests specified by the PREPARE or EXECUTE IMMEDIATE statements.
Examples 1 - 4
The following RDTIN fields are important for the following WAIT statement examples:
This RDTIN field … | Must … |
---|---|
RdtCType | be set to 470. |
RdtAux1 | be set to one of the following values:
|
RdtVersn | be set to 10. |
RdtExt | be set to ‘Y’, indicating the existence of an extension area, only if an asynchronous statement is specified. |
RdtXTotL | include the size of the RDTXASYN extension area, only if an asynchronous statement is specified. |
Additionally, the RdtX008 (RDTXASYN) structure must be included as one of the extension areas because it communicates the connection name if an asynchronous statement is specified.
Example: Passing Fixed Length Character Values to the Host Variables
This example passes fixed length character values to the host variables declared for the statement and session variables in an ANY COMPLETION INTO clause:
EXEC SQL WAIT ANY COMPLETION INTO :STMTNAMF, :CONNAMEF;
Lines Generated by C Preprocessor2 for Example 1
{ static struct { char sqldaid[8]; SQLInt32 sqldabc; short sqln; short sqld; struct { short sqltype; short sqllen; char *sqldata; char *sqlind; struct { short length; char data[30]; } sqlname; } sqlvar[2]; } Sql_DA019_StructO = {'S','Q','L','D','A',' ',' ',' ',104,2,2,{{460,31,0,0,{0,{' '}}} ,{460,31,0,0,{0,{' '}}}}}; Sql_DA019_StructO.sqlvar[0].sqldata = STMTNAMF; Sql_DA019_StructO.sqlvar[1].sqldata = CONNAMEF; { static struct { SQLInt32 RdtCType; SQLInt16 RdtVersn; SQLInt16 RdtDec; char RdtUserid[8]; SQLInt32 RdtEntty; char *RdtCA; char *RdtDAIn; char *RdtDAOut; char *RdtSql; char *RdtRtCon; SQLInt32 RdtAux1; SQLInt32 RdtAux2; char RdtLCS; char RdtComit; char RdtRelse; char RdtExt; char RdtSepBT; char RdtUCStm; char RdtCmpat; char RdtComp; SQLInt16 RdtXTotL; char RdtXFill[2]; struct { SQLInt16 RdtXLen; SQLInt16 RdtXType; SQLInt32 RdtXCode; } RdtX005; } RDTIN019 = {470,10,0,{' '},0,0,0,0,0,0,2,0,'N','B','N','Y','N','N', ' ','C',12,{' '},{8,5,255}}; RDTIN019.RdtDAOut = (char *)(&Sql_DA019_StructO); RDTIN019.RdtCA = (char *)(&sqlca); RDTIN019.RdtRtCon = SQL_RDTRTCON; TDARDI(&RDTIN019); SQL_RDTRTCON = RDTIN019.RdtRtCon; } }
Example 2
This example passes varying length character values to the host variables declared for the statement and session variables in an ANY COMPLETION INTO clause:
EXEC SQL WAIT ANY COMPLETION INTO :STMTNAMV, :CONNAMEV;
Lines Generated by C Preprocessor2 for Example 2
{ static struct { char sqldaid[8]; SQLInt32 sqldabc; short sqln; short sqld; struct { short sqltype; short sqllen; char *sqldata; char *sqlind; struct { short length; char data[30]; } sqlname; } sqlvar[2]; } Sql_DA020_StructO = {'S','Q','L','D','A',' ',' ',' ',104,2,2,{{448,30,0,0,{0,{' '}}} ,{448,30,0,0,{0,{' '}}}}}; Sql_DA020_StructO.sqlvar[0].sqldata = (char *)(&STMTNAMV); Sql_DA020_StructO.sqlvar[1].sqldata = (char *)(&CONNAMEV); { static struct { SQLInt32 RdtCType; SQLInt16 RdtVersn; SQLInt16 RdtDec; char RdtUserid[8]; SQLInt32 RdtEntty; char *RdtCA; char *RdtDAIn; char *RdtDAOut; char *RdtSql; char *RdtRtCon; SQLInt32 RdtAux1; SQLInt32 RdtAux2; char RdtLCS; char RdtComit; char RdtRelse; char RdtExt; char RdtSepBT; char RdtUCStm; char RdtCmpat; char RdtComp; SQLInt16 RdtXTotL; char RdtXFill[2]; struct { SQLInt16 RdtXLen; SQLInt16 RdtXType; SQLInt32 RdtXCode; } RdtX005; } RDTIN020 = {470,10,0,{' '},0,0,0,0,0,0,2,0,'N','B','N','Y','N','N', ' ','C',12,{' '},{8,5,255}}; RDTIN020.RdtDAOut = (char *)(&Sql_DA020_StructO); RDTIN020.RdtCA = (char *)(&sqlca); RDTIN020.RdtRtCon = SQL_RDTRTCON; TDARDI(&RDTIN020); SQL_RDTRTCON = RDTIN020.RdtRtCon; } }
Example 3
This example uses the ALL COMPLETION option to wait until all active asynchronous statements have completed:
EXEC SQL WAIT ALL COMPLETION;
Lines Generated by C Preprocessor2 for Example 3
{ static struct { SQLInt32 RdtCType; SQLInt16 RdtVersn; SQLInt16 RdtDec; char RdtUserid[8]; SQLInt32 RdtEntty; char *RdtCA; char *RdtDAIn; char *RdtDAOut; char *RdtSql; char *RdtRtCon; SQLInt32 RdtAux1; SQLInt32 RdtAux2; char RdtLCS; char RdtComit; char RdtRelse; char RdtExt; char RdtSepBT; char RdtUCStm; char RdtCmpat; char RdtComp; SQLInt16 RdtXTotL; char RdtXFill[2]; struct { SQLInt16 RdtXLen; SQLInt16 RdtXType; SQLInt32 RdtXCode; } RdtX005; } RDTIN021 = {470,10,0,{' '},0,0,0,0,0,0,1,0,'N','B','N','Y','N','N',' ','C', 12,{' '},{8,5,255}}; RDTIN021.RdtCA = (char *)(&sqlca); RDTIN021.RdtRtCon = SQL_RDTRTCON; TDARDI(&RDTIN021); SQL_RDTRTCON = RDTIN021.RdtRtCon; }
Example 4
This example uses multiple explicit asynchronous statements.
EXEC SQL WAIT ASYNSTMT1, ASYNSTMT2 COMPLETION;
Lines Generated by C Preprocessor2 for Example 4
{ static struct { SQLInt32 RdtCType; SQLInt16 RdtVersn; SQLInt16 RdtDec; char RdtUserid[8]; SQLInt32 RdtEntty; char *RdtCA; char *RdtDAIn; char *RdtDAOut; char *RdtSql; char *RdtRtCon; SQLInt32 RdtAux1; SQLInt32 RdtAux2; char RdtLCS; char RdtComit; char RdtRelse; char RdtExt; char RdtSepBT; char RdtUCStm; char RdtCmpat; char RdtComp; SQLInt16 RdtXTotL; char RdtXFill[2]; struct { SQLInt16 RdtXLen; SQLInt16 RdtXType; SQLInt32 RdtXCode; } RdtX005; struct { SQLInt16 RdtXLen; SQLInt16 RdtXType; SQLInt32 RdtXAsyC; struct { SQLInt16 RdtXAsyL; char RdtXAsyT[30]; } RdtXAsyS[2]; } RdtX008; } RDTIN022 = {470,10,0,{' '},0,0,0,0,0,0,3,0,'N','B','N','Y','N','N',' ','C', 84,{' '},{8,5,255},{72,8,2,{{9,'A','S','Y','N','S','T','M','T', '1',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ', ' ',' ',' ',' ',' ',' '},{9,'A','S','Y','N','S','T','M','T','2', ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ', ' ',' ',' ',' ',' '}}}}; RDTIN022.RdtCA = (char *)(&sqlca); RDTIN022.RdtRtCon = SQL_RDTRTCON; TDARDI(&RDTIN022); SQL_RDTRTCON = RDTIN022.RdtRtCon; }
Examples 1-4
The following examples present WAIT statement SQL text without any client programming code context.
Example: A Basic WAIT Statement
The following example shows a basic WAIT statement. WAIT returns control to the program when the SQL request named req_1 completes.
WAIT req_1 COMPLETION
Example: A More Complicated WAIT Statement
The following example shows a more complicated WAIT statement that specifies two asynchronous statement identifiers. WAIT returns control to the program when both req_1 and req_2 complete.
WAIT req_1, req_2 COMPLETION
Example: Outstanding Asynchronous SQL Statements
The following example waits on all outstanding asynchronous SQL statements and returns control to the program when they have all completed.
WAIT ALL COMPLETION
Example: Completing Outstanding Asynchronous SQL Statements
The following example waits on any outstanding asynchronous SQL request to complete and returns control to the program when any one of them completes, returning the value for the completed asynchronous statement identifier to :requid_var and the value for the ID of the session in which it ran to completion to :sessid_var.
WAIT COMPLETION INTO :reqid_var, :sessid_var
Related Topics
- See ASYNC Statement Modifier for information about how to submit an asynchronous request.
- See TEST for information about how to test the status of an asynchronous request without waiting for it to complete.