Archiving Repository Database

1. On executing the AWTRepositoryArchivingScript.sql script in the Repository database, the following tables and procedures are created.

   Tables and Procedures	Description
   SKDeleteTable	Table to store the ExecutionID and other details of the records to be deleted from the Repository database (Production database).
   SKDeleteTableFor ArchiveDB	Table to store the ExecutionID and other details of the records to be deleted from the Archived database.
   proc_SKInsertIntoSK DeleteTable	Procedure to insert ExecutionID, ProductionID, Application, Name, Data, LocalVariablesId, and ExecutionDetailsID into the SKDeleteTable table. Note: Records related to workflows with InternalStatus "FN" or "AB" will be inserted into the SKDeleteTable table.
   proc_SKDeleteIn Batches	Procedure to delete all records of a table in batches, where the size of each batch size is 50000 records.
   proc_SKDeleteFrom Production	Procedure to delete the Repository database records, and to update the ‘Isdeleted’ field of the SKDeleteTable table to 1, only if the delete was successful. The procedure will delete workflow records with InternalStatus "FN" or "AB".
   proc_SKDeleteFrom ArchivedDB	Procedure to delete workflow records, which have InternalStatus other than "FN" or "AB", from the Archived database. Note: Ensure that this procedure is executed only in the Archived database.
   proc_SKSKDelete TableInArchiveDB	Procedure to insert ExecutionID, ProductionID, Application, Name, Data, LocalVariablesId, and ExecutionDetailsID into the SKDeleteTableForArchiveDB table. Note: - Records related to workflows with InternalStatus other than "FN" or "AB" will be inserted into the SKDeleteTableForArchiveDB table. - Ensure that this procedure is executed only in the Archived database.

   Store the workflow data to be deleted from the Repository database in the SKDeleteTable table.

   Execute the proc_SKInsertIntoSKDeleteTable procedure on the Repository database.

   Exec proc_SKInsertIntoSKDeleteTable 'fromdate', 'todate', 'applicationname', 'executionstartid', 'executionendid'

   Pass the parameters for the above procedure based on the workflow records to be deleted from the Repository database. Workflow data with InternalStatus ‘FN’ and ‘AB’ will be stored in the database. For example, to delete workflow records executed for a period of 2 years, execute the procedure as follows:

   Exec proc_SKInsertIntoSKDeleteTable '01-01-2013', '12-31-2014', NULL, NULL, NULL

   The above query will insert records between 01-01-2013 and 12-31-2014 with InternalStatus ‘FN’ and ‘AB’ into the SKDeleteTable table.

   The records to be deleted are stored with ExecutionID, ProductionID, Application, Name, Data, LocalVariablesId, ExecutionDetailsID, and Isdeleted details in the SKDeleteTable table. Initially, the ‘Isdeleted’ field is ‘0’ for all the records to be deleted.

2. Store the workflow data to be deleted from the Archived database in the SKDeleteTableFor ArchiveDB table.

   Execute the proc_SKSKDeleteTableInArchiveDB procedure on the Repository database.

   Exec proc_SKSKDeleteTableInArchiveDB

3. Back up the Repository database.

4. Restore the above backup to new database. This can be the archived Repository database.

5. Delete the completed and aborted workflow instances related data from the Repository database tables.

   Execute the proc_SKDeleteFromProduction procedure on the Repository database to delete all the records similar to the ones that exist in the SKDeleteTable table. Workflow records with InternalStatus ‘FN’ or ‘AB’ will be deleted from database.

   Execute the following procedure:

   Exec proc_SKDeleteFromProduction

   After the workflow records are deleted, the Isdeleted field of these records in the SKDeleteTable table is updated to ‘1’.

   To delete 36118960 records, the system took approximately 39:25 minutes in a database of size 221121.63 MB. This time requirement will vary based on the system performance.

6. Delete the records other than that for the completed and aborted workflow from the Archived database.

   Execute the proc_SKDeleteFromArchivedDB procedure on the Archived database to delete all the Repository database table records that are available in the SKDeleteTableForArchiveDB table.

   Workflow records with InternalStatus other than ‘FN’ or ‘AB’ are deleted from the Archived database tables.

   Execute the following procedure :

   Exec proc_SKDeleteFromArchivedDB

Refer to the scenario explained in the Application Scenario section.