Maximo Open Forum

• The Failure Mode: WONUM is not a unique value in Maximo. It duplicates across different sites (siteid), organizations (orgid), and historical records.
• The Performance Impact: If your optimized mobile query maps attachments via a clause like ownertable='WORKORDER' and ownerid=:workorderid, the reality is OOB, MXAPIWODETAIL has a significant number of relationships making it difficult to pinpoint the root cause of the 'select *' root cause. The source might be an Object Structure Relation, OSLC Query, or an Application's saved query to name a few.
• The Fix: Refine the combinations and permutations driving the where clauses in addition to the change your team made,(i.e., ownertable='WORKORDER' and ownerid=:workorderid). Consider altering the MobileDB Crontask instance's where clause parameter if possible. 

--=================================================================================================================================================== 
-- COHESIVE SYSTEM MONITORING MATRIX: PARENT VS CHILD ATTACHMENT DIFFERENTIATION (SQL SERVER) 
--=================================================================================================================================================== 
SELECT 
     @@SERVERNAME AS [Maximo_Environment]
    ,p.siteid AS [Site_ID]
    ,p.worktype AS [Work_Type]
    ,wt.wtypedesc AS [Work_Type_Description]
    ,COUNT(p.workorderid) AS [Total_Qualifying_WOs]
    
    -- 1. Differentiate Base Table Records by Core Class 
    ,SUM(CASE WHEN p.woclass = 'WORKORDER' AND p.istask = 0 THEN 1 ELSE 0 END) AS [Parent_WOs]
    ,SUM(CASE WHEN p.woclass = 'ACTIVITY' OR p.istask = 1 THEN 1 ELSE 0 END) AS [Child_Activities_and_Tasks]
    
    -- 2. Cleanly Count Parent vs Child Attachments via Unique ID Subquery 
    ,ISNULL(SUM(CASE WHEN p.woclass = 'WORKORDER' AND p.istask = 0 THEN att.Attachment_Count ELSE 0 END), 0) AS [Parent_WO_Attachments]
    ,ISNULL(SUM(CASE WHEN p.woclass = 'ACTIVITY' OR p.istask = 1 THEN att.Attachment_Count ELSE 0 END), 0) AS [Child_Activity_Attachments]
    ,ISNULL(SUM(att.Attachment_Count), 0) AS [Combined_Total_Attachments]
    
    -- 3. Operational Queue Metrics 
    ,SUM(CASE WHEN p.wopriority <= 2 AND p.wopriority > 0 THEN 1 ELSE 0 END) AS [High_Priority_WOs]
    ,SUM(CASE WHEN p.wopriority > 2 AND p.wopriority <= 5 THEN 1 ELSE 0 END) AS [Medium_Priority_WOs]
    ,SUM(CASE WHEN p.wopriority > 5 OR p.wopriority = 0 THEN 1 ELSE 0 END) AS [Low_or_Unassigned_Priority]
    ,SUM(CASE WHEN p.amcrew IS NOT NULL OR p.crewworkgroup IS NOT NULL THEN 1 ELSE 0 END) AS [Crew_Assigned_WOs]
    ,SUM(CASE WHEN p.parentchgsstatus = 1 THEN 1 ELSE 0 END) AS [WOs_With_Inherit_Status_Enabled]
    ,COUNT(DISTINCT p.reportedby) AS [Unique_Reporters_In_Dataset]
    
    -- 4. Active Queue Breakdown 
    ,SUM(CASE WHEN sd.maxvalue = 'APPR' THEN 1 ELSE 0 END) AS [Status_Approved]
    ,SUM(CASE WHEN sd.maxvalue = 'INPRG' THEN 1 ELSE 0 END) AS [Status_In_Progress]
    ,SUM(CASE WHEN sd.maxvalue = 'WMATL' THEN 1 ELSE 0 END) AS [Status_Waiting_on_Material]
    ,SUM(CASE WHEN sd.maxvalue = 'COMP' THEN 1 ELSE 0 END) AS [Status_Completed]
    ,MIN(p.reportdate) AS [Oldest_Report_Date_In_Queue]
    ,MAX(p.reportdate) AS [Newest_Report_Date_In_Queue] 
FROM workorder p 
LEFT JOIN synonymdomain sd 
    ON sd.domainid = 'WOSTATUS' 
   AND sd.value = p.STATUS 
   AND sd.defaults = 1 
LEFT JOIN worktype wt 
    ON wt.worktype = p.worktype 
   AND wt.orgid = p.orgid 
   
-- THE STRATEGIC FIX: Left Join to an aggregated subquery targeting the unique physical index 
LEFT OUTER JOIN ( 
    SELECT 
        doc.ownerid AS target_workorderid, 
        COUNT(doc.doclinksid) AS Attachment_Count 
    FROM doclinks doc 
    INNER JOIN docinfo di 
        ON doc.docinfoid = di.docinfoid 
    WHERE doc.ownertable = 'WORKORDER' 
      AND doc.ownerid IS NOT NULL 
    GROUP BY doc.ownerid 
) att ON att.target_workorderid = p.workorderid 
WHERE p.historyflag = 0 
  AND sd.maxvalue IN ('APPR', 'WSCH', 'WMATL', 'WAPPR', 'INPRG', 'COMP') 
GROUP BY 
     p.siteid 
    ,p.worktype 
    ,wt.wtypedesc 
ORDER BY 
     p.siteid 
    ,[Total_Qualifying_WOs] DESC;

1. Extract the active, completed preloaded database identifier from your backend: In SQL Server you can correlate performance to query execution times by selecting all columns.

   SELECT mobiledbid, persongroup, siteid, changedate FROM mobiledb WHERE mobiledbid={YOUR_MOBILE_DB_ID}; 

2. Establish an active browser session cookie by logging into the Maximo desktop GUI as an administrator, then open a clean tab to stream the raw binary database payload directly to your desktop:

   http://{hostname}:{port}/maximo/oslc/graphite/mobile/db?mobileDbId={YOUR_MOBILE_DB_ID}
   

3. Open the downloaded file using DB Browser for SQLite and run this audit in the Execute SQL tab to pinpoint which tables are harboring the mega-byte bloat:

   SELECT name AS [Table Name], SUM(length(json)) / 1024.0 / 1024.0 AS [Size (MB)] 
   FROM sqlite_master CROSS JOIN json_each(name) WHERE type = 'table' AND name LIKE '%_lookup'
   GROUP BY name ORDER BY [Size (MB)] DESC;
   

1. Go to the Person Groups application in the Maximo UI and create a test group (e.g., YOUR_UNIQUE_MOBILE_TEST). Populate it with exactly one testing person/labor record.
2. In the Cron Task application, create a testing crontask instance in the MobileDbGeneration Cron Task.
3. Create a brand-new Cron Task Instance (e.g., YOUR_UNIQUE_MOBILE_INSTANCE).
4. Set the PersonGroup parameter for this test instance strictly to YOUR_MOBILE_PERSONGROUP and set a group default to receive runtime error notification emails.
5. Assign a tightly restricted, controlled WHERE clause parameter to this instance to fetch a test worklist.
6. Activate only this test instance. This allows you to generate, stress-test, and debug mobiledb causing your pod issue.

• mxe.db.fetchResultStopLimit 
• mxe.db.fetchStopLimitEnabled
• mobile.% prefix system properties