An application using stored procedures must be written in two separate procedures. The calling procedure is contained in a client application, and executes on the client. It can be written in any of the supported host languages. The stored procedure executes at the location of the database on the database server, and must be written in one of the supported languages for that database server.
The two procedures must be built in separate steps.
The client application performs the following:
Note that you can code SQL statements in any of the above steps.
When invoked, the stored procedure performs the following:
The stored procedure executes when called by the client application. Control is returned to the client when the server procedure finishes processing. You can put several stored procedures into one library.
This chapter describes how to write stored procedures with the following parameter styles:
You must register each stored procedure for the previously listed parameter styles with a CREATE PROCEDURE statement. The CREATE PROCEDURE statement specifies the procedure name, arguments, location, and parameter style of each stored procedure. These parameter styles offer increased portability and scalability of your stored procedure code across the DB2 family.
For information on using the styles of stored procedures supported by versions of DB2 prior to Version 6, that is, the DB2DARI and DB2GENERAL parameter styles, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
The client application performs several steps before calling the stored procedure. It must be connected to a database, and it must declare, allocate, and initialize host variables or SQLDA structure. The SQL CALL statement can accept a series of host variables, or an SQLDA structure. Refer to the SQL Reference for descriptions of the SQL CALL statement and the SQLDA structure. For information on using the SQLDA structure in a client application, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
Use the following steps to allocate the necessary input host variables on the client side of a stored procedure:
When writing the client portion of your stored procedure, you should attempt to overload as many of the host variables as possible by using them for both input and output. This will increase the efficiency of handling multiple host variables. For example, when returning an SQLCODE to the client from the stored procedure, try to use an input host variable that is declared as an INTEGER to return the SQLCODE.
| Note: | Do not allocate storage for these structures on the database server. The database manager automatically allocates duplicate storage based upon the storage allocated by the client application. Do not alter any storage pointers for the input/output parameters on the stored procedure side. Attempting to replace a pointer with a locally created storage pointer will cause an error with SQLCODE -1133 (SQLSTATE 39502). |
You can invoke a stored procedure stored at the location of the database by using the SQL CALL statement. Refer to the SQL Reference for a complete description of the CALL statement. Using the CALL statement is the recommended method of invoking stored procedures.
The client application must ensure that a database connection has been made before invoking the stored procedure, or an error is returned. After the database connection and data structure initialization, the client application calls the stored procedure and passes any required data. The application disconnects from the database. Note that you can code SQL statements in any of the above steps.
The stored procedure is invoked by the SQL CALL statement and executes using data passed to it by the client application. The parameter style with which you register the stored procedure in the database manager with the CREATE PROCEDURE statement determines how the stored procedure receives data from the client application.
To use the CREATE PROCEDURE statement, you must declare the following:
Your CREATE PROCEDURE should also declare the following:
You can find more information on the CREATE PROCEDURE statement, including its full syntax and options for DB2 family compatibility, in the SQL Reference. Descriptions of typical usages of the CREATE PROCEDURE statement follow.
You can overload stored procedures only by using the same name for procedures that accept a unique number of parameters. Since DB2 does not distinguish between data types, you cannot overload stored procedures based on parameter data types.
For example, issuing the following CREATE PROCEDURE statements will work because they accept one and two parameters, respectively:
CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER) ...
CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER, IN VAR2 INTEGER) ...
However, DB2 will fail to register the second stored procedure in the following example because it has the same number of parameters as the first stored procedure with the same name:
CREATE PROCEDURE OVERLOADFAIL (IN VAR1 INTEGER) ...
CREATE PROCEDURE OVERLOADFAIL (IN VAR2 VARCHAR(15)) ...
You must declare every parameter passed from a client application to the stored procedure, and from the stored procedure back to the client application, as either an IN, OUT, or INOUT parameter. For each parameter, you must also specify a name and SQL data type. See Variable Declaration and CREATE PROCEDURE Examples for examples.
The EXTERNAL clause of the CREATE PROCEDURE statement tells the database manager the location of the library that contains the stored procedure. If you do not specify an absolute path for the library, or a jar name for Java stored procedures, the database manager searches the function directory. The function directory is a directory defined for your operating system as follows:
For example, the function directory for a Windows NT server with DB2 installed in the C:\sqllib directory, and with no specified DB2INSTPROF registry setting, is:
C:\sqllib\function
| Note: | You should give your library a name that is different than the stored procedure name. If DB2 locates the library in the search path, DB2 executes a stored procedure with the same name as its library as a FENCED DB2DARI procedure. |
For LANGUAGE C stored procedures, specify:
On a UNIX-based system, for example, mymod!proc8 directs the database manager to the sqllib/function/mymod library and to use entry point proc8 within that library. On Windows 32-bit and OS/2 operating systems mymod!proc8 directs the database manager to load the mymod.dll file from the function directory and call the proc8() procedure in the dynamic link library (DLL).
For LANGUAGE JAVA stored procedures, use:
If you do not specify a jar file, the database manager looks for the class in the function directory.
For example, if you specify MyPackage.MyClass.myMethod, the database manager uses the myMethod method in the MyClass class, within the MyPackage package. Since you did not specify a JAR file, the database manager will search the function directory.
For more information on the function directory, see Location.
For C/C++, declare LANGUAGE C in your CREATE PROCEDURE statement. For Java stored procedures, declare LANGUAGE JAVA. For COBOL stored procedures, declare LANGUAGE COBOL. For Fortran or REXX stored procedures, you must write the stored procedure as a DB2DARI stored procedure. For more information on writing DB2DARI stored procedures, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
Table 9 summarizes the combinations of PARAMETER STYLE (horizontal
axis) and LANGUAGE (vertical axis) allowed in CREATE PROCEDURE statements for
DB2 Version 6.
Table 9. CREATE PROCEDURE: Valid Combinations of PARAMETER STYLE and LANGUAGE
| GENERAL, GENERAL WITH NULLS | JAVA | DB2SQL | DB2DARI | DB2GENERAL | |
|---|---|---|---|---|---|
| LANGUAGE C | Y | N | Y | Y | N |
| LANGUAGE JAVA | N | Y | N | N | Y |
| LANGUAGE COBOL | Y | N | Y | N | N |
DB2 Universal Database for OS/390 compatibility note: GENERAL is the equivalent of SIMPLE.
DB2 Universal Database for OS/390 compatibility note: GENERAL WITH NULLS is the equivalent of SIMPLE WITH NULLS
You can only specify DB2SQL when you also specify the LANGUAGE C or LANGUAGE COBOL option.
For increased portability, you should write Java stored procedures using the PARAMETER STYLE JAVA conventions. See Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs for more information on writing DB2GENERAL parameter style stored procedures.
To increase portability across the DB2 family, you should write your LANGUAGE C stored procedures using the GENERAL or GENERAL WITH NULLS parameter styles. If you want to write DB2DARI parameter style stored procedures, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
For LANGUAGE C stored procedures with a PARAMETER TYPE of GENERAL, GENERAL WITH NULLS, or DB2SQL, you have the option of writing your stored procedure to accept an additional parameter. You can specify DBINFO in the CREATE PROCEDURE statement to instruct the client application to pass a DBINFO structure containing information about the DB2 client to the stored procedure, along with the call parameters. The DBINFO structure contains the following values:
For more information on the DBINFO structure, see DBINFO Structure.
Writing Stored Procedures as main Functions:
For LANGUAGE C stored procedures with a PARAMETER TYPE of GENERAL, GENERAL WITH NULLS, or DB2SQL, you have the option of writing your stored procedure to accept parameters like a main function in a C program (MAIN) or like a subroutine (SUB). The default value for PROGRAM TYPE is SUB, which accepts parameters as separate arguments; for example:
void storproc (int arg1, long arg2, int arg3);
To write a stored procedure that accepts arguments like a main function in a C program, specify PROGRAM TYPE MAIN in the CREATE PROCEDURE statement. You must write stored procedures of PROGRAM TYPE MAIN to conform to the following specifications:
For example, the signature for a stored procedure of PROGRAM TYPE MAIN would appear as follows:
int main(int argc, char* argv[])
The following examples demonstrate the stored procedure source code and CREATE PROCEDURE statements you would use in hypothetical scenarios with the SAMPLE database.
Assume that you want to create a Java stored procedure GET_LASTNAME that, given EMPNO (SQL type VARCHAR), returns LASTNAME (SQL type CHAR) from the EMPLOYEE table in the SAMPLE database. You will create the procedure as the getname method of the Java class StoredProcedure, contained in the JAR installed as myJar. Finally, you will call the stored procedure with a client application coded in C.
String empid;
String name;
...
#sql { SELECT lastname INTO :empid FROM employee WHERE empno=:empid }
CREATE PROCEDURE GET_LASTNAME (IN EMPID CHAR(6), OUT NAME VARCHAR(15))
EXTERNAL NAME 'myJar:StoredProcedure.getname'
LANGUAGE JAVA PARAMETER STYLE JAVA FENCED
READS SQL DATA
EXEC SQL BEGIN DECLARE SECTION;
struct name { short int; char[15] }
char[7] empid;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL CALL GET_LASTNAME (:empid, :name);
For the following example, assume that you want to create a C stored procedure GET_MANAGER that, given DEPTNUMB (SQL type SMALLINT), returns MANAGER (SQL type SMALLINT) from the ORG table in the SAMPLE database.
EXEC SQL BEGIN DECLARE SECTION;
short onevar = 0;
EXEC SQL END DECLARE SECTION;
CREATE PROCEDURE GET_MANAGER (INOUT ONEVAR SMALLINT)
EXTERNAL NAME 'stplib!getman'
LANGUAGE C PARAMETER STYLE GENERAL FENCED
READS SQL DATA
short onevar = 0;
...
#SQL { CALL GET_MANAGER (:INOUT onevar) };
Stored procedures can contain SQL statements. When you issue the CREATE PROCEDURE statement, you should specify the type of SQL statements the stored procedure contains, if any. If you do not specify a value when you register the stored procedure, the database manager uses the restrictive default value CONTAINS SQL. To indicate the type of SQL used in the stored procedure, you can use one of the following four options:
For more information on the CREATE PROCEDURE statement, refer to the SQL Reference.
When you create a stored procedure, you must observe the following restrictions:
| Note: | For compatibility with previous versions of DB2, DB2DARI and DB2GENERAL stored procedures allow COMMIT statements within the procedure body. |
in a stored procedure.
The return value of the stored procedure is never returned to the client application. It is used by the database manager to determine if the server procedure should be released from memory upon exit.
| Note: | Java stored procedures do not return values to the database manager. This section applies only to stored procedures that are not written in Java. |
The stored procedure returns one of the following values:
If the stored procedure is invoked only once, it should return SQLZ_DISCONNECT_PROC.
If the client application issues multiple calls to invoke the same stored procedure, SQLZ_HOLD_PROC should be the return value of the stored procedure. The stored procedure will not be unloaded.
If SQLZ_HOLD_PROC is used, the last invocation of the stored procedure should return the value SQLZ_DISCONNECT_PROC to free the stored procedure library from main memory. Otherwise, the library remains in main memory until the database manager is stopped. As an alert to the stored procedure, the client application could pass a flag in one of the parameters indicating the final call.
Following is a sample program demonstrating the use of an OUT host variable. The client application invokes a stored procedure that determines the median salary for employees in the SAMPLE database. (The definition of the median is that half the values lie above it, and half below it.) The median salary is then passed back to the client application using an OUT host variable.
This sample program calculates the median salary of all employees in the SAMPLE database. Since there is no existing SQL column function to calculate medians, the median salary can be found iteratively by the following algorithm:
An application that uses neither the stored procedures technique, nor blocking cursors, must FETCH each salary across the network as shown in Figure 5.
Figure 5. Median Sample Without a Stored Procedure
|
Since only the salary at row n/2+1 is needed, the application discards all the additional data, but only after it is transmitted across the network.
You can design an application using the stored procedures technique that allows the stored procedure to process and discard the unnecessary data, returning only the median salary to the client application. Figure 6 shows this feature.
Figure 6. Median Sample Using a Stored Procedure
 |
OUT Client Description shows a sample OUT host variable client application and stored procedure. The sample programs are available in Java as:
For the OUT host variable client application and stored procedure in other programming languages, refer to the sqllib/samples directory.
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
import java.sql.*; // JDBC classes
class Outcli
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n" + e);
System.exit(1);
}
}
public static void main (String argv[])
{ try
{ System.out.println (" Java Output Stored Procedure Sample");
// Connect to Sample database
Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
if (argv.length == 0) (2)
{ // connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 2)
{ String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(url, userid, passwd);
}
else
{ throw new Exception("\nUsage: java Outcli [username password]\n");
}
// Set AutoCommit
con.setAutoCommit(true);
// Register and call the Remote Procedure
String callName = "outsrv";
String storedProcName = "Outsrv!outputStoredProcedure";
String mode = "fenced";
Statement stmt = con.createStatement (); // for DROP/CREATE
// drop the stored procedure if it exists
try
{ stmt.executeUpdate ("DROP PROCEDURE " + callName);
}
catch (Exception e)
{ // ignore this error
}
// construct a parameter list for the stored procedure and
// register it in the system catalogs (3)
String parameterList = "(out medianSalary double)";
System.out.println ("\n Registering Java stored procedure "
+ callName + "\n as " + storedProcName +
"\n in " + mode + " mode");
stmt.executeUpdate ("CREATE PROCEDURE " + callName + parameterList +
" LANGUAGE JAVA " +
" PARAMETER STYLE DB2GENERAL " + mode +
" EXTERNAL NAME '" + storedProcName + "'");
stmt.close ();
// prepare the CALL statement
CallableStatement callableStmt;
String sql = "Call " + callName + "(?) ";
callableStmt = con.prepareCall (sql);
// register the output parameters (4)
callableStmt.registerOutParameter (1, Types.DOUBLE);
// set all parameters
callableStmt.setDouble (1, 0.00);
// call the stored procedure
con.setAutoCommit(false); // Enable transactions
try
{ System.out.println("Calling stored procedure: " + callName );
callableStmt.execute (); (5)
// Commit the transaction
con.commit();
}
catch (Exception e)
{ // Rollback the transaction
con.rollback();
throw e;
}
// retrieve output parameters
double medianSalary = callableStmt.getDouble (1);
// display the information returned from the stored procedure
System.out.println ("\n Median Salary: " + medianSalary);
callableStmt.close (); (6)
}
catch (Exception e)
{ System.out.println (e);
}
}
}
import java.sql.*; // JDBC classes
import COM.ibm.db2.app.*; // StoredProc and associated classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator Outsrv_Cursor1 (double salary) ;
class Outsrv extends StoredProc
{ // (1) stored procedure body
public void outputStoredProcedure ( (1)
double medianSalary) throws Exception
{ try
{ // Get the DefaultContext
DefaultContext ctx = DefaultContext.getDefaultContext();
// If the default context is null, set a new one
if(ctx == null)
{ // get caller's connection to the database;
// inherited from StoredProc
Connection con = getConnection ();
ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
con.setAutoCommit(false); (2)
}
// Declare Variables
Outsrv_Cursor1 cursor1;
short numRecords;
int counter = 0;
double sal = 0.00;
// Prepare a Statement to Obtain and Order all Salaries
#sql cursor1 = { SELECT salary FROM STAFF ORDER BY salary }; (3)
// Determine the Total Number of Records
#sql { SELECT COUNT(*) INTO :numRecords FROM STAFF }; (4)
// Fetch Salaries until the Median Salary is Obtained
while (counter < numRecords/2 + 1) (5)
{ cursor1.next();
counter++;
}
sal = cursor1.salary();
cursor1.close();
// set value for the output parameter (6)
set (1, sal);
}
catch (Exception e)
{ throw e;
}
}
}
The code page considerations depend on the server.
When a client program (using, for example, code page A) calls a remote stored procedure that accesses a database using a different code page (for example, code page Z), the following events occur:
Therefore, you must run the stored procedure using the same code page as the database, in this example, code page Z. It is a good practice to prep, compile, and bind the server procedure using the same code page as the database.
| Note: | If the parameter of the stored procedure is defined as FOR BIT DATA at the server, conversion does not occur for a CALL statement to DB2 Universal Database for OS/390 or DB2 Universal Database for AS/400, regardless of whether it is explicitly specified in the SQLDA. (Refer to the section on the SQLDA in the SQL Reference for details.) |
For more information on this topic, see Conversion Between Different Code Pages.
When writing a stored procedure in C++, you may want to consider declaring the procedure name as:
extern "C" SQL_API_RC SQL_API_FN proc_name( short *parm1, char *parm2)
The extern "C" prevents type decoration (or mangling) of the function name by the C++ compiler. Without this declaration, you have to include all the type decorations for the function name when you call the stored procedure.
Any stored procedure written in C or C++, that receives or returns graphic data through its parameter input or output should generally be precompiled with the WCHARTYPE NOCONVERT option. This is because graphic data passed through these parameters is considered to be in DBCS format, rather than the wchar_t process code format. Using NOCONVERT means that graphic data manipulated in SQL statements in the stored procedure will also be in DBCS format, matching the format of the parameter data.
With WCHARTYPE NOCONVERT, no character code conversion occurs between the graphic host variable and the database manager. The data in a graphic host variable is sent to, and received from, the database manager as unaltered DBCS characters. Note that if you do not use WCHARTYPE NOCONVERT, it is still possible for you to manipulate graphic data in wchar_t format in a stored procedure; however, you must perform the input and output conversions manually.
CONVERT can be used in FENCED stored procedures, and it will affect the graphic data in SQL statements within the stored procedure, but not through the stored procedure's interface. NOT FENCED stored procedures must be built using the NOCONVERT option.
In summary, graphic data passed to or returned from a stored procedure through its input or output parameters is in DBCS format, regardless of how it was precompiled with the WCHARTYPE option.
For important information on handling graphic data in C applications, see Handling Graphic Host Variables. For information on EUC code sets and application guidelines, see Japanese and Traditional Chinese EUC and UCS-2 Code Set Considerations, and more specifically to Considerations for Stored Procedures.
Stored procedures that applications call with CONNECT TYPE 2 cannot issue a COMMIT or ROLLBACK, either dynamically or statically.