Maximo Mobile - SQL error 206 during Mobile sync

Troubleshooting

Problem

Users of Maximo Mobile experience SQL error 206 during data synchronization. The error is associated with the object structure MXAPIWODETAIL and a specific query involving the ASSIGNEDWOLIST. The query executes successfully when run directly in the database but fails during mobile data synchronization. The error 'BMXAA6713E - The MBO fetch operation failed in the mboset with the SQL error code -206' is logged, indicating a database retrieval issue.

Symptom

The user experiences SQL error 206 during data synchronization in Maximo Mobile. The error is related to the object structure MXAPIWODETAIL and the query ASSIGNEDWOLIST. The query involves filtering work orders based on the user's default site and status. The same query works when executed directly in the database and as a where clause in WOTRACK, but fails during mobile data synchronization.

Cause

The issue is caused by a technical fault due to a database compatibility problem. The query uses 'ROWNUM', which is specific to Oracle and not supported in DB2, leading to SQL error 206 during data synchronization in Maximo Mobile.

Environment

MAS Manage 9.0 and 9.1 with a DB2 database.

Diagnosing The Problem

Inspect the SQL query used in Maximo Mobile for data synchronization.
Identify the object structure MXAPIWODETAIL and query ASSIGNEDWOLIST.
Analyze the query for potential issues, focusing on subqueries and joins.
Test the query directly in the database to verify its validity.
Review relationships (ASSETSRECENTWOS and LOCATIONSRECENTWOS) using 'rownum <= 3'.
Modify these relationships to use 'FETCH FIRST 3 ROWS ONLY' instead.
Retest data synchronization in Maximo Mobile after applying the modified queries.

Resolving The Problem

The issue was related to SQL error 206 during data synchronization in Maximo Mobile. The error was linked to the object structure MXAPIWODETAIL and the query ASSIGNEDWOLIST. The resolution involved modifying two relationships (ASSETSRECENTWOS and LOCATIONSRECENTWOS) to use 'FETCH FIRST 3 ROWS ONLY' instead of 'rownum <= 3'. The modified queries are as follows:

1. ASSETSRECENTWOS:

workorderid IN (SELECT workorderid FROM workorder WHERE assetnum = :assetnum AND siteid = :siteid AND istask = 0 AND status IN (SELECT value FROM synonymdomain WHERE domainid = 'WOSTATUS' AND maxvalue IN ('COMP', 'CLOSE')) ORDER BY statusdate DESC FETCH FIRST 3 ROWS ONLY)

2. LOCATIONSRECENTWOS:

workorderid IN (SELECT workorderid FROM workorder WHERE location = :location AND siteid = :siteid AND istask = 0 AND status IN (SELECT value FROM synonymdomain WHERE domainid = 'WOSTATUS' AND maxvalue IN ('COMP', 'CLOSE')) ORDER BY statusdate DESC FETCH FIRST 3 ROWS ONLY)
These changes resolved the SQL error during data synchronization.

Document Location

Worldwide

[{"Line of Business":{"code":"LOB77","label":"Automation Platform"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSRHPA","label":"IBM Maximo Application Suite"},"ARM Category":[{"code":"a8m3p000000hAgaAAE","label":"Maximo Application Suite-\u003EMAS Applications-\u003EMobile"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0.0;8.0.1;8.1.0;8.10.0;8.11.0;8.2.0;8.3.0;8.4.0;8.5.0;8.6.0;8.7.0;8.8.0;8.9.0;9.0.0;9.1.0;9.2.0","Type":"MASTER"}]

Product Synonym

Maximo Application Suite as a Service;Maximo Application Suite Managed Service;Maximo Application Suite Dedicated;Maximo Vegetation Management

Tips