Reader Feedback on Using the SQL SET OPTION Statement

Published: March 21, 2007

Hey, Michael:

I just read your article about the SET OPTION statement. Here are some minor comments about the OPTION CLOSQLCSR. There is a difference between closing a SQL cursor with the SQL statements CLOSE or COMMIT and closing a cursor at either *ENDMOD or *ENDACTGRP (or the other options for non-ILE programs).

When closing a cursor with the SQL commands, only a soft (or pseudo) close will be performed, that is the ODP (if reusable) stays cached and will only be reactivated the next time the SQL statement will be executed.

Closing a cursor at the end of a module (*ENDMOD) or at the end of the activation group (*ENDACTGRP) will hard close the ODPs, that is, all temporary objects are deleted. When rerunning the same SQL statements, a complete full open must be performed. A full open includes among others: Index estimates, creating and validating access plans, creating the ODP based on the validated access plans. The creation of the ODP (i.e. temporary objects such as bitmaps or hash tables or temporary tables or indexes) is the most expensive process in the optimization. And each SQL statement gets its own ODP, which means that if the same statement is coded several times for each of these statements, then the complete optimization process must be performed.

The main goal to gain performance is, to minimize the full opens and hard closes. This can be achieved by a modular programming, where each SQL statement embedded in its own exported procedure and by avoiding the hard closing of cursors.

Instead of specifying *ENDMOD to be sure that the cursor really gets closed, it would be much better to execute the SQL statement CLOSE just before executing the OPEN statement or the PREPARE statement with dynamic SQL. If the cursor is already opened, it will be closed and with the next open the ODP can be reused. If the cursor is already closed, so much the better.

Form performance aspects, specifying *ENDMOD or using activation group *NEW should be avoided, the optimization process is too expensive! I can explain to my users that they have to wait for half a minute the first time they execute a program. They'll accept it, if all the subsequent executions will perform in less than 2 seconds. But I'll get into trouble if the users have to wait half a minute each time they call the program.

By the way, dynamic SQL also should be reduced as much as possible. With dynamic SQL the access plans always must be created from scratch when executing the statement. With static SQL on the other hand the access plans are stored and updated in the (service) program objects and only must be validated at run time. But it becomes better when the SQL statements can be executed by the SQL Query Engine, because these access plans get stored in the plan cache and can be used by all jobs.

Best regards,

--Birgitta Hauser, SSS-Software GmbH

Thanks, Birgitta. These comments are really helpful. We'll add a link to it at the bottom of the original article so people see it.

--Michael

RELATED STORY

Using the SQL SET OPTION Statement

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