Maximo Database Table Data Purging

Maximo Database Table Data Purging is to remove unused data, indexes and attachments that can slow down reads in the database system/storage. 

This activity will reduce the cost of owning the cloud database service for clients.

Some of the items that can be performed: 

• Out of Box Maximo tables that adds non-critical business data that can be removed from the system. for example, CRONTASKHISTORY, ESCREPEATTRACK etc., There are a few sample scripts:

-- delete crontask history records that are more than 2 years old based on start time
delete from crontaskhistory where starttime < dateadd(YEAR,-2,GETDATE());

-- delete escalation repeat tracking records that have records in historyflag
delete from escrepeattrack where escalation='POCLOSE' and ownerid in (select poid from po where historyflag=1);
delete from escrepeattrack where escalation='SRCLOSED' and ownerid in (select ticketuid from sr where historyflag=1);
delete from escrepeattrack where escalation='ESCBLTNEXP' and ownerid in (select BULLETINBOARDuid from BULLETINBOARD where expiredate < dateadd(DAY,-1,GETDATE()));
delete from escrepeattrack where escalation in ('WOCAN','WOCLOSE') and ownerid in (select workorderid from workorder where historyflag = 1);
delete from escrepeattrack where escalation in (select escalation from escalation where active = 0);

-- delete communication log (includes escalation) that are more than 8 years old based on created date
delete from commlog where createdate < dateadd (YEAR,-8,GETDATE());

-- delete Message Tracking records that are more than 3 months old based on initial date time
delete from maxintmsgtrkdtl where  meamsgid in (select meamsgid from MAXINTMSGTRK where initialdatetime < dateadd(DAY,-90,GETDATE()));
delete from maxintmsgtrk where initialdatetime < dateadd(DAY,-90,GETDATE());   

-- delete Integration Error Messages that are more than 2 years old based on statusdate
delete from maxinterrormsg where messageid in (select messageid from maxinterror where statusdate < dateadd(YEAR,-2,GETDATE()));
delete from maxinterror where  statusdate < dateadd(YEAR,-2,GETDATE());

• Identify indexes and tables that aren't being used with help of DBA

• Extract the attachments from doclinks that aren't linked to any records and delete them from storage. 

select * from doclinks a  where a.doclinksid in(select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid not in (select workorderid  from workorder)))

Courtesy: Nausheel Patadia | LinkedIn