Octopus Appendix B: DDL and NDL Scripts for the Octopus Stats Database

Octopus Administrator's Guide
Appendix B: DDL and NDL Scripts for the Octopus Statistics Database

DDL Script
NDL Scripts

Setting Up the Statistics Database

These files can be used to set up database tables to receive the contents of the Octopus detailed and cumulative statistics files. Connect to the database as the user DBA, and run the DDL file with the SAND CDBMS Interactive SQL utility (nisqlm) to create the domains and tables (DSTAT and CSTAT) for the database in a schema called NUCSTAT, owned by the userID NUCSTAT. Then run the NDL files from the directory specified for the .sta and .ost files (or edit the files to point to the proper location); make sure that the file names specified after the words import @ match the names specified for the files in the nucleus.ini file (this example uses "Sample_CStat.ost" and "Sample_Stat.sta").

Important:
When invoking the SAND CDBMS Data Loader (ndlm), include the argument -s 1 to start processing from the second row of data. The first row in either statistics file consists of column headers.

DDL Script

-- STAT.DDL

-- Creation of Octopus Stat Database

create authorization NUCSTAT default schema NUCSTAT;

set schema NUCSTAT;

drop Table NUCSTAT.DSTAT;
drop Table NUCSTAT.CSTAT;

Drop Domain NUCSTAT.D_TYPE;
Drop Domain NUCSTAT.D_COMPONENT;
Drop Domain NUCSTAT.D_DBNAME;
Drop Domain NUCSTAT.D_USERID;
Drop Domain NUCSTAT.D_SQL;
Drop Domain NUCSTAT.D_ERROR;
Drop Domain NUCSTAT.D_DATE;
Drop Domain NUCSTAT.D_TIME;
Drop Domain NUCSTAT.D_TIMESTAMP;
Drop Domain NUCSTAT.D_SMALLINT;
Drop Domain NUCSTAT.D_INTEGER;
Drop Domain NUCSTAT.D_UNSIGNED;

Create Domain NUCSTAT.D_TYPE VARCHAR(16);
Create Domain NUCSTAT.D_COMPONENT VARCHAR(64);
Create Domain NUCSTAT.D_DBNAME VARCHAR(32);
Create Domain NUCSTAT.D_USERID VARCHAR(32);
Create Domain NUCSTAT.D_SQL VARCHAR(4000);
Create Domain NUCSTAT.D_ERROR VARCHAR(4000);
Create Domain NUCSTAT.D_DATE Date;
Create Domain NUCSTAT.D_TIME Time;
Create Domain NUCSTAT.D_TIMESTAMP TimeStamp;
Create Domain NUCSTAT.D_SMALLINT SMALLINT;
Create Domain NUCSTAT.D_INTEGER INTEGER;
Create Domain NUCSTAT.D_UNSIGNED UNSIGNED;

-- Table Definition for Detailed Stats
Create Table NUCSTAT.DSTAT(
START_TS NUCSTAT.D_TIMESTAMP,
OCTO_DATE NUCSTAT.D_DATE,
OCTO_YEAR NUCSTAT.D_SMALLINT,
OCTO_MONTH NUCSTAT.D_SMALLINT,
OCTO_DAY NUCSTAT.D_SMALLINT,
OCTO_TIME NUCSTAT.D_TIME,
OCTO_HOUR NUCSTAT.D_SMALLINT,
OCTO_MINUTE NUCSTAT.D_SMALLINT,
OCTO_SECOND NUCSTAT.D_SMALLINT,
DBNAME NUCSTAT.D_DBNAME,
USERID NUCSTAT.D_USERID,
USERNUM NUCSTAT.D_INTEGER,
RSIZE NUCSTAT.D_INTEGER,
FROWS NUCSTAT.D_INTEGER,
TIMEWAIT NUCSTAT.D_INTEGER,
TIMEEXEC NUCSTAT.D_INTEGER,
TIMEFETCH NUCSTAT.D_INTEGER,
ENNUCSTAT.D_TS NUCSTAT.D_TIMESTAMP,
SQL_REQ1 NUCSTAT.D_SQL,
SQL_REQ2 NUCSTAT.D_SQL,
SQL_REQ3 NUCSTAT.D_SQL,
SQL_REQ4 NUCSTAT.D_SQL,
SQL_REQ5 NUCSTAT.D_SQL,
SQL_REQ6 NUCSTAT.D_SQL,
SQL_REQ7 NUCSTAT.D_SQL,
SQL_REQ8 NUCSTAT.D_SQL,
SQL_REQ9 NUCSTAT.D_SQL,
ERROR_CODE NUCSTAT.D_INTEGER,
ERROR_STR NUCSTAT.D_ERROR);

-- Table Definition for Cumulative Stats
Create Table NUCSTAT.CSTAT(
START_TS NUCSTAT.D_TIMESTAMP,
OCTO_DATE NUCSTAT.D_DATE,
OCTO_YEAR NUCSTAT.D_SMALLINT,
OCTO_MONTH NUCSTAT.D_SMALLINT,
OCTO_DAY NUCSTAT.D_SMALLINT,
OCTO_TIME NUCSTAT.D_TIME,
OCTO_HOUR NUCSTAT.D_SMALLINT,
OCTO_MINUTE NUCSTAT.D_SMALLINT,
OCTO_SECOND NUCSTAT.D_SMALLINT,
ENTRY_TYPE NUCSTAT.D_TYPE,
COMPONENT NUCSTAT.D_COMPONENT,
INT_NBREQ NUCSTAT.D_INTEGER,
INT_NBREQPROM NUCSTAT.D_INTEGER,
INT_RSIZE NUCSTAT.D_INTEGER,
INT_FROWS NUCSTAT.D_INTEGER,
INT_TOTTIMEWAIT NUCSTAT.D_INTEGER,
INT_TOTTIMEEXEC NUCSTAT.D_INTEGER,
INT_TOTTIMEFETCH NUCSTAT.D_INTEGER,
INT_MAXTIMEWAIT NUCSTAT.D_INTEGER,
INT_MAXTIMEEXEC NUCSTAT.D_INTEGER,
INT_MAXTIMEFETCH NUCSTAT.D_INTEGER,
CUM_NBREQ NUCSTAT.D_INTEGER,
CUM_NBREQPROM NUCSTAT.D_INTEGER,
CUM_RSIZE NUCSTAT.D_UNSIGNED,
CUM_FROWS NUCSTAT.D_INTEGER,
CUM_TOTTIMEWAIT NUCSTAT.D_INTEGER,
CUM_TOTTIMEEXEC NUCSTAT.D_INTEGER,
CUM_TOTTIMEFETCH NUCSTAT.D_INTEGER,
CUM_MAXTIMEWAIT NUCSTAT.D_INTEGER,
CUM_MAXTIMEEXEC NUCSTAT.D_INTEGER,
CUM_MAXTIMEFETCH NUCSTAT.D_INTEGER);

COMMIT WORK;

.EXIT

NDL Scripts

-- CSTAT_CSV.ndl

IMPORT @/usr/Sample_CStat.ost
{
  SKIP 1
  RECORD
  {
    CSVFORMAT(
    FIELDNAMESINFILE:0
    NUMBEROFFIELDS:23
    FIELDSEPARATOR:','
    RECORDSEPARATOR:'\n'
    )
  }

  NUCSTAT.CSTAT
  {
  START_TS f1
  OCTO_DATE LEFT(f1,10)
  OCTO_YEAR VALI(LEFT(f1,4))
  OCTO_MONTH VALI(MID(f1,6,2))
  OCTO_DAY VALI(MID(f1,9,2))
  OCTO_TIME MID(f1,12,8)
  OCTO_HOUR VALI(MID(f1,12,2))
  OCTO_MINUTE VALI(MID(f1,15,2))
  OCTO_SECOND VALI(MID(f1,18,2))

  ENTRY_TYPE f2
  COMPONENT f3
  INT_NBREQ f4
  INT_NBREQPROM f5
  INT_RSIZE f6
  INT_FROWS f7
  INT_TOTTIMEWAIT f8
  INT_TOTTIMEEXEC f9
  INT_TOTTIMEFETCH f10
  INT_MAXTIMEWAIT f11
  INT_MAXTIMEEXEC f12
  INT_MAXTIMEFETCH f13
  CUM_NBREQ f14
  CUM_NBREQPROM f15
  CUM_RSIZE f16
  CUM_FROWS f17
  CUM_TOTTIMEWAIT f18
  CUM_TOTTIMEEXEC f19
  CUM_TOTTIMEFETCH f20
  CUM_MAXTIMEWAIT f21
  CUM_MAXTIMEEXEC f22
  CUM_MAXTIMEFETCH f23
  }
}

-- DSTAT_CSV.ndl

IMPORT @/usr/Sample_Stat.sta
{
  SKIP 1
RECORD
  {
    CSVFORMAT(
    FIELDNAMESINFILE:0
    NUMBEROFFIELDS:20
    FIELDSEPARATOR:','
    RECORDSEPARATOR:'\n'
    )
  }

  NUCSTAT.DSTAT
  {
  START_TS f1
  OCTO_DATE LEFT(f1,10)
  OCTO_YEAR VALI(LEFT(f1,4))
  OCTO_MONTH VALI(MID(f1,6,2))
  OCTO_DAY VALI(MID(f1,9,2))
  OCTO_TIME MID(f1,12,8)
  OCTO_HOUR VALI(MID(f1,12,2))
  OCTO_MINUTE VALI(MID(f1,15,2))
  OCTO_SECOND VALI(MID(f1,18,2))

  DBNAME f2
  USERID f3
  USERNUM f4
  RSIZE f5
  FROWS f6
  TIMEWAIT f7
  TIMEEXEC f8
  TIMEFETCH f9
  SQL_REQ1 f10
  SQL_REQ2 f11
  SQL_REQ3 f12
  SQL_REQ4 f13
  SQL_REQ5 f14
  SQL_REQ6 f15
  SQL_REQ7 f16
  SQL_REQ8 f17
  SQL_REQ9 f18
  ERROR_CODE f19
  ERROR_STR f20
  }
}

Octopus Administrator's Guide Appendix B: DDL and NDL Scripts for the Octopus Statistics Database

Octopus Administrator's Guide
Appendix B: DDL and NDL Scripts for the Octopus Statistics Database