search cancel

CA Release Automation Database Care and Feeding

book

Article ID: 201295

calendar_today

Updated On:

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

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: 1602557914842__MySQLBestPracticeGuide-2-0.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)

NoteRefer 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.

Attachments

1602557914842__MySQLBestPracticeGuide-2-0.zip get_app
1602557804334__OracleBestPracticeGuide-3-0.zip get_app
1602557792645__SQLBestPracticeGuide-3-0.zip get_app