MAS DB2 wont support Oracle Supported SQL - DB2_COMPATIBILITY_VECTOR registry to ORA

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