zOs/TX/WK403DDL

  SET CURRENT SQLID='S100447';
  SET CURRENT SCHEMA='$creator';
  drop tablespace $db.A403P1;
  drop tablespace $db.A403P2;
  drop tablespace $db.A403P3;
  drop tablespace $db.A403P4;
  drop tablespace $db.WK403P1;
  drop tablespace $db.WK403P2;
  drop tablespace $db.WK403P3;
  drop tablespace $db.WK403P4;
  commit;
$@[
if $env = 'src' then $@[
   $=qty=48
   $=segsz=8
   $=comp=NO
   $=ixbp=BP7
   $=tsbp=BP7
$] else $@[
   $=qty= -1
   $=segsz=64
   $=comp=YES
   $=ixbp=BP1
   $=tsbp=BP2
$]
   $=pha3=- min(3,$phase)
   $=nameL=- 20 + $delta * $pha3
   $=textA=- 30 + $delta * $pha3
   $=text1=- 30 + $delta * $phase
$$ -- env=$env phase=$phase qty=$qty segsz=$segsz comp=$comp
$@do px=1 to  $pha3 $@=/phase/
  $=textL=- if($px=1, $text1, $textA)
$=bp =- if($px<3,BP$px,BP32K)
--          px=$px ixbp=$ixbp tsbp=$tsbp nameL=$nameL textL=$textL
  CREATE TABLESPACE  WK403P$px
    IN $db
    USING STOGROUP GSMS
    PRIQTY $qty SECQTY $qty
    ERASE  NO
    SEGSIZE $segsz
    COMPRESS $comp
    BUFFERPOOL $bp
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    CCSID      EBCDIC
    DEFINE YES
    MAXROWS 123;

CREATE TABLE $creator.Tqz91wk403P$px
   (
     id int not null
   ,  name char($nameL) FOR SBCS DATA not null
   , text char($textL) FOR SBCS DATA not null
   , parent int
   , par2   int
   , wk403TST             TIMESTAMP not null with default
   , primary key (id)
   )
  IN $db.wk403P$px
  with restrict on drop
  AUDIT NONE
  DATA CAPTURE NONE
  CCSID      EBCDIC
  NOT VOLATILE;

  CREATE unique INDEX $creator.Iqz91wk403P${px}1
    ON $creator.Tqz91wk403P$px
     (id asc)    include(name)
    USING STOGROUP GSMS
    PRIQTY $qty SECQTY $qty
    ERASE  NO
    BUFFERPOOL $bp
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    CLUSTER
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G;
  CREATE unique INDEX $creator.Iqz91wk403P${px}p1
    ON $creator.Tqz91wk403P$px
     (id asc)    include(name)
    USING STOGROUP GSMS
    PRIQTY $qty SECQTY $qty
    ERASE  NO
    BUFFERPOOL $bp
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    not CLUSTER
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 1 G;
$*(
  CREATE unique INDEX $creator.Iqz91wk403P${px}p2
    ON $creator.Tqz91wk403P$px
     (id asc)    include(name)
    USING STOGROUP GSMS
    PRIQTY $qty SECQTY $qty
    ERASE  NO
    BUFFERPOOL $bp
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    not CLUSTER
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G;
  CREATE unique INDEX $creator.Iqz91wk403P${px}p4
    ON $creator.Tqz91wk403P$px
     (id asc)    include(name)
    USING STOGROUP GSMS
    PRIQTY $qty SECQTY $qty
    ERASE  NO
    BUFFERPOOL $bp
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    not CLUSTER
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 4 G;
$*)
$@[
if $phase >= 2 then $@=[
  CREATE INDEX $creator.Iqz91wk403P${px}Changing
    ON $creator.Tqz91wk403P$px
     ($-[word(parent par2 text, $pha3//3 + 1)$] asc)
    USING STOGROUP GSMS
    PRIQTY $qty SECQTY $qty
    ERASE  NO
    BUFFERPOOL $ixbp
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    NOT CLUSTER
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G;
$]
if $phase >= 3 then $@=[
  CREATE INDEX $creator.Iqz91wk403P${px}3
    ON $creator.Tqz91wk403P$px
     (name asc)
    USING STOGROUP GSMS
    PRIQTY $qty SECQTY $qty
    ERASE  NO
    BUFFERPOOL $ixbp
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    NOT CLUSTER
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G;
$]
$]
$@do py=1 to $px $@=[
 alter table $creator.tqz91wk403P$px add constraint for${px}to$py
       foreign key(parent)references $creator.tqz91wk403P$py
       on delete cascade ;
$]
 alter table $creator.tqz91wk403P$px add constraint for${px}changing
       foreign key(par2)references
                  $creator.tqz91wk403P$-[$pha3 // $px + 1$]
       on delete cascade ;
$@do py=1 to $px $@=[
  CREATE view  $creator.vqz91wk403ComC${px}P$py as
      select c.*, p.name paCh${px}Pa${py}
          from  $creator.tqz91wk403P$px c
          left join  $creator.tqz91wk403P$py p
          on   c.parent = p.id ;
$]
  CREATE view  $creator.vqz91wk403P${px}TXTCHANGING as
      select c.*, p.name paCh${px}Ph$pha3
          from  $creator.tqz91wk403P$px c
          left join  $creator.tqz91wk403P$px p
          on   c.parent = p.id ;
  CREATE view  $creator.vqz91wk403P${px}changing as
      select c.*, p.name paCh${px}
          from  $creator.tqz91wk403P$px c
          left join  $creator.tqz91wk403P$-[$pha3 // $px + 1$] p
          on   c.parent = p.id ;
insert into $creator.Tqz91wk403P$px (id,name,text) values
  (1, 'walter$px', 'waPha$pha3 tb$px id1');
insert into $creator.Tqz91wk403P$px (id,name,text, parent) values
  (2, 'gregor$px', 'grPha$pha3 tb$px id2', 1);
insert into $creator.Tqz91wk403P$px (id,name,text, parent) values
  (3, 'david$px', 'daPha$pha3 tb$px id3', 1);
insert into $creator.Tqz91wk403P$px (id,name,text, parent) values
  (4, 'hori$px', 'hoPha$pha3 tb$px id4', 1);
insert into $creator.Tqz91wk403P$px (id,name,text, parent) values
  (5, 'gabriel$px', 'gaPha$pha3 tb$px id5', 4);
update $creator.Tqz91wk403P$px set par2 = parent ;
$/phase/
if $phase = 3 then $@=[
    create view $creator.vqz91wk403Joining as
        select p1.* from $creator.Tqz91wk403P1 p1
        ;
$] else if $phase > 3 then $@=[
    create view $creator.vqz91wk403Joining as
        select c1.ibmReqD, c2.dbid, p1.*
        from sysibm.sysDummy1 c1
        inner join sysibm.sysDatabase c2 on c2.name = c1.ibmReqD
        inner join sysibm.sysTables   c3 on c3.dbName = c2.name
        inner join $creator.Tqz91wk403P1 p1 on p1.Name = c3.name
    ;
$]
$]
commit;