CA Release Automation Database Care and Feeding

Products

• CA Release Automation - Release Operations Center (Nolio)
• CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

How to purge old historical data in CA Release Automation (RA)?

Environment

Release : 6.6, 6.7,6.8

Component : CA RELEASE AUTOMATION CORE

Resolution

Below mentioned scripts for respective DB type can be executed to purge historical data for Release Automation(CARA).

Pre requisites:

• Create a backup/dump of your current DB, can be used for recovery if required
• Execute purge store procedures during non-peak hours, as the execution may be extensive depending on DB size.

 

• Oracle (referenced zip: 1602557804334__OracleBestPracticeGuide-3-0.zip)

• • CA Release Automation Database Care and Feeding Guide- Oracle.pdf
  • create_FK_indexes_oracle.sql
  • sp_purge_oracle.sql

 

• MS-SQL (referenced zip: 1602557792645__SQLBestPracticeGuide-3-0.zip)

• • CA Release Automation Database Care and Feeding Guide- SQL.pdf
  • create_job_for_purging_execution_jobs - sqlserver.sql
  • sp_purge_audit - sqlserver.sql
  • sp_purge_execution_jobs - sqlserver.sql

 

• MySQL (referenced zip: MySQLBestPracticeGuide-2-0_1662458238547.zip)
• • Release Automation Database Purging - MySQL.pdf
  • sp_purge_audit_tables-mysql.sql
  • sp_purge_execution_jobs-mysql-v1.1.sql
  • sp_purge_execution_jobs-mysql.sql (old version, the new version is v1.1 above)

Note: Refer to respective guides for details around script, tables impacted, execution etc.

---

Script Improvements:

•  Additional logging capabilities are improved in the script (sp_purge_audit.*.sql) which will provide additional information on STDOUT.

Note: For Oracle scripts the script logs will be displayed at end of script execution (as per Oracle design)

---

Script Execution:

• Script need to be executed as it is described in the PDF guide.

---

Additional Information

• Capture Records Count

Disclaimer: The generic queries below is to capture records count, it is neither a recommendation/guidelines and neither liable for any technical Support from CA Release Automation Product. These are just shared as informative and should be used by user discretion.

• Oracle DB

SET SERVEROUTPUT ON
DECLARE  
        val NUMBER;
BEGIN  
        FOR I IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE '%_AUD' ORDER BY TABLE_NAME) LOOP    
             EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || i.table_name INTO val;    
             DBMS_OUTPUT.PUT_LINE(RPAD(i.table_name, 40, ' ') || ' ==> ' || val );  
        END LOOP;
END;

• MS-SQL

SELECT A.name AS TableName, SUM(B.rows) as RecordCount
     FROM sys.objects A INNER JOIN sys.partitions B 
     ON A.object_id= B.object_id
     WHERE A.type='U' AND name LIKE '%_aud'
     GROUP BY A.schema_id,A.name
     ORDER BY RecordCount DESC;

---

• Oracle specific consideration
  • While execution of script you may face an issue with reaching buffer overflow limit running the script. There are 2 ways to resolve this issue:
    • To specify the maximum buffer size. Run this before running the sp_purge.purge_audit procedure:

SET SERVEROUTPUT ON SIZE 1000000

• • To reduce the time_limit parameter - this means less data will be removed in one run, and as result, less information will be logged.