KB286938: After upgrading to Enterprise Manager 10.x, the JOBSTATUS, REPORTTYPE, SERVERNAME fields are missing from the table IS_REPORT_STATS

• Strategy

SYMPTOM
After upgrading to Enterprise Manager 10.x, the JOBSTATUS, REPORTTYPE, SERVERNAME fields missing from the IS_REPORT_STATS table
CAUSE
This change to the Enterprise Manager Warehouse is by design. The DDL for this table in version 10.x can be found below:


CREATE TABLE IS_REPORT_STATS
(
DAY_ID TIMESTAMP NOT NULL,
HOUR_ID NUMBER(3) NOT NULL,
MINUTE_ID NUMBER(5) NOT NULL,
JOBID NUMBER(10) NOT NULL,
SESSIONID CHAR(32) NOT NULL,
SERVERID CHAR(32) NULL,
SERVERMACHINE VARCHAR2( 255 CHAR) NULL,
PROJECTID CHAR(32) NULL ,
USERID CHAR(32) NULL ,
REPORTID CHAR(32) NULL ,
FILTERID CHAR(32) NULL ,
EMBEDDEDFILTER NUMBER(5) NULL ,
TEMPLATEID CHAR(32) NULL ,
EMBEDDEDTEMPLATE NUMBER(5) NULL ,
PARENTJOBID NUMBER(10) NULL ,
DBINSTANCEID CHAR(32) NULL ,
DBUSERID CHAR(32) NULL,
PARENTINDICATOR NUMBER(1) NOT NULL ,
REQUESTRECTIME TIMESTAMP NOT NULL ,
REQUESTQUEUETIME NUMBER(10) NULL ,
EXECSTARTTIME NUMBER(10) NULL ,
EXECFINISHTIME NUMBER(10) NULL ,
SQLPASSES NUMBER(10) NULL ,
JOBERRORCODE NUMBER(10) NULL ,
CANCELINDICATOR NUMBER(1) NOT NULL ,
ADHOCINDICATOR NUMBER(1) NOT NULL ,
PROMPTINDICATOR NUMBER(5) NOT NULL ,
DATAMARTINDICATOR NUMBER(1) NOT NULL ,
ELEMENTLOADINDIC NUMBER(1) NOT NULL ,
DRILLINDICATOR NUMBER(1) NOT NULL ,
SCHEDULEINDICATOR NUMBER(1) NOT NULL ,
CACHECREATEINDIC NUMBER(1) NOT NULL ,
PRIORITYNUMBER NUMBER(5) NOT NULL ,
JOBCOST NUMBER(5) NOT NULL,
FINALRESULTSIZE NUMBER(10) NULL,
RECORDTIME TIMESTAMP NULL,
ERRORMESSAGE VARCHAR2(4000 CHAR) NULL,
DRILLTEMPLATEUNIT CHAR(32) NULL ,
NEWOBJECT CHAR(32) NULL ,
DRILLTYPE NUMBER(3) NULL ,
TOTALTABLEACCESS NUMBER(10) NULL,
SQLLENGTH NUMBER(10) NULL,
EXECDURATION NUMBER(10) NULL,
CPUTIME NUMBER(10) NULL,
STEPCOUNT NUMBER(3) NULL,
EXECACTIONS NUMBER(10) NULL,
EXECFLAGS NUMBER(10) NULL,
DBERRORINDIC NUMBER(3) NULL,
PROMPTANSTIME NUMBER(10) NULL,
CUBEINSTANCEID CHAR(32) NULL,
CUBESIZE NUMBER(10) NULL,
SQLEXECINDIC NUMBER(3) NULL,
EXPORTINDIC NUMBER(3) NULL,
REPOSITORYID CHAR(32),
MESSAGEID CHAR(32),
CONSTRAINT IS_REPORT_PK
PRIMARY KEY (SESSIONID, JOBID )
)

ACTION
Use the CANCELINDICATOR, SERVERMACHINE and JOBERRORCODE columns of the IS_REPORT_STATS table to acquire the same information. Information about the report type will be obtained from the IS_REP lookup table

Comment

0 comments

Details

Knowledge Article

Published:

June 7, 2017

Last Updated:

September 25, 2017

CREATE TABLE IS_REPORT_STATS ( DAY_ID TIMESTAMP NOT NULL, HOUR_ID NUMBER(3) NOT NULL, MINUTE_ID NUMBER(5) NOT NULL, JOBID NUMBER(10) NOT NULL, SESSIONID CHAR(32) NOT NULL, SERVERID CHAR(32) NULL, SERVERMACHINE VARCHAR2( 255 CHAR) NULL, PROJECTID CHAR(32) NULL , USERID CHAR(32) NULL , REPORTID CHAR(32) NULL , FILTERID CHAR(32) NULL , EMBEDDEDFILTER NUMBER(5) NULL , TEMPLATEID CHAR(32) NULL , EMBEDDEDTEMPLATE NUMBER(5) NULL , PARENTJOBID NUMBER(10) NULL , DBINSTANCEID CHAR(32) NULL , DBUSERID CHAR(32) NULL, PARENTINDICATOR NUMBER(1) NOT NULL , REQUESTRECTIME TIMESTAMP NOT NULL , REQUESTQUEUETIME NUMBER(10) NULL , EXECSTARTTIME NUMBER(10) NULL , EXECFINISHTIME NUMBER(10) NULL , SQLPASSES NUMBER(10) NULL , JOBERRORCODE NUMBER(10) NULL , CANCELINDICATOR NUMBER(1) NOT NULL , ADHOCINDICATOR NUMBER(1) NOT NULL , PROMPTINDICATOR NUMBER(5) NOT NULL , DATAMARTINDICATOR NUMBER(1) NOT NULL , ELEMENTLOADINDIC NUMBER(1) NOT NULL , DRILLINDICATOR NUMBER(1) NOT NULL , SCHEDULEINDICATOR NUMBER(1) NOT NULL , CACHECREATEINDIC NUMBER(1) NOT NULL , PRIORITYNUMBER NUMBER(5) NOT NULL , JOBCOST NUMBER(5) NOT NULL, FINALRESULTSIZE NUMBER(10) NULL, RECORDTIME TIMESTAMP NULL, ERRORMESSAGE VARCHAR2(4000 CHAR) NULL, DRILLTEMPLATEUNIT CHAR(32) NULL , NEWOBJECT CHAR(32) NULL , DRILLTYPE NUMBER(3) NULL , TOTALTABLEACCESS NUMBER(10) NULL, SQLLENGTH NUMBER(10) NULL, EXECDURATION NUMBER(10) NULL, CPUTIME NUMBER(10) NULL, STEPCOUNT NUMBER(3) NULL, EXECACTIONS NUMBER(10) NULL, EXECFLAGS NUMBER(10) NULL, DBERRORINDIC NUMBER(3) NULL, PROMPTANSTIME NUMBER(10) NULL, CUBEINSTANCEID CHAR(32) NULL, CUBESIZE NUMBER(10) NULL, SQLEXECINDIC NUMBER(3) NULL, EXPORTINDIC NUMBER(3) NULL, REPOSITORYID CHAR(32), MESSAGEID CHAR(32), CONSTRAINT IS_REPORT_PK PRIMARY KEY (SESSIONID, JOBID ) )