Giving back to the community
--Job Runs on DSODB
Code: Select all
SELECT
UPPER(J.PROJECTNAME) PROJECT_NAME,
J.JOBTYPE JOB_TYPE,
UPPER(J.JOBNAME) JOB_NAME,
R.RUNID RUN_ID,
R.INVOCATIONID INVOCATION_ID,
R.RUNSTARTTIMESTAMP RUN_START_TIME,
R.RUNENDTIMESTAMP RUN_END_TIME,
TIMESTAMPDIFF(2, CHAR(TIMESTAMP(R.RUNENDTIMESTAMP) - TIMESTAMP(R.RUNSTARTTIMESTAMP))) DURATION,
REF1.NAME RUN_TYPE,
REF2.NAME RUN_STATUS,
REF3.NAME RUN_SUB_STATUS,
R.NUMMESSAGESFATAL NO_OF_ERRORS,
R.NUMMESSAGESWARNING NO_OF_WARNINGS,
R.TOTALROWSCONSUMED ROWS_INPUT,
R.TOTALROWSPRODUCED ROWS_OUTPUT,
R.TOTALCPU CPU_TIME_MILLISECS
FROM DSODB.JOBRUN R
INNER JOIN DSODB.JOBEXEC J ON R.JOBID = J.JOBID
INNER JOIN DSODB.MASTERREF REF1 ON REF1.CODE = R.RUNTYPE AND REF1.ENUMERATION = 'RunType'
INNER JOIN DSODB.MASTERREF REF2 ON REF2.CODE = R.RUNMAJORSTATUS AND REF2.ENUMERATION = 'RunMajorStatus'
INNER JOIN DSODB.MASTERREF REF3 ON REF3.CODE = R.RUNMINORSTATUS AND REF3.ENUMERATION = 'RunMinorStatus'
;
--Job and Stage Runs on DSODB
Code: Select all
SELECT
R.RUNID RUN_ID,
UPPER(J.PROJECTNAME) PROJECT_NAME,
J.JOBTYPE JOB_TYPE,
UPPER(J.JOBNAME) JOB_NAME,
R.INVOCATIONID INVOCATION_ID,
R.RUNSTARTTIMESTAMP JOB_START_TIME,
R.RUNENDTIMESTAMP JOB_END_TIME,
TIMESTAMPDIFF(2, CHAR(TIMESTAMP(R.RUNENDTIMESTAMP) - TIMESTAMP(R.RUNSTARTTIMESTAMP))) JOB_DURATION,
R.TOTALCPU JOB_CPU_MILLISECS,
R.TOTALROWSCONSUMED JOB_ROWS_INPUT,
R.TOTALROWSPRODUCED JOB_ROWS_OUTPUT,
REF1.NAME JOB_RUN_TYPE,
REF2.NAME JOB_RUN_STATUS,
REF3.NAME JOB_RUN_SUB_STATUS,
R.NUMMESSAGESFATAL JOB_NO_OF_ERRORS,
R.NUMMESSAGESWARNING JOB_NO_OF_WARNINGS,
UPPER(M.STAGENAME) STAGE_NAME,
S.STAGESTARTTIMESTAMP STAGE_START_TIME,
S.STAGEENDTIMESTAMP STAGE_END_TIME,
TIMESTAMPDIFF(2, CHAR(TIMESTAMP(S.STAGEENDTIMESTAMP) - TIMESTAMP(S.STAGESTARTTIMESTAMP))) STAGE_DURATION,
S.TOTALCPU STAGE_CPU_MILLISECS,
REF4.NAME STAGE_RUN_STATUS
FROM DSODB.JOBRUN R
INNER JOIN DSODB.JOBEXEC J ON R.JOBID = J.JOBID
LEFT OUTER JOIN DSODB.JOBRUNSTAGE S ON R.RUNID = S.RUNID
LEFT OUTER JOIN DSODB.JOBSTAGE M ON S.STAGEID = M.STAGEID
INNER JOIN DSODB.MASTERREF REF1 ON REF1.CODE = R.RUNTYPE AND REF1.ENUMERATION = 'RunType'
INNER JOIN DSODB.MASTERREF REF2 ON REF2.CODE = R.RUNMAJORSTATUS AND REF2.ENUMERATION = 'RunMajorStatus'
INNER JOIN DSODB.MASTERREF REF3 ON REF3.CODE = R.RUNMINORSTATUS AND REF3.ENUMERATION = 'RunMinorStatus'
LEFT OUTER JOIN DSODB.MASTERREF REF4 ON REF4.CODE = S.STAGESTATUS AND REF4.ENUMERATION = 'StageStatus'
;
--Job-Stage Master on XMETA
Code: Select all
SELECT
UPPER(SUBSTRING(A.DSNAMESPACE_XMETA,DBMS_LOB.INSTR(A.DSNAMESPACE_XMETA,':',1)+1,50))||'-'||UPPER(A.NAME_XMETA)||'-'||UPPER(S.NAME_XMETA)||'-'||UPPER(S.INTERNALID_XMETA) JOB_STAGE_KEY
,UPPER(SUBSTRING(A.DSNAMESPACE_XMETA,DBMS_LOB.INSTR(A.DSNAMESPACE_XMETA,':',1)+1,50)) PROJECT_NAME
,A.DSNAMESPACE_XMETA
,UPPER(A.NAME_XMETA) JOB_NAME
,UPPER(S.NAME_XMETA) STAGE_NAME
,UPPER(S.INTERNALID_XMETA) STAGE_INTERNAL_ID
,A.CATEGORY_XMETA JOB_PATH
,CASE WHEN A.JOBTYPE_XMETA = 3 THEN 'SEQ'
WHEN A.JOBTYPE_XMETA = 4 THEN 'PAR' ELSE 'NA' END JOB_TYPE
,A.SHORTDESCRIPTION_XMETA JOB_DESC
,S.STAGETYPECLASSNAME_XMETA STAGE_TYPE
,S.STAGETYPE_XMETA STAGE_SUB_TYPE
,(TIMESTAMP('01/01/1970', '00:00:00') + (S.XMETA_MODIFICATION_TIMESTAMP_XMETA/ 1000) SECONDS) AS STAGE_LAST_MODIFIED
,(TIMESTAMP('01/01/1970', '00:00:00') + (A.XMETA_MODIFICATION_TIMESTAMP_XMETA/ 1000) SECONDS) AS JOB_LAST_MODIFIED
FROM
XMETA.DATASTAGEX_DSSTAGE S,
XMETA.DATASTAGEX_DSJOBDEF A
WHERE
S.CONTAINER_RID = A.XMETA_REPOS_OBJECT_ID_XMETA
;