The number of STX tables in RSA Identity Governance & Lifecycle database is increasing

[#1]

Article Number

000036110

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
 

Issue

More and more STX* tables are showing up in the RSA Identity Governance & Lifecycle database in the AVUSER schema.

Cause

The STX* tables are temporary tables used by collections and of the format STX_{DataType}_{CollectorId}_{RunId}.They are dropped by the collector process as the last step in a collection. If  there is a problem with the collection for any reason and the last step is not completed, these STX tables are not dropped. 

One known reason for the STX tables not being dropped is when the circuit breaker is triggered. 

Resolution

Install 7.0.2 P07 or 7.1.0 P01 to have this issue resolved for when the circuit breaker is triggered. However, cleanup of the existing tables is still needed as described below. You may also use the below procedure if the STX tables are left behind for any other reason.
 

STX tables that are being used by active collections should not be dropped. Therefore, the safest way to remove these tables is to shutdown acm to ensure no collections are running. Steps to follow are shown below.

1. Shutdown acm

$ acm stop

2. Login to SQL as the AVUSER.
3. Execute the following SQL to find all the tables to be dropped:

SELECT table_name FROM user_catalog WHERE table_name LIKE 'STX%'

4. Drop all the STX tables:

BEGIN
     FOR tablename IN (SELECT table_name FROM user_catalog WHERE table_name LIKE 'STX%') LOOP
      EXECUTE IMMEDIATE
     'DROP TABLE ' || tablename.table_name || ' purge'; 
     END LOOP; 
 END;

5. Restart acm:

$ acm start