zOs/TX/WKA01DDL

set current sqlid = 'S100447';
drop   TABLE $creator.twka01Act;
drop   TABLE $creator.twka01Hist;
drop   TABLESPACE $db.aa01h;
drop   TABLESPACE $db.aa01a;
commit;
$@[
if $phase >= 1 then $@=[
CREATE TABLESPACE aa01a
  IN $db
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 0 PCTFREE 10
  GBPCACHE CHANGED
  TRACKMOD YES
  SEGSIZE 64
  BUFFERPOOL BP2
  LOCKSIZE ANY
  LOCKMAX SYSTEM
  CLOSE YES
  COMPRESS YES
  CCSID      UNICODE
  DEFINE YES
  MAXROWS 255
;
CREATE TABLESPACE aa01h
  IN $db
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 0 PCTFREE 10
  GBPCACHE CHANGED
  TRACKMOD YES
  SEGSIZE 64
  BUFFERPOOL BP2
  LOCKSIZE ANY
  LOCKMAX SYSTEM
  CLOSE YES
  COMPRESS YES
  CCSID      UNICODE
  DEFINE YES
  MAXROWS 255
;
commit;
CREATE TABLE $creator.twka01Act
  (
      id         char(3) NOT NULL
    , txt        char(20) NOT NULL
    , rowChg timestamp not null generated always
        for each row on update as row change timestamp
    , busTimFr   date NOT NULL WITH DEFAULT
    , busTimTo   date NOT NULL WITH DEFAULT '31.12.9999'
$]
if $phase >= 2 then $@=[
    , period business_time (busTimFr, busTimTo)
    , unique (id, business_time without overlaps)
$]
if $phase >= 3 then $@=[
    , 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
$]
if $phase >= 4 then $@=[
    , period system_time (rowBegin, rowEnd)
$]
if $phase >= 1 then $@=[
  ) in $db.aa01a
;
$]
if $phase >= 2 then $@=[
CREATE UNIQUE INDEX $creator.iwka01Act1
  ON $creator.twka01Act
   (id, business_time without overlaps)
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 0 PCTFREE 10
  GBPCACHE CHANGED
  NOT CLUSTER
  COMPRESS NO
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFER NO
  DEFINE YES
;
$]
if $phase >= 1 then $@=[
CREATE TABLE $creator.twka01Hist like $creator.twka01Act
    in $db.aa01h
;
$]
if $phase >= 6 then $@=[
alter table $creator.twka01Act add versioning
      use history table $creator.twka01Hist
;
$]
if $phase >= 1 then $@=[
commit;
insert into $creator.twka01Act (id, txt, busTimFr)
                 values   ('a', ' von 1.1', '01.01.2013');
update $creator.twka01Act set busTimTo = '01.02.2013',
                              txt = 'alter to 1.2'
                          where id = 'a';
insert into $creator.twka01Act (id, txt, busTimFr)
                 values   ('a', 'von 1.2', '01.02.2013');
select * from $creator.twka01Act;
select * from $creator.twka01Hist;
select * from $creator.twka01Act
      for system_time from '1900-01-01-00.00.00'
                      to   '9999-12-31-24.00.00' ;
insert into $creator.twka01Act (id, txt, busTimFr)
                 values   ('b', ' von 1.1', '01.01.2013');
commit;
update $creator.twka01Act set busTimTo = '01.02.2013',
                              txt = 'alter to 1.2'
                          where id = 'b';
commit;
insert into $creator.twka01Act (id, txt, busTimFr)
                 values   ('b', 'von 1.2', '01.02.2013');
commit;
select * from $creator.twka01Act;
select * from $creator.twka01Act
      for system_time from '1900-01-01-00.00.00'
                      to   '9999-12-31-24.00.00' ;
$*(
xnsert into $creator.tBiPaCo2 (colls, seq, txt, qua, busTimFr)
                 values   ('defIns', 1, 'AV','', '01.02.2011');
insert into $creator.tBiPaCo2 (colls, seq, txt, qua, busTimFr)
                 values   ('defIns', 1, 'AV','', '01.02.2011');
select * from $creator.tBiPaCo2;
select * from $creator.tBiPaCoH;
select * from $creator.tBiPaCo2
       for business_time as of '30.05.1997' ;
select * from $creator.tBiPaCo2
       for business_time as of '31.12.1999' ;
select * from $creator.tBiPaCo2
       for business_time as of '01.01.2000' ;
select * from $creator.tBiPaCo2
       for business_time as of '31.01.2011' ;
select * from $creator.tBiPaCo2
       for business_time as of '01.02.2011' ;
select * from $creator.tBiPaCo2
       for business_time as of '02.02.2011' ;
$*)
$]
$]