05-27-2017 02:24 AM
Our customer has ECM 6.3 installed. They have loaded some test cases for user testing.
They now want to purge all the case data in ECM for new test runs. What we have learnt is that it's not as easy as just dropping the tables in Oracle since the case data hooks into many places. The instructions we received from Cary is also very manual with no guarantees that it will work.
Does anyone have experience in this ?
05-30-2017 04:43 AM - edited 05-30-2017 04:53 AM
Can you share here what you've got from SAS Cary?
It's been a while but yes, I've done this in the past and it wasn't that hard. You just need to delete the records from the tables in the right sequence to not get hit by integrity constraints.
I've attached code which should do the job. Make sure you take a database backup before you execute it AND you use this code at your own risk.
The code was sufficient for the implementation I was working on. Not sure if there could be additional case tables not covered in the script (I believe not). You'll need to check in your environment but if there is more then it should logically just be "more of the same".
May be take a copy of all rk's in case_live prior to running the attached code so should you later on find another case related table which needs a clean-out then you'll still have the required information to do so.
05-30-2017 09:22 PM
Thanks for this.
Below I'll paste what we received from Cary. There appears to be two components, Oracle content and content within the SAS Content Server. For Oracle, I was thinking about just running the SAS provided drop/load DDL scripts as per the ECM Admin guide. From your code it seems like we only have to selectively delete data. Any risk if we simply drop and re-create all the Oracle tables ?
It's the data within the SAS Content Server (if any) that I'm unsure about since the db seems to contain system config as well. Is this correct ?
The SCS repository persists with SS and a Repository rebuild occurs when executed with the initial JVM
In the past I have done something very similar to what your suggesting, it was however on the SAS 9.3 version. We were able to migrate the Oracle schemas for ECM and SS successfully from environment to environment. You however just purge or rename the SCS Repository directory where persistence would rebuild. I had a colleague attempt to move the SCS repository from environment to environment last year (on SAS 9.4) and was unsuccessful. However I cannot say if his effort shares the same method your outlining here. So far my experience in 9.4 has been to use purge scripts, but honestly this could be because we use WIPDS in most cases these days. I am trying to track down one or more we used recently on a project and see if we can share those here.
Any route you wish to try, just be sure to back up the existing structure for easy recovery. If you attempt the method I mention take note of a table in SS – SAS_Theme. This table has a single row which contains internal and external host properties for the specific environment and may need to be updated post-migration."
05-30-2017 09:37 PM
As I understand it this is not about migrating content to a different environment but about purging the data in the same environment to allow for another test cycle.
We had in my case so few attachments that I didn't care about some left-overs. I don't believe leaving these objects under the content server (Postgres) will impact on test valididty.
I wouldn't DROP and re-create the tables but I believe you could just use a TRUNCATE.
I went for the delete approach because I've considered this as the safest option (after having initially deleted too much from party_x_party when I've implemented my cleanout scripts).
The volumes in the case tables shouldn't be high so the elapsed time difference between a DELETE and a TRUNCATE shouldn't really matter.
05-31-2017 12:41 AM
05-31-2017 09:18 AM
05-31-2017 06:58 PM - edited 05-31-2017 07:05 PM
My ECM experience comes from a SAS AML implementation but I'm far from being an ECM expert.
06-15-2017 10:42 PM
Apologies for the delay in response.
I'm pleased to say this has been resolved.
Since the solution was implemented, the customer has been inserting data into ECM from their old case management systems. To purge the data, they really just did the reverse and adding some additional table names that you provided. Between the two, it seems like all the data can be purged and the system is stable. They will now test this process a couple of times.
Thanks so much for you help with this, you were a lifesaver
06-16-2017 06:05 AM
Thanks for the feedback. That's much appreciated.
I've had in-between the chance to have a chat with a real expert. What she told me is that cleaning out the ECM case tables should work as we've discussed but that it can become really hard to clean out unfinished Workflows.
What this expert recommended is to finish all Workflows manually before you clean-out ECM.