DSXchange

Code: Select all

-- List start and finish times, names, and statuses of all jobs that have
--    been run on host H, started after time YYYY-MM-DD HH:MM:SS, 
--    and have finished.
SELECT
      X.ProjectName, X.JobName,
      R.RunStartTimestamp, R.RunEndTimestamp, R.RunMinorStatus
FROM
      DSODB.JOBRUN AS R
JOIN  DSODB.JOBEXEC AS X ON R.JOBID = X.JOBID
JOIN  DSODB.HOST AS H    ON X.HOSTID = H.HOSTID
WHERE
      H.HostName = "H"
  AND R.RunMajorStatus = "FIN"
  AND R.RunStartTimestamp >= "YYYY-MM-DD HH:MM:SS"
ORDER BY R.RunStartTimestamp


-- List names of all jobs on host H that were running at time T and sort them
--     in descending order of total CPU usage for the whole run.
SELECT
      X.ProjectName, X.FolderPath, X.JobName,
      R.RunStartTimestamp, R.RunEndTimestamp, R.TotalCPU
FROM
      DSODB.JOBRUN AS R
JOIN  DSODB.JOBEXEC AS X ON R.JOBID = X.JOBID
JOIN  DSODB.HOST AS H    ON X.HOSTID = H.HOSTID
WHERE
      H.HostName = "H"
  AND R.RunStartTimestamp <= "YYYY-MM-DD HH:MM:SS"
  AND ( R.RunEndTimestamp >= "YYYY-MM-DD HH:MM:SS"
    OR R.RunEndTimestamp IS NULL )
ORDER BY R.TotalCPU DESC

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'
;

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'
;

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
;