zOs/TX/QT003DDL

------------$-[right(userid() sysvar(sysnode) date(s) time(), 60, '-')$]
-- testCase $dsn
-- env      $env      phase $phase
-- subsys   $dbSys     db $db       creator $creator
-----------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
SET CURRENT SQLID='S100447';
SET CURRENT SCHEMA = $creator;
-- alter  TABLE $creator.Tqz91qt003A drop RESTRICT ON DROP ;
-- alter  TABLE $creator.Tqz91qt003H drop RESTRICT ON DROP ;
-- alter  TABLE $creator.Tqz91qt003A drop versioning;
drop tablespace $db.aqt003A;
drop tablespace $db.aqt003H;
commit;
$@ say versioning $vers
$@[
if $phase >= 1 then $@=[
$= pgmLen =- 4 * (2 + ($phase = 4))
    CREATE TABLESPACE aqt003A
           IN $db
           BUFFERPOOL BP2
           CLOSE NO
           LOCKMAX SYSTEM
           DEFINE YES
           LOGGED
           LOCKSIZE ANY
           MAXROWS 255
           COMPRESS YES
           CCSID EBCDIC
           SEGSIZE 64
;
$]
if $phase >= 2 then $@=[
    CREATE TABLESPACE aqt003H
           IN $db
           BUFFERPOOL BP2
           CLOSE NO
           LOCKMAX SYSTEM
           DEFINE YES
           LOGGED
           LOCKSIZE ANY
           MAXROWS 255
           COMPRESS YES
           CCSID EBCDIC
           SEGSIZE 64
;
$]
if $phase >= 1 then $@=[
    CREATE TABLE $creator.Tqz91qt003A
  ( appl       CHAR(4) NOT NULL default '%'
  , pgm        CHAR($pgmLen) NOT NULL default '%'
  , PRIO       smallint NOT NULL
  , rdl        char(10) NOT NULL default 'default'
  , cq         char(10) NOT NULL default 'default'
$@ if $phase >= 5 then
  , c2         char(10) NOT NULL default 'c zwei'
  , ov         char(10) NOT NULL default 'default'
  , installBegin date NOT NULL WITH DEFAULT
  , installEnd date NOT NULL WITH DEFAULT '31.12.9999'
  , rowBegin not null generated always as row begin
  , rowEnd   not null generated always as row end
  , rowtrans not null generated always as transaction start id
  , period business_time (installBegin, installEnd)
  , period system_time (rowBegin, rowEnd)
  , primary key (appl, pgm, business_time without overlaps)
--, primary key (appl, pgm, prio)
  )
 in $db.aqt003A
  with RESTRICT ON DROP
;
CREATE UNIQUE INDEX $creator.Iqz91qt003A
  ON $creator.tqz91qt003A
   (appl, pgm, business_time without overlaps)
   include (prio, rdl, cq, ov)
  USING STOGROUP GSMS
  ERASE  NO
  GBPCACHE CHANGED
  not CLUSTER
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFINE YES
;
CREATE UNIQUE INDEX $creator.Iqz91qt003B
  ON $creator.tqz91qt003A
   (appl, pgm, prio)
  USING STOGROUP GSMS
  ERASE  NO
  GBPCACHE CHANGED
  not CLUSTER
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFINE YES
;
$]
if $phase >= 2 then $@=[
CREATE TABLE $creator.tqz91qt003H
        like $creator.tqz91qt003A
  in $db.aqt003H
    AUDIT NONE
    DATA CAPTURE NONE
    NOT VOLATILE
  with RESTRICT ON DROP
;
$@[ if $vers then $@=[
alter table $creator.tqz91qt003A add versioning
      use history table $creator.tqz91qt003H
;
$]  $]
commit;
insert into $creator.tqz91qt003H (appL, prio,rowBegin,rowEnd,rowTrans)
     values ('his0', 0, current timestamp - 113 years
                       , current timestamp -  112 year
                       , current timestamp -  111 year); COMMIT;
insert into $creator.tqz91qt003A (appL, prio) values ('app1',1);COMMIT;
insert into $creator.tqz91qt003A (appL, prio) values ('app2',2);COMMIT;
update      $creator.tqz91qt003A set pgm = 'pgm2';          COMMIT;
delete from $creator.tqz91qt003A where appl = 'app1';       COMMIT;
select * from $creator.tqz91qt003H
;
$]
$]