Archival and Purge Strategy for Clarity

[Anon Anon]

We are trying to implement some sort of purge and archival strategy for our Clarity implementation with the primary focus being to reduce the size of key tables and improve performance of our application. Ofcourse archival of the purged data is also required since its a compliance requirement and this data may need to be referred to in the future.

If anyone has done anything similar some reccommendation or "best practices" would be appreciated.

[Deactivated User]

There is no archiving in Clarity, so anything you do purge is gone at that point.
One option is "full archive" ie database dump which your restore in a separate environment.
You can xog investments out before purging and if the standard content of the xml files do not contain what you need to archive you can create portlets and either export to Excel or use their queries wiht the XOG query api to get xml directly.

There are other posts which have other tables which create overhead like notifications, collaboration data, process instancess, job runs (commonly the job schedule page acts as job log and not as future schedule - I've seen a case where a schedule of 100 000 jobs stopped the bg), datamart, timeslices, timeentries to name a few.

Martti K.

[Anon Anon]

We have this practice to take a DB dump from Production and load it to our Dev/Staging environments to get the latest data. In Process while taking Prod dump we excluded the hugh tables of Datamart, Timeslicing and other selected one's which actually would minimize the dump size. The performance would show progress and same time our daily archival purpose was getting served. I would recommend this way as it is very easy and foolproof.

Thanks,
Murali

[Anon Anon]

Hi,

As there is no archival option available in clarity,you can work on the Data Clean Up Activities and some house keeping Jobs.That will help you to reduce data in some key tables which are very frequently used.And in addition we can also do some activities that will help to improve the over all application performance.

Some of them are below.

1. Allocated the JVM in a better way.(Pls look at CA josh Posts)

2. Schedule the Cleanup Jobs Regularly.(See Below) and schedule all jobs in an meaningful way and Schedule the db intensive jobs (data mart job and
investment allocation job) on minimal usage time.

Clean User Sessions - this job will remove user session records
Delete Investments job - Delete selected investments and their associated data including investment hierarchy, financial data, tasks, timesheets and documents etc.
Delete Process Instance - Delete process instances according to the specified parameters
Purge Temporary Hierarchy Data - Removes temporary data created while computing aggregated costs
Purge Audit Trail - Removes audit trail records
Purge Financial Tables - this job will purge financial data from the project selected at run time
Purge Documents - Delete documents based on supplied parameters
Remove Job Logs and Report Library entries - Remove the job log entries and/or the report library entries that are older that the supplied parameters

3. Very Important--> Reduce the Data in the key tables which is used often in the system like User permission (CMN_SEC_ASSGND_OBJ_PERM) and Slice tables.You can work on it by re-creating the slices to suit your needs.(TEC435572 Document).

4.Re-visit your security access rights which will help you considerably.Don't give any additional access rights unwantedly.

5. Put an regular cleanup process and try to enforce an process.This will help to reduce data in User permission and Slice tables.
For every user the function CMN_SEC_CHECK_RIGHT_FCT is being called every second and inturn it will call an view CMN_SEC_ASSGND_OBJ_PERM_VO
which is pointing out the CMN_SEC_ASSGND_OBJ_PERM table.so if the data is reduced in that table then it helps a lot.
(i,e)
a.Periodically remove the access rights for In Active Resources.
b.In all Inactive projects properly update the allocation finish date for all resources and uncheck the open for time entry.The same for inactive resources
in active projects.
c.Ensure that DOT is updated for In Active Resources so they will not be considered for slices.
d.When an project is de-activated then remove the participants who are not active.

6.Data clean up in DB.(Removed the unwanted custom tables,triggers).Clean the temp tables and whenever need you can generate the data.

7.Set in all objects view,portlet views that unless the user click the filter button the data will not displayed.This will also helps you a bit.

8.Restart all the app/bg/reports/NSA related services daily.

9.set the app session time out to an meaning one.

10.Users will access some Links frequently (i.e Financial Plan screen) you can provide some shortcut links,so the application pages accessed by
users will be short,the hits will be low.

11.CA had given some recommendations for performance (Admin guide,Installation Guide).You can try that in dev server.

12.Do some house keeping at server level.Periodically Clear all the Logs under /clarity/logs and logs under sqltrace.

13.If you have written custom nsql queries in custom portlet ensure that it is optimized and the same applicable for custom reports.
Even 1 bad query can haunt server performance.(you can use Tuesday tips by shawn to work on optimization).

14.In the objects if you have created custom attributes you acn de-cativate the attributes which you are not using,if you are sure that you are not using
any more even you can delete those attributes.

15.If you have enabled audit trail re-visit on it.It is all DB triggers written by CA.If it is really essential then keep it otherwise don't use it.

16.If you are using lot of process then You can also monitor the process engine performance then you can opt for an Dedicated BG Service for
the Process Engine.

17.If the user base is more you can opt for multiple application services and use an load balancer.

cheers,
sundar

[Anon Anon]

Wow. Thanks sundar for the Detailed Mail. This helps

[Anon Anon]

In your reference to "a.Periodically remove the access rights for In Active Resources." We have over 6000 In Active Resources" I would like to clean them all up. Do you have sql that would make this easier than doing it all manually one at a time thru the application.?

[Anon Anon]

Hi,

For In activated resources we remove the access rights via XOG.Pls find the sample xml file.

************************************************************************************************************************************************************************

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_user.xsd">

<Header action="write" externalSource="NIKU" objectType="user" version="8.1.3.4545"/>

<Users>

<User externalId=" " userLanguage="English" userLocale="en_GB"

userName="1172507" userStatus="LOCK"

userTimezone="Europe/Brussels" userType="INTERNAL">

<PersonalInformation emailAddress="[e-mail]"

firstName="sundar" lastName="Jayaraman"/>

<Resource resourceId="1172507"/>

<Company/>

<General addedBy="admin" addedDate="2009-02-03"/>

<OBSAssocs completed="true"/>

<Groups completed = "true"/>

<GlobalRights completed = "true"/>

<InstanceRights completed = "true"/>

<InstanceOBSRights completed = "true"/>

</User>

</Users>



<XOGOutput>

<Object type="user"/>

<Status state="SUCCESS"/>

<Statistics failureRecords="0" insertedRecords="0"

totalNumberOfRecords="1" updatedRecords="0"/>

<Records/>

</XOGOutput>

</NikuDataBus>


The ‘completed="true"’ attribute, XOG will delete any group, right or association that is not mentioned explicitly in the .xml file and because there is no group, right or association, everything will be deleted.

By default [completed="false"] XOG will append the information in the .xml file and not delete anything.you need to produce a .xml file, with one User node for each inactive resource, make sure that the OBSAssocs, Groups, GlobalRights, InstanceRights and InstanceOBSRights node are the same as above and then XOG the above .xml file in.


Cheers,
sundar