DSNTESS

-- /*******************************************************************/
-- /*                                                                 */
-- /*  LICENSED MATERIALS - PROPERTY OF IBM                           */
-- /*  5625-DB2                                                       */
-- /*  (C) COPYRIGHT 2003, IBM CORP.  ALL RIGHTS RESERVED.            */
-- /*                                                                 */
-- /*  STATUS = VERSION 8                                             */
-- /*                                                                 */
-- /*******************************************************************/
--
-- Use the following SQL data definition statements to create
-- the statistics database, table space, tables and indexes.
--
-- The names and declared attributes of the objects must not be changed.
-- However, other attributes can be changed.  For example,
-- COMPRESS YES can be specified.
--
-- The amount of primary and secondary space to allocate can be
-- calculated by using the formulas in the "Administration Guide".
--
-- The approximate number of rows in the two statistics tables
-- can be determined by the following SQL statement.  The sample DDL
-- used 20,000 objects as an estimate to determine the amount of
-- space to request.
--
-- SELECT C1 + C2 FROM
--        (SELECT COUNT(*) AS C1 FROM SYSIBM.SYSTABLEPART) AS T1,
--        (SELECT COUNT(*) AS C2 FROM SYSIBM.SYSINDEXPART) AS T2;
--
   CREATE DATABASE DSNRTSDB CCSID EBCDIC;
   CREATE TABLESPACE DSNRTSTS IN DSNRTSDB
      CCSID EBCDIC
      CLOSE NO
      LOCKMAX 0
      LOCKSIZE ROW
      SEGSIZE 32
      USING STOGROUP SYSDEFLT
            PRIQTY 1600
            SECQTY 160;

   CREATE TABLE SYSIBM.TABLESPACESTATS
      (DBNAME             CHAR( 8)  NOT NULL,
       NAME               CHAR( 8)  NOT NULL,
       PARTITION          SMALLINT  NOT NULL,
       DBID               SMALLINT  NOT NULL,
       PSID               SMALLINT  NOT NULL,
       UPDATESTATSTIME    TIMESTAMP NOT NULL WITH DEFAULT,
       TOTALROWS          FLOAT             ,
       NACTIVE            INTEGER           ,
       SPACE              INTEGER           ,
       EXTENTS            SMALLINT          ,
       LOADRLASTTIME      TIMESTAMP         ,
       REORGLASTTIME      TIMESTAMP         ,
       REORGINSERTS       INTEGER           ,
       REORGDELETES       INTEGER           ,
       REORGUPDATES       INTEGER           ,
       REORGUNCLUSTINS    INTEGER           ,
       REORGDISORGLOB     INTEGER           ,
       REORGMASSDELETE    INTEGER           ,
       REORGNEARINDREF    INTEGER           ,
       REORGFARINDREF     INTEGER           ,
       STATSLASTTIME      TIMESTAMP         ,
       STATSINSERTS       INTEGER           ,
       STATSDELETES       INTEGER           ,
       STATSUPDATES       INTEGER           ,
       STATSMASSDELETE    INTEGER           ,
       COPYLASTTIME       TIMESTAMP         ,
       COPYUPDATEDPAGES   INTEGER           ,
       COPYCHANGES        INTEGER           ,
       COPYUPDATELRSN     CHAR(6)   FOR BIT DATA,
       COPYUPDATETIME     TIMESTAMP         )
      IN DSNRTSDB.DSNRTSTS CCSID EBCDIC;

   CREATE UNIQUE INDEX SYSIBM.TABLESPACESTATS_IX
      ON SYSIBM.TABLESPACESTATS
         (DBID, PSID, PARTITION)
      CLUSTER CLOSE NO;

   CREATE TABLE SYSIBM.INDEXSPACESTATS
      (DBNAME             CHAR( 8)  NOT NULL,
       INDEXSPACE         CHAR( 8)  NOT NULL,
       PARTITION          SMALLINT  NOT NULL,
       DBID               SMALLINT  NOT NULL,
       ISOBID             SMALLINT  NOT NULL,
       PSID               SMALLINT  NOT NULL,
       UPDATESTATSTIME    TIMESTAMP NOT NULL WITH DEFAULT,
       TOTALENTRIES       FLOAT             ,
       NLEVELS            SMALLINT          ,
       NACTIVE            INTEGER           ,
       SPACE              INTEGER           ,
       EXTENTS            SMALLINT          ,
       LOADRLASTTIME      TIMESTAMP         ,
       REBUILDLASTTIME    TIMESTAMP         ,
       REORGLASTTIME      TIMESTAMP         ,
       REORGINSERTS       INTEGER           ,
       REORGDELETES       INTEGER           ,
       REORGAPPENDINSERT  INTEGER           ,
       REORGPSEUDODELETES INTEGER           ,
       REORGMASSDELETE    INTEGER           ,
       REORGLEAFNEAR      INTEGER           ,
       REORGLEAFFAR       INTEGER           ,
       REORGNUMLEVELS     INTEGER           ,
       STATSLASTTIME      TIMESTAMP         ,
       STATSINSERTS       INTEGER           ,
       STATSDELETES       INTEGER           ,
       STATSMASSDELETE    INTEGER           ,
       COPYLASTTIME       TIMESTAMP         ,
       COPYUPDATEDPAGES   INTEGER           ,
       COPYCHANGES        INTEGER           ,
       COPYUPDATELRSN     CHAR(6)   FOR BIT DATA,
       COPYUPDATETIME     TIMESTAMP         )
      IN DSNRTSDB.DSNRTSTS CCSID EBCDIC;

   CREATE UNIQUE INDEX SYSIBM.INDEXSPACESTATS_IX
      ON SYSIBM.INDEXSPACESTATS
         (DBID, ISOBID, PARTITION)
      CLUSTER CLOSE NO;