How to truncate DE messaging tables in DB2?

Products

• DSERIES- SERVER
• CA Workload Automation DE - System Agent (dSeries)

Issue/Introduction

The number of records in the DE messaging tables has exceeded certain limit where DB2 transaction logs space cannot accommodate the requested delete operations. 

What are the steps to truncate DE messaging tables?

Environment

Release: DSWAHA01300-12-Workload Automation DE-High Availability
Component:

Resolution

1. Open a support issue and get approval from CA support to perform the steps below.  

2. Shutdown dSeries HAC (Primary and Standby)  servers, or the Standalone server.

3. Backup dSeries database 

4. Execute  three SQL statements shown below to obtain the ALTER statements to drop the Foreign Key constraints. 

select CONCAT('ALTER TABLE ', TABNAME),CONCAT(' DROP FOREIGN KEY ', CONSTNAME) from syscat.references where REFTABNAME = 'ESP_STATUS_MESSAGE'

select CONCAT('ALTER TABLE ', TABNAME),CONCAT(' DROP FOREIGN KEY ', CONSTNAME) from syscat.references where REFTABNAME = 'ESP_SM_SNMP'

select CONCAT('ALTER TABLE ', TABNAME),CONCAT(' DROP FOREIGN KEY ', CONSTNAME) from syscat.references where REFTABNAME = 'ESP_SM_LOG'

 

For example, the output will look like this:

 

ALTER TABLE ESP_SM_AGENT DROP FOREIGN KEY SQL160825122747020

ALTER TABLE ESP_SM_CLIENT_CONNECTION DROP FOREIGN KEY SQL160825122747030

ALTER TABLE ESP_SM_EVENT_AUTHORIZATION DROP FOREIGN KEY SQL160825122747040

ALTER TABLE ESP_SM_LOGIN_FAILURE DROP FOREIGN KEY SQL160825122747050

ALTER TABLE ESP_SM_MEMORY_USAGE DROP FOREIGN KEY SQL160825122747060

ALTER TABLE ESP_SM_SERVER_STATUS DROP FOREIGN KEY SQL160825122747060

ALTER TABLE ESP_SM_SNMP DROP FOREIGN KEY SQL160825122747070

ALTER TABLE ESP_SM_LICENSE_STATUS DROP FOREIGN KEY SQL160825122747080

ALTER TABLE ESP_SM_LDAP_STATUS DROP FOREIGN KEY SQL160825122747100

ALTER TABLE ESP_SM_QUIESCE_STATUS DROP FOREIGN KEY SQL160825122747110

ALTER TABLE ESP_SM_LOG DROP FOREIGN KEY SQL160825122747120

ALTER TABLE ESP_SM_SNMP_VARIABLE DROP FOREIGN KEY SQL160825122747090            

ALTER TABLE ESP_SM_LOG_STACKTRACE_ELEMENT DROP FOREIGN KEY SQL160825122747120            

 

5. Execute these ALTER TABLE queries. 

6. Execute the TRUNCATE statements in the order shown below: 

TRUNCATE ESP_STATUS_MESSAGE IMMEDIATE

TRUNCATE ESP_SM_ACTIVE_APPLS IMMEDIATE

TRUNCATE ESP_SM_AGENT IMMEDIATE

TRUNCATE ESP_SM_CLIENT_CONNECTION IMMEDIATE

TRUNCATE ESP_SM_EVENT_AUTHORIZATION IMMEDIATE

TRUNCATE ESP_SM_LDAP_STATUS IMMEDIATE

TRUNCATE ESP_SM_LICENSE_STATUS IMMEDIATE

TRUNCATE ESP_SM_LOG IMMEDIATE

TRUNCATE ESP_SM_LOG_STACKTRACE_ELEMENT IMMEDIATE

TRUNCATE ESP_SM_LOGIN_FAILURE IMMEDIATE

TRUNCATE ESP_SM_MEMORY_USAGE IMMEDIATE

TRUNCATE ESP_SM_QUIESCE_STATUS IMMEDIATE

TRUNCATE ESP_SM_SERVER_STATUS IMMEDIATE

TRUNCATE ESP_SM_SNMP IMMEDIATE

TRUNCATE ESP_SM_SNMP_VARIABLE IMMEDIATE

 

7. Execute the ALTER TABLE statements to recreate the Foreign Key constraints:

 

ALTER TABLE ESP_SM_ACTIVE_APPLS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_AGENT ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_CLIENT_CONNECTION ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_EVENT_AUTHORIZATION ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_LOGIN_FAILURE ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_MEMORY_USAGE ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_SERVER_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_SNMP ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE

ALTER TABLE ESP_SM_LICENSE_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE

ALTER TABLE ESP_SM_SNMP_VARIABLE ADD FOREIGN KEY (ID) REFERENCES ESP_SM_SNMP ON DELETE CASCADE

ALTER TABLE ESP_SM_LDAP_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE

ALTER TABLE ESP_SM_QUIESCE_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE

ALTER TABLE ESP_SM_LOG ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE

ALTER TABLE ESP_SM_LOG_STACKTRACE_ELEMENT ADD FOREIGN KEY (ID) REFERENCES ESP_SM_LOG ON DELETE CASCADE 

 

8. Start the dSeries server(s).