How to Truncate Status Message Tables for Oracle Database
Article ID: 40259
Updated On:
Products
Issue/Introduction
Regular maintenance dSeries database is required for optimal application performance. One such maintenance activity is deleting old status messages. dSeries provides DELETESTATUSMESSAGES cli command for cleaning up old messages, which can be scheduled as a dSeries housekeeping job. When this regular maintenance status messages have been ignored for a prolonged time, server performance can degrade. In such cases, status message tables needs to be truncated manually.
The resources on the database server and dSeries application server will determine how much data it can process without performance impact. In general 1 to 2 million rows in the ESP_STATUS_MESSAGE may show signs of performance degradation.
Environment
ESP dSeries Server (CA WA DE)
Oracle Database 11g and 12c
Resolution
It is recommended to open a support issue and get approval from Broadcom (CA) support to perform the steps below.
1. Shutdown dSeries (Primary and Standby).
2. Backup dSeries database
Oracle 11g:
3. Execute the below query to obtain a list of SQL statements to disable the foreign key constraints of tables dependent on 'ESP_STATUS_MESSAGE' table.
select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINTS '||CONSTRAINT_NAME||';' from user_constraints where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name IN ('ESP_SM_LOG','ESP_SM_SNMP','ESP_STATUS_MESSAGE') AND CONSTRAINT_TYPE = 'P');
The result of the above query will be similar to the one below.
ALTER TABLE ESP_SM_ACTIVE_APPLS DISABLE CONSTRAINTS SYS_C0012000;
ALTER TABLE ESP_SM_AGENT DISABLE CONSTRAINTS SYS_C0012001;
ALTER TABLE ESP_SM_CLIENT_CONNECTION DISABLE CONSTRAINTS SYS_C0012002;
ALTER TABLE ESP_SM_EVENT_AUTHORIZATION DISABLE CONSTRAINTS SYS_C0012003;
ALTER TABLE ESP_SM_LOGIN_FAILURE DISABLE CONSTRAINTS SYS_C0012004;
ALTER TABLE ESP_SM_MEMORY_USAGE DISABLE CONSTRAINTS SYS_C0012005;
ALTER TABLE ESP_SM_SERVER_STATUS DISABLE CONSTRAINTS SYS_C0012006;
ALTER TABLE ESP_SM_SNMP DISABLE CONSTRAINTS SYS_C0012007;
ALTER TABLE ESP_SM_LICENSE_STATUS DISABLE CONSTRAINTS SYS_C0012008;
ALTER TABLE ESP_SM_SNMP_VARIABLE DISABLE CONSTRAINTS SYS_C0012009;
ALTER TABLE ESP_SM_LDAP_STATUS DISABLE CONSTRAINTS SYS_C0012010;
ALTER TABLE ESP_SM_QUIESCE_STATUS DISABLE CONSTRAINTS SYS_C0012011;
ALTER TABLE ESP_SM_LOG DISABLE CONSTRAINTS SYS_C0012012;
ALTER TABLE ESP_SM_LOG_STACKTRACE_ELEMENT DISABLE CONSTRAINTS SYS_C0012013;
4. Execute these ALTER TABLE statements above in the order they appear.
5. Execute the query below to verify the constraints have been disabled.
select table_name, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from user_constraints where STATUS='DISABLED';
6. Execute the truncate statements below in the order they appear.
Example:
truncate table ESP_SM_LOG_STACKTRACE_ELEMENT;
truncate table ESP_SM_SNMP_VARIABLE;
truncate table ESP_SM_ACTIVE_APPLS;
truncate table ESP_SM_AGENT;
truncate table ESP_SM_CLIENT_CONNECTION;
truncate table ESP_SM_EVENT_AUTHORIZATION;
truncate table ESP_SM_LDAP_STATUS;
truncate table ESP_SM_LICENSE_STATUS;
truncate table ESP_SM_LOG;
truncate table ESP_SM_LOGIN_FAILURE;
truncate table ESP_SM_MEMORY_USAGE;
truncate table ESP_SM_QUIESCE_STATUS;
truncate table ESP_SM_SERVER_STATUS;
truncate table ESP_SM_SNMP;
truncate table ESP_STATUS_MESSAGE;
7. On completion of step 7, copy the 'ALTER TABLE' statements from step 4 and modify it to have 'ENABLE' in the place of 'DISABLE'. Execute these 'ALTER TABLE' statements to enable the constraints that were disabled in step 4.
Example:
ALTER TABLE ESP_SM_ACTIVE_APPLS ENABLE CONSTRAINTS SYS_C0012000;
ALTER TABLE ESP_SM_AGENT ENABLE CONSTRAINTS SYS_C0012001;
ALTER TABLE ESP_SM_CLIENT_CONNECTION ENABLE CONSTRAINTS SYS_C0012002;
ALTER TABLE ESP_SM_EVENT_AUTHORIZATION ENABLE CONSTRAINTS SYS_C0012003;
ALTER TABLE ESP_SM_LOGIN_FAILURE ENABLE CONSTRAINTS SYS_C0012004;
ALTER TABLE ESP_SM_MEMORY_USAGE ENABLE CONSTRAINTS SYS_C0012005;
ALTER TABLE ESP_SM_SERVER_STATUS ENABLE CONSTRAINTS SYS_C0012006;
ALTER TABLE ESP_SM_SNMP ENABLE CONSTRAINTS SYS_C0012007;
ALTER TABLE ESP_SM_LICENSE_STATUS ENABLE CONSTRAINTS SYS_C0012008;
ALTER TABLE ESP_SM_SNMP_VARIABLE ENABLE CONSTRAINTS SYS_C0012009;
ALTER TABLE ESP_SM_LDAP_STATUS ENABLE CONSTRAINTS SYS_C0012010;
ALTER TABLE ESP_SM_QUIESCE_STATUS ENABLE CONSTRAINTS SYS_C0012011;
ALTER TABLE ESP_SM_LOG ENABLE CONSTRAINTS SYS_C0012012;
ALTER TABLE ESP_SM_LOG_STACKTRACE_ELEMENT ENABLE CONSTRAINTS SYS_C0012013;
8. Execute the query below to verify there are no constraints in disabled state. It should return 0 records.
select table_name, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from user_constraints where STATUS='DISABLED';
9. Start dSeries
Oracle 12c:
1. Shutdown dSeries (Primary and Standby).
2. Backup dSeries database
3. Execute the truncate statement below
truncate table ESP_STATUS_MESSAGE cascade
4. Start dSeries
Additional Information
Important Note: Broadcom does not recommend modifying the dSeries database directly. There are situations where direct maintenance on the database may be required. These actions shall be performed by a DBA and in consultation with Broadcom support. Performing this action will permanently remove all entries from the status message tables.
Feedback
