Use SUBQUERYROWESTIMATE to give CONNX a hint as to how it should report the query plan when {statistics} is used.

 

Some queries that have a sub query may use a less optimized query plan if the sub query returns more than one row.  In this case, CONNX will automatically switch to the correct query plan at runtime, but the query plan reported by the {statistics} function may not be the same as the plan which was ultimately used.  If you find that the query plan reported by {statistics} is different than what is being used at runtime due to multiple rows being returned in a sub query, set SUBQUERYROWESTIMATE to a value greater than 1.

 

The query plan will only change at run time if returning multiple rows in a sub query will cause duplicate rows to be returned if a more optimized path is used.  For example, if an Adabas sub query uses a PE index and the sub query only returns 1 row, the PE index can be used.  If the sub query returns multiple rows however, duplicates would be returned so CONNX must switch to a different query plan at runtime.  Since the {statistics} function does not know the number of rows that will be returned, it is possible that it will return a query plan that is different than the one that will ultimately be used.

 

To tell CONNX to return a query plan for {statistics} that is based on a sub query returning multiple rows, set:

SUBQUERYROWESTIMATE=2

 

Default = 0.

 

Environments: Client, Windows, Unix

Configuration Manager: CONNX Settings; Current Key = CONNX; Key Value/Value Name =  SUBQUERYROWESTIMATE

 

Unix Environment Variable: CONNX.SUBQUERYROWESTIMATE