版本：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 实现前台监控：
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。