CA PPM: How to check Load Data Warehouse Access Rights job while it is running

Document ID : KB000106047
Last Modified Date : 11/07/2018
Show Technical Document Details
Issue:

Is there any way to see progress of Load Data Warehouse Access Rights job while it is running?

The UI just shows 'Executing ETL Job' and there is no other useful logging.

Sometimes it runs long and it would be very helpful to know how close the job is to completing so we can decide to cancel or not.

Cause:
The performance of the job depends on how much access rights are given to the CA PPM users.
Resolution:

The following query can be used to check the progress of the job:

SELECT 'DWH_SEC_GLOBAL_RIGHT', COUNT(*)
FROM DWH_SEC_GLOBAL_RIGHT
UNION
SELECT 'DWH_SEC_RIGHT', COUNT(*)
FROM DWH_SEC_RIGHT
UNION
SELECT 'DWH_SEC_GLOBAL_RIGHT', COUNT(*)
FROM DWH_SEC_GLOBAL_RIGHT
UNION
SELECT 'DWH_SEC_GROUP', COUNT(*)
FROM DWH_SEC_GROUP
UNION
SELECT 'DWH_SEC_USER_GROUP', COUNT(*)
FROM DWH_SEC_USER_GROUP
UNION
SELECT 'DWH_SEC_GROUP_HIER', COUNT(*)
FROM DWH_SEC_GROUP_HIER
UNION
SELECT 'DWH_SEC_ASSIGN_RIGHT', COUNT(*)
FROM DWH_SEC_ASSIGN_RIGHT
UNION
SELECT 'DWH_SEC_ASSIGN_PERM', COUNT(*)
FROM DWH_SEC_ASSIGN_PERM
UNION
SELECT 'DWH_SEC_PERM_ELEMENT', COUNT(*)
FROM DWH_SEC_PERM_ELEMENT
UNION
SELECT 'DWH_SEC_UNIT_BY_MODE', COUNT(*)
FROM DWH_SEC_UNIT_BY_MODE
UNION
SELECT 'DWH_SEC_USER', COUNT(*)
FROM DWH_SEC_USER
UNION
SELECT 'DWH_SEC_GLOBAL_RIGHT', COUNT(*)
FROM DWH_SEC_GLOBAL_RIGHT
 

Additional Information:
https://docops.ca.com/ca-ppm/15-4-1/en/reference/ca-ppm-jobs-reference#CAPPMJobsReference-LoadDataWarehouseAccessRights