第２章
Oracleからの移行を促進する新機能
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する 使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同 様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。
内容
•
Oracleからの移行
•
DB2_COMPATIBILITY_VECTORレジストリ変数
• データタイプの互換性
•
PL/SQLサポート
• パッケージサポート
• スカラー関数
• ビルトイン関数、パッケージ
•
Weak Typing
• ディクショナリ互換ビュー
•
Currently Commited
• ロックの考慮点
IBM DB2 : Why DB2?
Reduce Operational Costs
2. 信頼性
業界で実証された信頼性、
可用性、セキュリティー、
連続稼働の技術
1. 低コスト
運用管理の自動化と
ストレー ジ の最 小 化 を
高いパフォーマンスで実現
.
3. 使いやすさ
開発、仮想
アプリケーションを
容易に実現
なぜ移行を行えない？？
• 移行時に問題となる非互換機能
•
Data types, locking model, weak typing,
packages, …
• 非互換機能の吸収
Æパフォーマンスの劣化
• 開発チームのスキルが足りない
ORACLE
DB2
DB2
Oracle
これはDB2 V9.7以前までの
の話です。
DB2 V9.7の新機能を
見て行きましょう！
DB2_COMPATIBILITY_VECTOR レジストリ変数
•
Oracleとの互換機能を有効にするレジストリ変数
• 設定方法
•
各ビットでそれぞれの機能のON、OFFを決定します。このビットの16進
値を設定します。
•
それぞれのビットに割り当てられている機能は次ページを参照
•
DB2_COMPATIBILITY_VECTOR=ORAの設定も可能
•
この設定はFFF（全ての機能をON）と同じように全ての互換フィーチャー
を有効にします。
•
この設定はOracle との最大の互換性を実現する設定であり、Oracle互
換機能を使用する際の推奨値
•
この設定により、DB2_DEFERRED_PREPARE_SEMANTICS=YESの設定
も暗黙的に有効になります。
•
DB2_DEFERRED_PREPARE_SEMANTICS=YESは、Unicode,SBCS環
境でのみの推奨値
DB2_COMPATIBILITY_VECTORで設定可能なフィーチャー
ビット位置 互換性フィーチャー 説明
1 (0x01) ROWNUM ROWNUM をROW_NUMBER() OVER() の同義語として使用することを可能にし、 ROWNUM をSQL ステートメントの WHERE 節に含めることを許可します。 2 (0x02) DUAL 「DUAL」への非修飾表参照を SYSIBM.DUAL として解決します。
3 (0x04) 外部結合演算子 外部結合演算子 (+) のサポートを有効にします。
4 (0x08) 階層照会 CONNECT BY 節を使用した階層照会のサポートを有効にします。 5 (0x10) NUMBER データ・タイプ NUMBER データ・タイプおよび関連する数値処理を有効にします。
6 (0x20) VARCHAR2 データ・タイプ VARCHAR2 データ・タイプおよび関連する文字ストリング処理を有効にします。
7 (0x40) DATE データ・タイプ DATE データ・タイプを日付と時間の値を結合した TIMESTAMP(0) として使用することを 可能にします。
8 (0x80) TRUNCATE TABLE TRUNCATE ステートメント用の代替セマンティクスを有効にします。TRUNCATE ステート メントが論理作業単位内の最初のステートメントではない場合、TRUNCATE ステートメン トが実行される前に暗黙的なコミット操作が実行されます。
9 (0x100) 文字リテラル バイト長が254 以下である文字定数および GRAPHIC ストリング定数に、CHAR データ・ タイプまたは GRAPHIC データ・タイプ(VARCHAR データ・タイプでも VARGRAPHIC デー タ・タイプでもない) を割り当てることを可能にします。 10 (0x200) コレクション・メソッド 配列で first、last、next、previous などの演算を実行するメソッドの使用を可能にします。 また、配列内の特定の要素の参照に、大括弧の代わりに括弧を使用できるようにします。 例えば、array1(i) はarray1 の要素 i を参照します。 11 (0x400) データ・ディクショナリー互換 ビュー データ・ディクショナリー互換ビューの作成を可能にします。 12 (0x800) PL/SQL のコンパイル PL/SQL ステートメントおよび言語要素のコンパイルと実行を可能にします。
13(0x1000) インセンシティブ・カーソル SELECT ステートメントでFOR UPDATE が明示的に指定されていない場合には、WITH RETURN が定義されたカーソルをインセンシティブにできるようにします。
14(0x2000) INOUT パラメーター INOUT パラメーター宣言のDEFAULT 指定を可能にします。 17(0x10000) SQLデータ・アクセス・レベル
適用
データタイプの互換性
•
Oracleがサポートしている以下のデータタイプとの互換性をサポート
• TIMESTAMP(n) • DATE • NUMBER • VARCHAR2•
データタイプの互換性によりOracleからの移行時のデータタイプのマッピングやDDLの変更を最小
限できる
• DATE、NUMBER、VARCHAR2のデータタイプの互換性を有効にするには、 DB2_COMPATIBILITY_VECTORの設定が必要 • データベース作成時に設定が行われている必要がある、データベース作成後は変更不可 • 以下のデータベース構成パラメーターの以下の項目で互換性サポートを有効化を確認できる • NUMBER データ・タイプの互換性 • VARCHAR2 データ・タイプの互換性 • データ・タイプDATE の TIMESTAMP(0) への互換性C:¥>db2 get db cfg for sample | find "互換性"
NUMBER データ・タイプの互換性 = ON VARCHAR2 データ・タイプの互換性 = ON データ・タイプ DATE の TIMESTAMP(0) への互換性 = ON
確認例
TIMESTAMPの精度指定
• TIMESTAMPの精度の指定が可能 • 互換機能（DB2_COMPATIBILITY_VECTOR）に関係なくV9.7から使用可能 • 小数点以下の秒数の精度を指定 • 0から12までを指定可能（デフォルト：6） • OracleのTimestamp型との互換 ← OracleのTimestampでも精度指定可能(指定可能な桁数 0-9） • より高い精度を持つTIMESTAMP型の使用 • 低い精度を指定することでスペースの節約create table tab1( c0 timestamp(0), c2 timestamp(12),c3 timestamp ) DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL --- --- --- --- --- ---C0 SYSIBM TIMESTAMP 7 0 はい C2 SYSIBM TIMESTAMP 13 12 はい C3 SYSIBM TIMESTAMP 10 6 はい 14 レコードが選択されました。
精度の指定により列の
長さが変わる
精度を短くとることで
スペースの節約が可能
DATEデータタイプの互換性
• 「データ・タイプ
DATE の TIMESTAMP(0) への互換性」がON
•
DATEタイプがTIMESTAMP(0)として扱われる
•
OracleのDATEと同様に秒までの精度を持つ
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。 describe table tab1
データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL --- --- --- --- --- ---C1 SYSIBM DATE 4 0 はい
1 レコードが選択されました。 insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。 select * from tab1
C1
---2009-04-22
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。 describe table tab1
データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL --- --- --- --- --- ---C1 SYSIBM TIMESTAMP 7 0 はい
1 レコードが選択されました。 insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。 select * from tab1
C1 ---2009-04-22-13.39.31
OFF
の場合（従来のDB2のDATEタイ
プ）
ONの場合（DATEタイプがOracleと同じ精度を持つ） Date型でテーブルを作 成するとTimestamp(0) として定義される 出力形式（精度）が異な るNUMBER データ・タイプの互換性
• 「NUMBERデータ・タイプの互換性」がON
•
表定義時の列タイプやプロシージャー内の変数タイプなどでNUMBER型を使用する
ことが出来る
•
実際には以下のような型として作成される
•
NUMBER -> DECFLOAT（16）
•
NUMBER(p)->DECIMAL(p)
•
NUMBER(p,s)->DECIMAL(p,s)
•
精度（p）は31まで
create table tab1(c1 number,c2 number(10),c3 number(10,5)) DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL --- --- --- --- --- ---C1 SYSIBM DECFLOAT 8 0 はい C2 SYSIBM DECIMAL 10 0 はい C3 SYSIBM DECIMAL 10 5 はい 3 レコードが選択されました。
NUMBER
データ・タイ
プの互換性＝ON
精度を付けないNUMBER型でテーブルを作成する とDECFLOAT（16）型として定義されます。 精度、位取りを付けたNUMBER型はDECIMALと DDLでNUMBERを使用することが出来ます。VARCHAR2 データ・タイプの互換性
• 「VARCHAR2データ・タイプの互換性」がON
•
表定義時の列タイプやプロシージャー内の変数タイプなどでVARCHAR2型を使用
することが出来る
•
実際には、VARCHARとして作成される
•
空文字（''）がNULLとして扱われる
•
VARCHARの比較には非空白埋め比較セマンティクスが使用される
create table tab1(c1 varchar2(10),c2 varchar2(10) for bit data) DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL --- --- --- --- --- ---C1 SYSIBM VARCHAR 10 0 はい C2 SYSIBM VARCHAR 10 0 はい 2 レコードが選択されました。 Varchar2型でテーブルを作成 するとVarcharとして定義され る
VARCHAR2
データ・タイ
プの互換性=ON
DDLでVARCHAR2が使用できる非空白埋め比較セマンティクスと空白埋め比較セマンティクス
• 非空白埋め比較セマンティクス
•
以下の文字は異なるものとみなす
•
'A'、'A_'、'A__' （'_'は空白文字）
• 空白埋め比較セマンティクス
•
空白文字をパティングして比較する
•
末尾の空白文字の違いは無視される
•
以下の文字は同じものとみなす
•
'A'、'A_'、'A__' （'_'は空白文字）
VARCHAR2
データ・タ
イプの互換性＝OFF
VARCHAR2
データ・タ
イプの互換性＝ON
PL/SQLサポート
•
DB2 9.7では新たにPL/SQLをサポート
•
DB2 9.7ではPL/SQL用とSQL PL用の２つのコンパイラを持つ
•
それぞれのコンパイラから作成されたモジュールは同じランタイムで稼
動する
• 以下のEditionで使用可能
•
DB2 Enterprise Server Edition
•
DB2 Workgroup Server Edition
PL/SQLが記述される場所
•
PL/SQLブロック（Anonymous Block）
•
データベース外部に保持
•
PL/SQLストアード・プログラム
（データベースのオブジェクトとしてデータベース内にロジックを格納）
•
プロシージャー
•
ファンクション
•
パッケージ
•
トリガー
declare ・・・ begin ・・・ end create procedure xx is ・・・ begin ・・・ end create function xxx return varchar2 is ・・・ begin ・・・ end; create trigger xxx before insert ・・・ on ・・・ for each row declare ・・・ begin ・・・ end; プロシー ジャー ファンク ション トリ ガー PL/SQLブロック実行
DB2とOracleのPL/SQL実行比較
•
CLPPlusとSQL*Plusで同じPL/SQLブロックを実行
Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 -
Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
に接続されました。
SQL> set serveroutput on
SQL> DECLARE
2 str VARCHAR2(30);
3 BEGIN
4 str := 'HELLO WORLD';
5 DBMS_OUTPUT.PUT_LINE(str);
6 END;
7 /
HELLO WORLD
PL/SQLプロシージャが正常に完了しました。
SQL>
CLPPlus: バージョン
1.0
Copyright (c) 2009, IBM CORPORATION. All
rights reserved.
SQL> set serveroutput on
SQL> DECLARE
2 str VARCHAR2(30);
3 BEGIN
4 str := 'HELLO WORLD';
5 DBMS_OUTPUT.PUT_LINE(str);
6 END;
7 /
HELLO WORLD
DB250000I: コマンドは正常に完了しました。
SQL>
CLPPlus
からDB2に対して実行
SQL*Plus
からOracleに対して実行
PL/SQLを実行するには？
•
DB2_COMPATIBILITY_VECTOR （レジストリ変数）
•
PL/SQLコンパイラの使用可能にする
•
PL/SQLを実行するためには設定は必須
•
SET SQLCOMPAT PLSQL（CLPオプション）
•
CLPにPL/SQLの区切り文字（/）を認識させるオプション
パッケージ（モジュール）のサポート
•
Oracleのパッケージに相当するオブジェクトとしてモジュールを
サポート
•
関数、プロシージャー、タイプ、変数などのオブジェクトをまと
めたオブジェクト
•
PL/SQLのパッケージと同じ機能を提供
•
PL/SQLではCREATE PACKAGE文によりモジュールを作成
•
SQL PLではCREATE MODULE文によりモジュールを作成
モジュー
ル
SQL
プロシージャー
グローバル変数
SQL
ファンクション
・
・
・
モジュールの構成
• モジュールは仕様部と本体で構成される
•
仕様部
•
オブジェクトの呼び出し方法の記述
•
グローバル変数やユーザー定義タイプ（配列、連想配列、カーソルタイプ・・・）の宣言も行う
•
本体
•
プロシージャーやファンクションを実装
CREATE MODULE moo@
ALTER MODULE moo PUBLISH PROCEDURE prot (IN a INT, IN b BIGINT, IN c VARCHAR(20))
LANGUAGE SQL@
ALTER MODULE moo ADD PROCEDURE prot (IN a INT, IN b BIGINT, IN c VARCHAR(20)) BEGIN
DECLARE stmt VARCHAR(1000);
DECLARE c1 CURSOR WITH RETURN FOR sl; SET stmt = 'SELECT 1 FROM (values(1))'; PREPARE sl FROM stmt;
OPEN c1; END@
仕様部
モジュールとPL/SQL
•
PL/SQLでパッケージを作成するとモジュールとして登録される
•
PL/SQLではモジュールと同じ機能をパッケージで提供している
C:¥work>db2 -tvf modtest.sql set sqlcompat db2
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。 create module mod1
DB20000I SQL コマンドが正常に完了しました。
alter module mod1 publish variable var1 int default 0 DB20000I SQL コマンドが正常に完了しました。
alter module mod1 publish procedure print DB20000I SQL コマンドが正常に完了しました。 alter module mod1 publish procedure plus(in p1 int) DB20000I SQL コマンドが正常に完了しました。
alter module mod1 publish function get() returns int DB20000I SQL コマンドが正常に完了しました。
alter module mod1 add procedure print begin call dbms_output.put_line(var1); end
DB20000I SQL コマンドが正常に完了しました。
alter module mod1 add procedure plus(in p1 int default 1) begin set var1 = var1 + p1; end
DB20000I SQL コマンドが正常に完了しました。
CC:¥work>db2 -tvf pkgtest.sql set sqlcompat plsql
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。 create or replace package pkg1 is
var1 int :=0; procedure print;
procedure plus(p1 in int); function get return int; end;
DB20000I SQL コマンドが正常に完了しました。 create or replace package body pkg1 is
procedure print is begin
dbms_output.put_line(var1); end;
procedure plus(p1 in varchar2 default 1) is begin
var1 := var1 + p1; end;
function get return int is begin return var1; end; end; SQL PLでモジュールを作成 ・CREATE MODULE文でModuleを作成後、ALTER MODULE文で仕様部、本体を追加する PL/SQLでパッケージ（モジュール）を作成 ・CREATE PACKAGE文、CREATE PACKAGE BODY文を使用して作成
仕様部
本体
仕様部
モジュールに関するシステムカタログ
•
SYSCAT.MODULES
•
モジュール情報
•
SYSCAT.MODULEOBJECTS
•
モジュールに含まれるオブジェクト情報
select char(modulename,5) modulename,dialect,moduletype from syscat.modules where modulename in ('PKG1','MOD1') MODULENAME DIALECT MODULETYPE
--- --- ---MOD1 DB2 SQL PL M
PKG1 PL/SQL P
2 レコードが選択されました。
select char(objectmodulename,10) objectmodulename,char(objectname,20) objectname,objecttype,published from syscat.moduleobjects where objectmodulename = 'PKG1' order by 1
OBJECTMODULENAME OBJECTNAME OBJECTTYPE PUBLISHED --- --- --- ---PKG1 VAR1 VARIABLE Y PKG1 GET FUNCTION Y PKG1 PRINT PROCEDURE Y PKG1 PLUS PROCEDURE Y 8 レコードが選択されました。
PL/SQL Package
にはPが入る
SQL PL
作成されたModuleにはMが入る
参照例
スカラー関数の機能強化
•
Oracleのスカラー関数と同じ名前で同じ動作をする関数の追加
•
Oracleからの移行の際に、これらの関数によりSQLの書き換えが必要なく
なる（Oracleの全関数をサポートしていないため注意）
• 以下のような関数を新規でサポートまたは機能拡張
•
形式・型変換関連
•
TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_NUMBER, TO_CLOB
•
日付操作関連
•
EXTRACT, ADD_MONTHS, …
•
文字列操作関連
•
INITCAP, RPAD, LPAD, INSTR, REVERSE・・・
•
その他
•
DECODE, NVL, LEAST, GREATEST, BITAND
(*) サポートする関数の一覧
形式・型変換関連のスカラー関数の使用例
• 関数
•
フォーマットストリングを使用し、フォーマットの変換を行いながら型変換を行える
•
TO_NUMBER（数値型への変換）
•
TO_CHAR（文字型への変換）
•
TO_DATE（日付型への変換）
•
TO_TIMESTAMP（タイムスタンプへの変換）
•
キャストとの違い
•
フォーマットの変換を行いながら型変換を行う
• 例 • '$123,456,789' （文字）→ 123456789（数値） • 123456789 （数値）→'$123,456,789' （文字） • '09/05/20' （文字）→2009-05-20-00.00.00（タイムスタンプ） • 2009-05-20-00.37.24.843000（タイムスタンプ）→ '09/05/20' （文字） C:¥>db2 values to_number('$123,456,789','$999,999,999') 1 ---123456789 C:¥>db2 values to_char(123456789,'$999,999,999') 1 ---$ 123,456,789TO_NUMBER
使用例
TO_CHAR
使用例
文字型に変換
ビルトイン・パッケージ・ライブラリー（ビルトイン・モジュール）
•
V9.7ではアプリケーション開発に役立つビルトイン・パッケージ・ライブラ
リーを多数提供
•
パッケージ（モジュール）は、関数やプロシージャーや変数などをまとめ
たオブジェクトでSQLから呼び出すことが可能です。（PL/SQLやSQL
PLからも呼び出すことも可能）
•
SQLのみでは出来ないような処理（例：ファイル操作、メッセージの出力
など）を行うことができます。
•
Oracleと同じ名前で同じ機能を持つビルトイン・パッケージ・ライブラリー
を提供
•
OracleのPL/SQLの開発では、ビルトイン・パッケージ･ライブラリーが
頻繁に利用されます。
•
OracleのPL/SQLの移行の際に、使用されているビルトイン・パッケー
ジ･ライブラリーがDB2 V9.7でも提供されていれば書き直しなしでの移
行も可能です。
ビルトイン・パッケージ・ライブラリー（ビルトイン・モジュール）
モジュール名 説明 DBMS_ALERT DBMS_ALERT モジュールは、アラートの登録、アラートの送受信を行うための一連のプロシージャーを備えてい ます。 DBMS_JOB DBMS_JOB モジュールは、ジョブの作成、スケジューリング、および管理のためのプロシージャーを提供します。 DBMS_LOB DBMS_LOB モジュールは、ラージ・オブジェクトを操作する機能を提供します。 DBMS_OUTPUT DBMS_OUTPUT モジュールは、メッセージ・バッファーにメッセージを書き込み(複数行のテキスト)、メッセージ・ バッファーからメッセージを取得する一連のプロシージャーを提供します。これらのプロシージャーは、メッセージ を標準出力に書き込む必要のあるアプリケーション・デバッグの際に役立ちます。 DBMS_PIPE DBMS_PIPE モジュールは、同じデータベースに接続されたセッション内またはセッション間のパイプを通して、 メッセージを送信するための一連のルーチンを提供します。 DBMS_SQL DBMS_SQL モジュールは、動的SQL を実行するための一連のプロシージャーを提供します。したがって、さまざ まなデータ操作言語(DML) ステートメントやデータ定義言語(DDL) ステートメントをサポートします。 DBMS_UTILITY DBMS_UTILITY モジュールは、さまざまなユーティリティー・プログラムを提供します。UTL_DIR UTL_DIR モジュールは、UTL_FILE モジュールで使用するディレクトリー別名を維持するための一連のルーチン を提供します。
UTL_FILE UTL_FILE モジュールは、データベース・サーバーのファイル・システム上のファイルとの間で読み取りおよび書き 込みを行うための一連のルーチンを提供します。
UTL_MAIL UTL_MAIL モジュールは、E メールを送信する機能を提供します。
UTL_SMTP UTL_SMTP モジュールは、SMTP (Simple Mail Transfer Protocol) を介してE メールを送信する機能を提供しま す。
DBMS_OUTPUT使用例
•
DBMS_OUTPUTパッケージ（モジュール）
•
メッセージバッファーへの書き込み、読み込み行うためのプロシージャー、関数を提供
•
処理結果の出力、デバッグなどで役立ちます。
C:¥work>db2 -td@ -vf dbms_output_test.sql drop procedure proc1
DB20000I SQL コマンドが正常に完了しました。
CREATE PROCEDURE proc1( P1 VARCHAR(10) ) BEGIN CALL DBMS_OUTPUT.PUT( 'P1 = ' ); CALL DBMS_OUTPUT.PUT_LINE( P1 ); END DB20000I SQL コマンドが正常に完了しました。 SET SERVEROUTPUT ON
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。
CALL proc1( '10' ) リターン状況 = 0 P1 = 10
CLP
でメッセージバッ
ファーを読み出すための設
定
メッセージ・バッファーか
ら読み出されたデータ
DBMS_OUTPUT
パッケージのPUTプ
ロシージャー、PUT_LINEプロシー
ジャーを呼び出して、メッセージ・
バッファーへの書き込みを行います。
UTL_DIR、UTL_FILE使用例
call utl_dir.create_directory('mydir','C:¥work') リターン状況 = 0 call utl_dir.get_directory_path('mydir',?) 出力パラメーターの値 ---パラメーター名: PATH パラメーター値: C:¥work リターン状況 = 0•
UTL_DIRパッケージ
•
ディレクトリを管理するためのプ
ロシージャーや関数を提供
•
UTL_FILEパッケージ
•
ファイルの読み取りや書き込みを
行うプロシージャーや関数を提供
begindeclare v_filehandle utl_file.file_type; declare isopen boolean;
declare row varchar(4000);
set v_filehandle = utl_file.fopen('mydir','myfile.csv','w'); set isopen = utl_file.is_open( v_filehandle );
if isopen != true then return ;
end if;
for cur as (select deptno,deptname,mgrno from department) do
set row = cur.deptno||','||cur.deptname||','||cur.mgrno; call utl_file.put_line(v_filehandle,row); end for; call utl_file.fclose(v_filehandle); end DB20000I SQL コマンドが正常に完了しました。 !type myfile.csv
A00,SPIFFY COMPUTER SERVICE DIV.,000010 B01,PLANNING,000020 C01,INFORMATION CENTER,000030 D11,MANUFACTURING SYSTEMS,000060 ・・・省略・・・
UTL_DIR
を使用してディレクトリの準備
UTL_FILE
を使用してファイルに出力
CSV
形式で
ファイル出
力
•
DB2 9.5まではstrict(厳密な）データタイピング
•
トレンドはweak(緩やかな)タイピング
(PERL, RUBY, PHP, …)
•
DB2 9.7での新しい振る舞い
•
数値と文字列間の暗黙的な変換（cast):
•
割当て
SET salary := ‘52000’
•
比較
WHERE salary > ‘52000’
•
ビルトイン関数での利用
‘salary: ‘ || 52000 （文字列連結の例）
•
NULL やパラメーター・マーカーの利用性向上
•
非型付きパラメーター・マーカーとNULLを伴う関数呼び出し
•
この振る舞いにより、明示的なキャストが不要になりステートメント量が少なくな
ります。
Weak Typing
暗黙的な変換の例
$ db2 "select * from itest_w
where salary
> '10000‘"
SQL0401N The data types of the operands for the operation ">" are
not compatible. SQLSTATE=42818
$ db2 "select * from itest_w
where salary
> '10000‘"
NAME SALARY
---
---TEST 200000
1 record(s) selected.
V9.7
V9.5
INTEGER型で定 義している。 エラーOK
OK
NumericとString 間の暗黙的な変換。データ・ディクショナリー互換ビュー
•
Oracleのデータ・ディクショナリの互換ビューを提供
•
以下のネーミング・ルールを持つビューを作成
•
USER_* （例
USER_TABLES）
•
ユーザーのオブジェクトを表示
•
ALL_*
（例
ALL_TABLES）
•
ユーザーがアクセス可能なオブジェクトを表示
•
DBA_*
（例
DBA_TABLES）
•
全てのオブジェクトを表示
•
Oracleと同様にそれぞれのビューにはPublic Aliasが作成されているため、
スキーマ指定なしでも呼び出し可能
(*) 作成される互換ビューの一覧 http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.porting.doc/doc/c0054440.htmlデータディクショナリー互換ビュー使用例
C:¥>db2 select char(tschema,20) tschema,char(tname,20) tname,char(tabtype) tabtype from tab
TSCHEMA TNAME TABTYPE
--- --- ---DB2ADMIN CL_SCHED TABLE
DB2ADMIN DEPARTMENT TABLE DB2ADMIN EMPLOYEE TABLE ・・・
C:¥>db2 select char(index_name,20) index_name,char(index_type,20) index_type,char(table_name,20) table_name from user_indexes where table_name = 'CUSTOMER'
INDEX_NAME INDEX_TYPE TABLE_NAME
--- --- ---SQL090627173927420 XRGN CUSTOMER
SQL090627173928650 XVIP CUSTOMER CUST_PHONET_XMLIDX XVIL CUSTOMER SQL090627173928640 XVIP CUSTOMER CUST_PHONES_XMLIDX XVIL CUSTOMER ・・・
データディクショナリーのTABを参照→ユーザーのテーブルを参照
並行性制御に関するこれまでの機能強化
• 並行性を向上させる3つのレジストリー変数（V8.1/V8.2）
•
DB2_SKIPINSERTED、DB2_SKIPDELETED、DB2_EVALUNCOMMITTED
•
インスタンス単位。整合性が崩れるリスクあり。
• 楽観的ロック（V9.5）
•
参照したデータを直ぐに更新しない場合、参照後にロックを一旦解放する方式
•
DB2 V9.5では、RID_BIT()組み込み関数、ROW CHANGE TOKENが利用可能
•
Lock Avoidance（V9.5）
•
Xロックが保持されていても、CSでブロッキングのスキャンはロックウェイトしない
• ケース１ ： WITH RS USE AND KEEP EXCLUSIVE LOCKS のXロック • ケース２ ： どの列を変更しなかったUPDATEのXロック
• ケース３ ： 更新された列以外で構成された索引ONLYスキャン
• 詳細は TechnicalFlash：「DB2 for LUW V9.5のLock Avoidance機能によって、V9.1までは発生していた
ロック競合が発生しなくなることがある」（DM-09-013）を参照
DB2 9.7での機能強化
•
CS分離レベルの機能拡張
新たな読み取り一貫性：Currently Committedの導入
•
読み取り一貫性の比較
•
Currently Committedの挙動
•
Currently Committedを有効に使うための設定
•
考慮点
ISO分離レベル 分離レベルを定義する現象（３種） 対応するDB2の分離レベル 対応するOracleの分離レベル SERIALIZABLE 幻像読取りなし 反復不能読取りなし 未コミット読取りなし Repeatable Read (反復可能読取り) SERIALIZABLE REPEATABLE READ 反復不能読取りなし 未コミット読取りなし Read Stability (読取り固定) なしREAD COMMITTED 未コミット読取りなし Cursor Stability （デフォルト）
(カーソル固定)
READ COMMITTED （デフォルト）
READ UNCOMMITTED Uncommitted Read なし
TRN2
SELECT
TRN1
DB2 （V9.5以前）の読み取り一貫性
•
DB2は最新のデータを読む
•
参照処理は、更新処理がコミットされるまで待つ
A
B
C
D
E
F
UPDATE
UPDATE
C’
D’
TRN2は、TRN1が
COMMITするまで
WAIT
読むデータは常に最新だが、参照処理がロック待機する可能性あり
COMMIT
TRN1
TRN2
Oracleの読み取り一貫性 （Read Committed）
• マルチ・バージョン一貫性制御 (Multi-Version Concurrency Control)
•
更新処理により、更新前イメージが専用の領域へ書き出される
•
参照処理は
コミットを待たずに更新前イメージを読む
•
Select発行時点でコミット済みのデータを読む
•
下記の例では、UPDATEがCOMMITされていたとしてもTRN2は更新前データを読む
A
B
C
D
E
F
SELECT
UNDOセグメント
UPDATE
UPDATE
C
D
C’
D’
読んだデータが最新とは限らないが、参照処理はロック待機しない
SCN=100
SCN=10
SCN=110
SCN=98
SCN=29
DB2 9.7の読み取り一貫性（Currently Committed）
TRN1
TRN2
• 未コミットの更新があってもロック待機をしない
•
参照処理は、更新処理に伴うロックの開放を待たず、更新前のデータ
（コミット済みの最新データ）をログ（ログバッファー）から読む
•
常に最新のコミット済みデータを読む
A
B
C
D
E
F
SELECT
トランザクションログ
（ログバッファー）
UPDATE
UPDATE
C
D
C’
D’
参照処理はロック待機せず、かつコミット済みの最新データを読む
同時実行性の向上： Currently Committed
別アプリから参照可能か
別アプリから更新可能か
Oracleの
Read Committed分離レベル
参照中の行を
Yes
Yes
更新中の行を
Yes
No
別アプリから参照可能か
別アプリから更新可能か
DB2 9.5以前の
CS 分離レベル
参照中の行を
Yes
基本的にYes（※）
更新中の行を
No
No
読み取り処理が書き込み処理を妨害しない
(ReadがLockを取らない）
書き込み処理が読み取り処理を妨害しない（ReadはLock行をバイパス)
別アプリから参照可能か
別アプリから更新可能か
DB2 9.7以降の
CS分離レベル w/CC
参照中の行を
Yes
Yes
更新中の行を
Yes
No
Yes：参照
or 更新可
No ：ロックウェイト
※カーソルのある行であった場合は、一瞬だけ待つ。基本的に更新は可能。
•
データベース構成パラメーター（CUR_COMMIT）
•
ON
:分離レベルCSはCurrently Committedとして動く（新規作成DBのデフォルト）
•
AVAILABLE
:アプリケーションから明示的に指定された場合にCurrently Committed有効
•
DISABLED
:Currently Committed無効 （MigrationされたDBでのデフォルト)
•
パッケージ作成時の指定 （BIND コマンド）
•
CONCURRENTACCESSRESOLUTIONオプションで指定
•
動的SQLであっても、使用するパッケージを明示的にBINDすることで制御可能
•
セッション単位の指定
•
JDBC、CLI、.NET等のアプリ・インターフェースからセッション単位で指定可能
Currently Committedの設定方法
CUR_COMMIT DB
CUR_COMMIT DB
構成パラメーター
構成パラメーター
BIND
BIND
オプショ
オプショ
ン
ン
セッションで指
セッションで指
定
定
•
CC設定の原則
•
スコープが狭い設定パラ
メータがより優先される
•
アクセスプラン決定時の設
定がセクションに書き込ま
れ、有効になる。
•
静的SQLでは注意
並行性の向上 – Currently Committed まとめ
•
Currently Committedの機能により、最新の情報を提供しな
がらもアプリケーションの並行性が向上
•
未コミットのINSERT行はスキップ
•
未コミットのUPDATE行は更新前データを戻す
•
未コミットのDELETE行は、参照対象
（未だDELETEされていない状態が最新の状態であるため）
• 既存のログをベースにした仕組みにり、更新と参照が競合した
場合にのみ、ログへのアクセスが発生する。オーバーヘッドが
少なく、パフォーマンスに優れている
•
DB構成パラメータ：CUR_COMMIT、BINDオプションで設定可
能。有効/無効はEXPLAINで確認
Oracle : FOR UPDATE
航空券の予約アプリケーションを作成することを考えてみます。
以下の手順で予約を実施することになります
１）空席の検索、２）空席の確認、３）予約
ロック制御をどのように行うべきでしょうか？
trx1 trx2(4)SELECT FOR UPDATE文のロック
待機が解除され“空席なし”の結果となる
Oracle
(2)SELECT FOR UPDATE
空席を検索
23Fの行にTXロックを取得しよう とするがロック待機となる
(1)SELECT FOR UPDATE
空席を検索
23F席が最後の1席
⇒23Fの行にTXロックを取得
DB2 : FOR UPDATE
DB2
航空券の予約アプリケーションを作成することを考えてみます。
以下の手順で予約を実施することになります
１）空席の検索、２）空席の確認、３）予約
ロック制御をどのように行うべきでしょうか？
trx1 trx2(4)SELECT FOR UPDATE文のロック待ちが
解除され、”空席なし”の結果となる.
(2)SELECT FOR UPDATE WITH RS
空席検索
23Fの行にUロックを取得しようとす るが、ロック待機となる
(1)SELECT FOR UPDATE WITH RS
空席の検索 23F席が最後の1席
⇒23Fの行にUロックを取得
