Use the statement to EXPAND any SELECT statement into block form.

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.

  • For DATAMOVE, generated pre-steps will be in order: triggers, constraints, indexes; and 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 ;