BEGIN PARALLEL WORK statement

Saves time when executing a list of CREATE INDEX or LOAD TABLE statements on a computer with multiple logical processors.

Syntax

BEGIN PARALLEL WORK
statement-list
END PARALLEL WORK

statement-list: 
  list of CREATE INDEX statements
| list of LOAD TABLE statements

Parameters

CREATE INDEX statement See the CREATE INDEX statement for information and restrictions about running the CREATE INDEX statement inside the BEGIN PARALLEL WORK statement. The CREATE TEXT INDEX statement is not supported.
LOAD TABLE statement
See the LOAD TABLE statement for information and restrictions about running the LOAD TABLE statement inside the BEGIN PARALLEL WORK statement.

Remarks

The BEGIN PARALLEL WORK statement can improve performance by executing in parallel a list of CREATE INDEX or a list of LOAD TABLE statements.

The number of statements that can execute at the same time is limited by:

the number of available logical processors on the computer that the database server runs on.
the settings of the -gtc and -gta database server options and the Processor Affinity option of the sa_server_option system procedure.
the setting of the max_parallel_statements option.

The BEGIN PARALLEL WORK statement executes atomically. If one statement inside the BEGIN PARALLEL WORK statement fails, then the entire statement rolls back. The BEGIN PARALLEL WORK statement, including the statements listed inside of it, is logged to the transaction log.

Granularity is at the table level.

Privileges

You must have the privileges necessary to execute the statements in the list.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard Not in the standard. Vendor extension.

Example

The following example creates two indexes using the BEGIN PARALLEL WORK statement:

BEGIN PARALLEL WORK
    CREATE INDEX L_SHIPDATE_IDX
            ON LINEITEM(l_shipdate);
    CREATE INDEX O_ORDERDATE_IDX
            ON ORDERS(o_orderdate);
END PARALLEL WORK;

The following example loads three tables into the database by using the BEGIN PARALLEL WORK statement:

BEGIN PARALLEL WORK
    LOAD TABLE dba.Part
        FROM 'D:\\data\\part.tbl'
        FORMAT 'ASCII'
        QUOTES OFF ESCAPES ON STRIP OFF HEXADECIMAL OFF
        DELIMITED BY '|'
        ORDER OFF;
    LOAD TABLE dba.Supplier
        FROM 'D:\\data\\supplier.tbl'
        FORMAT 'ASCII'
        QUOTES OFF ESCAPES ON STRIP OFF HEXADECIMAL OFF
        DELIMITED BY '|'
        ORDER OFF;
    LOAD TABLE dba.Partsupp
        FROM 'D:\\data\\partsupp.tbl'
        FORMAT 'ASCII'
        QUOTES OFF ESCAPES ON STRIP OFF HEXADECIMAL OFF
        DELIMITED BY '|'
        ORDER OFF;
END PARALLEL WORK;