IBM Support

Maximo Upgrade Tips: BMXAA7450E - Duplicates found in Phone table

Technical Blog Post


Abstract

Maximo Upgrade Tips: BMXAA7450E - Duplicates found in Phone table

Body

Hello!
 
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.
 
Jean Carlos
IBM Maximo Test Software Engineer (QA)
 
 

 

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

UID

ibm11132809

Page Feedback