Welcome to the Asset Management Blog, where you can read the perspectives from Asset Management experts. This Blog provides technical insights into the Maximo product solutions.
I am going to provide a tip today in how to fix one of the common errors that are reported during the Validation run, in an upgrade scenario from the Maximo Asset Management (with or without IS/Addons) 6.x to 7.1.x level.
The Validation utility is executed after the Integrity Checker runs are completed without any error remaining and its main objective is to check that valid data in the source Maximo database (to-be-upgraded) that needs user intervention is transformed correctly before the ugprade can proceed. For example, you might need to provide a unique phone number for one or more persons whose phone number is not unique, because it was possible on the Maximo 6.x level, but is not anymore at the Maximo 7.1.x level. For more details check the Upgrade Guide in the IBM Maximo Upgrade Resources
support web page. For this case specifically, the Validation utility when executed will present this error message: "BMXAA0443E - ERROR -- BMXAA7450E - Duplicates found in Phone table on columns PersonID, PhoneNum, Type. These need to be unique." By running a few SQL queries you can check the values and change as needed depending of the customer's/user's willing. But if your intention is just to test the upgrade process you probably wants to move forwarded regardless the change done in the data. I mean, whatever phoneid for a personID, or some other kind of change would work for your purpose that is just testing the upgrade process. So, an easy way to do that is just using one of the scripts below, depending of your database manager:
For SQL Server:
Copy and paste everything below between ***** (except the **** lines) in a SQL Server Query editor, connected with the same user described in the mxe.db.user property of the maximo.properties file, located under <maximo_folder>\applications\maximo\properties, and run it. It will automatically delete the duplicates.
SET NOCOUNT ON GO -- used to obtain the database data DECLARE @vPersonId varchar(30), @vPhoneNum varchar(20), @vType varchar(10) DECLARE @vCount int DECLARE @vPhoneId int -- obtain duplicate records in phone table DECLARE c1 CURSOR FOR SELECT personid, phonenum, type, count(*) FROM phone GROUP BY personid, phonenum, type HAVING count(*) > 1 -- get the duplicate data OPEN c1 FETCH NEXT FROM c1 INTO @vPersonId, @vPhoneNum, @vType, @vCount -- for each personid/phonenum/type with duplicates -- delete the duplicates until remains just 1 line of the record WHILE (@vCount > 1 AND @@FETCH_STATUS = 0) BEGIN SET @vCount = @vCount - 1 IF (@vType is null) BEGIN -- used to select one duplicate record to be deleted when type is null DECLARE c3 CURSOR FOR SELECT phoneid FROM phone WHERE personid = @vPersonId AND phonenum = @vPhoneNum AND type is null OPEN c3 FETCH NEXT FROM c3 INTO @vPhoneId CLOSE c3 DEALLOCATE c3 END ELSE BEGIN -- used to select one duplicate record to be deleted DECLARE c2 CURSOR FOR SELECT phoneid FROM phone WHERE personid = @vPersonId AND phonenum = @vPhoneNum AND type = @vType OPEN c2 FETCH NEXT FROM c2 INTO @vPhoneId CLOSE c2 DEALLOCATE c2 END DELETE FROM phone WHERE phoneid = @vPhoneId FETCH NEXT FROM c1 INTO @vPersonId, @vPhoneNum, @vType, @vCount END DEALLOCATE c1 SET NOCOUNT OFF GO ****************************************************************************************************************************
For Oracle:
Copy and paste everything below between ***** (except the **** lines) in a file called fixphonedups.ora. Then, open an Oracle SQL Query editor, like SQL Plus, connected with the same user of the database described in the mxe.db.user property of the maximo.properties file, located under <maximo_folder>\applications\maximo\properties, and run this script as you'd run any other PL-SQL script. For example, if you put this file in your C:\ folder, you just run this in SQL Plus: @c:\fixphonedups.ora; <ENTER>
SET DEFINE OFF declare /* obtain duplicate records in phone table */ cursor c1 is select personid, phonenum, type, count(*) from phone group by personid, phonenum, type having count(*) > 1; /* used to obtain the database data */ vPersonId phone.personid%TYPE; vPhoneNum phone.phonenum%TYPE; vType phone.type%TYPE; vCount NUMBER := 0; vPhoneId phone.phoneid%TYPE; /* used to select one duplicate record to be deleted */ cursor c2 is select phoneid from phone where personid = vPersonId and phonenum = vPhoneNum and type = vType; /* used to select one duplicate record to be deleted when type is null */ cursor c3 is select phoneid from phone where personid = vPersonId and phonenum = vPhoneNum and type is null; begin /* get the duplicate data */ open c1; loop fetch c1 into vPersonId, vPhoneNum, vType, vCount; exit when c1%NOTFOUND; /* for each personid/phonenum/type with duplicates delete the duplicates until remains just 1 line of the record */ while vCount > 1 loop vCount := vCount - 1; if vType is null then open c3; fetch c3 into vPhoneId; close c3; else open c2; fetch c2 into vPhoneId; close c2; end if; delete from phone where phoneid = vPhoneId; commit; end loop; end loop; exception when OTHERS then RAISE_APPLICATION_ERROR(-20001, 'ERRO!!! '||SQLCODE||' '||SQLERRM); end; / ********************************************************************************************************** For DB2: By using a DB2 SQL Query editor, connected with the same user of the database described in the mxe.db.user property of the maximo.properties file, located under <maximo_folder>\applications\maximo\properties, run the query below: SELECT personid, phonenum, type, count(*) FROM phone GROUP BY personid, phonenum, type HAVING count(*) > 1
Get the results of the query above and replace each column with the similar named variable prefixed by @ in the query below and then run this query: SELECT phoneid FROM phone WHERE personid = @vPersonId AND phonenum = @vPhoneNum AND type = @vType Get the result of the query above and then replace the variable prefixed by @ in the query below and then run this query: DELETE FROM phone WHERE phoneid = @vPhoneId
If more than 1 phoneid was returned by the second query use the query below instead, with will have a list separated by commas to delete all the duplicates:
DELETE FROM phone WHERE phoneid in (@vPhoneID1, @vPhoneID2,...,@vPhoneIDn)
Whatever the script above used, what all they do is, first, to find the duplicates in the phone table, returning the personid, phonenum and type. Then with this information in hand, it is applied in a second query to find the phoneid's of the duplicates, and finally, with these phone id's, a third query deletes the record, letting one only, instead duplicates. After the SQL runs above, you just need to run the Validation utility again. This time it will not report that error anymore and if you already fixed the other possible errors, you can proceed with the Upgrade Process.
Keep in mind that this approach is just to go ahead with the Upgrade Process, without considering specific needs. The scripts are going to delete whatever duplicated value. If you are concerned with what record in the phone table will be deleted, make sure to use the instructions of the fixphonedups.db2 file, even running against an Oracle or SQL Server database, and after the second query, seleting the specific phone id's to be deleted by the third query.
