CommonTableExpression Support for zOS DB2

[rokhmanov]

Hi,

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 (

SELECT SOMEFIELD 

FROM SOME.TEST 

ORDER BY SOMEFIELD

LIMIT 20

)

SELECT TABLE_B.SOMEFIELD

FROM TABLE_B

ORDER BY SOMEFIELD

LIMIT 10

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.

Thanks,

Andriy

==============================

We can identify the DB2 version and mode through another DB2 query:

SELECT GETVARIABLE('SYSIBM.VERSION') as VERSION
FROM SYSIBM.SYSDUMMY1

The result from this query on D91B is:

DSN09010

Parsed:

SELECT

  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   

FROM SYSIBM.SYSDUMMY1;

Result:

PRODUCT VERSION RELEASE MODE

DSN 09      01      0

Details:

1. SYSIBM.VERSION
   Contains a string that represents the version of DB2. This string has
   the
   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
   compatibility
   and enabling-new-function mode from Version 8 (CM8,
   CM8*,
   ENFM8, and ENFM8*)
   Values 2 and 3 are reserved for modification levels in
   compatibility
   and enabling-new-function mode from Version 9 (CM9,
   CM9*,
   ENFM9, and ENFM9*)
   Value 4 is reserved
   Values 5, 6, 7, 8, and 9 are for modification levels in
   new-function
   mode.

==============================

[shawkins]

Yes, this is possible, go ahead and log something.  You can temporarily workaround with a custom translator.  As for the version logic we're currently just basing the automatic detection based upon the version reported by the DatabaseMetaData.  Seems like this will get a little complicated with considering compatibility mode, so we may just want to fallback on having the user manually set the version on a translator override and use a simple check for CTE support.

[rokhmanov]

Thanks Steven, I opened TEIID-2779. I was not able to reply earlier, for some reason my reply was not accepted by Community application.