MAS DB2 won't support Oracle Compatible SQL commands due to security concerns.
Database parameter DB2_COMPATIBILITY_VECTOR registry won't be set to ORA by the Support team, which was supported in SaaS Flex Maximo 7.6 version.
As part of Upgrade, application development team need to re-write queries in Maximo BIRT reports and relationships.
Following are some of the queries that can be referred for conversion:
- rownum vs fetch first n rows only
SaaS Flex DB2: siteid=:siteid and ponum = :ponum and rownum=1
MAS DB2: siteid=:siteid and ponum = :ponum FETCH FIRST 1 ROWS ONLY
- CONNECT BY PRIOR vs WITH Clause
Classification Hierarchy Path generation from Maximo. Both the queries work in Out of Box Maximo
SaaS DB2 with Oracle Compatibility
SELECT
level, classstructureid, parent, classificationid,
sys_connect_by_path(classificationid, '**') AS path
FROM maximo.classstructure
START WITH parent IS NULL
CONNECT BY
PRIOR classstructureid = parent
ORDER BY
level, parent, classstructureid
MAS DB2 recursive sql using WITH clause
WITH classhier ( level, classstructureid, parent, classificationid, path )
AS (
SELECT
1, classstructureid, parent, classificationid,
'' || classificationid
FROM maximo.classstructure
WHERE parent IS NULL
UNION ALL
SELECT
level + 1, c1.classstructureid, c1.parent, c1.classificationid,
classhier.path || '\' || c1.classificationid
FROM maximo.classstructure c1, classhier
WHERE classhier.classstructureid = c1.parent AND c1.parent IS NOT NULL
)
SELECT * FROM classhier
Reference: db2-compatibility-vector-registry-variable
