本稿はデータベースソフトウェア「SQL Anywhere」およびデータベース全般に関する英語ドキュメントを翻訳する形で提供しています。図など、部分的に英語のままになっていますが、製品のSQL Anywhere自体は完全に日本語化されていますのでご安心ください。
概要
SQL Anywhere 11には驚くほど多くの新機能があります。わずか2年前にSQL Anywhere 10で膨大な数の機能が導入されたことを考えると、SQL Anywhere 11の新機能の多さは驚きというほかありません。この記事では、開発者にとって最も興味深いと思われるSQL Anywhere 11の新機能トップ10を紹介します。トップ10には入らなかった機能の中からも、いくつか興味深いものを取り上げて紹介します（例えば、経管理的な視点から見れば重要かもしれないが開発者の視点では必ずしもクールではない機能など）。また、素晴らしいというより奇妙な機能についてもいくつか簡単に紹介します。
あらためまして、いよいよ皆さん注目のSQL Anywhereアカデミー賞の発表です。自分のお気に入りの機能のうち、どれが選ばれ、どれが落選するか、どんな変わりダネが紹介されるのか...。SQL Anywhere 11「クールな新機能」部門トップ10を紹介しましょう！
11でも問題は10
2年前にSQL Anywhere 10がリリースされたとき、筆者は「10」という数字自体が大きな問題だと書きました。多くのクールな新機能のうち、どの10個を取り上げるかを決めることが難しかったからです。
SQL Anywhere 11でも、「10」という数字は相変わらず悩みの種です。ただ今回の場合は、「まだバージョン10の機能を学習している最中なのに11がリリースされてしまうなんて」という意味ですが。バージョン11のベータフォーラムに、新しい機能の使い方を覚えるために「私の1日の時間を長くしてほしい」という要望が書き込まれていましたが、筆者も同意見です。
SQL Anywhereの変化のスピードがいかに速いか、例を挙げて説明しましょう。筆者が巨大なXMLファイルを解析してSQL Anywhereテーブルに直接ロードするという処理をするために初めてOpen XMLを使ったのはわりと最近のことですが、この機能は9.0.1の頃から導入されています。筆者が4月に「Open XMLの使い方」という記事を投稿すると、それがたちまち筆者のSQL Anywhereブログで一番人気のページになり、それ以来、その人気は衰えを見せていません。
もう1つ例を挙げましょう。HTTPセッションを使ってSQL Anywhereの組み込みWebサーバとの間で状態を維持する機能はバージョン10で導入されましたが、筆者がこの機能を使ってみたのはつい先日のことです。実は、この機能は筆者の選んだバージョン10のトップ10リストに入っていなかったのですが、それは大きな間違いでした。これは本当にクールな機能です。
以上を踏まえて言えば、バージョン11ではバージョン10ほど画期的な更新がなかったため、トップ10を選ぶ作業はそれほど難しくありませんでした。まずは、残念ながらトップ10に入らなかった機能から紹介していきましょう。皆さんのお気に入りの機能がここで紹介されてしまうかもしれませんが、そこはどうか広い心で最後まで読んでもらいたいと思います。
残念ながら選外の機能
ここでは、トップ10リストにランクインされなかったSQL Anywhere 11の新機能を紹介します。
.NET Entity FrameworkとLINQに対するサポートが重要な新機能であることは間違いありませんが、少なくとも筆者にとって、これはクールな機能ではありません。リレーショナルデータベースを使用するのであれば、リレーショナルデータベースの言語を学習し、使用する必要があります。明確に言えば、その言語とはSQLであり、LINQのような分かりにくいラッパーではありません。筆者の考えでは、単純なWHERE句を表現するためにCustomer customer = this._db.Customers.Where( "it.ID = @ID", new ObjectParameter( "ID", id ) ).First();と書かなければならないような言語は合理的とは言えません。
全文検索と正規表現は、LIKE述語の代わりに使用できる2つの新機能です。前者の全文検索は非常にクールであり、トップ10リストに入っていますが、正規表現はランクインしていません。筆者の頭では、「ゼロ幅アサーションの正のルックビハインド」の意味を学んだり、読みやすいSELECT文で書けるような内容を'(?<=new\\s)york'などとコーディングする方法を学習したりするのに長い時間がかかりそうです。正規表現に精通している人はこの機能を歓迎するのでしょうが、保守のしやすさを重視する人は歓迎しないでしょう。
これを選外にしたのは我ながら失敗だったと後悔することになればいいと思っているのが、DCXという機能です。これは"DocCommentXchange"の略で、大げさな名前が付いていますが、簡単に言えばヘルプからWebサイトへのハイパーテキストリンクであり、各トピックについての他人のコメントを読んだり、自分のコメントを入力したりできるようにするものです。DCXのインターフェースの外観はヘルプと非常によく似ているため、リンクをクリックしたときに、ヘルプと同じものを見ているような気がするでしょう（図1を参照）。同じトピックが同じレイアウトで表示され、［Contents］タブ、［Index］タブ、［Search］タブも同じです。新しいのは、各トピックのコメントエリアです。ここには他の人の意見が表示され、自分もそこにログインしてコメントを追加できます。左側の［Recent Comments］タブも新しい機能です。現在、dcx.sybase.comのWebサイトは、SQL Anywhere 10データベース上で動作しています。このWebサイトも近いうちにSQL Anywhere 11上で動作し、Apacheの代わりに組み込みHTTPサーバを使用し、PHPクライアントサーバアプリケーションの代わりにPHPで保存されたプロシージャを使用する構成にする予定です。さらに、LIKEの代わりに全文検索を使用するかもしれません。筆者はこれらすべてが実現することを望んでいます。実現したあかつきには、DCXをトップ10に含めるべきだったと後悔するようになるでしょう（DCXはversion12より日本語化予定です）。
もう1つ、トップ10に入れるかどうか悩んだ機能がありました。それはUltra Lightでのバックグラウンド同期のサポートです。この機能を利用すると、ハンドヘルドデバイスに大量のコードを展開するときに、Mobile Link同期の進行中にユーザーが操作を継続してもデータがめちゃくちゃにならないようにするアプリケーションを比較的簡単に設計できます。この機能があれば、2番目のスレッドで同期を開始することができ、Ultra Lightは既にコミットが完了している変更だけをアップロードできます。それ以降に加えられた変更は、次の同期の際にアップロードされます。この機能がクールである理由ですか？ 筆者のクライアントの中で最初にSQL Anywhere 11への移行を決めてくれた会社が、移行を決めた最大の理由としてこの機能を挙げたからです。トップ10リストに含めるには不十分ですが、この記事で触れるには十分にクールな機能だと言えます。
その他に、縁の下の力持ちのような存在の非常に重要な機能を2つ紹介しておきます。
- インデックスのみの取得機能。クエリを満たすために必要なデータがインデックスで見つかった場合は、SQL Anywhereはテーブルの検索さえ行いません。
- 複数インデックススキャン機能。クエリを満たすために、同じテーブル上の最大4個の異なるインデックスを使用できます（現在の制限では各テーブルにつき1つのインデックスのみ使用可能）。
一部のクエリは、この2つの機能のおかげで速度が飛躍的に速くなります。これが、これらの機能をここで紹介した理由です。この2つは縁の下の力持ちのような機能のため、クールな新機能トップ10には入れていません。ミニバンの購入を検討するときにカップホルダーを見たがる人はいますが、カムシャフトについて知りたがる人はあまりいませんね。それと同じことです。
Mobile Link同期環境ではMySQLを統合データベースに使用できます。つまり、Mobile LinkがMySQLと連携して機能するようになったのです。個人的に言わせてもらえば、なぜMySQLを使う人がそんなに多いのかよく分かりません。MySQLは、設定やオプションが難しくて管理しにくく、落とし穴や混乱がつきもので、しかも（サポートを利用する場合は）無料ではありません。それにもかかわらず、MySQLは人気があります。そしてこのたび、MySQL環境でSQL Anywhereを使い、Mobile Link経由でレガシーのMySQLデータベースと同期する自己管理型のリモートデータベースを実装できるようになりました。レガシーと言えば、Mobile Linkは、メインフレーム上のDB2と連携して動作するようにもなっています。あまりクールな機能とはありませんが、もしこの機能をきっかけにSQL Anywhereの世界に足を踏み入れる人がいるならば、もちろん非常にクールだと言えるでしょう。
まずは、気まぐれのカテゴリに属する話です。WindowsのSQL Anywhere実行可能フォルダは、現在、"win32"ではなく、"Bin32"になっています。つまり、"%SQLANY10%\win32 \..."などのパスが含まれたバッチファイルがある場合は、"%SQLANY11%\Bin32\..."のように2箇所を変更する必要があります。こうなった責任はMicrosoftにあるとだれかが言っていましたが、あの巨人の気まぐれに合わせるのは無理な話でしょう。
最新バージョンでは、既定で"Printable Books"バージョンのヘルプがインストールされるようになっています。つまり、あの巨大なPDFファイル群をインストールしたくない場合は、ドキュメントをインストールするときに［Custom］オプションを選択する必要があるのです。通常のコンパイル済みHTMLバージョンのヘルプもあります。従来どおり、すべてのマニュアルが1箇所にまとまっていて、全文検索機能が利用できるという優れものです。だれが紙を必要とするのでしょうか？ まったく奇妙な話です。
SQL以外の言語でストアドプロシージャを書く機能は大幅に拡張され、改善されました。この機能はクールな新機能トップ10にランクインしています。ただ、奇妙な点が1つあります。もしあなたの選んだ言語がたまたまCまたはC++だった場合は、ヘルプの次の行を読んで落胆することでしょう。「また、コンパイル済みネイティブ関数はネイティブ関数呼び出しAPIを使用する必要があるという点にも注意すること」。このAPIはずっと以前から使われているものですが、あらゆるコーディング世界において最悪のインターフェースの1つであり、今なおそれを使わなければならないというのは不幸としか言いようがありません。もっとも、この要件が課されるのはCとC++だけの話で、Perl、PHP、Visual Basicやその他の言語では使う必要がありません。このAPIが以前よりも悪くなっていないことがせめてもの救いです。
INSTEAD OFトリガーは最近のクールな新機能です。奇妙なのは、エンジニアがシステムカタログテーブルに対するトリガーを用意してくれなかったことです。CREATE TABLEなどのスキーマオブジェクトを動的に操作する高度なアプリケーションが作成されることが多くなっており、それらの変化を動的にキャプチャし、追跡する機能は歓迎されるはずです。システムカタログトリガー内のコードがユーザーテーブルしか操作できず、それ以上のスキーマの変更を発行できないとしても、ニーズの80％以上は満たされるはずです。おそらく、あの使いにくい関数呼び出しAPIが改良されるよりも、この機能が追加される方が早いことでしょう。
クールな新機能トップ10
紹介する順番はあまり気にしないでください。10個の機能を選ぶだけでもなかなか大変でした。あえてランク付けしようとは考えていません。
1. 変数へのUNLOAD
LOAD文とUNLOAD文がファイルだけでなく、変数に対しても機能するようになりました。変数を利用してファイル仕様を保存するだけでなく、変数やファイルに保存されたデータをロードしたりアンロードしたりすることが可能になりました。テーブルのすべてのローとカラムを1つの文字列変数にUNLOADしたり、逆に、文字列からテーブルをLOADしたりできます。
BEGIN DECLARE s LONG VARCHAR; UNLOAD TABLE t1 TO s; LOAD TABLE t2 FROM s; END;
ファイルの代わりに変数を使うことの利点を以下に挙げます。
- 同時性の改善。ファイルはグローバルであるのに対して、変数は接続にローカルのため、同時性が改善されます。
- 柔軟性の向上。SQL Anywhereには文字列を操作する強力な関数がたくさんありますが、ファイルを操作する関数の数はそれほど多くありません。
- 処理速度の向上。ファイル入出力が行われないため、速度が向上します。ただし、調子に乗ってINSERT SELECTの代わりにUNLOAD LOADなどとしないように注意してください。そこまで速くはなりません。
この機能はUNLOAD SELECTやUNLOAD TABLEでも有効のため、変数に保存されたデータを完全に制御するクエリを書くことができます。
個人的な話になりますが、この機能については少々複雑な思いがあります。筆者は長年にわたってこの機能を要求していたのですが、初めて使用できるようになったときには、その使い道を思いつくことができませんでした。というのも、この機能が実現するまでの間に、他の機能を利用して問題を解決できるようになってしまったからです。最も重要だったのは、拡張LIST集約関数です。LIST関数とSTRING関数をLISTのデリミターおよびORDER BY句と組み合わせて使用することで、集合指向の文字列操作を実行できます。例えば、1つのSELECTを使って、複数のテーブルをジョインし、データをフォーマット済みのHTML Webページに変換できます。
筆者はずば抜けて頭がいいとは言えません。新しい機能が使えるようになったからと言って、直ちにその使い道が分かるわけではありません。LISTの場合もそうだったし、変数へのUNLOADの場合も同じことが言えます。ただし、だれかが使い道を教えてくれれば話は違います。その場合の飲み込みは早い方です。Ivan Bowmanがこの新機能を次に紹介する機能と組み合わせる方法を教えてくれたときがそうでした。
2. FROMOPENSTRING
新しいOPENSTRING句を使うと、SELECTのFROM句でファイルまたは変数の名前を指定し、SQL Anywhereにそのファイルまたは変数の内部のデータをローとカラムの集合として処理させることができます。これはプロキシテーブルを使って、ODBCリモートアクセスミドルウェアを介してファイルをテーブルのように処理する方法に少し似ていますが、それよりずっと簡単で、柔軟性が高い方法です。それに、OPENSTRINGでは、ファイルだけでなく、変数も使えます。
この機能についても、少々ばつの悪い思い出があります。当初、筆者はこれをよくある使いにくいXMLのたぐいだと考えていました。最近は何にでも「オープン（open）」という言葉が使われすぎていて、その言葉を聞くだけでうんざりしていたのです。そこで、OPENSTRINGという機能を頭から無視して、「そのうち車にはねられた動物の死骸（corpse）のことを『OpenCorpse』とかなんとか呼ぶようになるんだろうよ」などと思っていました。
ところが、Wikipediaの「IvanAnywhere」として名高いIvan Bowmanが、OPENSTRINGを変数へのUNLOAD SELECTと組み合わせて使用する例を見せてくれたとき、私の考えは変わりました。ここで紹介しておきましょう。
「私が考えた便利な使用例は次のようなものです（ここで使用しているSYSDOMAINはただの例です）。
CREATE VARIABLE @var LONG VARCHAR; UNLOAD SELECT * FROM SYSDOMAIN INTO VARIABLE @var; CREATE VIEW V AS SELECT * FROM OPENSTRING ( VALUE @var ) WITH ( domain_id SMALLINT, domain_num VARCHAR ( 128 ), type_id SMALLINT, "precision" SMALLINT ) AS T;このコードが何をやっているかと言えば、結果セットを接続レベル変数
@varにコピーし、ビューVを使ってその結果セットを参照しています。@varの値は接続プロシージャで作成できます。これは、ホットデータのローカルオプションの設定や接続ローカルキャッシュを管理する（ホットなローの競合を回避する）適切な方法になり得ます。この種の機能の使い方には注意が必要ですが、これは必要なときにすぐ使える便利なツールです。ビューに対するINSTEAD OFトリガーを作成して、ビューをテーブルのように見せることもできます。
OPENSTRING機能は、INリストが文字列として指定されている動的なINリストの処理に最適と思われます。この機能を利用すれば、SQL Anywhereでは直接サポートされていないマルチカラムのINリストを使用することもできます。
OPENSTRINGをLISTと組み合わせると面白い使い道があるかもしれません。これは1つのカラム値をデコードして結果セットを得るラテラルジョインのように見えます。私は過去に何度も
sa_splitlist()を使って面白いソリューションを構築してきましたが、いずれもOPENSTRINGで対応することができそうですし、別の要件にも利用できそうです。なぜなら、OPENSTRINGはカラムだけでなくローのデコードも行い、従来の方法よりずっと高速で、複雑なデータを処理するための解析オプションをより多く備えているからです。私は、これらの新機能が実際の現場でどのような用途に使われるのかに興味があります。」Ivan T. Bowman（アイエニウェア社エンジニア）
Ivanの最後のコメントについては、まさに同感です。新しい機能がどのように利用されていくかは簡単には予想できません。LIST関数のように直ちに受け入れられるものもあれば、ANSI標準の再帰結合のように敬遠されるものもあるでしょう。おそらくOPENSTRINGは人気の機能になるのではないでしょうか。変数へのUNLOADもそうなると思います。
Ivanは、これまでどこのトップ10リストにもランクインしていない、バージョン10.0.1でひっそりと導入されたもう1つの機能についても語っています。その機能とはINSTEAD OFトリガーです。このトリガーを利用すると、テーブルやビューに対して、トリガーアクションの代わりに実行されるトリガーを書くことができます。例えば、INSTEAD OF INSERT ON Tトリガーを用意しておくと、INSERT T文が実行されたときにこのトリガーが起動しますが、このトリガーではローの挿入を行いません。INSERT Tの結果として実行したいアクションがある場合は、このトリガーの内部にコーディングする必要があります。
この機能がクールなのは、ビューに対するトリガーを書けるという点です。ビュー自体が更新可能でない場合でも、そのビューに対するトリガーを作成できるのです。INSERT、UPDATEまたはDELETEをトリガーしても、それに対応するINSTEAD OFトリガーが存在しさえすれば、挿入、更新または削除は実行されないため、ビューが更新可能かどうかは問題になりません。また、このトリガー内には実行したいアクションを自由にコーディングできます（もちろんCOMMITをコーディングすることはできません。その制約は変わりません）。
3. 即時マテリアライズドビュー
部署ごとの従業員リストの作成に使われるマテリアライズドビューの例を以下に示します。
CREATE MATERIALIZED VIEW DeptList AS SELECT Departments.DepartmentID, Employees.EmployeeID, Departments.DepartmentName, Employees.GivenName, Employees.Surname, Employees.Phone FROM Departments INNER JOIN Employees ON Employees.DepartmentID = Departments.DepartmentID;
マテリアライズドビューは、使用する前に必ず初期化する必要があります。
REFRESH MATERIALIZED VIEW DeptList;
通常の（手動の）マテリアライズドビューでは、ベーステーブルを変更しても、その後でREFRESHを実行しなければ、古いデータが表示されます。
UPDATE Employees SET DepartmentID = 400 WHERE EmployeeID = 1336; SELECT * FROM DeptList ORDER BY DepartmentName, Surname, GivenName; DepartmentID EmployeeID DepartmentName GivenName Surname Phone ============ ========== ============== ========= ======= ========== 300 1336 Finance Janet Bigelow 6175551493
SQL Anywhere 11からは、ベーステーブルに対して行ったすべての更新がマテリアライズドビュー内のデータに直ちに適用されるようにビューを変更することができます。これを行うための手順を以下に示します。
TRUNCATE TABLE DeptList; CREATE UNIQUE INDEX xid ON DeptList ( DepartmentID, EmployeeID ); ALTER MATERIALIZED VIEW DeptList IMMEDIATE REFRESH; REFRESH MATERIALIZED VIEW DeptList;
手動のビューに既にデータが挿入されている場合は、TRUNCATE TABLEが必要です。ビューを即時ビューに変更するには、ビューを空にする必要があります。CREATE UNIQUE INDEXも即時ビューの要件です。SQL Anywhereがローを更新するときに、目的のローを見つけ出せるようにしなければならないからです。上記のALTER文では、ビューのリフレッシュモードをIMMEDIATEに変更しています。また、最初にデータをビューに挿入するためにREFRESHが1回必要です。確かにステップ数は多くなりますが、1回だけ実行すれば済みます。
ベーステーブルを更新してからビューをクエリすると、明示的なREFRESHを新たに実行しなくても、新しいデータが直ちに表示されます。
UPDATE Employees SET DepartmentID = 400 WHERE EmployeeID = 1336; SELECT * FROM DeptList ORDER BY DepartmentName, Surname, GivenName; DepartmentID EmployeeID DepartmentName GivenName Surname Phone ============ ========== ============== ========= ======= ========== 400 1336 Marketing Janet Bigelow 6175551493
マテリアライズドビューがクールである本当の理由は、そのビューが存在するかどうかわからなくても利用できることです。次のクエリではベーステーブルを使用していますが、図2は、SQL Anywhereがベーステーブルの代わりにDeptListビューを使用したことを示しています。
SELECT Departments.DepartmentName, Employees.GivenName, Employees.Surname, Employees.Phone FROM Departments INNER JOIN Employees ON Employees.DepartmentID = Departments.DepartmentID;
4. 下位互換性が復活
バージョン10で作成したデータベースがある場合は、リビルドしなくても、SQL Anywhere 11でそのデータベースを実行できます。非常に大規模なデータベースでは、この機能が重要です。時間がかかるうえに不可逆のリビルドを行わなくても、バージョン11でデータベースのテストを開始できるからです。
バージョン10の導入時に驚いたのは、それ以前のバージョンで作成されたすべてのデータベースのリビルドを要求されたことです。バージョン9までは、古いデータベースファイルを使用できるのが当たり前で、たとえバージョン5.5で作成したデータベースでもそのまま使用できました。バージョン10でその互換性の道が絶たれたわけですが、それにはもっともな理由がありました。その理由とは、データベースエンジンから大量のレガシーコードを追放すること、コードのメンテナンス作業をなくすこと、それによって生まれた時間をもっと重要な分野（例えばクールな新機能の習得など）に費やすことです。
バージョン11で新しい互換性の道が開かれました。10から11への移行ではリビルドは必要ありません。それより前のバージョン、例えば9から11へ移行する場合などは、相変わらずリビルドが必要です。
もう1つ重要なことがあります。パフォーマンスを重視する場合は、10から11に移行すると決定した後、直ちにリビルドを行った方がよいでしょう。バージョン11で導入された新しいデータベースファイル形式の利点を活かすには、完全なアンロードとリロードを実行するしかありません。
5. Perl、PHP、C#、VBでのストアドプロシージャ
SQL Anywhere 11では、9つの言語でストアドプロシージャを書けるようになりました。Perl、PHP、C#、Visual Basicが追加され、Java、C、C++および2つのSQL言語（ANSI/WatcomとTransact）が引き続きサポートされます。実際、使える言語の数は9より多いかもしれません。例えば、Fortranの.Net 2.0 CLRバージョンも機能するはずです。ただし、Tech Support Languageの担当者が言う「機能するはず」という言葉は、「実際に試すのはあなたが最初かもしれませんが」という意味であると覚えておいた方がいいでしょう。
ストアドプロシージャについてのサポートの詳細は言語によって異なります。例えば、VBは結果セットを返せるのに対して、PerlはLONG VARCHAR文字列しか返せません。
特筆に値するのは、非SQL言語で書かれたプロシージャをデータベースサーバ自体から完全に独立した実行可能環境で実行できるようになったことです。これはつまり、プロシージャがクラッシュしても、その道連れでデータベースがダウンするおそれはないということです。この場合も詳細は言語によって異なります。例えば、Perlでは接続ごとに1つの外部環境が設定されますが、VBではデータベースごとに1つの外部環境しか開始されません。表1はさまざまな言語でのサポートの違いをまとめたものです。
| 1つの外部環境が設定される単位 | プロシージャが返せるもの | |
| Perl | 接続 | LONG VARCHAR |
| PHP | 接続 | LONG VARCHAR |
| C#、VB | データベース | 結果セット |
| Java | データベース | 結果セット |
| C、C++ | 接続 | 結果セット |
| SQL | なし | 結果セット |
さまざまな言語で書かれたプロシージャを安全に呼び出せる機能は、それ自体をトップ10のリストに入れてもいいくらい素晴らしいものです。以前は、CやC++のプロシージャがデータベース内部で実行されており、コードが暴走すると、1つのユーザー接続がダウンするだけでなく、サーバがクラッシュして全員が被害を受けるという壊滅的な結果が生じました。
もう1つの朗報は、非SQL言語で書かれたプロシージャの内部で現在のデータベース接続を使用できるようになったことです。つまり、プロシージャを呼び出したデータベースにSQL文を渡すために新しい接続を開始する必要がなくなったのです。以前のバージョンでは、CまたはC++のストアドプロシージャの内部からSQL文を発行することは一切できず、新しい接続を開始したくても開始できませんでした。現在は、すべての言語で同一接続によるデータベースアクセスが可能です。
これはクールな機能です。例えば筆者は、データをやり取りする際の手間を減らすために、データベースに埋め込みたいコードを山ほど持っています。1つの例はファイル比較ユーティリティ用のAPIです。データベース内にBLOBとして保存されるドキュメントに対して使用する比較ユーティリティ用のAPIをデータベースに埋め込み、SQL Anywhereの組み込みHTTPサーバを使って、ブラウザからのWebサービス呼び出しを介して比較結果を表示できるようにすることを考えています。
6. インメモリサーバモード
インメモリサーバオプションは、耐久性を犠牲にして挿入と更新の速度を向上させるまったく新しい動作モードです。「SQL Anywhere Classic」モードでデータベースを作成してロードした後に、そのデータベースを「Never Write（非書き込み）」または「Checkpoint Only（チェックポイントのみ）」という2つのインメモリモードのどちらかで実行できます。2つのインメモリモードを切り替えたり、Classicモードに戻したりするには、エンジンを停止してから再開します。ソフトウェア自体は同じで、使用するコマンドラインパラメータが異なるだけです。
読者が混乱しないよう先に説明しておきますが、SQL Anywhere 11は、MySQLのMyISAMのような非トランザクション型・自動コミット方式のストレージエンジンのようなものを採用しているわけではありません。SQL Anywhereは完全にリレーショナルなデータベース管理システムであり、完全なトランザクション型で、インメモリモードで動作しているときでさえ、ほとんど完全にACIdを実現しています。
ACIdとは、トランザクションに求められる4大要素であるAtomic（原子性）、Consistent（一貫性）、Isolated（独立性）、Durable（耐久性）を意味しています。耐久性とは、COMMITに永続性があることを意味しています。筆者がACIdの「d」を小文字にしている理由は、Checkpoint Onlyインメモリモードでは、コミットされたすべてのトランザクションが最後のチェックポイントまで保存されますが、そのチェックポイントを超えるフォワードリカバリが不可能だからです。さらに、Never Writeインメモリモードでは、その名のとおり何も保存されず、リカバリも不可能です。
インメモリモードが効果を発揮するのは、データベースのRAMキャッシュがデータベース全体を保存するのに十分なほど大きい場合か、大量の挿入、更新、コミットを実行している場合、あるいは速度を大幅に向上させるために多少の安全性を犠牲にすることを厭わない場合です。筆者の考えでは、このモードが威力を発揮するのは継続的モニタリングのようなアプリケーションです。つまり、短期間に大量のデータを受信し、クラッシュ時にデータの完全復旧を保証することよりも入力のスピードに追いつくことの方が重要であるアプリケーションが最有力候補です。
どちらのインメモリモードでもキャッシュ内のデータを更新できます。つまり、Never Writeモードは読み取り専用を意味しているわけではありません。Checkpoint Onlyモードでは、更新されたデータがCHECKPOINTでデータベースファイルに書き込まれます。Never Writeモードでも、永続性を重視するのであれば、データを保存できます。データの保存を迅速に行うには、データベースバックアップ、データベースアンロードユーティリティ、個別のUNLOAD TABLE文の3つの方法があります。表2はNever WriteモードとCheckpoint Onlyモードの比較、および2つのモードとClassicモードとの比較を示しています。
| -imnw Never Write | -imc Checkpoint Only | Classic | |
| トランザクション型 | ○ | ○ | ○ |
| .dbファイルの更新 | × | ○ | ○ |
| トランザクションログ | × | × | ○ |
| 一時ファイル | × | × | ○ |
| チェックポイントログ | × | ○ | ○ |
| チェックポイント | × | ○ | ○ |
| ダーティページフラッシュ | × | ○（a） | ○ |
| 無制限拡張 | × | ○（b） | ○ |
| リカバリ | × | ○（c） | ○ |
- （a）ダーティページはチェックポイントでのみフラッシュされます。
- （b）一時ファイルがない場合、キャッシュが満杯になるのを防止する手段はチェックポイントに限られます。
- （c）クラッシュ後の再起動では、最後の有効なチェックポイントまでの通常の自動リカバリが実行されますが、トランザクションログがない場合、最後の有効なチェックポイントからコミットされた最後のトランザクションまでのフォワードリカバリを実行することはできません。
7. MERGE文
新しいMERGE文を使用すると、1つのテーブルから別のテーブルへデータをコピーしなければならないときに、INSERT、UPDATEおよびDELETEのロジックで集合指向の処理を行うことができます。簡単な例を紹介しましょう。まず、次に示す入力テーブルとトランザクションセットを見てください。
CREATE TABLE transaction ( part_number INTEGER NOT NULL PRIMARY KEY, action VARCHAR ( 6 ) NOT NULL, description LONG VARCHAR NULL, price DECIMAL ( 11, 2 ) NULL ); INSERT transaction VALUES ( 1, 'Add', 'widget', 10.00 ); INSERT transaction VALUES ( 2, 'Add', 'gizmo', 20.00 ); INSERT transaction VALUES ( 3, 'Add', 'thingie', 30.00 ); INSERT transaction VALUES ( 4, 'Add', 'bouncer', 40.00 );
対応する出力テーブルと、上記の4つの入力'Add'トランザクションを1つのINSERT操作にまとめるMERGE文を次に示します。
CREATE TABLE part ( part_number INTEGER NOT NULL PRIMARY KEY, description LONG VARCHAR NOT NULL, price DECIMAL ( 11, 2 ) NOT NULL, exceptions LONG VARCHAR NOT NULL DEFAULT '' ); MERGE INTO part USING transaction ON PRIMARY KEY WHEN NOT MATCHED AND transaction.action = 'Add' THEN INSERT ( part_number, description, price ) VALUES ( transaction.part_number, transaction.description, transaction.price ) WHEN MATCHED AND transaction.action = 'Change' THEN UPDATE SET part.description = COALESCE ( transaction.description, part.description ), part.price = COALESCE ( transaction.price, part.price ) WHEN MATCHED AND transaction.action = 'Delete' THEN DELETE WHEN MATCHED AND transaction.action = 'Add' THEN UPDATE SET part.exceptions = STRING ( part.exceptions, 'Duplicate Add ignored. ' ) WHEN NOT MATCHED AND transaction.action = 'Change' THEN INSERT VALUES ( transaction.part_number, transaction.description, transaction.price, 'Change accepted, new part inserted. ' ) WHEN NOT MATCHED AND transaction.action = 'Delete' THEN SKIP;
このMERGE文の意味は次のとおりです。
- INTO句で、出力テーブルつまりターゲットテーブルを指定します。
- USING句で、入力テーブルつまりソーステーブルを指定します。
- ON句で、ローのマッチングを行う方法を指定します。この例では、両方のテーブルに対してプライマリキーを使用します。
- 最初のWHEN句では、'Add'トランザクションが部品（part）テーブルのいずれのローとも一致しない場合に実行する操作を指定します。
- 2番目のWHEN句では、'Change'トランザクションが一致したときに実行する操作を指定します。この例では、descriptionカラムまたはpriceカラム、あるいはその両方のカラムのUPDATEを指定しています。
- 3番目のWHEN句では、一致する'Delete'トランザクションをDELETE操作に変換します。
- 4番目のWHEN句では、'Duplicate Add ignored.'というエラーを記録します。
- 5番目のWHEN句では、一致しない'Change'トランザクションをINSERTに変換します。
- 最後のWHEN句では、テーブルにない部品を'Delete'しようとする試みを単に無視します。
このMERGE文の初回実行では、最初のWHEN句が4回実行され、部品テーブルにデータが挿入されます。
part_number description price exceptions =========== =========== ===== ========== 1 widget 10.00 2 gizmo 20.00 3 thingie 30.00 4 bouncer 40.00
ここから面白くなります。6つの新しいトランザクションが残りのすべてのWHEN句を実行します。
TRUNCATE TABLE transaction; INSERT transaction VALUES ( 1, 'Change', NULL, 15.00 ); INSERT transaction VALUES ( 2, 'Change', 'gizmoid', NULL ); INSERT transaction VALUES ( 3, 'Delete', NULL, NULL ); INSERT transaction VALUES ( 4, 'Add', 'bouncer', 45.00 ); INSERT transaction VALUES ( 5, 'Change', 'gong', 55.00 ); INSERT transaction VALUES ( 6, 'Delete', NULL, NULL );
再びMERGEを実行すると、部品テーブルは次のようになります。
part_number description price exceptions =========== =========== ===== ========== 1 widget 15.00 2 gizmoid 20.00 4 bouncer 40.00 Duplicate Add ignored. 5 gong 55.00 Change accepted, new part inserted.
ただし、MERGE文にも1つ欠点があります。複数の入力ローで1つの出力ローを更新できないのです。これはSQL Anywhereの落ち度ではなく、例のロクでもない委員会が決定したことであり、この点はMERGE文についての最大の不満になることでしょう。「ANSI SQL/2003標準では、マージ操作中にソースオブジェクト内の複数のローがターゲットオブジェクト内のローを更新することが許されない」のです。MERGE文について2番目に要望が多いのは、おそらくWHEN句からプロシージャを呼び出す機能だと思われます。
8. CREATE EVENT ... TYPE DEADLOCK
循環デッドロックを検出し、報告する新しいEVENTタイプが導入されています。デッドロックとは、使用率の高いシステムで時々発生する現象であり、2つの異なる接続がそれぞれ目的のローをロックした後、直ちに相手がロックしているローのロックを要求することです。デッドロックが発生すると、両方の接続がブロックされ、どちらの接続も必要なものを取得できず、第三者がデッドロックを解消するアクションを起こさない限り、どちらの接続の動作も進展しません。
その第三者の役割を果たすのがSQL Anywhereです。これまでのSQL Anywhereの機能でも、デッドロックを検出し、デッドロックを「解決」することができました。その方法とは、どちらか一方の接続を犠牲にして、ROLLBACKを発行し、そのアプリケーションにエラーメッセージを返すというものです。
そこまでのところに問題はありません。一方のユーザーが勝ち、もう一方のユーザーは処理をやり直し、メンテナンスプログラマーはエラーを探すことになります。そして、ほとんど常に、原因はプログラミングのエラーにあります。SQL文を実行する順序に何らかの欠陥があるのです。問題は、どのテーブルまたはローがデッドロックに関係していたのか、あるいはそれぞれの接続がエラーの発生時に何をしていたのか、ということを判別する手掛かりが標準のデッドロックエラーメッセージからはまったく得られないことです。この問題を解決するのが新しいCREATE EVENT ... TYPE DEADLOCK文です。この文を使用すると、デッドロックに関連するすべての詳細情報を記録し、出力できます。記録される詳細情報には、両方の接続の接続番号とユーザーID、デッドロック発生時に実行されていた実際のSQL文が含まれます。
CREATE EVENT deadlock TYPE DEADLOCK HANDLER BEGIN -- Copy fresh entries from sa_report_deadlocks() INSERT deadlock ( snapshotId, snapshotAt, waiter, who, what, object_id, record_id, owner, is_victim, rollback_operation_count ) SELECT * FROM sa_report_deadlocks() WHERE NOT EXISTS ( SELECT * FROM deadlock WHERE deadlock.snapshotId = sa_report_deadlocks.snapshotId AND deadlock.snapshotAt = sa_report_deadlocks.snapshotAt ); COMMIT; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' deadlock' ) TO CONSOLE; END;
ここで使用した組み込みのsa_report_deadlocks()プロシージャは新しいものではありませんが、SQL Anywhere 11で大幅に改良されました。このプロシージャは、サーバの起動後に発生した個々のデッドロックについて、犠牲になった接続ともう一方の接続に対応する少なくとも2つのローが含まれた結果セットを返します。INSERTはそのデータを「deadlock」という名前の常設ユーザー定義テーブルにコピーします。WHERE NOT EXISTSは、新たなデッドロックが発生したときに古いローが再びコピーされることを防止するために必要です。
「deadlock」テーブルは次のようになります。人工的なプライマリキーとして機能する「row_number」という余分なカラムが1つあり、さらにsa_report_deadlocks()が返したすべてのものに対応するカラムがあります。
CREATE TABLE deadlock ( row_number BIGINT NOT NULL DEFAULT AUTOINCREMENT, snapshotId BIGINT NOT NULL, snapshotAt TIMESTAMP NOT NULL, waiter INTEGER NOT NULL, who VARCHAR ( 128 ) NOT NULL, what LONG VARCHAR NOT NULL, object_id UNSIGNED BIGINT NOT NULL, record_id BIGINT NOT NULL, owner INTEGER NOT NULL, is_victim BIT NOT NULL, rollback_operation_count UNSIGNED INTEGER NOT NULL, PRIMARY KEY ( row_number ) );
これを機能させるには、次のように指定して、サーバーにレポートデータを収集するように指示する必要があります。
SET OPTION PUBLIC.LOG_DEADLOCKS = 'ON';
'LastStatement'接続プロパティを有効にして、ブロックされた実際のSQL文がレポートデータに表示されるようにすると、作業が非常に楽になります。それを行う最も簡単な方法は、サーバを起動するときにdbsrv11 -zlオプションを指定することです。
図3は、デッドロックの後に、次のクエリによって表示された情報を示しています。waiterは接続番号、whoはユーザー名、whatはCONNECTION_PROPERTY('LastStatement')、is_victimはROLLBACKの指示を受けた接続を示しています。
SELECT waiter, who, what, is_victim FROM deadlock ORDER BY row_number;
9. 価値ある小さきもの達
SQL Anywhereのどのリリースでも、数多くの機能がひっそりと導入されています。これといった喧伝がされないのは、それらの機能が重要であるとだれも思っていないからです。ときには、そのうちの1つが人気を集め、重要性を認められることもあります（例えばUPDATEやINSERTと連携して機能する素晴らしいDEFAULT TIMESTAMPなどがそうです）。筆者は、このような機能を「価値ある小さきもの達」と呼んでいます。次に示すリストの中には、本来ならばトップ10に値する（にもかかわらずその真価がまだ認められていない）機能が1つぐらい潜んでいるかもしれません。
- 関数呼び出しPROPERTY('TCPIPAddresses')は、「自分はいま正しいサーバに接続しているだろうか」という疑問に答えてくれるものであり、サーバがリッスンしているアドレスを192.168.1.51:49270のような形式で返します。
- Ctrlキーを押すことによって、dbisql内のコードのブロックをコメント化したり、コメント化を解除したりできます。
- UPDATEおよびINSERTでDEFAULTキーワードを使用できます（例：UPDATE t SET c = DEFAULT）。
- ENDIFとEND IFを完全に同じものとして使用できます。どちらをIF文と組み合わせて使用し、どちらをIF式と組み合わせて使用すればよいのかを覚える必要がなくなりました。
- dbisqlの"SQL Statements"フレーム内のコードに行番号が表示されます。
- @configurationファイル内でアンパサンド（&）を行継続文字として使用できます。この機能は、High Availabilityおよび組み込みHTTPサーバ用に長いパラメータを設定するときに非常に便利です。
- HTML、SOAP、XML、Raw形式のほかに、JSON、つまりJavaScript Object NotationをWebサービスの結果セットに利用できるようになりました。JSONが我々をXMLから救ってくれるかもしれません。
- 新しいPRIORITYオプションを利用して、アプリケーションのSQL要求が実行される優先レベルを調整できます。例えば、高速のOLTP接続については優先度を「Critical」に設定し、実行時間の長いOLAPクエリについては「Background」（または「Critical」と「Background」の中間にある5つのレベルのいずれか）に設定できます。
- 変数へのUNLOADに関心がない場合でも、LOADとUNLOADのその他の拡張機能の中には気に入るものがあるかもしれません。例えば、暗号化機能、圧縮機能、dbisqでLOADとUNLOADを使用する機能などがあります。あるいは、オプションのトランザクションロギング機能を利用して、ミラーリングされたデータベース環境でLOADとUNLOADを使えるようにするという案はどうでしょうか。
- ミラーリングされたデータベースと言えば、高可用性セットアップのセカンダリサーバに対して読み取り専用クエリを実行できるようになりました。この機能を利用すると、重いOLAPセッションをプライマリOLTPサーバから移動して、セカンダリサーバをフェールオーバー以外の目的にも使うことができます。
- NewPassword接続パラメータを利用すれば、ユーザーは現在のパスワードが期限切れになっている場合でも、新しいパスワードを指定できます。
期限切れのパスワードで思い出したのですが、ログインポリシーの分野では数多くの新機能が追加されています。率直に言って、セキュリティはクールなトピックとは思えないので、これはトップ10のリストに入っていません。しかし、DBAを呼び出さずにログインできる機能には、金に代えられない価値があります。
10. 全文検索
もしもこの記事のテーマが「新機能ベストワン」だったとしても、やはり全文検索を外すわけにはいきません。これはSQL Anywhere 11の一番クールな機能です。全文検索では、複数のLONG VARCHARカラムにまたがる高速検索を実装するために個別のソフトウェアを使用する必要がなく、その検索を実行するためにデータベースの外部にデータを保存する必要もありません。一度でも大きいテーブルに対してSELECT LIKE '%word%クエリを実行したことがある人ならば、「遅い」という言葉の意味が分かるでしょう。そういう人にお勧めなのが全文検索です。
それでは、全文検索の使い方の例をお見せしましょう。この例では、英語バージョンのWikipediaからダウンロードした650万個の項目すべてが含まれたテーブルを使用します。
CREATE TABLE enwiki_entry ( -- 6,552,490 rows, 17.2G total page_number BIGINT NOT NULL, from_line_number BIGINT NOT NULL, to_line_number BIGINT NOT NULL, page_title VARCHAR ( 1000 ) NOT NULL, page_id VARCHAR ( 100 ) NOT NULL, page_text LONG VARCHAR NOT NULL, PRIMARY KEY CLUSTERED ( page_number ) );
最初のステップでは、検索対象となるカラムに対するテキストインデックスを定義します。
CREATE TEXT INDEX tx_page_text ON enwiki_entry ( page_text ) MANUAL REFRESH;
2番目のステップでは、テキストインデックスを構築します。テーブルが非常に大きい場合、この処理には長時間かかることがあります。
REFRESH TEXT INDEX tx_page_text ON enwiki_entry WITH EXCLUSIVE MODE FORCE BUILD;
ただし、いったんインデックスを構築すれば、インデックスを使用するクエリは非常に高速です。次のSELECT文では、新しいCONTAINS句を使って、"Ayn Rand"と完全一致する語句が含まれているすべてのWikipediaの項目を検索します。
SELECT score, enwiki_entry.page_title, LEFT ( enwiki_entry.page_text, 500 ) AS excerpt FROM enwiki_entry CONTAINS ( enwiki_entry.page_text, '"Ayn Rand"' ) ORDER BY score DESC;
CONTAINS句は、enwiki_entry.page_textカラムに対して既に定義されている全文インデックスを使用して、クエリ文字列'"Ayn Rand"'をenwiki_entry.page_textカラムに適用するため、結果セットは一致するローに限定されます。CONTAINS句は、個々のローがクエリ文字列と一致する度合いを測定する暗黙的な"score"カラムも返します。ORDER BY句は、そのカラムを使ってマッチ率の高い順に1位から項目を並べ替えます。図4は、SQL Anywhere 11 Webサービスによって生成された結果をブラウザ画面に表示した様子です。
ここで、筆者が自分の失敗から学んだコツを紹介しておきましょう。CREATE TEXT INDEX文には重要なカラムを忘れずに含めてください。図4を見ると、メインのWikipediaの項目である「Ayn Rand」が1ページ目に表示されていませんが、本当ならこの項目も表示されなければなりません。表示されなかったのは、筆者がインデックスにenwiki_entry.page_titleカラムを含めるのを忘れたからです。全文インデックスが複数のカラムを指定している場合、CONTAINS句は両方のカラムを対象としてスコアを計算します。この例では、タイトルが"Ayn Rand"であるローがクエリ文字列'"Ayn Rand"'に対して非常に高いスコアを獲得するはずです。
全文検索ではたくさんのオプションを利用することができ、筆者がここで説明したのは、ごく基本的なものだけです。もう1つ注意してほしい点があります。2つのカラムに対するインデックスを構築した場合は、CONTAINS句で一方のカラムを参照することができ、そのカラムのみを対象として検索が行われます。これが、筆者の失敗を繰り返してはいけないもう1つの理由です。インデックスに含めるカラムは多ければ多いほどよいと言えます。そうすれば、クエリを設計するときの自由度が高くなります。
ここまで説明をし忘れましたが、Google検索と同様、全文検索の既定のブーリアン演算子はANDです。つまり、クエリ文字列'Ayn Rand'と'Ayn AND Rand'は同じであり、'Ayn OR Rand'にはなりません。
