Install the complete cygwin Environment

Original ENGLISH

Compile the DB2 UDB stored procedure with GCC in Windows

Jim haungs IBM Websphere Business Integration Department Burlingame, CA October 2003 2003 IBM Copyright Important: Read the disclaimer of waiver before reading this article.

	Content
Introduction
Install gcc
Edit the script (the most important step)
How it works
Test the installation of DB2 UDB
Summary
Waiver right statement
Thank you
Download

Introduction

In the past, using stored procedures in DB2 required additional installation of the C compiler (sometimes expensive ). The solution provided in this article describes how to install and use the gnu c compiler (GCC), a free open source C compiler.

DB2
The UDB stored procedure is written in the SQL procedure language (SQL PL. When DB2 UDB compiles a stored procedure, it first converts SQL PL to C
Language, and then compile the converted C program into a DLL (dynamic link library ). During the running process, DB2 loads the DLL into the memory and calls the main entry point of the DLL.

This process involves the following steps:

1. Install the GCC compiler and related utilities.
2. Create a batchcompute script used by the DB2 compilation stored procedure.
3. Create a Help file required by the compiler and linker.
4. Configure DB2 to use new files and scripts.
5. Install and test.

Based on your connection speed, the entire process will last for about an hour.

InstallGcc

Security
The quickest way to install GCC is to install the complete cygwin environment on your machine. Cygwin is similar to Linux on Windows.
Environment. It includes a DLL that provides a basic subset of UNIX functionality and a set of tools above it. Install cygwin
You can ignore it. Even if you are a fan of command line, you can still find that you are more comfortable.

Make sure that you have a fast Internet connection. The GCC compiler and related utilities are about 50 MB in size. Check whether your connection requires a proxy server. This can be done through your Internet browser options:Tools> Internet
Option-> connection-> LAN SettingsTo confirm.

Make sure to install this program under your administrator account and select "all users" for installation.

Direct your Internet browser to www.cygwin.com. In the center of the screen is a black-green "C" logo with the title "install or update
Now .". Click on the link to download a small program setup.exe (250kb ). This installer will guide the cygwin environment installation or update process on your machine.

Run the installer

Download setup.exe to a known location, such as the desktop, and then run it.

It will open the Installation Wizard, as shown in:

Figure 1. Installation Wizard of cygwin Environment

ClickNextButton.

Select Download location

On this page, select "Install from Internet ".

Figure 2. Select the installation type

ClickNextButton.

Select an installation directory

On this page, select the directory where cygwin is executed.

Set the remaining options shown in figure 3.

Figure 3. Select an installation directory

ClickNextButton.

Select a temporary installation directory

On this page (figure 4), select a temporary directory for the installation package. After cygwin is installed, you can delete this directory and its content.

Note that the installer remembers this directory and only downloads the version later than the package in this directory during update. If you delete this directory and reinstall it,SetupAll the content you selected will be reinstalled regardless of whether the package's installer already exists in the running directory.

Figure 4.
Select a temporary installation directory

ClickNextButton.

Specify your Internet connection

On this page (figure 5), specify the Internet connection. If you have a proxy server, select "use ie5 Settings", or obtain the server name from your browser options, select use proxy, and enter the host name and port number of the proxy server.

Figure 5. Select the connection type

ClickNextButton.

Select Download Site

On this page (figure 6), select a download site. Because these sites are obligated images, it is risky to select a download point (usually they are available but sometimes unavailable ). Generally, try the site that is closer to you.

Figure 6. Select a download site

ClickNextButton.

Select a package for Installation

On this page, you will select the package to install. By default, GCC is not installed in the cygwin basic package. Therefore, you must modify the default settings.

Move the mouse overDevelOn the plus sign (+) of the edge, click it to expandDevelClass.

Figure 7. Select a package for Installation

You can see that there are many portals in Devel, and only three of them are what you need. The order is as follows:

1. Binutils
2. Gcc
3. Gcc-mingw

Not all the default basic packages need to run GCC, but they are not very large, so to simplify them, install them.

Note that it is easy to select the wrong package or version, especially when you reinstall it, because the cycle button in the selection window is not standard. In this step, we recommend that you be more careful, especially when there are multiple GCC versions.

To select the three packages, click the columnNewUntil the version number of each package is displayed. According to the installed version on your machine and the appropriate version on the image, the inclusion port loops between icons:

If multiple versions are available, select the largest number. To download the binary file, make sure that the check box in the "bin" column is selected.

Figure 9. Download a binary file

Download complete

After three packages are selected, cygwin accesses the selected image host and downloads the three packages and the default basic package. Depending on your connection speed, this will last for several minutes. During installation, many pop-up windows will be opened and closed.

Because it is an obligation to store images installed by cygwin, it is sometimes unavailable or overloaded. If the selected image does not respond, click Cancel (Cancel), Which will terminate the entire installation program.

The only solution is to re-install and select another image, which is frustrating. Fortunately, the installer remembers most of what you did last time, so the default setting is usually used. Be careful when selecting the correct package again, because you will not be able to select the content for later installation.

SetupProgram and then ask whether to create a cygwin shortcut on your desktop or start menu. This shortcut key starts cygwin shell (BASH), which is not required for the usage of DB2 in our plan, but is useful for testing our installation. ThereforeSetupCreate at least one desktop shortcut. We will use it after installation.

Finally, clickFinishButton. We will test the installation result later.

Figure 10. Complete installation

Set system environment variables

When the installation is complete, you must create or modify two system (not a user) environment variables: path and db2path. The PATH variable usually exists, but you must create the db2path variable.

After this step, restart the machine to ensure that all new processes inherit the new path and db2path settings.

Path: Because both GCC and dllwrap call other cygwin utilities, their bin directories must be in path. Run the cygwin bin directory (in our examplec:\\cygwin\\bin) To your system path environment variable, as shown below.

Figure 11. Add to System Path Environment Variable

Db2path:Db2pathYou can set variables in either of the following ways: global environment variables or local batch files. If your DB2 path (suchF:\\SQLLIBOrc:\\ibm\\sqllib.

If your DB2 path (suchc:\\program files\\ibm\\sqllib. GCC sometimes interprets the text before and after spaces as two different parameters.

The only reliable way to solve this problem is to set it in your global environment.Db2pathEnvironment variable, which is not used in batch filesSetlocalAndSetStatement. Obviously, GCC parses environment variables differently from command line parameters. If spaces are from environment variables, there is no problem with spaces in the path.

Figure 12. db2path environment variable

Edit script(The most important step)

This part is the main point of this article. You will create an editing script to show DB2 how to compile the stored procedure with GCC. The part of how it works contains detailed information for each step.

Create and edit a script file

Create a new file or a new directory in a directory. In our example, we will create a file named db2gcc. bat in the C: \ bin directory. You can place the file anywhere you want, but make sure the location is correct in the db2set command, which is described in the next section.

If you read this article on the soft copy, you can cut the following text and paste it into the text editor. Note that you should keep dllwrap on one line.

List 1.c:\\bin\\db2gcc.batFile

setlocal REM Set the DB2PATH variable to your DB2 base path, e.g., c:\\ibm\\sqllib set DB2PATH=c:\\ibm\\sqllib gcc -c -g -mno-cygwin -I%DB2PATH%\\include %1.c -o %1.o dllwrap -mno-cygwin -target i386-mingw32 -o %1.dll %1.o %DB2PATH%\\lib\\db2api.lib --def c:\\bin\\standard.def

Tell DB2 where the script file is

In a Windows command prompt, type the following command:
db2set DB2_SQLROUTINE_COMPILE_COMMAND="c:\\bin\\db2gcc.bat SQLROUTINE_FILENAME"

This is only done once. Note thatDb2setThere is a space after the command and after the path name; there is no other space in the Command; pay attention to the underline and quotation marks.

The file path is newly created.Absolute path of the db2gcc. BAT file.

Create a standard. Def File

This file defines the entry point for export from the generated C process. This entry point is the same for each process. The standard. Def file can be stored anywhere, as long as it is in the batch file.DllwrapCommand path (for example, create and edit the path described in the script file ).

List 2.standard.defFile

EXPORTS pgsjmp@8 pgsjmp=pgsjmp@8

How it works

When DB2 compiles your stored procedure into the C language, it generates a. c file with a random number in the file name. Generally, the file name isPnnnnnnn.CHere N is a number. After the file is generated, DB2 saves the name to the environment variable sqlroutine_filename.

To compile this stored procedure, DB2 callsdb2gcc.bat File to pass the file name without. c extension in the environment variable to the first input parameter (% 1) of the batch file ). Add the required file extension (C, O, DLL) to the batch processing script)
Then, GCC compiles the c file into a target (. O) file. ThenDllwrapCall the linker (LD),
Add the target file to the DB2 Runtime Library andstandard.defFile binding generates a DLL (Dynamic Link Library), which is finally loaded and run by the DB2 server.

Pay attention to the following aspects:

• This batch file is converting your stored procedure to CAfterCalled.
• You must setDb2pathSet the environment variables to the DB2 basic directory on your machine. This file is used to find the DB2 include file and the link library. This can be achieved through batch processing files or global system environment variables. See
  Set system environment variables.
• DllwrapThe command must be entered in one line.

Each Command and parameter of this function is as follows:

• SetlocalPreventSet db2pathCommand to modify your global command line environment.
• SetCreate a local environment variable that contains the DB2 include file (DB2 \ include) and the path of the target database (DB2 \ Lib.
• GccCompile the c file into a target file
• -COnly compile (not link)
• -GContains debugging information.
• -MnO-cygwinIndicates "do not generate code from cygwin Environment"
• -ISpecifies the path of the DB2 include header file
• % 1.cIs the name of the generated C file
• % 1.oIs the name of the compiled target file
• DllwrapFrom the target file, the DB2 link file andStandard. DefFile to create a DLL file. It actually calls the linker several times (LD) AndDlltoolCreate a final DLL each time.
• -Target
  Display the target machine structure (386) and Runtime Library (mingw32 = the smallest GNU in Windows 32 ). The mingw32 library is already in use
  Msvcrtnn. dll library (usually in the \ Windows or \ WINNT directory) on a thin layer. Therefore, it uses
  The C Runtime library available on the system.
• Standard. DefInclude the name of the entry to the exported compilation process. Because every DB2 process has the same name, a standard file is enough.

Test the installation of DB2 UDB

Test your GCC Installation

To test the GCC installation, run cygwin shell and enter (Note: Two dashes ):
gcc --version

The output result is similar to the following:
gcc (GCC) 3.2 20020927 (prerelease)

Create a sample process.

In this example Filesample.clpAs the delimiter of DB2 commands.

Note:ConnectCommand syntax:

connect to <database> user <id> using <password>

You must adjust the parameter value according to your installation.

List 3.sample.clpFile

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!

Sample compilation process

This command is only used to test your installation. In fact, DB2 automatically calls a similar command to process the generated C file.

Open the command shell, windows or cygwin. Enter the commanddb2cmdCreate a DB2 command window. This opens another command shell.

Use the following commandDb2setCommand. Make sure thatsample.clp
Run this command in the same directory of the file.

db2 -td! -v -fsample.clp

The functions of each command are as follows:

• DB2Call the DB2 command processor
• -Td!Set exclamation point (!) As the statement Terminator, this should be consistent with what you use in the sample. CLP file.
• -V
  Tells the command line processor to reflect the command text to the standard output.
• -Fsample. CLPName the file to be compiled by DB2. Note that-FThere is no space after the parameter.

After successful compilation and running, check whether there are any error messages and the table results are as follows:

If you can read this, it worked.

List 4. Sample compilation and running process

C:\\bin\\db2\\FUNCTION\\ROUTINE>db2 -td! -v -fsample.clp connect to sample user db2admin USING Database Connection Information Database server = DB2/NT 8.1.0 SQL authorization ID = DB2ADMIN Local database alias = SAMPLE drop table result DB20000I The SQL command completed successfully. create table result (text VARCHAR(80)) DB20000I The SQL command completed successfully. drop procedure TestProcedure DB20000I The SQL command completed successfully. create procedure TestProcedure() BEGIN insert into result (text) values ('If you can read this, it worked.'); END DB20000I The SQL command completed successfully. call TestProcedure Return Status = 0 select text from result TEXT -------------------------------------------------------------------------- If you can read this, it worked. 1 record(s) selected. terminate DB20000I The TERMINATE command completed successfully.

Summary

That's it. You can callDb2gcc
The batch file is compiled and runs correctly as a DLL in the DB2 process.

Waiver right statement

The configuration recommendations in this article are not officially supported by IBM, but from specific operating systems and environments. When verifying the correctness of information under given conditions, the results obtained in your operation environment may differ greatly. Therefore, IBM does not provide any result or performance representative, warranty, guarantee or authorization.

Thank you

Thanks to Garfield Lewis for solving the problem.Dllwrap
To avoid restrictions on gcc dll relocation. Thanks to Kyle Brown, Gustavo arocena, Paul Yip, and
Maxime Tiran answered my endless questions and thanked Juraj suja for helping to copy the cygwin screen. In addition, I would like to thank Tom, the manager.
Reed gave me the time and motivation to study this problem.

Download

File Name	Purpose	Note
Sample-db2gcc.bat	Edit script	You must modify the db2path statement.
Sample-db2set.bat	Example of how to use db2set	The file name must match the one in "Create and edit Script File ".
Standard. Def	Standard output file	The file is always the same.
Sample. CLP	Sample Stored Procedure	You must modify the database name, user ID, and password.
Sample-test.clp	Example of how to run the process	The file name must be "Create sample process.

Description	File Type	File Size	Download Method
Haungssamples.zip	Zip	3 KB	HTTP

To Homepage

About the author

Jim haungs is a Burlingame lab course developer who develops Websphere Business integration courses. He has been designing and developing software since 1974. He earned a bachelor's degree in computer science from RIT and a Master's degree in software engineering from Wang. Before arriving at IBM, he was in Oracle, Coopers & Lybrand, Lotus, Wang I have worked in the lab and managed a consulting company dedicated to object technology.

DB2, DB2 UDB, IBM, WebSphere

Windows

Other companies