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

Document created by RSA Customer Support Employee on Mar 6, 2018Last modified by RSA Customer Support Employee on Jan 28, 2019
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000036110
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
 
IssueMore and more STX* tables are showing up in the RSA Identity Governance & Lifecycle database in the AVUSER schema.
CauseThe 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. 
ResolutionInstall 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


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


SELECT table_name FROM user_catalog WHERE table_name LIKE 'STX%'


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


  1. Restart acm:


$ acm start

Attachments

    Outcomes