The script file bldxlc, in sqllib/samples/c, contains the commands to build a sample C program.
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. Parameter $3 specifies the user ID for the database, and $4 specifies the password. Only the first parameter, the source file name, is required. Database name, user ID, and password are optional. If no database name is supplied, the program uses the default sample database.
#! /bin/ksh # bldxlc script file # Builds a sample C program containing embedded SQL # Usage: bldxlc <prog_name> [ <db_name> [ < userid> <password> ]] # Connect to a database. if (($# < 2)) then db2 connect to sample elif (($# < 3)) then db2 connect to $2 else db2 connect to $2 user $3 using $4 fi # Precompile the program. db2 prep $1.sqc bindfile # Bind the program to the database. db2 bind $1.bnd # Disconnect from the database. db2 connect reset # Compile the util.c error-checking utility. xlc -I/usr/lpp/db2_05_00/include -c util.c # Compile the program. xlc -I/usr/lpp/db2_05_00/include -c $1.c # Link the program. xlc -o $1 $1.o util.o -ldb2 -L/usr/lpp/db2_05_00/lib |
| Compile and Link Options for bldxlc |
|---|
|
The script file contains the following compile options:
|
|
The script file contains the following link options:
Refer to your compiler documentation for additional compiler options.
|
To build the sample program updat.sqc, do the following:
db2start
bldxlc updat
The result is an executable file updat. You can run the executable file against the SAMPLE database to see how it works by doing the following :
db2start
updat
| Note: | To build C applications that do not contain embedded SQL, you can use the script file bldxlcapi. It contains the same compile and link options as bldxlc, but does not connect, prep, bind, or disconnect from the SAMPLE database. It is used to compile and link the DB2 API sample programs written in C. |
The script file bldxlcsrv, in sqllib/samples/c, contains the commands to build a stored procedure. The script file compiles the stored procedure into a shared library that can be called by a client application.
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. Parameter $3 specifies the user ID for the database, and $4 specifies the password. Only the first parameter, the source file name, is required. Database name, user ID, and password are optional. If no database name is supplied, the program uses the default sample database.
The script file uses the source file name, $1, for the shared library name, and for the main entry point to the shared library.
#! /bin/ksh
# bldxlcsrv script file
# Builds a stored procedure
# Usage: bldxlcsrv <stor_proc_name> [ <db_name> [ <userid> <password> ]]
# Connect to a database.
if (($# < 2))
then
db2 connect to sample
elif (($# < 3))
then
db2 connect to $2
else
db2 connect to $2 user $3 using $4
fi
# Precompile the program.
db2 prep $1.sqc bindfile
# Bind the program to the database.
db2 bind $1.bnd
# Disconnect from the database.
db2 connect reset
# Compile the util.c error-checking utility.
xlc -I/usr/lpp/db2_05_00/include -c util.c
# Compile the program.
xlc -I/usr/lpp/db2_05_00/include -c $1.c
# Link the program using the export file $1.exp,
# creating a shared library called $1 with the default
# entry point $1.
xlc -o $1 $1.o util.o -ldb2 -L/usr/lpp/db2_05_00/lib \
-H512 -T512 -bE:$1.exp -e $1
# Copy the shared library to the sqllib/function subdirectory of the DB2 instance.
# Note: this assumes the user has write permission to this directory.
eval "H=~$DB2INSTANCE"
cp $1 $H/sqllib/function
|
| Compile and Link Options for bldxlcsrv |
|---|
|
The script file contains the following compile options:
|
|
The script file contains the following link options:
Refer to your compiler documentation for additional compiler options.
|
To build the outsrv.sqc stored procedure, do the following:
db2start
bldxlcsrv outsrv
The script file copies the stored procedure to the server in the path sqllib/function to indicate that the stored procedure is fenced. If you want the stored procedure to be unfenced, you must move it to the sqllib/function/unfenced directory. These paths are in the home directory of the DB2 instance.
| Note: | An unfenced stored procedure or UDF runs in the same address space as the database manager and results in increased performance when compared to a fenced stored procedure or UDF, which runs in an address space isolated from the database manager. With unfenced stored procedures or UDFs there is a danger that user code could accidentally or maliciously damage the database control structures. Therefore, you should only run unfenced stored procedures or UDFs when you need to maximize the performance benefits. Ensure these programs are thoroughly tested before running them as unfenced. Refer to the Embedded SQL Programming Guide for more information about fenced and not fenced stored procedures. |
Once you build the stored procedure outsrv, you can build the client application outcli that calls the stored procedure. You can build outcli using the bldxlc script file. Refer to "IBM C" for details.
To run the stored procedure, do the following:
db2start
outcli remote_database userid password
where
The client application passes a variable to the server program outsrv, which gives it a value and then returns the variable to the client application.
This section provides a general discussion about coding stored procedures, and the compiler options you can use.
The Embedded SQL Programming Guide describes how to code your stored procedure. The SQL Reference describes how to invoke your stored procedure at the location of a database using the CALL statement. This section ties how you compile and link your stored procedure to the information you provide in the CALL statement.
When you compile and link your program, you can identify functions in two ways:
For example, you can specify the following in the link step:
-e modify
This indicates that the default entry point for the linked library is the function modify.
If you are linking a library mystored in a directory /u/mydir/procs, and you want to use the default entry point modify as specified above, code your CALL statement as follows:
CALL '/u/mydir/procs/mystored'
The library mystored is loaded into memory, and the function modify is picked up by DB2 as the default entry point, and is executed.
Generally speaking, you would use this link option when you have more than one stored procedure in your library, and you want to access additional functions as stored procedures.
To continue the example from above, suppose that the library mystored contains three stored procedures: modify as above, remove, and add. You identify modify as the default entry point, as above, and indicate in the link step that remove and add are additional entry points by including them in an export file.
In the link step, you specify:
-bE:mystored.exp
which identifies the export file mystored.exp.
The export file looks like this:
* additional entry points for mystored #! remove add |
Finally, your two CALL statements for the stored procedures, which invoke the remove and add functions, are coded as follows:
CALL '/u/mydir/procs/mystored!remove'
and
CALL '/u/mydir/procs/mystored!add'
The script file bldxlcudf, in sqllib/samples/c, contains the commands to build a UDF. UDFs are compiled like stored procedures, but you do not need to connect to a database or precompile and bind the program.
| Note: | A UDF does not contain embedded SQL statements. Rather, the application that uses the UDF contains the statements, such as calludf. |
The first parameter, $1, specifies the name of your source file.
The script file uses the source file, $1, for the shared library name, and for the default entry point to the shared library.
#! /bin/ksh # bldxlcudf script file # Builds a sample C UDF library. # Usage: bldxlcudf <prog_name> # Compile the program. xlc -I/usr/lpp/db2_05_00/include -c $1.c # Link the program. xlc -o $1 $1.o -ldb2 -ldb2apie -L/usr/lpp/db2_05_00/lib -H512 -T512 -bE:$1.exp -e $1 # Copy the shared library to the sqllib/function subdirectory of the DB2 instance. # Note: this assumes the user has write permission to this directory. eval "H=~$DB2INSTANCE" cp $1 $H/sqllib/function |
| Compile and Link Options for bldxlcudf |
|---|
|
The script file contains the following compile options:
|
|
The script file contains the following link options:
Refer to your compiler documentation for additional compiler options. Refer
to "Coding and Compiling UDFs" for a general discussion about compiler options and UDFs.
|
To build the user-defined function udf, do the following:
bldxlcudf udf
The script file copies the UDF to the server in the path sqllib/function to indicate that the UDF is fenced. If you want the UDF to be unfenced, you must move it to the sqllib/function/unfenced directory. These paths are in the home directory of the DB2 instance.
| Note: | An unfenced UDF or stored procedure runs in the same address space as the database manager and results in increased performance when compared to a fenced UDF or stored procedure, which runs in an address space isolated from the database manager. With unfenced UDFs or stored procedures there is a danger that user code could accidentally or maliciously damage the database control structures. Therefore, you should only run unfenced UDFs or stored procedures when you need to maximize the performance benefits. Ensure these programs are thoroughly tested before running them as unfenced. Refer to the Embedded SQL Programming Guide for more information about fenced and not fenced UDFs. |
Once you build udf, you can build the client application, calludf, that calls it. You can build calludf using the bldxlc script file. Refer to "IBM C" for details.
To run the UDF, do the following:
db2start
calludf
The calling application calls functions from the udf library.
After you run the calling application, you can also invoke the UDF interactively using the command line processor like this:
db2 "SELECT name, DOLLAR(salary), SAMP_MUL(DOLLAR(salary), FACTOR(1.2)) FROM staff"
You do not have to type the command line processor commands in uppercase.
This section provides a general discussion about coding UDFs, and the compiler options you can use.
The Embedded SQL Programming Guide describes how to code your UDF. The SQL Reference describes how to register your UDF with DB2 using the CREATE FUNCTION statement. This section ties how you compile and link your UDF to the information you provide in the EXTERNAL NAME clause of the CREATE FUNCTION statement.
When you compile and link your program, you can identify functions in two ways:
For example, you can specify the following in the link step:
-e modify
This indicates that the default entry point for the linked library is the function modify.
If you are linking a library myudfs in a directory /u/mydir/procs, and you want to use the default entry point modify as specified above, include the following in your CREATE FUNCTION statement:
EXTERNAL NAME '/u/mydir/procs/myudfs'
DB2 picks up the default entry point of the library myudfs, which is the function modify.
Generally speaking, you would use this link option when you have more than one UDF in your library, and you want to access additional functions as UDFs.
To continue the example from above, suppose that the library myudfs contains three UDFs: modify as above, remove, and add. You identify modify as the default entry point, as above, and indicate in the link step that remove and add are additional entry points by including them in an export file.
In the link step, you specify:
-bE:myudfs.exp
which identifies the export file myudfs.exp.
The export file looks like this:
* additional entry points for myudfs #! remove add |
Finally, your two CREATE FUNCTION statements for the UDFs, which are implemented by the remove and add functions, would contain these EXTERNAL NAME clauses:
EXTERNAL NAME '/u/mydir/procs/myudfs!remove'
and
EXTERNAL NAME '/u/mydir/procs/myudfs!add'
Multi-threaded applications on AIX Version 4 need to be compiled and linked with the xlc_r compiler instead of the xlc compiler, or with the xlC_r compiler instead of the xlC compiler.