Application Building Guide

IBM VisualAge C++ Version 3.5

This section contains the following topics:

• DB2 CLI Applications
• DB2 CLI Stored Procedures
• DB2 API and Embedded SQL Applications
• Embedded SQL Stored Procedures
• User-Defined Functions (UDFs)

Note:

The VisualAge C++ compiler is used for both C and C++ sample programs supplied in the %DB2PATH%\samples\c and %DB2PATH%\samples\cpp directories. The same batch files have been placed in both these directories. They contain commands to accept either a C or C++ source file, depending on the file extension.

DB2 CLI Applications

The batch file bldvcli.bat, in %DB2PATH%\samples\cli, contains the commands to build a DB2 CLI program in IBM VisualAge C++.

The parameter, %1, specifies the name of your source file.

This is the only required parameter for CLI programs that do not contain embedded SQL. Building embedded SQL programs requires a connection to the database so three optional parameters are also provided: the second parameter, %2, specifies the name of the database to which you want to connect; the third parameter, %3, specifies the user ID for the database, and %4 specifies the password.

If the program contains embedded SQL, indicated by the .sqc or the .sqx extension, then the embprep batch file is called to precompile the program, producing a program file with either a .c or .cxx extension, respectively.

@echo off
rem  bldvcli batch file - Windows 32-bit Operating Systems
rem  Builds a CLI program with IBM VisualAge C++.
rem  Usage: bldvcli prog_name
 
if exist "%1.sqc" call embprep %1 %2 %3 %4 
if exist "%1.sqx" call embprep %1 %2 %3 %4
 
rem Compile the error-checking utility.
icc -c -Ti -W1 /I"%DB2PATH%\include" utilcli.c
 
rem  Compile the program.
if exist "%1.sqx" goto cpp
icc -c -Ti -W1 /I"%DB2PATH%\include" %1.c
goto link_step
:cpp
icc -c -Ti -W1 /I"%DB2PATH%\include" %1.cxx 
 
rem  Link the program.
:link_step
ilink /MAP /DEBUG /ST:32000 /PM:VIO %1.obj utilcli.obj db2cli.lib
@echo on

Compile and Link Options for bldvcli
Compile Options: icc The IBM VisualAge C++ compiler. -c Perform compile only; no link. This book assumes that compile and link are separate steps. -Ti Generate debugger information. -W1 Output warning, error, and severe and unrecoverable error messages.
Link Options: ilink Use the resource linker to link edit. /MAP Generate a map file. /DEBUG Include debugging information. /ST:32000 Specify a stack size of at least 32 000. /PM:VIO Enable the program to run in a window or in a full screen. %1.obj Include the object file. utilcli.obj Include the utility object file for error checking. db2cli.lib Link with the DB2 CLI library. Refer to your compiler documentation for additional compiler options.

To build the sample program tbinfo from the source file tbinfo.c , enter:

 
   bldvcli tbinfo

The result is an executable file tbinfo. You can run the executable file by entering the executable name:

   tbinfo

Building and Running Embedded SQL Applications

There are three ways to build the embedded SQL application, dbusemx, from the source file dbusemx.sqc :

1. If connecting to the sample database on the same instance, enter:

      bldvcli dbusemx
   

2. If connecting to another database on the same instance, also enter the database name:

      bldvcli dbusemx database
   

3. If connecting to a database on another instance, also enter the user ID and password of the database instance:

      bldvcli dbusemx database userid password
   

The result is an executable file, dbusemx.

There are three ways to run this embedded SQL application:

1. If accessing the sample database on the same instance, simply enter the executable name:

      dbusemx
   

2. If accessing another database on the same instance, enter the executable name and the database name:

      dbusemx database
   

3. If accessing a database on another instance, enter the executable name, database name, and user ID and password of the database instance:

      dbusemx database userid password
   

DB2 CLI Applications with DB2 APIs

DB2 includes CLI sample programs that use DB2 APIs to create and drop a database in order to demonstrate using CLI functions on more than one database. The descriptions of the CLI sample programs in Table 7 indicates the samples that use DB2 APIs.

The script file bldvapi in sqllib/samples/cli contains the commands to build a DB2 CLI program with DB2 APIs. This file compiles and links in the utilapi utility file, which contains the DB2 APIs to create and drop a database. This is the only difference between this file and the bldvcli batch file. Please see "DB2 CLI Applications" for the compile and link options common to both bldvapi and bldvcli.

To build the sample program dbmconn from the source file dbmconn.c , enter:

 
   bldvapi dbmconn

The result is an executable file dbmconn. You can run the executable file by entering the executable name:

   dbmconn

DB2 CLI Stored Procedures

The batch file bldvclis.bat, in %DB2PATH%\samples\cli, contains the commands to build a CLI stored procedure. The batch file builds the stored procedure into a DLL on the server.

The parameter, %1, specifies the name of your source file. The batch file uses the source file name, %1, for the DLL name.

@echo off
rem bldvclis.bat file - Windows 32-bit Operating Systems
rem Builds a CLI stored procedure using the IBM VisualAge C++ compiler
rem Usage: bldvclis prog_name
 
if "%1" == "" goto error
 
rem Compile the program.
if exist "%1.cxx" goto cpp
icc -c+ -Ti -Ge- -Gm+ -W1 %1.c  utilcli.c
goto link_step
:cpp
icc -c+ -Ti -Ge- -Gm+ -W1 %1.cxx  utilcli.c
 
:link_step 
rem Import the library and create an export file.
rem The function name in the .def file must be decorated to be consistent 
rem with the function name in the .map file.  Typically, this is done by
rem prepending "_" and appending "@" and the number of bytes of arguments, 
rem as in: "@16". In spserverva.def, for example, the IBM VisualAge C++
rem compiler requires "EXPORTS _outlanguage@16" and not "EXPORTS outlanguage".
ilib /GI %1va.def
 
rem Link the program and produce a DLL.
ilink /ST:64000 /PM:VIO /MAP /DLL %1.obj utilcli.obj %1va.exp db2cli.lib
 
rem Copy the stored procedure DLL to the 'function' directory
copy %1.dll "%DB2PATH%\function"
 
goto exit
:error
echo Usage: bldvclis prog_name 
:exit
@echo on

Compile and Link Options for bldvclis
Compile Options: icc The IBM VisualAge C++ compiler. -c+ Perform compile only; no link. This batch file has separate compile and link steps. -Ti Generate debugger information. -Ge- Build a .DLL file. Use the version of the run-time library that is statically linked. -Gm+ Link with multi-tasking libraries. -W1 Output warning, error, and severe and unrecoverable error messages.
Link Options: ilink Use the resource linker to link edit. /ST:64000 Specify a stack size of at least 64 000. /PM:VIO Enable the program to run in a window or in a full screen. /MAP Generate a map file. /DLL Build a .DLL file. %1.obj Include the object file. %1.exp Include the VisualAge export file. db2cli.lib Link with the DB2 CLI library. Refer to your compiler documentation for additional compiler options.

To build the spserver stored procedure from the source file spserver.c , enter:

   bldvclis spserver

The batch file uses the module definition file, spserverva.def, contained in the same directory as the CLI sample programs, to build the stored procedure. The batch file copies the stored procedure DLL, spserver.dll, to the server in the path %DB2PATH%\function.

Next, catalog the stored procedures by running the spcreate.db2 script on the server. First, connect to the database:

   db2 connect to sample

If the stored procedures were previously cataloged, you can drop them with this command:

   db2 -td@ -vf spdrop.db2

Then catalog them with this command:

   db2 -td@ -vf spcreate.db2

Then, stop and restart the database to allow the new shared library to be recognized. If necessary, set the file mode for the shared library so the DB2 instance can access it.

Once you build the stored procedure spserver, you can build the CLI client application spclient that calls the stored procedure.

You can build spclient by using the batch file, bldvcli. Refer to "DB2 CLI Applications" for details.

To call the stored procedure, run the sample client application by entering:

spclient database userid password

where

database

Is the name of the database to which you want to connect. The name could be sample, or its alias, or another database name.

userid

Is a valid user ID.

password

Is a valid password.

The client application accesses the stored procedure library, spserver, and executes a number of stored procedure functions on the server database. The output is returned to the client application.

DB2 API and Embedded SQL Applications

The batch file bldvapp.bat, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build a DB2 application program.

The first parameter, %1, specifies the name of your source file. This is the only required parameter for programs that do not contain embedded SQL. Building embedded SQL programs requires a connection to the database so three optional parameters are also provided: the second parameter, %2, specifies the name of the database to which you want to connect; the third parameter, %3, specifies the user ID for the database, and %4 specifies the password.

For an embedded SQL program, bldvapp passes the parameters to the precompile and bind batch file, embprep. If no database name is supplied, the default sample database is used. The user ID and password parameters are only needed if the instance where the program is built is different from the instance where the database is located.

@echo off
rem bldvapp.bat -- Windows 32-bit operating systems
rem Builds a VisualAge C++ application program
rem Usage: bldvapp prog_name [ db_name [  userid password ]]
 
if exist "%1.sqx" goto embedded
if exist "%1.sqc" goto embedded
goto non_embedded
 
:embedded
rem Precompile and bind the program.
call embprep %1 %2 %3 %4
rem  Compile the program.
if exist "%1.cxx" goto cpp_emb
icc -c -Ti -W1 %1.c utilemb.c
goto link_embedded
:cpp_emb
icc -c -Ti -W1 %1.cxx utilemb.cxx
rem Link the program.
:link_embedded
ilink /MAP /DEBUG /ST:32000 /PM:VIO %1.obj utilemb.obj db2api.lib
goto exit
 
:non_embedded
rem  Compile the program.
if exist "%1.cxx" goto cpp_non
icc -c -Ti -W1 %1.c utilapi.c
goto link_non_embedded
:cpp_non
icc -c -Ti -W1 %1.cxx utilapi.cxx
rem Link the program.
:link_non_embedded
ilink /MAP /DEBUG /ST:32000 /PM:VIO %1.obj utilapi.obj db2api.lib
:exit
@echo on

To build the DB2 API non-embedded SQL sample program, client, from either the source file client.c , in %DB2PATH%\samples\c, or from the source file client.cxx , in %DB2PATH%\samples\cpp, enter:

   bldvapp client

The result is an executable file, client.exe. You can run the executable file by entering the executable name (without the extension) on the command line:

   client

Building and Running Embedded SQL Applications

There are three ways to build the embedded SQL application, updat, from the C source file updat.sqc in %DB2PATH%\samples\c, or from the C++ source file updat.sqx in %DB2PATH%\samples\cpp:

1. If connecting to the sample database on the same instance, enter:

      bldvapp updat
   

2. If connecting to another database on the same instance, also enter the database name:

      bldvapp updat database
   

3. If connecting to a database on another instance, also enter the user ID and password of the database instance:

      bldvapp updat database userid password
   

The result is an executable file updat.exe.

There are three ways to run this embedded SQL application:

1. If accessing the sample database on the same instance, simply enter the executable name:

      updat
   

2. If accessing another database on the same instance, enter the executable name and the database name:

      updat database
   

3. If accessing a database on another instance, enter the executable name, database name, and user ID and password of the database instance:

      updat database userid password
   

Embedded SQL Stored Procedures

The batch file bldvsrv.bat, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build an embedded SQL stored procedure. The batch file compiles the stored procedure into a DLL, and stores it on the server.

The first parameter, %1, specifies the name of your source file. The second parameter, %2, specifies the name of the database to which you want to connect. Since the stored procedure must be built on the same instance where the database resides, there are no parameters for user ID and password.

Only the first parameter, source file name, is required. Database name is optional. If no database name is supplied, the program uses the default sample database.

The batch file uses the source file name, %1, for the DLL name.

@echo off
rem bldvsrv.bat -- Windows 32-bit operating systems
rem Builds a VisualAge C++ stored procedure
rem Usage: bldvsrv prog_name [ db_name ]
 
rem Precompile and bind the program.
call embprep %1 %2
 
rem Compile the program.
if exist "%1.cxx" goto cpp
icc -c+ -Ti -Ge- -Gm+ -W1 %1.c
goto link_step
:cpp
icc -c+ -Ti -Ge- -Gm+ -W1 %1.cxx
 
:link_step
rem Import the library and create a definition file.
rem The function name in the .def file must be decorated to be consistent
rem with the function name in the .map file. Typically, this is done by
rem prepending "_" and appending "@" and the number of bytes of arguments,
rem for example, "@16". In spserverva.def, the IBM VisualAge C++ compiler requires
rem "EXPORTS _outlanguage@16" and not "EXPORTS outlanguage".
ilib /GI %1va.def
 
rem Link the program and produce a DLL.
ilink /ST:64000 /PM:VIO /MAP /DLL %1.obj %1va.exp db2api.lib
 
rem Copy the Stored Procedure DLL to the 'function' directory.
copy %1.dll "%DB2PATH%\function"
@echo on

Compile and Link Options for bldvsrv
Compile Options: icc The IBM VisualAge C++ compiler. -c+ Perform compile only; no link. This batch file has separate compile and link steps. -Ti Generate debugger information. -Ge- Build a .DLL file. Use the version of the run-time library that is statically linked. -Gm+ Link with multi-tasking libraries. -W1 Output warning, error, and severe and unrecoverable error messages.
Link Options: ilink Use the resource linker to link edit. /ST:64000 Specify a stack size of least of 64 000. /PM:VIO Enable the program to run in a window or full screen. /MAP Generate a MAP file. /DLL Build a .DLL file. %1.obj Include the object file. %1va.exp VisualAge export file. db2api.lib Link with the DB2 library. Refer to your compiler documentation for additional compiler options.

To build the spserver stored procedure DLL from either the C source file, spserver.sqc , or the C++ source file, spserver.sqx , enter:

   bldvsrv spserver

If connecting to another database, also enter the database name:

    bldmsrv spserver database

The batch file uses the module definition file spserverva.def, contained in the same directory as the sample programs, to build the stored procedure. The batch file copies the stored procedure DLL, spserver.dll, to the server in the path %DB2PATH%\function.

Next, catalog the stored procedures by running the spcreate.db2 script on the server. First, connect to the database:

   db2 connect to sample

If the stored procedures were previously cataloged, you can drop them with this command:

   db2 -td@ -vf spdrop.db2

Then catalog them with this command:

   db2 -td@ -vf spcreate.db2

Then, stop and restart the database to allow the new shared library to be recognized. If necessary, set the file mode for the shared library so the DB2 instance can access it.

Once you build the stored procedure DLL, spserver, you can build the client application spclient that calls it.

You can build spclient by using the script file, bldvapp. Refer to "DB2 API and Embedded SQL Applications" for details.

To call the stored procedure, run the sample client application by entering:

spclient database userid password

where

database

Is the name of the database to which you want to connect. The name could be sample, or its alias, or another database name.

userid

Is a valid user ID.

password

Is a valid password.

The client application accesses the stored procedure DLL, spserver, and executes a number of stored procedure functions on the server database. The output is returned to the client application.

User-Defined Functions (UDFs)

The batch file bldvudf, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build a UDF.

UDFs cannot contain embedded SQL statements. Therefore, to build a UDF program, you do not connect to a database, or precompile and bind the program.

The parameter, %1, specifies the name of your source file. The batch file uses the source file name, %1, for the DLL name.

@echo off
rem bldvudf.bat -- Windows 32-bit operating systems 
rem Builds a VisualAge C++ user-defined function (UDF)
rem Usage: bldvudf program_name
 
if "%1" == "" goto error
 
rem Compile the program.
if exist "%1.cxx" goto cpp
icc -Ti -c+ -Ge- -Gm+ -W1 %1.c
goto link_step
:cpp
icc -Ti -c+ -Ge- -Gm+ -W1 %1.cxx
 
:link_step
rem Generate an import library and export file using a definition file.
rem Function(s) in the .def file are prepended with an underscore, and
rem appended with the @ sign and number of bytes of arguments (in decimal).
rem Parameters of less than four bytes are rounded up to four bytes.
rem Structure size is rounded up to a multiple of four bytes.
rem For example, function fred prototyped as: "int fred(int, int, short);"
rem would appear as:  "_fred@12" in the .def file.
rem These decorated function names can also be found in %1.map
rem after running the following ilink command without %1va.exp.
ilib /gi %1va.def
 
rem Link the program to a dynamic link library
ilink /ST:64000 /PM:VIO /MAP /DLL %1.obj %1va.exp db2api.lib db2apie.lib
 
rem Copy the UDF DLL to the 'function' directory.
copy %1.dll "%DB2PATH%\function"
 
goto exit
:error
echo Usage: bldvudf prog_name
:exit
@echo on

Compile and Link Options for bldvudf
Compile Options: icc The IBM VisualAge C++ compiler. -Ti Generate debugger information. -c+ Perform compile only; no link. This book assumes that compile and link are separate steps. -Ge- Build a .DLL file. Use the version of the run-time library that is statically linked. -Gm+ Link with multi-tasking libraries. -W1 Output warning, error, and severe and unrecoverable error messages.
Link Options: ilink Use the resource linker to link edit. /ST:64000 Specify a stack size of at least 64000. /PM:VIO Enable the program to run in a window or a full screen. /MAP Generate a MAP file. /DLL Build a .DLL file. %1.obj Include the object file. %1va.exp Include the VisualAge export file. db2api.lib Link with the DB2 library. db2apie.lib Link with the DB2 API Engine library. Refer to your compiler documentation for additional compiler options.

To build the user-defined function udfsrv from the source file udf.c , enter:

   bldvudf udfsrv

The batch file uses the module definition file udfsrv.def, contained in the same directory as the sample programs, to build the user-defined function. The batch file copies the user-defined function DLL, udfsrv.dll, to the server in the path %DB2PATH%\function.

Once you build udfsrv, you can build the client application, udfcli, that calls it. DB2 CLI, as well as embedded SQL C and C++ versions of this program are provided.

You can build the DB2 CLI udfcli program from the udfcli.c source file in %DB2PATH%\samples\cli using the batch file bldvcli. Refer to "DB2 CLI Applications" for details.

You can build the embedded SQL C udfcli program from the udfcli.sqc source file in %DB2PATH%\samples\c using the batch file bldvapp. Refer to "DB2 API and Embedded SQL Applications" for details.

You can build the embedded SQL C++ udfcli program from the udfcli.sqx source file in %DB2PATH%\samples\cpp using the batch file bldvapp. Refer to "DB2 API and Embedded SQL Applications" for details.

To run the UDF, enter:

   udfcli

The calling application calls the ScalarUDF function from the udfsrv DLL.