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
- Stop Application Server / Web Server/ Application before starting archiving.
- 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.
- Connect to the MySQL database where archiving / purging should be done as shown in the example database below:
- 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.
- 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:
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:
After archiving and purging, optimization is run for all those tables which are archived.
Archiving audit information is maintained in another table "archive_audit". You can see the following sample output from this 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>','<');
- 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.
- It is recommended the date column value passed is consistent when archiving all tables.