zOs/TX/PER19DDL

------------$-{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
SET CURRENT SQLID='S100447';
------------------------------------------------------------------------
$@[
if $phase <= 1 then $@=[
drop tablespace $db.aPER19;
commit;
$]
if $phase = 1 then $@=[
drop tablespace $db.aPER19;
commit;
CREATE TABLESPACE aper19
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    ERASE  NO
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    numparts $parts
    dssize 16 G
    BUFFERPOOL BP2
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      EBCDIC
    DEFINE YES
    locksize row
    MAXROWS 255
;
commit;
  CREATE TABLE $creator.tPER19
     (EN1PART              INTEGER NOT NULL,
      EA1FAMILY            CHAR(20) FOR SBCS DATA NOT NULL
        WITH DEFAULT,
      EA1DOKTYPE           CHAR(16) FOR SBCS DATA NOT NULL
        WITH DEFAULT,
      EA1ATRN              CHAR(20) FOR SBCS DATA WITH DEFAULT NULL,
      ET1SEOCC             TIMESTAMP (6) WITHOUT TIME ZONE
        WITH DEFAULT NULL,
      EN1ENTERTAG          CHAR(20) FOR SBCS DATA WITH DEFAULT NULL,
      data             varCHAR(2000) WITH DEFAULT not NULL
     ) in $db.aPER19 not volatile
     partition by (en1part)
     ( partition 1 ending at(1)
   $@do px=2 to $parts $@=[
     , partition $px ending at($px)
   $]
     ) ;
--------------------------------------------------------------
commit;
  insert into $creator.tPER19
       select mod(row_number() over(), $parts) + 1
            , left(tbName, 20)
            , left(tbCreator, 16)
            , left(name     , 20)
            , createdTs + colNo years
            , left(strip(substr(name || '      ', 6)) || strip(tbName)
                     , 20)
            , left(repeat(strip(tbCreator) || '.' || strip(tbName)
            || '.' || strip(name) || ':' || strip(colType), 10), 1000)
           from sysibm.syscolumns
  ;
  commit;
  insert into $creator.tPER19 select * from $creator.tPER19;
  commit;
  insert into $creator.tPER19 select * from $creator.tPER19;
  commit;
  insert into $creator.tPER19 select * from $creator.tPER19;
  commit;
  insert into $creator.tPER19 select * from $creator.tPER19;
  commit;
$]
if $phase >= 2 then $@=[
   drop   INDEX $creator.iPer19;
    COMMIT;
   CREATE INDEX $creator.iPer19
      ON $creator.tPER19
       (SUBSTR(CHAR(ET1SEOCC),1,4) ASC,
        EN1ENTERTAG           ASC,
        EA1ATRN               ASC)
      USING STOGROUP GSMS
      PRIQTY -1 SECQTY -1
      FREEPAGE 5 PCTFREE 5
      GBPCACHE CHANGED
      $-[if($phase==3, partitioned)$]
      NOT CLUSTER
      COMPRESS NO
      BUFFERPOOL BP1
      CLOSE YES
      COPY NO
      DEFER YES
      DEFINE YES;
    COMMIT;
$]
$]
---||| end   ddl testcase $mbr env $env phase $phase