Creating Stored Procedure in DB2

vanessaspring

The original link is: http://www.devx.com/IBMDB2/Article/28308?trk=Stinger_PartnerNews

Creating Stored Procedures in DB2
If you're a DB2 developer and don't use stored procedures, or worse you don't know how, then you're missing out on one of its best features. This walkthrough gets you started by stepping through the creation of a simple SQL Procedure.  

by Justin Whitney         June 3, 2005

IBM's DB2 Universal Database gives developers fabulous new superpowers for building high performance database functionality. If you've used other databases, then you're probably familiar with the basic SQL stored procedure, which can add power and performance to your app. DB2 goes even further, allowing you to create external procedures in the language you're most comfortable with, whether it's Visual Basic .NET, C#, or Java.

If you're new to this topic, be sure to check out "Introduction to DB2 Stored Procedures" for all the basic information. The following walkthrough will demonstrate how to actually create one of these pearls of goodness.

What This Walkthrough Covers
DB2 gives you a lot of options, both in what kind of stored procedure to create and in how to create it. For this demonstration, you'll start with a sample database and project setup. Then you'll use a SQL Procedure creation wizard, and SQL Assist, to build the UPDATE query that will serve as the basis for your procedure. After that, I'll show you a different way to look at and tinker with stored procedures so that you can add some conditional logic and blow open the functionality of your basic procedure. Finally, of course, you'll test it all.

The Scenario
Though you can create a stored procedure for just about any database operation, the power of the procedure is wasted on simple SELECT queries. So to show off what DB2 is capable of, I'm going to create a procedure that does several things at once.

When managing a simple email opt-in list, you generally execute several types of operations on the table, depending on conditions:

    * INSERT new subscribers if they don't already exist
    * UPDATE existing subscribers to inactive if they opt-out
    * UPDATE existing subscribers to active if they opt back in

I'm sure you can think of more. But we'll start with these. One of the biggest advantages to using a stored procedure over programmatic database calls is the reduction of network traffic and the speed advantage of using your pricey database servers for executing compiled logic. Well, that's two reasons. A third, and my focus in this scenario, is to encapsulate several similar operations into one identical database call. When coding, I like to streamline as much as possible. Using a stored procedure lets me throw my subscriber data over to the database without having to do all the conditional checking within the code.

This also gives me more options in language platforms. For instance, if I code my app in .NET, then switch to Cold Fusion, then switch to Java (you laugh?), then this is a huge chunk I don't have to port. All I change are the database calls.

DB2 Universal Database for Linux, UNIX and Windows- Download Now!
DB2 is the fastest growing database on UNIX and Windows. Judge for yourself -- get DB2 Version 8 via free download.

Step 1: Prepare the Sample Spamalot database
If you haven't already downloaded and installed DB2, you can do that here. Once everything is set up and ready to go, open the Control Center.

   1. DB2 gives you many ways to create a new database, including command-line utilities. For now, select the All Databases folder on the left and click Create New Database in the lower right panel.
   2. For Database Name, enter "Spamalot" (or some other name if you don't like my joke). Click Next.
   3. Since this is just an example, I'll say "No" when asked to set a maintenance strategy. Same with providing an SMTP server - just leave it blank and hit Next. And Finish to kick off the database creation process.
   4. Once the database has been created, expand it in the left pane, then select the Tables folder. Click Create New Table in the lower right pane, again one of many different ways to do the job.
   5. Since this example is going to be super-easy, you'll create just one table with two fields. For Table Name, enter "EMAILS" and click Next.
   6. Using the Add column on the right, give the table two fields: EMAIL (varchar: 255) and ACTIVE (smallint; default =1). Click Next.
   7. Click Next again to bypass storage space specification.
   8. Define EMAIL as the Primary key by clicking "Add Primary" and selecting the field.
   9. Click Next through the Clustering definitions and Check Constraint definitions.
  10. When you're ready to "review the actions that will take place when you click Finish," then take a look at the table-generation code if you want, by clicking "Show SQL." Then click Finish. You should get a confirmation message that the table creation was successful.
  11. Find the table in the Tables folder and double-click to open it. Add a few starter rows for testing and development purposes:
      john@aol.com         1
      carol@yahoo.com         1
      bob@msn.com         0
  12. Click Commit, then Close.

Now we have the database, the table, and some simple sample data. Time to make a stored procedure.

Step 2: Create the Project
Stored procedures are created not in the Control Center, but in the Development Center. So click the Development Center icon or open it from your Start menu. If your Launchpad is enabled, you'll see an option to Create Project. You can also choose either Project ->; New or Project ->; Launchpad from the main menu to get to the same place.

  13. Click Create Project
  14. Call the new Project "SpamSproc" and click Next.
  15. In the Launchpad, click Add Connection.
  16. Choose an Offline Connection Type and click Next.
  17. In the Database dropdown, choose Spamalot, the database you just created. Leave all the other fields set to their defaults. Click Next.
  18. Under Options, you don't need to specify a SQL Schema, so just click Next.
  19. At the Summary, click Finish.
  20. Now you're ready to create the SQL Procedure. Click Create Object to start off the wizard. (This is the same as right-clicking on the Stored Procedures folder within the Project View and selecting "New", then "Stored Procedure Using Wizard." Once you've closed the Launchpad you'll see what I'm talking about.

By the way, if at some point you're prompted for a login and password, you should be able to just check the box that says "Use your current ID and password," assuming you didn't do anything fancy with your passwords.

Step 3: Start the SQL Procedure
As a reminder, the real-world scenario for this stored procedure is to serve as an all-in-one place for managing my email list. When I get a new subscriber, I want to send that info to this procedure for an INSERT. If I get an opt-out for an existing subscriber, I want to UPDATE using the same procedure The pseudo-code for this looks like:

if (Status = active) then
        if (Email already exists) then
                update Status to active
        else
                add Email
else
        update Status to inactive

  21. In the New Object wizard, make sure Stored Procedure is selected on the left side and SQL is selected on the right side. Click OK.
  22. For the procedure name, I'ma call this one MANAGE_EMAIL_SQL. Click Next.
  23. On the Definition page, keep all of the defaults and click on the ellipses (…) next to "Single SQL Statement" in the Statement field. You should see the SQL Statement generator.
  24. Leave the "Generate one SQL statement" option selected but click on "SQL Assist" to the right of the existing statement.
  25. Under Statement Type, select "UPDATE," then OK to nuke the "entire statement" (which, at this point, effectively consists of nothing).
  26. To build the statement, you'll be selecting elements of the database from the icons in the left pane. You'll pull together the entire statement before clicking OK at the bottom, so resist the temptation to confirm something until you're done with the entire statement. Click on UPDATE (Target Table) and you'll see a list of available tables, including system tables. Chances are, the EMAILS table you created earlier will be collapsed under "Administrator" or whatever else you have set for your admin name (not saying what mine is). Wherever it is, select it and click the ">;" symbol to the right.
  27. Click SET (Row values) in the left pane. In the row for the ACTIVE column, put a "1" under Value. Notice that you don't have any wizard tools for conditional statements, so for now you'll build just one of the conditions and add the If statements later.
  28. Click WHERE (Row filter) in the left pane. Select "EMAIL" for the Column, leave Operator as "=" and select "Host Variable" for Value. In the pop-up, enter a parameter name. I'm going to defy all naming conventions and call it "em", short for "Email" (or Dorothy's Auntie, or half of a candy-coated chocolate or a world-famous rapper). Finally, click ">;" to set it in the Search Condition window. Obviously, you can have several conditions, with Boolean operators and other interesting variations. But for now, this will suffice.

At this point, you should see the following SQL code in the bottom pane:

UPDATE ADMINISTRATOR.EMAILS EMAILS
   SET ACTIVE = 1
   WHERE EMAILS.EMAIL = :em   

Step 4: Finish the Wizard

  29. If you want, you can add the rest of the SQL code now from this very screen. You can also test the procedure by clicking Run. Because I like to save as I go, I'll click OK and finish the wizard before screwing it up.
  30. On the SQL Statement screen, click OK.
  31. On the Definition screen, click Next.
  32. I can, of course, add my parameter for the "ACTIVE" field later, but I'll go ahead and let the wizard do it for me now. So on the Parameters screen, click "Add".
  33. In the Add Parameter pop-up, select "In" for Parameter mode. For Name, I like "ac," which stands for "Active" (or air conditioner, or alternating current, or half of one of the great hair bands of the 80s). SQL type will be "SMALLINT." No Comments at this time. OK. Next!
  34. Under Options, you can give the procedure a Specific Name, which is used when dropping or commenting on the procedure. Though it doesn't have to be, a lot of people use the procedure name, so I'll enter "MANAGE_EMAIL_SQL" and click Next.
  35. Glance quickly over the Summary page and click Finish before you find any mistakes.

In the Output View panel of the Development Center, you should see such things as:

ADMINISTRATOR.MANAGE_EMAIL_SQL - Build started.
ADMINISTRATOR.MANAGE_EMAIL_SQL - Create stored procedure completed.
ADMINISTRATOR.MANAGE_EMAIL_SQL - Build successful.

Sometimes this or other panels can get squeezed and hidden in your layout. If you don't see the Output View, then click View ->; Output View from the menu and/or resize some of the panels until it shows up.

Step 5: Add If...Then Statements
Just for kicks, go ahead and test the procedure now. In the Project View, right-click the procedure and choose "Run..." You should get a dialog box for the two parameters you specified. Enter "bob@msn.com" for "em" and 1 for "ac". Click OK.

Your Output View should spew:

ADMINISTRATOR.MANAGE_EMAIL_SQL - Calling the stored procedure.
ADMINISTRATOR.MANAGE_EMAIL_SQL - Returned 0
ADMINISTRATOR.MANAGE_EMAIL_SQL - Run completed.

Since this is an UPDATE, you won't have a resulting recordset, nor will you have a return value since we specified no OUT parameters. But as long as it says "completed," you're good. And if you were to look at the data in the EMAILS table, you would see that our friend Bob is now Active—his 0 has been changed to a 1.

  36. Right-click the procedure again and this time choose "Edit..." The contents of the procedure should appear in the Editor View pane. It's beneath all the toolbars, so you may need to resize to see the code.
  37. The first thing you'll notice is that the procedure wizard added a lot of additional code. That's one of the things I love about using wizards. Scroll all the way down, to the end of the "1" section. That's where you see the good stuff. Go to the line just above the UPDATE statement.
  38. Add the following SQL code:

IF ac = 1
      THEN
        IF EXISTS (SELECT * FROM EMAILS WHERE EMAIL = em)
          THEN

  39. Go to the end of the WHERE clause of your query, hit enter, and add the following:

           ELSE
             INSERT INTO EMAILS
               VALUES (em, 1);
        END IF;
      ELSE
        UPDATE EMAILS
            SET ACTIVE = 0
            WHERE EMAIL = em;
    END IF;

  40. Feel free to adjust your spacing so it all looks nice. The final result of the entire stored procedure should look something like this. (Note: automatic comments have been deleted and the parameters have been wrapped to a second line to fit on the screen here. I also tightened up the existing UPDATE query a little bit.)

CREATE PROCEDURE ADMINISTRATOR.MANAGE_EMAIL_SQL
( IN em VARCHAR(255), IN ac SMALLINT )
    SPECIFIC ADMINISTRATOR.MANAGE_EMAIL_SQL
    DYNAMIC RESULT SETS 1

P1: BEGIN
    IF ac = 1
      THEN
        IF EXISTS (SELECT * FROM EMAILS WHERE EMAIL = em)
          THEN
             UPDATE EMAILS
               SET ACTIVE = 1
               WHERE EMAIL = em;
           ELSE
             INSERT INTO EMAILS
               VALUES (em, 1);
        END IF;
      ELSE
        UPDATE EMAILS
            SET ACTIVE = 0
            WHERE EMAIL = em;
    END IF;
END P1

As mentioned before, all of this could have been typed in at the beginning of the wizard, without even using SQL Assist. But what fun is that? Besides, it's always good to know what's available so you can ignore it later.

  41. On the Editor View toolbar, click the Save Object icon (or Ctrl-S, or File ->; Save Object) and the Build icon (or Ctrl-B, or Selected ->; Build). Hopefully, you will see the following in the Output View. Best of luck.

ADMINISTRATOR.MANAGE_EMAIL_SQL - Build started.
DROP SPECIFIC PROCEDURE ADMINISTRATOR.MANAGE_EMAIL_SQL
ADMINISTRATOR.MANAGE_EMAIL_SQL - Drop stored procedure completed.
ADMINISTRATOR.MANAGE_EMAIL_SQL - Create stored procedure completed.
ADMINISTRATOR.MANAGE_EMAIL_SQL - Build successful.

Step 6: Test the Procedure

  42. So many ways to test it, where to begin. The easiest will be to run it from here and check the results in the Control Center. Go back to the Control Center and close the EMAILS table if it's already open.
  43. Back in the Development Center, click the Run icon on the Editor View toolbar (or Ctrl-R, or Selected ->; Run). You can also right-click the procedure in the Project View.
  44. Start with making Bob inactive again. For "em" enter "bob@msn.com" and for "ac" use 0. Click OK. In the Control Center, double-click on the EMAILS table. Bob should have an ACTIVE field of 0. Close the table.
  45. Just for kicks, let Carol opt-out of the mailing list, as well. Enter "carol@yahoo.com" and 0, then click OK. Again, check the EMAILS table to make sure it got changed.
  46. Add a new subscriber. Enter "alice@google.com" and 1, then click OK. Feel free to check this as well.
  47. Lastly, Bob had another change of heart. Re-activate him again. Enter "bob@msn.com" and 1. Click OK.

At this point, the records of your EMAILS table should look similar to this:

john@aol.com                 1
carol@yahoo.com         0
bob@msn.com                 1
alice@google.com         1

Conclusion
Hopefully, you now grasp the basics of creating a SQL Procedure. This walkthrough doesn't begin to cover the power and versatility of DB2's implementation of stored procedures. The wizard you used here can also be used to create an external Java procedure. You can also create procedures in other languages, such as VB.NET or C#. Lastly, this same functionality and more can be accomplished through DB2's rich set of command-line utilities.

Use this as your launching point, but don't stop now. Explore. Try new things. You may be surprised at what you can do.
Page 1 of 1
Justin Whitney is a regular contributor to DevX.com and Jupitermedia. He currently lives in San Francisco, where he consults for leading high-tech firms and writes about emerging technologies.