Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
SAP Data Services

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 NameDescription
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 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 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 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']