zOs/SQL/BIPA9DD3

set current sqlid = 'S100447';
$*(
drop   TABLESPACE db2admin.ABiPaCo2;
drop   TABLESPACE db2admin.ABiPaCoH;
commit;
CREATE TABLESPACE ABiPaCo2
  IN db2admin
  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 ABiPaCoH
  IN db2admin
  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;
$*)
drop   TABLE oa1p.tBiPaCo2;
drop   TABLE oa1p.tBiPaCoH;
commit;
CREATE TABLE oa1p.tBiPaCo2
  (
      colls      char(12) not null,
      seq        smallint not null,
      col        char(8) NOT NULL,
      qua        char(8) NOT NULL,
      installFr  date NOT NULL WITH DEFAULT,
      installTo  date NOT NULL WITH DEFAULT '31.12.9999',
      period business_time (installFr, installTo) ,
      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 system_time (rowBegin, rowEnd) ,
      unique (colls, seq, qua, business_time without overlaps) ,
      rowChg timestamp not null generated always
        for each row on update as row change timestamp
  ) in db2admin.aBiPaCo2
;
CREATE UNIQUE INDEX OA1P.iBiPaCo2A1
  ON oa1p.tBiPaCo2
   (colls, seq, qua, business_time without overlaps)
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 10 PCTFREE 10
  GBPCACHE CHANGED
  NOT CLUSTER
  COMPRESS NO
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFER NO
  DEFINE YES
  PIECESIZE 2 G;
CREATE TABLE oa1p.tBiPaCoH like oa1p.tBiPaCo2 in db2Admin.abiPaCoH;
commit;
alter table oa1p.tBiPaCo2 add versioning
      use history table oa1p.tBiPaCoH;
commit;
insert into OA1P.tBiPaCo2 (colls, seq, col, qua, installFr)
                 values   ('defIns', 1, 'AV','', '01.01.2000');
insert into OA1P.tBiPaCo2 (colls, seq, col, qua, installFr)
                 values   ('defIns', 1, 'AV','', '01.02.2011');
update OA1P.tBiPaCo2 set installTo = '01.02.2011';
insert into OA1P.tBiPaCo2 (colls, seq, col, qua, installFr)
                 values   ('defIns', 1, 'AV','', '01.02.2011');
select * from OA1P.tBiPaCo2;
select * from OA1P.tBiPaCoH;
select * from OA1P.tBiPaCo2
       for business_time as of '30.05.1997' ;
select * from OA1P.tBiPaCo2
       for business_time as of '31.12.1999' ;
select * from OA1P.tBiPaCo2
       for business_time as of '01.01.2000' ;
select * from OA1P.tBiPaCo2
       for business_time as of '31.01.2011' ;
select * from OA1P.tBiPaCo2
       for business_time as of '01.02.2011' ;
select * from OA1P.tBiPaCo2
       for business_time as of '02.02.2011' ;