Before it starts building the results, IP EXECUTE can indicate to the OpenAccess SDK SQL engine that it is returning the complete set of records. This is done by calling:
dam_setOption(DAM_STMT_OPTION, hstmt,
DAM_STMT_OPTION_TABLE_ROWSET,
DAM_PROCESSING_ON);
This option is ignored by the OpenAccess SDK SQL engine in the following cases:
• If the query is a single table query.
• If the table being processed is an outer table in the join query.
• If the block join is disabled because the query is not block join compatible.
The IP should check the return code from dam_setOption to check for DAM_SUCCESS. DAM_NOT_AVAILABLE is returned when TableRowset cannot be supported for the query. If TableRowset is enabled for the inner table, the OpenAccess SDK SQL engine modifies the behavior of dam_isTargetRow() and dam_addRowToTable() to save the records returned for the entire execution of the join query. This ensures only one call to IP EXECUTE for the inner table records instead of multiple calls.
• dam_isTargetRow() validates all conditions except join conditions.
• dam_addRowToTable() saves the row in the inner table TableRowSet.
Before calling IP EXECUTE for records of the inner table, the Block Join query execution checks if the inner table TableRowSet is available, skip Step 3 of the Block Join Execution Sequence, and use the inner table TableRowSet instead of BlockJoinRowList in Step 4.
Note that when the IP sets the option for DAM_STMT_OPTION_TABLE_ROWSET, it should not operate in cursor mode and should return with DAM_SUCCESS after processing all records. The OpenAccess SDK SQL engine retains all records of the inner table in memory.
TableRowset size
The OpenAccess SDK SQL engine checks the memory that is used by the TableRowset. If it exceeds the CacheMemSize limit, the OpenAccess SDK SQL engine disables the table result set caching and moves the result rows from the TableRowset to BlockJoin Rowset. This ensures that the TableRowset size does not grow too large. The IP is not notified about this, so it continues to process the TableRowset. The OpenAccess SDK SQL engine calls IP EXECUTE again for matching inner table records, when it processes the next block of outer table records.
Table rowset and JOINBLOCK_SIZE
If the IP were to process all inner tables as TableRowsets, having a higher JoinBlockSize may improve performance. The OpenAccess SDK SQL engine uses JoinBlockSize to process a block of outer table records. These records are sorted and then matching records are found from the inner table TableRowset.
