🔧 SQL Server Now Using Sequences in MAS

💡 Use Case: Data Migration Headaches

Imagine you’ve just completed a large data migration into Maximo Application Suite 8 on SQL Server. You carefully updated all the right tables, including MAXSEQUENCE, just like you’ve always done. Everything looks good—until you start inserting new records. Suddenly, you’re hit with a wave of 2601 duplicate key errors. What happened?

The answer: MAS 8 is no longer using MAXSEQUENCE for SQL Server. Instead, it has moved to native SQL Server sequences. If you don’t update your processes, you’ll run into conflicts and broken inserts.

This post explains what changed, why it matters, and how to adapt your workflows to avoid these pitfalls.

📌 The Role of MAXSEQUENCE in Earlier Maximo Versions

In traditional Maximo installations, every time a new record needed a unique key (like a work order number or ticket ID), Maximo would reference the MAXSEQUENCE table. This table stored the current next value for each object requiring sequential numbering.

While functional, this approach had some limitations:

• ⚠️ Concurrency bottlenecks: Frequent access to a single table for updates could cause performance issues under heavy load.
• 🔒 Database locks: On SQL Server, row-level locking sometimes escalated to page or table locks, slowing down the application.
• 📈 Scalability issues: As environments grew, centralized sequencing became a performance risk.

🚀 What’s Changing in MAS 8 for SQL Server

In MAS 8, when configured for SQL Server, the platform now utilizes native SQL Server sequences instead of the MAXSEQUENCE table.

A sequence in SQL Server is an independent database object designed specifically to generate unique numeric values. It is fast, scalable, and avoids the overhead of a shared table.

✅ Key Benefits

• Improved performance in concurrent environments
• Reduced locking issues and better transaction handling
• Modernized architecture aligned with enterprise standards

⚙️ How It Works: Sequences in SQL Server

Instead of updating the MAXSEQUENCE table, you now advance the sequence directly.

Example: Insert Statement

INSERT INTO companies (COMPANY, NAME, COMPANYID)
SELECT 'ACME', 'ACME CORPORATION', NEXT VALUE FOR COMPANIESSEQ;

The key is NEXT VALUE FOR COMPANIESSEQ, which reads the current value and advances the sequence automatically.

❓ What If You Already Loaded Data?

If you already loaded data while still relying on the old MAXSEQUENCE table, you may encounter 2601 errors (duplicate key issues).

Steps to Fix It

1. Assess the damage – determine how many records are affected.
2. Reset the sequence using an ALTER SEQUENCE statement:

ALTER SEQUENCE dbo.INSPFIELDRESULTSELECTIONSEQ
RESTART WITH 1050;

This resets the starting point so that new inserts do not conflict with existing data.

🔍 How to View Sequences in SQL Server

Want to see the new sequences in your MAS 8 SQL Server database? Run:

SELECT * FROM sys.sequences;

This query lists all defined sequences and allows you to monitor their current values.

✅ Wrap-Up

The move from MAXSEQUENCE to SQL Server sequences eliminates the need for row updates in a centralized table, reducing contention and improving scalability. While it requires some adjustments for administrators and developers, it aligns Maximo with modern database practices and ensures better performance in high-demand environments.

About

A3J Group is a leading solutions and consulting firm based in Tampa, Florida, specializing in Enterprise Asset Management (EAM) with a focus on fully developed Computerized Maintenance Management System (CMMS) solutions like IBM Maximo.

In addition to our suite of proprietary products—including MxMobile, our powerful mobile solution for Maximo—we provide traditional consulting services that help organizations plan, implement, optimize, and support their EAM strategies. Whether clients need hands-on configuration, strategic guidance, or ongoing system support, our team delivers the expertise to drive long-term success.

With over 50 years of combined experience, our specialized team is dedicated to our core mission: to serve as excellent stewards of our clients’ trust and investments, while driving measurable results through tailored, high-impact solutions and intelligent consulting.

Visit us at www.a3jgroup.com. Or drop us a line at info@a3jgroup.com.