Let's Start Over With a New Beginning

Published: January 6, 2010

Hey, Ted:

I'm writing in response to your article Let's Start Over from the Beginning. Neither specifying CLOSQLCSR(*ENDMOD) nor running your program in a activation group *NEW (which is not even possible if your procedure is located in a service program and repeatedly called) is a good solution.

In both situations the open data path (ODP) will be deleted after execution and cannot be reused. That means each time you execute your query, the system performs a full open, which is a time-consuming process.

A full open is always necessary the first and second time a query is executed within the same activation group. When executing a full open, an access plan must be either created or validated. (If an access plan already exists, for example in the SQL plan cache or stored within the program object.) An access plan describes which indexes must be used, or whether a table scan or table probe must be executed, and also contains all information about any temporary objects, such as hash tables or relative record lists, that are necessary for executing the SQL statement.

After the access plan is generated or validated, the temporary objects will be created and filled with data (i.e., the ODP will be opened). Opening the ODP is the most time-consuming part of the optimization. After the first execution the ODP will be deleted. The next time the same query is executed, the already actualized access plan will be checked again and the ODP reopened. After the second execution the ODP stays open, which means for all subsequent executions, only the data within the temporary objects must be actualized.

When specifying CLOSQLCSR(*ENDMOD), the ODP will be deleted at the end of the module. That means each time you execute the same query--even within the same activation group--the access plan must be validated and the ODP must be recreated and cannot be reused.

If I tell my users that they will have to wait for half a minute the first time they call a program, but that subsequent calls will be faster, they will accept it. But my users will complain if they have to wait for 30 seconds each time they call the program.

The easiest way to solve Lynne's problem is to close the cursor before opening it. If the cursor is open, it will be closed. If it was not open, the system generated an error--SQLCODE -501 or SQLSTATE 24501--which can be checked and ignored.

In this way the ODP becomes reusable, and beginning with the third execution, only a soft (or pseudo) open is necessary, which means only the data must be actualized when opening the cursor.

--Birgitta Hauser

The following comes from the IBM i 6.1 Information Center:

The first time (or times) a open occurs for a specific statement in a job is a full open. A full open creates an Open Data Path (ODP) that will be then be used to fetch, update, delete, or insert rows. Since there will typically be many fetch, update, delete, or insert operations for an ODP, as much processing of the SQL statement as possible is done during the ODP creation so that same processing does not need to be done on each subsequent I/O operation. An ODP may be cached at close time so that if the SQL statement is run again during the job, the ODP will be reused. Such an open is called a pseudo open and is much less expensive than a full open. You can control the number of ODPs that are cached in the job and then number of times the same ODP for a statement should be created before caching it.

Thanks to Birgitta Hauser for starting off the new year with this valuable information. My answer to Lynne was just for her situation, and therefore too simplistic.

--Ted

RELATED STORY

Let's Start Over from the Beginning

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot