Apache-Derbyのアプリへの組込とUDT/UDFの使い方のメモ(後編)
記事が長くなりすぎたので分割しています。前記事は、こちら。
Derbyにおける自動採番の取得方法とシーケンスオブジェクト
Apache Derbyは従来は、プライマリキーとしては「generated always as identity」を指定し、自動採番とする方法と、
SQL:2003準拠、Oracleではおなじみのシーケンスオブジェクトによる採番の、両方が使えるようになっている。
自動採番の値の取得方法
INSERT文で、「generated always」を指定してカラムを省略すると、自動的に採番された値が格納される。
このときに設定された値を取得するには、PreparedStatement作成時に「Statement.RETURN_GENERATED_KEYS」オプションを指定し、
PreparedStatementから生成キーのResultSetを受け取って取得する。
http://db.apache.org/derby/docs/10.9/ref/rrefsqlj37836.html
try (Connection conn = ds.getConnection()) { String sql = "insert into testtbl (val) values (?)"; try (PreparedStatement stm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { stm.setString(1, "test" + System.currentTimeMillis()); int ret = stm.executeUpdate(); try (ResultSet rs = stm.getGeneratedKeys()) { while (rs.next()) { int idx = rs.getInt(1); System.out.println("generated: idx=" + idx); } } } }
なお、最後に採番された値は、IDENTITY_VAL_LOCAL ビルトイン関数によって取得できるので、こちらを直接使っても良い。
(実行計画のログをみると、getGeneratedKeysでは内部的には、これが呼び出されている様子である。)
http://db.apache.org/derby/docs/10.9/ref/rrefidentityvallocal.html#rrefidentityvallocal
※ 上記理由によるのか、BatchUpdateで複数行を一括挿入した場合、derbyではgetGeneratedKeysで返されるレコードは最後のinsert文の1個だけである。
シーケンスを使う場合
Apache Derbyでも、Oracleスタイルのシーケンスオブジェクトが使えるようになった。
シーケンスは以下のように宣言する。
-- 1000番スタートのInteger型のシーケンス create sequence testtbl4_seq as integer start with 1000;
使い方は、以下のようになる。
ij> values (next value for testtbl4_seq) 1 ----------- 1000 1 行が選択されました ij>
※ Apache DerbyのInsert文には、Returningのような拡張構文がないため、Oracleの「Insert … returning」のようには使えない。
ちなみに、Oracleを基準に考えてしまうとFrom句に使うダミーテーブルが欲しくて、ついつい「Dual表」なんてものを探してしまうのだが、Apache Derbyには、OracleのDual表のようなものとして「SYSIBM.SYSDUMMY1」というテーブルが過去の遺物として残されている。
ネットの使用例によると、このSYSIBM.SYSDUMMY1を便利なDual表のかわりとして、みなさん多用してらっしゃる様子である。
しかし、最近のApache Derbyには「Values Expression」という、SQL標準の、サブクエリを使える場所なら、どこでも使えるリテラル表記の行セットが使えるので、Apache Derbyでは、OracleのDual表のかわりとしてはValues Expressionを使うのが良く、
-- valuesでダミー表を作る select current_date from (values 1) dual; -- valuesで行セットを作る select dt from (values current_date) as t(dt);
みたいにするほうが良いのではないかと思われる。(メモリ上の仮想表なのでアクセスコストもゼロと考えてよいはず)
※ FROM句にValues Expressionによる行セットを指定する場合は、必ず「名前」をつけること。さもないとSyntax Errorとなる。表名に加えて、括弧でカラム名を定義することができる。
ところが、よくよく調べてみると、さらに推し進めてselect文である必要さえないようである。
values current_date
これで、JDBC側ではResultSetで受け取れる。
try (Connection conn = ds.getConnection(); Statement stm = conn.createStatement()) { try (ResultSet rs = stm.executeQuery("values current_date")) { if (rs.next()) { java.sql.Date dt = rs.getDate(1); System.out.println("current_date=" + dt); } } }
つまり、Apache Derbyにおいては、OracleのDual表にかわる類似の表は、そもそも必要ない、ということのようである。(そのかわりが、Values Expressionであろう。)
トリガーの利用
Apache Derbyにはトリガーを定義し、レコードの挿入・更新・削除の前後で任意のSQL文を実行することができる仕組みがある。
INSERT/UPDATE/DELETEのタイミングで、別のSQLの呼出しを行う
トリガーは、いくつかの選択肢がある。
[タイミング]
- NO CASCADE BEFORE (更新前トリガー)
- AFTER (更新後トリガー)
[アクションの種類]
- INSERT
- UPDATE
- DELETE
[行単位か、ステートメント単位か]
- FOR EACH ROW (行単位)
- FOR EACH STATEMENT (ステートメント単位)
これに加えて、変更前・後を参照するための名前を指定したり、トリガーのアクションとなるSQL文を指定することでトリガーの定義となる。
ij> -- TESTTBLのINSERT後に各行ごとに起動するトリガーの定義
create trigger trg_ins_testtbl4
after insert on testtbl -- TESTTBLのINSERT後を指定
referencing new as new -- 新規行を参照する名前の設定
for each row -- 各行ごとに
insert into testtbl4 (idx, val) values (new.idx, new.val); -- 起動されるSQL
-- 格納先のテーブルを一旦クリア
autocommit off;
delete from testtbl4;
commit;
-- トリガーが設定されたテーブルへの書き込みテスト
insert into testtbl (val) values
('aaa'), ('bbb'), ('ccc'), ('ddd');
commit;
-- トリガー起動後の確認
select * from testtbl4;
ij> > > > > 0 行が挿入/更新/削除されました
ij> ij> > > ij> 4 行が挿入/更新/削除されました
ij> ij> ij> > 4 行が挿入/更新/削除されました
ij> ij> ij> IDX |VAL
----------------------------
34 |aaa
35 |bbb
36 |ccc
37 |ddd
4 行が選択されました
ij>
ストアドプロシージャの利用
組込みモードでApache Derbyを使用している場合、アプリケーション内でJDBCドライバを介してDerbyにアクセスするのと、
JDBCドライバを介してDerbyにアクセスするプロシージャを定義するのとでは、実現できる事に差はない。(プロシージャにするほうが手間なだけである。)
しかし、もしトリガーから呼び出されるトリガアクションのSQLとしてのプロシージャであるならば、それを定義する意義がでてくる。
/** * トリガーから呼び出される単純なストアドプロシージャの例. * 単にコンソールにIDXを表示するだけ. * * create procedure showIdx(in idx integer) * language java * not deterministic -- 引数が同じでも同じ値を返すとは限らないことを明示する.(デフォルト) * parameter style java -- 戻り値のタイプ * no sql -- 関数の中でSQLを使わないことを示す. * external name 'apachederbysample.pg.DbStoredServices.notifyIns'; * * @param idx * @return * @throws SQLException */ public static void notifyIns(int idx) throws SQLException { System.out.println("ins idx=" + idx); }
DDLは以下のように定義する
ij> create procedure showIdx(in idx integer) language java not deterministic -- 引数が同じでも同じ値を返すとは限らないことを明示する.(デフォルト) parameter style java -- 戻り値のタイプ no sql -- 関数の中でSQLを使わないことを示す. external name 'apachederbysample.pg.DbStoredServices.notifyIns'; > > > > > 0 行が挿入/更新/削除されました ij> call showIdx(1); ins idx=1 0 行が挿入/更新/削除されました ij>
このストアドプロシージャをトリガーから呼び出してみる。
ij> -- TESTTBLのINSERT後に各行ごとに起動するトリガーの定義
create trigger trg_ins_testtbl4_2
after insert on testtbl4 -- TESTTBLのINSERT後を指定
referencing new as new -- 新規行を参照する名前の設定
for each row -- 各行ごとに
call showIdx(new.idx); -- 起動されるSQL
-- テーブルへの書き込みテスト
autocommit off;
insert into testtbl (val) values
('aaa'), ('bbb'), ('ccc'), ('ddd');
commit;
ij> > > > > 0 行が挿入/更新/削除されました
ij> ij> > > ij> > ins idx=38
ins idx=39
ins idx=40
ins idx=41
4 行が挿入/更新/削除されました
ij>TESTTBLのレコードの追加でトリガーが発動し、トリガアクションとしてTESTTBL4のレコードが追加のSQLが実行され、
TESTTBL4のレコードの追加でトリガーが発動し、トリガアクションとしてshowIdxプロシージャが呼び出されている、ということが確認できる。
複数のOUTパラメータを持つストアドプロシージャの定義
ストアドプロシージャは引数に IN, OUT, INOUT のようにDirectionを指定することができるため、宣言によっては複数のOUT引数を定義できることになる。
DDLで定義できるのは良いとして、それがJavaでは、どのようなメソッド定義になるのか、ドキュメントからは、かならずしも明らかではない。
結論としては、IBMの以下のページが非常に参考になった。
http://www.ibm.com/developerworks/opensource/tutorials/os-ad-wiki2/section4.html
以下のようなプロシージャの宣言がある場合、
CREATE PROCEDURE login( IN username VARCHAR(64), IN password VARCHAR(64), OUT authenticated INTEGER ) PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'net.humandoing.wiki.Utilities.login';
これを受けるJAVAコードは、以下のようになる。
public static void login( String username, String password, int[] authenticated ) throws SQLException { ... authenticated[0] = 1; // OUTパラメータの返却 ... }
配列オブジェクトとして引数に渡すことで、その中身を入れ替えることでOUTパラメータを実現しているようである。
この方式ならば、OUT引数がいくつあっても対応可能である。
… とはいえ、前述のとおり、埋め込みモードでは、わざわざプロシージャとして定義する必要性がないので、この方法は出番はまずないと思われる。
その他のDerbyの設定
Apache Derbyには、その動作を設定するためのプロパティがあり、それぞれスコープをもっている。
- システムワイドプロパティ (プロパティファイルまたはシステムプロパティで指定する)
- システムプロパティは大域であるため個々のデータベースごとに指定することはできない。
- データベースワイドプロパティ (SQLからプロシージャコールで設定し各データベースに記憶させる)
- コネクションワイドプロパティ (SQLから設定、揮発性)
プロパティにより、どのスコープでも設定できるものもあれば、特定のスコープしか設定できないものもある。
また、設定がただちに反映される動的な設定と、一度設定されたら再起動するなどしないと反映されない静的なものとがある。
http://db.apache.org/derby/docs/10.9/ref/crefproper22250.html
ログの統合
Apache Derbyのログ出力先はシステムワイドプロパティによって指定できる。
システムプロパティによる指定方法には3つある。
- derby.stream.error.file ログファイルの指定
- derby.stream.error.field ログ出力先のWriter/OutputStreamのあるフィールド
- derby.stream.error.method ログ出力先のWriter/OutputStreamを返すメソッド
いずれかの設定をすると、他方の設定を行うことはできない。
デフォルトは、カレントディレクトリ上にderby.logファイルを作成する。
実行計画やステートメントのログ出力
実行計画やステートメントログの設定はシステムおよびデータベースワイドの設定項目である。
システムプロパティで設定する場合、以下のキーに対して「true」を与えることで有効化できる。
- derby.language.logStatementText ステートメントのログ出力
- derby.language.logQueryPlan 実行計画のログ出力
実装例
たとえば、以下のような実装によってDerbyのログ出力先を標準出力にし、クエリの実行計画などもログに出すように変更できる。
package apachederbysample; import apachederbysample.types.HostAddr; import java.io.File; import java.io.OutputStream; import java.sql.*; import javax.sql.DataSource; import org.apache.derby.jdbc.EmbeddedDataSource; import org.apache.derby.jdbc.EmbeddedDataSource40; public class ApacheDerbySample { /** * Apache Derbyのログ出力先の取得 * * @return */ public static OutputStream getDerbyLogWriter() { // derbyのログを標準出力へ return System.out; } /** * クラス初期化 */ static { // Apache Derbyのプロパティ各種 // http://db.apache.org/derby/docs/10.1/tuning/ctunproper22250.html // Apache Derbyのログ出力先を取得するメソッドの指定 // (OutputStreamか、Writerを戻り値とするメソッドを指定できる.) System.setProperty( "derby.stream.error.method", "apachederbysample.ApacheDerbySample.getDerbyLogWriter"); // Apache Derbyの実行計画を見るためのプロパティ System.setProperty("derby.language.logQueryPlan", "true"); // Apache Derbyのステートメントをロギングするためのプロパティ System.setProperty("derby.language.logStatementText", "true"); // Apache Derbyのキャッシュサイズ System.setProperty( "derby.storage.pageCacheSize", "2000" ); // 以下は、Derbyのチューニングガイドによるお勧め設定 // http://db.apache.org/derby/docs/10.9/tuning/ // Apache Derbyの行ロックを無効にし、常にテーブルロックにする. // (マルチスレッドで更新することがなければパフォーマンスとメモリ使用量の改善になる) System.setProperty("derby.storage.rowLocking", "false"); } /** * アプリケーション名とデータベース名から、データベースのパスを取得する. * @param appName アプリケーション名 * @param dbName データベース名 * @return データベースのパス */ public static String getDbDirectory(String appName, String dbName) { … 既出なので中略 … } public static void main(String[] args) throws Exception { … 既出なので中略 … // テスト testQuery(ds); } /** * 連番を返す表関数とTESTTBL4のJOINの例 * @param ds データソース * @throws SQLException 失敗｀ */ public static void testQuery(DataSource ds) throws SQLException { try (Connection conn = ds.getConnection(); Statement stm = conn.createStatement()) { String sql = "select s.seq, t.idx, t.val from table(makeRangeTable(3)) as s" + " left outer join testtbl4 t" + " on t.idx = s.seq"; try (ResultSet rs = stm.executeQuery(sql)) { while (rs.next()) { int seq = rs.getInt("SEQ"); System.out.println(seq); } } } } }
実行例
run: ---------------------------------------------------------------- Sun Jul 29 20:52:12 JST 2012: Derby バージョン The Apache Software Foundation - Apache Derby - 10.9.1.0 - (1344872): インスタンス a816c00e-0138-d294-5491-0000059325a8 を クラスローダー sun.misc.Launcher$AppClassLoader@2cd9799d によってデータベースディレクトリ /Users/seraphy/Library/myApp/db.derby 上でブート中 java.vendor=Oracle Corporation java.runtime.version=1.7.0_06-ea-b20 user.dir=/Users/seraphy/NetBeansProjects/ApacheDerbySample derby.system.home=null derby.stream.error.method=apachederbysample.ApacheDerbySample.getDerbyLogWriter データベースクラスローダーが開始されました - derby.database.classpath='APP.apachederbysample' Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1726), (SESSIONID = 0), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Committing Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1726), (SESSIONID = 0), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Rolling back Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1727), (SESSIONID = 1), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Committing warn=null Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1727), (SESSIONID = 1), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Rolling back Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1728), (SESSIONID = 2), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Committing Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1728), (SESSIONID = 2), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Rolling back Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1729), (SESSIONID = 3), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Committing Sun Jul 29 20:52:12 JST 2012 Thread[main,5,main] (XID = 1730), (SESSIONID = 3), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Begin compiling prepared statement: select s.seq, t.idx, t.val from table(makeRangeTable(3)) as s left outer join testtbl4 t on t.idx = s.seq :End prepared statement Sun Jul 29 20:52:13 JST 2012 Thread[main,5,main] (XID = 1730), (SESSIONID = 3), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), End compiling prepared statement: select s.seq, t.idx, t.val from table(makeRangeTable(3)) as s left outer join testtbl4 t on t.idx = s.seq :End prepared statement Sun Jul 29 20:52:13 JST 2012 Thread[main,5,main] (XID = 1730), (SESSIONID = 3), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Executing prepared statement: select s.seq, t.idx, t.val from table(makeRangeTable(3)) as s left outer join testtbl4 t on t.idx = s.seq :End prepared statement initScan: columns: [SEQ,], restriction= 0 1 2 Sun Jul 29 20:52:13 JST 2012 Thread[main,5,main] (XID = 1730), (SESSIONID = 3), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Committing lastSeq=3 Sun Jul 29 20:52:13 JST 2012 Thread[main,5,main] (XID = 1730), (SESSIONID = 3), select s.seq, t.idx, t.val from table(makeRangeTable(3)) as s left outer join testtbl4 t on t.idx = s.seq ******* Hash Left Outer Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 2 Empty right rows returned = 1 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 10000.00 optimizer estimated cost: 100035.52 Left result set: VTI ResultSet for apachederbysample.types.RangeTable: Number of opens = 1 Rows seen = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 10000.00 optimizer estimated cost: 100000.00 Right result set: Hash Scan ResultSet for TESTTBL4 at read committed isolation level using instantaneous share row locking: Number of opens = 3 Hash table size = 5 Hash key is column number 0 Rows seen = 2 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 10000.00 optimizer estimated cost: 35.52 Sun Jul 29 20:52:13 JST 2012 Thread[main,5,main] (XID = 1730), (SESSIONID = 3), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Committing Sun Jul 29 20:52:13 JST 2012 Thread[main,5,main] (XID = 1730), (SESSIONID = 3), (DATABASE = /Users/seraphy/Library/myApp/db.derby), (DRDAID = null), Rolling back ---------------------------------------------------------------- Sun Jul 29 20:52:13 JST 2012: Derby エンジンをシャットダウンしています ---------------------------------------------------------------- Sun Jul 29 20:52:13 JST 2012: クラスローダー sun.misc.Launcher$AppClassLoader@2cd9799d を持つデータベースディレクトリ /Users/seraphy/Library/myApp/db.derby のインスタンス a816c00e-0138-d294-5491-0000059325a8 をシャットダウンしています ---------------------------------------------------------------- java.sql.SQLException: Derby システムがシャットダウンされました。 構築成功 (合計時間: 0 秒)
実行計画をみると、連番を返す表関数とTESTTBL4との結合にはHash Joinが使われている様子。
表関数のデフォルトのコストが、かなりの高コストであることを示してらっしゃるので、やはり、まともに実装するなら最適化に対応する必要があるのかもしれない。
Derbyのオプティマイザのオーバーライド方法 (実行計画の指定方法)
コメント文
Apache Derbyにも、Oracleのヒント句で使われるような、SQL上にコメント文をいれてオプティマイザによる実行計画を上書きすることが可能である。
http://db.apache.org/derby/docs/10.9/tuning/ctunoptimzoverride.html
コメント文によるオプティマイザの実行計画の上書きとしては
- 使用するインデックスの指定, インデックス名をnullにすることでフルスキャンに強制することも可能
- 結合方法の指定 (NESTEDLOOPかHASHか)
- 結合する場合の順序 (どちらが駆動表になるのか)をSQL記述順に固定するか、否か (FIXED, UNFIXEDのいずれか)
などが可能である。
たとえば、Derbyの結合方法を手動で指定するSQLのヒント句は以下のような記述方法になる。
SELECT * FROM --DERBY-PROPERTIES joinOrder=FIXED t1 a, t1 b --DERBY-PROPERTIES joinStrategy=NESTEDLOOP WHERE a.c1=b.c1
ijでの実行計画の確認方法
前述のシステムプロパティで実行計画を出力を設定すると、ログに常に実行計画が出力される。
しかし、ijなどで単発的にクエリの実行計画を確認したい場合には、
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
を呼び出して、実行計画を採取するように指定する。
(Onにしたい場合は1、Offにしたい場合は0とする.)
のちに、select文を実行するなどしたあとで、
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
とすると、直前のSQLに対する実行計画が表示される。
ただし、実行計画は非常に長い文字列データであるため、ijのデフォルトでは最初の数行ぐらいしか見る事が出来ない。
全部見られるようにするには、以下のように入力し、ijの表示幅を必要なだけ増やす必要がある。
MaximumDisplayWidth 5000
なお、この実行計画や表示幅などの設定は永続化されないので、ijを終了すればデフォルトに戻る。
http://db.apache.org/derby/docs/10.8/tuning/ttundepth34375.html
http://www.oklab.org/derby.xhtml
ヒント句を使った実行計画の変更
[標準]
select a.seq , b.seq from table(makeRangeTable(3)) a inner join table(makeRangeTable(5)) b on a.seq = b.seq;
実行計画
Statement Execution Plan Text: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 10000.00 optimizer estimated cost: 200000.00 Left result set: VTI ResultSet for apachederbysample.types.RangeTable: Number of opens = 1 Rows seen = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 10000.00 optimizer estimated cost: 100000.00 Right result set: Hash Table ResultSet (4): Number of opens = 3 Hash table size = 5 Hash key is column number 0 Rows seen = 5 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 10000.00 optimizer estimated cost: 100000.00 next time in milliseconds/row = 0 next qualifiers:
[NestedLoopに変更]
select a.seq , b.seq from -- DERBY-PROPERTIES joinOrder=FIXED table(makeRangeTable(3)) a inner join table(makeRangeTable(5)) b -- DERBY-PROPERTIES joinStrategy=NESTEDLOOP on a.seq = b.seq;
実行計画
Statement Execution Plan Text:
User supplied optimizer overrides for join are { joinOrder=FIXED }
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 3
Rows seen from the right = 3
Rows filtered = 0
Rows returned = 3
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1000000000000.00
optimizer estimated cost: 10000000100000.00
Left result set:
VTI ResultSet for apachederbysample.types.RangeTable:
Number of opens = 1
Rows seen = 3
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 10000.00
optimizer estimated cost: 100000.00
Right result set:
Project-Restrict ResultSet (4):
Number of opens = 3
Rows seen = 15
Rows filtered = 12
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1000000000000.00
optimizer estimated cost: 10000000000000.00
Source result set:
VTI ResultSet for apachederbysample.types.RangeTable:
Number of opens = 3
Rows seen = 15
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1000000000000.00
optimizer estimated cost: 10000000000000.00上記例では、実行計画によると、HASH JOINのものがNESTED LOOPに変更されていることなどを確認できる。
Derbyのデータベースのバックアップ、およびリストアの方法
- バックアップとリストアに関する分かりやすい手順
http://www.ibm.com/developerworks/data/library/techarticle/dm-0502thalamati/index.html
http://home.online.no/~olmsan/publications/pres/apachecon07eu/DerbyApacheCon07eu.pdf
- Derbyのロギングとリカバリー(公式)
http://db.apache.org/derby/papers/recovery.html
ファイルコピーによる方法
オフラインであればDerbyのデータベースはOSのファイルコピーで移動・コピーすることができる。
コピーしたデータベースは、そのまま利用可能である。
つまり、ポータビリティがある。
オフライン状態のデータベースをjarまたはzipに圧縮すれば、そのまま読み込み専用データベースとして使用できる。
OSによるファイルコピー
データベースが起動している状態で、ファイルコピーによるバックアップを行う場合、データベースの活動を一時停止する必要がある。
SYSCS_UTIL.SYSCS_FREEZE_DATABASE() ストアドプロシージャの呼出しにより、データベースを一時停止させることができる。
オンラインバックアップ
Derbyの備え付けのストアドプロシージャによりバックアップを行う。
$ ij
ij> connect 'jdbc:derby:db.derby';
ij> call syscs_util.syscs_backup_database('db.backups');
0 行が挿入/更新/削除されました
ij> exit;
$ ls -l db.backups/
total 0
drwxr-xr-x 6 seraphy staff 204 8 1 01:54 db.derby
$ 引数に指定するディレクトリの下に、データベースのフォルダが作成される。
指定したディレクトリそのものがデータベースフォルダになるわけではないことに注意。
(物理コピーの場合と同様に、バックアップしたデータベースは、そのまま新しいデータベースとしても利用可能である。)
復元するには、「restoreFrom=」オプションでバックアップ先のデータベースを指定する。
パスの指定では、バックアップフォルダの下にあるデータベース名までを指定する必要がある。
復元を行うと、既存のデータベースは上書きされる。(存在しなければ作成される。)
$ ij ij バージョン 10.9 ij> connect 'jdbc:derby:db.derby;restoreFrom=db.backups/db.derby'; ij> select * from testtbl; IDX |VAL ---------------------------- 1 |aa 2 |bb 3 |cc 4 |dd 4 行が選択されました ij>
あるいは、新規に作成する場合には、「createFrom=データベースのパス」とする。
なお、どちらもバックアップ時点のデータベースの状態に復元するが、createFromは新規作成であり既存のデータベースがある場合はエラーになる、という点が異なる。
$ ij ij バージョン 10.9 ij> connect 'jdbc:derby:db.derby;createFrom=db.backups/db.derby'; エラー XJ040: クラスローダー sun.misc.Launcher$AppClassLoader@663c0737 を使用してデータベース 'db.derby' を始動できません。詳しくは、次の例外を参照してください。 エラー XBM0J: ディレクトリー /Users/seraphy/work/derbystudy/db.derby はすでに存在します。 ij>
アーカイブログを有効としたバックアップ
Derbyのアーキテクチャでは、データベースの変更はすべてトランザクションログとして記録されてからデータベースに反映する仕組みとなっており、アプリケーションが何らかの原因で強制終了させられたり、あるいは電源が切れてしまった場合でも、次回起動時にトランザクションログファイルからデータベースを回復させることができる。
このログファイルをアーカイブログ化することで、よりスパンの長い、バックアップから次のバックアップまでの変更履歴として残すことができるようになる。
アーカイブログ化することによって、前回のバックアップと、その時点からのアーカイブログさえ残っていれば、データベースが全損したとしても、その直前の状態まで回復することが可能となる。
トランザクションログやアーカイブログは、信頼性にとって非常に重要であるため、データベースとは独立した別のデバイスに格納する、という設定ができる。
$ ij ij バージョン 10.9 ij> connect 'jdbc:derby:db2.derby;create=true;logDevice=archivelog'; ij>
この場合、このデータベース「db2.derby」が使うログディレクトリが、logDeviceオプションで指定された「archivelog」というディレクトリとなる。
データベースを作成したあとで、ログディレクトリの位置を変更するには、現在のログファイルを移動させる必要がある。
logDeviceオプションを指定しなかった場合、ログディレクトリはデータベースのディレクトリ下にある「log」というディレクトリになっていこる。
$ mv db.derby/log/ logs $ ij ij バージョン 10.9 ij> connect 'jdbc:derby:db.derby;logDevice=logs'; ij>
この状態では、まだ通常のログであって、アーカイブログ化されていない。
アーカイブログを有効にするには、一度バックアップを実行する必要がある。
テスト用に、テーブルとデータを作成してみる。
ij> create table testtbl (
> idx integer primary key generated always as identity,
> val varchar(16));
0 行が挿入/更新/削除されました
ij> insert into testtbl (val) values ('1'), ('2'), ('3');
3 行が挿入/更新/削除されました
ij> commit;
ij> select * from testtbl;
IDX |VAL
----------------------------
1 |1
2 |2
3 |3
3 行が選択されましたこれをアーカイブログを有効にしてバックアップをとる。
ij> call syscs_util.syscs_backup_database_and_enable_log_archive_mode( > 'db.backups', 1); 0 行が挿入/更新/削除されました
これにより、db.backupsフォルダの下にデータベースのバックアップが作成され、且つ、logDeviceにはアーカイブログが記録されるように設定される。
このバックアップコマンドの第2引数は、バックアップ時に過去のアーカイブログを消去するかを指定する。
アーカイブログは、前回のバックアップから現在までの履歴であり、バックアップが完了すれば古いアーカイブログは不要になるため、通常は消去するべきである。(なお、バックアップが成功しなければアーカイブログは削除されない。)
バックアップが成功した時点から、アーカイブログが有効となり、以後、データベースへの変更はすべてarchivelogディレクトリに履歴として蓄積されてゆくことになる。
バックアップとアーカイブログからの復元
バックアップとアーカイブログがあれば、データベースが全損しても復元することができる。
前回バックアップ後から、以下のようなデータベースの変更が行われたものとする。
ij> update testtbl set val = '#' || val; 4 行が挿入/更新/削除されました ij> select * from testtbl; IDX |VAL ---------------------------- 1 |#1 2 |#2 3 |#3 4 |#4 4 行が選択されました ij> exit;
そして、ハードディスクに重大なエラーが発生し、ディスクを交換することになったと想定し、データベースをまっさらにしてみる。
$ rm -fr db2.derby
この状態で、データベースに接続するときに
とすることで、前回バックアップと、それ以降の更新履歴を順次適用して、障害発生の直前の状態まで復元する。
ij ij バージョン 10.9 ij> connect 'jdbc:derby:db2.derby;rollForwardRecoveryFrom=db.backups/db2.derby;logDevice=archivelog'; ij> select * from testtbl; IDX |VAL ---------------------------- 1 |#1 2 |#2 3 |#3 4 |#4 4 行が選択されました
このように、バックアップ後の変更状態も復元されていることが確認できる。
Derbyのデータベースの一貫性の確認方法
データベースの一貫性が破損しているかチェックするには、組込み関数syscs_util.syscs_check_tableでチェックすることができる。
この関数が1を返せば正常で、エラーが発生すれば破損している。
すべてのスキーマ、すべてのテーブルに対してチェックするには、以下のSQLを実行すれば良い。
select schemaname , tablename , syscs_util.syscs_check_table(schemaname, tablename) as result from sys.sysschemas s inner join sys.systables t on s.schemaid = t.schemaid;
derbyが、どうゆう状況で一貫性が保てなくなるのか、いまいち分からないのだが、
たいした時間がかかるわけでもないので、データベースを何回かマウントする毎にチェックするのが良いかもしれない。
結論
アプリケーションにDerbyを組み込み、管理するために必要な最低限のことと、パフォーマンスチューニングや運用において必要になりそうな項目には、どのようなものがあるのか、といったことを大雑把にではあるが把握できたように思う。
率直な感想としてはApache Derby(JavaDB)は、十分に商用ベースの品質があり、信頼性やパフォーマンスはかなり高いと感じられる。*1
また、診断情報を示す表関数や、メンテナンスするための組込みプロシージャ、ヒント句による実行計画のチューニング方法なども整備されており、開発する上でも、あるいは長期にわたって運用する上でも、データベースを最適なものに保つことができそうに感じられた。
組み込むデーターベースとして、末永く付き合ってゆく習得に労力をかけるに十分なプロダクトだと、あらためて確信した。
以上、ノート終了。
