Archiving and Purging of VoltMX Foundry Reports Databases

HCL Volt MX Volt MX Tips
1

Archiving and Purging of VoltMX Foundry Reports Databases

Archiving and Purging:

Archival procedure should be run during the maintenance window only.

Note: Stop VoltMX Foundry Application Server before starting archiving.

Note: Available Required scripts for Archive and purge, Go to the VoltMX Foundry Installation Path: C:\VoltFoundryInstaller\scripts\metricsArchiveAndPurgeScripts\metricsArchiveAndPurgeScripts.sql file.

  • Run the metricsArchiveAndPurgeScripts.sql connecting to the database.

The SQL file should create the objects as below:

Table: archive_audit

Procedure: proc_archive_table

  • Extract the summary data exported to excel for future use. Below queries will help to extract the summary information.

Mysql:

Dates in below queries to be replaced with actual dates of data being purged

Events Summary:

SELECT eid, aname, evttype, evtsubtype, plat, DATE_FORMAT(ts, ‘%Y-%m-01’), count(*) FROM application_events WHERE ts between ‘YYYY-MM-DD’ and ‘YYYY-MM-DD’ GROUP BY eid,aname,evttype,evtsubtype,plat,DATE_FORMAT(ts, ‘%Y-%m-01’);

Custom Metrics:

SELECT cust.kaid, cust.eid, cust.aid, count(*) FROM custom_metrics_master cust , middleware_requests ms WHERE cust.request_key = ms.request_key and ms.ts BETWEEN ‘YYYY-MM-DD’ AND ‘YYYY-MM-DD’ GROUP BY cust.kaid, cust.eid, cust.aid;

SESSIONS:

SELECT eid, aname, DATE_FORMAT(ts, ‘%Y-%m-01’), count(*) FROM MIDDLEWARE_SESSIONS WHERE ts BETWEEN ‘YYYY-MM-DD’ AND ‘YYYY-MM-DD’ GROUP BY eid, aname, DATE_FORMAT(ts, ‘%Y-%m-01’);

NAMED USERS:

#1 SELECT eid, count(DISTINCT KUID) FROM MIDDLEWARE_REQUESTS WHERE ts BETWEEN ‘YYYY-MM-DD’ AND ‘YYYY-MM-DD’ GROUP BY eid;

#2 SELECT eid, aname, count(DISTINCT KUID), DATE_FORMAT(ts, ‘%Y-%m-01’) FROM MIDDLEWARE_REQUESTS WHERE ts between ‘YYYY-MM-DD’ AND ‘YYYY-MM-DD’ GROUP BY eid, aname, DATE_FORMAT(ts, ‘%Y-%m-01’);

ANONYMOUS USERS

#1 SELECT eid, count(DISTINCT DID) FROM MIDDLEWARE_REQUESTS WHERE ts BETWEEN ‘YYYY-MM-DD’ AND ‘YYYY-MM-DD’ GROUP BY eid;

#2 SELECT eid, aname, count(DISTINCT DID), DATE_FORMAT(ts, ‘%Y-%m-01’) FROM MIDDLEWARE_REQUESTS WHERE ts BETWEEN ‘YYYY-MM-DD’ AND ‘YYYY-MM-DD’ GROUP BY eid, aname, DATE_FORMAT(ts, ‘%Y-%m-01’);

Important Note: The procedure will give warning for Middleware Sessions and Middleware Requests if we choose to archive or purge data within 13 months date from current period. From a licensing perspective, for metrics, we retain at least 13 months of data for middleware_sessions and middleware_requests table. Other tables can be archived for a different time duration if required.

MSSQL:

Dates in below queries to be replaced with actual dates of data being purged

Events Summary:

select eid, aname, evttype, evtsubtype, plat, CONVERT(DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’) as dt, count(*) from application_events where ts < ‘YYYY-MM-DD’ group by eid, aname, evttype, evtsubtype, plat, (DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’);

Custom Metrics:

select cust.kaid, cust.eid, cust.aid, count(*) from custom_metrics_master cust, middleware_requests ms where cust.request_key = ms.request_key and ms.ts < ‘YYYY-MM-DD’ group by cust.kaid, cust.eid, cust.aid;

SESSIONS:

select eid, aname, CONVERT(DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’) as dt, count(*) from middleware_sessions where ts < ‘YYYY-MM-DD’ group by eid, aname, CONVERT(DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’);

NAMED USERS:

#1 select eid, count(DISTINCT kuid) from middleware_requests where ts < ‘YYYY-MM-DD’ group by eid;

#2 select eid, aname, CONVERT(DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’) as dt, count(DISTINCT kuid) from middleware_requests where ts < 'YYYY-MM-DD’ group by eid, aname, CONVERT(DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’);

ANONYMOUS USERS:

#1 select eid, count(DISTINCT did) from middleware_requests where ts < ‘YYYY-MM-DD’ group by eid;

#2 select eid, aname, CONVERT(DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’) as dt, count(DISTINCT did) from middleware_requests where ts < ‘YYYY-MM-DD’ group by eid, aname, CONVERT(DATETIME, CONVERT(VARCHAR(7), ts, 120) + ‘-01’);

Important Note: The procedure will give warning for Middleware Sessions and Middleware Requests if we choose to archive or purge data within 13 months date from current period. From a licensing perspective, for metrics, we retain at least 13 months of data for middleware_sessions and middleware_requests table. Other tables can be archived for a different time duration if required.

Oracle:

Dates in below queries to be replaced with actual dates of data being purged

Events Summary:

select eid,aname,evttype,evtsubtype,plat, TRUNC(TS,‘MM’),count(*) from application_events where ts between to_Date(‘2017-01-01’, ‘YYYY-MM-DD’) and to_date(‘2017-10-31’, ‘YYYY-MM-DD’ ) group by eid,aname,evttype,evtsubtype,plat, TRUNC(TS,‘MM’);

Custom Metrics:

select cust.kaid,cust.eid,cust.aid,count(*) from custom_metrics_master cust , middleware_requests ms

where cust.request_key = ms.request_key and ms.ts between to_Date(‘2017-01-01’, ‘YYYY-MM-DD’) and to_date(‘2017-10-31’, ‘YYYY-MM-DD’ ) group by cust.kaid,cust.eid,cust.aid;

SESSIONS:

select eid,aname,count(*),TRUNC(TS,‘MM’) from MIDDLEWARE_SESSIONS WHERE ts between to_Date(‘2017-01-01’, ‘YYYY-MM-DD’) and to_date(‘2017-10-31’, ‘YYYY-MM-DD’ ) group by eid,aname, TRUNC(TS,‘MM’)

NAMED USERS:

#1 select eid,count(DISTINCT KUID) from MIDDLEWARE_REQUESTS WHERE ts between to_Date(‘2016-01-01’, ‘YYYY-MM-DD’) and to_date(‘2016-06-30’, ‘YYYY-MM-DD’ ) group by eid;

#2 select eid,aname,count(DISTINCT KUID),TRUNC(TS,‘MM’) from MIDDLEWARE_REQUESTS WHERE ts between to_Date(‘2016-01-01’, ‘YYYY-MM-DD’) and to_date(‘2016-06-30’, ‘YYYY-MM-DD’ ) group by eid,aname,TRUNC(TS,‘MM’);

ANONYMOUS USERS

#1 select eid,count(DISTINCT DID) from MIDDLEWARE_REQUESTS WHERE ts between to_Date(‘2016-01-01’, ‘YYYY-MM-DD’) and to_date(‘2016-06-30’, ‘YYYY-MM-DD’ ) group by eid;

#2 select eid,aname,count(DISTINCT DID),TRUNC(TS,‘MM’) from MIDDLEWARE_REQUESTS WHERE ts between to_Date(‘2016-01-01’, ‘YYYY-MM-DD’) and to_date(‘2016-06-30’, ‘YYYY-MM-DD’ )

group by eid,aname,TRUNC(TS,‘MM’) ;

Important Note: The procedure will give warning for Middleware Sessions and Middleware Requests if we choose to archive or purge data within 13 months date from current period. From a licensing perspective, for metrics, we retain atleast 13 months of data for middleware_sessions and middleware_requests table. Other tables can be archived for a different time duration if required.

  • Archiving and purging or purging alone will be performed using a stored procedure “proc_archive_table”.

This procedure takes the following inputs:

→ Table name

→ Column name

→ Archiving/Purging criteria value

→ Archiving/Purging condition operator like <,>,!=,<=,>=

→ Type like “archive” or “purge”.

  • The type value “archive” will create an archive table equivalent rows as part of the Archiving criteria value and Archiving conditions defined. It also purges the records after successfully archiving the table. The archive table will occupy the equivalent space of the original table based on the defined conditions.

  • The type value “purge” will only purge the records equivalent rows as part of the purging criteria value and purging conditions defined.

The following is an example for archiving metrics table:

Archive

  • call proc_archive_table(‘application_form_detail’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’)

  • The above statement will archive all the data which is less than ‘YYYY-MM-DD 00:00:00” (i.e. from before this date) to a new archive table application_form_detail_1(the table name suffixed with _1 ) and then deletes the archived data from application_form_detail table (main table).

  • Example: if archival is done for the first time, the table will be created as below: application_form_detail_1. Subsequent archrivals will create tables like application_form_detail_2, etc.

Purge

  • call proc_archive_table(‘APPLICATION_EVENTS’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’)

  • The above statement will purge((which is deleted without archiving)) all the data which is less than ‘YYYY-MM-DD 00:00:00’ (i.e. from before this date) from application_events table (main table).

Archiving Audit

  • Archiving audit information is maintained in another table “archive_audit”. You can see the following sample output from this table:

Metrics tables - comprehensive list -Mysql

Below are identified tables and columns that will be used to archive and purge: Values for the columns and the operator mentioned below are just samples and the values and operator can be changed appropriate to the archival requirement.

NOTE: custom_metrics_master table need to be archived/purged before the middleware_request table is archived/purged.

  • call proc_archive_table(‘middleware_sessions’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘custom_metrics_master’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘middleware_requests’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘application_events’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘application_error_detail’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘application_service_detail’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘application_form_detail’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘dw_keys_request’,‘insert_date’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘dw_keys_session’,‘insert_date’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

  • call proc_archive_table(‘invalid_messages’,‘insert_date’, ‘YYYY-MM-DD’,‘<’, ‘archive’);

If we want to do only purge we need to execute the commands as follows:

  • call proc_archive_table(‘middleware_sessions’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘custom_metrics_master’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘middleware_requests’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘application_events’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘application_error_detail’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘application_service_detail’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘application_form_detail’,‘ts’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘dw_keys_request’,‘insert_date’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘dw_keys_session’,‘insert_date’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

  • call proc_archive_table(‘invalid_messages’,‘insert_date’, ‘YYYY-MM-DD’,‘<’, ‘purge’);

Metrics tables - comprehensive list -MSSQL

Below are identified tables and columns that will be used to archive and purge: Values for the columns and the operator mentioned below are just samples and the values and operator can be changed appropriate to the archival requirement.

NOTE: custom_metrics_master table need to be archived/purged before the middleware_request table is archived/purged.

  • EXEC proc_archive_table ‘middleware_sessions’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘custom_metrics_master’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘middleware_requests’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘application_events’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘application_error_detail’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘application_service_detail’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘application_form_detail’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘dw_keys_request’, ‘insert_date’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘dw_keys_session’, ‘insert_date’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

  • EXEC proc_archive_table ‘invalid_messages’, ‘insert_date’, ‘YYYY-MM-DD’, ‘<’, ‘archive’;

If we want to do only purge we need to execute the commands as follows:

  • EXEC proc_archive_table ‘middleware_sessions’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘custom_metrics_master’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘middleware_requests’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘application_events’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘application_error_detail’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘application_service_detail’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘application_form_detail’, ‘ts’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘dw_keys_request’, ‘insert_date’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘dw_keys_session’, ‘insert_date’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

  • EXEC proc_archive_table ‘invalid_messages’, ‘insert_date’, ‘YYYY-MM-DD’, ‘<’, ‘purge’;

Metrics tables - comprehensive list -Oracle

Below are identified tables and columns that will be used to archive and purge: Values for the columns and the operator mentioned below are just samples and the values and operator can be changed appropriate to the archival requirement.

NOTE: custom_metrics_master table need to be archived/purged before the middleware_request table is archived/purged.

  • exec proc_archive_table(‘MIDDLEWARE_SESSIONS’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘CUSTOM_METRICS_MASTER’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘MIDDLEWARE_REQUESTS’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_EVENTS’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_ERROR_DETAIL’,‘ts’, to_date(‘2011-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_SERVICE_DETAIL’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_FORM_DETAIL’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘DW_KEYS_REQUEST’,‘insert_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘DW_KEYS_SESSION’,‘insert_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘METRICS_APPLICATION_LOG’,‘event_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

  • exec proc_archive_table(‘INVALID_MESSAGES’,‘insert_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘archive’, ‘DATA_TS’);

If we want to do only purge we need to execute the commands as follows:

  • exec proc_archive_table(‘MIDDLEWARE_SESSIONS’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘CUSTOM_METRICS_MASTER’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘MIDDLEWARE_REQUESTS’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_EVENTS’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_ERROR_DETAIL’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_SERVICE_DETAIL’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘APPLICATION_FORM_DETAIL’,‘ts’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘DW_KEYS_REQUEST’,‘insert_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘DW_KEYS_SESSION’,‘insert_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘METRICS_APPLICATION_LOG’,‘event_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

  • exec proc_archive_table(‘INVALID_MESSAGES’,‘insert_date’, to_date(‘2023-08-03’, ‘YYYY-MM-DD’),‘<’, ‘purge’, ‘DATA_TS’);

Note:

  1. Post archival, all those archived tables which are ending with _digits(1,2,etc) should be backed up to tape/storage/any other media by respective backup plans before dropping them.
  2. It is recommended the date column value passed is consistent when archiving all tables.
  3. In case archival is not needed, summary should be extracted, and above executable statements can be replaced with ‘purge’ in place of ‘archive’.