Add a Maximo sequence to an existing table

Some Maximo tables do not generate a unique value when you add a record through the MBO layer. This legacy example associates a Maximo sequence with MXIN_INTER_TRANS.TRANSID, allowing a server-side customization to receive a transaction ID when it creates the queue record.

This is a specialist customization, not the standard interface-table contract. IBM requires the system writing inbound interface tables to create and maintain its own sequential TRANSID counter. Use that documented design when an external process writes directly to the database.

Before making this change: test it on a disposable copy of the environment, take a database backup and export the affected Maximo metadata. Direct changes to MAXSEQUENCE, MAXTABLE and MAXTABLECFG can affect key generation. Stop competing writers while the metadata is changed and have a tested rollback plan.

The original use case

The customization polled an external web service, then used an automation script to write an INTTEST interface-table record and its corresponding MXIN_INTER_TRANS queue record. The relevant part looked like this:

#Get MXServer
mxServer = MXServer.getMXServer()
 
#Get the MBOSet for the Integraton table queue object
mxinSet = mxServer.getMboSet("MXIN_INTER_TRANS", mxServer.getSystemUserInfo())
 
#Create a new MXIN_INTER_TRANS record, this is where the new sequence will generate a unique Transaction ID
mxinRecord = mxinSet.add()
 
#Set the external system & enterprise service name in the queue table
mxinRecord.setValue("EXTSYSNAME", "EXTSYS1")
mxinRecord.setValue("IFACENAME", "TESTIFACE")
 
#Get MBOSet of the integration table
intTableSet = mxServer.getMboSet("INTTEST", mxServer.getSystemUserInfo())
 
#Create a new integration table record
intTableRecord = intTableSet.add()
 
#Set the Integration Transaction ID, generated for the MXIN_INTER_TRANS record
intTableRecord.setValue("TRANSID", mxinRecord.getInt("TRANSID"))
 
#Set details on integration table
intTableRecord.setValue("COL1", "VALUE1")
 
#Save the integration tables
mxinSet.save()
intTableSet.save()

The excerpt assumes MXServer has already been imported. Replace EXTSYS1, TESTIFACE and INTTEST with valid configuration from your environment.

Do not copy the two independent save() calls into production code without transaction handling. IBM's inbound interface-table procedure requires the interface record and its queue record to be committed as one database transaction. Otherwise one save can succeed while the other fails, leaving an orphaned record. The system-user context also bypasses normal user access, so restrict and audit the script accordingly.

Establish a safe starting value

This example uses 0 for MAXRESERVED and creates a database sequence with its default starting value. That is safe only for a new, empty queue with no competing counter.

Before adding anything, check for an existing Maximo sequence row, database sequence and queue data. Choose a starting value greater than every existing or externally reserved TRANSID. If another process already maintains inbound IDs, do not introduce this second generator.

The examples below retain the original empty-table value of 0. Replace it with the agreed current high-water mark when data already exists.

Register the sequence in MAXSEQUENCE

Create one MAXSEQUENCE row for MXIN_INTER_TRANS.TRANSID. Run only the statement for the target database.

DB2

--Create the sequence record in the MAXSEQUENCE table.
INSERT INTO
  MAXSEQUENCE (
    TBNAME,
    NAME,
    MAXRESERVED,
    SEQUENCENAME,
    MAXSEQUENCEID
  )
VALUES
  (
    'MXIN_INTER_TRANS',
    'TRANSID',
    0,
    'MXINTERTRANSSEQ',
    NEXT VALUE FOR MAXSEQUENCESEQ
  );

Oracle

--Create the sequence record in the MAXSEQUENCE table.
INSERT INTO
  MAXSEQUENCE (
    TBNAME,
    NAME,
    MAXRESERVED,
    SEQUENCENAME,
    MAXSEQUENCEID
  )
VALUES
  (
    'MXIN_INTER_TRANS',
    'TRANSID',
    0,
    'MXINTERTRANSSEQ',
    MAXSEQUENCESEQ.NEXTVAL
  );

SQL Server

--Create the sequence record in the MAXSEQUENCE table.
INSERT INTO
  MAXSEQUENCE (
    TBNAME,
    NAME,
    MAXRESERVED,
    SEQUENCENAME,
    MAXSEQUENCEID
  )
VALUES
  (
    'MXIN_INTER_TRANS',
    'TRANSID',
    0,
    'MXINTERTRANSSEQ',
    (SELECT MAX(MAXSEQUENCEID) + 1 FROM MAXSEQUENCE)
  );

Run the SQL Server insert in a controlled maintenance window. Calculating an identifier with MAX(...) + 1 is unsafe if another process can insert a MAXSEQUENCE row concurrently.

Mark TRANSID as the unique column

The Maximo data dictionary must identify the attribute that receives the generated value:

--Update the MXIN_INTER_TRANS table so it has a unique attribute in MAXTABLECFG.
UPDATE
  MAXTABLECFG
SET
  UNIQUECOLUMNNAME = 'TRANSID'
WHERE
  TABLENAME = 'MXIN_INTER_TRANS';
 
--Update the MXIN_INTER_TRANS table so it has a unique attribute in MAXTABLE.
UPDATE
  MAXTABLE
SET
  UNIQUECOLUMNNAME = 'TRANSID'
WHERE
  TABLENAME = 'MXIN_INTER_TRANS';

Verify that each statement updates exactly one intended row. If the environment already identifies a different unique column, stop and investigate rather than overwriting it.

Create the database sequence for DB2 or Oracle

Traditional Maximo uses its internal number-generation mechanism for this SQL Server pattern. On DB2 and Oracle, create the database sequence referenced by SEQUENCENAME:

--Create the sequence.
CREATE SEQUENCE MXINTERTRANSSEQ;

For a queue that already contains data, add the database-specific START WITH value chosen during the high-water-mark check. Sequence syntax and ownership differ between DB2 and Oracle, so have the database administrator review the final DDL rather than running the bare example unchanged.

Restart and validate

Commit the approved metadata change and restart Maximo so its metadata and sequence caches are rebuilt. In a test environment:

Add one MXIN_INTER_TRANS record through the MBO layer and confirm that TRANSID is populated.
Add the corresponding interface-table rows with the same TRANSID in the same transaction.
Confirm that IFACETABLECONSUMER processes the record once and in sequence.
Check for duplicate IDs, orphaned queue rows and errors in the application log.

IBM's TRANSID reference explains why the value must remain unique and sequential. Anything that writes directly to MXIN_INTER_TRANS still has to use the same coordinated counter; an MBO default does not protect independent SQL writers from collisions.

Settings

Keyboard shortcuts