zOs/TX/QBRIDDL

----------$-[right(userid() sysvar(sysnode) date(s) time(), 60, '-')$]
-- testCase $dsn
-- env      $env      phase $phase
-- dbSys    $dbSys       db $db       creator $creator
------------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
  SET CURRENT SQLID='S100447';
  drop   TABLESPACE $db.aQbRiPar;
  drop   TABLESPACE $db.aQbRiChi;
  commit;
$@ if $phase  >= 1 then $@=/cre/
  CREATE TABLESPACE aQbRiPar
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    segsize 64
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      EBCDIC
    DEFINE YES
    MAXROWS 255;
  CREATE TABLESPACE aQbRiChi
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    segsize 64
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      EBCDIC
    DEFINE YES
    MAXROWS 255;
CREATE TABLE $creator.Tqz91qbriPar
     (id  int not null
     ,txt char(10) not null with default
     ,refPP int
     , constraint prim primary key(id)
     )
    IN $db.aQbRiPar
;
CREATE unique INDEX $creator.iQz91qbriPar
  ON $creator.Tqz91qbriPar
  (id                    asc
  )
  USING STOGROUP GSMS
  ERASE  NO
  GBPCACHE CHANGED
  CLUSTER
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFER NO
  DEFINE YES
  PIECESIZE 2 G
;
alter table $creator.Tqz91qbRIPar
    add constraint refPP foreign key (refPP)
         references $creator.Tqz91qbRIPar on delete cascade
;
CREATE TABLE $creator.Tqz91qbriChi
     (chi  int not null
     ,txt char(10) not null with default
     ,refCP int
 --  , constraint prim primary key(id)
     , constraint refPP foreign key (refCP)
         references $creator.Tqz91qbRIPar on delete cascade
     )
    IN $db.aQbRiCHI
;  $*(
CREATE unique INDEX $creator.iQz91qbriPar
  ON $creator.Tqz91qbriPar
  (id                    asc
  )
  USING STOGROUP GSMS
  ERASE  NO
  GBPCACHE CHANGED
  CLUSTER
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFER NO
  DEFINE YES
  PIECESIZE 2 G
;     $*)
  COMMIT;
  insert into $creator.Tqz91qbriPar (id, txt) values(1, 'root') ;
  insert into $creator.Tqz91qbriPar     values(11, 'chi11', 1) ;
  insert into $creator.Tqz91qbriPar     values(12, 'chi12', 1) ;
  insert into $creator.Tqz91qbriPar     values(121, 'grCh121', 12);
  insert into $creator.Tqz91qbriPar     values(3, 'cycle3', 3);
  insert into $creator.Tqz91qbriChi
       select id, txt, refPP from $creator.Tqz91qbriPar;
$/cre/
---||| end   ddl testcase $mbr env $env phase $phase