Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

Hi Ahmed

You will probably have to create data cleanup scripts for this. I found an interesting review of data archiving solutions in Maximo, might be a useful read

https://bportaluri.com/2023/02/maximo-data-archiving-solutions.html

Data cleanup would need to be handled carefully and my suggestion would be to engage with a data migration specialist. Generally direct data manipulation of the database is discouraged (and with good reason) but if handled properly and with care, then it can be a very effective method of fixing your data. You will need to create a script that traverses through the entire database and cleans all associated tables.

Regards

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

Hi Ahmed

I hope you have a filter/type to identify these workorders. If you have, below process might work.

This data update should be done in sequential steps one after the other.

Step 1: Take back up of all the tables which will be worked on in next steps.

Step 2: Removing wonum field data in related tables

a. You have to find the associations of wonum in various tables for which you have started in right direction querying Maxattribute table. select * from maxattribute where attributename="WONUM" and persistent =1.

b. Take back up of all the tables that you will work on based on the outcome of next step, step c.

c. Clear out the wonum table by table on all the related tables for example on Asset using update statements, except the ones related to workorder which are: WORKORDER, WOACTIVITY, WPLABOR, WPMATERIAL, WPSERVICE, WPTOOL, WPITEM, WOTASKRELATION, WOSTATUS, WOCHANGESTATUS, WOHAZARD, WOLOCKOUT, WOMETER, WOOWNERHISTORY, WOPRECAUTION, WORKVIEW, WOSAFETYPLAN, WOTAGOUT, WOWEATHERALERT, PLUSWOMEASUREMENT, SKDEMAVAILRES. Mentioning these tables based on my experience, I would suggest that you to analyze and add any other table based on your configuration/customization. Basically, in this step we are removing wonum association from tables other than work order tables. 

Step 3: Flagging the data which needs to be deleted

a. Create a new field on all WO related tables( this can be deleted once this data update is done). For example create field WODATACLENUP.

b. Query for a set of WO's -  WO's created in 5 years then update this field WODATACLENUP as DELETESET1.

c. Next query for next set of date for next 5 years and update WODATACLENUP as DELETESET2.

c. For the tables which don't have the report date you have to write joins to update WODATACLENUP field. I am just giving the date created as example as you have quoted years in your question. If you have any other way of easily segregating data, please follow. Also, please make sure no more than 200k records are identified per set.

d. Please make sure to consider wonum when you are flagging this data. For example, in WOSTATUS table, if might have more than one entry for each workorder, so please make sure the wonum selection is consistent across all tables.

Step 4: Delete workorder tables data 

a. Query each table for WODATACLENUP attribute and start delete the data by sets. For example, to delete data from first set, delete from workorder where WODATACLENUP="DELETESET1". 

Step 5: Testing

a. Whenever a huge data update/migration is done, it is best to test basic functionality of workorder even though there is no configuration. For example, testing saving the workorder, taking the workorder through the entire wo status cycle, running reports etc.

Step 6: Deleting backup tables

a. Monitor the application for 1-2 months before purging the back up tables. This step is needed if you want optimize the storage limit.

I would suggest to perform this deletion for a sample of 20-50 records and then implement for rest of the data. Hope this is helpful!

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

------------------------------
Regards, Ahmed
---
Maximo 7.6
------------------------------

Here is a SQL query to generate 'SQL queries' to get workorder referenced in all tables. Replace <wonum>, <orgid> with relevant data.
This is written in MS-SQL, slight changes required for running in other Databases. 

--Generate select queries to find WO reference in all tables--select 'select '+cast((ROW_NUMBER() OVER(ORDER BY objectname ASC)) as varchar)+' , * from '+objectname+' (nolock) where '+attributename+'=''<wonum>'' and orgid=''<orgid>'';', * from maxattributecfg (nolock) where attributename in ('WONUM','REFWO','RECORDKEY')and persistent=1;

Output would be like this.

Thanks!
------------------------------
Sankar Ganesh V S
Technical Consultant
DXC Technology

Original Message:
Sent: Fri April 25, 2025 08:34 AM
From: Ahmed Waguei
Subject: How to properly bulk delete a million workorders

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

------------------------------
Regards, Ahmed
---
Maximo 7.6
------------------------------

Hi All - Use SITEID instead of ORGID. You can have the same work order number in two sites of the same organization.

Regards - Andrew

Here is a SQL query to generate 'SQL queries' to get workorder referenced in all tables. Replace <wonum>, <orgid> with relevant data.
This is written in MS-SQL, slight changes required for running in other Databases. 

--Generate select queries to find WO reference in all tables--select 'select '+cast((ROW_NUMBER() OVER(ORDER BY objectname ASC)) as varchar)+' , * from '+objectname+' (nolock) where '+attributename+'=''<wonum>'' and orgid=''<orgid>'';', * from maxattributecfg (nolock) where attributename in ('WONUM','REFWO','RECORDKEY')and persistent=1;

Output would be like this.

Thanks!
------------------------------
Sankar Ganesh V S
Technical Consultant
DXC Technology

Original Message:
Sent: Fri April 25, 2025 08:34 AM
From: Ahmed Waguei
Subject: How to properly bulk delete a million workorders

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

------------------------------
Regards, Ahmed
---
Maximo 7.6
------------------------------

Here is a SQL query to generate 'SQL queries' to get workorder referenced in all tables. Replace <wonum>, <orgid> with relevant data.
This is written in MS-SQL, slight changes required for running in other Databases. 

--Generate select queries to find WO reference in all tables--select 'select '+cast((ROW_NUMBER() OVER(ORDER BY objectname ASC)) as varchar)+' , * from '+objectname+' (nolock) where '+attributename+'=''<wonum>'' and orgid=''<orgid>'';', * from maxattributecfg (nolock) where attributename in ('WONUM','REFWO','RECORDKEY')and persistent=1;

Output would be like this.

Thanks!
------------------------------
Sankar Ganesh V S
Technical Consultant
DXC Technology

Original Message:
Sent: Fri April 25, 2025 08:34 AM
From: Ahmed Waguei
Subject: How to properly bulk delete a million workorders

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.

------------------------------
Regards, Ahmed
---
Maximo 7.6
------------------------------

Hello guys,

Due to some poorly configured preventive maintenance (and other reasons), uncessary workorders have been generated along the years.

The bad news is: there are +1.5 million of them accross multiple sites ranging from 2014 to 2025.

Some have been approved before being cancelled while others went straight from WAPPR to CAN.

I found 100+ objects in maxattribute table potentially holding "wonum".

What's the proper way to clean the database and get rid of them in bulk: sql, escalation, crontask, ... ?

I'm down for every script/method you may have.

Thanks and let me know if you need more information.