May 26, 2013

How to rename objects

You have probably noticed that once you have saved an object you are not allowed to change it's ID. This is basically because each object stored on the database needs an immutable unique identifier that can be used to reference that object from other tables. Maximo prevents updating such identifiers to avoid loosing links between records referencing each other.

However there is a little trick you can apply to update the ID of an object in the main table and all the related records. The technique consists in using the Data Dictionary stored in MAXATTRIBUTE table to find all the possible tables and fields where references to the renamed object could be stored.

Let's pretend we have to rename an object stored in the [TABLE] table and whose ID is stored in attribute [IDATTR]. The following SQL query will generate a set of update statements to update both the main record and all its potential references.

SELECT 'update ' || a.objectname ||
       ' set ' || a.attributename || '=''[NEWVALUE]''' ||
       ' where ' || a.attributename || '=''[OLDVALUE]'';'
FROM maxattribute a
JOIN maxobject o ON o.objectname=a.objectname
WHERE a.persistent=1 AND o.isview=0
  AND ((a.sameasobject='[TABLE]' AND a.sameasattribute='[IDATTR]') OR
       (a.objectname='[TABLE]' AND a.attributename='[IDATTR]'))
ORDER BY a.objectname, a.attributename;


Example

For example, if we need to rename a security group called 'MAINGRP1' to 'NEWGROUP' you will need to run the following query.

SELECT 'update ' || a.objectname ||
       ' set ' || a.attributename || '=''NEWGROUP''' ||
       ' where ' || a.attributename || '=''MAINGRP1'';'
FROM maxattribute a
JOIN maxobject o ON o.objectname=a.objectname
WHERE a.persistent=1 AND o.isview=0
  AND ((a.sameasobject='MAXGROUP' AND a.sameasattribute='GROUPNAME') OR
       (a.objectname='MAXGROUP' AND a.attributename='GROUPNAME'))
ORDER BY a.objectname, a.attributename;

The result of the select will be something similar to this.

update APPLICATIONAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update COLLECTIONAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update CTRLGROUP set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update GLAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update GROUPUSER set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update GRPREASSIGNAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update LABORAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update LIMITTOLERANCE set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update LOCAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update MAXGROUP set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update PMSCCATSEC set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update REPORTAPPAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update REPORTAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update SECURITYRESTRICT set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';
update SITEAUTH set GROUPNAME='NEWGROUP' where GROUPNAME='MAINGRP1';

Now run this set of update statements in a single transaction against your database and you are done.


Important notes

  1. It is better to execute the update statements after having stopped the Maximo application server.
  2. Store the update statements in a text file you can revert your changes back if something goes wrong.
  3. This is not a supported procedure so you must be very careful when using it. Test it in a dev/test environment before using it in production.
  4. Backup your database first.


2 comments:

  1. Your title is a little confusing. At first, I thought you were talking about changing a table or Attribute name after creation. Suggest changing title to Updating a Primary Key such as Assetnum or Location...

    ReplyDelete