5.21 Additional considerations

< Day Day Up >

External procedures and functions are also frequently seen in Oracle and DB2 UDB application. The complete information on building and running external procedures and functions is beyond the scope of this redbook. We provide here two examples of building routines using C and Java. For complete information on building and running external procedures and functions, please consult the following IBM DB2 UDB v8.1 documents:

Application Development Guide: Building and Running Applications, SC09-4825
Application Development Guide: Programming Client Applications, SC09-4826
Application Development Guide: Programming Server Applications, SC09-4827

5.21.1 Building C/C++ routines

We now show an example of creating a stored procedure written in C. First, here are some basic steps that must be followed when creating any C procedure (or function):

Create and save the external procedure/function and save it on your file system. If the procedure/function contains embedded SQL then it should be saved with the extension .sqc, if not save it with the extension .c
Create the export file (AIX ONLY)

AIX requires you to provide an export file that specifies which global functions in the library are callable from outside it. This file must include the names of all routines in the library. Other UNIX platforms simply export all global functions in the library. This is an example of an AIX export file for the procedure outlanguage that exists within the file spserver.sqc:
```
 #! spserver export file outlanguage 
```
The export file spserver.exp lists the stored procedure outlanguage. The linker uses spserver.exp to create the shared library spserver that contains the outlanguage stored procedure.
On Windows, a DEF file is required which has a similar purpose. See sqllib/samples/c/spserver.def for an example.
Run the bldrtn script which creates the shared library:
```
 bldrtn my_routine my_database_name 
```
The script copies the shared library to the server in the path sqllib/function.
Catalog the routines by running the catalog_my_routine script on the server.

Notes

After a shared library is built, it is typically copied into a directory from which DB2 will access it. When attempting to replace a routine 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.
DB2 provides build scripts for precompiling, compiling, and linking C stored procedures. These are located in the sqllib/samples/c directory, along with sample programs that can be built with these files. This directory also contains the embprep script used within the build script to precompile a *.sqc file. The build scripts have the .bat (batch) extension on Windows, and have no extension on UNIX platforms. For example, bldrtn.bat is a script to build C/C++ stored procedure on Windows platform; bldrtn is the equivalent on UNIX.

Example

Here is a simple example of creating and cataloging a stored procedure that is written in C. This procedure queries the sysprocedures table from the DB2 system catalog to find in which language (JAVA, C, etc.) that a procedure TWO_RESULT_SETS is written.

The C source file (Example 5-44), with embedded SQL is created and saved as outlanguage.sqc.

Example 5-44: Stored Procedure in C

    #include <stdio.h>    #include <string.h>    #include <stdlib.h>    #include <sqlda.h>    #include <sqlca.h>    #include <sqludf.h>    #include <sql.h>    #include <memory.h>    SQL_API_RC SQL_API_FN outlanguage(char language[9]){      struct sqlca sqlca;      EXEC SQL BEGIN DECLARE SECTION;        char out_lang[9];      EXEC SQL END DECLARE SECTION;      /* Initialize strings used for output parameters to NULL */      memset(language, '\0', 9);      EXEC SQL SELECT language INTO :out_lang        FROM sysibm.sysprocedures        WHERE procname = 'TWO_RESULT_SETS';      strcpy(language, out_lang);      return 0;    } /* outlanguage function */

The .exp file is created and saved as outlanguage.exp. Here are the contents of that file:
```
    outlanguage 
```

The file outlanguage_crt.db2, which catalogs the procedure is created and saved. Here is the contents of that file:

    CREATE PROCEDURE outlanguage (OUT language CHAR(8))    DYNAMIC RESULT SETS 0    LANGUAGE C    PARAMETER STYLE SQL    NO DBINFO    FENCED NOT THREADSAFE    MODIFIES SQL DATA    PROGRAM TYPE SUB    EXTERNAL NAME 'outlanguage!outlanguage'!

The build script bldrtn for the outlanguage.sqc file is executed using the db2_emp database:
```
 bldrtn outlanguage db2_emp 
```
A connection is made to the database:
```
 db2 connect to db2_emp 
```

The script to Catalog the procedure is executed:

 db2 –td! –vf outlanguage_crt.db2 > message.out

DB2 responds with the message:
```
 DB20000I The SQL command completed successfully. 
```
The message.out file should be viewed for messages, especially if any other message than The SQL command completed successfully is returned.

The procedure is tested:

 db2 "call outlanguage(?)"

Results:

    Value of output parameters      --------------------------      Parameter Name  : LANGUAGE      Parameter Value : JAVA      Return Status = 0

5.21.2 Building JAVA routines

Here are the basic steps to create an external Java User-Defined Function (UDF) from the DB2 Command Window:

Compile your_javaFileName.java to produce the file your_javaFileName.class with this command:
```
 javac your_javaFileName.java 
```
Copy your_javaFileName.class to the sqllib\function directory on Windows operating systems, or to the sqllib/function directory on UNIX.
Connect to the database:
```
 db2 connect to your_database_name 
```
Register the your_javaFileName library in the database using the CREATE FUNCTION SQL statement.
```
 db2 –td! –vf <your_create_function_statement.db2> 
```

Example

Here is an example of a Java UDF that will retrieve the system name from the DB2 Registry variable DB2SYSTEM.

The Java source file as shown in Example 5-45 is saved as db2system_nameUDF.java.

Example 5-45: UDF Java source

 import java.io.*; public class db2system_nameUDF {   public static String db2system_name() {     Runtime rt = Runtime.getRuntime();     Process p=null;     String s = null;     String returnString = "";     try {        // WINDOWS: **** uncomment and compile the following for Windows        // p = rt.exec("cmd /C db2set DB2SYSTEM");        // UNIX: **** uncomment and compile the following for UNIX        p = rt.exec("db2set DB2SYSTEM");        BufferedInputStream buffer =           new BufferedInputStream(p.getInputStream());        BufferedReader commandResult =           new BufferedReader(new InputStreamReader(buffer));        try {            while ((s = commandResult.readLine()) != null)                returnString = returnString.trim() + s.trim() ;            // MAX number of chars for the DB2SYSTEM variable is 209 characters            commandResult.close();            // Ignore read errors; they mean process is done        } catch (Exception e) {        }     } catch (IOException e) {         returnString = "failure!";     }     return(returnString);   } }

Compile the Java source. The compile command is:
```
 javac db2system_nameUDF.java 
```
Copy the .class file to /sqllib/function directory

$ cp db2system_nameUDF.java /home/db2inst1/sqllib/function

Construct the Create Function file and save it as db2system_name.db2:

    DROP FUNCTION DB2SYSTEM_NAME !    CREATE FUNCTION DB2SYSTEM_NAME ()    RETURNS VARCHAR(209)    EXTERNAL NAME 'db2system_nameUDF!db2system_name'    LANGUAGE JAVA    PARAMETER STYLE JAVA    NOT DETERMINISTIC    NO SQL    EXTERNAL ACTION!

Connect to the database:
```
 db2 connect to db2_emp 
```
Execute the script to register the UDF with the database:
```
 db -td! -vf db2system_name.db2 
```

Test the UDF:

 db2 "values db2system_name()"

Result:

 ------------------------------------------------- smpoaix 1 record(s) selected.

< Day Day Up >