WAIT | Teradata Vantage - WAIT - Advanced SQL Engine - Teradata Database

Pauses execution of the invoking program and waits for the completion of one or more asynchronous SQL statements.

ANSI Compliance

WAIT is a Teradata extension to the ANSI/ISO SQL:2011 standard.

Required Privileges

None.

Invocation

Executable.

Embedded SQL only.

Syntax

WAIT {
  { statement_spec [,...] | ALL } COMPLETION |

    ANY COMPLETION INTO [:] statement_variable [:] session_variable
}

Syntax Elements

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.

Each asynchronous statement identifier can be no more than 30 characters in length and must be unique across all active connections.

async_statement_identifier_variable_name

The name of a host variable that contains an asynchronous statement identifier to be passed to the WAIT statement.

The identifier must be a fixed or varying length character string no more than 30 characters long.

This permits an application to supply multiple values of an async_statement_identifier to WAIT by means of a host variable.

The preceding colon is not mandatory, but conforms to good programming practices.

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.

Usage Notes

Each async_statement_identifier must be unique (up to 30 bytes) across all active connections and is case-sensitive.

Examples 1 - 4

The following RDTIN fields are important for the following WAIT statement examples:

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 Information

• 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.