SELECT processing with DAM_START_QUERY and DAM_END_QUERY
OpenAccess SDK SQL engine calls IP_EXECUTE once the query is parsed and validated, and the join order of the query is decided. The OpenAccess SDK SQL engine assigns a process order starting at 0 for each table in a JOIN query. If a query is a single-table operation, then the process order of the single table in the query is 0.
The procedure that is described in this section is used to first process nested queries and then the outer query. To execute each nested and outer query, use this sequence of operations:
1. The OpenAccess SDK SQL engine calls IP EXECUTE (DAM_START_QUERY) for each table in the query using the process order of the tables. The table with process order 0 is called first, followed by others.
For a single table query, only one call is made to IP EXECUTE (DAM_START_QUERY).
For JOIN queries, IP EXECUTE (DAM_START_QUERY) is called for each table. The IP can set up to process the query by getting details of the table and doing any initialization required for processing that table. The IP cannot get details of the query conditions at this stage.
The IP should perform the following operations when processing DAM_START_QUERY:
a. Get the type of query using this call: dam_getInfo(DAM_INFO_QUERY_TYPE).
The query type is one of the following:
• DAM_SELECT
• DAM_UPDATE
• DAM_DELETE
• DAM_INSERT
b. Get details of the table using dam_describeTable.
c. Get details of the columns using dam_getFirstCol/dam_getNextCol.
d. The SELECT type of query can check whether the query is part of a join by calling: dam_getInfo(DAM_INFO_JOIN_QUERY_SIZE).
If JoinQuerySize is greater than 1, the query is a join.
e. Allocate and initialize query context required for processing. Save the query context using dam_setIP_hstmt().
f. For JOIN queries, check the current table’s process order using this call: dam_getInfo(DAM_INFO_QUERY_PROCESS_ORDER).
The process order is from 0 to (JoinQuerySize-1). To get details of other tables in the join, call dam_describeTableByProcessOrder(), where ProcessOrder can be 0 to (JoinQuerySize-1).
This call returns table name and table number for all tables in the join. The IP can also get hstmt of these other tables by calling dam_getTableStmt().
g. Decide whether Pushdown Joins can be used, based on tables being processed in the Join and the sequence in which they are processed.
h. Check comments in the query by calling dam_getFirstComment and dam_getNextComment.
2. The OpenAccess SDK SQL engine calls IP EXECUTE with the type set to one of the following:
• DAM_SELECT
• DAM_UPDATE
• DAM_DELETE
• DAM_DELETE
The IP should perform the operation. The IP should complete the operation and return DAM_SUCCESS or process a block of results and return DAM_SUCCESS_WITH_RESULT_PENDING.
3. In case of normal join processing, Step 2 is repeated for each row of the outer table.
4. The IP returns partial results for the outer table, and the OpenAccess SDK SQL engine calls IP EXECUTE(DAM_FETCH) to process the next block of records of the outer table. The IP is called again to process inner table records.
Consider the following query example:
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept, division
WHERE dept.divno = division.divno AND division.location='California')
In this example, the OpenAccess SDK SQL engine first processes a nested query on dept and division tables using Step 1 through Step 4. The OpenAccess SDK SQL engine then processes an outer query on the emp table using the same steps.
When the query results are processed, the OpenAccess SDK SQL engine calls IP EXECUTE(END_QUERY) on all tables. Nested query tables are processed first, followed by outer table query. If the query has JOINS, then the tables are processed in the JOIN order.
This algorithm can be used with either simple SELECT processing or cursor-based SELECT processing. Use this approach to optimize join processing where many queries are executed on the same table with different input values.
For example, if a join using the default OpenAccess SDK SQL engine join processing algorithm is performed between emp and dept, the first query is executed on dept to return matching rows in that table. Then emp is queried once for each row returned from the dept table query. If your data source is file-based, then opening and closing the emp file for each query can be time consuming. Here, you would use DAM_START_QUERY to open the file associated with each table and use DAM_END_QUERY to close all open files.
During the DAM_START_QUERY processing, the IP should do the following:
• Allocate memory for the IP defined data structure to store statement information that can be used later.
• Get the table information using dam_describeTable.
• Get type of query that would be done (for example, SELECT, INSERT) using dam_getInfo(DAM_INFO_QUERY_TYPE).
• Save the IP statement information data structure using dam_setIP_hstmt. This handle can be retrieved during execution using the dam_getIP_hstmt function. You may also store information that applies to the entire query (which would be executed as multiple queries in a JOIN scenario). This is done by using dam_setInfo with the DAM_INFO_STMT_IP_CONTEXT option. These two methods allow you to maintain context for each subquery and for the entire query.
During the query execution, the IP should retrieve the IP allocated statement data pointer by using the dam_getIP_hstmt to retrieve the IP statement handle or dam_getInfo(DAM_INFO_STMT_IP_CONTEXT).
When the execution is complete, the IP receives a DAM_END_QUERY at which time it can free any allocated resources.
