gunpr01

How to Truncate DSeries Status Message Tables with Oracle Database

Blog Post created by gunpr01 Employee on Jun 2, 2016

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.

Your
resources on the database server and dSeries application server will
determine how much data it can process without performance impact.  In general 1-2 million rows in the ESP_STATUS_MESSAGE tend show signs of performance degradation.

Environment:

  • dSeries Server
  • Desktop Client
  • Oracle Database 11g and 12c

Instructions: 

Important Note:
CA does not encourage customers to modify the dSeries database
directly, however, there are situations where direct maintenance on the
database is required.  These actions shall be performed by a DBA and in
consultation with CA support.  Performing this action will permanently
remove all entries from the status message tables.

  1. Open a support issue and get approval from CA support to perform the steps below.
  2. Shutdown dSeries (Primary and Standby)
  3. Backup dSeries database

 

Oracle 11g:

  1. 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;

  1. Execute these ALTER TABLE statements above in the order they appear. 
  2. 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';
 
7. 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;

                               

  1. 8.
    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;

  1. 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';

  1. Start dSeries

Oracle 12c:

  1. Execute the truncate statement below

truncate table ESP_STATUS_MESSAGE cascade

  1. Start dSeries

Additional Information:

Refer to KB Article TEC1101632

Refer to CA Workload Automation DE Deployment Best Practices for additional best practices.

Outcomes