2 Replies Latest reply on Dec 19, 2013 3:21 PM by Andriy Rokhmanov

    CommonTableExpression Support for zOS DB2

    Andriy Rokhmanov Newbie

      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.

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