These instructions are in addition to the instructions for setting up the DB2 environment in "Setup".
For SQL procedures support you have to install the Application Development Client and a DB2 supported C or C++ compiler on the server. For information on installing the Application Development Client, refer to the Quick Beginnings book for your platform. For the C and C++ compilers supported by DB2 on your platform, see "Supported Software by Platform".
| Note: | On the OS/2 FAT file system, you are limited to a schema name for SQL Procedures of eight characters or less. You have to use the HPFS file system for schema names longer than eight characters. |
Configuring the Compiler Environment
To create SQL procedures, configure DB2 to use a supported C or C++ compiler on the server by the following steps:
db2set DB2_SQLROUTINE_COMPILER_PATH=executable_file
where executable_file is the full path name for the C compiler environment file.
If you do not set the DB2_SQLROUTINE_COMPILER_PATH DB2 registry variable, DB2 sets it to a default file. Depending on your operating system, this file will have one of the following paths and file names:
You can use this default file as long as you modify it to reflect the settings required for the server operating system and the C or C++ compiler you are using.
| Note: | On Windows NT and Windows 2000, you do not have to set the DB2_SQLROUTINE_COMPILER_PATH DB2 registry variable if you store the environment variables for your compiler as SYSTEM variables. |
Customizing Compiler Options
DB2 provides default values for one of the compilers it supports on each platform. To use other compilers, set the SQL procedure compiler options using the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable. To specify customized C or C++ compiler options for SQL procedures, store the entire command line, including all options, in the DB2 registry with the following command:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=compiler_command
where compiler_command is the C or C++ compile command, including the options and parameters required to create stored procedures.
In the compiler command, use the keyword SQLROUTINE_FILENAME to replace the filename for the generated SQC, C, PDB, DEF, EXP, messages log and shared library files. For AIX only, use the keyword SQLROUTINE_ENTRY to replace the entry point name.
As examples of the default values for the DB2_SQLROUTINE_COMPILE_COMMAND for the supported C or C++ compilers, here are the default compiler values on AIX, Solaris, and Windows 32-bit operating systems. Also given are suggested changes to return debugging information. Similar changes can be made to return debugging information on other platforms.
xlC_r -+ -H512 -T512 -I$HOME/sqllib/include SQLROUTINE_FILENAME.c \
-bE:SQLROUTINE_FILENAME.exp -e SQLROUTINE_ENTRY \
-o SQLROUTINE_FILENAME -L$HOME/sqllib/lib -lc -ldb2
To return debug information, change the default to add the -g option to the DB2_SQLROUTINE_COMPILE_COMMAND as follows:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="xlC_r -+ -H512 -T512 -g \
-I$HOME/sqllib/include SQLROUTINE_FILENAME.c \
-bE:SQLROUTINE_FILENAME.exp -e SQLROUTINE_ENTRY \
-o SQLROUTINE_FILENAME -L$HOME/sqllib/lib -lc -ldb2"
where "\" is only used to indicate a carriage return.
| Note: | To compile 64-bit SQL procedures on AIX, add the -q64 option to the above commands. |
cc -# -Kpic -I$HOME/sqllib/include SQLROUTINE_FILENAME.c -G \
-o SQLROUTINE_FILENAME -L$HOME/sqllib/lib -R$HOME/sqllib/lib -ldb2
To return debug information, change the default to add the -g option to the DB2_SQLROUTINE_COMPILE_COMMAND as follows:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="cc -# -Kpic -g \
-I$HOME/sqllib/include SQLROUTINE_FILENAME.c -G \
-o SQLROUTINE_FILENAME -L$HOME/sqllib/lib \
-R$HOME/sqllib/lib -ldb2"
where "\" is only used to indicate a carriage return.
| Note: | To compile 64-bit SQL procedures on Solaris, add the -xarch=v9 option to the above commands. |
cl -Od -W2 /TC -D_X86_=1 -I%DB2PATH%\include SQLROUTINE_FILENAME.c
/link -dll -def:SQLROUTINE_FILENAME.def /out:SQLROUTINE_FILENAME.dll
%DB2PATH%\lib\db2api.lib
To return debug information, change the default as follows:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="cl -Od -W2 /TC -D_X86_=1
-Z7 -I%DB2PATH%\include SQLROUTINE_FILENAME.c /link -dll
-def:SQLROUTINE_FILENAME.def /out:SQLROUTINE_FILENAME.dll
-debug:full -pdb:none -debugtype:cv %DB2PATH%\lib\db2api.lib"
| Note: | You must enter the compiler command value on one line for Windows 32-bit operating systems. |
To return to the default compiler options, set the DB2 registry value for DB2_SQLROUTINE_COMPILE_COMMAND to null with the following command:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=
Retaining Intermediate Files
When you issue a CREATE PROCEDURE statement, DB2 creates a number of intermediate files that are normally deleted if DB2 successfully completes the statement. If an SQL procedure does not perform as expected, you might find it useful to examine the SQC, C, PDB, and message log files created by DB2. To keep the files that DB2 creates during the successful execution of a CREATE PROCEDURE statement, you must set the value of the DB2_SQLROUTINE_KEEP_FILES DB2 registry variable to "1", "y" or "yes", as in the following command:
db2set DB2_SQLROUTINE_KEEP_FILES=1
Depending on your operating system, the intermediate files are retained in one of the following directories:
$HOME/sqllib/function/routine/sqlproc/database_name/schema_name
where database_name and schema_name are the database and schema used to create the SQL procedures.
%DB2PATH%\function\routine\sqlproc\database_name\schema_name
where database_name and schema_name are the database and schema used to create the SQL procedures.
Customizing Precompile and Bind Options
The precompile and bind options can be customized by setting the DB2_SQLROUTINE_PREPOPTS DB2 registry variable. These options cannot be customized at procedure level. To specify customized precompilation options for SQL procedures, put the list of precompile options to be used by the DB2 precompiler in the DB2 registry with the following command:
db2set DB2_SQLROUTINE_PREPOPTS=options
where options specifies the list of precompile options to be used by the DB2 precompiler. Only the following options are allowed:
BLOCKING {UNAMBIG | ALL | NO}
DATETIME {DEF | USA | EUR | ISO | JIS | LOC}
DEGREE {1 | degree-of-parallelism | ANY}
DYNAMICRULES {BIND | RUN}
EXPLAIN {NO | YES | ALL}
EXPLAINSNAP {NO | YES | ALL}
INSERT {DEF | BUF}
ISOLATION {CS |RR |UR |RS |NC}
QUERYOPT optimization-level
SYNCPOINT {ONEPHASE | TWOPHASE | NONE}