Data Services Metadata Query Part 3

Updated on Oct 02, 2020

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
ORDER BY SERVICE, OBJECT_KEY

Query 1: The following query returns the run statistics of all successfully executed 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.

SELECT 
OBJECT_KEY,
NAME, VALUE
FROM AL_HISTORY_INFO
ORDER BY OBJECT_KEY, NAME

3. ALVW_HISTORY

This View contains history information about the run statistics and execution logging 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
PROFILE_NAME, SYSTEM_CONFIG_INFO, SERVER_GROUP_NAME,
ERROR_LOG, TRACE_LOG, MONITOR_LOG
FROM ALVW_HISTORY

4. AL_STATISTICS

This Table contains information about the Execution statistics of transforms ( row count, cache size, execution time etc. ) within dataflows.

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 A.OBJECT_KEY,
A.KEY2, A.SEQNUM,
A.NAME, A.VALUE
FROM AL_STATISTICS A 
WHERE A.OBJECT_KEY = (
SELECT MAX(B.OBJECT_KEY)
FROM AL_STATISTICS B
WHERE B.NAME = 'DATAFLOW_NAME' 
AND B.VALUE = '[Dataflow Name]' )
ORDER BY A.OBJECT_KEY,
A.KEY2, A.SEQNUM

5. ALVW_FLOW_STAT

This View contains information about the execution statistics of transforms ( start time, end time, execution time and row count only ) within dataflows.

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 
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
WHERE JOB_NAME = '[Job Name]'
ORDER BY JOB_KEY, DATAFLOW_NAME

6. AL_SCHED_INFO

This Table contains information about the Job scheduling in Data Services Management Console.

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_KEY	Uniquely identifies the audit event in a dataflow execution.
HISTORY_KEY	Refers 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_KEY	Refers to the OBJECT_KEY column in the AL_LANG table. Use this referential relationship to obtain the definition for the data flow.
STATUS	Audit status can be one of the following values:0 — Not audited1 — Audit rule succeeded2 — Audit information collected. This status occurs when you define audit labels to collect statistics but do not define audit rules.3 — Audit rule failed

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 Name	Description
AUDIT_KEY	Refers to the OBJECT_KEY column in the AL_AUDIT table. Use this referential relationship to obtain audit information for the data flow.
LABEL	Refers to the OBJECT_KEY column in the AL_HISTORY table. Use this referential relationship to obtain history information for the data flow.
VALUE	Value of the label. This value can be one of the following: Number of rows in a table or whole row setSum of the values in a columnAverage of the values in a columnChecksum 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']