|
Porting Stored Procedures to older (pre-V4R2) DB2 / 400
The code and programming advice herein, that is furnished by IBM is a series of simple examples, intended to provide illustrations of concept. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of this code. All program code and programming advice contained herein is provided to you "AS IS". THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED.
Ultimately, it is the responsibility of the individual programmer to understand his or her own application and use this advice as it best pertains to that application. IBM offers the following as only as potential solutions, which must be evaluated on an individual-use basis.
All brand, company, service, or product names mentioned are trademarks, service marks, or registered trademarks of their respective companies.
Overview
In early 1998, with OS/400 V4R2, IBM included SQL-based stored procedures based on the ANSI standard syntax. These 'SQL Procedures' are different from the method described below. SQL Procedures are documented in the book, DB/2 for iSeries SQL Programming V4R2, and DB/2 for iSeries SQL Reference V4R2. If you have the requirement of supporting earlier releases, you likely should follow the methods outlined below to implement your stored procedures.
The intent of this paper is to introduce the most basic aspects of programming DB2 for OS/400 stored procedures. It is written with the intent that the reader is familiar with SQL, especially familiar with the semantics of programming stored procedures in a Transact-SQL environment, such as Microsoft SQL-Server. This paper is not intended to be a throrough description of the topic. While reading this paper, it would be beneficial to have available the two iSeries manuals listed in the References section.
Stored procedures on the iSeries are simply iSeries programs. They are created using the same techniques as any other iSeries application code. They are typically built from a third-generation language, such as C, CL, COBOL, RPG, etc. In addition to this flexibility, these programs can, and often do, contain embedded SQL. This means that the process used in adapting SQL-based stored procedures will be basically creating a small wrapper of 3GL code around the original SQL.
This paper will describe the conversion process using C programs on the iSeries. However, there is no apparent advantage to using C, except that it is the primary language used by this author. There are five main topics to learn when adapting stored procedures from Transact-SQL to the iSeries's built-in RDBMS, DB2 for OS/400. These topics are:
- Declaration/Creation Format
- Code Format
- Parameters
- Host Variables
- Result Sets
Declaration/Creation Format
The important thing to remember is that DB2 for OS/400 stored procedures are simply iSeries programs. Therefore, the procedure definition and the actual code which implements it are in separate places. The implementation code becomes the stored procedure. The definition code, which identifies a name, parameters, etc. is SQL in a separate place. This contrasts with Transact-SQL, where the procedure implementation is defined in the same SQL script as part of the procedure naming. There are two ways to identify a stored procedure to DB2 for OS/400. These are the DECLARE PROCEDURE and CREATE PROCEDURE statements. Both of these statements perform the necessary functions of specifying parameter types, a procedure name, the external iSeries program name, etc. The difference is that CREATE PROCEDURE updates the procedure information into the system tables, and any program connected to DB2 for OS/400 can call it (if authorized, of course). The DECLARE PROCEDURE only defines the procedure within the scope of an SQL session, such as within an iSeries program lifetime. In most cases, it will make the most sense to use CREATE PROCEDURE one time, during installation of a product.
The following is a simple example of a stored procedure declaration for DB2 for OS/400, written in SQL.
CREATE PROCEDURE proc1 (IN parm1 INTEGER)
(EXTERNAL NAME mylib/proc1 LANGUAGE C GENERAL)
This identifies the procedure name as proc1 and that it has one parameter for both input and output named parm1. This statement also defines that the program proc1 in the library mylib is to be called when this procedure is called. Finally, the language type and whether it is a GENERAL or a GENERAL WITH NULLS. This WITH NULLS clause is needed if parameters passed to the procedure can be NULL.
Code Format
To see the basic format of a C program which executes a single SQL statement, observe the following:
Original SQL:
INSERT INTO table1 VALUES (123,'Bob')
iSeries C program with embedded SQL:
EXEC SQL INCLUDE SQLCA;
void main()
{
EXEC SQL INSERT INTO table1 VALUES (123,'Bob');
}
To embed SQL in a C program, there are two changes to a typical C program. First, EXEC SQL INCLUDE SQLCA must appear at the beginning. Then, before any SQL statement, simply add the prefix EXEC SQL. After each statement, add a semicolon.
Parameters
Parameters to stored procedures on the iSeries can be any type or structure of data. The individual parameters can be declared as INPUT, OUTPUT, or both (INOUT). They can also be specified, all-or-none, as nullable or not nullable.
Parameters to stored procedures are identified by a C program using the same methods as those used in retrieving command line parameters to DOS, UNIX, etc. programs. The second argument to the main() function is an array of pointers, where elements of the array point to the data in a parameter. The parameter given to the program can be used like any other program variable. In the example below, the first parameter is used to determine which of this author's favorite palindromes is to be inserted into table1.
EXEC SQL INCLUDE SQLCA;
void main(int argc, char* argv[])
{
if ( (*(long*) argv[1]) == 1)
{
EXEC SQL INSERT INTO table1 VALUES (123,'Bob');
} else {
EXEC SQL INSERT INTO table1 VALUES (321,'Rise to vote, sir!');
}
}
These parameters can be used in SQL statements, by assigning their values to variables declared as Host Variables, as described in the following section.
Host Variables
There are many ways to use variables from the 3GL program within SQL. They can be used in WHERE clauses, UPDATE clauses, SELECT INTO statements, and others. These three are the most common, though. A host variable is identified by using a colon. For example, the variable number is used in the following SQL statements. * Before using a host variable, it must be declared. If it is to be used in an SQL statement, the variable should be declared within the boundaries of a DECLARE SECTION statement. Use of the DECLARE SECTION is good coding practice for portability to other DB2 platforms. But, unlike DB2 common server, the iSeries SQL precompilers process all host language variables whether in the DECLARE SECTION or not. See the example below. The second line below actually declares the variable number for use by the SQL and the C code.
EXEC SQL BEGIN DECLARE SECTION;
long number;
EXEC SQL END DECLARE SECTION;
number = *(long*) argv[1];
EXEC SQL SELECT name FROM table1 WHERE
table1.id = :number;
EXEC SQL SELECT sum(amt) FROM table1 INTO :number;
Other types of variables can be used, including VARCHAR, VARGRAPHIC, DATE, TIME, FLOAT, etc. Refer to chapter 7, Stored Procedures, of DB2 for OS/400 Programming V3R7 for more information.
Result Sets
To identify a result set cursor for an ODBC client, use the following code as an example.
The following code specifies a cursor, X, as the result set from the query that will be returned when the stored procedure is called from an ODBC client.
EXEC SQL INCLUDE SQLCA;
void main()
{
EXEC SQL DECLARE csr_x CURSOR FOR SELECT id,name
FROM table1 WHERE name='Bob';
EXEC SQL OPEN csr_x;
EXEC SQL SET RESULT SETS CURSOR csr_x;
}
References
The best source for more a detailed description of programming iSeries stored procedures is DB2 for OS/400 SQL Programming V3R7, SC41-4611, specifically chapters 7 (Stored Procedures) and 10 (Coding SQL in C Applications).
Additionally, 'DB2 for OS/400 SQL Reference V3R7', SC41-4612, specifically chapter 2 (Host Variables) is a good source of information.
These two texts, as well as the entire set of iSeries manuals is available from IBM via the Web
|
