EXPAND any SELECT statement into block form to see how the execution of the block will be changed by all the parameters.

Syntax

EXPAND <statement> ;

Arguments

  • statement - any valid SQL statement.

Additional Information

  • Any block without child statements will not be generated.

  • The only currently envisaged commands with pre-, post- or validate steps are DATAMOVE and BEGIN…END blocks.

  • DATAMOVE

    • generated pre-steps will be in order: triggers, constraints, indexes;

    • generated post-steps will be in order: indexes, constraints, triggers.

  • For BEGIN…END blocks, the step actions of all child statements will be used.

  • Ideally, EXPAND will be identical to EXPAND .

Examples

DATAMOVE a.b.c.d TO w.x.y.x;

Expands to:


BEGIN ;
  BEGIN AS 'Pre-step' ;
    ALTER TABLE w.x.y.x DISABLE CONSTRAINT ... ;
  END ;
  BEGIN AS 'Body';
    INSERT INTO w.x.y.x
    SELECT *
      FROM a.b.c.d ;
  END ;
  BEGIN AS 'Post-step' ;
    ALTER TABLE w.x.y.x ENABLE CONSTRAINT ... ;
  END ;
  BEGIN AS 'Validate' ;
    INSERT INTO CIRRO.cirrosys.cirrosys.datamove_log
    SELECT CURRENT_UTC_TIMESTAMP dt_stamp, 'a.b.c.d' source_tablename, 'w.x.y.x' target_tablename,
           CASE WHEN b.chk = a.chk = 0 THEN 'info' ELSE 'error' END status,
           a.chk source_chk, b.chk target_chk, b.chk - a.chk difference
     FROM (SELECT count(*) cnt FROM CIRRO.cirrosys.cirrosys.excel_vb LIMIT 1) as x
     CROSS JOIN (SELECT CHECKSUM(*) chk FROM a.b.c.d) a
     CROSS JOIN (SELECT CHECKSUM(*) chk FROM w.x.y.x) b ;
  END ;
END ;

DELIMITER @

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