zOs/TX/PER05DDL

------------$-{right(userid() sysvar(sysnode) date(s) time(), 60, '-')}
-- testCase $dsn
-- env      $env      phase $phase
-- subsys   $subsys     db $db       creator $creator
------------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
$=tb=TWK934A1
SET CURRENT SQLID='S100447';
$@[
if $phase <= 1 then $@=[
drop tablespace $db.aper05x0;
drop tablespace $db.aper05x1;
drop tablespace $db.aper05x2;
drop tablespace $db.aper05SW;
commit;
$]
if $phase = 1 then
    $@do ix=0 to 2 $@=[

------------------------------------------------------------------------
CREATE TABLESPACE aper05x$ix
    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      EBCDIC
    DEFINE YES
    MAXROWS 255
;  ---------------------------------------------------------------------
  CREATE TABLE $creator.tper05x$ix
     ( da date not null,
       ix integer not null,
       ch char(10)
     )
    IN $db.aper05x$ix
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE
 ;
CREATE INDEX $creator.Iper05x$ix
  ON $creator.tper05x$ix
   (da            ASC)
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 0 PCTFREE 5
  GBPCACHE CHANGED
  CLUSTER
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFER NO
  DEFINE YES
;
alter table $creator.tper05x$ix
    add constraint date check(da between '01.01.201$ix'
                                 and     '31.12.201$ix')
;
delete from $creator.tper05x$ix;
insert into $creator.tper05x$ix
  select date('01.01.201$ix') + (r - 1) days, 0, 'i$ix' from
    (select row_number () over() r from sysibm.sysColumns
         fetch first 365 rows only) r
;
update $creator.tper05x$ix set ix = (month(da)*100+day(da))*1000000
;
insert into $creator.tper05x$ix
  select da, ix + r, 'j$ix' from $creator.tper05x$ix,
    (select row_number () over() r from sysibm.sysColumns
         fetch first 199  rows only) r
;
$]
$@=[
CREATE TABLESPACE aper05SW
    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      EBCDIC
    DEFINE YES
    MAXROWS 255
;  ---------------------------------------------------------------------
  CREATE TABLE $creator.tper05SW
     ( fr date not null,
       to date not null,
       tb char(2) not null
     )
    IN $db.aper05SW
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE ;
  insert into $creator.tper05SW
                select min(da), max(da), 'x0' from $creator.tper05x0
      union all select min(da), max(da), 'x1' from $creator.tper05x1
      union all select min(da), max(da), 'x2' from $creator.tper05x2
;
create view $creator.vPer05Union as
              select * from $creator.tPer05x0
    union all select * from $creator.tPer05x1
    union all select * from $creator.tPer05x2
;
create view $creator.vPer05UnionWhere as
              select * from $creator.tPer05x0
                  where da between '01.01.2010' and '31.12.2010'
    union all select * from $creator.tPer05x1
                  where da between '01.01.2011' and '31.12.2011'
    union all select * from $creator.tPer05x2
                  where da between '01.01.2012' and '31.12.2012'
;
create view $creator.vPer05UnionJoin as
              select x.* from $creator.tPer05x0 x, $creator.tPer05sw
                  where tb = 'x0' and da between fr and to
    union all select x.* from $creator.tPer05x1 x, $creator.tPer05sw
                  where tb = 'x1' and da between fr and to
    union all select x.* from $creator.tPer05x2 x, $creator.tPer05sw
                  where tb = 'x2' and da between fr and to
;
create view $creator.vPer05UnionInt as
              select * from $creator.tPer05x0
                  where ix between 0            and 433000000
    union all select * from $creator.tPer05x1
                  where ix between 500000000    and 833000000
    union all select * from $creator.tPer05x2
                  where ix between 900000000   and 1300000000
;
  commit;
$]
$]
---||| end   ddl testcase $mbr env $env phase $phase