Data Services Metadata Query Part 2

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.

Imported Metadata

This set of tables and views capture information about the metadata imported into SAP Data Services from external databases and applications. Let us take a look into the metadata tables and some sample query

1. ALVW_PROJECTINFO

This View contains information about the Projects in a Repository. Related TABLE AL_PROJECTS contains the entire version information related to Projects in case of Data Services central repository.

SELECT 
ALVW_PROJECTINFO.PROJECT_ID, 
ALVW_PROJECTINFO.PROJECT_NAME 
FROM ALVW_PROJECTINFO

2. ALVW_JOBINFO

This View contains information about Data Services Jobs in a Repository.

SELECT 
ALVW_JOBINFO.JOB_ID, ALVW_JOBINFO.JOB_NAME 
FROM ALVW_JOBINFO
WHERE ALVW_JOBINFO.JOB_NAME <> 'di_job_al_mach_info'

In order to get the Version History of a Job we can join this table with AL_LANG

SELECT * 
FROM AL_LANG LEFT OUTER JOIN ALVW_JOBINFO
ON AL_LANG.OBJECT_KEY = ALVW_JOBINFO.JOB_ID
WHERE JOB_NAME = '[Job Name]'

Alternatively, using AL_LANG table alone, which contains information of various Data Services objects found in Object Library.

SELECT AL_LANG.* 
FROM AL_LANG 
WHERE AL_LANG.OBJECT_TYPE = 0 
AND AL_LANG.TYPE = 0
AND AL_LANG.NAME ='[Job Name]'

As discussed previously, OBJECT_TYPE = 0 (JOB), 1 (DATAFLOW), 4 (FileFormat), 5 (DataStore).

To get the latest version of a JOB using AL_LANG as discussed previously

SELECT A.* 
FROM AL_LANG 
WHERE A.OBJECT_TYPE = 0 
AND A.VERSION = (
SELECT MAX(B.VERSION) FROM AL_LANG B 
WHERE A.NORMNAME = B.NORMNAME )
AND A.NAME = '[Job Name]'

3. ALVW_PROJECT_JOB

This View contains relationship between Project and Jobs in a Repository. It provides the information regarding which Jobs are associated with a particular project.

SELECT PROJECT_ID, JOB_ID
FROM ALVW_PROJECT_JOB

We can join the Views ALVW_PROJECTINFO and ALVW_JOBINFO with the above view to generate a report on Project and Job association.

SELECT 
ALVW_PROJECTINFO.PROJECT_NAME, ALVW_JOBINFO.JOB_NAME
FROM ALVW_PROJECT_JOB, ALVW_PROJECTINFO, ALVW_JOBINFO
WHERE ALVW_PROJECT_JOB.PROJECT_ID = ALVW_PROJECTINFO.PROJECT_ID
AND ALVW_PROJECT_JOB.JOB_ID = ALVW_JOBINFO.JOB_ID
ORDER BY ALVW_PROJECTINFO.PROJECT_NAME, ALVW_JOBINFO.JOB_NAME

4. ALVW_WORKFLOWINFO

This View contains information about the Workflows in a Repository.

SELECT WORKFLOW_ID, WORKFLOW_NAME
FROM ALVW_WORKFLOWINFO

5. ALVW_DATAFLOWINFO

This View contains information about the Dataflows in a Repository.

SELECT DATAFLOW_ID, DATAFLOW_NAME
FROM ALVW_DATAFLOWINFO
WHERE DATAFLOW_NAME <> 'di_df_al_mach_info'

6. AL_VARPARAM

This Table contains information about the Global/Local Variables and Parameters in a Repository.

SELECT 
PARENT_OBJID, PARENT_OBJTYPE,
VP_NAME, VP_TYPE, VP_SEQNUM,
VP_DTYPE, VP_DSIZE, VP_DPREC, 
VP_DSCAL, VP_DESC
FROM AL_VARPARAM

Query 1: The following query returns the Global and Local Variables information used in a Job.

SELECT 
ALVW_JOBINFO.JOB_NAME,
AL_VARPARAM.VP_NAME, AL_VARPARAM.VP_TYPE, 
AL_VARPARAM.VP_SEQNUM, AL_VARPARAM.VP_DTYPE, 
AL_VARPARAM.VP_DSIZE, AL_VARPARAM.VP_DPREC, 
AL_VARPARAM.VP_DSCAL, AL_VARPARAM.VP_DESC
FROM AL_VARPARAM INNER JOIN ALVW_JOBINFO
ON AL_VARPARAM.PARENT_OBJID = ALVW_JOBINFO.JOB_ID
WHERE ALVW_JOBINFO.JOB_NAME = '[Job Name]'
ORDER BY AL_VARPARAM.VP_SEQNUM

Query 2: The following query returns the Parameters information used in a Dataflow.

SELECT 
ALVW_DATAFLOWINFO.DATAFLOW_NAME,
AL_VARPARAM.VP_NAME, AL_VARPARAM.VP_TYPE, 
AL_VARPARAM.VP_SEQNUM, AL_VARPARAM.VP_DTYPE, 
AL_VARPARAM.VP_DSIZE, AL_VARPARAM.VP_DPREC, 
AL_VARPARAM.VP_DSCAL, AL_VARPARAM.VP_DESC
FROM AL_VARPARAM INNER JOIN ALVW_DATAFLOWINFO
ON AL_VARPARAM.PARENT_OBJID = ALVW_DATAFLOWINFO.DATAFLOW_ID
WHERE ALVW_DATAFLOWINFO.DATAFLOW_NAME = '[Dataflow Name]'
ORDER BY AL_VARPARAM.VP_SEQNUM

7. ALVW_DATASTOREINFO

This View contains information about the Datastores in a Repository.

SELECT 
DATASTORE_ID, DATASTORE_NAME
FROM ALVW_DATASTOREINFO

8. ALVW_TABLEINFO

This View contains information about the list of tables imported into the repository.

SELECT TABLE_ID, DATASTORE, TABLE_NAME, TABLE_OWNER
FROM ALVW_TABLEINFO
WHERE TABLE_NAME NOT IN ( 'AL_PARENT_CHILD', 'AL_USAGE', 'AL_MACHINE_INFO' )

9. AL_SCHEMA

This Table contains the association information between the Datastores and Tables along with Version control information in case of Central Repository.

Query 3: The following query returns the Version information of all the Tables in a Datastore.

SELECT
NAME, OWNER, DATASTORE,
TABLE_TYPE, VERSION, NORMNAME, 
OBJECT_KEY, GUID, STATE,
CHECKOUT_DT, CHECKOUT_REPO, CHECKIN_DT,
CHECKIN_REPO, LABEL_DT, LABEL,
COMMENTS, SEC_USER, SEC_USER_COUT
FROM AL_SCHEMA
WHERE DATASTORE = '[Data Store Name]'
ORDER BY NAME, VERSION

10. ALVW_TABLEATTR

This View contains information about the attributes in a table- Description, Business_Name, Date_last_loaded, Total_Number_Of_Rows_Processed, etc.

SELECT 
TABLE_NAME, TABLE_OWNER, DATASTORE, 
TABLE_ATTR, TABLE_ATTR_VALUE
FROM ALVW_TABLEATTR
WHERE TABLE_NAME NOT IN ( 'AL_PARENT_CHILD', 'AL_USAGE', 'AL_MACHINE_INFO' )

Query 4: The following query returns some of the attribute values related to loading of a Target table in a DataStore.

SELECT 
TABLE_NAME, TABLE_OWNER, DATASTORE, 
TABLE_ATTR, TABLE_ATTR_VALUE
FROM ALVW_TABLEATTR
WHERE DATASTORE= '[Data Store Name]'
AND TABLE_NAME = '[Target Table]'
AND TABLE_ATTR IN ( 
'Date_last_loaded',
'Total_Number_Of_Rows_Processed', 
'Number_Of_Rows_Rejected',
'Number_Of_Inserts',
'Number_Of_Updates',
'Number_Of_Deletes',
'Elapsed_Time_For_Load')

11. AL_PKEY

This Table contains primary key information for all the tables in a repository.

SELECT 
TABLEKEY, COLNAME, COLPOSITION
FROM AL_PKEY

Query 5: The following query returns the primary keys associated with their corresponding tables in a datastore.

SELECT 
ALVW_TABLEINFO.DATASTORE, ALVW_TABLEINFO.TABLE_NAME, 
AL_PKEY.COLNAME, AL_PKEY.COLPOSITION 
FROM AL_PKEY, ALVW_TABLEINFO 
WHERE AL_PKEY.TABLEKEY = ALVW_TABLEINFO.TABLE_ID
AND ALVW_TABLEINFO.DATASTORE = '[Data Store Name]'

12. AL_INDEX

This Table contains index information for all the tables in a repository.

SELECT 
TABLEKEY, NAME, INDEXTYPE, 
COLNAME, COLPOSITION
FROM AL_INDEX

Query 6: The following query returns the indexes associated with their corresponding tables in a datastore.

SELECT 
ALVW_TABLEINFO.DATASTORE, ALVW_TABLEINFO.TABLE_NAME, 
AL_INDEX.NAME, AL_INDEX.INDEXTYPE, 
AL_INDEX.COLNAME, AL_INDEX.COLPOSITION
FROM AL_INDEX, ALVW_TABLEINFO 
WHERE AL_INDEX.TABLEKEY = ALVW_TABLEINFO.TABLE_ID
AND ALVW_TABLEINFO.DATASTORE = '[Data Store Name]'

13. ALVW_FKREL

This View contains information about the primary and foreign key relationships among tables.

SELECT 
TABLEKEY, PKCOLUMN, 
FKTABLE, FKCOLUMN, FKTYPE
FROM ALVW_FKREL

Query 7: The following query returns the the primary and foreign key information associated with a table in a datastore.

SELECT 
ALVW_TABLEINFO.DATASTORE, ALVW_TABLEINFO.TABLE_NAME, 
ALVW_FKREL.PKCOLUMN, ALVW_FKREL.FKTABLE, 
ALVW_FKREL.FKCOLUMN, ALVW_FKREL.FKTYPE
FROM ALVW_FKREL, ALVW_TABLEINFO 
WHERE ALVW_FKREL.TABLEKEY = ALVW_TABLEINFO.TABLE_ID
AND ALVW_TABLEINFO.DATASTORE = '[Data Store Name]'
AND ALVW_TABLEINFO.TABLE_NAME = '[Table Name]'

14. AL_PCOLUMN

This Table contains partition information for tables in a repository.

SELECT 
TABLEKEY, PARTITION_TYPE, 
COLNAME, COLPOSITION
FROM AL_PCOLUMN

15. ALVW_FILEFORMINFO

This View contains information about File Formats (Flat File, XML) in a repository.

SELECT 
FILEFORMAT_ID, FILEFORMAT_TYPE, 
FILEFORMAT_NAME
FROM ALVW_FILEFORMINFO
WHERE FILEFORMAT_NAME NOT IN ( 
'Transport_Format', 'di_ff_al_mach_info' 
)

16. ALVW_COLUMNINFO

This View contains information about the columns of datastore tables.

SELECT 
TABLE_NAME, TABLE_OWNER, DATASTORE, 
COLUMN_ID, COLUMN_NAME, COLUMN_DATATYPE, 
COLUMN_LENGTH, COLUMN_PRECISION, COLUMN_SCALE, 
COLUMN_IS_NULLABLE
FROM ALVW_COLUMNINFO

Query 8: The following query returns the columns information associated with a table in a datastore.

SELECT 
TABLE_NAME, TABLE_OWNER, DATASTORE, 
COLUMN_ID, COLUMN_NAME, COLUMN_DATATYPE, 
COLUMN_LENGTH, COLUMN_PRECISION, COLUMN_SCALE, 
COLUMN_IS_NULLABLE
FROM ALVW_COLUMNINFO
WHERE DATASTORE = '[Data Store Name]'
AND TABLE_NAME = '[Table Name]'

17. ALVW_COLUMNATTR

This View contains information about column attributes- Description, Content_Type, Business_Name, ui_mapping_text etc. of Datastore tables.

SELECT 
TABLE_NAME, TABLE_OWNER, DATASTORE,
COLUMN_NAME, COLUMN_ATTR, COLUMN_ATTR_VALUE
FROM ALVW_COLUMNATTR

Query 9: The following query returns the columns having description attribute associated with a table in a datastore.

SELECT 
TABLE_NAME, TABLE_OWNER, DATASTORE,
COLUMN_NAME, COLUMN_ATTR, COLUMN_ATTR_VALUE
FROM ALVW_COLUMNATTR
WHERE DATASTORE = '[Data Store Name]'
AND TABLE_NAME = '[Table Name]'
AND COLUMN_ATTR = 'Description'

18. ALVW_FUNCINFO

This View contains information about list of functions defined or imported into Data Services repository.

SELECT 
FUNC_KEY, FUNC_NAME, 
FUNC_OWNER, DATASTORE
FROM ALVW_FUNCINFO

19. ALVW_FUNCATTR

This View contains information about function Attributes -Description, Parallelizable, isScriptValidationFunction, CallableFromSQLExpr etc. defined or imported into Data Services repository.

SELECT 
FUNC_NAME, FUNC_OWNER, DATASTORE, 
FUNC_ATTR, FUNC_ATTR_VALUE
FROM ALVW_FUNCATTR

20. AL_EXT_FUNCTEXT

This Table contains the code or text of the functions defined in Data Services or imported into repository.

SELECT 
PARENT_OBJID, SEQNUM, TEXT_VALUE
FROM AL_EXT_FUNCTEXT

In this context refer the table AL_FUNCINFO, which contains the information of the functions along with their function type and return types.

Also check the table AL_FUNCPARAM, which contains the Parameters and Variables defined or used by functions imported in the Data Services repository.

21. ALVW_MAPPING

This View contains information about target tables and columns, the sources used to populate target columns, and the transforms Data Services applies to sources before applying them to targets. Data Services uses this view for impact analysis in Metadata Reports.

The ALVW_MAPPING view joins the AL_COLMAP and the AL_COLMAP_TEXT tables. The column mapping calculation generates the following information for target columns- The source column(s) from which the target column is mapped; The expressions used to populate target columns.

Data Services stores column mappings of nested source and target data in data flows using both the ALVW_MAPPING view and the AL_COLMAP_NAMES table. Also check the table AL_COLUMN.

SELECT 
DF_NAME, 
TRG_TAB_NAME, TRG_TAB_ID, TRG_TAB_DESC, 
TRG_OWNER, TRG_DS, TRG_TYPE, TRG_USAGE, 
TRG_COL_NAME, TRG_COL_ID, TRG_COL_DESC,
SRC_TAB_NAME, SRC_TAB_ID, SRC_TAB_DESC, 
SRC_OWNER, SRC_DS, SRC_TYPE, 
SRC_COL_NAME, SRC_COL_ID, SRC_COL_DESC,
MAPPING_TYPE, MAPPING_TEXT
FROM ALVW_MAPPING

The list of values of MAPPING_TYPE are- Computed, Computed - Merged, Direct, Direct - Merged, Generated, LookedUp, LookedUp - Merged and Not Mapped.

Query 10: The following query returns the Mapping and transformation logic for the columns of a Target table in a datastore.

SELECT 
DF_NAME, 
TRG_TAB_NAME, TRG_OWNER, TRG_DS, 
TRG_COL_NAME, TRG_COL_ID, TRG_COL_DESC,
SRC_TAB_NAME, SRC_TAB_ID, SRC_TAB_DESC, 
SRC_OWNER, SRC_DS, SRC_TYPE, 
SRC_COL_NAME, SRC_COL_ID, SRC_COL_DESC,
MAPPING_TYPE, MAPPING_TEXT
FROM ALVW_MAPPING
WHERE TRG_DS = '[Data Store Name]'
AND TRG_TAB_NAME = '[Table Name]'
ORDER BY TRG_COL_ID

Query 11: The following query returns the Target tables and the columns populated from a Column of a Source table in a datastore.

SELECT 
DF_NAME, 
SRC_TAB_NAME, SRC_OWNER, SRC_DS,  
SRC_COL_NAME, SRC_COL_ID, 
TRG_TAB_NAME, TRG_OWNER, TRG_DS, 
TRG_COL_NAME, TRG_COL_ID, TRG_COL_DESC,
MAPPING_TYPE, MAPPING_TEXT
FROM ALVW_MAPPING
WHERE SRC_DS = '[Data Store Name]'
AND SRC_TAB_NAME = '[Table Name]'
AND SRC_COL_NAME = '[Column Name]'
ORDER BY TRG_TAB_NAME

22. ALVW_OBJ_CINOUT

This view contains information about versions of repository objects.

SELECT 
OBJECT_TYPE, NAME, TYPE, 
NORMNAME, DATASTORE, OWNER
VERSION, STATE, 
CHECKOUT_DT, CHECKOUT_REPO, 
CHECKIN_DT, CHECKIN_REPO, 
LABEL, LABEL_DT, 
COMMENTS, SEC_USER, SEC_USER_COUT
FROM ALVW_OBJ_CINOUT