SAS Risk Dimensions, SAS High Performance Risk, SAS Enterprise GRC and other banking and insurance solutions

Purge case data from ECM 6.3

Reply
Frequent Contributor
Posts: 93

Purge case data from ECM 6.3

Morning all,

 

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 ?

 

Thanks,

Nico. 

Respected Advisor
Posts: 3,894

Re: Purge case data from ECM 6.3

[ Edited ]

Hi @nhvdwalt

 

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.

 

 

Attachment
Frequent Contributor
Posts: 93

Re: Purge case data from ECM 6.3

Hi Patrick,

 

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 ?

 

Many thanks,

Nico.

 

Cary response....

"

The SCS repository persists with SS and a Repository rebuild occurs when executed with the initial JVM

  • You are correct attachments are being stored in SCS as BLOBs
  • You are correct the SAS B&R will not backup SS if its external to WIPDS

 

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."

 

Respected Advisor
Posts: 3,894

Re: Purge case data from ECM 6.3

@nhvdwalt

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.

Frequent Contributor
Posts: 93

Re: Purge case data from ECM 6.3

Yes, it's a purge not a migration. I don't think the message to Cary was
clear, but well spotted.

Thanks for this, we will be trying this today.

Thanks a mil,

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Frequent Contributor
Posts: 93

Re: Purge case data from ECM 6.3

Hi Patrick,

 
I spent some time with the client today.
 
Over time, it turns out, they have been purging data on the tables mentioned in your scripts.
 
Their problem is the 'what else?' factor i.e.
 
1.) Is there any content in the ECM database, apart form the tables in the script ?
2.) Content in the Shared Services schema ?
3.) Content in the Content Server ?
 
There are hundreds of tables all hooking into each other, so the concern is that a case, might span many tables with different parts i.e. comments, attachments, audit, workflow, etc
 
What is your view ?
 
Thanks,
Nico.
Respected Advisor
Posts: 3,894

Re: Purge case data from ECM 6.3

[ Edited ]

Hi Nico,

 

My ECM experience comes from a SAS AML implementation but I'm far from being an ECM expert.

 

1.) Is there any content in the ECM database, apart form the tables in the script ?
ECM uses a Entity–Attribute–Value (EAV) data model. In my understanding such a model should be very stable in regards of table structures and customizations like additional tables and columns get implemented as virtual tables defined in data but don't require structural changes to tables. 
I can't know what has been done at your site and if there are customizations which have anyway implemented additional tables structures. If not then there shouldn't be hundreds of tables.
If there are additional virtual tables for cases which have been defined properly then one should be able to identify these by analysing the case...def table (forgot the actual name) and this could also be done scripted generating additional deletion code dynamically as necessary.
 
There are hundreds of tables all hooking into each other
There should'n be hundreds of tables in ECM and it's in ECM where your case information gets stored and maintained.
 
i.e. comments, attachments, audit, workflow, etc
comments: I believe these are stored in ECM case tables directly so the script I've posted should capture them
 
attachments: content server. I never cared deleting these in a non-prod environment and for testing purposes only so can't tell you. I would expect that the identifying value used is case_rk but I don't know how and where this is stored under the content server.
 
audit: That should be ECM_EVENT. If you care then expand the script and delete case events from this table. You need delete everything which is a case and has a matching rk to case_version.
 
workflow: Haven't done this myself but I believe the only thing you need to do here is to stop instances currently running. Not sure though how to do this.
 
 
If this is a real problem for your customer and not only about being perfectionist then "someone" must have implemented this at your customer's site and understand how things work. If the customer lost this expertise and you don't know either then another option would be to engage with SAS professional services and get a real ECM expert look into this.
 
Thanks,
Patrick
Frequent Contributor
Posts: 93

Re: Purge case data from ECM 6.3

Hi Patrick,

 

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 Smiley Happy

 

Regards,

Nico.

Respected Advisor
Posts: 3,894

Re: Purge case data from ECM 6.3

Hi Nico,

 

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.

 

Thanks,

Patrick

 

Frequent Contributor
Posts: 93

Re: Purge case data from ECM 6.3

Thanks Patrick, will let the customer know.

 

Regards,

Nico.

Ask a Question
Discussion stats
  • 9 replies
  • 351 views
  • 0 likes
  • 2 in conversation