AIX
DB2 for AIX 7.1 supports stored procedures built with Micro Focus COBOL compiler 4.1.30 on AIX 4.2.1. For information on DB2 support for Micro Focus COBOL stored procedures on AIX 4.3, please see the DB2 Application Development Web page:
http://www.ibm.com/software/data/db2/udb/ad
HP-UX
The listed version for the C++ compiler should be the following:
HP aC++, Version A.03.13
| Note: | HP does not support binary compatibility among objects compiled with old and
new compilers, so this will force recompiles of any C++ application
built to access DB2 on HP-UX. C++ applications must also be
built to handle exceptions with this new compiler.
Following is the URL for the aCC transition guide: http://www.hp.com/esy/lang/cpp/tguide. The C++ incompatibility portion is here: http://www.hp.com/esy/lang/cpp/tguide/transcontent.html#RN.CVT.1.2 http://www.hp.com/esy/lang/cpp/tguide/transcontent.html#RN.CVT.3.3 The C vs C++ portion is here: http://www.hp.com/esy/lang/cpp/tguide/transcontent.html#RN.CVT.3.3.1 Even though C and aCC are compatible, when using the two different object types, the object containing "main" must be compiled with aCC, and the final executable must be linked with aCC. |
OS/2
The listed versions for C/C++ compiler should be the following:
IBM VisualAge C++ for OS/2 Version 3.0, Version 3.6.5, and Version 4.0
Windows 32-bit Operating Systems
The listed versions for the IBM VisualAge C++ compiler should be the following:
IBM VisualAge C++ for Windows Versions 3.6.5 and 4.0
The following should be added to the "Object Linking and Embedding Samples" section:
The following should be added to the "Log Management User Exit Samples" section:
Applications on AIX using the ADSM API Client at level 3.1.6 and higher must be built with the xlc_r or xlC_r compiler invocations, not with xlc or xlC, even if the applications are single-threaded. This ensures that the libraries are thread-safe. This applies to the Log Management User Exit Sample, db2uext2.cadsm.
If you have an application that is compiled with a non thread-safe library, you can apply fixtest IC21925E or contact your application provider. The fixtest is available on the index.storsys.ibm.com anonymous ftp server. This will regress the ADSM API level to 3.1.3.
The entry for bldevm in table 16 should read:
Table 17 should include the entries:
If you are using IBM JDK 1.1.8 on supported platforms to build SQLJ programs, a JDK build date of November 24, 1999 (or later) is required. Otherwise you may get JNI panic errors during compilation.
If you are using IBM JDK 1.2.2 on supported platforms to build SQLJ programs, a JDK build date of April 17, 2000 (or later) is required. Otherwise, you may get Invalid Java type errors during compilation.
For sub-sections AIX and Solaris, replace the information on JDBC 2.0 with the following:
Using the JDBC 2.0 Driver with Java Applications
The JDBC 1.22 driver is still the default driver on all operating systems. To take advantage of the new features of JDBC 2.0, you must install JDK 1.2 support. Before executing an application that takes advantage of the new features of JDBC 2.0, you must set your environment by issuing the usejdbc2 command from the sqllib/java12 directory. If you want your applications to always use the JDBC 2.0 driver, consider adding the following line to your login profile, such as .profile, or your shell initialization script, such as .bashrc, .cshrc, or .kshrc:
. sqllib/java12/usejdbc2
Ensure that this command is placed after the command to run db2profile, as usejdbc2 should be run after db2profile.
To switch back to the JDBC 1.22 driver, execute the following command from the sqllib/java12 directory:
. usejdbc1
Using the JDBC 2.0 Driver with Java Stored Procedures and UDFs
To use the JDBC 2.0 driver with Java stored procedures and UDFs, you must set the environment for the fenced user ID for your instance. The default fenced user ID is db2fenc1. To set the environment for the fenced user ID, perform the following steps:
. sqllib/java12/usejdbc2
db2set DB2_USE_JDK12=1
To switch back to the JDBC 1.22 driver support for Java UDFs and stored procedures, perform the following steps:
. sqllib/java12/usejdbc2
db2set DB2_USE_JDK12=
If you want your applications to always use the JDBC 2.0 driver, you can add the following line to your login profile, such as .profile, or your shell initialization script, such as .bashrc, .cshrc, or .kshrc:
. sqllib/java12/usejdbc2
Ensure that this command is placed after the command to run db2profile, as usejdbc2 should be run after db2profile.
For sub-section Silicon Graphics IRIX, add the following:
When building SQLJ applications with the -o32 object type, using the Java JIT compiler with JDK 1.2.2, if the SQLJ translator fails with a segmentation fault, try turning off the JIT compiler with this command:
export JAVA_COMPILER=NONE
For sub-section Windows 32-bit Operating Systems, add the following:
Using the JDBC 2.0 Driver with Java Stored Procedures and UDFs
To use the JDBC 2.0 driver with Java stored procedures and UDFs, you must set the environment by performing the following steps:
usejdbc2
db2set DB2_USE_JDK12=1
To switch back to the JDBC 1.22 driver support for Java UDFs and stored procedures, perform the following steps:
usejdbc2
db2set DB2_USE_JDK12=
The following replaces the corresponding sections in the book.
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. For example,
OS2:
for IBM VisualAge C++ for OS/2 Version 3.6:
db2set DB2_SQLROUTINE_COMPILER_PATH="c:\ibmcxxo\bin\setenv.cmd"
for IBM VisualAge C++ for OS/2 Version 4:
db2set DB2_SQLROUTINE_COMPILER_PATH="c:\ibmcpp40\bin\setenv.cmd"
Windows 32-bit operating systems
for Microsoft Visual C++ Versions 5.0:
db2set DB2_SQLROUTINE_COMPILER_PATH="c:\devstudio\vc\bin\vcvars32.bat"
for Microsoft Visual C++ Versions 6.0:
db2set DB2_SQLROUTINE_COMPILER_PATH="c:\Micros~1\vc98\bin\vcvars32.bat"
for IBM VisualAge C++ for Windows Version 3.6:
db2set DB2_SQLROUTINE_COMPILER_PATH="c:\ibmcxxw\bin\setenv.cmd"
for IBM VisualAge C++ for Windows Version 4:
db2set DB2_SQLROUTINE_COMPILER_PATH="c:\ibmcppw40\bin\setenv.cmd"
If you do not set the DB2_SQLROUTINE_COMPILER_PATH DB2 registry variable, DB2 will create a default file at the first attempt of creating any SQL procedure. 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 update 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. |
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.
The following are the default values for the DB2_SQLROUTINE_COMPILE_COMMAND for C or C++ compilers on supported server platforms.
AIX
To use IBM C for AIX Version 3.6.6:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=xlc -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 use IBM C Set++ for AIX Version 3.6.6:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=xlC -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
This is the default compile command if the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable is not set.
| Note: | To compile 64-bit SQL procedures on AIX, add the -q64 option to the above commands. |
db2set DB2_SQLROUTINE_COMPILE_COMMAND="vacbld"
If you do not specify the configuration file after vacbld command, DB2 will create the following default configuration file at the first attempt of creating any SQL procedure:
$HOME/sqllib/function/routine/sqlproc.icc
If you want to use your own configuration file, you can specify your own configuration file when setting the DB2 registry value for DB2_SQLROUTINE_COMPILE_COMMAND:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="vacbld %DB2PATH%/function/sqlproc.icc"
HP-UX
To use HP C Compiler Version A.11.00.03:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=cc +DAportable +ul -Aa +z \ -I$HOME/sqllib/include -c SQLROUTINE_FILENAME.c; \ ld -b -o SQLROUTINE_FILENAME SQLROUTINE_FILENAME.o \ -L$HOME/sqllib/lib -ldb2
To use HP-UX C++ Version A.12.00:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=CC +DAportable +a1 +z -ext \ -I$HOME/sqllib/include -c SQLROUTINE_FILENAME.c; \ ld -b -o SQLROUTINE_FILENAME SQLROUTINE_FILENAME.o \ -L$HOME/sqllib/lib -ldb2
This is the default compile command if the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable is not set.
Linux
To use GNU/Linux gcc Version 2.7.2.3:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=cc \ -I$HOME/sqllib/include SQLROUTINE_FILENAME.c \ -shared -o SQLROUTINE_FILENAME -L$HOME/sqllib/lib -ldb2
To use GNU/Linux g++ Version egcs-2.90.27 980315 (egcs-1.0.2 release):
db2set DB2_SQLROUTINE_COMPILE_COMMAND=g++ \ -I$HOME/sqllib/include SQLROUTINE_FILENAME.c \ -shared -o SQLROUTINE_FILENAME -L$HOME/sqllib/lib -ldb2
This is the default compile command if the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable is not set.
PTX
To use ptx/C Version 4.5:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=cc -KPIC \ -I$HOME/sqllib/include SQLROUTINE_FILENAME.c \ -G -o SQLROUTINE_FILENAME.so -L$HOME/sqllib/lib -ldb2 ; \ cp SQLROUTINE_FILENAME.so SQLROUTINE_FILENAME
To use ptx/C++ Version 5.2:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=c++ -KPIC \ -D_RWSTD_COMPILE_INSTANTIATE=0 -I$HOME/sqllib/include SQLROUTINE_FILENAME.c \ -G -o SQLROUTINE_FILENAME.so -L$HOME/sqllib/lib -ldb2 ; \ cp SQLROUTINE_FILENAME.so SQLROUTINE_FILENAME
This is the default compile command if the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable is not set.
OS/2
To use IBM VisualAge C++ for OS/2 Version 3:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="icc -Ge- -Gm+ -W2 -I%DB2PATH%\include SQLROUTINE_FILENAME.c /B\"/NOFREE /NOI /ST:64000\" SQLROUTINE_FILENAME.def %DB2PATH%\lib\db2api.lib"
This is the default compile command if the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable is not set.
To use IBM VisualAge C++ for OS/2 Version 4:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="vacbld"
If you do not specify the configuration file after vacbld command, DB2 will create the following default configuration file at the first attempt of creating any SQL procedure:
%DB2PATH%\function\routine\sqlproc.icc
If you want to use your own configuration file, you can specify your own configuration file when setting the DB2 registry value for DB2_SQLROUTINE_COMPILE_COMMAND:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="vacbld %DB2PATH%\function\sqlproc.icc"
Solaris
To use SPARCompiler C Versions 4.2 and 5.0:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=cc -xarch=v8plusa -Kpic \ -I$HOME/sqllib/include SQLROUTINE_FILENAME.c \ -G -o SQLROUTINE_FILENAME -L$HOME/sqllib/lib \ -R$HOME/sqllib/lib -ldb2
To use SPARCompiler C++ Versions 4.2 and 5.0:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=CC -xarch=v8plusa -Kpic \ -I$HOME/sqllib/include SQLROUTINE_FILENAME.c \ -G -o SQLROUTINE_FILENAME -L$HOME/sqllib/lib \ -R$HOME/sqllib/lib -ldb2
This is the default compile command if the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable is not set.
Notes:
Windows NT and Windows 2000
| Note: | SQL procedures are not supported on Windows 98 or Windows 95. |
To use Microsoft Visual C++ Versions 5.0 and 6.0:
db2set DB2_SQLROUTINE_COMPILE_COMMAND=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
This is the default compile command if the DB2_SQLROUTINE_COMPILE_COMMAND DB2 registry variable is not set.
To use IBM VisualAge C++ for Windows Version 3.6:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="ilib /GI SQLROUTINE_FILENAME.def & icc -Ti -Ge- -Gm+ -W2 -I%DB2PATH%\include SQLROUTINE_FILENAME.c /B\"/ST:64000 /PM:VIO /DLL\" SQLROUTINE_FILENAME.exp %DB2PATH%\lib\db2api.lib"
To use IBM VisualAge C++ for Windows Version 4:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="vacbld"
If you do not specify the configuration file after vacbld command, DB2 will create the following default configuration file at the first attempt of creating any SQL procedure:
%DB2PATH%\function\routine\sqlproc.icc
If you want to use your own configuration file, you can specify your own configuration file when setting the DB2 registry value for DB2_SQLROUTINE_COMPILE_COMMAND:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="vacbld %DB2PATH%\function\sqlproc.icc"
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=
You have to manually delete intermediate files that may be left when an SQL procedure is not created successfully. These files are in the following directories:
where $DB2PATH represents the directory in which the instance was created, $DATABASE represents the database name, and $SCHEMA represents the schema name with which the SQL procedures were created.
where %DB2PATH% represents the directory in which the instance was created, %DATABASE% represents the database name, and %SCHEMA% represents the schema name with which the SQL procedures were created.
The executables of SQL procedures on the filesystem should be backed up and restored at the same time as the database. This must be done manually. The executables are in the following directory:
where $DB2PATH represents the directory in which the instance was created and $DATABASE represents the database name with which the SQL procedures were created.
where %DB2PATH% represents the directory in which the instance was created and %DATABASE% represents the database name with which the SQL procedures were created.
Set the database manager configuration parameter KEEPDARI to 'NO' for developing SQL procedures. If an SQL procedure is kept loaded once it is executed, you may have problems dropping and recreating the stored procedure with the same name, as the library cannot be refreshed and the executables cannot be dropped from the filesystem. You will also have problems when you try to rollback the changes or drop the database because the executables cannot be deleted.
See 'Updating the Database Manager Configuration File' in "Chapter 2. Setup" of the 'Application Building Guide' for more information on setting the KEEPDARI parameter.
| Note: | SQL procedures do not support the following data types for parameters:
|
The first paragraph in 'Using the CALL Command' should read:
To use the call command, you must enter the stored procedure name plus any IN or INOUT parameters, as well as '?' as a place-holder for each OUT parameter. For details on the syntax of the CALL command, see 9.8, "CALL".
In "Multi-threaded Applications", the bldmt script file has been revised with different compile options. The new version is in the sqllib/samples/c directory.
In the build scripts, the C++ compiler variable CC has been replaced by aCC, for the HP aC++ compiler. The revised build scripts are in the sqllib/samples/cpp directory.
In "Multi-threaded Applications", the bldmt script file has been revised with different compile options. The new version is in the sqllib/samples/cpp directory.
Libraries need to be linked with the -shared option to build stored procedures and user-defined functions. In the sqllib/samples directory, the makefile, the build scripts bldsrv, and bldudf have been updated to include this option, as in the following link step from bldsrv:
c++ -shared -G -o $1 $1.o -L$DB2PATH/lib -ldb2
Problems with executing C/C++ Applications and running SQL Procedures on Solaris
When using the Sun WorkShop Compiler C/C++, if you experience problems with your executable where you receive errors like the following:
you may be experiencing a problem that the compiler does not produce valid executables when linking with libdb2.so. One suggestion to fix this is to add the following compiler option to your compile and link commands:
-xarch=v8plusa
for example, when compiling the sample application, dynamic.sqc:
embprep dynamic sample
embprep utilemb sample
cc -c utilemb.c -xarch=v8plusa -I/export/home/db2inst1/sqllib/include
cc -o dynamic dynamic.c utilemb.o -xarch=v8plusa -I/export/home/db2inst1/sqllib/include \
-L/export/home/db2inst1/sqllib/lib -R/export/home/db2inst1/sqllib/lib -l db2
Notes:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="cc -# -Kpic -xarch=v8plusa -I$HOME/sqllib/include \
SQLROUTINE_FILENAME.c -G -o SQLROUTINE_FILENAME -L$HOME/sqllib/lib -R$HOME/sqllib/lib -ldb2
| Note: | This updates the section "VisualAge C++ Version 4.0" in "Chapter 6. Building AIX Applications". That section contains information common to AIX, OS/2, and Windows 32-bit operating systems. |
For OS/2 and Windows, use the set command instead of the export command. For example, set CLI=tbinfo.
In "DB2 CLI Applications", subsection "Building and Running Embedded SQL Applications", for OS/2 and Windows, the cliapi.icc file must be used instead of the cli.icc file, because embedded SQL applications need the db2api.lib library linked in by cliapi.icc.