BEGIN ASYNC OPERATION Statement

Marks the beginning of a block of statements intended for asynchronous, parallel execution.

Valid in: SPD Server
Explicit SQL pass-through facility
Requirement: Must be used in conjunction with the END ASYNC OPERATION Statement.Optionally used with the LIBREF Statement.
Syntax
Without Arguments
Details
Examples
Example 1: Creating Tables and Indexes in Parallel
Example 2: Using SQL Options in an ASYNC Block Statement
See Also

Syntax

BEGIN ASYNC OPERATION

Without Arguments

execute(begin async operation) by sasspds;

Details

You can maximize the performance of certain SQL statements by specifying to execute them asynchronously, in parallel. SPD Server provides the BEGIN ASYNC OPERATION and END ASYNC OPERATION statements to delimit the block of statements that are intended for asynchronous, parallel execution.
Not all SQL statements are candidates for submission within the same ASYNC block. SPD Server initiates thread execution according to the order of the statements in the block. However, there is no way to guarantee that a statement will finish before another statement executes. Therefore, you should avoid submitting statements that depend on another statement to complete within the same block. Examples of statements that should not be executed within the same block are CREATE TABLE and CREATE INDEX. There is no guarantee that the CREATE TABLE statement will complete before the CREATE INDEX statement begins.
The block approach is useful for operations such as creating multiple tables in parallel and for creating multiple indexes on one or more existing tables in parallel.
If you plan to submit statements to more than one domain within a block, then you must re-create the connection made with the CONNECT TO statement within the block using the LIBREF statement. You must also issue a LIBREF statement to connect to the second domain within the block. The connection made with the CONNECT TO statement does not extend to the block. Conversely, any LIBREF statement that you issue inside the ASYNC block does not extend outside of the ASYNC block. To function correctly, a LIBREF statement that is specified inside a block must precede the first SQL statement that references it.For an example of how the LIBREF statement is used, see Using SQL Options in an ASYNC Block Statement.
If you plan to specify an SQL RESET statement option for any statement in a block, the option must be set globally for all EXECUTE statements in the block. This is done by issuing the EXECUTE statement that resets the option before specifying the BEGIN ASYNC OPERATION statement.

Examples

Example 1: Creating Tables and Indexes in Parallel

This example shows how to create multiple tables and multiple indexes in parallel. The CREATE TABLE statements and the CREATE INDEX statements are submitted within separate asynchronous blocks. The END ASYNC statement in the first block serves as a synchronization point to ensure that all the tables are created before the second ASYNC statement block begins. 
proc sql;
            connect to sasspds
              (dbq="path1"
               server=host.port
               user='siteusr1'
               password='mypasswd');

            execute(begin async operation)
               by sasspds;

            execute(create table state_al as
              select *
              from allstates
              where state='AL')
              by sasspds;

            execute(create table state_az as
              select *
              from allstates
              where state='AZ')
              by sasspds;
              
            execute(create table state_wy as
               select *
               from allstates
               where state='WY')
               by sasspds;

            execute(end async operation)
              by sasspds;

      /*                                                */
      /* Create indexes in a second ASYNC block  */
      /*                                                */

            execute(begin async operation)
              by sasspds;

            execute(create index county on
                    state_al(county))
              by sasspds;

            execute(create index county on
                    state_az(county))
              by sasspds;
              ...

            execute(create index county on
                    state_wy(county))
              by sasspds;

            execute(end async operation)
              by sasspds;

           disconnect from sasspds;
          quit;

Example 2: Using SQL Options in an ASYNC Block Statement

This example shows how to submit SQL planner options in an asynchronous block. You must set the options globally for all EXECUTE statements in the ASYNC block. The options must be set before the BEGIN ASYNC OPERATION statement.
This code sample also shows how to use the LIBREF statement. When referencing more than one domain in an ASYNC block, you must specify a LIBREF statement for each of the domains in the asynchronous block. Note that the LIBGEN= option is set in the LIBNAME statement as well. 
libname path1 sasspds ... libgen=yes;
	libname path2 sasspds ... libgen=yes;

proc sql;
    connect to sasspds
       (dbq='path1'
        host='hostname'
        service='spdsname'
        user='siteusr1')
        password='mypasswd';

    execute(reset noexec _method)
      by sasspds;

    execute(begin async operation)
      by sasspds;

    execute(libref path1
      engopt='dbq="path1"
              host='hostname'
              service='spdsname'
              user='siteusr1'
              password='mypasswd')
              by sasspds;

     execute(libref path2
       engopt='dbq="path2"
               host='hostname'
               service='spdsname'
               user='siteusr1'
               password='mypasswd')
               by sasspds;

     execute(create table path1.southeast as
        select a.customer_id,
               a.region,
               b.sales
        from   path1.customer a,
               path2.orders b
        where  a.customer_id = b.customer_id
        and    a.region='SE')
        by sasspds;

      execute(create table path1.northeast as
         select a.customer_id,
                a.region,
                b.sales
         from   path1.customer a,
                path2.orders b
         where  a.customer_id = b.customer_id
         and    a.region='NE')
         by sasspds;

      execute(end async operation)
         by sasspds;

      disconnect from sasspds;
  quit;

See Also

Statements:
END ASYNC OPERATION Statement
LIBREF Statement
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: February 8, 2017