We have problem when running the following sample query against DB2 on zOS (mainframe). The cause is the LIMIT clause in CommonTableExpression (inside WITH..), when LIMIT removed - everything works fine.
WITH TABLE_B AS (
ORDER BY SOMEFIELD
ORDER BY SOMEFIELD
The query works fine only on the recent versions of DB2 (V9, V10 and V11), but there are several apps which still run on pre-9 versions of DB2, or run newer DB2 in CM (Compatibility Mode).
Note that Teiid DB2 Translator converts "LIMIT.." into "FETCH FIRST..." expression, this is expected and correct, but still not supported by older DB2 inside CTE.
Is it possible to disable supportsCommonTableExpression() in DB2ExecutionFactory based on some specific version of DB2? Something like already implemented in DB2ExecutionFactory.supportsElementaryOlapOperations()?
Below is additional information about how to identify the DB2 version and execution mode.
We can identify the DB2 version and mode through another DB2 query:
SELECT GETVARIABLE('SYSIBM.VERSION') as VERSION
The result from this query on D91B is:
SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,1,3) AS PRODUCT,
SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,4,2) AS VERSION,
SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,6,2) AS RELEASE,
SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,8,1) AS MODE
PRODUCT VERSION RELEASE MODE
DSN 09 01 0
Contains a string that represents the version of DB2. This string has
form pppvvrrm where:
ppp is a product string that is set to the value 'DSN'
vv is a two-digit version identifier such as '09'
rr is a two-digit release identifier such as '01'
m is a one-digit modification level.
Values 0 and 1 are reserved for modification levels in
and enabling-new-function mode from Version 8 (CM8,
ENFM8, and ENFM8*)
Values 2 and 3 are reserved for modification levels in
and enabling-new-function mode from Version 9 (CM9,
ENFM9, and ENFM9*)
Value 4 is reserved
Values 5, 6, 7, 8, and 9 are for modification levels in