To set non-default values for code generation options that apply to a singe SQL statement, you must complete the Code Generation - Statement Options dialog box, as shown below.

Code Generation - Statement Options Dialog Box

Default Values

The Code Generation - Statement Options dialog box displays the current statement type, and specifies the following options that apply to the currently open SQL statement:

•Action routine name

•WHENEVER processing

•Cursor options

The Action Routine Name entry field specifies the name that will appear after the OPEN_, FETCH_, CLOSE_, or EXECUTE_ prefix in the name of generated action routines.

The default is the statement name. For example, an INSERT statement named InsertStaff generates an action routine named EXECUTE_InsertStaff

You can change the action routine name by specifying a new name in the Name entry field. However, you cannot change the use of the OPEN_, FETCH_ CLOSE_ or EXECUTE_ prefix (For information about how DB/Assist uses these prefixes in generated code, see Examples of Generated ESL Code.)

The WHENEVER Processing options in the Code Generation - Statement Options dialog box are identical to the Default WHENEVER Processing options in the Code Generation Options dialog box, and will override these defaults.

These options specify the actions that your ESL program should take if it finds any of the following SQL exception conditions:

•SQL system error (SQLERROR)

•SQL system warning (SQLWARNING)

•No rows found (SQLNOTFOUND)

Unless you set different WHENEVER processing options for a particular statement in the Code Generation - Statement Options dialog box, DB/Assist applies the default WHENEVER processing options set in the Code Generation Options dialog box to each statement in the SAM (For information about completing the Code Generation Options dialog box to set SAM-wide options, see Setting Code Generation Options for a SAM.)

Therefore, if you want to override the default WHENEVER processing for a particular statement, use the Code Generation - Statement Options dialog box to set WHENEVER processing options. These options apply only to the current SQL statement.

For complete information on the WHENEVER processing options and the default error handling routine, EDB2WheneverError, see Setting Code Generation Options for a SAM.

If you are building a multi-row SELECT, statement, the cursor options enable you to specify the following.

•The name of the cursor assigned to the statement

•Whether the cursor is declared with the FOR FETCH ONLY clause, the WITH HOLD clause, or both.

A cursor declared with the FOR FETCH ONLY clause has no UPDATE or DELETE statements issued against it. Read-only cursors can take advantage of record blocking provided by DB2. This minimizes network traffic and improves performance in a remote database environment.

On the other hand, a cursor declared with the WITH HOLD clause maintains resources across multiple units of recovery. This type of cursor declaration has different effects on units of recovery ending with COMMIT and units or recovery ending with ROLLBACK For complete information about these effects, see the online help for the Code Generation - Statement Options dialog box.

For multi-row SELECT statements against which you want to issue positioned UPDATE or positioned DELETE statements, you must declare the cursor with the WITH HOLD clause if you want to commit on a row-by-row basis. To do so, check the With Hold check box

The following table briefly describes the options in the Code Generation - Statement Options dialog box and lists their default values.

For complete information about each option, see the online help for the Code Generation - Statement Options dialog box.

WHENEVER Processing Group Box:

The following options are enabled only if Use Default is unchecked.

Cursor Options Group Box:

The following options are enabled only for multi-row SELECT statements.

Setting Non-Default Values

To set non-default values for code generation options that apply to a particular statement in the SAM, follow these steps:

1.Open the statement for which you want to set code generation options.
If you need information about opening an existing statement, see Opening an Existing SQL Statement.

2.Select SQL Statement Ø Code generation options.
The Code Generation - Statement Options dialog box appears (as shown above).

3.If you want to change the action routine name, type a new name in the Action Routine Name entry field.

4.If you want to override the default WHENEVER Processing options specified in the Code Generation Options dialog box, uncheck the Use Default box.
The WHENEVER Processing options become enabled.

5.Set the WHENEVER Processing options that you want in either of the following ways:

•Check the Continue box to execute the next sequential instruction in your program when a SQLERROR, SQLWARNING, or SQLNOTFOUND condition is found.

•Uncheck the Continue box and type an action routine name in the Action entry field. This passes program control to the specified action when a SQLERROR, SQLWARNING, or SQLNOTFOUND condition is found.

6.If you want to set cursor options for a multi-row SELECT, statement, do one or more of the following:

•Type a cursor name in the Cursor Name entry field.

•Check the For Fetch Only box to declare the cursor with the FOR FETCH ONLY clause.

•Check the With Hold box to declare the cursor with the WITH HOLD clause.

7.Select the OK push button.
DB/Assist sets the selected options and saves them in the SAM.

For example, Figure 8-5 shows how you would complete the Code Generation - Statement Options dialog box to set the following options:

•Set NewSelectStaff as the action routine name.

•Use an action routine named MyErrorRoutine for the SQLERROR condition.

•Ignore the SQLWARNING and SQLNOTFOUND conditions by leaving Continue checked.

•Set NewSelectCursor as the cursor name.

•Declare the cursor with the WITH HOLD clause.

Completed Code Generation - Statement Options Dialog Box