Stored procedures are programs that access the database and return information to your client application. User-Defined Functions (UDFs) are your own scalar or table functions. Stored procedures and UDFs are compiled on the server, and stored and executed in a shared library on the server. This shared library is created when you compile the stored procedures and UDFs.
The shared library has an entry point, which is called from the server to access procedures in the shared library. Unlike compilers on other UNIX platforms, the IBM XL C compiler on AIX allows you to specify any exported function name in the library as the default entry point. This is the function that is called if only the library name is specified in a stored procedure call or CREATE FUNCTION statement. This can be done with the -e option in the link step. For example:
-e funcname
makes funcname the default entry point. For information on how this relates to the CREATE FUNCTION statement, see "Relationship to Your CREATE FUNCTION Statement".
On other UNIX platforms, no such mechanism exists, so the default entry point is assumed by DB2 to be the same name as the library itself.
AIX requires you to provide an export file which specifies which global functions in the library are callable from outside it. This file must include the names of all stored procedures and/or user-defined functions in the library. Other UNIX platforms simply export all global functions in the library. This is an example of an AIX export file:
#! outsrv export file outsrv |
The export file outsrv.exp lists the stored procedure outsrv. The linker uses outsrv.exp to create the shared library outsrv that contains the stored procedure of the same name.
| Note: | After the shared library is built, it is typically copied into a directory from which DB2 will access it. When attempting to replace either a stored procedure or a user-defined function shared library, you should either run /usr/sbin/slibclean to flush the AIX shared library cache, or remove the library from the target directory and then copy the library from the source directory to the target directory. Otherwise, the copy operation may fail because AIX keeps a cache of referenced libraries and does not allow the library to be overwritten. |
For more information about stored procedures and UDFs, refer to your compiler documentation. The AIX compiler documentation also has additional information on export files.
Because function names can be 'overloaded' in C++, that is, two functions with the same name can coexist if they have different arguments, as in int foo( int i ) and int foo( char c ), C++ compilers 'type-decorate' or 'mangle' function names by default. This means that argument type names are appended to their function names to resolve them, as in foo__Fi and foo__Fc for the two earlier examples.
The type-decorated function name can be determined from the .o file using the nm command:
nm myprog.o
The command produces some output which includes a line similar to the following:
myprogen__FPlT1PsT3PcN35| 3792|unamex| | ...
When registering such a UDF with CREATE FUNCTION, the EXTERNAL NAME clause must specify the mangled function name obtained from nm (not including the | character):
CREATE FUNCTION myprogo(...) RETURNS...
...
EXTERNAL NAME '/whatever/path/myprog!myprogen__FPlT1PsT3PcN35'
...
Likewise, when calling a stored procedure, the function name also specifies the mangled function name:
CALL '/whatever/path/myprog!myprogen__FPlT1PsT3PcN35' ( ... )
If your stored procedure or UDF library does not contain overloaded C++ function names, you have the option of using extern "C" to force the compiler to not type-decorate function names. (Note that you can always overload the SQL function names given to UDFs, since DB2 resolves what library function to call based on the name and the parameters it takes.)
#include <string.h>
#include <stdlib.h>
#include "sqludf.h"
/*---------------------------------------------------------------------*/
/* function fold: output = input string is folded at point indicated */
/* by the second argument. */
/* inputs: CLOB, input string */
/* LONG position to fold on */
/* output: CLOB folded string */
/*---------------------------------------------------------------------*/
extern "C" void fold(
SQLUDF_CLOB *in1, /* input CLOB to fold */
...
...
}
/* end of UDF: fold */
/*---------------------------------------------------------------------*/
/* function find_vowel: */
/* returns the position of the first vowel. */
/* returns error if no vowel. */
/* defined as NOT NULL CALL */
/* inputs: VARCHAR(500) */
/* output: INTEGER */
/*---------------------------------------------------------------------*/
extern "C" void findvwl(
SQLUDF_VARCHAR *in, /* input smallint */
...
...
}
/* end of UDF: findvwl */
|
In this example, the UDFs fold and findvwl are not type-decorated by the compiler, and should be registered in the CREATE FUNCTION statement using their plain names. Similarly, if a C++ stored procedure is coded with extern "C", its undecorated function name would be used in the CALL statement.