DCTB Subqueries

The SQL queries generated by the RIM query API can be optimized in three different ways. By default, in all nested SELECT sub queries, the WHERE conditions will be generated as a SQL IN condition. This can be modified by configuring the HDR managed server start-up JVM argument CTB_SUBQRY_OPT_METHOD with one of the values NONE, EXISTS, or JOIN. Based on the database version and configuration, you can choose an option that results in the best database SQL execution plans for the HDR generated SQL queries.

-DCTB_SUBQRY_OPT_METHOD=NONE

This is the default behavior where all nested select sub queries in the where condition will be generated as the SQL IN condition.

-DCTB_SUBQRY_OPT_METHOD=EXISTS

By setting sub-query optimization method to EXISTS, all nested select sub queries in the where condition will be generated as the SQL EXISTS condition.

-DCTB_SUBQRY_OPT_METHOD=JOIN

By setting sub-query optimization method to JOIN, all nested select sub queries in the where condition will be converted to the SQL JOIN condition.