Troubleshooting
Problem
Work order generation in the Preventive Maintenance application fails on one or more PMs. The SystemOut.log indicates BMXAA4211E errors on WOANCESTOR.
Symptom
PM work order generation fails for one or more PMs, and the SystemOut.log shows errors similar to the following:
BMXAA4211E - Database error number 2601 has occurred when operating on WOANCESTOR : Site=02 Work Order=4537279 Ancestor=4537279. Report the error to the owner of the deployment.
Database error number 2601 is specific to SQL Server. On Oracle, the database error number would be ORA-00001. On DB2, the error number would be -803.
Cause
Orphan records can exist in databases that are created as copies of other databases or in databases where archiving is used if foreign key-related records are not removed with the primary key record. The example in this document is from WORKORDER (the table containing the primary key WONUM) to WOANCESTOR (the table containing the foreign keys from WORKORDER.WONUM to WOANCESTOR.WONUM and WOANCESTOR.ANCESTOR). Orphan records should not exist as a consequence of normal functionality. If you suspect the problem is due to Maximo functionality, you should contact support before proceeding with the resolution described in this document. Proceed with the resolution herein only if you are confident the problem is due to an issue with the origin of the database (created from a copy of another database and not fully cleaned) or with incomplete archiving. If you do contact support because you believe the problem is with Maximo functionality, we may first ask you to remove the orphan WOANCESTOR records as outlined in this document, disable autoscripts, and remove customizations to expedite root cause determination.
Environment
Maximo Asset Management (All)
Diagnosing The Problem
After executing the following steps...
- Go To -> Preventive Maintenance -> Preventive Maintenance
- Press return to retrieve all records
- Tick Selected Records
- Tick PM so all records are selected
- More Actions -> Generate Work Orders
- Click OK.
...you observe the following error message in the UI:
BMXAA3212E - Error while generating work order for PM 8400.
In the SystemOut.log, you notice the following:
BMXAA4211E - Database error number 2601 has occurred when operating on WOANCESTOR : Site=02 Work Order=WO1234 Ancestor=WO1234. Report the error to the owner of the deployment.
In order to get a complete accounting of them, the necessary orphan entries need to be identified.
The following SQL statements identify WOANCESTOR records whose WONUM and/or ANCESTOR values do not have a corresponding WORKORDER record:
- select * from woancestor where siteid='01' and not exists (select 1 from workorder where siteid=woancestor.siteid and wonum=woancestor.wonum);
- select * from woancestor where siteid='01' and not exists (select 1 from workorder where siteid=woancestor.siteid and wonum=woancestor.ancestor);
Resolving The Problem
The to remove orphan WOANCESTOR records is as follows:
1. ***Back up the WOANCESTOR table.*** Do not proceed further until you have done this.
2. Execute the following:
select 'delete from woancestor where woancestorid='||woancestorid||';' from woancestor where not exists (select 1 from workorder where siteid=woancestor.siteid and wonum=woancestor.wonum);
3. Copy the output and paste into the SQL execution pane as a script (for execution in step 3).
4. Execute the "delete" script.
5. commit;
6. Execute the following to create the next "delete" script:
select 'delete from woancestor where woancestorid='||woancestorid||';' from woancestor where not exists (select 1 from workorder where siteid=woancestor.siteid and wonum=woancestor.ancestor);
If no rows are returned then the first "delete" script was adequate and you do not have to proceed any further.
7. Copy the output and paste into the SQL execution pane as a script for execution in step 3.
8. Execute the delete script.
9. commit;
The issue should now be resolved.
Product Synonym
EAM
Was this topic helpful?
Document Information
Modified date:
03 September 2018
UID
ibm10728099