© 2002 International Business Machines Corporation. All rights reserved.
Newton's third law states that for every action, there is an equal and opposite reaction. I hope you are still there. Don't worry, I'm not giving you a physics lesson. In this article, I will be guiding you through the concept of cause and effect as implemented in a database application that brings together the concepts of DB2 triggers, Java TM user-defined functions (UDFs), and the JavaMail API.
Here's a high-level overview of what I want to show:
- A record in a DB2 © database is changed.
- If the change causes the data record to meet certain conditions based on Boolean logic, a database trigger will invoke a Java UDF.
- This Java UDF uses the JavaMail API to send an e-mail to a specified recipient.
Before setting up our experiment, let's provide a real-world example where we might see it used. Let us say that we are a credit card company. Customers have requested that they be notified via e-mail when they are nearing their credit card limit.
Figure 1 depicts what we want to accomplish. To get a bit more specific as to the criterion that must be met before an e-mail is sent, let's assume that a user must be within 15% of their credit limit before we send off a message.
Figure 1. Flow from database update to e-mail confirmation
For our endeavor, we will need a database to play with. Of course, a true credit card system's database would probably be much more complex than the simplistic model I am about to present. However, for learning purposes, our example will be kept simple.
First we'll create our database with the
following SQL statement:
create db cardDB
Of course, we must connect to our database to do
connect to cardDB
- Next, we need a table to store all of our customer data. Table 1 gives you an idea of the columns that will exist for each customer row.
- Now we need to populate the table with some dummy data values. Table 2 is a taste of some sample data that we might have in our database.
Important: For our sample data, we need to keep the current balance of our "customers" lower than 85% of their credit limit when we first populate the table. Customer records that have a current balance greater than 85% even before our trigger is created won't fall under the scrutiny of our system.
To insert the first row of data shown in Table 2 , use the following SQL:
insert into cardHolderTable values ('Bhogal','Kulvir','email@example.com',3753.00,5000.00)
Go ahead and execute similar statements to populate your sample database with the rest of the dummy data records shown in Table 2 .
Going back to our "cause and effect" theme, we need to define both "cause" and "effect". If you're reading this article the first time through for understanding, please go ahead with the cause section. But, if this is the second time through, and you're trying it out now, please jump down to the effect section. You must create the effect (the UDF) before you create the cause (the trigger), so that when your trigger is ready to call the UDF, it will be there.
Now let's define our "cause." Do this by creating a DB2 trigger.
create trigger spawnEmailTrigger after update of currentBalance on cardHolderTable referencing old as oldrow new as newrow for each row mode db2sql when (newrow.currentBalance>(oldrow.creditLimit*.85)) (values(mailer(oldrow.emailAddress,oldrow.lastName,oldRow.firstName,newRow. currentBalance,oldRow.creditLimit)))
Let's look at the create trigger statement above. If you are unfamiliar with triggers, a one-sentence crash course will inform you that triggers are a set of actions that occur as the direct result of a delete, insert, or update operation on a specific table. No, this is not another Newtonian definition. This is DB2 we are talking about!
Triggers are an extremely powerful functionality made available to the database programmer. In this article, we present only one of the many possible uses of a trigger. To learn more about triggers, see the Information Center for your version of DB2.
The syntax is actually rather simple. In our case, our trigger is the result of an update operation. The way we plan to emulate a transaction in the system where our customer owes the credit card company more is to update the currentBalance column. (Our mock credit card tracking system is very crude. I would seriously suggest that you start worrying if your credit card company uses such a skeletal system. However, for our example it will work just fine.)
Let's go back to the trigger specification:
We named our trigger
afterindicates that our e-mail is generated after we manipulate the DB2 table (as opposed to before we update it).
currentBalance on cardHolderTablenarrows down what database action we want our trigger to look out for. In short, we want the trigger to be activated if an update of the
currentBalancecolumn of the
mode db2sqlis required.
Next, we narrow down when our trigger will fire with some Boolean logic. With the clause
when (newrow.currentBalance>(oldrow.creditLimit*.85)), we specify that our trigger will only fire when the currentBalance has been updated to a value which is greater that 85% of the customer's credit limit. It is important to note how we can refer to our pre-database manipulation values and post-database manipulation values when an update occurs. This power allows us to perform our calculation and determine if the trigger should in fact be fired.
Accordingly, when an update is performed
which throws our customer in the "red zone" of
within 15% of their credit card limit, we call our
UDF to spawn an e-mail with the clause:
We'll talk about our UDF in the next section. The point to take note of here is that we pass arguments to our UDF function (named "mailer") that will be used to construct our customized e-mail.
In our trigger, we call our Java UDF if our specified conditions have been met. But what UDF is being referred to? We'll take some time to answer that question. However, first we must prepare for our UDF's grand entrance.
The task of e-mailing that we plan on performing revolves around the JavaMail API. You may or may not have the files you need to use this program interface. Depending on the Java version you are using, the jar files you need may already be at your disposal. I will be a pessimist and say they aren't. If you in fact do not have the jar files you need, you don't need your credit card. The JavaMail API is free and available for download .
I am going to assume that you are familiar with the JavaMail API. If you are not, try a tutorial .
Assuming that you have the jar files associated with the JavaMail API installed on your hard drive called mail.jar and activation.jar, we need to let DB2 know where these are. We will install the jars on the database server under the names "Mail" and "Activation."
You can do this by issuing the following statements:
In the statements above, we are assuming that the jar files of interest are in your temp directory. You will need to adjust the above statements to point to your correct jar file location.
Now, let's move on to the Java UDF code. You may want to download the code and spend some time to study it. The documentation provided with the code will guide you through the use of the JavaMail API to create and send the customized e-mail message.
By decree of how things have to be done, our method of interest, mailer, is a static method that is public and returns a String. Your UDF method must have a return type. Our incoming method parameters are used to customize our e-mail and inform the intended recipient about their being in the "red" with their credit card balance.
Before you can use your Java UDF, you must perform some preliminary steps.
After you compile the JavaUDF.java file, place
the resulting class file into the sqllib/function
directory of the DB2 instance that will use the
function. If you are using Windows NT and the
default install options, your drop location would
Next, you must register the newly created UDF so DB2 can become aware of its existence.
create function mailer(recipientEmail varchar(30), lastName varchar(30), firstName varchar(30), currentBalance decimal(7,2), creditLimit decimal(7,2)) returns varchar(70) fenced variant no sql external action language java parameter style java external name 'JavaUDF!mailer'
- Let's take some time out to analyze this registration statement. In this CREATE FUNCTION statement, we specify what our function's name will be in DB2. In our example, we use "mailer."
We must also specify what the expected SQL parameters of our function will be. It is important to note that we use
parameters here and Java variable types in
our actual Java UDF. If you take a look at the Java
code juxtaposed with the UDF registration
statement, you will see that for varchars, our
corresponding Java variable type is a String.
Similarly, for the SQL type of decimal (7,2), the
Java type of type of java.math.BigDecimal is used.
This one-to-one matching of SQL to Java types is
necessary to make things go smoothly. The signature
of the mailer method is shown below.
public static String mailer(String input, String lastName, String firstName, BigDecimal currentBalance, BigDecimal creditLimit)
- Going back to our registration statement, we indicate that our function will return a varchar(70). This is used to house the progress statement from our UDF.
- We use a fenced function since they tend to be safer than non-fenced functions. Without delving too deep into the subject, fenced functions run independently from the internal resources of the database manager, making them less of a threat to the manager if bugs occur.
- Moving along, we state that our function contains no sql, meaning that it contains no SQL statements.
- The external action clause indicates that our function performs an action which is affecting the world outside of the database.
- With the clause "language java parameter style java," the database is informed that the language and parameter style of our UDF is Java.
- Finally, using the clause "external name 'JavaUDF!mailer'," we inform the database that the Java class file it should look for is "JavaUDF" and the particular function of interest that is being registered is the method "mailer."
Restrictions: It is important to note an inherent shortcoming of our setup. Our external e-mailing action has no way of being informed of transactions that are not successful (e.g., a balance update that is aborted via a Web-based form). Consequently, an e-mail could be spawned even if a database transaction is not carried through.
If you have followed the steps outlined so far without any problems, you are on the verge of seeing the fruits of your labors blossom.
To recap our endeavor, by issuing an update statement that would put a particular credit card customer within 15% of their credit limit, an e-mail should be fired off by the trigger via the Java UDF you created.
Given that you are using the prescribed dummy data values I suggested above, you can test our experiment with the statement:
update cardHolderTable set currentBalance = 4600.00 where lastName = 'Bhogal' and firstName = 'Kulvir'
By issuing this update statement, we have effectively put the customer with lastName Bhogal and firstName Kulvir in our "red zone" of within 15% of his credit limit.
Accordingly, our trigger should react to this update as it meets the criterion to call our Java UDF.
You can check if the setup is working by setting the e-mail address of a customer that falls into the "red zone" to your e-mail address. One pitfall that you should look out for is that your SMTP server might not allow e-mail relaying. This is common amongst ISPs that try to make sure their SMTP servers are not being used for spamming. Accordingly, the "From" address that is specified in your Java UDF might have to be an address that the SMTP server recognizes. If you received an e-mail in your box from the automated process, our experiment worked!
DB2 triggers are powerful assets to the database programmer. Having them in your toolbox allows you to instantly perform operations as the database is updated, Triggers allow you to encapsulate and define business logic in a manner that decouples operations you want performed from your applications. By doing this, you, in essence, can polarize certain operations towards the back-end. This is extremely useful in environments where different programs (possibly written in different languages) might be updating a database simultaneously. These programs may all have common ultimate functionality (e.g., the firing off of an e-mail). By using a trigger/UDF combination as was done in this article, you can save yourself the labor of recoding a common functionality in different languages.
Furthermore, by tapping into Java UDFs, you can use triggers to make DB2 and Java work in a synergetic manner. In this article, you used a DB2 trigger to invoke a Java user-defined function when a customer was in danger of reaching his credit limit. The JavaUDF used the JavaMail API to spawn a customized e-mail message.
Kulvir Singh Bhogal works as an IBM consultant, devising and implementing Java-centric solutions at customer sites across the nation. You can reach Kulvir at firstname.lastname@example.org.