Midrange Programmer

I recently received an e-mail from a reader asking a question about stored procedures. She didn't seek information related to creating or using stored procedures; rather, she wanted to how to find her procedures once she had written them. Well, this being the last issue of Midrange Programmer in 2002, I have decided to spread some holiday cheer and answer this reader's question by combining some of the techniques published this past year, sprinkled with some brand new information. Come take a peek.

A stored procedure is a program, or procedure, that is called using an SQL call statement. On the iSeries, this stored procedure can be written in SQL, as it is on other platforms, as well as any other high-level language used on the iSeries. That's right! You can write stored procedures in RPG, COBOL, CL, C, and Java. The difference between stored procedures and plain old programs is that the database, not OS/400, manages the stored procedure. This is no big deal if you only plan to call these programs from within the iSeries; you don't need stored procedures for that. However, employing stored procedures is a great way to extend your data and business rules to the outside world. They allow applications outside the iSeries that use ODBC and JDBC to very easily call iSeries applications using SQL. Check out my article "Call iSeries Programs Using SQL" to learn more about stored procedures. Stored procedures are stored and managed by DB2 and are not a part of any specific table or view. As such, there is no native OS/400 command with which to view stored procedures. You can start to understand what prompted the reader's question. In this article, I will briefly discuss the only official way (that I know of) to view stored procedures on the iSeries. I will also share with you my unofficial, homegrown method, which those of you who can't access the first method can use. My homegrown version is only the beginning, sort of a building block that can be extended a number of ways, depending on your specific needs.

If you use Operations Navigator, V5R1 or later, you have the ability to view all DB2-related objects, including stored procedures, inside of a specific library. This is accomplished by clicking the iSeries of your choice inside your left Explorer-like window. Next, click Database to expand the list of database functions, then click libraries to expand the list of libraries in your job's library list. Once you have a list of libraries, click one of the libraries to display its DB2 contents in the right-hand window. Right-click the stored procedure of your choice, and you'll get a menu that allows you to view the properties and permissions of the stored procedure; delete the stored procedure; or--and this is way cool--generate the SQL that was used to create the stored procedure. To check out some other SQL-related tasks that you can perform using Operations Navigator, check out "Run SQL Scripts Using Operations Navigator." Well, that's all there is to it, right? Not exactly. The technique I showed you works really well if you use OpsNav in your shop. But what if you don't? How, then, are you supposed to view information about stored procedures? That's where my homegrown starter kit comes in.

There are no commands or tools available on the iSeries that allow you to view information on stored procedures, so I decided to write one. I knew that information about stored procedures had to be contained somewhere on the '400 because they are DB2 objects. But where? With a little investigation, I was able to find a table on the iSeries that contains all the information about stored procedures. With that table as a starting point, I have built the basics of a command that can be used to view information about one or more stored procedures. It really wasn't too difficult.

First, I developed a command, called DSPSTRPRC (Display Stored Procedure), to allow users to request a stored procedure, or library of stored procedures, to view. The source for the command, DSPSTRPRC, is included with this article. There is not much to this command. It basically allows the user to key in a stored procedure and library name. The user can also enter *ALL for one or both of these parameters. As a part of the command, I employed a validity-checking program, DSPSTRPRCV, to validate that a correct library is entered into the library parameter. If *ALL is entered, the validity checking logic will not run. Once the validity-checking program is written and compiled, it can be linked to the command during command compilation by placing the name of the validity-checking program in the validity checking program parameter. To learn more about creating homegrown commands, check out "Back to Basics: Homegrown OS/400 Commands." For more information on validity checking programs, take a look at "Add Oomph to Your Commands with Validity Checking Programs."

Next, I needed a program to actually retrieve the stored procedure information. I accomplished this with a fairly straightforward subfile program. I say fairly straightforward because I added a twist. I wanted to add some flexibility, so that you, the reader, could more easily extend the capabilities of my program. Therefore, I employed some dynamic SQL sorting and selection techniques, as opposed to traditional record level logic or open query file techniques. The display file, DSPSTRPRCD, and the RPG program, DSPSTRPRCR, are pretty standard subfile fare. I am going to create a self-extending subfile, which means that I will build one page at a time in my program but allow the subfile to be added to, if the user wants to see more information. This also means that my program will share paging responsibilities with OS/400. OS/400 will handle all paging tasks for data already in the subfile, and my program will be responsible for adding new records if the user wants to scroll past the end. We at Midrange Programmer have written quite a few advanced subfile articles this past year, but if you're new to subfiles, you may want to check out my book Subfiles in RPG IV: Rules, Examples, Techniques and Other Cool Stuff. (Hey, 'tis the season to shamelessly plug. Besides, the book would make a great holiday gift.)

The SQL twist allows me to easily and dynamically sort and select based on what the users request. There's not much to the selection logic. I build the Where clause portion of an SQL Select statement based on what the user enters in the command parameters. By placing the selection responsibility on DB2 instead of inside my RPG code, I've added flexibility by separating the subfile logic from the business logic. I've also added efficiency by allowing DB2, not my program, to process the data selection.

I've also included dynamic sorting logic using SQL. SQL is not the only method to accomplish this task, but it is the best (in my opinion). You could use multiple logical views and add IF logic inside your subfile build routine or have multiple subfile build routines based on how the data is going to be sorted. Or you could use Open Query File in a CL program to sort the data. However, those methods are more convoluted and don't allow for as much flexibility as SQL. Besides, if you decide you want secondary, or beyond, sorting capabilities, SQL is the way to go. In my example, there are only two fields to sort by, but you may have different ideas about how to use this command, and you may want more data displayed on the subfile, but whatever the case, I've built in the flexibility for you. The program allows the user to press the F4 key to bring up a window containing a list of field names to sort by. The user can then select a field by placing anything in the corresponding input field and pressing Enter; the subfile will redisplay, only this time it will be sorted by the desired field. Again, using this method will allow you to easily add multiple sorting criteria without disrupting your program too much. In fact, you'd only have to modify the SORT subroutine. I go into the SQL sorting and selection techniques, as well as all the embedded SQL syntax and compiling details, in "Empower Users with Embedded SQL".

Once you compile the validity checking and processing programs, you will link them to your command during command compilation. You will see a parameter that will hold each respective value. If you've read my previous articles, you already understand this. Now you have the beginnings of a command that will allow you to view the stored procedures on your system. From the subfile, you can do many things. For one, you could allow the user to select a specific subfile record and view the details of a stored procedure. There is a lot of data in the SYSPROCS file that can be viewed. Or you could decide to display more information in the subfile and allow for more sorting and selection capabilities. One thing that I'm working on right now is trying to replicate the OpsNav technique of generating the SQL used to create the stored procedure. Whatever the case, you can use my source code as the building blocks for a tool that will fit your needs. It's my gift to you. Enjoy!