Using the SQL SET OPTION Statement

January 24, 2007 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:

*ENDACTGRP	SQL cursors are closed, SQL prepared statements are implicitly discarded, and LOCK TABLE locks are released when the activation group ends.
*ENDMOD	SQL cursors are closed and SQL prepared statements are implicitly discarded when the module is exited. LOCK TABLE locks are released when the first SQL program on the call stack ends.
*ENDPGM	SQL cursors are closed and SQL prepared statements are discarded when the program ends. LOCK TABLE locks are released when the first SQL program on the call stack ends.
*ENDSQL	SQL cursors remain open between calls and can be fetched without running another SQL OPEN. One of the programs higher on the call stack must have run at least one SQL statement. SQL cursors are closed, SQL prepared statements are discarded, and LOCK TABLE locks are released when the first SQL program on the call stack ends. If ENDSQL is specified for a program that is the first SQL program called (the first SQL program on the call stack), the program is treated as if ENDPGM was specified.
*ENDJOB	SQL cursors remain open between calls and can be fetched without running another SQL OPEN. The programs higher on the call stack do not need to have run SQL statements. SQL cursors are left open, SQL prepared statements are preserved, and LOCK TABLE locks are held when the first SQL program on the call stack ends. SQL cursors are closed, SQL prepared statements are discarded, and LOCK TABLE locks are released when the job ends.

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.

*CHG	Specifies the isolation level of Uncommitted Read. (this is the default)
*NONE	Specifies the isolation level of No Commit. In many SQL database systems this is known as a “dirty read” because the database manager doesn’t concern itself with whether uncommitted rows in pending transactions will actually be committed. It just reads them anyway thereby improving performance.
*CS	Specifies the isolation level of Cursor Stability.
*ALL	Specifies the isolation level of Read Stability.
*RR	Specifies the isolation level of Repeatable Read

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:

Long date formats:
*ISO	The International Organization for Standardization (ISO) date format (yyyy-mm-dd) is used.
*EUR	The European date format (dd.mm.yyyy) is used.
*USA	The United States date format (mm/dd/yyyy) is used.
*JIS	The Japanese Industrial Standard date format (yyyy-mm-dd) is used.
Short date formats
*JOB	The format specified for the current job is used.
*MDY	The date format (mm/dd/yy) is used.
*DMY	The date format (dd/mm/yy) is used.
*YMD	The date format (yy/mm/dd) is used.
*JUL	The Julian date format (yy/ddd) is used.

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.

*NONE	A debug view will not be generated.
*SOURCE	Allows the compiled module object to be debugged using SQL statement source. If *SOURCE is specified, the modified source is stored in source file QSQDSRC in the same schema as the created function, procedure, or trigger.
*STMT	Allows the compiled module object to be debugged using program statement numbers and symbolic identifiers.
*LIST	Generates the listing view for debugging the compiled module object.

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:

*SQL

SQL objects
(tables, views, etc.) are qualified using a period in the form: schema.object.
If a default relational database collection (i.e. library) is specified, then
this value is used to tell the SQL processor where to find all unqualified
table or view references. If DFTRDBCOL isn’t specified then unqualified
tables and views are assumed to be in a schema with the same name as the job’s
user profile. The SQL naming convention is useful in that it resembles SQL
object qualification in other database systems.

*SYS

SQL objects
are qualified using the familiar schema (library)/object format used on the
OS/400 command line. Unqualified objects are identified by a library list
search. This option is useful because it can take advantage of the AS/400’s
library list system.

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