All Kony Fabric Posts

Updated - Archiving and Purging of MobileFabric Reports Databases

Ajay Bhat - Mar 09, 2017 - Metrics

In this post, Radhakrishna Tanuku follows up on an earlier post where he explained how to archive and purge information in the Kony MobileFabric Metrics database. This post updates and supersedes the information in the earlier post.

Archiving and Purging Process

  1. Stop Application Server / Web Server/ Application before starting archiving.
  2. Download the attached archiving stored procedure and copy it to the host where the database is installed. Rename the file "Archive_table_MySQL.txt" to "Archive_table_MySQL.sql" after downloading it.
  3. Connect to the MySQL database where archiving / purging should be done as shown in the example database below:

Connect to the database

  1. Execute the archiving procedure SQL file referenced in Step 2 above from the MySQL command prompt. This will cause the database tables to be archived.

Run archival script

  • Archiving and purging will be performed using a stored procedure "proc_archive_table" as created in Step 4. This procedure takes the following inputs:
    • Table name
    • Column name
    • Archiving criteria value
    • Archiving condition operator like <,>,!=,<=,>=

The following is an example for archiving metrics table in the MySQL command prompt or any SQL client:

Example for archival procedure call

The above statement will archive all the data which is less than ‘2016-01-25 12:00:00’ (i.e. from before this date) to a new archive table middleware_sessions_arch_current_archival_date (the table name suffixed with _arch and the date on which archival was done) and then deletes the archived data from middleware_sessions table (main table).

Example: if  archival is done at 1:28 PM on March 13, the table will be created as below:

middleware_sessions_arch_20173131328

After archiving and purging, optimization is run for all those tables which are archived.

Archiving Audit

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

Archival audit table

Metrics tables - a comprehensive list

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.

- call proc_archive_table('middleware_sessions','ts','2011-08-03','<');
- call proc_archive_table('middleware_requests','ts','2011-08-03','<');
- call proc_archive_table('application_events','ts','2011-08-03','<');
- call proc_archive_table('application_error_detail','ts','2011-08-03','<');
- call proc_archive_table('application_service_detail','ts','2011-08-03','<');
- call proc_archive_table('application_form_detail','ts','2011-08-03','<');
- call proc_archive_table('dw_keys_request','insert_date','2011-08-03','<');
- call proc_archive_table('dw_keys_session','insert_date','2011-08-03','<');
- call proc_archive_table('metrics_application_log','event_date','2011-08-03','<');
- call proc_archive_table('invalid_messages','insert_date','2011-08-03','<');
- call proc_archive_table('tmp_calendar','dt','2011-08-03','<');
- call proc_archive_table('form_mapping','created_date','2011-08-03','<');
- call proc_archive_table('service_mapping','created_date','2011-08-03','<');

 

To archive/purge specific table custom_metrics_master, we need an output value from the middleware_request table after archival. This output value should be passed to the <above_output_value> of next line for archiving custom_metrics_master.

select MIN(REQUEST_KEY) FROM middleware_requests; 

call proc_archive_table('CUSTOM_METRICS_MASTER','REQUEST_KEY','<above_output_value>','<');

Note:

  1. Post archival, all those archived tables which are ending with _arch_"archival_date" (ie *_arch_archival_date - Archival date would be the date on which the archival script is run) should be backed up to tape/storage/any other media by respective backup plans.
  2. It is recommended the date column value passed is consistent when archiving all tables.