This article is a continuation of the previous topic related to Data Services Metadata Query. Let us explore more into the Data Services Repository Metadata.

Operational & Auditing Metadata

1. AL_HISTORY

This Table contains information about the execution statistics (Start time, End time, Execution time, Status, Has error etc.) of Jobs in a repository.

SELECT 
OBJECT_KEY, MACHINE, SERVER,
TYPE, SERVICE,
INST_MACHINE, INST_SERVER,
INST_SERVER_PORT, INST_EXEC,
INST_EXEC_KEY, RUN_SEQ,
START_TIME, END_TIME, EXECUTION_TIME
STATUS, HAS_ERROR, IGNORE_ERROR
FROM AL_HISTORY A
WHERE HAS_ERROR = 0 
AND SERVICE NOT IN (
'di_job_al_mach_info', 'CD_JOB_d0cafae2' ) 
AND OBJECT_KEY = (
SELECT MAX(OBJECT_KEY) FROM AL_HISTORY B 
WHERE A.SERVICE = B.SERVICE )
ORDER BY SERVICE, OBJECT_KEY

2. AL_HISTORY_INFO

This Table contains information about the execution logging (Error Log, Trace Log, Monitor Log, Threaded etc.) of Jobs in a repository.




3. ALVW_HISTORY

This View contains history information about the run statistics and execution logging of jobs in a repository.

SELECT 
OBJECT_KEY,
KEY2, SEQNUM,
NAME, VALUE
FROM AL_STATISTICS
ORDER BY OBJECT_KEY,
KEY2, SEQNUM

Query 2: The following query returns the Execution statistics of the transforms of a dataflow in a repository.

SELECT 
JOB_NAME, JOB_KEY, 
JOB_RUNID, RUN_SEQ, 
DATAFLOW_NAME, PATH, 
OBJECT_NAME, OBJECT_TYPE,
ROW_COUNT, START_TIME, END_TIME, 
EXECUTION_TIME
FROM ALVW_FLOW_STAT

Query 3: The following query returns the Execution statistics of all the transforms and dataflows within a Job.

SELECT 
OBJECT_KEY, SCHED_NAME, 
JOB_GUID, JOB_COMMAND,
MACHINE_NAME, HOST_NAME, PORT,
USES_PARAMFILE, SCHEDULED_IN,
RECURRENCE_TYPE, RECURRENCE_PATTERN, 
START_TIME, AT_ID, ACTIVE, 
HOUR_BEGIN, HOUR_END, INTERVAL 
FROM AL_SCHED_INFO

7. AL_AUDIT

This table contains audit statistics information about each data flow execution. The column OBJECT_KEY uniquely identifies a data flow execution.

Column Name Description
OBJECT_KEYUniquely identifies the audit event in a dataflow execution.
HISTORY_KEYRefers to the OBJECT_KEY column in the AL_HISTORY table. Use this referential relationship to obtain history information about operational statistics for the data flow.
DF_LANG_KEYRefers to the OBJECT_KEY column in the AL_LANG table. Use this referential relationship to obtain the definition for the data flow.
STATUSAudit status can be one of the following values: 0 — Not audited 1 — Audit rule succeeded 2 — Audit information collected. This status occurs when you define audit labels to collect statistics but do not define audit rules. 3 — Audit rule failed
RULEINFOThe audit rule that failed and the values of the left-hand-side (LHS) and right-hand-side (RHS) of the Boolean expression.

Query 4:The following query returns the dataflows that failed an audit rules.

SELECT 
AL_LANG.NAME, AL_AUDIT.STATUS, AL_AUDIT.RULEINFO
FROM AL_AUDIT, AL_LANG
WHERE DF_LANG_KEY = AL_LANG.OBJECT_KEY
AND AL_AUDIT.STATUS = 3

8. AL_AUDIT_INFO

This table contains information about the audit statistics.

Column NameDescription
AUDIT_KEYRefers to the OBJECT_KEY column in the AL_AUDIT table. Use this referential relationship to obtain audit information for the data flow.
LABELRefers to the OBJECT_KEY column in the AL_HISTORY table. Use this referential relationship to obtain history information for the data flow.
VALUEValue of the label. This value can be one of the following: Number of rows in a table or whole row set Sum of the values in a column Average of the values in a column Checksum of the values in a column

Query 5:The following query returns the values of the labels of audit points that failed.

SELECT 
AL_LANG.NAME, 
AL_AUDIT.STATUS, AL_AUDIT.RULEINFO,
AL_AUDIT_INFO.LABEL, AL_AUDIT_INFO.VALUE
FROM AL_AUDIT_INFO, AL_AUDIT, AL_LANG
WHERE AL_AUDIT_INFO.AUDIT_KEY = AL_AUDIT.OBJECT_KEY
AND DF_LANG_KEY = AL_LANG.OBJECT_KEY
AND AL_AUDIT.STATUS = 3

Query 6:The following query returns the values of the labels of failed audit points of a Dataflow.

SELECT 
AL_LANG.NAME, 
AL_AUDIT.STATUS, AL_AUDIT.RULEINFO,
AL_AUDIT_INFO.LABEL, AL_AUDIT_INFO.VALUE
FROM AL_AUDIT_INFO, AL_AUDIT, AL_LANG
WHERE AL_AUDIT_INFO.AUDIT_KEY = AL_AUDIT.OBJECT_KEY
AND DF_LANG_KEY = AL_LANG.OBJECT_KEY
AND AL_AUDIT.STATUS = 3
AND AL_LANG.NAME = '[Dataflow Name']

Do you know the answer?


Which of the following is not a database?

Popular

Also Read


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.