ASYNC Statement Modifier | VantageCloud Lake - ASYNC Statement Modifier - Teradata Vantage

Initiates the asynchronous execution of an SQL statement.

ANSI Compliance

The ASYNC clause is a Teradata extension to the ANSI/ISO SQL:2011 standard.

Required Privileges

None.

Invocation

Executable.

Embedded SQL only.

Syntax

ASYNC (
  { async_statement_identifier | :async_statement_identifier_variable_name }
) async_SQL_statement

Syntax Elements

async_statement_identifier

A case-sensitive, application-supplied identifier assigned to the asynchronously run SQL statement so the statement can be accessed and its status can be tested and reported by the TEST and WAIT statements.

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 supplies multiple async_statement_identifier strings.

Using a host variable permits a single ASYNC statement to support multiple asynchronous sessions simultaneously.

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

The preceding colon is required.

async_SQL_statement

The executable SQL statement.

async_SQL_statement can be passed to ASYNC indirectly through dynamic SQL using a host variable.

Usage Notes

Only one asynchronous statement can run per connection.

Before another statement can be processed asynchronously on a connection, the previous asynchronous statement must have completed; otherwise, a runtime error occurs.

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

ASYNC is not valid within cursor requests specified by the DECLARE CURSOR statement.

ASYNC is not valid within dynamic requests specified by the PREPARE or EXECUTE IMMEDIATE statements.

You can use dynamic SQL to pass an asynchronous SQL statement to ASYNC indirectly through a host variable (see Example: Using dynamic SQL to Pass the Asynchronous SQL Statement).

Example: Using the ASYNC Statement Modifier

The following example shows how to use the ASYNC statement modifier.

The RdtX008 (RDTXASYN) structure communicates the connection name, and therefore must be included as one of the extension areas.

EXEC SQL ASYNC (INSEMP)
         INSERT EMPLOYEE VALUES (2010,1003,2216,8201,'JONES',
           'FREDDY','20/06/14','19/05/25',200000);

Lines Generated by C Preprocessor2 for Example 1

{
static struct {
       SQLInt32 StrLen;
       char     Str[93];
       } Sql_Stmt016 =
       {93,{' '}};
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;
              } RdtX008;
       } RDTIN016 =
       {300,10,0,{' '},0,0,0,0,0,0,0,0,'N','B','N','Y','N','N',' ','C',
       52,{' '},{8,5,255},{40,8,1,{6,'I','N','S','E','M','P',' ',' ',
       ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',
       ' ',' ',' ',' ',' ',' '}}};
memcpy(Sql_Stmt016.Str,"INSERT EMPLOYEE VALUES ( 2010,1003,2216,8201,'\
JONES', 'FREDDY','20/06/14','19/05/25',200000 )",93);
RDTIN016.RdtSql = (char *)(&Sql_Stmt016);
RDTIN016.RdtCA = (char *)(&sqlca);
RDTIN016.RdtRtCon = SQL_RDTRTCON;
TDARDI(&RDTIN016);
SQL_RDTRTCON = RDTIN016.RdtRtCon;
  }

Examples

These examples present ASYNC statement modifier SQL text without any client programming code context.

Example: Asynchronous Requests to Open a Cursor

This example submits an asynchronous request to open a cursor.

ASYNC (request_1) OPEN cursor_1

Example: Asynchronous Requests to Perform a Searched Update

This example submits an asynchronous request to perform a searched update of a table.

ASYNC (request_1) UPDATE table_1
SET a = :a

Example: Asynchronous Requests to Run a Macro

This example submits an asynchronous request to run a macro.

ASYNC (request_1) EXEC macro_1

Example: Using dynamic SQL to Pass the Asynchronous SQL Statement

This example uses dynamic SQL to pass the asynchronous SQL statement to ASYNC through a host variable.

strcpy (SQL_STATEMENT.arr,"DELETE FROM TABLE1 WHERE FIELD1 = ?");
       SQL_STATEMENT.len = strlen (SQL_STATEMENT.arr);
    
EXEC SQL PREPARE s1 FROM :sql_statement;
 
EXEC SQL ASYNC (stmt01) EXECUTE s1 USING :var1;

Related Information

See TEST for more information about testing the completion status of an asynchronous request.

See WAIT for more information about waiting for an asynchronous request to complete.

See Dynamic SQL Statement Syntax for more information about dynamic SQL, descriptions of EXECUTE (Dynamic SQL Form) and PREPARE (Dynamic) for information about how to prepare and run an SQL statement dynamically.