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 take several stored procedures and put them into one library.
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 the SQLDA structure or host variables. The SQL CALL statement can accept a series of host variables, or an SQLDA structure. (See the SQL Reference for a description of the SQL CALL statement.)
| 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). |
For the SQLDA structure used, perform the following steps before calling the stored procedure:
If your application will be working with character strings defined as FOR BIT DATA, you will need to initialize the SQLDAID field to indicate that the SQLDA includes FOR BIT DATA definitions and the SQLNAME field of each SQLVAR that defines a FOR BIT DATA element. If your application will be working with large objects, that is, data with types of CLOB, BLOB, or DBCLOB, you will also need to initialize the secondary SQLVAR elements. See the information on the SQLDA structure in the SQL Reference.
Following are the 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 SQLVARs as possible in order to increase the efficiency of handling SQLVARs. For example, when returning an SQLCODE to the client from the stored procedure, try to use an input SQLVAR that is declared as an INTEGER to return the SQLCODE. Unless you need to reuse the values in the SQLDA on the client, there is no problem overwriting the content of any SQLDA buffer.
In addition, the client application should set the indicator of output-only SQLVARs to -1 as discussed in "Data Structure Manipulation". This will improve the performance of the parameter passing mechanism by avoiding having to pass the contents of the SQLDATA pointer as only the indicator is sent. You should set the SQLTYPE field to a nullable data type for these parameters. If the SQLTYPE indicates a non-nullable data type, the indicator variable is not checked by the database manager.
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. Information is returned to the client application using the stored procedure's SQLDA structure.
The parameters of the SQL CALL statement are treated as both input and output parameters and are converted into the following format for the stored procedure:
SQL_API_RC SQL_API_FN proc_name( void *reserved1,
void *reserved2,
struct sqlda *inoutsqlda,
struct sqlca *sqlca )
The SQL_API_FN is a macro that specifies the calling convention for a function that may vary across each supported operating system. This macro is required when you write stored procedures or UDFs.
Following is an example of how a CALL statement maps to a server's parameter list:
CALL OUTSRV (:empno:empind,:salary:salind)
The parameters to this call are converted into an SQLDA structure with two SQLVARs. The first SQLVAR points to the empno host variable and the empind indicator variable. The second SQLVAR points to the salary host variable and the salind indicator variable.
Notes:
The database manager automatically allocates a duplicate SQLDA structure at the database server. To reduce network traffic, it is important to indicate which host variables are input-only, and which ones are output-only. The client procedure should set the indicator of output-only SQLVARs to -1. The server procedure should set the indicator for input-only SQLVARs to -128. This allows the database manager to choose which SQLVARs are passed.
Note that an indicator variable is not reset if the client or the server sets it to a negative value (indicating that the SQLVAR should not be passed). If the host variable to which the SQLVAR refers is given a value in the stored procedure or the client code, its indicator variable should be set to zero or a positive value so that the value is passed. For example, consider a stored procedure which takes one output-only parameter, called as follows:
empind = -1;
EXEC SQL CALL storproc(:empno:empind);
When the stored procedure sets the value for the first SQLVAR, it should also set the value of the indicator to a non-negative value so that the result is passed back to empno.
The stored procedure runs using any information passed in the input variables of the SQLDA structure. Information is returned to the client in the output variables of the SQLDA. Do not change the value of the SQLD, SQLTYPE, and SQLLEN fields of the SQLDA, as these fields are compared to the original values set by the client application before data is returned. If they are different, one of the following SQLCODEs is returned:
In addition, do not change the pointer for the SQLDATA and the SQLIND fields, although you can change the value that is pointed to by these fields.
| Note: | It is possible to use the same variable for both input and output. |
Before the stored procedure returns, SQLCA information should be explicitly copied to the SQLCA parameter of the 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.
The stored procedure returns with one of the following values:
If the stored procedure is invoked only once, SQLZ_DISCONNECT_PROC should be returned.
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 server request should return the value SQLZ_DISCONNECT_PROC to free the server library from main memory. Otherwise, the library remains in main memory until the database manager is stopped. As an alert to the server, the client application could pass a flag in one of the parameters indicating the final call.
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 server being accessed is running Version 1 of DB2 for OS/2, the client code page must be the same as the database code page. |
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( reserved1, reserved2,
inoutsqlda, sqlca )
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 decoration for the function name when you call the stored procedure.
When creating a stored procedure in the Java language, you must use the CREATE PROCEDURE statement to register the procedure to the system catalog table SYSCAT.PROCEDURES. For details on writing stored procedures in Java, see "Java UDFs and Stored Procedures", or refer to the CREATE PROCEDURE statement in the SQL Reference.
Any stored procedure written in C or C++, that receives or returns graphic data through its parameter input or output SQLDA should generally be precompiled with the WCHARTYPE NOCONVERT option. This is because graphic data passed through these SQLDAs 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 SQLDA 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, refer to "Japanese and Traditional-Chinese EUC Code Set Considerations", and more specifically to "Considerations for Stored Procedures".
Stored procedures that are called by applications in which the CONNECT TYPE 2 DUOW parameter is in effect, are restricted from issuing COMMIT or ROLLBACK, either dynamically or statically.
Table 10 summarizes the use of the various structure fields by the stored procedures application. In the table, sqlda is an SQLDA structure passed to the stored procedure and n is a numeric value indicating a specific SQLVAR element of the SQLDA. The numbers on the right refer to the notes following the table.
Table 10. Stored Procedures Parameter Variables
| Input/Output SQLDA | sqlda.SQLDAID |
|
|
| 4 |
|
|
|
|
| ||
|
| sqlda.SQLDABC |
|
|
| 4 |
|
|
|
|
| ||
|
| sqlda.SQLN |
| 2 |
| 4 |
|
|
|
|
| ||
|
| sqlda.SQLD |
| 2 | 3 |
| 5 |
|
|
|
| ||
| Input/Output SQLVAR | sqlda.n.SQLTYPE |
| 2 | 3 |
| 5 |
|
|
|
| ||
|
| sqlda.n.SQLLEN |
| 2 | 3 |
| 5 |
|
|
|
| ||
|
| sqlda.n.SQLDATA | 1 | 2 | 3 |
|
| 6 |
| 8 |
| ||
|
| sqlda.n.SQLIND | 1 | 2 | 3 |
|
| 6 |
| 8 | 9 | ||
|
| sqlda.n.SQLNAME.length |
|
|
|
|
| 6 | 7 |
|
| ||
|
| sqlda.n.SQLNAME.data |
|
|
|
|
| 6 | 7 |
|
| ||
|
| sqlda.n.SQLDATATYPE_NAME |
| 2 | 3 |
| 5 |
|
|
|
| ||
|
| sqlda.n.SQLLONGLEN |
| 2 | 3 |
| 5 |
|
|
|
| ||
|
| sqlda.n.SQLDATALEN | 1 | 2 | 3 |
|
| 6 | 7 |
|
| ||
| SQLCA (all elements) |
|
|
|
|
|
| 6 | 7 |
|
| ||
| ||||||||||||
(4) 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 for MVS/ESA, DB2 for OS/390, or DB2 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.)