2 Replies Latest reply on Dec 15, 2014 3:01 PM by Steven Hawkins

    Teiid not pushing the Join SQL to the translator

    Rakesh Balguri Expert

      Hi,

       

      We are using Teiid 8.7 and looks like in one of the case Teiid is not pushing the joined SQL to the translator. Instead it is breaking the SQL into two and sending individual SQL's to the translator.

      Since the translator supports joins we expect Teiid to push the complete SQL to the translator.

      Our translator expects some columns in the where clause using which it uses to processes the SQL. Since after breaking of the SQL into two individual SQL's the required parameters are not passed as part of the second SQL.

      The following are the properties that are set in the translator's constructor:

       

      super.setSupportsOuterJoins(true);

      super.setSupportsFullOuterJoins(true);

      super.setSupportsInnerJoins(true);

      super.setSupportsOrderBy(true); 

      super.setSupportsSelectDistinct(true);

       

      The following is the SQL and attached is the Query Plan:

      BEGIN

      SELECT

      *

      FROM chorus_time.timetable

      WHERE period = 30000 WITHOUT RETURN; WITH TB AS

      (

         SELECT

         *

         FROM

         (

            SELECT

            TB.*

            FROM (SELECT * FROM DBMzDB2c.CAT_TABLES_Rbase_V111) AS TB

         )

         AS TB

         WHERE (TB.dsConf = 'QA8800')

         AND (TB.dsGroup = '')

         AND (TB.dsSystem = 'CA11')

         AND (TB.dsSSID = 'DH0G') LIMIT 12

      )

      SELECT

      CHORUS_B.dsSSID,

      CHORUS_B.NAME,

      CHORUS_B.dsGroup,

      CHORUS_B.CREATOR,

      CHORUS_B.dsSystem,

      CHORUS_B.dsConf,

      CHORUS_B.TSNAME,

      CHORUS_B.APPEND,

      CHORUS_B.CHILDREN,

      CHORUS_B.OWNER,

      CHORUS_B.CHECKS,

      CHORUS_B.DBID,

      CHORUS_B.CREATEDBY,

      CHORUS_B.EDPROC,

      CHORUS_B.VERSION,

      CHORUS_B.TBNAME,

      CHORUS_B.ARCHIVING_TABLE,

      CHORUS_B.OWNERTYPE,

      CHORUS_B.CLUSTERTYPE,

      CHORUS_B.NUM_DEP_MQTS,

      CHORUS_B.TYPE,

      CHORUS_B.RELCREATED,

      CHORUS_B.RECLENGTH,

      CHORUS_B.KEYCOLUMNS,

      CHORUS_B.SECURITY_LABEL,

      CHORUS_B.HASHKEYCOLUMNS,

      CHORUS_B.VERSIONING_SCHEMA,

      CHORUS_B.AUDITING,

      CHORUS_B.AVGROWLEN,

      CHORUS_B.CARD,

      NVL(CHORUS_J0.COUNTS, 0) AS "NotesCount@@@@COUNTS",

      CHORUS_B.COLCOUNT,

      CHORUS_B.OBID,

      CHORUS_B.NPAGESF,

      CHORUS_B.NPAGES,

      CHORUS_B.VALPROC,

      CHORUS_B.ENCODING_SCHEME,

      CHORUS_B.CHECKFLAG,

      CHORUS_B.STATS_FEEDBACK,

      CHORUS_B.PCTPAGES,

      CHORUS_B.TABLESTATUS,

      CHORUS_B.REMARKS,

      CHORUS_B.CONTROL,

      CHORUS_B.DATACAPTURE,

      CHORUS_B.IBMREQD,

      CHORUS_B.STATUS,

      CHORUS_B.PCTROWCOMP,

      CHORUS_B.SPLIT_ROWS,

      CHORUS_B.PARTKEYCOLNUM,

      CHORUS_B.CHECKRID,

      CHORUS_B.ARCHIVING_SCHEMA,

      CHORUS_B.CLUSTERRID,

      CHORUS_B.PARENTS,

      CHORUS_B.SPACEF,

      CHORUS_B.LABEL,

      CHORUS_B.CREATEDTS,

      CHORUS_B.RBA1,

      CHORUS_B.RBA2,

      CHORUS_B.LOCATION,

      CHORUS_B.ALTEREDTS,

      CHORUS_B.CHECKRID5B,

      CHORUS_B.STATSTIME,

      CHORUS_B.KEYOBID,

      CHORUS_B.TBCREATOR,

      CHORUS_B.VERSIONING_TABLE,

      CHORUS_B.CARDF,

      CHORUS_B.DBNAME,

      'DBMzDB2c.CAT_TABLES_Rbase_V111' AS "__objecttype__"

      FROM

      (

         SELECT

         TB.*

         FROM (SELECT * FROM (SELECT * FROM TB) AS TB) AS TB,

         (

            SELECT

            PD.*

            FROM (SELECT * FROM DBMzDB2c.CAT_SYSPLANDEP_Rbase_V111) AS PD

         )

         AS PD

         WHERE (PD.DNAME = 'DSNESPCS')

         AND (PD.BTYPE IN ('B', 'C', 'G', 'M', 'T'))

         AND (TB.CREATOR = PD.BCREATOR)

         AND (TB.NAME = PD.BNAME)

      )

      AS CHORUS_B LEFT OUTER

      JOIN

      (

         SELECT

         *

         FROM (SELECT * FROM chorusdata.NOTES_COUNT_SUMMARY) AS CHORUS_J0

      )

      AS CHORUS_J0 ON primary_key

      (

         'DBMzDB2c.CAT_TABLES_Rbase_V111',

         'dsSSID',

         CHORUS_B.dsSSID,

         'NAME',

         CHORUS_B.NAME,

         'CREATOR',

         CHORUS_B.CREATOR,

         'dsGroup',

         CHORUS_B.dsGroup,

         'dsConf',

         CHORUS_B.dsConf,

         'dsSystem',

         CHORUS_B.dsSystem

      )

      = CHORUS_J0.OBJECT_PKEY

      AND IFNULL

      (

         CHORUS_J0.USER_ID, 0

      )

      =

      (

         SELECT

         CASE COUNT

         (

            NOTES_COUNT_SUMMARY.USER_ID

         )

         WHEN 0 THEN 0 ELSE

         (

            SELECT

            get_chorus_user."USER"

            FROM

            (

               SELECT

               *

               FROM

               (

                  SELECT

                  *

                  FROM chorus_platform_config.get_chorus_user

               )

               AS get_chorus_user

            )

            AS get_chorus_user

         )

         END

         FROM

         (

            SELECT

            *

            FROM (SELECT * FROM chorusdata.NOTES_COUNT_SUMMARY) AS NOTES_COUNT_SUMMARY

         )

         AS NOTES_COUNT_SUMMARY

         WHERE

         (

            primary_key

            (

               'DBMzDB2c.CAT_TABLES_Rbase_V111',

               'dsSSID',

               CHORUS_B.dsSSID,

               'NAME',

               CHORUS_B.NAME,

               'CREATOR',

               CHORUS_B.CREATOR,

               'dsGroup',

               CHORUS_B.dsGroup,

               'dsConf',

               CHORUS_B.dsConf,

               'dsSystem',

               CHORUS_B.dsSystem

            )

            = NOTES_COUNT_SUMMARY.OBJECT_PKEY

         )

         AND

         (

            NOTES_COUNT_SUMMARY.USER_ID =

            (

               SELECT

               get_chorus_user."USER"

               FROM

               (

                  SELECT

                  *

                  FROM

                  (

                     SELECT

                     *

                     FROM chorus_platform_config.get_chorus_user

                  )

                  AS get_chorus_user

               )

               AS get_chorus_user

            )

         )

      )

      WHERE (CHORUS_B.dsConf = 'QA8800')

      AND (CHORUS_B.dsGroup = '')

      AND (CHORUS_B.dsSystem = 'CA11')

      AND (CHORUS_B.dsSSID = 'DH0G');

      END

       

      Thanks

      Rakesh