MAS : Estimating MAS AppPoints Using current Maximo 7.6 DB2 – A Complete SQL-Based Approach

Migrating from IBM Maximo Asset Management 7.6 to IBM Maximo Application Suite (MAS) requires a clear understanding of AppPoints, user persona classification, and peak concurrency. Many organizations struggle with this during modernization because licensing depends not only on the number of Maximo users, but also on: Peak concurrent usage User security profiles Installed Industry Solutions / Add-ons Required personas (Admin, Practitioner, Viewer) A reasonable buffer to avoid under-licensing This article simplifies the entire approach by providing a ready-to-run SQL script for DB2, which analyzes your Maximo 7.6 environment and produces: ✔ Peak concurrent users ✔ User-type suggestion ✔ AppPoints estimate ✔ AppPoints with 25% buffer ✔ Impact of Industry/Add-on Solutions (Transportation, SP, HSE, O&G, BIM, etc.) 💡 Why You Should Calculate AppPoints From Actual Maximo Usage IBM recommends sizing MAS AppPoints based on real historical data, not just user list counts. Relying on license seats or security groups often leads to: Overestimation → unnecessary licensing cost Underestimation → compliance risk during audits Incorrect persona mapping → Admins vs Practitioners vs Viewers not understood This SQL solves these problems by: Measuring actual concurrency using LOGINTRACKING Understanding what applications users really access Mapping them automatically into user personas Applying dynamic add‑on multipliers based on installed Industry Solutions 📌 Full DB2 SQL – MAS AppPoints Estimator for Maximo 7.6 Copy and paste directly into DB2 or Maximo Report/DB tool. -- ============================================ -- Maximo 7.6 (DB2) → MAS AppPoints Estimator -- ============================================ WITH CONFIG_PERIOD AS ( SELECT CURRENT TIMESTAMP - 90 DAYS AS START_TS, CURRENT TIMESTAMP AS END_TS FROM SYSIBM.SYSDUMMY1 ), CONFIG_USER_POINTS AS ( SELECT 'ADMIN' AS USER_TYPE, 10 AS POINTS_PER_USER FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 'PRACTITIONER', 5 UNION ALL SELECT 'VIEWER', 1 ), TXN_APPS AS ( SELECT 'WOTRACK' AS APP FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 'ASSET' UNION ALL SELECT 'INVENTOR' UNION ALL SELECT 'INVUSE' UNION ALL SELECT 'PO' UNION ALL SELECT 'PR' UNION ALL SELECT 'MR' UNION ALL SELECT 'LABREP' UNION ALL SELECT 'SR' ), HOURS_SERIES (TS) AS ( SELECT START_TS FROM CONFIG_PERIOD UNION ALL SELECT TS + 1 HOUR FROM HOURS_SERIES, CONFIG_PERIOD WHERE TS + 1 HOUR <= (SELECT END_TS FROM CONFIG_PERIOD) ), SESSIONS AS ( SELECT LT.USERID, LT.LOGINTIME AS LOGIN_TS, COALESCE(LT.LOGOUTTIME, CURRENT TIMESTAMP) AS LOGOUT_TS FROM LOGINTRACKING LT JOIN CONFIG_PERIOD P ON LT.LOGINTIME BETWEEN P.START_TS AND P.END_TS WHERE LT.ATTEMPTRESULT = 'SUCCESS' ), CONC_BY_HOUR AS ( SELECT H.TS AS HOUR_TS, COUNT(DISTINCT S.USERID) AS CONCURRENT_USERS FROM HOURS_SERIES H JOIN SESSIONS S ON S.LOGIN_TS <= H.TS + 1 HOUR AND S.LOGOUT_TS > H.TS GROUP BY H.TS ), PEAK_CONC AS ( SELECT COALESCE(MAX(CONCURRENT_USERS), 0) AS PEAK_CONCURRENT FROM CONC_BY_HOUR ), ACTIVE_USERS AS ( SELECT DISTINCT MU.USERID FROM MAXUSER MU WHERE MU.STATUS = 'ACTIVE' AND COALESCE(MU.LOGIN, 1) = 1 AND MU.USERID NOT IN ('MAXADMIN','MXINTADM','MXINTSYS','SPDEMO','WILSON') ), USER_GROUPS AS ( SELECT DISTINCT AU.USERID, GU.GROUPNAME FROM ACTIVE_USERS AU JOIN GROUPUSER GU ON AU.USERID = GU.USERID ), ADMINS AS ( SELECT DISTINCT USERID FROM USER_GROUPS WHERE UPPER(GROUPNAME) = 'MAXADMIN' ), GRP_HAS_TXN AS ( SELECT DISTINCT AA.GROUPNAME FROM APPLICATIONAUTH AA WHERE AA.APP IN (SELECT APP FROM TXN_APPS) ), PRACT_USERS AS ( SELECT DISTINCT UG.USERID FROM USER_GROUPS UG WHERE UG.GROUPNAME IN (SELECT GROUPNAME FROM GRP_HAS_TXN) ), USER_ROLES AS ( SELECT U.USERID, CASE WHEN EXISTS (SELECT 1 FROM ADMINS A WHERE A.USERID = U.USERID) THEN 'ADMIN' WHEN EXISTS (SELECT 1 FROM PRACT_USERS P WHERE P.USERID = U.USERID) THEN 'PRACTITIONER' ELSE 'VIEWER' END AS USER_TYPE FROM ACTIVE_USERS U ), TYPE_TOTALS AS ( SELECT USER_TYPE, COUNT(*) AS TOTAL_USERS FROM USER_ROLES GROUP BY USER_TYPE ), ALLOC_CONC AS ( SELECT T.USER_TYPE, T.TOTAL_USERS, (SELECT PEAK_CONCURRENT FROM PEAK_CONC) AS PEAK_CONCURRENT, DECIMAL( (T.TOTAL_USERS * 1.0) / NULLIF(SUM(T.TOTAL_USERS) OVER (), 0) * (SELECT PEAK_CONCURRENT FROM PEAK_CONC), 18, 4 ) AS CONCURRENT_EST FROM TYPE_TOTALS T ), ALLOC_CONC_R AS ( SELECT USER_TYPE, TOTAL_USERS, INTEGER(CEIL(CONCURRENT_EST)) AS CONCURRENT_USERS FROM ALLOC_CONC ), ADDON_MULTS AS ( SELECT 'MANAGE_CORE' AS ADDON, 1.00 AS MULTIPLIER FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 'SERVICE_PROVIDER', 1.15 WHERE EXISTS (SELECT 1 FROM MAXAPPS WHERE APP LIKE 'SP%') UNION ALL SELECT 'TRANSPORTATION', 1.10 WHERE EXISTS (SELECT 1 FROM MAXAPPS WHERE APP LIKE 'TR%') UNION ALL SELECT 'OIL_GAS', 1.10 WHERE EXISTS (SELECT 1 FROM MAXAPPS WHERE APP LIKE 'OIL%') UNION ALL SELECT 'HSE', 1.10 WHERE EXISTS (SELECT 1 FROM MAXAPPS WHERE APP LIKE 'HSE%') UNION ALL SELECT 'BIM', 1.05 WHERE EXISTS (SELECT 1 FROM MAXAPPS WHERE APP LIKE 'BIM%') ), PRODUCT_MULT AS ( SELECT MAX(MULTIPLIER) AS MULTIPLIER FROM ADDON_MULTS ), POINTS_BASE AS ( SELECT A.USER_TYPE, A.TOTAL_USERS, A.CONCURRENT_USERS, CUP.POINTS_PER_USER, (A.CONCURRENT_USERS * CUP.POINTS_PER_USER) AS BASE_POINTS FROM ALLOC_CONC_R A JOIN CONFIG_USER_POINTS CUP ON CUP.USER_TYPE = A.USER_TYPE ), POINTS_FINAL AS ( SELECT PB.USER_TYPE, PB.TOTAL_USERS, PB.CONCURRENT_USERS, INTEGER(CEIL(PB.BASE_POINTS * (SELECT MULTIPLIER FROM PRODUCT_MULT))) AS APP_POINTS, INTEGER(CEIL(PB.BASE_POINTS * (SELECT MULTIPLIER FROM PRODUCT_MULT) * 1.25)) AS APP_POINTS_WITH_25PC_BUFFER FROM POINTS_BASE PB ) SELECT PB.USER_TYPE, PB.TOTAL_USERS, PB.CONCURRENT_USERS, PB.APP_POINTS, PB.APP_POINTS_WITH_25PC_BUFFER FROM POINTS_FINAL PB UNION ALL SELECT 'TOTAL' AS USER_TYPE, SUM(PB.TOTAL_USERS), SUM(PB.CONCURRENT_USERS), SUM(PB.APP_POINTS), SUM(PB.APP_POINTS_WITH_25PC_BUFFER) FROM POINTS_FINAL PB WITH UR; 🔍 What This SQL Tells You 1. Peak Concurrent Users (PCU)Based on real login sessions from LOGINTRACKING. 2. Automatic Persona Mapping Admin → MAXADMIN or equivalent Practitioner → users with access to Work, Asset, Inventory, Purchasing Viewer → everyone else 3. Add-on/Industry Solution Detection The SQL automatically checks for: Service Provider Transportation Oil & Gas HSE BIM …and applies safe multipliers. 4. AppPoints Estimation With 25% Buffer Output includes: User TypeTotal UsersConcurrent UsersAppPointsAppPoints + 25% …plus a TOTAL row. 🚀 How to Use This as Part of MAS Upgrade Planning Step 1 — Run SQL in Maximo 7.6 Use DBViewer, DBeaver, or even BIRT query viewer. Step 2 — Review Persona Distribution Identify if too many users have practitioner-level access. Step 3 — Optimize Groups Before Migrating This is where MAS savings usually come from. Step 4 — Validate Peak Concurrency Confirm PCU with business teams. Step 5 — Size MAS AppPoints Correctly Use IBM’s MAS licensing matrix with SQL output. This SQL method gives organizations a data-driven, defendable, audit-ready way to size MAS AppPoints. It avoids guesswork, provides clarity to procurement teams, and supports architecture decisions during MAS migration.