Migration of history cases to another DB - BOS 5.8, Tomcat 6, JDK 1.7 ,Windows Server 2008 R2

Hi,

I was trying to delete Bonita archival cases as when the archived cases go above 20000, it starts giving performance issues. Right now, somehow in our Production environment, there are 22000 archival cases and we needed to find a solution to delete the cases soon. I already have a process in place to get the archived instances and delete through Bonita API itself. But there is a requirement to first move all the archival/history data to another database, i.e. if I am having around 20000 cases in my Bonita_History DB, I want to move all the data of these cases to another Bonita_history_ backup DB before running my deletion process. I am worried, as to how can I migrate all the history instances from one DB to another using API. Please help me on it.

I have also found that there are total 70 tables, out of which only 9 tables have the actual archival data. I will update on these 9 table names tomorrow when I will be in office.

I was just wondering if I could find the relations in these 9 tables and write a stored procedure to just migrate the data of these 9 tables to another DB(I can call this stored procedure by connecting to external DB using DB connector), will this be okay or I will face issues if anytime I require to migrate the data back to my original DB. I am not sure if Bonita does any internal processing with all or may be some of the other tables.

Also, there is one thing I want to confirm, if I move lets say archival cases #1 to #10 from DB_1 to external DB_2, then next time I want to put the remaining archival cases, what archival instance # IDs would they be, are they always incremental even if I restart my server in between because if I get again #1..or anything until #10, and I try migrate these into the DB_2, it might fail giving primary key integrity constraint. So, this has to be kept in my mind as I cannot have multiple external DBs to keep the archival data(all the archival cases need to be migrated into single external DB).

I need urgent help on this, if someone could provide some solution or strategy to migrate the history cases to an external DB before they are sent for deletion.

Regards, Sehej