ASYNC Statement Modifier | Teradata Vantage - ASYNC Statement Modifier - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Initiates the asynchronous execution of an SQL statement.

Invocation

Executable.

Embedded SQL only.

Syntax

ASYNC (
  { async_statement_identifier | :async_statement_identifier_variable_name }
) async_SQL_statement
async_statement_identifier
A case-sensitive, application-supplied identifier assigned to the asynchronously executed SQL statement so it 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 mandatory.
async_SQL_statement
The executable SQL statement.
async_SQL_statement can be passed to ASYNC indirectly through dynamic SQL using a host variable.

ANSI Compliance

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

Authorization

None.

Rules

Only one asynchronous statement can execute 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).

ASYNC cannot be used with any of the following embedded SQL declarative statements:
  • BEGIN DECLARE SECTION
  • DECLARE CURSOR
  • DECLARE STATEMENT
  • DECLARE TABLE
  • END DECLARE SECTION
  • INCLUDE
  • INCLUDE SQLCA
  • INCLUDE SQLDA
  • SET BUFFERSIZE
  • WHENEVER
ASYNC cannot be used with any of the following executable embedded SQL statement:
  • ABORT
  • BEGIN TRANSACTION
  • COMMIT
  • CONNECT
  • DATABASE
  • DESCRIBE
  • END TRANSACTION
  • FETCH
  • GET CRASH
  • LOGOFF
  • LOGON
  • POSITION
  • REWIND
  • SET BUFFERSIZE
  • SET CHARSET
  • SET CRASH

Example: Using the ASYNC Statement Modifier

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

The following RDTIN fields are important for using ASYNC:
This RDTIN field … Must …
RdtVersn be set to 10.
RdtExt be set to ‘Y’ to indicate the existence of an extension area.
RdtXTotL include the size of the RDTXASYN extension area.

The RdtX008 (RDTXASYN) structure must be included as one of the extension areas because it communicates the connection name.

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 Execute a Macro

This example submits an asynchronous request to execute 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 Topics

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 for information about how to prepare and execute an SQL statement dynamically.