Maximo Database Table Data Purging

Maximo Database Table Data Purging involves removing unused data, indexes, and attachments that can slow down database read operations and consume unnecessary storage. 

Performing regular data purging helps improve database performance and reduce the storage cost of owning the cloud database service for clients.

Prerequisite: Always take a full Database backup before running purge scripts, especially in Production environment.

Common Data Cleanup Activities 

• Removing non-critical business data from out-of-the-box Maximo tables 

      Certain Maximo tables store operational or historical data that may no longer be required after a period of time. for example, CRONTASKHISTORY, ESCREPEATTRACK etc., Below are some sample scripts that can be used to purge old records.

-- 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 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 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 child records first before parent records to avoid orphan records and maintain referential integrity in the database.

-- 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());

• Batch Deletions 

        Deleting large volume of data in a single transaction can fill up transaction log and impact database performance. So, it's recommended to perform the delete operation in batches.

-- SQL Server Script
SET NOCOUNT ON;
 
    DECLARE @DeletedRows INT = 0;
    DECLARE @TotalDeleted BIGINT = 0;
    DECLARE @BatchStart DATETIME;
    DECLARE @StartTime DATETIME = GETDATE();
 
    PRINT CONCAT('Cleanup started at ', CONVERT(VARCHAR(19), @StartTime, 120), '.');
 
    WHILE 1 = 1
    BEGIN
        SET @BatchStart = GETDATE();
 
       delete top (50000) from escrepeattrack 
       where escalation='WOCLOSE' and objectname = 'WORKORDER' 
       and not exists (select 1 from workorder (nolock) 
       where escrepeattrack.ownerid = workorderid); 
 
        SET @DeletedRows = @@ROWCOUNT;
        SET @TotalDeleted += @DeletedRows;
        
        PRINT CONCAT('Deleted ', @DeletedRows, ' rows in this batch (started at ', 
                     CONVERT(VARCHAR(19), @BatchStart, 120), 
                     '). Total deleted so far: ', @TotalDeleted, '.');
         
        IF @DeletedRows = 0
            BREAK;
        
        -- Checkpoint after each batch to flush data and logs
        CHECKPOINT;
 
        -- Optional pause to reduce load
        WAITFOR DELAY '00:00:04';
    END;

• Reclaim Unused Data After Large Deletions - Database dependent
• When a large number of records are deleted from a table, unused space may still remain allocated.
• For MS SQL Server, rebuild the clustered indexes on the table after cleanup process to reclaim unused data pages and improves overall database storage efficiency. 
• This approach varies for other databases like DB2 or Oracle.  

ALTER INDEX [indexname] ON [dbo].[tablename] 
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE)), ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, DATA_COMPRESSION = PAGE)

• Create a procedure with all delete scripts if you plan to run same set of scripts against multiple databases and schedule to run in fixed intervals.

• Identifying Unused Tables and Indexes 
• Identify indexes and tables that aren't being used with help of DBA
• Disable the Indexes and drop the tables
• Always review the list with customer or business stakeholders before taking any action           

• Cleaning Up Unlinked Attachments

          Attachments from doclinks table may not be linked to valid records. 

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

Best practices
Automate purge scripts using stored procedure if you plan to run same set of scripts against multiple databases
Schedule cleanup jobs during off-peak hours 
Test scripts in lower environments before production
Monitor performance impact

Courtesy: Nausheel Patadia