Important: Please read the disclaimer before reading this article.
Historically, using stored procedures in DB2 has required a separately installed (and sometimes costly) C compiler. The solution outlined in this document describes how to install and use the GNU C Compiler (GCC), a free open-source C compiler.
DB2© Universal Database#x2122 stored procedures are written in SQL Procedural Language (SQLPL). When DB2 UDB compiles a stored procedure, it first converts the SQLPL to C, and then compiles the resulting C program into a DLL (dynamic link library). When it's time to run the procedure, DB2 loads the DLL into memory, and calls the main entry point of the DLL.
There are several steps to this procedure:
- Installing the GCC compiler and its associated utilities.
- Creating a batch script with which DB2 will compile your stored procedures.
- Creating helper files needed by the compiler and linker.
- Configuring DB2 to use the new files and scripts.
- Testing the installation.
Depending on your connection speed, the entire procedure should take about an hour.
The quickest way to install GCC is to install the complete Cygwin environment on your machine. Cygwin is a Linux-like environment for Windows. It includes a DLL that provides a substantial subset of UNIX functionality and a set of tools that live on top of this DLL. After Cygwin is installed, you can generally ignore it, though if you're a command-line fan, you'll find your life much improved.
Be sure you have a reasonably fast Internet connection. The GCC compiler and associated utilities comprise about 50 megabytes. Also be sure you know whether your connection requires a proxy server. You can determine this from your Internet Explorer options: Tools -> Internet Options -> Connections -> LAN Settings.
Be sure to install this program under your Administrator account, and choose the install for "All Users."
Point your Internet Browser at www.cygwin.com. In the middle of the screen is a black and green "C" logo, with the caption "Install or update now." Clicking on this link will download a small (250KB) program called setup.exe. This setup program will orchestrate the installation or update of the Cygwin environment on your machine.
Run the setup program
When you've downloaded setup.exe to a known place, for example, your Desktop, run it.
It will open the setup wizard, which looks like this:
Figure 1. Setup wizard for Cygwin environment
Click the Next button.
Choose a download location
On this page, choose "Install from Internet."
Figure 2. Choose installation type
Click the Next button.
Choose an installation directory
On this page, select the directory from which the Cygwin programs will execute.
Set the rest of the options as shown in Figure 3.
Figure 3. Choose an installation directory
Click the Next button.
Choose a temporary installation directory
On this page (Figure 4), select a temporary directory for the installation packages. When the Cygwin installation is complete, you can remove this directory and its contents.
Note that the setup program remembers this directory, and during an update will download only the versions of the packages in this directory that are more recent. If you delete this directory and reinstall, setup will reinstall everything you've selected despite the fact that the installed programs from these packages already exist in the running directory.
Figure 4. Choose a temporary installation directory
Click the Next button.
Specify your Internet connection
On this page (Figure 5), specify your Internet connection. If you have a proxy server, select "Use IE5 Settings," or get the server name from your browser options, select Use Proxy, and enter the host name and port number of the proxy server.
Figure 5. Choose connection type
Click the Next button.
Choose a download site
On this page (Figure 6), select a download site. As these sites are volunteer mirrors, picking a download site is a bit of a crapshoot; usually they're up, sometimes not. In general, try to pick one near you.
Figure 6. Choose a download site
Click the Next button.
Select packages to install
On this page, you will select the packages to be installed. By default, GCC is not installed with the base Cygwin packages, so you must change the defaults.
Move your mouse to the plus sign (+) next to Devel, and click it to expand the Devel category.
Figure 7. Choose packages to install
You will see dozens of entries under Devel, only three of which you need, in the following order:
Not all of the default base packages are needed to run GCC, but they're not very large, so for simplicity, just install them.
Note that it's quite easy to select the wrong packages or the wrong versions, especially if you're re-installing, as the cycle buttons in the selection window are non-standard. Taking extra care in this step is all we can suggest, especially with the multiple GCC versions.
To select the three packages, keep clicking on each two-headed arrow in the New column until the version number for each package shows. Depending on what's already installed on your machine, and what versions the mirror has available, the column entry will cycle among various icons:
If there is more than one version available, choose the one with the highest number. To download the binary program files, be sure the box is checked in the "Bin" column.
Figure 9. Download binary files
Complete the download
When you've finished selecting the three packages, Cygwin will access the selected mirror host, and download those three, plus the default base packages. This will take several minutes depending on your connection speed, and there will be lots of popup windows opening and closing during the installation steps.
Because the mirror sites that host the Cygwin installation are volunteer-based, they are sometimes unavailable or overloaded. If you find that the mirror you selected does not respond, and you press Cancel, you will find that the entire setup program shuts down.
Restarting the install and picking a different mirror is the only recourse; this can be very frustrating. Fortunately, the setup program remembers most of what you did the last time, so the defaults are usually sensible. You must be careful to reselect the correct packages, as these are not selected for you on subsequent installs.
The setup program will then ask whether to create a Cygwin shortcut on your desktop or Start Menu. This shortcut starts the Cygwin shell (called bash), which is not strictly necessary for our intended DB2 usage, but is useful to test our installation. So allow setup to create at least the desktop shortcut. We will use it later in the installation.
Finally, click the Finish button. We'll test the results of our installation later.
Figure 10. Completing installation
Set system environment variables
When the installation is finished, you must create or modify two System (not User) environment variables: PATH and DB2PATH. The PATH variable always exists, but you may have to create the DB2PATH variable.
IMPORTANT: Reboot after this step to ensure that all new processes inherit the new PATH and DB2PATH settings.
PATH: Because both GCC and DLLWRAP call other Cygwin utilities, it's essential that their bin directory is on the PATH. Append the Cygwin bin directory (
c:\cygwin\bin in our example) to your System PATH environment variable as shown here.
Figure 11. Appending to the System PATH environment variable
DB2PATH: The DB2PATH variable can be set in either of two ways: either in the global environment, or locally in the batch file. If you have no spaces in your DB2 path, for example,
c:\ibm\sqllib, then setting it in the batch file will work just fine.
If you do have spaces in your DB2 path, for example
c:\program files\ibm\sqllib, then different versions of GCC will have various problems finding the files. GCC sometimes interprets the text before the space and the text after the space as two distinct parameters.
The only reliable way to fix this is to set the DB2PATH environment variable in your global environment and do not use the setlocal and set statements in the batch file. Apparently, GCC parses environment variables differently from command line parameters; it seems to have no trouble constructing paths with spaces in them if the spaces come from an environment variable.
Figure 12. The DB2PATH environment variable
The compilation script (the most important step)
This section is the main point of this article. You will create a compilation script that tells DB2 how to compile your stored procedures with GCC. The How it works section contains detailed information about each step.
Create the compilation script file
Create a new file in some directory, or create a new directory. In our example, we'll create a file called db2gcc.bat in the c:\bin directory. You can put the file anywhere you want, but be sure the location is correct in the db2set command, which we describe in the next section.
If you're reading this in soft-copy, and you're careful to keep the
dllwrap command all on one line, you can cut and paste the text below directly into a text editor.
Listing 1. The
setlocal REM Set the DB2PATH variable to <strong>your</strong> DB2 base path, e.g., c:\ibm\sqllib REM Set CYGWIN to <strong>your</strong> top-level CYGWIN directory. set DB2PATH=c:\ibm\sqllib set CYGWIN=c:\bin\cygwin gcc -c -g -mno-cygwin -I%DB2PATH%\include -I%CYGWIN%\usr\include %1.c -o %1.o sed -e s/\_// %1.def > %1_m.def dllwrap -mno-cygwin -target i386-mingw32 -o %1.dll %1.o %DB2PATH%\lib\db2api.lib --def %1_m.def
Inform DB2 where the script file is
Type the following command in a windows command prompt:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="c:\bin\db2gcc.bat SQLROUTINE_FILENAME"
Do this only once. Note that there is a space after the db2set command, and after the pathname; there are no other spaces in this command; also note where the underscores and quotes are.
The file path is an absolute path to the newly created
How it works
When DB2 compiles your stored procedure into C, it generates a single .C file with a name generated from a random number; the file is typically called
Pnnnnnnn.C, where the n's are digits. When this file is generated, DB2 stores its name into an environment variable called SQLROUTINE_FILENAME.
DB2 also generates a corresponding
.def file that names the entry point in the C program; the entry point is analogous to the "main" procedure in a C program.
To compile the stored procedure, DB2 invokes the
db2gcc.bat file, passing the file name from the environment variable as the first input parameter to the batch file (%1), without the .C extension. The batch script appends the required file extensions (c, o, dll, def) and GCC compiles the C file into an object (.o) file. Then dllwrap calls the linker (ld), which combines the object file, plus the DB2 runtime libraries, plus the generated
.def file, and produces a DLL (dynamic link library), which will ultimately be loaded by the DB2 server and run.
Please note several things about this batch file:
- This batch file is called after DB2 has translated your stored procedure into C.
- The DB2PATH environment variable must be set to the DB2 base directory on your machine; this path is used to find the DB2 include files and the linked libraries. You can do this either in the batch file, or globally via the system environment variables. See Set system environment variables section.
- The dllwrap command must be typed entirely on a single line.
The function of each command and parameter is as follows:
- setlocal prevents the actions of the set commands from modifying your global command-line environment.
- set creates local environment variables containing the paths to the DB2 include files (db2\include) the DB2 object libraries (db2\lib)and the Cygwin include files (cygwin\usr\include).
- gcc compiles the C file into an object file
- -c means compile only (do not link)
- -g means include debug information
- -mno-cygwin means "do not generate code for the Cygwin environment"
- -I indicates the path to the DB2 include header files
- %1.c will be the name of the generated C file
- %1.o will be the name of the compiled object file.
- sed is the cygwin "stream editor." The sed command removes any underscore characters from the generated entry point names in the .def file. This step ensures that the compilation process works properly under both DB2 version 7.2 and version 8.1. The "s" command substitutes the empty string for any underscores found in the file.
- dllwrap creates the DLL file from the object file, the DB2 link libraries, and the .def file. It actually calls the linker (ld) and dlltool several times each to create the final DLL.
- -target indicates the target machine architecture (386) and the runtime libraries (mingw32 = minimal GNU under Windows 32). The mingw32 libraries are a thin layer over the existing MSVCRTnn.DLL library (usually found in the \windows or \winnt directory). Thus, it uses the C runtime library available on every Windows system.
- %1.def is the name of the file that defines the entry point for the compiled procedure.
- %1_m.def is the name of the file generated by the sed command.
Testing the installation with DB2 UDB
Testing your GCC installation
To test the GCC installation, run the Cygwin shell and type (note: two dashes):
You should see output that looks like this:
gcc (GCC) 3.2 20020927 (prerelease)
Create a sample procedure.
In this sample file, called
sample.clp, exclamation points delimit separate DB2 commands.
Note that the connect command syntax is:
connect to <database> user <id> using <password>
You must adjust this for your installation.
Listing 2. The
connect to sample user db2admin USING somepassword! drop table result! create table result (text VARCHAR(80))! drop procedure TestProcedure! create procedure TestProcedure () BEGIN insert into result (text) values ('If you can read this, it worked.'); END! call TestProcedure! select text from result! terminate!
Compile the sample procedure
This command is used only to test your setup. In practice, DB2 automatically invokes a similar command, which processes the generated C file.
Open a command shell, either Windows or Cygwin. Type the command
db2cmd to create a DB2 command window; this will open another command shell.
The following command compiles the sample procedure with the db2gcc.bat script, which was configured in the db2set command. Be sure to run this command in the same directory where you created the
db2 -td! -v -fsample.clp
The function of each command parameter is as follows:
- db2 invokes the DB2 command processor
- -td! sets the exclamation point (!) as the statement termination character, which should match whatever delimiter you used in the sample.clp file.
- -v tells the command line processor to echo the command text to standard output.
- -fsample.clp names the file that DB2 should compile. Note that there's no space after the -f parameter.
Upon successful completion of the compile and run, check that there are no error messages and that the table results are:
If you can read this, it worked.
Listing 3. Compiling and running the sample procedure
C:\bin\db2\FUNCTION\ROUTINE><strong>db2 -td! -v -fsample.clp</strong> connect to sample user db2admin USING <strong>Database Connection Information Database server = DB2/NT 8.1.0 SQL authorization ID = DB2ADMIN Local database alias = SAMPLE</strong> drop table result <strong>DB20000I The SQL command completed successfully.</strong> create table result (text VARCHAR(80)) <strong>DB20000I The SQL command completed successfully.</strong> drop procedure TestProcedure <strong>DB20000I The SQL command completed successfully.</strong> create procedure TestProcedure() BEGIN insert into result (text) values ('If you can read this, it worked.'); END <strong>DB20000I The SQL command completed successfully.</strong> call TestProcedure <strong>Return Status = 0</strong> select text from result <strong>TEXT </strong> -------------------------------------------------------------------------- <strong>If you can read this, it worked. 1 record(s) selected.</strong> terminate <strong>DB20000I The TERMINATE command completed successfully.</strong>
That's it. Compiling a stored procedure should now invoke the db2gcc batch file, and the compiled procedure should run properly as a DLL under the DB2 process. This will work from the db2cmd command prompt, or from the DB2 Development Center GUI, or as a Relationship in the Websphere® Business Integration System Manager.
The configuration recommendations contained in this article are not officially supported by IBM and were derived under specific operating and environmental conditions. While the information has been reviewed for accuracy under the given conditions, the results obtained in your operating environments may vary significantly. Accordingly, IBM does not provide any representations, assurances, guarantees, or warranties regarding results or performance.
Thanks to Garfield Lewis for figuring out how to use dllwrap to get around the DLL relocation limitations of GCC. Thanks to Kyle Brown, Gustavo Arocena, Paul Yip, and Maxime Tiran for answering my endless questions, and to Juraj Suja for help with the Cygwin screenshots. A special thanks to my manager, Tom Reed, for giving me the time and motivation to investigate this solution.
|sample-db2gcc.bat||The compilation script||The DB2PATH and CYGWIN statements must be changed by the user|
|sample-db2set.bat||Example of how to use db2set||The file name given must match the one in "Create the compilation script file"|
|standard.def||The standard export file||This file always stays the same.|
|sample.clp||Sample stored procedure||The database name, userid and password must be changed by the user.|
|sample-test.clp||Example of how to run the procedure||The file name must match the one in "Create a sample procedure"|
|Code sample||haungssamples.zip ( HTTP | FTP )||3K|