配置 IBM InfoSphere DataStage Operations Console

版本:IBM InfoSphere DataStage V11.3.1

操作系统:linux redhat 6.4

 

查看是否注册DSODB 这个用户

目录:

/ds/informationServer/ASBServer/bin

脚本:

-sh-4.1$ ./RepositoryAdmin.sh -listRepositories

Sep 22, 2016 10:42:16 AM com.ibm.xmeta.pm.orm.impl.LegacySessionManager cleanupLocks

INFO: Clearing abandoned locks (2) ...

QSSRDDB

dsodb
-sh-4.1$ 

DatabasePlatform.databaseType=ORACLE

DatabasePlatform.version=11g

DatabaseServer.host=dwtest

DatabaseServer.port=1521

Database.name=dsdb1

Database.alias=dsdb1

Database.location=

Repository.name=dsodb

Repository.description=

Repository.tool=DataStage

Repository.context=

Repository.schema=dsodb

RepositoryConnection.name=dsodbConn

RepositoryConnection.userName=dsodb

RepositoryConnection.password={iisenc}N2RHakj6gLz7fCJ2yknhIg==

RepositoryConnection.connectionURL=jdbc:ibm:oracle://dwtest:1521;SID=dsdb1;batchPerformanceWorkaround=true

RepositoryConnection.managedDataSourceName=

Tablespace.name=dsodb

--

./RepositoryAdmin.sh -displayRepository -rn QSSRDDB

RepositoryAdmin.sh

DatabasePlatform.databaseType=ORACLE

DatabasePlatform.version=11g

DatabaseServer.host=report-ds

DatabaseServer.port=1521

Database.name=dsdb

Database.alias=dsdb

Database.location=

Repository.name=DSODB

Repository.description=

Repository.tool=StandardizationRulesDesigner

Repository.context=

Repository.schema=DSODB

RepositoryConnection.name=DSODB

RepositoryConnection.userName=DSODB

RepositoryConnection.password=DSODB

RepositoryConnection.connectionURL=jdbc:ibm:oracle://report-ds:1521;SID=dsdb;batchPerformanceWorkaround=true

RepositoryConnection.managedDataSourceName=

Tablespace.name=dsdbspace

注册

./RepositoryAdmin.sh -registerRepository -pf DSODB.properties


 

本文主要参考:

http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1205renyy/

为了使 Operation Console 能够监控作业与系统资源等,用户必须先在 IBM InfoSphere DataStage 的上做一些配置。配置工作由 5 个具体步骤组成:

  • 创建 Operations 数据库及其模式和表
  • 在 Engine 层上配置连接 operations 数据库
  • 配置 Operations 数据库监控目标
  • 检测配置信息
  • 管理启动和停止 Operations Console 各服务进程

以下将详细介绍这 5 个配置步骤:

1:创建 Operations 数据库及其模式和表

 

--创建表空间:
CREATE TABLESPACE dsodbspace LOGGING DATAFILE '/ds/oracle/oradata/dsdb/datafile/dsodb_01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 2048M EXTENT MANAGEMENT LOCAL ONLINE SEGMENT SPACE MANAGEMENT AUTO;

--创建用户并指定表空间:
create user DSODB identified by DSODB default tablespace dsodbspace;

--创建表脚本:

--           Licensed Materials - Property of IBM
--           Copyright IBM Corp. 2012, 2014
--------------------------------------------------------------------------------
-- DataStage operations database: create tables
-- for Oracle
-------------------------------------------------------------------------------

WHENEVER SQLERROR EXIT 1;

CREATE TABLE DSODB.Host (
     HOSTID               NUMBER(19, 0)    NOT NULL
   , HostName             NVARCHAR2(80)    NOT NULL         
   , CreatedTimestamp     TIMESTAMP        NOT NULL
   , InstallationDir      NVARCHAR2(255)   NOT NULL    -- '-' indicates no installation
   , MonStartTimestamp    TIMESTAMP
   , PRIMARY KEY (HOSTID)
   , CONSTRAINT realkeyHost UNIQUE (HostName, InstallationDir)
)tablespace dsodbspace;

CREATE TABLE DSODB.HostDetail (
     CreatedTimestamp     TIMESTAMP        NOT NULL
   , HOSTID               NUMBER(19, 0)    NOT NULL 
   , HEAD_HOSTID          NUMBER(19, 0)    NOT NULL 
   , LastCheckedTimestamp TIMESTAMP        NOT NULL
   , UTCOffsetMins        NUMBER(11, 0)
   , TimezoneName         NVARCHAR2(80)
   , PlatformName         NVARCHAR2(80)
   , PlatformVersion      NVARCHAR2(80)
   , NumCPUs              NUMBER(11, 0)
   , CPUModel             NVARCHAR2(80)
   , PhysicalMemoryKB     NUMBER(19, 0) 
   , VirtualMemoryKB      NUMBER(19, 0) 
   , PRIMARY KEY (CreatedTimestamp, HOSTID, HEAD_HOSTID)
   , CONSTRAINT host1HostDetail FOREIGN KEY (HOSTID)      REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
   , CONSTRAINT host2HostDetail FOREIGN KEY (HEAD_HOSTID) REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
)tablespace dsodbspace;

CREATE TABLE DSODB.ParallelConfig (
     CONFIGID             NUMBER(19, 0)    NOT NULL
   , HOSTID               NUMBER(19, 0)    NOT NULL 
   , CreationTimestamp    TIMESTAMP        NOT NULL
   , NodeListHash         NUMBER(11, 0)    NOT NULL
   , NodeList             XMLTYPE
   , PRIMARY KEY (CONFIGID)
   , CONSTRAINT hostParallelConfig FOREIGN KEY (HOSTID) REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
)tablespace dsodbspace XMLTYPE NodeList STORE AS CLOB;

-- Format of XML in NodeList:            --
--   <nodes>                             --
--      <node pname="xxxx" lnum="N"/>    --
--   </nodes>                            --

-- Following lets you see the XML in NodeList as an embedded table --

CREATE VIEW DSODB.ParallelConfigNodes AS 
   SELECT c.CONFIGID
        , c.HOSTID
        , x.PhysicalName
        , x.NumLogicalNodes 
   FROM DSODB.ParallelConfig c,
        xmltable('/nodes/node' passing c.NodeList
                 columns PhysicalName      NVARCHAR2(255)   path '@pname'
                       , NumLogicalNodes   NUMBER(11, 0)    path '@lnum'
                ) x;

CREATE TABLE DSODB.JobExec (
     JOBID                NUMBER(19, 0)    NOT NULL       
   , HOSTID               NUMBER(19, 0)    NOT NULL
   , ProjectName          NVARCHAR2(80)    NOT NULL
   , JobName              NVARCHAR2(255)   NOT NULL
   , CompilationTimestamp TIMESTAMP        NOT NULL
   , FolderPath           NVARCHAR2(255)
   , JobType              CHAR(3)          NOT NULL
   , JobShortDescription  NCLOB
   , JobLongDescription   NCLOB
   , DesignHostName       NVARCHAR2(80)
   , DesignProjectName    NVARCHAR2(80)
   , IsMultiInstance      NUMBER(6, 0)                                    
   , WebServicesEnabled   NUMBER(6, 0)                                   
   , PRIMARY KEY (JOBID)
   , CONSTRAINT realkeyJobExec UNIQUE (HOSTID, ProjectName, JobName, CompilationTimestamp)
   , CONSTRAINT hostJobExec    FOREIGN KEY (HOSTID) REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
)tablespace dsodbspace;

CREATE TABLE DSODB.JobRun (
     RUNID                NUMBER(19, 0)    NOT NULL       
   , CONTROLLING_RUNID    NUMBER(19, 0)
   , JOBID                NUMBER(19, 0)    NOT NULL
   , CONFIGID             NUMBER(19, 0)           
   , InvocationId         NVARCHAR2(255)   NOT NULL
   , CreationTimestamp    TIMESTAMP        NOT NULL
   , LastUpdateTimestamp  TIMESTAMP        NOT NULL
   , RunStartTimestamp    TIMESTAMP
   , RunEndTimestamp      TIMESTAMP
   , ElapsedRunSecs       NUMBER(11, 0)
   , RunType              CHAR(3)          NOT NULL
   , RunMajorStatus       CHAR(3)          NOT NULL
   , RunMinorStatus       CHAR(3)          NOT NULL
   , UserStatus           NVARCHAR2(40)
   , MasterPid            NUMBER(19, 0)
   , ConductorPid         NUMBER(19, 0)
   , NumMessagesTotal     NUMBER(11, 0)
   , NumMessagesWarning   NUMBER(11, 0)
   , NumMessagesFatal     NUMBER(11, 0)
   , TotalRowsConsumed    NUMBER(19, 0)
   , TotalRowsProduced    NUMBER(19, 0)
   , TotalCPU             NUMBER(19, 0)
   , ConfigFileName       NVARCHAR2(255)
   , TotalPhysicalNodes   NUMBER(11, 0)
   , TotalLogicalNodes    NUMBER(11, 0)
   , ISUserName           NVARCHAR2(40)
   , DSUserName           NVARCHAR2(40)
   , CustomBatchId        NVARCHAR2(40)
   , CustomJobType        NVARCHAR2(40)
   , CustomContact        NVARCHAR2(40)
   , CustomSequence       NVARCHAR2(40)
   , CustomField1         NVARCHAR2(40)
   , CustomField2         NVARCHAR2(40)
   , QueueName            NVARCHAR2(80)
   , PRIMARY KEY (RUNID)
   , CONSTRAINT realkeyJobRun UNIQUE (JOBID, InvocationId, CreationTimestamp)
   , CONSTRAINT jobJobRun     FOREIGN KEY (JOBID)             REFERENCES DSODB.JobExec(JOBID)            ON DELETE CASCADE
   , CONSTRAINT controlJobRun FOREIGN KEY (CONTROLLING_RUNID) REFERENCES DSODB.JobRun(RUNID)             ON DELETE CASCADE
   , CONSTRAINT configJobRun  FOREIGN KEY (CONFIGID)          REFERENCES DSODB.ParallelConfig(CONFIGID)  ON DELETE CASCADE
)tablespace dsodbspace;

CREATE TABLE DSODB.JobRunLog (
     RUNID                NUMBER(19, 0)    NOT NULL
   , EventId              NUMBER(11, 0)    NOT NULL
   , LogTimestamp         TIMESTAMP        NOT NULL
   , LogType              CHAR(3)          NOT NULL
   , MessageId            NVARCHAR2(24)
   , ContentType          NVARCHAR2(24)
   , MessageText          NCLOB   
   , PRIMARY KEY (RUNID, EventId)
   , CONSTRAINT runJobRunLog FOREIGN KEY (RUNID) REFERENCES DSODB.JobRun(RUNID) ON DELETE CASCADE
)tablespace dsodbspace;

CREATE TABLE DSODB.JobRunParams (
     RUNID                NUMBER(19, 0)    NOT NULL
   , ParamList            XMLTYPE
   , CONSTRAINT runJobRunParams FOREIGN KEY (RUNID) REFERENCES DSODB.JobRun(RUNID) ON DELETE CASCADE
)tablespace dsodbspace XMLTYPE ParamList STORE AS CLOB;

-- Format of XMLTYPE in ParamList:            --
--   <params>                             --
--      <param name="xxxx" value="yyyy"/> --
--   </params>                            --

-- Following lets you see the XML in ParamsList as an embedded table --

CREATE VIEW DSODB.JobRunParamsView AS 
   SELECT p.RUNID
        , x.ParamName
        , x.ParamValue 
   FROM DSODB.JobRunParams p,
        XMLTable('/params/param' passing p.ParamList
                 columns ParamName  NVARCHAR2(255) path '@name'
                       , ParamValue NVARCHAR2(255) path '@value'
                ) x;

CREATE TABLE DSODB.JobRunUsage (
     StartTimestamp       TIMESTAMP       NOT NULL
   , RUNID                NUMBER(19, 0)   NOT NULL
   , EndTimestamp         TIMESTAMP       NOT NULL
   , ResourceInfo         XMLTYPE
   , PRIMARY KEY (StartTimestamp, RUNID)
   , CONSTRAINT runJobRunUsage FOREIGN KEY (RUNID) REFERENCES DSODB.JobRun(RUNID) ON DELETE CASCADE
)tablespace dsodbspace XMLTYPE ResourceInfo STORE AS CLOB;

-- Format of XML in ResourceInfo:          --
--   <rows>                                --
--      <snap e="nn" c="nn" p="nn" />      --
--      <snap e="nn" c="nn" p="nn" />      --
--      ......                             --
--   </rows>                               --

-- Following lets you see the XML in ResourceInfo as as embedded table --

CREATE VIEW DSODB.JobRunTotalRowsUsage AS 
     SELECT u.RUNID
          , u.StartTimestamp
          , u.EndTimestamp
          , x.RunElapsedSecs
          , x.TotalRowsConsumed
          , x.TotalRowsProduced
       FROM DSODB.JobRunUsage u,
            XMLTable('/rows/snap' passing u.ResourceInfo
                     columns RunElapsedSecs    NUMBER(11,0) path '@e'
                           , TotalRowsConsumed NUMBER(19,0) path '@c'
                           , TotalRowsProduced NUMBER(19,0) path '@p'  
                    ) x;

CREATE TABLE DSODB.DataLocator (
     LOCATORID              NUMBER(19, 0)  NOT NULL        
   , CreationTimestamp      TIMESTAMP      NOT NULL
   , ComputerName           NVARCHAR2(80)  NOT NULL
   , SoftwareProductName    NVARCHAR2(60)  NOT NULL
   , DataStoreSubClass      NVARCHAR2(20)  NOT NULL
   , DataStoreName          NVARCHAR2(80)  NOT NULL
   , DataSchemaSubClass     NVARCHAR2(20)  NOT NULL
   , DataSchemaName         NVARCHAR2(80)  NOT NULL
   , DataCollectionSubClass NVARCHAR2(20)  NOT NULL
   , DataCollectionName     NVARCHAR2(80)  NOT NULL
   , PRIMARY KEY (LOCATORID)
   , CONSTRAINT realkeyDataLocator UNIQUE (ComputerName, SoftwareProductName,
                                           DataStoreSubClass, DataStoreName,
                                           DataSchemaSubClass, DataSchemaName,
                                           DataCollectionSubClass, DataCollectionName)
)tablespace dsodbspace;

CREATE TABLE DSODB.JobStage (
     STAGEID              NUMBER(19, 0)    NOT NULL      
   , JOBID                NUMBER(19, 0)    NOT NULL
   , StageName            NVARCHAR2(80)    NOT NULL   -- not 255 else key too long --
   , ContainerPath        NVARCHAR2(255)   NOT NULL
   , StageDescription     NCLOB
   , StageTypeName        NVARCHAR2(40)    NOT NULL
   , PRIMARY KEY (STAGEID)
   , CONSTRAINT realkeyJobStage UNIQUE (JOBID, StageName, ContainerPath)
   , CONSTRAINT jobJobStage     FOREIGN KEY (JOBID) REFERENCES DSODB.JobExec(JOBID) ON DELETE CASCADE
)tablespace dsodbspace;

CREATE TABLE DSODB.JobRunStage (
     RUNID                NUMBER(19, 0)    NOT NULL
   , STAGEID              NUMBER(19, 0)    NOT NULL
   , StageStartTimestamp  TIMESTAMP
   , StageEndTimestamp    TIMESTAMP
   , LastUpdateTimestamp  TIMESTAMP        NOT NULL
   , ElapsedRunSecs       NUMBER(11, 0)
   , StageStatus          CHAR(3)          NOT NULL
   , NumInstances         NUMBER(11, 0)
   , InstancePidList      NVARCHAR2(512)
   , InstanceCPUList      NVARCHAR2(512)
   , TotalCPU             NUMBER(11, 0)
   , CONSTRAINT realkeyJobRunStage UNIQUE (RUNID, STAGEID)
   , CONSTRAINT runJobRunStage     FOREIGN KEY (RUNID)   REFERENCES DSODB.JobRun(RUNID) ON DELETE CASCADE
   , CONSTRAINT stageJobRunStage   FOREIGN KEY (STAGEID) REFERENCES DSODB.JobStage(STAGEID) ON DELETE CASCADE
)tablespace dsodbspace;

CREATE TABLE DSODB.JobLink (
     LINKID               NUMBER(19, 0)    NOT NULL       
   , FROMSTAGEID          NUMBER(19, 0)    -- NB: One other of these --
   , TOSTAGEID            NUMBER(19, 0)    --     may be null        --
   , LinkName             NVARCHAR2(80)    NOT NULL
   , LinkType             CHAR(3)          NOT NULL
   , LinkDescription      NCLOB
   , IsSource             NUMBER(6, 0)
   , IsTarget             NUMBER(6, 0)
   , PRIMARY KEY (LINKID)
   , CONSTRAINT fromstageJobLink FOREIGN KEY (FROMSTAGEID) REFERENCES DSODB.JobStage(STAGEID) ON DELETE CASCADE
   , CONSTRAINT tostageJobLink   FOREIGN KEY (TOSTAGEID)   REFERENCES DSODB.JobStage(STAGEID) ON DELETE CASCADE
)tablespace dsodbspace;

CREATE TABLE DSODB.JobRunLink (
     RUNID                NUMBER(19, 0)    NOT NULL
   , LINKID               NUMBER(19, 0)    NOT NULL
   , LOCATORID            NUMBER(19, 0)
   , LastUpdateTimestamp  TIMESTAMP        NOT NULL
   , InstanceRowsList     NVARCHAR2(512)
   , TotalRows            NUMBER(19, 0)
   , CONSTRAINT realkeyJobRunLink UNIQUE (RUNID, LINKID)
   , CONSTRAINT runJobRunLink     FOREIGN KEY (RUNID)     REFERENCES DSODB.JobRun(RUNID)          ON DELETE CASCADE
   , CONSTRAINT linkJobRunLink    FOREIGN KEY (LINKID)    REFERENCES DSODB.JobLink(LINKID)        ON DELETE CASCADE
   , CONSTRAINT locatorJobRunLink FOREIGN KEY (LOCATORID) REFERENCES DSODB.DataLocator(LOCATORID)
)tablespace dsodbspace;

--
-- DataStage operations database: create reference/lookup tables & views
--

WHENEVER SQLERROR EXIT 1;

CREATE TABLE DSODB.MasterRef (
     Enumeration          VARCHAR2(20)      NOT NULL
   , Code                 CHAR(3)           NOT NULL
   , Name                 VARCHAR2(32)      NOT NULL
   , Description          NVARCHAR2(255)
   , PRIMARY KEY (Enumeration, Code)
)tablespace dsodbspace;

CREATE VIEW DSODB.JobTypeRef AS
SELECT
   Code AS JobTypeCode
 , Name AS JobTypeName
 , Description AS JobTypeDescription
FROM
   DSODB.MasterRef
WHERE
   Enumeration = 'JobType';

CREATE VIEW DSODB.RunTypeRef AS
SELECT
   Code AS RunTypeCode
 , Name AS RunTypeName
 , Description AS RunTypeDescription
FROM
   DSODB.MasterRef
WHERE
   Enumeration = 'RunType';

CREATE VIEW DSODB.RunMajorStatusRef AS
SELECT
   Code AS MajorStatusCode
 , Name AS MajorStatusName
 , Description AS MajorStatusDescription
FROM
   DSODB.MasterRef
WHERE
   Enumeration = 'RunMajorStatus';

CREATE VIEW DSODB.RunMinorStatusRef AS
SELECT
   Code AS MinorStatusCode
 , Name AS MinorStatusName
 , Description AS MinorStatusDescription
FROM
   DSODB.MasterRef
WHERE
   Enumeration = 'RunMinorStatus';

CREATE VIEW DSODB.LogTypeRef AS
SELECT
   Code AS LogTypeCode
 , Name AS LogTypeName
 , Description AS LogTypeDescription
FROM
   DSODB.MasterRef
WHERE
   Enumeration = 'LogType';

CREATE VIEW DSODB.StageStatusRef AS
SELECT
   Code AS StageStatusCode
 , Name AS StageStatusName
 , Description AS StageStatusDescription
FROM
   DSODB.MasterRef
WHERE
   Enumeration = 'StageStatus';

CREATE VIEW DSODB.LinkTypeRef AS
SELECT
   Code AS LinkTypeCode
 , Name AS LinkTypeName
 , Description AS LinkTypeDescription
FROM
   DSODB.MasterRef
WHERE
   Enumeration = 'LinkType';

INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('Version', 'SCH', '2', 'Schema version from 2011-03-21');

INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('JobType', 'SRV', 'Server', 'Server job');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('JobType', 'PAR', 'Parallel', 'Parallel job');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('JobType', 'SEQ', 'Sequence', 'Job sequence');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('JobType', '?!?', 'Invalid value', 'A non-numeric value was found');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('JobType', '???', 'Unknown value', 'An out-of-range numeric value was found');

INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunType', 'RUN', 'Run', 'Normal run mode');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunType', 'VAL', 'Validate', 'Validation-only run');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunType', 'RES', 'Reset', 'Reset run');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunType', '?!?', 'Invalid value', 'A non-numeric value was found');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunType', '???', 'Unknown value', 'An out-of-range numeric value was found');


INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMajorStatus', 'SUB', 'Submitted', 'Run submitted but not yet started');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMajorStatus', 'STA', 'Started', 'Run has started');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMajorStatus', 'FIN', 'Finished', 'Run has finished');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMajorStatus', '?!?', 'Invalid value', 'A non-numeric value was found');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMajorStatus', '???', 'Unknown value', 'An out-of-range numeric value was found');


INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'QUE', 'Submitted - queued', 'Run is waiting in a queue');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'STR', 'Submitted - starting', 'Run is about to start');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'RUN', 'Running - no warnings', 'Running, and has not yet logged any warning or fatal messages');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'RNW', 'Running - with warnings', 'Running, and has logged at least one warning message but no fatals');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'RNF', 'Running - with fatals', 'Running, and has logged at least one fatal message');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'RNS', 'Running - stop requested', 'Running, but has received an external stop request');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'FOK', 'Finished - OK', 'Run has finished without logging any warning or fatal messages');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'FWW', 'Finished - with warnings', 'Run has finished and logged at least one warning message but no fatals');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'FWF', 'Finished - aborted', 'Run has finished and logged at least one fatal message');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'CRA', 'Finished - crashed', 'Run finished unexpectedly');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'STP', 'Finished - stopped by request', 'Run terminated due to external stop request');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', 'SYN', 'Finished - synchronized', 'Run forcibly set to finished state by maintenance check');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', '?!?', 'Invalid value', 'A non-numeric value was found');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('RunMinorStatus', '???', 'Unknown value', 'An out-of-range value was found');

INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType',  'INF', 'Info', 'Informational message only');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType',  'WAR', 'Warning', 'Warning message');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType',  'FAT', 'Fatal', 'Fatal message - job will be aborted');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType',  'REJ', 'Reject', 'Stage reject message');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType',  'CTL', 'Control', 'Job started/finished');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType',  'PUR', 'Purge', 'Previous messages in the log file have been purged');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType',  'RUN', 'RunJob', 'Job run started under control of sequence, or returning to caller');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType', '?!?', 'Invalid value', 'A non-numeric value was found');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LogType', '???', 'Unknown value', 'An out-of-range value was found');

INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('StageStatus',  'RDY', 'Ready', 'Stage is ready to run but has not yet started');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('StageStatus',  'RUN', 'Running', 'Stage is running');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('StageStatus',  'FIN', 'Finished', 'Stage has finished');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('StageStatus', '?!?', 'Invalid value', 'A non-numeric value was found');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('StageStatus', '???', 'Unknown value', 'An out-of-range value was found');

INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LinkType',  'PRI', 'Primary', 'Primary link between two stages');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LinkType',  'REF', 'Reference', 'Reference input to a stage');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LinkType',  'REJ', 'Reject', 'Reject output from a stage');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LinkType', '?!?', 'Invalid value', 'A non-numeric value was found');
INSERT INTO DSODB.MasterRef (Enumeration, Code, Name, Description)VALUES ('LinkType', '???', 'Unknown value', 'An out-of-range numeric value was found');

--
-- DataStage operations database: create system resource tables & views
--

-----------------------------------------------------
-- SNAPSHOT: Last monitored state of each resource --
-----------------------------------------------------

WHENEVER SQLERROR EXIT 1;

CREATE TABLE DSODB.ResourceSnap (
     HOSTID               NUMBER(19, 0)    NOT NULL 
   , HEAD_HOSTID          NUMBER(19, 0)    NOT NULL 
   , LastUpdateTimestamp  TIMESTAMP        NOT NULL
   , CPUPctUser           DECIMAL(4,1)
   , CPUPctPrivileged     DECIMAL(4,1)
   , CPUPctIdle           DECIMAL(4,1)
   , CPUPctStolen         DECIMAL(4,1)
   , CPUPctIOWait         DECIMAL(4,1)
   , ProcNumRunning       NUMBER(11, 0)
   , ProcNumWaiting       NUMBER(11, 0)
   , ProcNumSleeping      NUMBER(11, 0)
   , ProcNumZombied       NUMBER(11, 0)
   , ProcNumBlocked       NUMBER(11, 0)
   , MemFreeKBPhysical    NUMBER(11, 0)
   , MemFreeKBVirtual     NUMBER(11, 0)
   , PageNumIn            NUMBER(11, 0)
   , PageNumOut           NUMBER(11, 0)
   , PageNumInterrupts    NUMBER(11, 0)
   , PageNumSwitches      NUMBER(11, 0)
   , DiskSnap             XMLTYPE
   , PRIMARY KEY (HOSTID, HEAD_HOSTID)
   , CONSTRAINT host1ResourceSnap FOREIGN KEY (HOSTID)      REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
   , CONSTRAINT host2ResourceSnap FOREIGN KEY (HEAD_HOSTID) REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
)tablespace dsodbspace XMLTYPE DiskSnap STORE AS CLOB;

-- Following is a view on ResourceSnap for non-disk stats --

CREATE VIEW DSODB.ResourceSnapSystem AS 
            select r.HOSTID
                 , r.HEAD_HOSTID
                 , r.LastUpdateTimestamp
                 , r.CPUPctUser
                 , r.CPUPctPrivileged
                 , r.CPUPctIdle
                 , r.CPUPctStolen
                 , r.CPUPctIOWait
                 , r.ProcNumRunning
                 , r.ProcNumWaiting
                 , r.ProcNumSleeping
                 , r.ProcNumZombied
                 , r.ProcNumBlocked
                 , r.MemFreeKBPhysical
                 , r.MemFreeKBVirtual
                 , r.PageNumIn
                 , r.PageNumOut
                 , r.PageNumInterrupts
                 , r.PageNumSwitches
           from DSODB.ResourceSnap r;

-- Format of XMLTYPE in DiskSnap:                                  --
-- <dsn>                                                       --
--    <dsk p="X" t="0" f="0" />                                --
--    ....                                                     --
-- </dsn>                                                      --

-- Following lets you see the XML in DiskSnap as table of file system stats --

CREATE VIEW DSODB.ResourceSnapDisks AS 
   select r.HOSTID
        , r.HEAD_HOSTID
        , r.LastUpdateTimestamp
        , x.DiskPathMonitored
        , x.DiskTotalKB
        , x.DiskFreeKB 
   from DSODB.ResourceSnap r,
  xmltable('/dsn/dsk' passing r.DiskSnap
                 columns DiskPathMonitored  NVARCHAR2(255) path '@p'
                       , DiskTotalKB        NUMBER(19, 0)  path '@t'
                       , DiskFreeKB         NUMBER(19, 0)  path '@f'
                ) x;

-----------------------------------------------------------
-- USAGE: History of each resource, aggregated over time --
-----------------------------------------------------------

CREATE TABLE DSODB.ResourceUsage (
     StartTimestamp       TIMESTAMP        NOT NULL
   , HOSTID               NUMBER(19, 0)    NOT NULL 
   , HEAD_HOSTID          NUMBER(19, 0)    NOT NULL 
   , EndTimestamp         TIMESTAMP        NOT NULL
   , NumSamples           NUMBER(11, 0)
   , CPUPctUserAvg        DECIMAL(4,1)
   , CPUPctUserMax        DECIMAL(4,1)
   , CPUPctUserMin        DECIMAL(4,1)
   , CPUPctPrivilegedAvg  DECIMAL(4,1)
   , CPUPctPrivilegedMax  DECIMAL(4,1)
   , CPUPctPrivilegedMin  DECIMAL(4,1)
   , CPUPctIdleAvg        DECIMAL(4,1)
   , CPUPctIdleMax        DECIMAL(4,1)   
   , CPUPctIdleMin        DECIMAL(4,1)   
   , CPUPctStolenAvg      DECIMAL(4,1)    
   , CPUPctStolenMax      DECIMAL(4,1) 
   , CPUPctStolenMin      DECIMAL(4,1) 
   , CPUPctIOWaitAvg      DECIMAL(4,1)
   , CPUPctIOWaitMax      DECIMAL(4,1)
   , CPUPctIOWaitMin      DECIMAL(4,1)
   , ProcNumRunningAvg    NUMBER(11, 0)
   , ProcNumRunningMax    NUMBER(11, 0)
   , ProcNumRunningMin    NUMBER(11, 0)
   , ProcNumWaitingAvg    NUMBER(11, 0)
   , ProcNumWaitingMax    NUMBER(11, 0)
   , ProcNumWaitingMin    NUMBER(11, 0)
   , ProcNumSleepingAvg   NUMBER(11, 0)
   , ProcNumSleepingMax   NUMBER(11, 0)
   , ProcNumSleepingMin   NUMBER(11, 0)
   , ProcNumZombiedAvg    NUMBER(11, 0)
   , ProcNumZombiedMax    NUMBER(11, 0)
   , ProcNumZombiedMin    NUMBER(11, 0)
   , ProcNumBlockedAvg    NUMBER(11, 0)
   , ProcNumBlockedMax    NUMBER(11, 0)
   , ProcNumBlockedMin    NUMBER(11, 0)
   , MemFreeKBPhysicalAvg NUMBER(11, 0)
   , MemFreeKBPhysicalMax NUMBER(11, 0)
   , MemFreeKBPhysicalMin NUMBER(11, 0)
   , MemFreeKBVirtualAvg  NUMBER(11, 0)
   , MemFreeKBVirtualMax  NUMBER(11, 0)
   , MemFreeKBVirtualMin  NUMBER(11, 0)
   , PageNumInAvg         NUMBER(11, 0)
   , PageNumInMax         NUMBER(11, 0)
   , PageNumInMin         NUMBER(11, 0)
   , PageNumOutAvg        NUMBER(11, 0)
   , PageNumOutMax        NUMBER(11, 0)
   , PageNumOutMin        NUMBER(11, 0)
   , PageNumInterruptsAvg NUMBER(11, 0)
   , PageNumInterruptsMax NUMBER(11, 0)
   , PageNumInterruptsMin NUMBER(11, 0)
   , PageNumSwitchesAvg   NUMBER(11, 0)
   , PageNumSwitchesMax   NUMBER(11, 0)
   , PageNumSwitchesMin   NUMBER(11, 0)
   , DiskUsage            XMLTYPE
   , PRIMARY KEY (StartTimestamp, HOSTID, HEAD_HOSTID)
   , CONSTRAINT host1ResourceUsage FOREIGN KEY (HOSTID)      REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
   , CONSTRAINT host2ResourceUsage FOREIGN KEY (HEAD_HOSTID) REFERENCES DSODB.Host(HOSTID) ON DELETE CASCADE
)tablespace dsodbspace XMLTYPE DiskUsage STORE AS CLOB;

-- Following is a view on ResourceUsage for non-disk stats --

CREATE VIEW DSODB.ResourceUsageSystem AS 
            select r.StartTimestamp
                 , r.HOSTID
                 , r.HEAD_HOSTID
                 , r.EndTimestamp
                 , r.NumSamples
                 , r.CPUPctUserAvg
                 , r.CPUPctPrivilegedAvg
                 , r.CPUPctIdleAvg 
                 , r.CPUPctStolenAvg   
                 , r.CPUPctIOWaitAvg
                 , r.CPUPctUserMax
                 , r.CPUPctPrivilegedMax
                 , r.CPUPctIdleMax    
                 , r.CPUPctStolenMax  
                 , r.CPUPctIOWaitMax 
                 , r.CPUPctUserMin
                 , r.CPUPctPrivilegedMin
                 , r.CPUPctIdleMin    
                 , r.CPUPctStolenMin 
                 , r.CPUPctIOWaitMin
                 , r.ProcNumRunningAvg
                 , r.ProcNumWaitingAvg
                 , r.ProcNumSleepingAvg
                 , r.ProcNumZombiedAvg
                 , r.ProcNumBlockedAvg
                 , r.ProcNumRunningMax
                 , r.ProcNumWaitingMax
                 , r.ProcNumSleepingMax
                 , r.ProcNumZombiedMax
                 , r.ProcNumBlockedMax
                 , r.ProcNumRunningMin
                 , r.ProcNumWaitingMin
                 , r.ProcNumSleepingMin
                 , r.ProcNumZombiedMin
                 , r.ProcNumBlockedMin
                 , r.MemFreeKBPhysicalAvg
                 , r.MemFreeKBVirtualAvg
                 , r.MemFreeKBPhysicalMax
                 , r.MemFreeKBVirtualMax
                 , r.MemFreeKBPhysicalMin
                 , r.MemFreeKBVirtualMin
                 , r.PageNumInAvg
                 , r.PageNumOutAvg
                 , r.PageNumInterruptsAvg
                 , r.PageNumSwitchesAvg
                 , r.PageNumInMax
                 , r.PageNumOutMax
                 , r.PageNumInterruptsMax
                 , r.PageNumSwitchesMax
                 , r.PageNumInMin
                 , r.PageNumOutMin
                 , r.PageNumInterruptsMin
                 , r.PageNumSwitchesMin
            from DSODB.ResourceUsage r;

-- Format of XML in DiskInfo:                           --
-- <dus>                                                --
--    <dsk p="X" t="0" af="0" xf="0"nf="0">             --
--    ....                                              --
-- </dus>                                               --

-- Following lets you see the XML in DiskUsage as table of file system stats --

CREATE VIEW DSODB.ResourceUsageDisks AS 
   select r.StartTimestamp
        , r.HOSTID
        , r.HEAD_HOSTID
        , r.EndTimestamp
        , r.NumSamples
        , x.DiskPathMonitored
        , x.DiskTotalKB
        , x.DiskFreeKBAvg
        , x.DiskFreeKBMax
        , x.DiskFreeKBMin
   from DSODB.ResourceUsage r,
        xmltable('/dus/dsk' passing r.DiskUsage
                 columns DiskPathMonitored    NVARCHAR2(255) path '@p'
                       , DiskTotalKB          NUMBER(19, 0)  path '@t'
                       , DiskFreeKBAvg        NUMBER(19, 0)  path '@af'
                       , DiskFreeKBMax        NUMBER(19, 0)  path '@xf'
                       , DiskFreeKBMin        NUMBER(19, 0)  path '@nf'
                ) x;

--           Licensed Materials - Property of IBM
--           Copyright IBM Corp. 2012, 2014
--------------------------------------------------------------------------------
-- IBM InfoSphere DataStage operations database creation  
-- for Oracle
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 
-- This file includes the definitions for all the user defined functions required
-- for the DataStage operations database access
--
-- User Defined Functions
-- ======================

-- Return a numeric value for the given major run status for ordering purposes
--
CREATE OR REPLACE FUNCTION DSODB.RunMajorStatus(runStatus IN VARCHAR)
RETURN INTEGER
DETERMINISTIC
IS
BEGIN
    RETURN
        CASE 
           WHEN runStatus = 'FIN' THEN 2
           WHEN runStatus = 'STA' THEN 1
           ELSE 0
        END;
END;
/

-- Return a numeric value for the given minor run status for ordering purposes
--
CREATE OR REPLACE FUNCTION DSODB.RunMinorStatus(runStatus IN VARCHAR)
RETURN INTEGER
DETERMINISTIC
IS
BEGIN
    RETURN 
        CASE 
            WHEN runStatus = 'FOK' THEN 11
            WHEN runStatus = 'FWF' THEN 8
            WHEN runStatus = 'FWW' THEN 9
            WHEN runStatus = 'RUN' THEN 5
            WHEN runStatus = 'QUE' THEN 0
            WHEN runStatus = 'STR' THEN 1
            WHEN runStatus = 'RNF' THEN 2
            WHEN runStatus = 'RNW' THEN 3
            WHEN runStatus = 'RNS' THEN 4
            WHEN runStatus = 'CRA' THEN 6
            WHEN runStatus = 'SYN' THEN 7
            WHEN runStatus = 'STP' THEN 10
            ELSE 10
        END;
END;
/
    
-- Return a numeric value for the category of a given run based on its current state for ordering purposes
--
CREATE OR REPLACE FUNCTION DSODB.RunCategory(isWebEnabled SMALLINT, runMajorStatus VARCHAR, runMinorStatus VARCHAR)
RETURN INTEGER
DETERMINISTIC
IS
BEGIN
    RETURN 
        CASE 
            WHEN isWebEnabled <> 0 THEN 1
            WHEN runMajorStatus = 'FIN' THEN 4
            WHEN runMajorStatus = 'STA' THEN 3
            WHEN runMajorStatus = 'SUB' THEN 
                CASE
                    WHEN runMinorStatus = 'QUE' THEN 2
                    WHEN runMinorStatus = 'STR' THEN 3
                    ELSE 5
                END
            ELSE 5
        END;
END;
/
   
-- Return a timestamp value for a job run end date, making sure that a null value will be sorted
-- later than any valid date. This is achieved by setting any null run end date to be 30 days in 
-- the future from the current time - this will always be later than a run end non-null value.
--
CREATE OR REPLACE FUNCTION DSODB.RunEndDate(runEndTimestamp IN TIMESTAMP)
RETURN TIMESTAMP
IS
BEGIN
    RETURN
        CASE 
            WHEN runEndTimestamp IS NULL THEN
                current_timestamp + interval '30' day
            ELSE runEndTimestamp
        END;
END;
/

-- Get the number of milliseconds since 0001-01-01 for the given timestamp
--
CREATE OR REPLACE FUNCTION DSODB.GetMilliseconds(thisTime TIMESTAMP)
RETURN INTEGER
DETERMINISTIC
IS
    epochTime TIMESTAMP := TO_TIMESTAMP('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
BEGIN
    RETURN 
        (EXTRACT(DAY FROM (thisTime - epochTime)) * 86400000) + 
        (EXTRACT(HOUR FROM (thisTime - epochTime)) * 3600000) +
        (EXTRACT(MINUTE FROM (thisTime - epochTime)) * 60000) +
        TRUNC(EXTRACT(SECOND FROM (thisTime - epochTime)) * 1000);
END;
/
    
-- Get the offset of a log entry timestamp compared to when the run was created. 
--
CREATE OR REPLACE FUNCTION DSODB.JobRunCreatedOffset(logTimestamp TIMESTAMP, runCreationTimestamp TIMESTAMP)
RETURN INTEGER
DETERMINISTIC
IS
BEGIN
    RETURN 
        CASE 
            WHEN (DSODB.GetMilliseconds(logTimestamp) - DSODB.GetMilliseconds(runCreationTimestamp)) < 0 THEN 0
            ELSE DSODB.GetMilliseconds(logTimestamp) - DSODB.GetMilliseconds(runCreationTimestamp)
        END;
END;
/

-- Get the elapsed time of a job run. For a finished job get the time from the start and end timestamp
-- fields. For a running job, use the current time as the comparitor against the start time.
--
CREATE OR REPLACE FUNCTION DSODB.RunElapsed(runStatus VARCHAR, endTime TIMESTAMP, startTime TIMESTAMP)
RETURN INTEGER
IS
BEGIN
    RETURN 
        CASE 
            WHEN runStatus = 'FIN' THEN 
                CASE 
                    WHEN endTime IS NULL OR startTime IS NULL THEN -1
                    WHEN (DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(startTime)) < 0 THEN 0
                    ELSE DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(startTime)
                END
            WHEN runStatus = 'STA' THEN
                CASE 
                    WHEN startTime IS NULL THEN -1
                    WHEN (DSODB.GetMilliseconds(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)) - DSODB.GetMilliseconds(startTime)) < 0 THEN 0
                    ELSE DSODB.GetMilliseconds(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)) - DSODB.GetMilliseconds(startTime)
                END
            ELSE 0
        END;
END;
/

-- Get the total elapsed time of a job run. For a finished job get the time from the creation and end timestamp
-- fields. For a running job, use the current time as the comparitor against the creation time.
--
CREATE OR REPLACE FUNCTION DSODB.TotalElapsed(runStatus VARCHAR, endTime TIMESTAMP, creationTime TIMESTAMP)
RETURN INTEGER
IS
BEGIN
    RETURN 
        CASE 
            WHEN runStatus = 'FIN' THEN 
                CASE 
                    WHEN endTime IS NULL OR creationTime IS NULL THEN -1
                    WHEN (DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(creationTime)) < 0 THEN 0
                    ELSE DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(creationTime)
                END
            ELSE
                CASE 
                    WHEN creationTime IS NULL THEN -1
                    WHEN (DSODB.GetMilliseconds(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)) - DSODB.GetMilliseconds(creationTime)) < 0 THEN 0
                    ELSE DSODB.GetMilliseconds(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)) - DSODB.GetMilliseconds(creationTime)
                END
        END;
END;
/
    
-- Calculate the rows per second for a job run based on the elapsed time of the run and the number
-- of rows processed.
--
CREATE OR REPLACE FUNCTION DSODB.RowsPerSec(runStatus VARCHAR, endTime TIMESTAMP, startTime TIMESTAMP, jobType VARCHAR, rowCount INTEGER)
RETURN FLOAT
IS
BEGIN
    RETURN 
        CASE 
            WHEN jobType = 'SEQ' THEN -1
            WHEN DSODB.RunElapsed(runStatus, endTime, startTime) > 0 THEN 
                TRUNC(rowCount / TRUNC(DSODB.RunElapsed(runStatus, endTime, startTime) / 1000), 4)
            WHEN DSODB.RunElapsed(runStatus, endTime, startTime) < 0 THEN -1
            ELSE 0
        END;
END;
/

-- Substring the start of the log message text so we can sort by this field.
--
CREATE OR REPLACE FUNCTION DSODB.MessageTextSort(logMsg IN NCLOB)
RETURN VARCHAR
DETERMINISTIC
IS
BEGIN
    RETURN SUBSTR(logMsg, 1, 256);
END;
/


--           Licensed Materials - Property of IBM
--           Copyright IBM Corp. 2013, 2014
--------------------------------------------------------------------------------
-- IBM InfoSphere DataStage operations database index creation 
-- for Oracle
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 
-- This file includes the definitions for all the indexes required
-- for the DataStage operations database access
--
-- Create indexes
-- =============

CREATE INDEX DSODB.JobExecHostID ON DSODB.JobExec (HOSTID);

CREATE INDEX DSODB.JobRunJobID ON DSODB.JobRun (JOBID);

CREATE INDEX DSODB.JobRunUsageRunID ON DSODB.JobRunUsage (RUNID);

CREATE INDEX DSODB.ResourceUsageSTS ON DSODB.ResourceUsage (STARTTIMESTAMP);

CREATE INDEX DSODB.JobRunControlRunID ON DSODB.JobRun (CONTROLLING_RUNID);

CREATE INDEX DSODB.JobRunConfigID ON DSODB.JobRun (CONFIGID);

CREATE INDEX DSODB.JobRunLogRunID ON DSODB.JobRunLog (RUNID);

CREATE INDEX DSODB.JobRunParamsRunID ON DSODB.JobRunParams (RUNID);

CREATE INDEX DSODB.PConfigHostID ON DSODB.ParallelConfig (HOSTID);

CREATE INDEX DSODB.JobRunStageRunID ON DSODB.JobRunStage (RUNID);

CREATE INDEX DSODB.JobRunStageStageID ON DSODB.JobRunStage (STAGEID);

CREATE INDEX DSODB.JobRunLinkRunID ON DSODB.JobRunLink (RUNID);

CREATE INDEX DSODB.JobRunLinkLinkID ON DSODB.JobRunLink (LINKID);

CREATE INDEX DSODB.JobStageJobID ON DSODB.JobStage (JOBID);


2:配置 Operation 数据库监控目标

[root@report-etl1 DSODB]# cat DSODBConnect.cfg
#           Licensed Materials - Property of IBM
#           Copyright IBM Corp. 2012

# DataStage Operations Database Connection Definition
# ===================================================
# The following parameters define the connection to the Operations Database.

# "DBTYPE" must be set to identify which database manager is in use.
DBTYPE=ORACLE

# "Driver" is the Java class name for the JDBC driver to be used.
Driver=com.ibm.isf.jdbc.oracle.OracleDriver

# "Schema" is the user selected schema name that was provided during install.
Schema=dsodb

# "JAR" specifies the names of the jar files that are needed to run the driver.
# The value is a semi-colon separated list of the jar file names required (or
# specified as separate multiple "JAR=" properties if required)
# The jar files must exist in the ASBNode/lib/java subdirectory of the
# InformationServer installation directory on this Server system.
JAR=ISoracle.jar;

# "URL" is used to identify the database concerned.
URL=jdbc:ibm:oracle://report-ds:1521;SID=DSDB

# The following specify the username and password to connect to the DSODB schema.
Username=DSODB
Password=DSODB

3.配置 Operation 数据库监控目标

[root@report-etl1 DSODB]# cat DSODBConfig.cfg
#           Licensed Materials - Property of IBM
#           (c) Copyright IBM Corp. 2010, 2014
#
# DataStage Operations Database Monitor Configuration
# ===================================================
# Lines in this file are either comments, introduced by a # sign like this,
# or of the form "key=value". Key lines may be commented out below. If this file is edited,
# the data collection system must be stopped and restarted before changes take effect.
#
# IMPORTANT:
# Ensure when making changes to this file that it is saved with the encoding set to
# UTF-8. Please be aware if the encoding isn't set to UTF-8 this may produce undesired
# behaviour.

# The following switches the whole data collection system on if set to 1, or off if 0.
# A setting of 0 cannot be overridden at project level -
# however individual projects can be switched off by setting this to 0
# in a copy of this file placed in the project's directory.
DSODBON=0

# Job Log Events
# ==============
# Normally, all Fatal log messages, Control messages, and the first N Warning messages
# of a run will be captured, except as modified by the following settings.
# Note: The message severity is looked at AFTER any Message Handlers have been invoked,
# which may adjust it or even discard the message altogether.

# The following specifies how many warning messages are to be captured for each run.
# Unless overridden by a "must capture" setting below, after this many warning log
# messages have been turned into events any further warnings will not be sent to the ODB.
# Default is 10. The value is capped at 1000.
# MaxWarnings=10

# The following key can be repeated, to specify any log messages that MUST be captured
# in the ODB regardless of message type or whether the limit for that type has been reached.
# Each value is a message ID or comma-delimited list of IDs associated with a log message.
# IDs should only appear once in these lists or the IgnoreLog lists.
# NOTE: IDs like "DSTAGE_XXX_X_nnnn" will actually be stored as "IIS-DSTAGE-XXX-X-nnnn"
#       in the database; in the lists below you may specify either format.
# (Entries in IgnoreLog take precedence over entries in CaptureLog.)
# IDs can be followed by a "content type" string of up to 20 chars, separated from the ID
# by a slash. If present, these will be used to tag the log events in the database.
# CaptureLog=IIS-DSTAGE-RUN-I-0126/ENV_VARS
# CaptureLog=IIS-DSTAGE-RUN-I-0470/OSH_SCRIPT,IIS-DSEE-TFSU-00016/OSH_SCORE_DUMP

# The following key can be repeated, to specify a log message that should NOT be captured
# even if its type is Fatal or Warning.
# Each value is a message ID or comma-delimited list of IDs associated with a log message.
# NOTE: IDs like "DSTAGE_XXX_X_nnnn" will actually be stored as "IIS-DSTAGE-XXX-X-nnnn"
#       in the database; in the lists below you may specify either format.
# IDs should only appear once in these lists or the CaptureLog lists.
# (Entries in IgnoreLog take precedence over entries in CaptureLog.)
# The strings following the / separator are purely descriptive in this case.
IgnoreLog=IIS-DSTAGE-RUN-I-0180/Attempting to Cleanup after ABORT
IgnoreLog=IIS-DSEE-TFSR-00019/Could not check all operators because of previous error(s)
IgnoreLog=IIS-DSEE-TCOS-00029/Creation of a step finished with status = FAILED.

# Job Run Statistics Monitoring
# ============================
# Specifies the time interval in seconds that should elapse between successive events
# that update the overall run stats, such as total rows read/written. Default=10.
# UpdateIntSecs=10

# The following key switches on or off the capturing of stage-level and link-level stats,
# and references to DataLocators, at the end of each job run. (1 = on, 0 = default/off)
# MonitorLinks=1

# Custom Job Parameters
# =====================
# The values of named job parameters can be extracted and used to tag job runs via custom fields.
# To use this feature, un-comment one or more of the lines below, and replace "paramname" with
# the actual job parameter name that is going to represent the appropriate custom field.
# CustomBatchId=paramname1
# CustomJobType=paramname2
# CustomContact=paramname3
# CustomSequence=paramname4
# CustomField1=paramname5
# CustomField2=paramname6

#################################################################
# The keys that follow are ignored in project-level config files.
#################################################################

# The following defines the directory where events files are to be landed.
# It must be an absolute path, and must exist, with write access.
# NOTE that the path MUST use forward-slashes as separators EVEN ON A WINDOWS SYSTEM.
# It will be ignored at project level - only the system level setting is read.
# If omitted, the default is the "events" sub-directory of the DSODB home directory.
# EventsDir=C:/IBM/InformationServer/Server/DSODB/events

# The following can be set to a number > 0 to enable tracing, and is the maximum
# number of lines to be written to the trace file (between 1000 and 100000).
# (Note that any number outside that range still switches tracing on.)
# It will be ignored at project level - only the system level setting is read.
# A file "JobRuntime.log" will be written to the "logs" sub-directory of DSODB home.
# By default trace monitoring is disabled.
# Tracing can adversely affect performance and should not be enabled except for problem diagnosis.
# TraceMax=1000

##################################################################
[OPTIONS FOR RESOURCE MONITORING FOLLOW - DO NOT REMOVE THIS LINE]
##################################################################

# Job Run Synchronization options
# ===============================
# The following specifies how often automatic validation of currently running jobs
# is performed. At this interval, any jobs running will be checked that their running
# status is correct, enabling any defunct jobs to be marked as no longer running.
# This is not something that is expected to be performed frequently.
# The value is specified in minutes, with a default of 60 (minimum 10)
# Setting this value to 0 will turn off this functionality.
# JobRunCheckInterval=60

# Job Run Usage Parameters
# ========================
# Set the following property to 1 to enable the collection of job run resource
# usage data (the default), or 0 to disable it.
# JobRunUsage=1

# The following property controls the number of snapshot values that are included
# in a single row before a new one is started.
# The default is 15 per row.
# JobRunAggSnaps=15

# System Resource Monitor - enable/disable
# ========================================
# The following switches on the collection of system resource data if set to 1 (the default),
# or switches it off if 0. If set to 0, all options below related to resource tracking are ignored.
# ResourceMonitor=1

# Resource Tracking - connections
# ===============================
# The following specifies the port number that the resource tracking application (ResTrackApp)
# will use on the local system. The default is 13450. (See also ODBQAPPPORTNUM below.)
# ResourcePortNum=13450

# The following specifies the port number that the resource tracking application (ResTrackApp)
# will use on all remote nodes. The default is 13450.
# ResourceRemotePortNum=13450

# The following specifies the name of a remote node whose resources are to be monitored.
# (The local system is always monitored if the resource tracker is running.)
# The name given for each node should match that used in Parallel Job config files.
# This property can be repeated any number of times to include multiple remote nodes.
# ResourceNode=xxxxxx
# ResourceNode=yyyyyy

# NOTE: Where supplying non-ANSI characters below, ensure file encoding is set to UTF-8
# Please see the full comment at the top on this subject for more details.

# The following specifies a locally mounted file system to be monitored.
# This property can be repeated any number of times to specify multiple file systems.
# ResourceLocalFS=/localfilesystemA
# ResourceLocalFS=/localfilesystemB

# The following specifies a file system mounted on a remote node to be monitored.
# Specify the node name and file system path separated by a plus (+)
# The remote node name must match that specified in the corresponding ResourceNode entry above.
# This property can be repeated any number of times to specify multiple file systems.
# ResourceRemoteFS=node1+/remotefilesystem
# ResourceRemoteFS=node2+/remotefilesystem

# Resource Tracking - polling options
# ===================================
# The following specifies how often a resource snapshot is taken (in seconds).
# The default value is 10.
# ResourcePollPeriod=10

# The following specifies how many snapshots are taken before an aggregated record of those
# values is stored. The default value is 6.
# Therefore, using the default values for poll period and sample size, an aggregated record
# will be written every 60 seconds (10 * 6).
# ResourceSampleSize=6

# The following specifies whether to always store aggregated resource usage data. If set to 1
# aggregated data will always be stored. If the value is set to 0, then resource usage data
# will only be stored while there is any DataStage job activity.
# This avoids storing large numbers of records when there is no such activity.
# The default value is 1.
# ResourceAllAggregatedUsage=1

# The following property is used when a user is not storing all aggregated resource usage,
# but only when there is any job activity. This value defines how many aggregated snapshots
# are automatically stored before and after any job activity has been detected.
# This number of aggregated values will be stored before a check for any job activity is made again.
# The time period covered will be this value multiplied by the aggregated snapshot time.
# The default value is 10. Using the defaults this means that the minimum time stored
# around any job activity will be 10 minutes (= 10 * (10 * 6) seconds).
# ResourceAggRunPollPeriod=10

# The following property is used when a user is not storing all aggregated resource usage,
# but only when there is any job activity. This value defines how often a check is made
# for whether there is any job activity if there has been no recent job activity.
# Snapshot data will always be stored according to the ResourceAggRunPollPeriod property above,
# but this property will specify how often the activity check is made.
# The time period will be this value multiplied by the aggregated snapshot time.
# The default value is 1. Using the defaults this means that the activity check
# will be made every 1 minute (= 1 * (10 * 6) seconds).
# This value MUST be less than the value for ResourceAggRunPollPeriod.
# ResourceAggNonRunPollPeriod=1

# OpsConsole - connections
# ========================
# Following sets the port number used by the ODBQueryApp application to service requests from
# the Operations Console. The default is 13451. (See also ResourcePortNum above.)
# ODBQAPPPORTNUM=13451

# DataStage Workload Manager Configuration
# ===================================================

# The following enables workload management if set to 1, or disabled if 0.
WLMON=0

# The following allows a job to run outside of WLM if communication between the DataStage runtime and WLM failed.
# A setting of 0 will stop the job if communication with the WLM failed.
# A setting of 1 will not send the job to the WLM. It will run immediately.
WLM_CONTINUE_ON_COMMS_ERROR=0

# The following sends a job to the default queue if the queue specified is no longer valid.
# A setting of 0 will stop the job if the queue specified in invalid
# A setting of 1 will send the job to the default WLM queue.
WLM_CONTINUE_ON_QUEUE_ERROR=0

# The following specifies the time a job will wait on the pending queue.
# If this time has been exceeded, the job will be stopped and removed from the queue.
# A value of 0 means do not time out.
WLM_QUEUE_WAIT_TIMEOUT=0



[root@report-etl1 DSODB]#

4.检测设置 – 错误配置显示

[root@ips156 bin]# ./DSAppWatcher.sh -test 
 DSODB is turned ON in the DSODBConfig.cfg file. 
        Link Monitoring is OFF. 
        Job Run Usage is ON. 
        Resource Monitoring is ON. 
 Checking Database Connection: 
        Successfully loaded the database driver. 
        Successfully connected to the database. 
        DB Schema version number: 1 
 Test Successful.

5.管理启动和停止 Operations Console 各服务进程

-sh-4.1$ sh DSAppWatcher.sh -status
which: no DSAppWatcher.sh in (/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin)
AppWatcher:RUNNING
EngMonApp:STOPPED
ODBQueryApp:RUNNING
ResMonApp:RUNNING
-sh-4.1$ sh DSAppWatcher.sh -start

6 实现前台监控:

打赏

觉得本站还不错就打赏一下吧!

支付宝扫一扫打赏

微信扫一扫打赏

本站所有内容均来自于互联网自动采集,如无意侵犯了您的权利,请您发送邮件到admin@chinaetl.com.cn联系管理员删除。
ChinaETL » 配置 IBM InfoSphere DataStage Operations Console