zOs/TX/ORMANDDL

------------$-[right(userid() sysvar(sysnode) date(s) time(), 60,'-')$]
-- testCase $dsn
-- env      $env      phase $phase
-- dbSys    $dbSys      db $db       creator $creator
-----------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
SET CURRENT SQLID='S100447';
SET CURRENT SCHEMA = $creator;
drop tablespace $db.ORMAN;
commit;
$@ if $phase >= 1 then $@=/create/
  CREATE TABLESPACE ORMAN
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    FREEPAGE 10 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    dssize 16 g
    numparts 55
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      EBCDIC
    DEFINE YES
    MAXROWS 255;
  CREATE TABLE $creator.TORMANJOUR
     ( cust                 char(3)  not null
     , pa2                  smallint not null
     , tst                  timestamp not null
     )
     PARTITION BY (cust, pa2)
     $=c1=(
     $=px = 0
     $@do jx=0 to 1 $@=[
       $c1 partition $.^paNx ending at ('DPB', ${jx}0105)
       $=c1= ,
         $do mx=1 to 12 $@=[
             $= mx =- right($mx, 2, 0)
       , partition $.^paNx ending at ('DPB', ${jx}${mx}15)
       $@ if $mx = 12 then $@=[
       , partition $.^paNx ending at ('DPB', ${jx}1229)  $]
       , partition $.^paNx ending at ('DPB', ${jx}${mx}31)
         $]
    $]
       , partition $.^paNx ending at ('GPP', 32767)
       -- Attention ' ' is necessary, because  DB2 pads with x'FF' |||
       , partition $.^paNx ending at ('MF ',  01231)
       , partition $.^paNx ending at ('MF ',  11231)
      )
    IN $db.ORMAN
    AUDIT NONE
    DATA CAPTURE CHANGES
    CCSID      EBCDIC
    NOT VOLATILE
;
CREATE TRIGGER $creator.tORMANJOURIns
  NO CASCADE BEFORE insert ON $creator.TORMANJOUR
  REFERENCING New as n
  for each row mode DB2SQL
    set pa2 = mod(  (year(n.tst) * 100 + month(n.tst)) * 100+day(n.tst)
                 , 20000)
;
CREATE TRIGGER $creator.tORMANJOURUpd
  NO CASCADE BEFORE update ON $creator.TORMANJOUR
  REFERENCING New as n
  for each row mode DB2SQL
    set pa2 = mod(  (year(n.tst) * 100 + month(n.tst)) * 100+day(n.tst)
                 , 20000)
;
  insert into $creator.TORMANJOUR
  with t (t, l) as
  (
     select current timestamp, 0 from sysibm.sysDummy1
     union all select t - (48 * 60 + 1.2) minutes, l+1
         from t where l < 500
  )
    select 'DPB', 0, t from t
    union all select 'GPP', 0, t from t where l < 100
    union all select 'MF', 0, t from t where l < 100
;
commit;
$/create/
$proc $@/paNx/
    $=px =- $px + 1
    return $px
$/paNx/