データベース/データ管理 > DB2 Developer Domain > ライブラリー > 技術白書-DB2ファミリー関連 >

DB2トリガー、Java UDF、JavaMail APIを使用したビジネス・ロジックの実行

Kulvir Singh Bhogal 著
IBM e-businessコンサルタントテキサス州オースティン
2002年4月

コンテンツ

はじめに

執筆者

Kulvir Singh Bhogal

IBM e-businessコンサルタントテキサス州オースティン

はじめに
ニュートンの運動の第3法則によれば、ある作用が働いたときには、必ずそれと等しい反作用が働くということです。ここで逃げ出したくなった読者はいないでしょうね。物理学の講義をしようというのではありませんから、ご心配なく。この記事で皆さんにご紹介したいのは、データベース・アプリケーションに実装されているDB2トリガー、Java^TM ユーザー定義関数（UDF）、JavaMail APIの概念を1つに統合した原因と結果の概念についてです。

ここで私が使用する筋書きは、要約すると次のようになります。

DB2^®データベース中のあるレコードが変更されます。
その変更によって、そのデータ・レコードが論理演算に基づくある条件を満たす場合は、データベース・トリガーによってJava UDFが呼び出されます。
このJava UDFはJavaMail APIを使用して、指定の受信人に電子メールを送信します。

実社会での例
この実験の手はずを整える前に、この概念が実社会で実用化されている例を挙げてみます。たとえば、あるクレジット・カード会社があったとします。顧客の要望で、この会社はクレジット・カードの利用額が信用限度に近づいたら顧客に電子メールで通知することになっています。
図1はここで行う作業の内容を図示したものです。電子メールが送信される前に満たされねばならない条件の作成について、もう少し具体的に示すために、会社が通知を送信する直前にユーザーの信用限度の残高が15%未満でなければならないという前提を立てます。

図1.　データベースの更新から電子メールの送信確認までの流れ

データベースのセットアップ
この作業のためには、さまざまな操作をするデータベースが必要になります。もちろん、実際のクレジット・カード・システムのデータベースは、おそらくここで取り上げる単純化したモデルよりもはるかに複雑だと思われます。しかし、ここではあくまでも学習が目的ですから、ここでの例はシンプルなものに留めておきます。

まず、次のSQLステートメントでデータベースを作成します。
　
create db cardDB
もちろん、何か有用なことをするには、このデータベースに接続しなければなりません。
　
connect to cardDB

次に、顧客データをすべて保存するためのテーブルが必要です。テーブル1を見ると、各顧客を表す行にどんな列があるのかがわかります。

テーブル1.　顧客スキーマ

列名	コンテンツ	データベース中での表現形式
lastName	Last Name	varchar(30)
firstName	First Name	varchar(30)
emailAddress	E-mail Address	varchar(30)
currentBalance	Running credit card balance	decimal(7,2)
creditLimit	Credit card limit	decimal(7,2)

上記のテーブルを作成するSQLステートメントは次のとおりです。

create table cardHolderTable
(lastName varchar(30) not null,
firstName varchar(30) not null,
emailaddress varchar(30) not null,
currentBalance decimal(7,2) not null,
creditLimit decimal (7,2) not null)

次に、このテーブルを記述すれば（ステートメントDESCRIBE TABLEを使用する）、このテーブルが正しく定義されていることがわかります（図2を参照）。

図2.　Cardholderテーブルの記述の結果

＜クリックして拡大＞

次にこのテーブルに、あるダミー・データ値を入力する必要があります。テーブル2はこのデータベースの値としてふさわしいサンプル・データをいくつか入力したところです。

テーブル2.　Cardholderテーブルのサンプル・データ

lastName	firstName	emailAddress	currentBalance	creditLimit
Bhogal	Kulvir	kulvir@somewhere.com	$3753.00	$5000.00
Peters	Nancy	nancy@someplace.com	$2722.43	$3500
Clancy	Jerry	clancyj@somedomain.net	$650.23	$1500.00
Biggs	Gloria	gloria@otherdomain.org	$718.78	$2500.00

重要：このサンプル・データでは、テーブルに最初にデータを入力するときに、「顧客」の現在利用額をその信用限度の85%未満に維持する必要があります。トリガーが作成される前に現在利用額がすでに85%を超えている顧客レコードは、このシステムの検査の対象外になります。

テーブル2に示したデータの第1行を挿入するには、次のSQLを使用します。

insert into cardHolderTable values
('Bhogal','Kulvir','kulvir@somewhere.com',3753.00,5000.00)

続けて同じようなステートメントを実行して、テーブル2に示したダミー・データ・レコードの残りをサンプル・データベースに入力していってください。

上に戻る

トリガーの作成（原因）
前に述べた「原因と結果」のテーマに戻ると、次に「原因」を定義する必要があります。このためには、DB2トリガーを作成します。

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)))

では、上記のcreate triggerステートメントをよく見てみましょう。トリガーになじみのない方のために1行で説明すると、トリガーとは、特定のテーブルに対する削除、挿入、更新の操作の直接的な結果として発生する一組の作用のことです。といっても、これはニュートンの定義ではありません。ここで話しているのは、DB2のことです!

トリガーはデータベース・プログラマーが利用できる極めて強力な機能です。この記事では、トリガーに考えられる多数の用途のうち、1つだけを紹介します。トリガーについての詳細は、「DB2 Application Programming Guide」を参照してください。

この構文は実は意外と簡単なのです。ここの例では、トリガーは更新操作の結果として発生します。このシステムで顧客がクレジット・カード会社に信用借りをするというトランザクションをエミュレートする方法は、currentBalance列を更新することです（このモデルのクレジット・カード追跡システムは非常に粗雑なものです。あなたのクレジット・カード会社がこのような骨格だけのシステムを使用しているようなら、用心したほうがいいと本気で注意したいと思います。しかしこの例では、このような簡単なシステムで十分に間に合います）。

では、トリガーの仕様に戻りましょう。

このトリガーの名前はspawnEmailTriggerです。
キーワードafterは、電子メールが生成されるのがDB2テーブルを操作した後であることを意味します（テーブルを更新する前ではなく）。
update of currentBalance on cardHolderTableという文節は、このトリガーに監視させたいデータベース・アクションの範囲を狭めるためのものです。つまり、cardHolderTableテーブルのcurrentBalance列の更新が発生した場合にトリガーが起動されるようにしているのです。
mode db2sqlという文節は必須です。
次に、トリガーが生成される条件を論理式で限定します。when (newrow.currentBalance>(oldrow.creditLimit*.85))という文節では、トリガーが生成される条件を、currentBalanceが顧客の信用限度の85%より大きい値に更新された場合として指定しています。ここで注目すべき重要な点は、更新が行われるときに、データベース操作前の値とデータベース操作後の値を参照する方法です。この方法を使用することで、必要な計算を実行し、トリガーを生成すべきかどうかを決定することができます。

したがって、顧客の残高が信用限度の15%未満の「警告ゾーン」になるような更新が実行されたときには、次の文節で電子メールを生成するUDFを呼び出します。

(values(mailer(oldrow.emailAddress,oldrow.lastName,oldRow.firstName,oldRow.
currentBalance,oldRow.creditLimit)))

このUDFについては、次の節で説明します。ここで理解しておくべき要点は、引数をUDF関数（名前はmailer）に渡し、それがカスタマイズされた電子メールの構成に使用されるということです。

電子メールの生成（結果）
このトリガーでは、指定の条件が満たされた場合に、Java UDFが呼び出されます。では、どのようなUDFが呼び出されるのでしょうか? この質問の答えには、少し時間をかけるつもりです。しかし、UDFを正式に紹介する前に、少し下準備が必要になります。

JavaMail jarファイルのインストール
ここで実行するつもりでいる電子メールの送信というタスクは、JavaMail APIを中心にして処理されます。皆さんのシステムに、このプログラム・インターフェースを使用するために必要なファイルがすでに存在するかどうかは不明です。使用しているJavaのバージョンによっては、必要なjarファイルはすでに自由に使用できることもあります。私はここで悲観的な立場を取って、ファイルがないものとしておきます。実際、皆さんのところに必要なjarファイルがなくとも、クレジット・カードは不要です。JavaMail APIは無料でダウンロードできるからです。

ここでは皆さんがJavaMail APIに精通しているものとします。そうでない方は、チュートリアルを読んでみてください。

さて、JavaMail APIに関連するjarファイルがハード・ドライブにインストールされていて、mail.jarおよびactivation.jarという名前が付いているものとします。この場所は、DB2 に知らせる必要があります。このjarファイルをデータベース・サーバー上に、「Mail」および「Activation」という名前でインストールします。

このためには、次のステートメントを実行します。

call sqlj.install_jar('file:///c:/temp/mail.jar','MAIL')
call sqlj.install_jar('file:///c:/temp/activation.jar','ACTIVATION')

上記のステートメントで、関心のあるjarファイルはtempディレクトリーにあります。皆さんは自分のjarファイルの位置を正しく指し示すように、上記のステートメントを適当に変更する必要があります。

Java UDFの操作
さて、次にJava UDFコードに進みます。このコードをダウンロードし、しばらく時間をかけて、じっくり眺めてみてください。このコードに付属しているドキュメンテーションには、JavaMail APIを使用して電子メールのメッセージを作成し、送信する手順が説明されています。

ものごとを実行する手順を規定したものとして、ここで関心のあるメソッド、mailerは、パブリックな静的メソッドであり、文字列を返します。UDFメソッドには戻りの型が定義されていなければなりません。着信メソッドのパラメーターは電子メールのカスタマイズに使用され、意図した受信人に、クレジット・カードの残高が「警告ゾーン」になっていることを通知します。

Java UDFを使用する前に、いくつか準備のための手順を実行しなければなりません。

JavaUDF.javaファイルをコンパイルした後、それで作成されるクラス・ファイルを、関数を使用するDB2インスタンスのsqllib/functionディレクトリーに格納します。Windows NTとデフォルト・インストール・オプションを使用している場合、ファイルをドロップする位置は次のとおりです。

C:\Program Files\SQLLIB\function

次に、新しく作成したUDFを登録し、DB2にその存在を知らせなければなりません。

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'

では少し時間を取って、この登録ステートメントを分析してみましょう。このCREATE FUNCTIONステートメントでは、DB2で使用されるこの関数の名前を指定します。この例では、「mailer」という名前にします。

このほかに、関数が受け取るSQLパラメーターも指定しなければなりません。重要なことは、ここではSQLパラメーターを使用しますが、実際のJava UDFではJava変数のデータ型を使用することです。Javaコードの中にUDF登録ステートメントが並んで配置されているのを見れば、この例のvarcharに対して、対応するJava 変数のデータ型はStringであることがわかります。同様に、SQLのデータ型decimal(7,2)に対しては、Javaのデータ型java.math のBigDecimalが使用されます。このSQLとJavaのデータ型との1対1の対応づけは、すべての処理をスムーズに運ぶために必要なことなのです。mailerメソッドのシグニチャーは下記のとおりです。

public static String mailer(String input, String lastName,
String firstName, BigDecimal currentBalance,
BigDecimal creditLimit)

登録ステートメントに戻ると、この関数から戻される値として、varchar(70)が定義されています。これはUDFからの進行状況の記述を格納するために使用されます。
fenced関数を使用していますが、これはnon-fenced関数よりも安全であるためです。この問題にはあまり深入りせず、fenced関数はデータベース・マネージャーの内部リソースからは独立して実行されるので、バグが発生した場合でもマネージャーにとっての脅威が少ないとだけ述べておきます。
先に進むと、この関数はsqlを含んでいないという記述がありますが、これは関数にSQLステートメントが含まれていないという意味です。
external action文節は、この関数がデータベースの外部の世界に影響するようなアクションを実行することを意味しています。
「language java parameter style java」という文節は、このUDFの言語とパラメーター・スタイルがJavaであることをデータベースに知らせています。
最後に、「external name 'JavaUDF!mailer'」という文節を使用して、データベースが探さねばならないJavaクラス・ファイルはJavaUDFであり、ここで登録している関心の的ともいうべき関数はメソッド「mailer」であることをデータベースに知らせています。

　

制限事項：このセットアップには根本的な欠点が1つあります。それは外部の電子メールに関するアクションが、成功しなかったトランザクション（たとえば、Webベースのフォーム経由で処理が中断される残高の更新など）について通知を受ける手段がまったくないことです。したがって、データベース・トランザクションが順調に遂行されなかった場合でも電子メールが生成されるというケースが起こり得ます。

上に戻る

最終的なテスト
これまで概略を述べた手順を順調に実行できたなら、次はいよいよその努力を開花させ、その実を摘み取る番です。

この作業を要約すると、あるクレジット・カード顧客の残高が信用限度の15%未満になるような更新ステートメントを実行すると、ここで作成したJava UDFを介したトリガーによって直ちに電子メールが送信されるという筋書きです。

前提条件として、前に提案した所定のダミー・データ値を使用するものとして、この実験をテストするステートメントは次のようになります。

 update cardHolderTable
set currentBalance = 4600.00
where lastName = 'Bhogal' and firstName = 'Kulvir'

この更新ステートメントを実行することは、要するにlastNameがBhogal、firstNameがKulvirである顧客の残高を、信用限度の15%未満という「警告ゾーン」に設定しているわけです。

ですから、定義したトリガーは一定の条件を満たすことを確認し、この更新に対する反作用としてJava UDFを呼び出します。

このトリガーがうまく作動するかどうか調べるには、「警告ゾーン」に入った顧客の電子メール・アドレスを自分の電子メール・アドレスに設定してみることです。その際に気をつけるべき問題は、あなたのSMTPサーバーが電子メールの中継（リレー）を認めないことがあることです。これはSMTPサーバーがスパミングに利用されないように確認措置を取っているISPの間では一般によくあることです。したがって、Java UDFに指定さているFromアドレスが、SMPTサーバーが認識できるアドレスでなければならない可能性もあります。あなたの受信トレイに自動化プロセスからの電子メールが届いたときは、この実験がうまくいったことになります！

まとめ
DB2トリガーはデータベース・プログラマーにとって強力なツールです。ツールボックスにこのツールを用意しておけば、データベースの更新に伴って、即座にさまざまな操作を実行することが可能になります。トリガーを使用すれば、実行したい操作とアプリケーションとを切り離すような形でビジネス・ロジックをカプセル化することができます。そうすることは、要するに特定の操作がバックエンドの方向に分極化させることにほかなりません。これはさまざまなプログラム（おそらく、さまざまな言語で書かれている）が同時に1つのデータベースを更新するような環境で極めて有用なものです。それらのプログラムはすべて究極の共通機能（電子メールの送信など）をもつことができるからです。この記事で説明したように、トリガー/UDFを組み合わせれば、この共通機能をいくつもの言語で何度も再コーディングするという手間を省くことができます。

さらにJava UDFを活用するには、トリガーを使用して互いに相乗効果を発揮するような形でDB2とJavaを機能させることができます。この記事では、DB2トリガーを使用して、クレジット・カードの顧客の利用額が信用限度に近づいたときにJavaユーザー定義の関数を呼び出すようにしました。そのJavaUDFは、JavaMail APIを使用して、カスタマイズされた電子メール・メッセージを生成するというものでした。

シングル・ダウンロード

説明	ファイルの種類	ファイルのサイズ	ダウンロードの方法
JavaUDF.java	java	4 KB	HTTP または FTP

どちらのダウンロード方法を使用すべきでしょうか?
ダウンロードに関するFAQ

著者について

Kulvir Singh BhogalはIBMコンサルタントとして、米国内の顧客サイトでJavaを中心とするソリューションの考案と導入を担当しています。
Kulvirの連絡先は、kbhogal@us.ibm.comです。

IBM、DB2は、IBM Corporationの商標です。
JavaおよびすべてのJava関連の商標およびロゴは、Sun Microsystems, Inc.の米国およびその他の国における商標または登録商標です。
他の会社名、製品名およびサービス名などはそれぞれ各社の商標または登録商標です。

原文はこちら

上に戻る