Stored procedures are programs that are executed when an embedded SQL Call command is issued. They improve the performance of client programs by shifting processing from clients to the host. They reduce the maintenance nightmare by eliminating the need to duplicate common calculations on many clients.
With OS/400 V3R1, DB2/400 offered new database features that included referential integrity, triggers, and stored procedures. These database features were already part of large-scale database management systems like Oracle for years. They were also part of smaller but rapidly growing database systems, such as Microsoft SQL Server. It was a general consensus of opinion among database experts that these new features for DB2/400 made it more powerful and would enable it to more effectively compete with the competition.
It has been my observation that these new features are not widely used in the AS/400 world. Part of the reason for this paradox is that we AS/400 professionals have been exposed to these new concepts for a relatively short time.
This article will explain the concept of using stored procedures on the AS/400. Then, by using example programs, this article will demonstrate how to implement stored procedure programs on the AS/400. If you are looking for an innovative way to give your DB2/400 database engine a performance boost, read on. You might find some powerful programming techniques that you can readily use.
Concepts of Stored Procedures
At this point, some important issues regarding stored procedures should be clarified. Stored procedures are used in conjunction with Structured Query Language (SQL). If you don’t use embedded SQL calls in your application, you can’t use stored procedures. On the other hand, if you do use embedded SQL calls, in server- or
client/server-based applications, there is a very good chance that you will use stored procedures. This article will describe how to use stored procedures with the AS/400.
Simply put, stored procedures are programs that are executed when an embedded SQL Call command is issued. To use stored procedures, your shop must have the OS/400 SQL Development Kit and any high-level language (HLL) installed on your system.
The stored procedure program resides on the AS/400 and can be written and called in the ILE C, CL, OPM COBOL, ILE COBOL, FORTRAN, PL/I, REXX, RPG/III, and RPG IV programming languages. Other HLL programs, such as Java or Visual Basic running in a client/server environment, can also execute server-based stored procedures.
Stored procedures definitely have a multiple HLL flavor. This is because they were specifically designed to work with different HLLs and in different situations. At the same time, they are also designed to be able to execute the same function in an efficient fashion.
Stored procedures are frequently mentioned in conjunction with triggers, but they are different. Triggers are special programs that are executed anytime a specified database event occurs.
Stored procedures, by contrast, are implemented at the programmer’s discretion. They are executed only when they are explicitly called with an SQL Call statement. Stored procedure programs were designed to accommodate parameter passing between the calling program and the called stored procedure program.
I will now proceed to illustrate the concepts of implementing stored procedures using a business example, followed by some easy-to-understand programming examples.
Business Application for Stored Procedures
Let’s suppose you are working in the MIS department of Mountain Stereo Equipment (MSE). Your AS/400 has a sales database file named SalesData, as listed in Figure 1. The DDS of the SalesData database file is shown in Figure 2. Since MSE is a fledgling company, your president told you that it is very important that users be able to compute the gross product and sales figures of the various product lines in the mainland
U.S. regions at any time. Right now, only five regions need to be tracked. But as sales go up, regions will be subdivided into other regions. Furthermore, marketing has determined that potential international sales are very promising. As an MIS professional, you realize that this means your sales database file will grow as sales regions and sales increase. You also know that, in the future, you will be using AS/400-based software written in several different languages. These applications will need the same sales information.
Then the network group in your company decides they want some of the latest and greatest client/server technology using Java and Visual Basic. They also want access to the same sales figures from the AS/400 that your president previously mentioned.
Because of all these factors, you decide that you are going to implement a stored procedure to calculate the sales figures. Then, you can simply send the numbers back to the calling program in the form of parameters anytime a request is made. Using this method, you realize that you can get the same answer, from many different directions, no matter what language or platform the request originated from. This will keep everyone happy and keep your programming time down to a minimum, because all the server- and client-based applications will call the same stored procedure program on your AS/400.
Example Programs
Let’s take a look at the stored procedure program named ADDAMTRPG, which will calculate the sales information requested. This program is displayed in Figure 3. It is written in RPG IV and operates as a typical AS/400 subprogram.
The first thing this program does when it is called will be to accept five parameters, which will be the product subtotals and total product sales. These parameters are called SpeakerAmt, CDPlayersAmt, TapeDecksAmt, StereoRecAmt, and TotalAmt. These will serve as our accumulator variables. Notice that these variables are all defined in the D specifications as binary numeric types with a length of 9. I will explain the reason for this later.
When the program begins, the accumulator variables are initialized to zero in subroutine InitVals. Next, subroutine ReadRecs sequentially reads the SalesData database and adds the region product sales to the accumulator variables by executing subroutine AddSales. When the end of the SalesData file is reached, the total product sales are computed by executing subroutine AddTotals. When this step is complete, program control returns to the calling program with the new calculated values loaded into the program parameters.
Now that we have the stored procedure program resolved, let’s take a look at RPG program named DCLCALLRPG, which calls the stored procedure. Program DCLCALLRPG is displayed in Figure 4. After DCLCALLRPG calls stored procedure program ADDAMTRPG, the results are printed to the simple report displayed in Figure 5. Now, let’s see how this happens.
As you can see in program DCLCALLRPG, embedded SQL commands are used. These SQL commands are not native commands present in the specific HLL, in this case, RPG IV. In RPG IV programs, these special SQL commands are delimited by the /EXEC and /END-EXEC commands.
The ExecSQLs subroutine is where these SQL routines are executed. The first SQL command is the Declare Procedure statement. This is probably the most important SQL command issued in this program because it sets up all the critical functions for the program’s execution. The parameter statements follow this code. The Inout SpeakersAmt Integer code fragment specifies the variable SpeakersAmt as an integer numeric type that will be used both as an input and output parameter. IBM documentation recommends using binary variables of a length from 5 to 9 digits to define integer parameters passed to stored procedures.
After the parameter statements, the External Name portion of the Declare statement specifies that program ADDAMTRPG, in library XXX—compiled as an RPG IV language program—will be the program associated with stored procedure SPROC.
The next SQL statement is the SQL Call command. This command calls the declared procedure SPROC with the parameters listed in the Declare statement. When the call is completed, these parameters will have the new values assigned by the stored procedure program. These new values will be printed to the report displayed in Figure 5 by using subroutine PrnArgVals.
Compiling an HLL with embedded SQL statements is a two-step process. First, the SQL precompiler is executed. Then, if there are no errors, appropriate HLL source code for these SQL statements is generated. This generated code is in turn compiled by the HLL compiler. If you use the syntax of the program creation commands listed with the example code, this will automatically be done for you. Because of space limitations, I cannot show all this code. I suggest that you look at it on your own when you compile the example code. You will get a good idea of what these embedded SQL calls are doing by examining the complete SQL and HLL compiler listings.
Calling Stored Procedures from Other HLLs
Stored procedures were designed for use with multiple HLLs. It is not necessary to use the same HLL. This is left up to the programmer’s discretion. In my examples, I will use ILE COBOL and ILE C programs to illustrate this concept.
The ILE COBOL program named DCLCALLCBL is shown in Figure 6. The ILE C program named DCLCALLC is shown in Figure 7. The execution results of the programs are displayed in Figure 8.
The SQL Declare Procedure and SQL Call portions of these programs are similar to the RPG IV version. There are some seemingly trivial differences between the way SQL commands are embedded in the HLLs. Each HLL has some slight differences, but these differences must be respected or the compiler will flag them as fatal errors.
The biggest difference between the original RPG IV version and its ILE COBOL and ILE C counterparts is the inclusion of an SQL Include SQLCA statement. (SQLCA stands for the SQL communications area.) With RPG IV, it is automatically copied. With the ILE COBOL and C versions, it has to be explicitly copied.
The syntax of the client/server-based Java or Visual Basic call to the stored procedure program would be different, but the calculated results would be the same. In the case of Java 1.1, stored procedures are very well supported though the Java Database Connectivity (JDBC) Statement classes. The CallableStatement object is the specific Statement object used by Java 1.1 to call a stored procedure based on the server.
Performance of Stored Procedures
Performance is an important consideration for all applications. Many database experts, who work with a variety of computer platforms and languages, agree that stored procedures are a performance-enhancing tool when used correctly.
The logic behind this contention is that client/server applications take a big performance hit when they are requested to send large amounts of data between the personal computer and the server. For example, a lot of overhead can be saved by using server-based stored procedure programs to access many rows of records on the server, calculating some figures from these records, and then finally sending this information back to the personal computer in the form of parameters. According to certain accounts, some client/server applications actually call hundreds of stored procedures from one screen.
It is beyond the scope of an introductory article on stored procedures to explain and run these client/server tests, but it is certainly prudent to mention it. When you implement a stored procedure, you should be aware of how long it is going to take and what to expect. The best way to get an idea of what to expect in terms of performance implications is by running some tests with the stored procedure program that you want to implement.
Other Applications for Stored Procedures
There are many things that you can do with stored procedures. Although I didn’t do it, you could put other embedded SQL calls in the stored procedure program. I used the parameter type Integer for my examples, but other parameter types exist, such as Time, Date, Character, Decimal, and Float, to mention a few. My SQL HLL programs were compiled as programs, but I could have compiled them as modules or service programs. I used ILE HLLs, but I could have used Original Program Model (OPM) programs. The point is that many possibilities exist for stored procedures, and I just scratched the surface.
The examples used in this article were by design relatively simple. This is because such examples are easier to understand when a new concept is introduced. If you want to learn more about this interesting subject, read though some of the references listed at the end of this article.
As I was going through the IBM stored procedure documentation, I was fascinated by all the different HLL examples I found. I was familiar with a few of the languages, but
many of them I wasn’t. I reasoned that if all these languages found stored procedures useful, then they must be good. Rather than be intimidated by such programs, I found myself even more intrigued by the software elegance of stored procedures.
Stored procedures are a great tool that DB2/400 now provides. I have enjoyed learning about them, and I hope you have as well.
References
DB2/400 Redbook (GG24-4249, CD-ROM QBKARJ02) DB2 for OS/400 SQL Programming (SC41-4611, CD-ROM QBJAQ801) DB2 for OS/400 SQL Reference (SC41-5612, CD-ROM QBJAQ901) Hoffman, Sharon. Referential Integrity and Triggers. Carlsbad, California: Midrange Computing. Marchesani, Skip. DB2/400. Carlsbad, California: Midrange Computing.
REGION SPEAKERS CDPLAYERS TAPEDECKS STEREOREC
Midwest 123,000 135,000 140,000 125,000 Northeast 135,000 145,000 190,000 185,000 Northwest 165,000 145,000 112,000 134,000 Southeast 125,000 115,000 130,000 155,000 Southwest 150,000 125,000 175,000 200,000
Figure 1: SalesData database file for product sales information
*===========================================================
* Physical File Text : Mountain Stereo Equipment Sales
*===========================================================
* To compile:
*
* CRTPF FILE(XXX/SALESDATA) SRCFILE(XXX/QDDSSRC)
*
*===========================================================
A R SALESDATR
A REGION 10A TEXT(‘Sales Region’)
A SPEAKERS 7S 0 TEXT(‘Speaker Sales’)
A CDPLAYERS 7S 0 TEXT(‘CD Players Sales’)
A TAPEDECKS 7S 0 TEXT(‘Tape Deck Sales’)
A STEREOREC 7S 0 TEXT(‘Stereo Receiver Sales’)
A K REGION
*=============================================================
* To compile:
*
* CRTBNDRPG PGM(XXX/ADDAMTRPG) SRCFILE(XXX/QRPGLESRC)
*
*=============================================================
FSalesData if e k disk
*
Figure 2: DDS for SalesData file
* Define accumlator values as binary values
DSpeakerAmt s 9b 0
DCDPlayersAmt s 9b 0
DTapeDecksAmt s 9b 0
DStereoRecAmt s 9b 0
D*TotalAmt s 9b 0
* Mainline - Accept program parameters
C *entry plist
C parm SpeakerAmt
C parm CDPlayersAmt
C parm TapeDecksAmt
C parm StereoRecAmt
C parm TotalAmt
*
C exsr InitVals
C exsr ReadRecs
C return
*
* Read SalesData until EOF, accumulate sales figures
C ReadRecs begsr
C *loval setll SalesData
C read SalesData 99
C dow (not *IN99)
C exsr AddSales
C read SalesData 99
C enddo
*
C exsr AddTotals
*
C endsr
* Routine to add sales to accumulator values
C AddSales begsr
C add Speakers SpeakerAmt
C add CDPlayers CDPlayersAmt
C add TapeDecks TapeDecksAmt
C add StereoRec StereoRecAmt
C endsr
* Routine to compute total product sales
C AddTotals begsr
C eval TotalAmt = ( SpeakerAmt +
C CDPlayersAmt +
C TapeDecksAmt +
C StereoRecAmt )
C endsr
* Routine to initialize accumulator values and EOF indicator
C InitVals begsr
C move *off *in99
C z-add *zeros SpeakerAmt
C z-add *zeros CDPlayersAmt
C z-add *zeros TapeDecksAmt
C z-add *zeros StereoRecAmt
C z-add *zeros TotalAmt
C endsr
*==========================================
* To compile:
*
* CRTSQLRPGI OBJ(XXX/DCLCALLRPG) SRCFILE(XXX/QRPGLESRC)
*
*============================================ Fqsysprt o f 132 printer oflind(*inof)
*
* Define accumlator values as binary values
DSpeakerAmt s 9b 0
DCDPlayersAmt s 9b 0
DTapeDecksAmt s 9b 0
DStereoRecAmt s 9b 0
DTotalAmt s 9b 0
* Mainline
C exsr ExecSQLs
C exsr PrnArgVals
C eval *inlr = *on
*
C ExecSQLs begsr
Figure 3: Stored procedure to compute gross product sales
*
* Declare SQL procedure SPROC, integer parameters,
* OS/400 external name, program type and general program call
*
C/EXEC SQL DECLARE SPROC PROCEDURE(INOUT SpeakerAmt INTEGER,
C+ INOUT CDPlayersAmt INTEGER,
C+ INOUT TapeDecksAmt INTEGER,
C+ INOUT StereoRecAmt INTEGER,
C+ INOUT TotalAmt INTEGER)
C+ (EXTERNAL NAME XXX/ADDAMTRPG LANGUAGE RPGLE
C+ GENERAL)
C/END-EXEC
* Execute SQL procedure SPROC using integer parameters
*
C/EXEC SQL CALL SPROC (:SpeakerAmt, :CDPlayersAmt,
C+ :TapeDecksAmt, :StereoRecAmt,
C+ :TotalAmt )
C/END-EXEC
C endsr
*
* Print report with returned parameter values
*
C PrnArgVals begsr
C except Heading1
C except Heading2
C except ArgDetail
C endsr
*
Oqsysprt e Heading1 1
O 42 ‘Mountain Stereo Sales’
Oqsysprt e Heading2 1
O 14 ‘Speakers’
O 29 ‘CD Players’
O 44 ‘Tape Decks’
O 59 ‘Receivers’
O 74 ‘Total Sales’
Oqsysprt e ArgDetail 1
O SpeakerAmt J 15
O CDPlayersAmt J 30
O TapeDecksAmt J 45
O StereoRecAmt J 60
O TotalAmt J 75
*=============================================================
* To compile:
*
* CRTSQLCBLI OBJ(XXX/DCLCALLCBL) SRCFILE(XXX/QCBLLESRC)
*
*=============================================================
IDENTIFICATION DIVISION.
PROGRAM-ID. DCLCALLCBL.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Access SQL Communication Area
EXEC SQL
Figure 4: RPG IV program to declare and call stored procedure
Mountain Stereo Sales Speakers CD Players Tape Decks Receivers Total Sales 698,000 665,000 747,000 799,000 2,909,000
Figure 5: Printed output from DCLCALLRPG
R
INCLUDE SQLCA
END-EXEC.
* Define accumlator values as binary values
01 ArguementValues.
05 SpeakerAmt PIC S9(9) BINARY.
05 CDPlayersAmt PIC S9(9) BINARY.
05 TapeDecksAmt PIC S9(9) BINARY.
05 StereoRecAmt PIC S9(9) BINARY.
05 TotalAmt PIC S9(9) BINARY.
PROCEDURE DIVISION.
MAINLINE.
INITIALIZE ArguementValues.
* Declare SQL procedure SPROC, integer parameters,
* OS/400 external name, program type and general program call
EXEC SQL DECLARE SPROC PROCEDURE(INOUT SpeakerAmt INTEGER,
INOUT CDPlayersAmt INTEGER,
INOUT TapeDecksAmt INTEGER,
INOUT StereoRecAmt INTEGER,
INOUT TotalAmt INTEGER)
(EXTERNAL NAME XXX/ADDAMTRPG LANGUAGE RPGLE
GENERAL)
END-EXEC.
* Execute SQL procedure SPROC using integer parameters
EXEC SQL CALL SPROC (:SpeakerAmt, :CDPlayersAmt,
:TapeDecksAmt, :StereoRecAmt,
:TotalAmt )
END-EXEC.
* Display results to console
DISPLAY “SpeakerAmt is : “ SpeakerAmt.
DISPLAY “CDPlayersAmt is : “ CDPlayersAmt.
DISPLAY “TapeDecksAmt is : “ TapeDecksAmt.
DISPLAY “StereoRecAmt is : “ StereoRecAmt.
DISPLAY “TotalAmt is : “ TotalAmt.
GOBACK.
END PROGRAM DCLCALLCBL. /*============================================================*/
/* To compile: */
/* */
/* CRTSQLCI OBJ(XXX/DCLCALLC) SRCFILE(XXX/QCSRC) */
/* */
/*============================================================*/
#include
main()
{
/* Define accumlator values as binary values */
signed long int SpeakerAmt, CDPlayersAmt;
signed long int TapeDecksAmt, StereoRecAmt, TotalAmt;
/* Access SQL Communication Area */
EXEC SQL INCLUDE SQLCA;
/* Declare SQL procedure SPROC, integer parameters, */
/* OS/400 external name, program type and */
/* general program call */
Figure 6: ILE CBL program to declare and call stored procedure
EXEC SQL DECLARE SPROC PROCEDURE (INOUT SpeakerAmt INTEGER,
INOUT CDPlayersAmt INTEGER,
INOUT TapeDecksAmt INTEGER,
INOUT StereoRecAmt INTEGER,
INOUT TotalAmt INTEGER)
(EXTERNAL NAME XXX/ADDAMTRPG LANGUAGE RPGLE
GENERAL);
/* Execute SQL procedure SPROC using integer parameters */
EXEC SQL CALL SPROC (:SpeakerAmt, :CDPlayersAmt,
:TapeDecksAmt, :StereoRecAmt,
:TotalAmt);
/* Display results to console */
printf(" SpeakerAmt is : %d", SpeakerAmt);
printf(" CDPlayersAmt is : %d", CDPlayersAmt);
printf(" TapeDecksAmt is : %d", TapeDecksAmt);
printf(" StereoRecAmt is : %d", StereoRecAmt);
printf(" TotalAmt is : %d", TotalAmt);
}
CALL PGM(DCLCALLCBL)
SpeakerAmt is : 000698000
CDPlayersAmt is : 000665000
TapeDecksAmt is : 000747000
StereoRecAmt is : 000799000
TotalAmt is : 002909000
CALL PGM(DCLCALLC) :
SpeakerAmt is : 698000
CDPlayersAmt is : 665000
TapeDecksAmt is : 747000
StereoRecAmt is : 799000
TotalAmt is : 2909000
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online