Functionality of System Maintenance, Database Optimization and Database Table Analyze jobs

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

What are the out-of-the-box database maintenance and optimization jobs available in Clarity PPM? What tables do they affect, and what parameters control them?

Environment

Clarity PPM 16.x

Resolution

Database Table Analyze Job

• This job gathers database optimizer statistics for all tables in the Clarity schema. Its purpose is to ensure the database query optimizer has up-to-date statistics so it can generate efficient execution plans. This job does not delete any data -- it only updates internal database metadata (statistics). This job is only Application for Oracle and PostgreSQL databases.

• OOTB this is scheduled to run weekly. Broadcom SaaS customers need not run this as we already have a Daily Background job which performs the same task. 

Database Optimization Job

• This job performs database maintenance by cleaning up orphaned records across several system tables. This job actively deletes specifically orphaned records that are no longer referenced by their parent objects. Each maintenance function is independently enabled via job parameters, and all are disabled by default.

  Functionality	Tables Impacted	Description
  Caption Maintenance	CMN_CAPTIONS_NLS	Orphaned caption records (non-English language entries without a corresponding English base caption)
  Instance Rights Maintenance	CMN_SEC_ASSGND_OBJ_PERM	Orphaned Permission Records for Custom Object instances that have been removed directly from DB
  Page Instance Maintenance	CMN_INSTANCE_PAGES	Orphaned Page Instance Records for Custom Object instances that have been removed directly from DB.

System Maintenance Job(16.4.1+)

• This job performs periodic housekeeping by purging old or orphaned records from various operational tables. Its purpose is to prevent unbounded table growth that could degrade database performance over time. Each function/parameter is independently enabled via job parameters, and all are disabled by default.
  
  

  Functionality	Tables Impacted	Description
  PPM Broker Maintenance	CMN_BROKER_SERVICES, CMN_BROKER_SERVICE_PARAMS, CMN_BROKER_SERVICE_RESONSE	Remove records from these tables. These are not orphaned but used for storing Broker request and responses. Only populated when Broker service is configured
  DWH Tracker Maintenance	DWH_ODF_INST_CHANGES_ARCHIVE, DWH_ODF_SCHEMA_CHANGES	Remove the archive records which are used by DWH Instantaneous Sync. These tables are only populated when Instantaneous Sync Feature is turned on.
  BPM Errors Maintenance	BPM_ERRORS, BPM_ERROR_ARGUMENTS	Removes the records whose parent process instance is in (Aborted/Done/Error) state and older than the retention period.
  BPM Error Orphan Cleanup	BPM_ERRORS, BPM_ERROR_ARGUMENTS	Removes the records whose parent process instance no longer exists.