Windows上のSQLiteで.importを行う

　SQLiteに格納するデータは基本的にUTF-8となります（少なくとも、SQLite Java Wrapper/JDBC Driverでアクセスした場合、自動的にUTF-8となります）。

　WindowsのコマンドプロンプトはデフォルトはMS932なので、sqlite3.exeでテキストファイルを読み込むとき、どうやってUTF-8でimportするか・・・と悩んでいたのですが、実は、テキストファイルをUTF-8で保存しておくだけでＯＫなんですね。

　UTF-8で保存したテキストを .import した場合、コマンドプロンプトのエンコードに関係なく、テキストファイル内の文字コードとして取り込んでくれるようです（なので、chcp 65001 等の作業をすることなく、UTF-8で取り込むことが可能）。

　もちろん、その状態だと、コマンドプロンプトからsqlite3.exeで対話型でSQLを実行したときにはまりますが（＾＾；

＃SQLite用のGUIフロントエンドは、基本的にUTF-8で処理をするようなので、UTF-8で取り込んでおいた方が何かと都合がいいかと。

DB2の自動インストール

応答ファイルのキーワード(DB2 9.5)

応答ファイルを用いてインストールする場合、

setup.exe /m /u (応答ファイルのパス)

のような感じで実行。

もっとも、実際にはいきなり応答ファイルを作成するのではなく、setup.exeを通常(GUIモード)起動し、そこで応答ファイルを保存したものをベースに編集した方が楽だと思われます（ちょっとした修正をテキストエディタで行うような感じでしょうか）。

byteaデータの書き込み

　PostgreSQLでbytea型のデータを書き込む処理のメモ。

１．PreparedStatementでSQLを構築する。
２．PreparedStatement#setBinaryStream(int, InputStream, int)でデータをセットする。なお、書き込むデータが既にbyte[]になっている場合は、setBytes(byte[])でも可（わざわざByteArrayInputStream()を作成しなくてもＯＫ）。

　注意点としては、setBinaryStream(int, InputStream)や、setBinaryStream(int, InputStream, long)の場合は、is not yet implemented. ということでエラーになってしまうようです（postgresql-8.3-604.jdbc4のドライバで検証）。

　なお、bytea型のデータを読み込む場合は、ResultSet#getBinaryStream(int)を使用するか、あるいはbyte[]の状態のまま次の処理を行うのであればResultSet#getBytes(int)で取得してもＯＫのようです。

copyでNULL値の読込

　PostgreSQLのCOPY を使うと、大量のデータを（SQLで処理する場合と比較し）高速に読み込ませることができます。

　ドキュメントによると、NULL値も取り込めるとのことですが、いくらやっても失敗してしまう・・・。と思ったら、何のことはない、テキスト中のNULL値が、\N（NULL値）ではなく\n（改行コード）になっていたことが原因でした・・・。

＃ちなみに、psqlのセッションにおいて、

\pset null *NULL*

のような感じのコマンドを投げる（「*NULL*」の部分は表示させたい任意の文字列）と、selectした結果のNULL値の出力を切り替えることができます。

JOTMを用いてTwo Phase Commit

　久しぶりにTwo Phase Commitが必要となるアプリケーションの開発を行うことに。前回の開発時はIBM WebSphere Appication Serverの環境下だったので、普通に（？）lookupで処理を行うことができました。しかしながら、今回はJavaアプリケーションにおいてもTwo Phase Commitが必要になってくるので、J2EEコンテナが必要というような方法は使用できず。

　S2JTAという選択肢も考えたのですが、諸事情により今回は見送り（S2JTAは「Java EEアプリケーション・サーバーに依存しない実装」も提供されているとのことですが、これはスタンドアローンアプリでも使用できるってことなんでしょうかね？？）。

　Samples for Connection Pooling and Distributed TransactionsのXA with Two-Phase Commit Operation--XA4.javaのような感じで、XAResourceなどを使用して自前で処理を記述するという方法もありますが、ソースの記述量が若干増えてしまうのが難点。

Continue reading "JOTMを用いてTwo Phase Commit"

PostgreSQL JDBCドライバの各バージョン

　配布されているソースコードは１種類ですが、バイナリダウンロードの場合はJDKのバージョンにあわせて選択するPostgreSQLのJDBCドライバ。その実体はどの程度違うんだろう・・・と思っていたのですが、たまたまJDBCドライバのソースを眺める機会があったので解決。

　Antでビルドする際のオプションにより、最終的にどのドライバを利用するClassファイル群ができあがるかが異なってきているようです（ビルドする際に、ソースコード（の元となるもの）内のテキストの置換を行っています）。

＃単純にソースファイル一式をEclipseに取り込んだだけではコンパイルエラー出まくり（＾＾；　

PostgreSQLでdblink

　dblink・・・接続中のデータベースから、別のデータベースに接続するための関数。分散されているデータを結合して検索したりするときに有用？

　参考：PostgreSQLのdblink(database link)

　まずはdblink関数のインストール。openSUSE11.1でYaSTでpostgresql-contribパッケージをインストールしている場合、必要なSQLファイルが既にコピーされているので、psqlでそれを実行すればＯＫ。

# psql -U postgres -d testdb < /usr/share/postgresql/contrib/dblink.sql

　なお、アンインストール用のSQLファイルも存在します（ひたすらDROPしていきます）。

# psql -U postgres -d testdb < /usr/share/postgresql/contrib/uninstall_dblink.sql

select文を発行する場合、

select dblink_connect('conn1',
'hostaddr=192.168.99.203 port=5432 dbname=testdb user=pguser password=pguser');

でリモートデータベースに接続し、

select * from dblink('conn1', 'select col1,col2,col3 from schema1.table1')
t1(col1 integer, col2 character(10), col3 timestamp);

でSELECT文実行。取得対象となるカラム名を全部定義する必要があるので、少し面倒かも・・・。

　ちなみに、コネクションを切断する場合は、

select dblink_disconnect('conn1');

のような感じです。

　とりあえず試しては見たものの、実際に（僕が）使う機会は出てくるんだろうか・・・（＾＾；

PL/pgSQLでupdate件数を取得する

PostgreSQLのストアド関数でPL/pgSQLを使ってみる練習。まずは、PL/pgSQLを使えるかどうかの確認。

psql -U username -d testdb -c "select * from pg_language;"

で、インストールされている言語の一覧を取得できます。

 lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
----------+----------+---------+--------------+---------------+--------------+--------
 internal |       10 | f       | f            |             0 |         2246 |
 c        |       10 | f       | f            |             0 |         2247 |
 sql      |       10 | f       | t            |             0 |         2248 |
(3 rows)

ってことで、インストールされていないようなので、

createlang -h 127.0.0.1 -d testdb -U username plpgsql

で実際に使用するデータベースに対してPL/pgSQLをインストール（参考：PostgreSQL 編19 - ストアドファンクション、function、PL/pgSQL、トリガー）。

Continue reading "PL/pgSQLでupdate件数を取得する"

PostgreSQLでカレントスキーマを設定

　PostgreSQLにおいては、通常はpublicスキーマがカレントとなり、スキーマを指定しない場合はpublicに対してアクセスされます（もちろん、明示的に指定することも可能）。

　カレントスキーマの情報を取得する場合は、

select current_schema();

を実行すればＯＫ。上記のような場合はpublicが結果として得られます。ちなみに、

select current_schemas(true);

の場合、暗黙的に検索されるパス（pg_catalog）も得られます（引数がfalseの場合は、暗黙的に検索されるパスは表示されません）。

　逆にカレントスキーマを設定する場合は、

set search_path to schema1;

でＯＫ。設定は上書きされますので、publicは検索対象から外れることとなります。

set search_path to schema1, schema2;

のように複数指定することも可能です。なお、複数指定している場合、current_schema()の戻り値は先頭のスキーマとなります。

openSUSE + DB2Express-C

　openSUSE11.1(64bit版)にDB2Express-C9.5.2beta(64bit版)をインストールしてみたので、そのときのメモ（厳密には、Xenの仮想マシン上へのインストールですが）。

参考資料：DB2 Express-C 9.5 for Linux クイックインストール

　tarアーカイブをダウンロードし、展開してインストーラを実行・・・したら、

The required library file libstdc++.so.5 is not found on the system.

というエラーが。

　必要となるパッケージは、DB2 サーバーおよび IBM Data Server Client のインストール要件 (Linux)にまとめられており、パッケージとしては、libaioとcompat-libstdc++の２つ。libaioは既にインストール済みだったので、あとはlibstdc++。
　YaST2のソフトウェア管理で確認すると、libstdc++43はインストール済み。

zypper what-provides libstdc++.so.5

でlibstdc++.so.5が含まれているパッケージを確認すると、libstdc++33とのこと。ってことで、改めてYaST2からインストール（32bit版もあわせてインストール）。

　あとは、developerworksのページの手順に従って作業を行えばインストール完了。ちなみに、9.5.2の場合は別途LanguagePackをインストールする不要っぽい？

＃何故かメッセージが全部英語になっていましたが・・・LANGの設定の都合？

pgAdminIIIのインストール

　openSUSE11.1は、PostgreSQLはパッケージで提供されていますがpgAdminIIIは提供されていません（phpPgAdminは提供されているのですが）。openSUSE11.0用のrpmは提供されているのですが、openSUSE11.1用のものはまだ提供されていないようで。

　ってことで、ソースからインストールしてみました。なお、事前にYaST2から以下のパッケージおよびその依存パッケージをインストール（OSインストール時に開発関係のパッケージをほとんどインストールしていなかったため、かなり大量のパッケージのインストールが必要でした）。

・gcc
・gcc-32bit
・gcc-c++
・postgresql-devel
・wxGTK-compat
・wxGTK-devel
・libxml2-devel
・libxml2-devel-32bit （これは不要かも）
・libxslt-devel
・make

Continue reading "pgAdminIIIのインストール"

JDBCで外部キー情報を取得する

　JDBCであるテーブルの外部キー(ForeignKey)情報を取得する場合は、DatabaseMetaData#getCrossReferenceメソッドを利用すればいいようです。

　あるテーブルを参照している外部キーの一覧を取得したい場合は後ろ３つの引数にnullをセット（『親テーブル』を指定）、あるテーブルが参照している外部キーの一覧を取得したい場合は前３つの引数にnullをセットすれば、欲しい情報が取得できるような感じです。

例）testスキーマのsample1テーブルを参照している外部キー情報を取得する。

ResultSet result = metadata.getCrossReference(null,"test","sample1",null, null, null);

例）hogeスキーマのfugaテーブルが参照している外部キー情報を取得する。

ResultSet result = metadata.getCrossReference(null,null, null, null,"hoge","fuga");

2008/12/11追記
データベースによっては、DatabaseMetaData#getCrossReferenceにおいて、テーブル名にnullを指定できない（３つ目と６つ目の引数は、いずれもnull以外の値でなければならない）ものもあるようです（試した範囲では、PostgreSQLはnullを許容しますが、DB2は駄目でした）。そういう場合は、getImportedKeys（指定したテーブルが参照している外部キー情報を取得）あるいはgetExportedKeys（指定したテーブルを参照している外部キー情報を取得）を利用すればＯＫです・・・というか、こっちを使う方が正当派？（＾＾；

DB2のSQLExceptionのスタックトレース

　DB2に対してjdbcドライバで接続した場合、SQLException発生時のスタックトレースにおいてIDやコードしか出力されないため、デバッグが結構手間に感じることが（他のドライバで接続した場合は試していないのでわかりませんが）。

　いろいろと調べてみると、接続時のパラメータでretrieveMessagesFromServerOnGetMessageの値をtrueにすればＯＫのようです。

参考：ftp://ftp.software.ibm.com/ps/products/db2/info/vr95/pdf/ja_JP/db2ajj950.pdfの資料の112ページ目（ページ下部のページ番号では102ページ目）。

SQLiteで複数DBへの同時接続

　SQLiteのデータベースファイルhoge.dbに含まれているテーブルhogehogeと、fuga.dbに含まれているテーブルfugafugaとを結合した結果を得たいような場合。

C:\sqlite_data>sqlite3.exe hoge.db ⇒ まず、hoge.dbに接続する（別にfuga.dbでもいいのですが）

sqlite> attach database "fuga.db" as fuga ⇒ fuga.dbをfugaという名前で接続。
sqlite> select hogehoge.*, fugafuga.* from hogehoge, fuga.fugafuga fugafuga where hogehoge.keyfield=fugafuga.keyfield ⇒ keyfieldという列の値で２つのテーブルを連結。

ってな感じ。

　attachするときに注意しなければならないのは、".db"のような感じでデータベースファイルに"."を含んでいる場合、ファイル名をシングルクォーテーションあるいはダブルクォーテーションで囲まなければならない点。忘れた場合、SQL error: no such column:が発生します。

PostgreSQL用デザインツール

　pgDesigner

　動作には、Gambasが必要。残念ながら、Windows上では動かないようです・・・。

記事：マイコミジャーナル PostgreSQL向けグラフィカルデザイナ「pgDesigner 1.0.0」登場

PostgreSQLのパーティショニングテーブル

　PostgreSQLのパーティショニングテーブルの処理速度を調べてみました。ただし、すごく簡単なテスト（ベンチマークテスト）となっていますので、これを一般化するのはちと危険かも（＾＾；　本当はスレッド数を変えたり、レコード数を増やしたり、データの生成処理を変えたり、別端末にするなど、いろいろとしなければならないのですが。

　測定環境は以下の通り。

１．Timestamp型で１ヶ月単位でテーブルを分割。とりあえずは2007年1月～12月の１２個を作成。
２．ループで５万レコードを挿入。列および挿入データは以下の通り。

・serial型：nextvalで採番しながらデータを生成
・Timestamp型：このフィールドの内容でテーブルを分割。１月→１２月の順で１レコードずつ挿入し、１２月の次は１月に戻る。日は、各月の最大日の範囲内で乱数で設定。
・varchar(20)：英数字を乱数で連結。文字数は10～20文字をやはり乱数で。
・int：１～２０の番号を順番に。
・varchar(40)：英数字を乱数で連結。文字数は30～40文字をやはり乱数で。
・int：１～Integer.MAX_VALUEの範囲の数値を乱数で。
・varchar(20)：英数字を乱数で連結。文字数は10～20文字をやはり乱数で。
・int：１～Integer.MAX_VALUEの範囲の数値を乱数で。

３．パーティショニングを行っていない１テーブルの状態と、１２個にパーティショニングを行った状態とで所要時間を比較。
４．ついでに検索処理もテスト。検索条件は年：2007年、月：１月→１２月の順（１２月の次は１月に戻る）、日：各月の最大日の範囲内で乱数で設定。各Select文でレコードの取得件数を取得（SELECT COUNT(*) FROM～）。検索処理も５万回ループし、所要時間を比較（検索対象フィールドにはインデックスをはっています）。

Continue reading "PostgreSQLのパーティショニングテーブル"

S2DaoとPostgreSQLのパーティショニングテーブル

　PostgreSQLはパーティショニングテーブルに対応しており、レコード件数が多くなる傾向のあるテーブルに対して適用すると効果があるといわれています。

　で、試しに「月単位」で別テーブルに切り替えるようなパーティショニングを作成し、S2Daoでデータを追加するような処理を書いたのですが・・・思わぬところではまってしまいました。

パーティショニングされたテーブルに対してInsertを行った場合、正しく書き込めても挿入件数の戻り値が"0"となってしまい、NotSingleRowUpdatedRuntimeException が発生する（従って、トランザクション制御を書けている場合はロールバックしてしまう）。

S2Daoを使用せず、普通にStatement等を使用してInsertをした場合も戻り値は0件なので、ドライバ側の問題？（パーティショニング後のテーブルに対して直接Insertをした場合の戻り値は1件となります）

自動SQL生成機能を使用しない場合は、戻り値が0件でも例外は発生しないとのこと。

なので、.sqlファイルを生成し、Insert文を実行するように方針変更。

すると、今度は自動採番機能が機能せず、該当フィールドの値がnullとなってしまいDB側のドライバが例外をスローしてしまう。

Continue reading "S2DaoとPostgreSQLのパーティショニングテーブル"

PostgreSQLのロール設定

　PostgreSQL(8.2.4)のロール設定のメモ。Windows版のPostgreSQLを使用し、pgAdminIIIで設定する場合の例です。なお、設定は全てユーザpostgres（スーパユーザ権限）で行っています。

グループロールの作成

ロール名	testrole
ログイン可	チェックなし
パスワード	（空欄）
アカウントの失効	（空欄）
ロール権限	全てオフ

　ここでログイン可をチェックありでロールの作成を行うと、おそらく次の「ログインロールの作成」は不要となるかと思います。詳しく調べたわけではないのですが、グループロールとログインロールを分けることにより、ログインロールを「ログイン用のアカウント」のような感じで扱えるのではないかと思います（複数のログインユーザに対して同じロールを割り当てたり、逆にあるログインユーザに対して複数のロールを割り当てたりすることがしやすくなるのではないかと）。

Continue reading "PostgreSQLのロール設定"

PostgreSQL用のJDBCドライバ

　PostgreSQLをインストールすると、JDBCドライバとしていくつかインストールされます。例えば、僕の環境では8.2.4(Windows版)をインストールすると、以下の３ファイルがコピーされました。

postgresql-8.2-505.jdbc2.jar

postgresql-8.2-505.jdbc2ee.jar

postgresql-8.2-505.jdbc3.jar

　どういう風に使い分ければいいのかなぁ・・・と思っていると、PostgreSQL JDBCドライバ ダウンロードページのJDBC Version Selectionにまとめられていました。

　これによると、使用するJavaのバージョンによって、使用するjarファイルを選択すればOKって感じですね。最初ドライバのTypeかなぁ、と思っていたのですが、Javaで使うところのJDBC2.0とかJDBC4.0とかいう分類に対応したもののようです（ちなみに、PostgreSQL用のJDBCドライバはTypeIV）。

Continue reading "PostgreSQL用のJDBCドライバ"

SQLiteでデータ処理

　SQLiteは特に何かをインストールする必要もなく、サクッと手軽に扱えるデータベースですが、その特徴を利用すると、ある定型データ（例えばログファイル）を集計したりするときにはラクチンですね。

　.importコマンドでテキストファイルのデータをサクッと取り込んでしまえば、あとは各種SQLite用フロントエンドツールを使うもよし、JDBCドライバを使ってアクセスするもよし、SQLで好き放題（笑）。

　ただ、タブ区切りのテキストを取り込んだりする場合は、.importの前に.separator \t（タブ区切りの場合）という感じでセパレータを切り替えておかなければいけませんが。

＃ちなみに、PentiumM 1.8GHzのマシンで10万レコードちょいのデータ（１０カラム）の取り込みがわずか数秒。

データベースのCHAR型

　例えば、sampleというテーブルにおいてhogeというchar型のフィールドがあった場合、例えば以下のようなselect文が書けます。

　select * from sample where hoge='10'

・・・が、最近のデータベースは、

　select * from sample where hoge=10

なんてのも受け付けるようになっているんですね。自動的なキャストで、建前としては（？）「容易性」ってことになっているようです。つまり、数値型だとシングルクォーテーションは不要なのに文字型だけ必要なのは間違えやすい、ってことなんでしょうか。

　まぁ、それはそれでありなのかもしれませんが・・・個人的には逆にしょーもないミスで悩む人も増えてしまうような危険性もあるのでは、と思ったりもします。
　例えば、hogeというフィールドにおいて'hoge'という値を条件で検索する場合、

　where hoge=hoge

って書いてしまうと、hogeというフィールドの（各レコードの）値なのか、特定の値なのかが分からなくなってしまいません？

　前者の解釈になってしまうと、全レコードがヒットしてしまうこととなり、'hoge'という値で検索しようと思っていた人の期待値と異なる結果が得られることになってしまいます。
　あとは、自動キャストをサポートしていないＤＢでSQLエラー連発とか（＾＾；；　まぁ、この辺はＤＢの方言の範囲内と言ってしまえるのかもしれませんが。

＃ただ、もしかしたら自動キャストが有効なのは、検索条件の値が数値だけの場合だったりするのかもしれませんが（手元に環境がないのでテストできない）。

Oracle 10g Express Edition

　オラクル、無償版データベース製品の提供へ。
　いろいろと制限事項はあるみたいですが、それでもすごいですね。

SQLのWHERE

　いつも使用しているのとは違うDBを使用したときの話。
　例えばCHAR(10)で定義されているフィールドに、"ABC"というデータが格納されているとして、その値をキーにして検索する場合。
　普段は、

WHERE COLUMNNAME='ABC'

で問題なくヒットするのですが、そのデータベースでは、それではヒットせずに、

WHERE COLUMNNAME='ABC       '

という感じで、スペースでうめて10桁にする必要がありました（ちなみに、普段使用しているDBも、今回使ったDBも非常に有名なDBです）。
　VarCHAR型ではなくCHAR型の場合、どちらの動きをするのが一般的なんでしょうね？？

DB2が1980円

　IBM DB2 Personal Developer’s Edition Lite V8.2がソースネクストから1980円（税込み）で発売されるとのこと。
　ネットワークを経由してのアクセスができないなどの制限はあるとのことですが、それなりに需要はありそう（個人的勉強用途などにもお気軽に買える値段ですし）。IBMとしては、これをきっかけにDB2に慣れて、業務などで上位版をサクッと扱ってもらうことができるように・・・との思惑だそうですが。

＃ちなみに、10/15発売予定とのことです。

XML DB

　最近、XML DBにもちょっと興味があります。とりあえず試してみるならXprioriなんかよさそうなのですが・・・それをインストールして試す環境が（＾＾；；
　「データベースをインストールしたコンピュータと同じIPアドレスからのアクセスのみ利用可能」って制限があるので、普段使わないパソコン（気軽に再構築できる）にインストールして、開発マシンからアクセスってのができないんですよねぇ・・・。

grobal temporary table

　Oracleなどで実装されている（というかSQL92で定義されている）CREATE GROBAL TEMPORARY TABLEと、DB2で実装されているDECLARE GROBAL TEMPORARY TABLEって、似ているようで微妙に違うんですかね。
　それはそうと、いずれもデータが有効となるのは基本的にセッションの終了までのようですが・・・データベース接続におけるセッションというのは、どこからどこまでになるんだろう？？　connect〜closeまでが１セッションと考えればいいのかなぁ・・・。

ストアドプロシージャ

　ストアドプロシージャに初挑戦。ん〜〜LANGUAGE SQLだけでも、結構いろんなことができるんですね。パフォーマンスは・・・同じことをJava＆SQLで比較してみなければなんともいえませんが、遅くはない感じですね。アプリ−ＤＢ間のオーバーヘッドを考えると、Java＆SQLよりは速いんでしょう。っていうか、遅かったら敢えてストアドにする必要がないような気もしますが（笑）。もちろん、ストアドの方のみ処理のアルゴリズムがイケテナイ場合はその限りではないでしょうけど。

　で、とりあえずINパラメータを使うやりかたはなんとなくわかりましたが、OUTパラメータやResultSetを使用するストアドはまだこれから。OUTはINと同じような感じで処理すればいいので多分すぐに使えるようになると思いますが、ResultSetの方がよくわからない（＾＾；

　ちなみに、ストアドの実行って、JavaではCallableStatementが用意されていますが、実行するだけならただのStatementで、パラメータ付きの実行でもPreparedStatementで処理することができるんですね（まぁ、PreparedStatementはStatementを継承していて、CallableStatementはPreparedStatementを継承していますから）。もちろん、OUTパラメータを使用する場合はgetXXXメソッドが定義されているCallableStatementが必要となりそうですが。

＃コンパイラなどがあれば、CやJavaでも記述できるそうですが、パフォーマンス的にはどうなんでしょうね？？　Cはおそらくネイティブコードにコンパイルされた状態で動くでしょうけど、Javaの場合は？？

ALTER TABLE

　データベースで、あるテーブルにおいて新たに列が必要となったり、フィールド長が足りなかったりした場合にはALTER TABLEを使いますが・・・ALTER TABLEを行った後のテーブルの挙動ってデータベースによって結構異なったりする？？
　あるDBサーバの説明のところで、「強力なALTER TABLE」ということで、追加した列などに対してもインデックスを作成することが可能、というようなことが書かれていたのですが、逆に言うと、それができないDBサーバってのもあるってことなんでしょうね。
　また別のDBサーバ（現在開発中）の場合、ALTER TABLEはまだサポートされていないとか言うのを雑誌で見かけたことも（もちろん、いずれサポートされることになるとは思いますが）。

SQL関数(2)

　SQL関数をうまく使うと、今まで「大量にデータを取得してJavaプログラムで処理」していたことを、「DBサーバでうまく処理してJavaプログラムで受け取るデータを必要最小限に」することができそうな感じ？
　ということで、今回は「CHAR型フィールドにおいて、文字列中の半角スペースおよび全角スペースを取り除いた状態で比較を行う」ための支援関数の作成に挑戦してみました。両端の半角スペースならTRIM()関数でサクッと処理できるのですが、"A B"を"AB"と同一視して比較ってのは、どうも標準で実装されている関数だけでは無理っぽいようで。

　まず悩んだのが、引数および戻り値の型指定。JavaのStringクラスなんかだと、文字長は不定なので、特に意識する必要がないのですが、SQLにおいては、CHARもVARCHARも文字数の定義が必要。文字数を指定せずにCHARとだけ記述した場合は、「CHAR(1)」として処理されてしまいました（笑）。しかたがないので、とりあえず今回の用途で「これだけの文字長があれば大丈夫だろう」ということでVARCHAR(100)で定義することに（CHAR(100)で定義するとうまく動かなかったのが不思議ではありますが）。

　アルゴリズムとして、
　１．引数の文字列を１文字ずつ切り取って、半角or全角スペース以外の場合に戻り値に連結。
　２．引数の文字列に対して半角or全角スペースを検索し、そこまでの文字列を戻り地に連結。それ以降の文字列に対して再度半角or全角スペースを検索して文字列を連結していくという処理の繰り返し。
　ってのが思い浮かんだので、とりあえず両方作ってパフォーマンスを比べてみたところ・・・２．の方が２〜２．５倍ほど速い感じでありました。

　不思議だったのが、１．の処理の際に「半角スペース」のみを処理すると全角スペースも取り除けたこと。DBの文字コードがEBCDICで、IN/OUTの処理があったりとややこしいことになりそうだなぁ・・・と思っていただけに、ちょっと意外。もしかすると、全角スペースの文字コードがEBCDICにおいては半角スペースとかぶったりするのかもしれませんが。
　あと、２．の処理においても、SUBSTRで文字列を切り出す際、全角スペースの次の文字から取り出す場合も、全角スペースの文字位置＋１で処理できたのも？　Javaなら「文字数単位」での処理なので全然不思議ではないのですが、DB2の場合はバイト単位だったような？？

SQL関数とパフォーマンス

　SQLを使用する際、今までは既に定義されている関数（SUMとかSUBSTRとか）しか使ったことがなかったのですが、今回思い立って、SQL関数の作成に挑戦してみました。といっても、いきなり複雑なものを作ろうとすると挫折してしまいそうなので、普段はSQLで取得した結果を元にJava側で処理を行っていた演算を、SQL関数に移植することに。でも、とりあえずどのように記述すればいいのかさっぱりわからないので、一番最初に作成したのは、DBサーバの参考資料に書かれていたCREATE FUNCTION(SQLスカラー)のサンプルソースそのものでした（笑）。
　CREATE FUNCTIONは普通にStatementを作成してexecuteUpdateで実行できるので、普段使っているSQLツール（自作）で処理・・・と思ったところ、セミコロンをSQL文の区切り文字として使用していたので、その部分を修正する必要が発生してしまいました。そんなこんなで作成したSQL関数を、SELECT文に組み込んでテスト実行。案外簡単に作成＆使用できるんですね（＾＾）。

　次に、オリジナルの関数の作成。まぁ、処理の内容自体は変数への割り当て(SETコマンド）と、条件分岐(IF〜ELSE〜END IF）程度で記述できる程度のものなので、先ほど参考にしたソースを元にあれこれ試行錯誤。
　何度か作成に失敗するものの、とりあえずは無事に完成。executeUpdateでの作成の場合、エラーが出ても「どこでエラーが出ているのか」が非常にわかりにくくて大変ですね（苦笑）。一応、SQLExceptionのメッセージは表示されているのですが、具体的にどの部分なのかが不明な場合も。せいぜい１０行〜数十行のソースなので、じっくりとソースを見直すことで何とか解決できましたが。

　ただ、データ量が多くなるとかなりの負荷がかかってしまうようで、約１３万レコードに対して処理を行うようなSELECT文を作成したところ、反応が戻ってくるまでに600秒ほどかかってしまいました（爆）。いろいろと調べてみると、IFステートメントをCASEで置き換えることができる場合はCASEステートメントの方がパフォーマンスがいい場合が多いとのこと。今回作成したSQL関数のIFステートメントもCASEで置き換えることができるような内容だったので、プログラムを書き直して再作成。最終的な状態では、同じ処理が70秒程度まで改善されました。GROUP BYなども使用しており、今回作成したSQL関数を使用しない状態でも30秒〜40秒程度かかっていたので、まぁ、許容範囲？　逆に、DBサーバ側でのGROUP BYでこれまでより取得レコード数を劇的に減らすことができるため、APサーバ側での負荷の軽減を考えると、いい感じと言えそうです。

テキストファイルに対してSQL

　カンマ区切りやタブ区切りのテキストファイルに対してSQLを発行することのできるドライバがあるんですね。CSVアクセス用のJDBCドライバもあるようですが、今回はODBCドライバ経由でトライしてみました（このドライバはMicrosoft Officeについてきているのかな？）。
　Eclipse用のDBプラグインとしてDbEdit/QuantumDB/JFaceDbcを使用しているのですが・・・DbEditを使うことが一番多いので、とりあえずそれで挑戦。が、どうもMetadataを扱うあたりでドライバが対応していないらしく、接続失敗。設定でMetadataを扱わずに処理を行うようになるのかと思うものの、そのような設定は見当たらず、またGoogleであれこれ調べても手がかりはつかめず。
　次に、QuantumDBで設定を行うと・・・とりあえず、さっくりと接続成功。さすがに、100MBを超えるテキストファイルが対称だっただけに処理速度はアレですが、SQLで手軽にデータを捜すことができるってのが結構便利（＾＾）。小さなファイルなら、レスポンスも問題ないですね。
　それまでは、Excelに取り込んで処理したり、CSVファイルの各カラムの最大文字長を解析するツール＆CSVを読み取りながらDBに格納するツールを作成したりして処理をしていましたが、直接データベースと同じように扱うことができると、手間が省けて楽です（笑）。
　ただ、DbEditで使えると一番うれしいのですが・・・。仕事柄、Paradoxのデータを扱うこともあるのですが、それもDbEditだとODBC経由での接続に失敗するため、QuantumDBを使用しています。DbEdit&ODBCが無理なのかなぁ・・・。

＃ちなみに、JFaceDbcでも問題なくODBC経由でCSVファイルにアクセスできました。

仮想テーブル

　DB2でのSQLにおいてWITH構文なるものがあったので、試しに使ってみました。簡単に言えば、１つのSQL中で仮想テーブルを作成し、その仮想テーブルに対してデータを結合したりできる感じでしょうか？

　FROMやLEFT OUTERの中でもサブクエリとして仮想テーブルを作成できますが、その場合はいろいろと制約がある（サブクエリ中では使用できないコマンドがいろいろとあったりしますし）のに対し、WITHを使用した場合はもっと幅広く使えたりするんでしょうか？　まだあまり使い込んでいないので詳細はわかりませんが、参考にしたSQL文は、WITHブロックの中でORDER BYを使用していたりしたので。

　ただ、SQL文が"WITH"で始まるため使用するツールによっては「参照系(executeQuery)」と判断されずに「更新系(executeUpdate)」として実行してしまい、SQLExceptionが出たりしますが（笑）。現在メインで使用しているのが、自作のSQL実行ツールと、EclipseのDBEditプラグインなのですが、自作の方はとりあえずソースを書き直してWITHで始まっていてもexecuteQueryを実行できるように修正することにより対応できましたが、DBEditの方は・・・？？　何か手段があるのかもしれませんが、まだ見つけていません（＾＾；；

　おまけとして、「強制executeQuery」「強制executeUpdate」を行えるような実行ボタンも追加しました（笑）。

インデックスとパフォーマンス

　データベースの読み取り速度の向上にはインデックスが有効である場合が多いのですが、インデックスを多用しすぎると、更新系処理のパフォーマンス低下につながる場合が多い・・・ってのは有名な話ではあります。
　で、実際、インデックスを増やすとどの程度パフォーマンスに影響があるのかを調べてみました。実際のアプリの一部分を抜き出して簡単なベンチマーク用のツールを作成し（１トランザクション当たり平均１０レコードをDELETEしてINSERTする処理を５００〜１０００回繰り返し×数回）、インデックスが１個の場合と６個の場合と十数個の場合とで実験。

　結果：１トランザクションあたりの所要時間に特に変化なし

　もともとの１トランザクションあたりの処理時間が100msec程度で、処理するレコード数も１０前後だったためこのような結果になっただけで、処理内容や、あるいはＤＢの種類が変わっただけでも全然違った結果が得られる可能性はありますが、数個程度ならあまり問題とならない場合もあるようで。っていうか、何事も「やってみなければ結果はわからない」ってことでしょうか（笑）。まぁ、元が100msの場合、5ms変化しただけで5%の違いになってしまいますが、この場合、時間測定の誤差も含めると非常に微妙なところですか。

　１つのテーブルに２０個くらいのインデックスを作成すると、明らかに更新系のパフォーマンスが低下したという話を聞いたことがありますが、だとすると、数個のインデックスでは特に影響はない可能性も大ですね。

　現実の開発においては、インデックスを作成したことによる更新系パフォーマンスへの影響と、参照系パフォーマンスへのメリットとのバランスも重要になってくるので、充分に検討する必要はあるものの、極端にインデックスを作りすぎたりしなければメリットが上回る場合も多々ありそうです。

Eclipse＆DB Plug-in＆InterBase

InterBaseにアクセスするとなると、ISQLかSQLExplorerを使用するのが一般的かとは思いますが（v7などではまた別ですが、使用しているのはv5.5）、ISQLはテーブルの内容を表イメージで簡単に一覧表示できなかったり、SQLExplorerの場合はQuery結果をテキストで取得できなかったり（もしかするとできるのかもしれませんが、そういう機能を見つけられず）と、いろいろと使いにくいと感じるところがあります。

で、普段の開発環境（VisualAge for Java）とは別にEclipseもインストールしていまして、それ用のDB Plug-in（DbEditやQuantumDBなど）を使ってInterBaseのデータベースを手軽に扱えないかとあれこれとトライ。しかし・・・InterClient経由でInterBaseのデータベースに接続するとなるとなかなか難しいものがありますね。

DbEditもQuantumDBも、データベースを開く際にカタログなどを基準として処理を行うみたいなのですが、InterClient経由でInterBaseにアクセスを行うと、カタログの取得ができず（例外のメッセージからするとサポートされていないらしい）、結局テーブルの閲覧などもできないような感じでした。で、いろいろと試したところ、JFaceDbcを使うと、カタログが取得できなくてもテーブル一覧などを表示できることが判明。
ところが・・・文字コードの問題からか、２バイト文字が正しく処理できず。使っているのがInterBase5.5＆InterClient1.6ってのが原因なのかもしれませんが。調べていると、データベース接続時にcharseのプロパティを指定するといいような感じなのですが、JFaceDbcの場合データベース接続時のプロパティをセットすることができず（DbEditはできるようですが、InterClient経由ではそもそも接続できなかったのでそれ以前の問題）、結局断念。

悔しいので、さらにいろいろと試してみると、JDBC-ODBCブリッジを使うことにより、JFaceDbcで２バイト文字も特に問題なく処理できるようになりました。また、QuantumDBも、ODBC経由で処理をするとカタログなしで処理できるようになりました。
んでも、実はODBCを経由させると、MicrosoftAccessからでも簡単にInterBaseのデータベースにアクセスすることができたんですよね（笑）。

＃他に、自前でJavaアプリケーションなどを作成するという手もありますが。

データグリッド

Oracle 10gやDB2II(Information Integrator)など、グリッド技術は「データグリッド」へと流れつつあるんでしょうかねぇ。
そのデータにアクセスしようと思っているユーザーからすれば、データの取得ができればいいわけであって、そのデータが「物理的に１ヶ所に格納されて」いようが、分散して存在しているデータを「要求処理プログラムが必要に応じて集めて」いようが関係ないわけで。

ただ、必要に応じてデータを集めるためには、そのデータが格納されている部分に対して処理要求を出す＝データが格納されている方は要求に対して待機していなければならないため、うまくシステムを構築するのもなかなか難しそうですね。
しばらく前の新聞で、IBMがデータグリッドを利用した「仮想医療機関」の実験を行った記事（リンク先はIBMのサイト）が載っておりましたが、これなんかの場合、各医療機関に格納されているデータに対して要求を投げるわけで、そのためには少なくとも、
・各医療機関の端末の電源が入っている必要がある
・各医療機関に設置されているマシンのIPアドレスがわかる必要がある
という条件を満たす必要があると思います。
後者は技術的に何とかなるとしても、前者に関してはそれだけではなんともならない可能性が。
例えば、「休診日にも電源を入れておくなんて電気代がもったいない」なんてことで電源が入っていないと、そこに格納されているデータはその日は利用できなくなってしまうわけですし（＾＾；；

ただ、そういうのを無視して、「技術的」にはなかなか興味深いものではあります（＾＾）。

桁あふれ

まったく予想していなかったところで桁あふれが発生（苦笑）。
う〜ん・・・どうやって対処したものか？？

まぁ、とりあえずはALTER TABLEで桁数増加、かなぁ。

SQLの複雑さと実行回数

SQLネタをもう１つ。

あるひとかたまりのデータを取得する場合、
・複雑なSQLを１回
・簡単なSQLを複数回
を比較すると、データ量が少ないうちは前者のほうがパフォーマンスがいいこともありますが、
データ量が膨大になってくると、後者のほうがパフォーマンスがよかったりすることもあるようで。

SQLの実行時間が、（データ取得後の処理も含めて）平均20分だったものが、1分程度で片付くようになったり（笑
まぁ、単純に前者のSQLがダメダメだったから、という説もありますが（汗

内容的には、プログラム（Java）でも処理可能な集計処理も1つのSQLでまとめてやってしまおうと、
多数のLEFT OUTER、多数のSUBQUERY、多数のCASE WHEN節を使った「自己満足に浸れる」SQL（爆）を、
シンプルにして集計処理をJavaに任せるようにしたんですけどね（＾＾；；

＃CASE WHENなどを使わずにGROUP BYなどだけで事足りるような場合だと、SQLで集計もやってしまった方が速いことも多いようですが。

外部結合

DBによって結果は異なってくるかもしれませんが、
A,B,Cの３つのテーブルがあって、それぞれのデータ量の比が１０：１２：３０〜４０で、
Aを第一基準としてCのデータを取得する場合、とりあえず
SELECT C.* FROM A,B,C WHERE A.hoge='hoge' AND B.A_KEY=A.AKEY AND B.fuga='fuga' AND C.BKEY=B.BKEY AND C.funi='funi'
って感じのSQLと、
SELECT C.* FROM A
LEFT OUTER JOIN B ON B.A_KEY=A.A_KEY AND B.fuga='fuga'
LEFT OUTER JOIN C ON C.B_KEY=B.B_KEY AND C.funi='funi'
WHERE A.hoge='hoge'
って感じのSQLが思い浮かびますが・・・（実際は*での取得は行いませんが）
環境によっては、後者の方が圧倒的に速い場合があるんですね〜。

テーブルAのレコード数が80万くらいのデータで試したところ、
前者で１分〜２分、後者で１〜２秒という結果が得られました（＾＾；；
もちろん、インデックスの状態や、上で書いたようにDBサーバによって結果は異なってくる可能性はありますが、
ここまで違うとちょっとびっくり。
おそらく、WHEREでの絞込みを行う部分で余計な結合を行う必要がないことがこのような結果につながっているとは思うのですが。

ただ、LEFT OUTERで結合しているため、前者だとはじかれるようなデータが混入する可能性もあるため、
それに対する後処理を忘れないようにしなければいけませんが（＾＾；；