Begin---End is

Syntax

BEGIN
  [AS 'name']
  [PARALLEL <n> ]
  [GROUPED]
  [ON ERROR ( CANCEL | IGNORE ) ]
  [WITH PASSTHROUGH '<remotesystem>'];

<main statements>

[FINALLY;]
  <Finally statements>
END;

Arguments

  • name - a string literal name for the block of queries used to identify the statement in Session logs. Unnamed blocks are given a unique name.

  • PARALLEL - The number of threads the statement can run concurrently within.

  • GROUPED - Move relevant pre and post operations (such as DROP TABLES) into the common area to be executed prior to or after MAIN script operations.

    • All pre, body and post step actions contributed by child statements are grouped into their own blocks, replacing the <main statements> and have the same PARALLEL and ON ERROR options as the parent.
  • ON ERROR - Script behaviour if errors occur which inherits from the parent BEGIN blocks if not specified.

    • Ignore - Error recorded to log, execution continues.

    • Cancel - Script execution stops and is logged. FINALLY statements are executed but also stop on error and are logged.

  • WITH PASSTHROUGH - Execute code block on the native database rather than by the Cirro SQL engine.

  • main statements - Any valid SQL statements, including BEGIN … END blocks.

  • FINALLY - Additional statements for the script run regardless of errors in body. If an error occurs, execution stops.

Default values

Argument Default Value
PARALLEL 1
ON ERROR IGNORE

Examples

Execute Datacopy statement in four threads, with cancel on the first error with CREATE of a new table and no constraints. The GROUPED argument breaks the script into pre, body and post statements, which can be viewed by use of the EXPAND statement

BEGIN
  PARALLEL 4
  GROUPED
  ON ERROR CANCEL;
  DATACOPY
  "dataxchange"."postgres"."public"."doctest"
   to "dataxchange"."postgres"."public"."doctest2"
  WITH CREATE NOCONSTRAINTS;
END