Fix SQL Server sequences after loading Maximo data

Before you begin: this procedure generates statements that update Maximo's internal sequence data. Take a database backup, follow your organisation's change process and test the generated SQL in a non-production environment. Review every statement before running it.

Maximo installations on SQL Server use the MAXSEQUENCE table to allocate numeric primary keys such as WORKORDERID and LOCATIONSID. This approach dates from versions of SQL Server before database sequences were available and remains part of Maximo's SQL Server implementation.

When records are created through Maximo, the application maintains the corresponding values in MAXSEQUENCE. A direct data load can insert higher IDs without updating that table. If Maximo later allocates an ID that the target table already contains, the next insert can fail with a duplicate-key error.

How MAXSEQUENCE identifies a sequence

Each relevant row in MAXSEQUENCE contains:

• TBNAME: the table that owns the primary key.
• NAME: the primary-key column whose maximum value must be checked.
• SEQUENCENAME: Maximo's name for the sequence.
• MAXRESERVED: the value Maximo uses when reserving IDs for that sequence.

The data looks similar to this:

These values are examples

Generate the updates

Run the following query first. It reads the table and column names from MAXSEQUENCE and returns an UPDATE statement for each non-audit table.

SELECT DISTINCT
  'UPDATE MAXSEQUENCE SET MAXRESERVED = (SELECT MAX(' + NAME + ') + 10 FROM ' + TBNAME + ') WHERE TBNAME = ''' + TBNAME + ''';'
FROM
  MAXSEQUENCE
WHERE NOT EXISTS (
  SELECT
    NULL
  FROM
    MAXTABLE
  WHERE
    MAXTABLE.TABLENAME = MAXSEQUENCE.TBNAME
    AND MAXTABLE.ISAUDITTABLE = 1
);

This query produces statements similar to the following extract:

UPDATE
  MAXSEQUENCE
SET
  MAXRESERVED = (
    SELECT
      MAX(WORKORDERID) + 10
    FROM
      WORKORDER
  )
WHERE
  TBNAME = 'WORKORDER';
 
UPDATE
  MAXSEQUENCE
SET
  MAXRESERVED = (
    SELECT
      MAX(WORKORDERSPECID) + 10
    FROM
      WORKORDERSPEC
  )
WHERE
  TBNAME = 'WORKORDERSPEC';
 
UPDATE
  MAXSEQUENCE
SET
  MAXRESERVED = (
    SELECT
      MAX(WORKPERIODID) + 10
    FROM
      WORKPERIOD
  )
WHERE
  TBNAME = 'WORKPERIOD';
 
UPDATE
  MAXSEQUENCE
SET
  MAXRESERVED = (
    SELECT
      MAX(WORKPRIORITYID) + 10
    FROM
      WORKPRIORITY
  )
WHERE
  TBNAME = 'WORKPRIORITY';
 
UPDATE
  MAXSEQUENCE
SET
  MAXRESERVED = (
    SELECT
      MAX(WORKSCAPELAYOUTID) + 10
    FROM
      WORKSCAPELAYOUT
  )
WHERE
  TBNAME = 'WORKSCAPELAYOUT';

Review and apply the script

The first query only generates text; it does not repair MAXSEQUENCE itself. Save its results as a script, inspect every generated statement and then run the approved statements against the database.

Pay particular attention to empty target tables. MAX(...) returns NULL when a table has no rows, so a generated statement for an empty table needs separate review rather than being run unchanged.

Commit the transaction if your database client does not do so automatically. Restart every Maximo application server after updating MAXSEQUENCE, then verify that Maximo can create records in the affected objects without reusing an existing ID.