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

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

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.

Environment

Release:
Component: PPMPRD

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