版本：IBM InfoSphere DataStage V11.3.1
作業系統：linux redhat 6.4
檢視是否注冊DSODB 這個使用者
/ds/informationServer/ASBServer/bin
-sh-$ ./RepositoryAdmin.sh -listRepositories
Sep , :: AM com.ibm.xmeta.pm.orm.impl.LegacySessionManager cleanupLocks
INFO: Clearing abandoned locks () ...
QSSRDDB
dsodb
-sh-$
DatabasePlatform.databaseType=ORACLE
DatabasePlatform.version=g
DatabaseServer.host=dwtest
DatabaseServer.port=
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:;SID=dsdb1;batchPerformanceWorkaround=true
RepositoryConnection.managedDataSourceName=
Tablespace.name=dsodb
--
./RepositoryAdmin.sh -displayRepository -rn QSSRDDB
RepositoryAdmin.sh
DatabasePlatform.databaseType=ORACLE
DatabasePlatform.version=g
DatabaseServer.host=report-ds
DatabaseServer.port=
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:;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 各服務程序
1：建立 Operations 資料庫及其模式和表
--建立表空間：
CREATE TABLESPACE dsodbspace LOGGING DATAFILE '/ds/oracle/oradata/dsdb/datafile/dsodb_01.dbf' SIZE M AUTOEXTEND ON NEXT M 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(, ) NOT NULL
, HostName NVARCHAR2() NOT NULL
, CreatedTimestamp TIMESTAMP NOT NULL
, InstallationDir NVARCHAR2() 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(, ) NOT NULL
, HEAD_HOSTID NUMBER(, ) NOT NULL
, LastCheckedTimestamp TIMESTAMP NOT NULL
, UTCOffsetMins NUMBER(, )
, TimezoneName NVARCHAR2()
, PlatformName NVARCHAR2()
, PlatformVersion NVARCHAR2()
, NumCPUs NUMBER(, )
, CPUModel NVARCHAR2()
, PhysicalMemoryKB NUMBER(, )
, VirtualMemoryKB NUMBER(, )
, 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(, ) NOT NULL
, HOSTID NUMBER(, ) NOT NULL
, CreationTimestamp TIMESTAMP NOT NULL
, NodeListHash NUMBER(, ) 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() path '@pname'
, NumLogicalNodes NUMBER(, ) path '@lnum'
) x;
CREATE TABLE DSODB.JobExec (
JOBID NUMBER(, ) NOT NULL
, HOSTID NUMBER(, ) NOT NULL
, ProjectName NVARCHAR2() NOT NULL
, JobName NVARCHAR2() NOT NULL
, CompilationTimestamp TIMESTAMP NOT NULL
, FolderPath NVARCHAR2()
, JobType CHAR() NOT NULL
, JobShortDescription NCLOB
, JobLongDescription NCLOB
, DesignHostName NVARCHAR2()
, DesignProjectName NVARCHAR2()
, IsMultiInstance NUMBER(, )
, WebServicesEnabled NUMBER(, )
, 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(, ) NOT NULL
, CONTROLLING_RUNID NUMBER(, )
, JOBID NUMBER(, ) NOT NULL
, CONFIGID NUMBER(, )
, InvocationId NVARCHAR2() NOT NULL
, CreationTimestamp TIMESTAMP NOT NULL
, LastUpdateTimestamp TIMESTAMP NOT NULL
, RunStartTimestamp TIMESTAMP
, RunEndTimestamp TIMESTAMP
, ElapsedRunSecs NUMBER(, )
, RunType CHAR() NOT NULL
, RunMajorStatus CHAR() NOT NULL
, RunMinorStatus CHAR() NOT NULL
, UserStatus NVARCHAR2()
, MasterPid NUMBER(, )
, ConductorPid NUMBER(, )
, NumMessagesTotal NUMBER(, )
, NumMessagesWarning NUMBER(, )
, NumMessagesFatal NUMBER(, )
, TotalRowsConsumed NUMBER(, )
, TotalRowsProduced NUMBER(, )
, TotalCPU NUMBER(, )
, ConfigFileName NVARCHAR2()
, TotalPhysicalNodes NUMBER(, )
, TotalLogicalNodes NUMBER(, )
, ISUserName NVARCHAR2()
, DSUserName NVARCHAR2()
, CustomBatchId NVARCHAR2()
, CustomJobType NVARCHAR2()
, CustomContact NVARCHAR2()
, CustomSequence NVARCHAR2()
, CustomField1 NVARCHAR2()
, CustomField2 NVARCHAR2()
, QueueName NVARCHAR2()
, 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(, ) NOT NULL
, EventId NUMBER(, ) NOT NULL
, LogTimestamp TIMESTAMP NOT NULL
, LogType CHAR() NOT NULL
, MessageId NVARCHAR2()
, ContentType NVARCHAR2()
, 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(, ) 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() path '@name'
, ParamValue NVARCHAR2() path '@value'
) x;
CREATE TABLE DSODB.JobRunUsage (
StartTimestamp TIMESTAMP NOT NULL
, RUNID NUMBER(, ) 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(,) path '@e'
, TotalRowsConsumed NUMBER(,) path '@c'
, TotalRowsProduced NUMBER(,) path '@p'
) x;
CREATE TABLE DSODB.DataLocator (
LOCATORID NUMBER(, ) NOT NULL
, CreationTimestamp TIMESTAMP NOT NULL
, ComputerName NVARCHAR2() NOT NULL
, SoftwareProductName NVARCHAR2() NOT NULL
, DataStoreSubClass NVARCHAR2() NOT NULL
, DataStoreName NVARCHAR2() NOT NULL
, DataSchemaSubClass NVARCHAR2() NOT NULL
, DataSchemaName NVARCHAR2() NOT NULL
, DataCollectionSubClass NVARCHAR2() NOT NULL
, DataCollectionName NVARCHAR2() NOT NULL
, PRIMARY KEY (LOCATORID)
, CONSTRAINT realkeyDataLocator UNIQUE (ComputerName, SoftwareProductName,
DataStoreSubClass, DataStoreName,
DataSchemaSubClass, DataSchemaName,
DataCollectionSubClass, DataCollectionName)
)tablespace dsodbspace;
CREATE TABLE DSODB.JobStage (
STAGEID NUMBER(, ) NOT NULL
, JOBID NUMBER(, ) NOT NULL
, StageName NVARCHAR2() NOT NULL -- not else key too long --
, ContainerPath NVARCHAR2() NOT NULL
, StageDescription NCLOB
, StageTypeName NVARCHAR2() 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(, ) NOT NULL
, STAGEID NUMBER(, ) NOT NULL
, StageStartTimestamp TIMESTAMP
, StageEndTimestamp TIMESTAMP
, LastUpdateTimestamp TIMESTAMP NOT NULL
, ElapsedRunSecs NUMBER(, )
, StageStatus CHAR() NOT NULL
, NumInstances NUMBER(, )
, InstancePidList NVARCHAR2()
, InstanceCPUList NVARCHAR2()
, TotalCPU NUMBER(, )
, 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(, ) NOT NULL
, FROMSTAGEID NUMBER(, ) -- NB: One other of these --
, TOSTAGEID NUMBER(, ) -- may be null --
, LinkName NVARCHAR2() NOT NULL
, LinkType CHAR() NOT NULL
, LinkDescription NCLOB
, IsSource NUMBER(, )
, IsTarget NUMBER(, )
, 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(, ) NOT NULL
, LINKID NUMBER(, ) NOT NULL
, LOCATORID NUMBER(, )
, LastUpdateTimestamp TIMESTAMP NOT NULL
, InstanceRowsList NVARCHAR2()
, TotalRows NUMBER(, )
, 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() NOT NULL
, Code CHAR() NOT NULL
, Name VARCHAR2() NOT NULL
, Description NVARCHAR2()
, 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(, ) NOT NULL
, HEAD_HOSTID NUMBER(, ) NOT NULL
, LastUpdateTimestamp TIMESTAMP NOT NULL
, CPUPctUser DECIMAL(,)
, CPUPctPrivileged DECIMAL(,)
, CPUPctIdle DECIMAL(,)
, CPUPctStolen DECIMAL(,)
, CPUPctIOWait DECIMAL(,)
, ProcNumRunning NUMBER(, )
, ProcNumWaiting NUMBER(, )
, ProcNumSleeping NUMBER(, )
, ProcNumZombied NUMBER(, )
, ProcNumBlocked NUMBER(, )
, MemFreeKBPhysical NUMBER(, )
, MemFreeKBVirtual NUMBER(, )
, PageNumIn NUMBER(, )
, PageNumOut NUMBER(, )
, PageNumInterrupts NUMBER(, )
, PageNumSwitches NUMBER(, )
, 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() path '@p'
, DiskTotalKB NUMBER(, ) path '@t'
, DiskFreeKB NUMBER(, ) path '@f'
) x;
-----------------------------------------------------------
-- USAGE: History of each resource, aggregated over time --
-----------------------------------------------------------
CREATE TABLE DSODB.ResourceUsage (
StartTimestamp TIMESTAMP NOT NULL
, HOSTID NUMBER(, ) NOT NULL
, HEAD_HOSTID NUMBER(, ) NOT NULL
, EndTimestamp TIMESTAMP NOT NULL
, NumSamples NUMBER(, )
, CPUPctUserAvg DECIMAL(,)
, CPUPctUserMax DECIMAL(,)
, CPUPctUserMin DECIMAL(,)
, CPUPctPrivilegedAvg DECIMAL(,)
, CPUPctPrivilegedMax DECIMAL(,)
, CPUPctPrivilegedMin DECIMAL(,)
, CPUPctIdleAvg DECIMAL(,)
, CPUPctIdleMax DECIMAL(,)
, CPUPctIdleMin DECIMAL(,)
, CPUPctStolenAvg DECIMAL(,)
, CPUPctStolenMax DECIMAL(,)
, CPUPctStolenMin DECIMAL(,)
, CPUPctIOWaitAvg DECIMAL(,)
, CPUPctIOWaitMax DECIMAL(,)
, CPUPctIOWaitMin DECIMAL(,)
, ProcNumRunningAvg NUMBER(, )
, ProcNumRunningMax NUMBER(, )
, ProcNumRunningMin NUMBER(, )
, ProcNumWaitingAvg NUMBER(, )
, ProcNumWaitingMax NUMBER(, )
, ProcNumWaitingMin NUMBER(, )
, ProcNumSleepingAvg NUMBER(, )
, ProcNumSleepingMax NUMBER(, )
, ProcNumSleepingMin NUMBER(, )
, ProcNumZombiedAvg NUMBER(, )
, ProcNumZombiedMax NUMBER(, )
, ProcNumZombiedMin NUMBER(, )
, ProcNumBlockedAvg NUMBER(, )
, ProcNumBlockedMax NUMBER(, )
, ProcNumBlockedMin NUMBER(, )
, MemFreeKBPhysicalAvg NUMBER(, )
, MemFreeKBPhysicalMax NUMBER(, )
, MemFreeKBPhysicalMin NUMBER(, )
, MemFreeKBVirtualAvg NUMBER(, )
, MemFreeKBVirtualMax NUMBER(, )
, MemFreeKBVirtualMin NUMBER(, )
, PageNumInAvg NUMBER(, )
, PageNumInMax NUMBER(, )
, PageNumInMin NUMBER(, )
, PageNumOutAvg NUMBER(, )
, PageNumOutMax NUMBER(, )
, PageNumOutMin NUMBER(, )
, PageNumInterruptsAvg NUMBER(, )
, PageNumInterruptsMax NUMBER(, )
, PageNumInterruptsMin NUMBER(, )
, PageNumSwitchesAvg NUMBER(, )
, PageNumSwitchesMax NUMBER(, )
, PageNumSwitchesMin NUMBER(, )
, 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() path '@p'
, DiskTotalKB NUMBER(, ) path '@t'
, DiskFreeKBAvg NUMBER(, ) path '@af'
, DiskFreeKBMax NUMBER(, ) path '@xf'
, DiskFreeKBMin NUMBER(, ) 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
WHEN runStatus = 'STA' THEN
ELSE
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
WHEN runStatus = 'FWF' THEN
WHEN runStatus = 'FWW' THEN
WHEN runStatus = 'RUN' THEN
WHEN runStatus = 'QUE' THEN
WHEN runStatus = 'STR' THEN
WHEN runStatus = 'RNF' THEN
WHEN runStatus = 'RNW' THEN
WHEN runStatus = 'RNS' THEN
WHEN runStatus = 'CRA' THEN
WHEN runStatus = 'SYN' THEN
WHEN runStatus = 'STP' THEN
ELSE
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 <> THEN
WHEN runMajorStatus = 'FIN' THEN
WHEN runMajorStatus = 'STA' THEN
WHEN runMajorStatus = 'SUB' THEN
CASE
WHEN runMinorStatus = 'QUE' THEN
WHEN runMinorStatus = 'STR' THEN
ELSE
END
ELSE
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)) * ) +
(EXTRACT(HOUR FROM (thisTime - epochTime)) * ) +
(EXTRACT(MINUTE FROM (thisTime - epochTime)) * ) +
TRUNC(EXTRACT(SECOND FROM (thisTime - epochTime)) * );
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)) < THEN
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 -
WHEN (DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(startTime)) < THEN
ELSE DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(startTime)
END
WHEN runStatus = 'STA' THEN
CASE
WHEN startTime IS NULL THEN -
WHEN (DSODB.GetMilliseconds(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)) - DSODB.GetMilliseconds(startTime)) < THEN
ELSE DSODB.GetMilliseconds(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)) - DSODB.GetMilliseconds(startTime)
END
ELSE
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 -
WHEN (DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(creationTime)) < THEN
ELSE DSODB.GetMilliseconds(endTime) - DSODB.GetMilliseconds(creationTime)
END
ELSE
CASE
WHEN creationTime IS NULL THEN -
WHEN (DSODB.GetMilliseconds(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)) - DSODB.GetMilliseconds(creationTime)) < THEN
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 -
WHEN DSODB.RunElapsed(runStatus, endTime, startTime) > THEN
TRUNC(rowCount / TRUNC(DSODB.RunElapsed(runStatus, endTime, startTime) / ), )
WHEN DSODB.RunElapsed(runStatus, endTime, startTime) < THEN -
ELSE
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, , );
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 資料庫監控目标
[[email protected]report-etl1 DSODB]# cat DSODBConnect.cfg
# Licensed Materials - Property of IBM
# Copyright IBM Corp.
# 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:;SID=DSDB
# The following specify the username and password to connect to the DSODB schema.
Username=DSODB
Password=DSODB
3.配置 Operation 資料庫監控目标
[[email protected] 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- 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=
# 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 The value is capped at
# MaxWarnings=
# 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=
# UpdateIntSecs=
# 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. ( = on, = default/off)
# MonitorLinks=
# 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 > to enable tracing, and is the maximum
# number of lines to be written to the trace file (between and ).
# (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=
##################################################################
[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 (minimum )
# Setting this value to will turn off this functionality.
# JobRunCheckInterval=
# Job Run Usage Parameters
# ========================
# Set the following property to to enable the collection of job run resource
# usage data (the default), or to disable it.
# JobRunUsage=
# 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 per row.
# JobRunAggSnaps=
# System Resource Monitor - enable/disable
# ========================================
# The following switches on the collection of system resource data if set to (the default),
# or switches it off if If set to , all options below related to resource tracking are ignored.
# ResourceMonitor=
# Resource Tracking - connections
# ===============================
# The following specifies the port number that the resource tracking application (ResTrackApp)
# will use on the local system. The default is (See also ODBQAPPPORTNUM below.)
# ResourcePortNum=
# The following specifies the port number that the resource tracking application (ResTrackApp)
# will use on all remote nodes. The default is
# ResourceRemotePortNum=
# 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-
# 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
# ResourcePollPeriod=
# The following specifies how many snapshots are taken before an aggregated record of those
# values is stored. The default value is
# Therefore, using the default values for poll period and sample size, an aggregated record
# will be written every seconds ( * ).
# ResourceSampleSize=
# The following specifies whether to always store aggregated resource usage data. If set to
# aggregated data will always be stored. If the value is set to , 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
# ResourceAllAggregatedUsage=
# 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 Using the defaults this means that the minimum time stored
# around any job activity will be minutes (= * ( * ) seconds).
# ResourceAggRunPollPeriod=
# 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 Using the defaults this means that the activity check
# will be made every minute (= * ( * ) seconds).
# This value MUST be less than the value for ResourceAggRunPollPeriod.
# ResourceAggNonRunPollPeriod=
# OpsConsole - connections
# ========================
# Following sets the port number used by the ODBQueryApp application to service requests from
# the Operations Console. The default is (See also ResourcePortNum above.)
# ODBQAPPPORTNUM=
# DataStage Workload Manager Configuration
# ===================================================
# The following enables workload management if set to , or disabled if
WLMON=
# The following allows a job to run outside of WLM if communication between the DataStage runtime and WLM failed.
# A setting of will stop the job if communication with the WLM failed.
# A setting of will not send the job to the WLM. It will run immediately.
WLM_CONTINUE_ON_COMMS_ERROR=
# The following sends a job to the default queue if the queue specified is no longer valid.
# A setting of will stop the job if the queue specified in invalid
# A setting of will send the job to the default WLM queue.
WLM_CONTINUE_ON_QUEUE_ERROR=
# 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 means do not time out.
WLM_QUEUE_WAIT_TIMEOUT=
[[email protected] DSODB]#
4.檢測設定 - 錯誤配置顯示
[[email protected] 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:
Test Successful.
5.管理啟動和停止 Operations Console 各服務程序
-sh-$ sh DSAppWatcher.sh -status
which: no DSAppWatcher.sh in (/usr/lib64/qt-/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin)
AppWatcher:RUNNING
EngMonApp:STOPPED
ODBQueryApp:RUNNING
ResMonApp:RUNNING
-sh-$ sh DSAppWatcher.sh -start
6 實作前台監控：
![1.6 Linux指令行使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)