zOs/TX/PER01DDL

------------$-{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.aPer01x1;
drop tablespace $db.aPer01x2;
drop tablespace $db.aPer01x3;
commit;
$]
if $phase = 1 then
    $@do ix=0 to 2 $@=[

------------------------------------------------------------------------
CREATE TABLESPACE aPer01x$ix
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    ERASE  NO
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    numParts 12
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      EBCDIC
    DEFINE YES
    MAXROWS 255
;  ---------------------------------------------------------------------
  CREATE TABLE $creator.tPer01x$ix
     ( da date not null,
       ix integer not null,
       ch char(10)
     )
     partition by (da)
     (part  1 values ('31.01.201$ix'),
      part  2 values ('28.02.201$ix'),
      part  3 values ('31.03.201$ix'),
      part  4 values ('30.04.201$ix'),
      part  5 values ('31.05.201$ix'),
      part  6 values ('30.06.201$ix'),
      part  7 values ('31.07.201$ix'),
      part  8 values ('31.08.201$ix'),
      part  9 values ('30.09.201$ix'),
      part 10 values ('31.10.201$ix'),
      part 11 values ('30.11.201$ix'),
      part 12 values ('31.12.201$ix')
     )
    IN $db.aPer01x$ix
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE ;
CREATE INDEX $creator.IPer01x$ix
  ON $creator.tPer01x$ix
   (ix            ASC)
  partitioned
  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 ;
commit;
------------------------------------------------------------------------
delete from $creator.tPer01x$ix;
insert into $creator.tPer01x$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.tPer01x$ix set ix = (month(da)*100+day(da))*1000000;
insert into $creator.tPer01x$ix
  select da, ix + r, 'j$ix' from $creator.tPer01x$ix,
    (select row_number () over() r from sysibm.sysColumns
         fetch first 1999 rows only) r
;
commit;
$] else
        if $phase = 2 then
    $@do ix=0 to 2 $@=[
alter table $creator.tPer01x$ix
    add constraint date check(da >= '01.01.201$ix');
$]
$]
---||| end   ddl testcase $mbr env $env phase $phase