zOs/SQL/EXCEPTS

  set current sqlid = 'S100447';
  drop   VIEW A540769.VEXCEPTIONS_TS ;
  CREATE VIEW A540769.VEXCEPTIONS_TS as
    SELECT prio, db, ts, part, REORG, REMARK, UNCLUST, FARINDREF,
           NEARINDREF, EXTENTS, REORGDAYS, INSERTS, UPDATES, DELETES,
           case when posstr(db,   '*') > 0
                then posstr(db,   '*') - 1 else 8 end dbLen,
           case when posstr(ts,   '*') > 0
                then posstr(ts,   '*') - 1 else 8 end tsLen,
           case when posstr(part, '*') > 0
                     then posstr(part,  '*') - 1 else 5 end partLen,
           char(right('00' || strip(char(prio)), 3) || ' '
                || translate(db || ts || part , x'01', '*') , 25) key,
           guVon, guBis,
           INSERTTIMESTAMP
      FROM A540769.TEXCEPTIONS_TS ;
--
  LABEL ON TABLE A540769.VEXCEPTIONS_TS IS 'RTS TABLESPACE EXCEPTIONS';
--
  COMMENT ON TABLE A540769.VEXCEPTIONS_TS
    IS 'RTS TABLESPACE EXCEPTIONS';
--
  LABEL ON A540769.VEXCEPTIONS_TS
   (INSERTTIMESTAMP IS 'INSERT TIMESTAMP',
    DELETES IS 'SCHWELLWERT FÜR DELETES %',
    UPDATES IS 'SCHWELLWERT FÜR UPDATES %',
    INSERTS IS 'SCHWELLWERT FÜR INSERTS %',
    REORGDAYS IS 'NACH X DAYS REORG',
    EXTENTS IS 'EXTENTS PRO TS/PARTITION',
    NEARINDREF IS 'OVERFLOW NEAR (<16 PAGES)',
    FARINDREF IS 'OVERFLOW FAR  (>16 PAGES)',
    UNCLUST IS 'SCHWELLWERT FÜR CLUSTERING %',
    REMARK IS 'BESCHREIBUNG',
    REORG IS 'ALWAYS,NEVER,THRESHOLD,DEFAULT',
    ts IS 'TABLESPACENAME',
    db IS 'DATENBANKNAME');
--
  COMMIT;
x
--drop   TABLESPACE dA540769.EXCEpTTS;
  commit;
  CREATE TABLESPACE EXCEpTTS
    IN DA540769
    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;
--
  COMMIT;
--
------------------------------------------------------------------------
--    Table=A540769.TEXCEPTIONS_TS In DA540769.EXCEpTTS
------------------------------------------------------------------------
--
  CREATE TABLE A540769.TEXCEPTIONS_TS
     (prio                 smallint not null default 0,
      db                   CHAR(8) FOR SBCS DATA NOT NULL
        WITH DEFAULT,
      ts                   CHAR(8) FOR SBCS DATA NOT NULL
        WITH DEFAULT,
      part                 CHAR(5) FOR SBCS DATA NOT NULL
        WITH DEFAULT '*',
      guVon                date not null with default '01.01.2000',
      guBis                date not null with default '01.01.9999',
      REORG                CHAR(12) FOR SBCS DATA NOT NULL
        WITH DEFAULT 'THRESHOLD',
      UNCLUST              INTEGER WITH DEFAULT NULL,
      INSERTS              INTEGER WITH DEFAULT NULL,
      UPDATES              INTEGER WITH DEFAULT NULL,
      DELETES              INTEGER WITH DEFAULT NULL,
      FARINDREF            INTEGER WITH DEFAULT NULL,
      NEARINDREF           INTEGER WITH DEFAULT NULL,
      EXTENTS              SMALLINT WITH DEFAULT NULL,
      INSERTTIMESTAMP      TIMESTAMP NOT NULL WITH DEFAULT,
      REMARK               CHAR(150) FOR SBCS DATA NOT NULL
        WITH DEFAULT,
      REORGDAYS            INTEGER WITH DEFAULT NULL,
      CONSTRAINT REORG
        CHECK (REORG='ALWAYS' OR REORG='NEVER' OR REORG='THRESHOLD'OR RE
ORG='DEFAULT'),
      CONSTRAINT PRIO
        CHECK (prio >= 0 and prio <= 999),
      CONSTRAINT guBis
        check (guBis >= guVon)
)
    IN DA540769.EXCEpTTS
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE;
--
  LABEL ON TABLE A540769.TEXCEPTIONS_TS
    IS 'AUSNAHMEN FüR REORGS MIT RTS';
--
  COMMENT ON TABLE A540769.TEXCEPTIONS_TS
    IS 'IN DIESER TABELLE MüSSEN ALLE TABLESPACES AUFGEFüHRT WERDEN, DIE
 NICHT MIT STANDARD-SCHWELLWERTEN REORGANISIERT WERDEN SOLLEN.';
--
  COMMENT ON A540769.TEXCEPTIONS_TS
   (EXTENTS IS 'EXTENTS PRO TS/PARTITION > NN',
    NEARINDREF IS '((REORGNEARINDREF*100)/TOTALROWS) > NN',
    FARINDREF IS '((REORGFARINDREF*100)/TOTALROWS) > NN',
    DELETES IS '((REORGDELETES*100)/TOTALROWS) > NN',
    UPDATES IS '((REORGUPDATES*100)/TOTALROWS) > NN',
    INSERTS IS '((REORGINSERTS*100)/TOTALROWS) > NN',
    UNCLUST IS '((REORGUNCLUSTINS*100)/TOTALROWS) > NN',
    REORG IS 'ALWAYS,NEVER,TRESHHOLD,DEFAULT');
--
  LABEL ON A540769.TEXCEPTIONS_TS
   (REORGDAYS IS 'NACH X DAYS REORG',
    REMARK IS 'BESCHREIBUNG',
    INSERTTIMESTAMP IS 'INSERT TIMESTAMP',
    EXTENTS IS 'EXTENTS PRO TS/PARTITION',
    NEARINDREF IS 'OVERFLOW NEAR (<16 PAGES)',
    FARINDREF IS 'OVERFLOW FAR (>16 PAGES)',
    DELETES IS 'SCHWELLWERT FÜR DELETES %',
    UPDATES IS 'SCHWELLWERT FÜR UPDATES %',
    INSERTS IS 'SCHWELLWERT FÜR DELETES %',
    UNCLUST IS 'SCHWELLWERT FÜR CLUSTERING %',
    REORG IS 'ALWAYS,NEVER,THRESHOLD,DEFAULT',
    ts IS 'TABLESPACENAME',
    db IS 'DATENBANKNAME');
--
  COMMIT;
--
------------------------------------------------------------------------
-- Database=DA540769
--    Index=A540769.IEXCEPTIONS_TS On A540769.TEXCEPTIONS_TS
------------------------------------------------------------------------
--
  CREATE UNIQUE INDEX A540769.IEXCEPTIONS_TS
    ON A540769.TEXCEPTIONS_TS
     (prio                  ASC,
      db                    ASC,
      ts                    ASC,
      part                  asc)
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    ERASE  NO
    FREEPAGE 0 PCTFREE 5
    GBPCACHE CHANGED
    CLUSTER
    BUFFERPOOL BP1
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G;
--
  COMMIT;
--
------------------------------------------------------------------------
--    View=A540769.VEXCEPTIONS_TS
------------------------------------------------------------------------
--
--