zOs/SQL/BIPLADDL

set current sqlid = 'S100447';
drop   TABLESPACE db2admin.ABiPlCo;
drop   TABLESPACE db2admin.ABiPlCoH;
commit;
CREATE TABLESPACE ABiPlCo
  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 ABiPlCoH
  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.tBiPlanCol;
drop   TABLE oa1p.tBiPlanColHist;
commit;
CREATE TABLE oa1p.tBiPlanCol
  (
      col        char(8) NOT NULL,
      installFr  date NOT NULL WITH DEFAULT,
      installTo  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 (installFr, installTo) ,
      period system_time (rowBegin, rowEnd) ,
      unique (col, business_time without overlaps)
  ) in db2admin.aBiPlCo
;
CREATE UNIQUE INDEX OA1P.iBiPlanCol1
  ON oa1p.tBiPlanCol
   (col, 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;
CREATE TABLE oa1p.tbiPlanColHist like oa1p.tbiPlanCol
     in db2Admin.abiPlCoH;
commit;
alter table oa1p.tBiPlanCol add versioning
      use history table oa1p.tbiPlanColHist;
commit;
xnsert into OA1P.tBiPaCo2 (colls, seq, col, qua, installFr)
                 values   ('defIns', 1, 'AV','', '01.01.2000');
xnsert 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' ;