パーソナルツール
現在の場所: ホーム ブログ Categories mail
« 2017November »
Su Mo Tu We Th Fr Sa
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30    
このBlogについて
 代表の向井田ことMUKAです。当ブログサイトでは、MAGICやDB2に関する技術者向け情報を公開しています。お気軽にお立ち寄り下さい。
最近のエントリ
twitter / 新RT機能を英語モードで・・・ muka 2009年11月19日
twitter / List Widget muka 2009年11月03日
twitter API / Retweet の仕様が・・・ muka 2009年10月30日
twitter API / Lists muka 2009年10月24日
IBM Rational Software Conference 2009 と アジャイル開発 muka 2009年10月08日
twitter API / geoタグ! muka 2009年10月01日
Twitter Developers Meetup in Tokyo muka 2009年09月11日
Club DB2 2009/9/4 muka 2009年09月05日
最近のコメント
Re:Club DB2 2009/8/29 muka 2009年09月01日
Re:Club DB2 2009/8/29 SIM 2009年08月31日
最近のトラックバック
Club DB2 8/29の感想エントリと今後の予定 Unofficial DB2 BLOG 2009年09月01日
カテゴリ
misc (47)
dbMAGIC (47)
DB2 (47)
mail (47)
Web (47)
twitter (47)
 

mail

一つ上に移動
メールアーカイブシステムや、メールサーバ構築など、メールソリューションに関連する話題

DB2 9 を利用したメール・アーカイブ・システムの開発 (1)




はじめに


 9月になり、当社も設立1周年を迎えました。
 これもひとえに、皆様のお陰と感謝しております。
 思えばがむしゃらにやってきたこの一年でしたが、二期目は今まで以上に楽しくやっていけたらと思っております。

 さて、この何ヶ月か密かに新製品の開発を行っているのですが、それが「DB2 9を利用したメール・アーカイブ・システム」です。
 現在、10月のリリースに向けて準備中という段階です。そこで、このページを借りて、その紹介などもしていきたいと思っています。
 今まで私がやってたことを知っている人からはよく、「何故、メールアーカイブなの?」と聞かれるんですが、およそ次のような理由です。

  • メールサーバ製品の開発を行っている会社との協業を考えた。
  • とにかく、「DB2 9」で何か作りたかった。
  • 内部統制などのジャンルで、今後需要の増すことが期待されている?
  • 大掛かりなシステムではないので、小さく早く始められる?
いずれにしても、全く開発経験の無いシステムだったんですが、試行錯誤しながらやってきて、ようやく形が見えてきたという感じですね。
 特に、メールレシーバや、メールデータのXML変換プログラム(後述)は、協業している会社の技術協力を頂くことができたのが大きなポイントで、これらの技術協力を頂けなければ、この製品も完成できなかったことと思います。
 また、「DB2 9」は、他のページでもいろいろ紹介させて頂いていますが、今のところ「我が社の開発テーマ」みたいなものになっています。このメールアーカイブでも大活躍します。特に、NSE(テキスト検索用エンジン、「Net Search Extender」の略)という拡張モジュールは、新たなDB2の世界を見せてくれたという感じです。今後、いろいろと他の分野のアプリケーションにも応用できる機能だと思っています。
 当然ながら、MAGICはV10で開発しています。V10になって、「こんなこともできるようになったんだ!」というようなものが幾つもありまして、それらもあわせてこのページで順次紹介できれば良いなと思っております。




メールアーカイブって?


 そもそも、メールアーカイブって何でしょうか?
 そうですね、簡単に言えば「送受信したメールデータを全てアーカイブ(書庫化)するシステム」のことです。
 送受信したメールというのは、SMTPサーバ(MTA)を経由した送信/受信メールの全てが該当データになります。
 また、書庫化するということは、インデックスが付けられて即座に検索することが可能になるということです。
 このジャンルの製品は最近沢山出ているようです。
 注目されることになったのが、システムセキュリティや内部統制の観点からです。個人情報漏洩の抑止効果も期待されていますし、当然ながらメールデータの保存・検索に便利ですので、単に問題発生時の検索用途だけでなく、データ共有的な機能をつけている製品もあるようです。
 というわけで、メールアーカイブも百花繚乱。製品価格もピンからキリまでいろんな製品があります。
 さて、そんな中で、当メールアーカイブは果たして生きていけるのか・・・。(それは私も良く分かりません。(^^;)
 まぁ、こんなものを作ったんだけど・・・というノリで、順を追って、概要や特長について説明していきたいと思います。



当メールアーカイブの概要



 それでは、順に当メールアーカイブの概要について説明していきましょう。

メールの受信機能

 まず、メールの受信部分についてです。
 この部分は、MTAから転送されたメールを受信して、保存するためのサービスプログラムをバックグランドで実行させることで実現しています。
 この部分を担当しているのが、「KSearch.exe」というモジュールです。その設定画面を示します。

ma_001.jpg

KSARSサービスは任意のポートからメールを受信して格納することが可能です。


 タイムアウト時間や、保存単位、受信フォルダ、サービス監視ポートなどを設定することが可能です。

 ところでレシーバにメールを転送するにはどうすれば良いでしょうか?
 MTA(SMTPサーバ)は、転送機能のあるものであれば、Windows系に限らず任意のものを利用することが可能です。
 下記は、とあるメールサーバ(現e-postメールサーバの前身版)での複写転送と配送先を設定している例です。


ma_002.jpg

ダミーのアドレスを転送先アドレスとして設定します。


ma_003.jpg

ダミーで設定したドメインに対して、配送先サーバとポート番号を指定します。

ただ、上記のような場合は、受信したメールのエンベロープ情報には送信先として常にダミーのメールアドレスが入ってしまいます。(ヘッダ情報の「To」等は問題ありません。)
 これを解決するためには、「Q-Send Server」というe-post製品を使用することで解決が可能です。(いつかの機会にご紹介したいと思います。)

メール書庫化機能

 次に、メールの書庫化についてです。
 メールデータは、1件=1ファイルのXMLデータに変換されます。変換されたXMLデータは、DB2の表に格納されます。
 下の画面は、Mail2XMLという変換プログラムを起動したものです。ダイアログにより対話的に処理を指示することも可能です。が、本システムでは変換とインポート処理をバックグランドで自動的に実行しますので、この画面が表示されることはありません。


ma_004.jpg

メールのXML変換プログラムは単独で起動も可能です。


 メールデータをXMLデータにする利点には、次のようなものがあります。

  • XMLデータベースに格納して、利用が可能になる。
  • メールのエンコードの文字種別(JIS, EUC,UTF-8)によらず、全て同じコード(UTF-8)でアクセスできるようになる。
  • エンベロープ、ヘッダ、ボディ(本文)、添付ファイル(テキスト情報)などの個別の情報について、1つのXMLスキーマを利用してアクセスが可能になる。
  • XMLデータであることによる他の副次的な利点を享受できる(システム連携のデータとして利用する等)

 一番大きいのは、なんと言っても「XMLデータベースに格納できる」ということです。他の利点は付随的なものとすらいえます。
 統一的に文字コードはUTF-8に変換されますが、中にはどうしても文字化け等の理由によりXMLデータでは扱えない文字コードが混入してしまうことがあります。(DB2は結構チェックが厳しいので、そういうデータはインポート時にはじかれてしまいます。)

 下図は、DB2の管理ツールコントロールセンターで表を開いたところです。「MAIL_DATA」というカラムがXMLタイプのカラムです。これ以外にも、いくつかの属性を同じ表のカラムに格納することができるわけです。DB2 9がハイブリッドデータベースと言われる由縁ですね。


ma_005.jpg

XMLタイプのカラムがテーブルに作成されていることが分かります。



 XMLカラムの右脇のボタンをクリックすると、文書ビューアーという画面で、XMLデータの内容を確認することが可能です。
 このXMLカラムを使用することにより、送信者、宛先、件名、本文、その他任意のヘッダ項目を使用したメールの検索を行うことが可能になります。


ma_006.jpg

XMLスキーマの階層構造に従って、メールメッセージデータが格納されます。


 
 メールメッセージデータとしての殆どの属性は、このXMLという形式のカラムにテキストデータとして格納されることになるわけです。
 XMLカラムがあれば、他の従来形式のカラムは必要ないのでは?という疑問が湧き起こるかもしれませんが、リレーショナルデータベースとXMLデータベースの双方の良いところを使えるというのがハイブリッドDBの利点です。
 特に当システムでは、次のような考え方に基づき、このメール・データの定義を行っています。

  • メールデータはRFC822の仕様に基づき、極力原型に近い形態でXMLスキーマを定義する
  • XMLカラムにメールデータを格納し、メールデータの検索ができるようにする
  • ヘッダの情報のうちのいくつか(Message-Id、In-Reply-To等)は、他のカラムに展開し、またインデックスを作成するなどして、データ検索の高速化を実現する
  • ヘッダの情報の日付(Date)については、GSTに変換して、他のカラムに展開
  • XMLデータにない属性(メールサイズ、添付ファイルの数、その他)は他のカラムに展開

 関連するメールを検索する際に、ヘッダの「Message-Id」と「In-Reply-To」の関連を調べるわけですが、当初はXMLの検索機能を使って行っていたんです。でもすぐに、これは従来のリレーショナルデータベースの持つインデックス検索には及ばないことが分かりました。

 なお、検索用の表以外に、メールの実データを格納する表が別途あります。
 ER図的に描くと次のようになります。

ma_007.jpg

メールデータは、1対1に関連付けられた2つの表で構成されます。


 メールデータは、MAIL_EMLというテーブルのEML_BLOBというBLOB形式のカラムにバイナリデータとして格納されます。(暗号化したものが格納されます。)
 例えば、メールクライアント上から誤って削除してしまっても、このテーブルを利用して復元することが可能です。



DB2 9 を利用したメール・アーカイブ・システムの開発 (2)


「RFC822」って?


 前回までは、メールアーカイブの全体像について説明してきましたので、今回から少しずつ細かい話をしていきたいと思います。
 今回は、メールのXML化についてです。

 メールデータを如何にXML化するか?という問題は、一番最初の問題となりました。つまり、どのようにしてメールデータをXMLで表現するかということなのですが、そもそもメールとはどういう構造になっているのかとかどのようにやり取りされるものなにかとかを良く知らないといけません。
 でも、これはKさんの技術協力を頂くことによって、殆ど割と簡単に行うことができました。Kさんは、メールサーバを長年開発していまして、細かいメールの仕様については、当たり前のように熟知している、いわゆる「その道のプロ」の方です。以前から別な仕事でも、XMLでデータ化するようなプログラムを作ってもらっていたりしましたから、割とこちらから指示することもなくXML化するプログラムを作ってしまいました。

 さて、一番最初にその出力されたXMLを見たときに思ったのは、「RFC822って何だ?」でした。
 XML文書には、必ず「文書要素」もしくは「ルート要素」と呼ばれるものがあるのですが、それがまさしく "RFC822" だったんです。
 当時は聞きなれない番号だったのと、「文書要素」ならもう少し一般的な名称・・・例えば、「MailMessageData」とか?・・・になるのかなぁ?なんて思っていましたから、ちょっと意外だったのを記憶しています。

 さて。「RFC822」ですが、もともとはメールのヘッダ等の情報について規定した仕様だったようです。現在はその仕様もいろいろと拡張されているそうです。(「RFC2822」他)
 いずれにしても、当システムで扱うXMLファイルは、メールメッセージデータの本来的な仕様に基づき、それをありのままの形で表現しようとしたものになっています。

 次に、具体的な、スキーマ情報についてみてみましょう。


当システムで扱うメールデータ用のXMLスキーマ



 当システムで利用しているメールメッセージXMLのスキーマ情報について、概略ですが説明していきます。

  • 文書要素「RFC822」配下
     文書要素「RFC822」の子要素としては、エンベロープの情報「Envelope」、ヘッダの情報「Header」、本文の情報「Body」を持つことができる構造となっています。
     この3つの要素が主要なデータとなります。

    ma_011.jpg

  • エンベロープ情報
     エンベロープ情報の子要素には、送信者の情報「MAIL_FROM」と宛先「RCPT_TO」の情報を持てるようにしてあります。
    ma_012.jpg


  • ヘッダ情報
     ヘッダ情報の子要素には、約40種のものが予約されています。RFC822では「From」, 「Subject」,「Date」の3つが必須らしいのですが、過去に「Date」を付けないメールクライアントが流行ったという話もあるそうで、その真相は良く分かりません。
     ということで、ここで紹介するスキーマ上は全てオプショナルにしてあります。


    ma_013.jpg

  • 本文情報
     本文情報の子要素は、ちょっと複雑な構造になります。
     複数個繰返し可能な「Data」要素の下に、再度「Header」や「Body」要素が現れる形です。


    ma_014.jpg

ここで、幾つかの補足をしておきましょう。


エンベロープ情報(/RFC822/Envelope)

 エンベロープというのはもしかしたら馴染みが無いかもしれませんが、メールサーバはメールヘッダの「From」や「To」ではなく、別個の情報を元に 配送します。例えば、SPAMメール等で良くあるのは、ヘッダのFromとは別のアドレスから送信してくるなどの場合です。
 当スキーマでは、それらの情報を含めて記述が可能になっているのがポイントです。

 また、「BCC」の宛先情報については、この 「RCPT_TO」の情報から検索することが可能で す。ヘッダの「To」や「Cc」に存在しない送信先がエンベロープにあれば、それは「BCC」で送信したと判断することが可能です。

 ちなみに、エンベロープを処理しないモードが「Mail2XML」のオプションにあります。
 これはエンベロープの情報を取得するためには、ちょっとした仕掛けが必要になるのですが、それが使用できない場合等、この出力しないオプションを使用することも可能です。
 その場合は、「RFC822/Envelope」を作成しません。


本文情報(/RCC822/Body)

 多少複雑な構造になっていますが、これは、簡単に言えば、マルチパート形式のメール(入れ子構造になっている)をサポートするためです。
 具体的な例を示してみます。

  1. 単純な形式のメール
    いわゆる本文のみがあり、添付ファイルなどの無い場合です。
    このときは、次のようなパターンになります。
    <RFC822>
    <Header>
     <Content-Type>text/plain; charset=iso-2022-jp</Content-Type>
    </Header>
    <Body>
    <Data>
    <Body>本文です。</Body>
    </Data>
    </Body>
    </RFC822>

  2. HTMLメール

     ヘッダ「Content-Type」が「multipart/alternative」のいわゆるマルチパート形式のメールになります。

    <RFC822>
    <Header>
     <Content-Type>multipart/alternative; boundary="..."
    </Content-Type>
    </Header>
    <Body>
    <Data>
    <Header>
    <Content-Type>text/plain;charset="..."</Content-Type>
    </Header>
    <Body>本文です。</Body>
    </Data>
    <Data>
    <Header>
    <Content-Type>text/html;charset="..."</Content-Type>
    </Header>
    <Body>&lt;HTML&gt;&lt;BODY&gt;
    本文です。&lt;/BODY&gt;&lt;/HTML&gt;
    </Body>
    </Data>
    </Body>
    </RFC822>

  3. 添付ファイルのあるメール
     ヘッダ「Content-Type」が「multipart/mixed」の、同じくマルチパート形式のメールになります。
     通常モードでは、添付ファイルのエンコードされた情報は除去されます。
     添付ファイルをテキスト化するオプションを組み込んだ場合は、それぞれの本文情報(/RFC822/Body/Data[n]/Body)に抽出したテキストが挿入されます。
    <RFC822>
    <Header>
     <Content-Type>multipart/mixed; boundary="..."
    </Content-Type>
    </Header>
    <Body>
    <Data>
    <Header>
    <Content-Type>text/plain;charset="..."</Content-Type>
    </Header>
    <Body>ファイルを送信します。</Body>
    </Data>
    <Data>
    <Header>
    <Content-Type>application/octet-stream;name="文書.DOC"
    </Content-Type>
    <Content-Transfer-Encoding>...</Content-Transfer-Encoding>
    </Header>
    </Data>
    </Body>
    </RFC822>

  4. その他
    テキスト本文の複数存在するもの、HTML形式でテキスト本文の無いもの、HTML形式で添付ファイルのあるものなど、組み合わせはいろいろですが、いずれもマルチパート形式のメールとして処理されます。


DB2 9を利用する上での考慮点


 さて、DB2 9へ作成したXMLファイルを格納する際に、設計上考慮しておかなければならない点がいくつかあります。


  • 要素内のテキストの長さ
     ひとつの要素内に格納できるテキストの長さには制限があり、32KByteまでです。
     ところが、メールメッセージの場合は、ちょっと長い文章だと、軽く超えてしまいます。
     しかも悪いことに、DB2 9に格納してしまうと、そのカラム(XML形式)に対する検索が一切できなくなってしまうんです。
     よもやこういう制限は無いと思っていたんですが、やってみて初めて知ることとなりました。(せめて、他のエラー同様、インポート時にはじいてしまってくれたほうがよほど親切なのではないかと...?)

     さて、しょうがないので、これを解決するために、「Body」(/RFC822/Body/Data/Body)は32KByteを超えないように適当な箇所で分けて格納する処理をMail2XMLに入れることで対応しました。
     /RFC822/Body/Data/Body[1]  ・・・  最初の8KByte
     /RFC822/Body/Data/Body[2]  ・・・  次の8KByte
     /RFC822/Body/Data/Body[3]  ・・・  更に次の8KByte
      ...
    皆さんも、長い文章をXMLに格納する場合はご注意下さい。

  • 使用できる文字コード
     これは、XMLで使用可能な文字コードの仕様らしいのですが、時々文字化け等により思わぬコードが混入してしまうことがあります。
     このような場合、たとえWebブラウザ上は何の問題もなく表示できたとしても、DB2にインポートする時点で厳しくはじかれます。(こちらは全く容赦してくれません(^^;)
     プログラム(Mail2XML)で使用できない文字コードをカットするように対処しましたが、最初から文字化けしているメールの場合もあります。また、添付ファイルをテキスト化する時点でそれに失敗することもあります。このあたりは、ある程度割り切りが必要かもしれません。
     参考になりそうなページをリンクしておきます。


XMLサンプルデータ


 せっかくなので、WordとExcelとPDFの3種類の文書を添付したメールのサンプルデータを作ってみました。その元データとXMLに変換した結果を一まとめにしたものをアップロードしておきます。参考にしてみて下さい。

ma_015.jpg



お知らせ


 MSJさんのパッケージ掲載サイトに当製品の紹介ページを掲載して頂きました。
 宜しければアクセスしてみて下さい。


 



DB2 9 を利用したメール・アーカイブ・システムの開発 (1) DB2 9 を利用したメール・アーカイブ・システムの開発 (1)
サイズ 12914 - File type text/html
パッケージソフト.com パッケージソフト.com
サイズ 1 kB - File type text/plain
メールのXML形式変換サンプル メールのXML形式変換サンプル
サイズ 35.0 kB - File type application/x-lzh

DB2 9 を利用したメール・アーカイブ・システムの開発 (3)


データベースの設計


 前回は、メールのデータ化=XMLスキーマの構造定義について説明してきました。今回は、XMLデータベースでどのように設計が変わるのか?という話に触れてみたいと思います。
 私も長く業務システムの開発をしておりますので、過去いろいろなシステムの設計を行ってきました。その自分がもし従来のリレーショナルデータベース(RDBMS)でメール・アーカイブ・システムを開発したらどうなるか?という問題を考えてみたのです。
 とは言うものの、メールアーカイブを開発するなら最初からDB2(ハイブリッドDB)を使ってみようと思っていたので、この数ヶ月はそういうことを考えることすらありませんでした。

 ここで、少しER図が出てくるんですが、これは私がこの7~8年前から付き合っている「T字形ER」というデータベース設計手法に基づいているものです。(なじみの無い方はごめんなさい。今時はやはりUMLなんでしょうかね?)

 ちょっと前後するんですが、テーブルの設計を行う前にクエリーの指示画面のイメージを示しておきたいと思います。下図は実際のアプリケーションの画面イメージです。
 さしあたっては、どんな項目を検索項目にしているかを見て頂ければと思います。

ma_025.jpg



リレーショナルDBで行った場合の表設計


 メールアーカイブは、その主たる目的がメールデータをデータベースに格納することなので、関連するテーブルがいくつも出てくるわけではありません。ちょっと物足りないかもしれませんが、単純なパターンとして考えてみてください。

 まず、ひとつのテーブルを作ってそのカラム(属性)を定義してみたとします。
 主キーたるIDがあり、その属性としては、タイトル、送信者、宛先、日付、本文等の項目でしょうか。
 下図は、「メール」エンティティを定義したもので、更にCC属性や、メッセージID、IN_REPRY_TO属性、ファイル名、メールファイルそのもの(BLOB)、ファイルのタイムスタンプ等を定義しています。


ma_021.jpg


 メッセージID(Message-Id)とIN_REPRY_TO属性を入れたのは、メールの関連表示をさせる際に必要となる属性値なので、ヘッダ項目の中でもより重要視したためです。
 上の例では、他のヘッダ項目(Received,Reply-To,X-Mailer,...)やエンベロープの情報はカットしてしまっています。これは重要性が無いと判断したからです。もしそれらもカラムに追加するとしたらどうなるでしょうか?前回のスキーマ定義で紹介したようにサポートしているヘッダ属性だけでも約40種ほどあるのですが、それらを全てカラムとして定義すべきでしょうか?「Received」属性などのように複数件の繰返すものもあります。
 これらの解決案としては、仮想エンティティとして別テーブル(MAIL_HEADER)を定義することなどが考えられます。

 上の例では、添付ファイルが考慮されていません。また、本文はテキストとHTMLの両者を持つ場合もあります。これらを解決するためには、やはり別テーブル(ATTACH_FILES)を定義し、テキスト/HTML/添付ファイルの区分項目、Content-Type、CharSet、Content-Transfer-Encodingなどの属性、そして本文などのテキスト情報を定義すると良いかも知れません。

 これらの解決案が次のER図です。


ma_022.jpg

 そうですね。これで、まぁまぁいけるんじゃないでしょうか?
 もし実際にRDBMSで設計しろと言われたら、私はこうすると思います...。


DB2 9で行った場合の表設計


 DB2 9を紹介する際に出てくる、いわゆる「おきまり」のパターンですね。
 図にすると次のようになります。

ma_023.jpg

 つまり、主キーのIDとXMLタイプのカラムだけの表です。
 いやぁ、ほんとシンプル!
 これでもなんとかなることはなるんですが、実際には幾つかのカラムが追加定義されています。その代表的なものを列挙してみます。(タイトルは付けたカラム名)

  1. MAIL_DES
     メールデータの識別子(Designator)で、メールレシーバが付けたユニークな文字列を格納します。
     主キーのMAIL_IDはいわゆる代理キー(サロゲート・キー)で数値の連番を付番します。このカラムは同じメールメッセージファイルかどうかを特定するために必要となります。
     ユニークキーとして定義しています。
  2. MESSAGE_ID
     メールヘッダ属性の「Message-Id」を格納します。
     本来メールを特定するIDですから、全世界のメールサーバは1通毎にユニークな文字列を付加しなければならないはずなのですが、SPAMメールなどは毎日同じIDで送ってきたりします。
     当システムでは敢えてこの項目にユニークなインデックスを付加しています。このことにより、同じ「Message-Id」はDBMSの制約によりはじかれることになります。例えば、社内の複数の人に同報でメールが届くと、メールファイルは複数件作成されますが、アーカイブのレコードは1件だけ登録されて残りは制約によりはじかれることになります。でもヘッダの「To」、「Cc」やエンベロープの「RCPT_TO」でBCCも含めて宛先は分かりますから、問題無いと判断しています。
  3. IN_REPLY_TO
     メールヘッダ属性の「In-Reply-To」を格納します。
     返信メールを書くと、元のメールの「Message-Id」がこの属性に記述されます。(「In-Reply-To」ではなく、属性「References」に記載される場合もあるので、実際はちょっと単純ではないですが・・・。)
     やはりこのカラムについても、インデックスを付加しています。
     このカラムから該当する「Message-Id」を辿っていくと、スレッドの最初(ルート)のメールに行き着きます。その後、順に「Message-Id」を参照しているメールを探すと下図のような関連メールのツリー(スレッド)表示が可能となります。

    ma_024.jpg

  4. TIMESTAMP
     この項目はXMLに変換されたファイルのタイムスタンプを格納しています。
  5. MAIL_DATE
     メールの日付("Sat, 13 Oct 2007 22:22:22 +0900"等)をGSTに直し、「YYYYMMDDHHMMSS」のような形式に直したものを格納しています。それによって、日付範囲の検索が高速に行うことが可能になります。
     実際の検索指示画面では、西暦の日付を指定します。(タイトル:「送信日付の範囲」)
  6. DATE_ATTR
     時々、未来の日付を付けて送ってきたりするメールがあると思うんですが、それをまじめに日付に直してしまうと嫌じゃないですか・・・。そんなとき、「Received」あたりから正しい日付時刻を推定するんですが、そういう処理をしたかどうかの属性を格納しています。
  7. MAIL_SIZE
     メールのサイズです。これはXML内の要素には持っていない属性なので、追加しています。
     実際の検索指示画面では、数値でサイズを指定します。(タイトル:「メールサイズ範囲」)
  8. CNT_FILES
     添付ファイルの数です。メールを解析し、レコードをセットする際に添付ファイルの数をセットしています。
     実際の検索指示画面では、数値でファイル数を指定します。(タイトル:「添付ファイル数の範囲」)
  9. BODY_TYPE
     本文がテキストなのか、HTMLなのか、はたまたその両者なのか、そういう属性値をセットしています。
     時々本分の無いメールも来たりすることないですか?

 ということで、実際はもう少し複雑な形になるわけです。

ma_026.jpg

 設計及び試作段階では、カラムを追加してみたり削除したりすることは結構あると思います。
 当然ながらXMLのスキーマを変更して要素などを追加することも可能ですから、どちらが良いかは検討する必要があると思います。

 ところで、クエリーを行うに当って、問題になるのはパフォーマンスですが、そのために索引(インデックス)の定義は重要です。XMLカラムの特定パスに対しても索引は作成できるのですが、テーブルの索引のパフォーマンス(ストレスは全く無し)には敵わないと思います。
 上の例では、関連メールを検索するための索引を2つ程作成しているのですが、当初はXML索引でやっていました。しかし、どうしても思うようなパフォーマンスが出なかったのですが、現在のような構造にすることで解決しています。
 逆に、表題、差出人、宛先などの情報は、クエリー結果を表示させる上で良く使うものですが、それらはカラムに展開しておく必要は無いと思います。
 RDBMSでは、項目長とか、データによってヌルになってしまう項目も結構気になるんですけど、XMLの場合はその煩わしさはあまり無いですよね。

 

まとめ


 今回は、テーブルの設計を行う上で、RDBMSの場合とハイブリッドDBを使う場合を比較してみましたが、如何だったでしょうか?
 多少の雰囲気の違いを知っていただければ幸いです。

 ハイブリッドDBとしてのDB2 9の使い方は、いろいろなパターンがあるのではないかと思います。
 今回の例は、ドキュメントとしてのメールデータを扱うというものでした。もっと定型的な業務システムの中で使う場合を考えたら、違った設計になってくることも十分に考えられます。
 最後に、(全てケースを想定したものではないことをお断りして)考え方などについて、まとめておきたいと思います。

  • ドキュメントを識別するためのプライマリーキーとXMLタイプのカラムで構成されたシンプルな表を基本として考える
  • データを特定するような属性や他のテーブルと連関する属性などは、インデックスを作成するなど、テーブルやテーブル間の制約を持たせるカラムとすることを検討する
  • サイズやタイムスタンプなど、ドキュメントそのものの属性を格納するカラムの必要性を検討する
  • どのようなクエリーをさせたいのかを検討し、必要な検索項目が過不足なくあるかを確かめた上で、検索項目がXMLで格納される形式よりも、並べ替えや処理しやすい形式にすべきものは無いかなどを検討する



DB2 9 を利用したメール・アーカイブ・システムの開発 (4)

ことしもあと・・・


 2007年も早いものでもう12月ですね~。
 この前まで、「暑い」とか言ってたと思ったら、すっかり寒くなってしまいました。w
 この週末は、少し風邪気味だったので、家でおとなしくしていたんですが、結構まわりで風邪とかインフルエンザとか流行っているようなので、皆様お体に気を付けて下さい。



スプールデータの処理について


 前回までは、XMLデータベースに定義する表の設計方法について触れました。
 このあたりは今でも頭の中ではいろいろなことを考えてるんですが、まだこれといった結論は出ていないし、いろいろと試行錯誤も繰り返しつつ模索しようと思っています。ということで、また思い出したように話題にするかもしれません・・・。
 さて、アーカイブの話に戻します。今回の一つ目の話題は、日々蓄積されるメールデータの格納に関してです。
 Windowsのサービスに組み込まれたレシーバプログラムは、転送されてくるメールを監視します(監視間隔の初期値は300秒)。受信したメールは、日単位に作成されるスプールフォルダに自動的に格納されます。

ma_031.jpg

 メール・レシーバのスプール処理は、設定により月単位などでも処理できるようになっていますが、当アプリケーションではデイリー(日単位)に行います。
 下図のように、日付の変わった夜間にスケジュール処理で前日までのデータを処理するのが通常の処理方法となります。

ma_032.jpg

 アーカイブを行うだけであれば、デイリーの運用で十分です。
 しかし、アーカイブだけではなく、メールデータの有効活用を考えたときは、将来的にリアルタイムの処理が必要になるかもしれません。
 オイデスでは、運用パラメータを変更することにより、リアルタイム的な処理も可能です。この場合は、本日のスプールフォルダのデータに関しては、未処理のものだけ抽出してアーカイブします(下図参照)。

ma_033.jpg


 リアルタイムの処理は、更に、メールレシーバと連携することにより、スケジュールすることなく、自動的に行うことも可能かもしれません。例えば、ある一定時間を経過してメールの受信があれば、自動的にインポート処理を実行させる機能をレシーバに組み込むのです。

 これによってシステムの自動連携なども可能になるかもしれません。
 例えば、緊急メールがあれば本人の携帯電話にメールするとか、本人が受信する前に自動的に不要なメールを削除してしまう・・・など、アイデアはいろいろです。


メールデータのインポート処理


 今回の二つ目の話題はメールデータのインポート処理に関してです。
 当メールアーカイブでは、検索用データとしてのXMLデータを格納する処理と、メールデータそのものを格納する処理の2系統の処理に分けられますが(下図参照)、特にXMLデータのインポートについて説明してみたいと思います。


ma_034.jpg


 XMLデータの一括入力については、DB2でサポートしている「インポート・ユーティリティ」を使用しています。これは、CSV形式のファイルを作成して読込処理を行うものです。
 下記のようなメリットがあります。 

  • およそ表のカラム順に格納する値を列挙すればよい
  • XMLカラムに読み込ませるXMLファイルは、ファイル名を記載すればよい
  • 割と高速で処理が可能

 もし、ID列とXML列だけの表であれば、次のような形式のファイル(DELファイル)を作成すればOKです。
 ここで、「00014895.xml」は、ファイルの名称です。格納されているパスはコマンドで指定できます。

14895,"<XDS FIL='00014895.xml'/>"
14896,"<XDS FIL='00014896.xml'/>"
当アプリケーションのケースでは、詳細の説明は省きますが、下記のようなファイルイメージになります。(表の各カラムについては第1回のER図と比較してみてください。)
20092,"{MAIL_DES値}","<XDS FIL='{XMLファイル名}' />","{MESSAGE_ID値}",
"{IN_REPLY_TO値}","2007/11/12 09:41:46.000000","20071112062356",
0,2524,0,3,0
実行している「import」コマンドは次のようなものです。
import from "{delファイル名}" of del xml from "{パス名}" 
modified by TIMESTAMPFORMAT="YYYY/MM/DD HH:MM:SS.UUUUUU"
insert_update into "{スキーマ名}"."{表名}"
ここで重要なのが、「insert_update」というキーワードです。
 これは、「あれば更新し、無ければ挿入して下さい」という意味になります。もし、これ以外を指定するとなると、「replace」となり、意味は「テーブルの全レコードを入替えてください」になります。
 つまり現在のデータベースの表にレコードを追加していく場合は、「replace」は使えませんから、必然的に「insert_update」となります。
 更に、インデックスの作成により、メールの識別番号や、メッセージIDによって重複不可のデータベースの制約を設けているので、もしそれらが重複したメールをインポートしようと思っても「できませんでしたよ」という警告がログに残ります。

 識別番号のほうは、同じファイルの更新なので、運用上は殆ど無いのですが、このメッセージIDの重複に関しては、結構頻繁に発生します。ログファイルに書かれる警告も結構うるさいので、インポートリストから予め除外するような改善を施しました。つまり同一IDの有無をデータベースで確認(フェッチ)して調べてから必要な分だけのDELファイルを作成しています。

 前段にSPAMフィルタを設置するようなケースもあるかと思うのですが、このDELファイルを作成する処理の中に、不要なメールはアーカイブしないようにするためのロジックを組み込んでいます。
 例えば、「件名の先頭に"[SPAM]"のあるメールを除外する・・・」などの処理を有効にさせることが可能です。

 さて、実際に実行しているコマンドは、次のものです。
DB2CMD.exe /c /w /i DB2 -td# -ixf {DDLFile} -z[LogFile]

 パラメータの説明は省略しますが、特に「DB2CMD」の「/w」パラメータは重要なので一言。これはプロセスが終了するまで待機してくれるものです。MAGICの外部コールのDOSコマンドの「Wait」に相当しますが、このパラメータを指定しておかないと全然意味がありません。
 例えば、後続の処理でログファイルを読み込む必要がある場合等は、これを指定しておかないと、正しくログファイルとハンドルしてくれません。

 ログファイルの話題になったので、一言書いておきますが、メールデータの中には、文字コードの関連で、XMLとして不正なデータとなるためにDB2に取り込めないケースが結構あります。それらの内容はログに記載されるので、どのデータがエラーとなったかを記録するための処理を実装しています。

MAGICでのコーディング上のヒントですけど、SQLコマンドなどを実行する時は、同じような手順で処理しますので、SQL文をパラメータとして渡すと実行してくれるような、共通的なプログラムを作っておくと便利かと思います。

 余談ですが、DB2の新しいバージョン9.5では、「インポート」を「ロード」コマンドに置き換えることによって、高速の処理が可能になった模様です。これは、ロードコマンドがログを書き出さないで処理できるようになったためだそうです。
 このあたりは実際にやってみようと思っています。


次回は・・・


 もう少し早い連載を当初は考えていたのですが、多少、遅れ気味で申し訳ありません。
 今回は、なんかいろいろと書いてしまって、ちょっとまとまりがつかなかったんですが、ご勘弁下さい。
 次回はいよいよ検索処理の実際について書いてみようと思っています。



DB2 9 を利用したメール・アーカイブ・システムの開発 (5)



DB2 9.5へ


 ご無沙汰してま~す。
 このシリーズも前回から2ヶ月も経ってしまいました。またボツボツと進めていきますので、お付き合いのほど宜しくお願い致します。
 さて、その間、DB2の周辺でもいろいろな出来事がありましたが、なんと言っても大きいのは9.5のリリースでした。(昨年10月末のリリース)
 9.5のメリットや拡張された機能は沢山ありすぎて、ここで述べるのは大変なくらいです。知れば知るほど使ってみたいと思うものも増えてきます。先日も、今年初めての「クラブDB2」に参加させて頂きましたが、そこでも新機能( ワークロード管理)の実際的なところの紹介があり、興味深い勉強をさせて頂きました。

 今回は、現時点までの弊社9.5移行関連の状況について紹介させて頂きたいと思います。


新バージョンとの並存


 さて9.5を入手したものの、9.1で開発してきた環境はどうなるのかと思いました。
 何台も開発用のマシンがあるわけではないし、開発したアプリのテスト環境にも影響しないかどうかと心配になりました。しかしたまたま、クラブDB2でお会いしたIBMの方にお聞きしたところ、「いろんなエディションも入れられるようですよ」という答だったんです。
 なんだ、複数のDB2エディションがインストールできるのか!(知らなかった・・・)

 ということで、早速9.5をインストールすることになりました。
 複数のDB2のバージョンを同じマシンにインストールしたときの状況は次のようになります。

  •  追加インストールを行うと、「C:\Program Files\IBM\SQLLIB_v95」に新しいバージョンがインストールされる
  • データベースも別個のフォルダ(例えば、「C:\DB2_01」等)に作成される。レジストリ上は「DB2COPY2」という名称で管理されている模様。
  • DB2用のサービスも「DB2COPY1」用とは別個に「DB2COPY2」用のものがもう1セット作成される。
  • データベースの切り替えは、セットアップ・ツールの「デフォルト DB2 およびデータベース・クライアント・インターフェース選択ウィザード」を起動し、デフォルトDB2コピーを「DB2COPY2」を選択することで可能。

新バージョンへのマイグレーション(1)


 9.1から9.5の移行については、次の手順により、殆ど何の問題も無く移行できることが分かりました。(実は半年ほど前にインストールしたベータ版では思うように動作しないなどの経験があったので、大丈夫なのかどうか、ちょっと心配でした。)

  1. 9.1でデータベースをバックアップ
  2. 9.5をインストール
  3. 9.1でバックアップしたものをリストア

 ただ、NSE(Net Search Extender)絡みでは、移行時に若干問題が起きました。

  • NSE用に使用可能にしたデータベースを、db2extmdb マイグレーション・スクリプトを使用してマイグレーションすればよいらしい・・・
  • 実際に実行してみたが、完了のメッセージは出るものの、正常に動作せず?

 私の理解不足が原因かと思いますが、結局テキストインデックスのマイグレーションはできませんでした。しょうがないので、テキスト索引をドロップし、再作成することにより済ませてしまいました。

 このレベルで、1~2ヶ月アプリケーションを動作させてみました。
 どのような機能を使っているかという問題はありますが、前のバージョンと同じように動作するかどうかという互換性の問題では、殆ど問題ないことが分かりました。


新バージョンへのマイグレーション(2)


 9.5で試してみたい機能は、やはりXMLに関連する拡張機能です。
 DB2では、XML列に格納されるデータは基本表とは別のオブジェクト(XMLストレージ・オブジェクト)に格納されていますが、9.5では、32Kまでの指定したサイズより小さいXMLデータは基本表内に格納(基本表行保管)できるようになった模様です。
 この機能を使用した場合、小さいXMLファイルを格納することが多いケースでは、ストレージを圧縮できたり、パフォーマンスも向上するということでした。

 3万件程のメールデータのXMLファイルのサイズを調べたところ、98.8%は32K以下のサイズであることが分かりました。
 ということで、この機能を使うべく、メインとなっているXML格納用の表を変更することにしました。

XMLの基本表行保管機能を使う

 XMLの基本表行保管機能を使うためには、CREATE TABLE または ALTER TABLE ステートメントで、INLINE LENGTH キーワードを指定する必要があります。
 また、デフォルトで作成される表スペース(今まで使用していた表スペース)の行サイズはは4Kなので、32K用の表スペースを新たに作成し、そこに表を作成する必要があります。
 32K用の表スペースを作成するためには、32K用のバッファプールも作成しておく必要があります。
 表スペースを作成する際、データ格納用の表スペース以外に、システム一時領域用と一時表格納領域用の表スペースも別途作成する必要があります。
 表内に他のカラムがある場合は、それらのサイズの合計値も INLINE LENGTH で指定できるサイズに影響します。
 ということで、表の変更を含めたデータの移行手順とその実際をまとめると次のようになります。

  1. EXPORTコマンドにより、XMLと表の情報を出力
    CONNECT TO MAIL_ARC;
    EXPORT TO "U:\export\export.del" OF DEL XML TO "U:\export\xml"
    XMLFILE "data" MODIFIED BY CHARDEL""
    TIMESTAMPFORMAT="YYYY/MM/DD HH:MM:SS.UUUUUU"
    COLDEL, XMLINSEPFILES
     MESSAGES "U:\export\export.log"
    SELECT * FROM MAIL_ARC.MAIL_DB;
    CONNECT RESET;
  2. 既存のXML表、NSE索引のドロップ
    これはコントロールセンターなどで行いました。
    念のために、ドロップする前にデータベースをバックアップしておきましょう。
  3. 32K用バッファプールの作成
    ページサイズを指定して新しいバッファプールを作成します。
    CONNECT TO MAIL_ARC;
    CREATE BUFFERPOOL FOR_XML32K
    IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32 K ;
    CONNECT RESET;
  4. 32K用の表スペースを作成
    作成したバッファプールを指定して、3種の表スペースを作成します。
    「...」の部分には、「EXTENTSIZE 16 OVERHEAD 12.67 PREFETCHSIZE 16 TRANSFERRATE 0.18」等の数値を含んだ文字列が入ります。
    CONNECT TO MAIL_ARC;
    CREATE LARGE TABLESPACE FOR_XML32K PAGESIZE 32 K
    MANAGED BY AUTOMATIC STORAGE ... BUFFERPOOL FOR_XML32K ;
    CREATE SYSTEM TEMPORARY FOR_XML32K_TMP PAGESIZE 32 K
    MANAGED BY AUTOMATIC STORAGE ... BUFFERPOOL FOR_XML32K ;
    CREATE USER TEMPORARY FOR_XML32K_USR PAGESIZE 32 K
    MANAGED BY AUTOMATIC STORAGE ... BUFFERPOOL FOR_XML32K ;
    CONNECT RESET;
  5. INLINE LENGTH キーワードを使用して表を作成
    いよいよ表作成です。INLINE LENGTHで指定する数値は最初32000を指定してみたのですが、行サイズを超えてしまったというエラーが出ました。他のカラムのサイズを考慮する必要があります。「IN」、「INDEX IN」、「LONG IN」でそれぞれの表スペースを指定します。
    CONNECT TO MAIL_ARC;
    CREATE TABLE MAIL_ARC.MAIL_DB (
    MAIL_ID BIGINT NOT NULL ,
    MAIL_DES VARCHAR (254) NOT NULL ,
    MAIL_DATA XML INLINE LENGTH 31000 ,
    MESSAGE_ID VARCHAR (254) NOT NULL ,
    IN_REPLY_TO VARCHAR (254) NOT NULL ,
    TIMESTAMP TIMESTAMP ,
    MAIL_DATE CHARACTER (14) ,
    DATE_ATTR INTEGER ,
    MAIL_SIZE BIGINT ,
    CNT_FILES INTEGER ,
    BODY_TYPE INTEGER ,
    POPT_ATTR INTEGER ,
    CONSTRAINT MAIL_ID PRIMARY KEY (MAIL_ID) ,
    CONSTRAINT MAIL_DES UNIQUE (MAIL_DES) ,
    CONSTRAINT MESSAGE_ID UNIQUE (MESSAGE_ID),
    CONSTRAINT IN_REPLY_TO UNIQUE (IN_REPLY_TO, MAIL_ID)
    )
    IN FOR_XML32K
    INDEX IN FOR_XML32K_TMP
    LONG IN FOR_XML32K_USR
    COMPRESS YES ;
    CONNECT RESET;
  6. INPORT , LOAD コマンドによるXML表データを読みこみ
    さて、いよいよデータの取り込みです。あいにくまだLOADに慣れていないのでIMPORTで実行しました。(LOADコマンドは9.5からXMLを含んだ表に対しても利用できるようになったのですが・・・。)
    2で既に表をドロップしてしまっていますが、REPLACEであれば自動的に入替えされるはずです。
    CONNECT TO MAIL_ARC;
    IMPORT FROM "U:\export\export.del" OF DEL
    XML FROM "U:\export\xml"
    MODIFIED BY TIMESTAMPFORMAT="YYYY/MM/DD HH:MM:SS.UUUUUU"
    CHARDEL""
    MESSAGES "U:\export\import.log"
    REPLACE INTO MAIL_ARC.MAIL_DB;
    CONNECT RESET;
  7. NSE索引の作成と更新
    NSEのテキスト索引がある場合はそれを更新します。


マイグレーションその後


 さて、パフォーマンスやディスクのサイズは変わったでしょうか?

 移行後にとったバックアップファイルのサイズと比較すると、84%ほどに小さくなりました。しかし、これは信頼できる数値結果なのかどうかはまだ良く分かりません。
 肝心の計測方法をどうするかは、すっかり頭に無かったので・・・。(これから考えてみます。(^^;)
 パフォーマンスのほうも少し動作させてみながら・・・。

 ということで、取り敢えず今回は、「9.5の新機能である基本表行保管への対応の実際は、無事成功!」・・・ということで、終わりにしたいと思います。
(きっと、新機能の効果はあった・・・と思いつつ・・・。)



カテゴリ
DB2
mail