zOs/TX/WK401DDL

SET CURRENT SQLID='S100447';
SET CURRENT SCHEMA='$creator';
drop tablespace $db.A401A;
drop tablespace $db.awk401A;
commit;

$@ if $phase > 0 then $@=/phaseGtr0/
CREATE TABLESPACE  aWk401a
    IN $db
    USING STOGROUP GSMS
    PRIQTY 48 SECQTY 100
    ERASE  NO
$@ if $phase <> 41 then $@=[
    FREEPAGE 0 PCTFREE 10
    COMPRESS YES
    MAXROWS 255
$] $@ else $@=[
    FREEPAGE 41 PCTFREE 41
    COMPRESS no
    MAXROWS 41
$]
    GBPCACHE CHANGED
    TRACKMOD YES
    SEGSIZE $-[if($phase = 2, 8, 64)$]
    $-[if($phase =41, MAXPARTITIONS 5)$]
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    CCSID      EBCDIC
    DEFINE YES
;
create TABLE $creator.Tqz91wk401A
  (
    $@[
        if $phase = 1 then $@=[
    wk401CH20         VARCHAR(20) FOR SBCS DATA not null with default
        $] else if $phase = 2 then $@=[
    wk401CH20         VARCHAR(12) FOR SBCS DATA not null with default
        $] else if $phase >= 3 then $@=[
    wk401CH20         VARCHAR(22) FOR SBCS DATA not null with default
        $]
    $]
  , wk401CH5             CHAR(5) FOR SBCS DATA not null with default
  , wk401CH2             CHAR(2) FOR SBCS DATA not null with default
  , wk401TST             TIMESTAMP not null with default
$@ if $phase = 22 then $@=[
  , wk401CHApp           CHAR(5) FOR SBCS DATA not null with default
 $]
$@ if $phase = 23 then $@=[
  , wk401CHlength30lll20abcdefgh30
             CHAR(5) FOR SBCS DATA not null with default
 $]
$@ if $phase = 42 then $@=[
  , primary key (wk401CH20)
 $]
  )
    IN $db.aWk401a
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE
;
    $@[
        if $phase = 1 then $@=[
CREATE UNIQUE INDEX $creator.Iqz91wk401A0
        $] else $@=[
CREATE UNIQUE INDEX $creator.Iqz91wk401A1
        $]
    $]
    ON $creator.Tqz91wk401A
      ( wk401CH20             ASC
$@ if $phase = 31 then $@=[
      , wk401CH5              ASC
$]
      )
$@ if $phase = 32 then $@=[
      include (wk401Tst )
$]
    USING STOGROUP GSMS
    PRIQTY 12 SECQTY 12
    ERASE  NO
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    NOT CLUSTER
    BUFFERPOOL BP1
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G
;
$@ if $phase = 21 then $@=[
CREATE INDEX $creator.Iqz91wk401A2
    ON $creator.Tqz91wk401A
      ( wk401CH2              ASC)
    USING STOGROUP GSMS
    PRIQTY 12 SECQTY 12
    ERASE  NO
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    NOT CLUSTER
    BUFFERPOOL BP1
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G
;
$]
$@      if $phase  = 4 then $@=[
ALTER TABLE $creator.Tqz91wk401A
      ALTER WK401TST SET DATA TYPE TIMESTAMP(10);
    commit;
     $]
$@      if $phase >= 3 then $@=[
CREATE view  $creator.vqz91wk401A1 as
    select * from  $creator.tqz91wk401A
;
CREATE view  $creator.vqz91wk401A2 as
    select * from  $creator.vqz91wk401A1
;
$]
$@      if $phase = 24 then $@=[
CREATE view  $creator.vqz91wk401A24 as
    select 'wk401CH20=' || wk401CH20 || ', wk401CH5=' || wk401CH5
         || ' longStringConstant         30 longer 40 longer 50 longer 6
0 longer 70 longer 80 longer 90 longer100 longer110 longer120 longer130
longer140 longer150 longer160 longer170' || ', wk401CH2='||wk401CH2 ll
from  $creator.vqz91wk401A2
;
$]
commit
;
insert into $creator.Tqz91wk401A
       (wk401ch20,  wk401ch5, wk401ch2, wk401tst ) values
       ('erstens', 'e'     ,  '1'     , current timestamp          );
insert into $creator.Tqz91wk401A
       (wk401ch20,  wk401ch5, wk401ch2, wk401tst ) values
       ('zweitens', 'zw',     '2'     , current timestamp - 2 minute);
insert into $creator.Tqz91wk401A
       (wk401ch20,  wk401ch5, wk401ch2, wk401tst ) values
       ('drittens', 'dre',    '3',      current timestamp - 3 hours);
insert into $creator.Tqz91wk401A
       (wk401ch20,  wk401ch5, wk401ch2, wk401tst ) values
       ('viertens', 'vier',   '4',      current timestamp - 4 days);
insert into $creator.Tqz91wk401A
       (wk401ch20,  wk401ch5, wk401ch2, wk401tst ) values
       ('fuenftens', 'fuenf', '5'     , current timestamp - 5 months );
commit
;
$/phaseGtr0/