SQL SET OPTION Statement:

SQL's SET OPTION statement is a powerful way to control the parameters of the DB2 execution environment in which an SQL program runs.

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.

Few important things that can be controlled by SET OPTION are,
• CLOSQLCSR (Close SQL Cursor)
• COMMIT
• DATFMT (Date Format)
• DBGVIEW (Debug View)
• DFTRDBCOL (Default Relational Database Collection)
• DLYPRP (Delay Prepare)
• DYNUSRPRF (Dynamic User Profile)
• USRPRF (User Profile)

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, CloSqlCsr=*ENDMOD, DlyPrp=*YES
C/End-Exec