Using the SQL SET OPTION Statement

Published: January 24, 2007

by Michael Sansoterra

SQL's SET OPTION statement is a powerful way to control the parameters of the DB2 execution environment in which an SQL program runs. Using it properly will allow SQL code to execute consistently between different environments--including those thorny differences that often arise between green screen and client/server environments. Read on to find out why this statement is essential to embedded SQL programs and persistent stored modules (SQL based triggers, procedures and functions).

First of all, SET OPTION is a statement that is evaluated at "compile time." It never actually gets executed. Therefore SET OPTION can only be specified once in a program. In embedded SQL, it should be the first SQL statement in the program. (For ILE RPG programmers, this is similar to specifying compile time options in the H spec.) For SQL routines (triggers, functions and stored procedures) SET OPTION is actually implemented as a clause in the various CREATE statements.

So what can we control with SET OPTION? There are many items, but I'll cover just a few that I use often. See the IBM SQL Reference for the complete list.

CLOSQLCSR (Close SQL Cursor): This option controls when the database manager automatically releases various resources including open cursors, locked tables (from LOCK TABLE) and prepared statements. Of course, all of these resources can be released programmatically using the appropriate SQL statements such as CLOSE, COMMIT, etc. Valid values for this option are:

When is this option most useful? I find specifying CLOSQLCSR=*ENDMOD useful when developing dynamic SQL programs. Often dynamic SQL programs crash after a prepared statement has been opened due to an invalid statement or FETCH. The program is fixed and called again but this time an SQL0502 "Cursor x already opened" message is issued. Selecting *ENDMOD, instead of the default *ENDACTGRP will insure that these "dangling" resources are closed properly even if the module ends abnormally.

Incidentally, this option is only allowed in embedded SQL programs. ILE programs may only use *ENDACTGRP or *ENDMOD while OPM programs may only use the remaining options.

COMMIT: This option specifies the default commitment control level to be used by the program or routine.

Since many legacy applications do not use journaling and therefore are not able to participate in transaction processing, I specify *NONE in these cases. From a performance perspective, even if application tables are journaled it may still be beneficial to specify a COMMIT level of *NONE in cases when the data is relatively static (such as when doing history reporting).

DATFMT (Date Format): This option controls the default date format that SQL uses to handle dates. The valid options can be divided into two categories:

As a matter of practice, I always specify *ISO, which is usually the default for non-green screen interfaces (ODBC, JDBC, .NET). The default for green screen interfaces is *JOB. I prefer the long date formats that specify a four-digit year because they can handle the widest range of date values, while the short values can only hold dates within a pre-defined century range. Since it is often necessary to show the user a short date format, I often specify a different date format in the client application, display or printer DDS specifications.

Keep in mind that when building character strings with a two-digit year to be converted into a date, the date format option controls how the character data must be supplied. Consider the following trivial example of converting a character representation of 07/12/31 into an actual date:

SET :HOSTVAR=DATE('07' || '/' || '12' || '/' || '31')

In this example, if the date format is set to *YMD the conversion will be successful. However, if one of the other short date formats is specified then the conversion will fail. Long date constructions will convert successfully regardless of the date format. When doing character to date casts in SQL, I prefer to build my date representation strings in one of the long formats (usually *ISO). This way it doesn't matter which date format is being used in the program so the code snippet can always be re-used.

DBGVIEW (Debug View): This option controls the debug level of SQL modules only (SQL triggers, functions, and stored procedures). Unfortunately, it cannot be specified in an embedded SQL program.

SQL modules are actually converted into embedded SQL ILE C programs "behind the scenes." Therefore, these options are virtually identical to the parameters values for the DBGVIEW option of the CRTBNDC command. STRDBG can be used to debug the SQL modules like any other ILE C program.

DFTRDBCOL (Default Relational Database Collection): This option is used to indicate what OS/400 library should be used when working with unqualified tables, views and indexes. The SQL term "collection" has largely been abandoned by IBM in favor of the term schema.

This option is useful when working with an SQL program that creates or accesses several objects in a single library. Newly created objects need a default schema (and the library list doesn't help). Likewise, setting DFTRDBCOL can be used to access objects that are not in an application's default library list.

This option may not be specified for an SQL module. However, the SQL module will inherit the setting from the host SQL job that is active when the module is created.

DLYPRP (Delay Prepare): Specifies whether the dynamic statement validation for a PREPARE statement is delayed until an OPEN, EXECUTE, or DESCRIBE statement is run. Valid values are *YES and *NO (default).

This option is useful when executing dynamic SQL statements. Often subfile and web programs need to perform searches where the criteria isn't known until run time. In these cases programs build dynamic SQL statements on the fly. The logic for processing dynamic SQL queries is as follows:

PREPARE (creates an executable form of a text SQL statement)
DECLARE (declare a cursor based on a prepared statement)
OPEN (open a cursor to retrieve results)
FETCH (retrieve results)
CLOSE (close cursor)

Under normal circumstances the PREPARE statement and the OPEN statement perform some redundant processing. Specifying *YES for this option will cause dynamic SQL to perform faster by waiting to do the PREPARE processing at the time the OPEN is executed and thereby eliminate some of the redundant processing. Unless you need the statement's metadata (parameter or column information) that is provided by the PREPARE statement, specify *YES for this parameter.

DYNUSRPRF (Dynamic User Profile): This setting is used to indicate if dynamic SQL code should execute under the job user's authority or the object owner's authority (adopted authority.) The permissible values are: *USER (default) or *OWNER. This setting is often overlooked by SQL developers who are new to embedded SQL. The companion setting USRPRF (see below) does not apply to dynamic SQL.

NAMING: This option specifies whether SQL statements should follow the system or SQL naming convention. Valid values are:

The default for this option depends on the SQL environment. The default for most green screen commands (CRTxxxSQL, STRSQL, CRTQMQRY, etc.) is *SYS. The default for most IBM client/server connectivity options (JDBC, ODBC, OLE DB, .NET managed provider) is *SQL.

This option is only allowed in embedded SQL programs. When creating SQL modules the naming convention is inherited from the host SQL session's naming convention. For example, if you're creating an SQL stored procedure in the Run SQL Scripts option of iSeries Navigator using the *SQL naming convention, then the stored procedure will be created with *SQL naming. If the same stored procedure is created using the STRSQL utility using the *SYS naming convention then the stored procedure will be created using the *SYS naming convention.

USRPRF (User Profile): This option indicates if the static SQL statements within a program should run under the job user's authority or the object owner's authority (adopted authority.) The valid values are *USER (default) or *OWNER.

Most typical applications should have data access protected from the average Joe's user profile. Access to these tables should only be temporarily granted through the controlled environment of a program or SQL module. Therefore, for most application programming this value should be set to *OWNER.

Now that we know some of the more popular options, here is an example of how they are specified in an RPG embedded SQL program:

C/Exec SQL
C+ Set Option Commit=*NONE, DatFmt=*ISO, DynUsrPrf=*Owner, DlyPrp=*YES
C/End-Exec

Likewise, here is how SET OPTION is specified in a CREATE PROCEDURE statement:

CREATE PROCEDURE spGetResult(@SQL VarChar(1024))
Result Sets 1
Language SQL
Set Option Commit=*None, DatFmt=*ISO, DynUsrPrf=*OWNER, DlyPrp=*YES
Begin
. . .
End

The Set Option clause would similarly be specified in CREATE FUNCTION and CREATE TRIGGER statements.

In summary, SET OPTION provides a great way to define many important parameters for how SQL statements within a program or module will execute. Adopted authority, default commitment control, date format and more can be specified with SET OPTION. Since it is compile time only, it also serves as a documentation feature as an alternative to looking at the SQL attributes of an embedded SQL program or SQL module. For consistency between environments and for ease of compilation, I use SET OPTION in every one of my SQL programs.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.

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