第2章 Oracleからの移行を促進する新機能

by user

on 28 марта 2017

Category: Documents

>> Downloads: 11

views

Report

Comments

Description

Download 第2章 Oracleからの移行を促進する新機能

Transcript

第2章 Oracleからの移行を促進する新機能

第２章 Oracleからの移行を促進する新機能
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する
使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同
様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。
© Copyright IBM Japan Co., Ltd. 2011
内容
• Oracleからの移行
• DB2_COMPATIBILITY_VECTORレジストリ変数
• データタイプの互換性
• PL/SQLサポート
• パッケージサポート
• スカラー関数
• ビルトイン関数、パッケージ
• Weak Typing
• ディクショナリ互換ビュー
• Currently Commited
• ロックの考慮点
2
© 2011 IBM Corporation
IBM DB2 : Why DB2?
Reduce Operational Costs
低コスト
1.
運用管理の自動化と
ストレージの最小化を
高いパフォーマンスで実現
3
.
信
2. 頼性
業界で実証された信頼性、
可用性、セキュリティー、
連続稼働の技術
使
3. いやすさ
開発、仮想
アプリケーションを
容易に実現
© 2011 IBM Corporation
なぜ移行を行えない？？
• 移行時に問題となる非互換機能
• Data types, locking model, weak typing,
packages, …
DB2
Oracle
• 非互換機能の吸収 Æパフォーマンスの劣化
これはDB2 V9.7以前までの
の話です。
• 開発チームのスキルが足りない
ORACLE
4
DB2 V9.7の新機能を
見て行きましょう！
DB2
© 2011 IBM Corporation
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環
境でのみの推奨値
5
© 2011 IBM Corporation
DB2_COMPATIBILITY_VECTORで設定可能なフィーチャー
ビット位置
1 (0x01)
互換性フィーチャー
ROWNUM
2
3
4
5
6
7
DUAL
外部結合演算子
階層照会
NUMBER データ・タイプ
VARCHAR2 データ・タイプ
DATE データ・タイプ
(0x02)
(0x04)
(0x08)
(0x10)
(0x20)
(0x40)
8 (0x80)
TRUNCATE TABLE
9 (0x100)
文字リテラル
10 (0x200)
コレクション・メソッド
11 (0x400)
データ・ディクショナリー互換
ビュー
12 (0x800) PL/SQL のコンパイル
13(0x1000) インセンシティブ・カーソル
14(0x2000) INOUT パラメーター
17(0x10000) SQLデータ・アクセス・レベル
適用
説明
ROWNUM を ROW_NUMBER() OVER() の同義語として使用することを可能にし、
ROWNUM を SQL ステートメントの WHERE 節に含めることを許可します。
「DUAL」への非修飾表参照を SYSIBM.DUAL として解決します。
外部結合演算子 (+) のサポートを有効にします。
CONNECT BY 節を使用した階層照会のサポートを有効にします。
NUMBER データ・タイプおよび関連する数値処理を有効にします。
VARCHAR2 データ・タイプおよび関連する文字ストリング処理を有効にします。
DATE データ・タイプを日付と時間の値を結合した TIMESTAMP(0) として使用することを
可能にします。
TRUNCATE ステートメント用の代替セマンティクスを有効にします。TRUNCATE ステート
メントが論理作業単位内の最初のステートメントではない場合、TRUNCATE ステートメン
トが実行される前に暗黙的なコミット操作が実行されます。
バイト長が 254 以下である文字定数および GRAPHIC ストリング定数に、CHAR データ・
タイプまたは GRAPHIC データ・タイプ (VARCHAR データ・タイプでも VARGRAPHIC デー
タ・タイプでもない) を割り当てることを可能にします。
配列で first、last、next、previous などの演算を実行するメソッドの使用を可能にします。
また、配列内の特定の要素の参照に、大括弧の代わりに括弧を使用できるようにします。
例えば、array1(i) は array1 の要素 i を参照します。
データ・ディクショナリー互換ビューの作成を可能にします。
PL/SQL ステートメントおよび言語要素のコンパイルと実行を可能にします。
SELECT ステートメントで FOR UPDATE が明示的に指定されていない場合には、WITH
RETURN が定義されたカーソルをインセンシティブにできるようにします。
INOUT パラメーター宣言の DEFAULT 指定を可能にします。
実行時ルーチンSQLデータ・アクセス・レベル適用を有効にします。
※網がけ部分の機能は、データベース作成前にレジストリ変数の設定が必要
6
© 2011 IBM Corporation
データタイプの互換性
• 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
7
| find "互換性"
NUMBER データ・タイプの互換性
= ON
VARCHAR2 データ・タイプの互換性
= ON
データ・タイプ DATE の TIMESTAMP(0) への互換性
= ON
© 2011 IBM Corporation
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 レコードが選択されました。
8
© 2011 IBM Corporation
DATEデータタイプの互換性
• 「データ・タイプ DATE の TIMESTAMP(0) への互換性」がON
• DATEタイプがTIMESTAMP(0)として扱われる
• OracleのDATEと同様に秒までの精度を持つ
OFFの場合（従来のDB2のDATEタイ
プ）
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
1 レコードが選択されました。
9
データ・タイデータ・列のスケ
列名プ・スキーマタイプ名長さール NULL
----- --------- ------------- ----- ----- -----C1
SYSIBM
TIMESTAMP
7
0 はい
1 レコードが選択されました。
insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。
C1
---------2009-04-22
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイデータ・列のスケ
列名プ・スキーマタイプ名長さール NULL
----- --------- ------------- ----- ----- -----C1
SYSIBM
DATE
4
0 はい
select * from tab1
ONの場合（DATEタイプがOracleと同じ精度を持つ）
出力形式（精度）が異な
る
insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。
select * from tab1
C1
------------------2009-04-22-13.39.31
Date型でテーブルを作
成するとTimestamp(0)
として定義される
© 2011 IBM Corporation
NUMBER データ・タイプの互換性
• 「NUMBERデータ・タイプの互換性」がON
• 表定義時の列タイプやプロシージャー内の変数タイプなどでNUMBER型を使用する
ことが出来る
• 実際には以下のような型として作成される
• NUMBER -> DECFLOAT（16）
• NUMBER(p)->DECIMAL(p)
• NUMBER(p,s)->DECIMAL(p,s)
• 精度（p）は31まで
NUMBERデータ・タイ
プの互換性＝ON
create table tab1(c1 number,c2 number(10),c3 number(10,5))
DB20000I SQL コマンドが正常に完了しました。
DDLでNUMBERを使用することが出来ます。
describe table tab1
データ・タイデータ・
列の
スケ
列名
プ・スキーマタイプ名
長さ
ール NULL
----------------- --------- ------------------- ---------- ----- -----C1
SYSIBM
DECFLOAT
8
0 はい
C2
SYSIBM
DECIMAL
10
0 はい
C3
SYSIBM
DECIMAL
10
5 はい
3 レコードが選択されました。
精度を付けないNUMBER型でテーブルを作成する
とDECFLOAT（16）型として定義されます。
精度、位取りを付けたNUMBER型はDECIMALと
して定義されます。
10
© 2011 IBM Corporation
VARCHAR2 データ・タイプの互換性
• 「VARCHAR2データ・タイプの互換性」がON
• 表定義時の列タイプやプロシージャー内の変数タイプなどでVARCHAR2型を使用
することが出来る
• 実際には、VARCHARとして作成される
• 空文字（''）がNULLとして扱われる
• VARCHARの比較には非空白埋め比較セマンティクスが使用される
create table tab1(c1 varchar2(10),c2 varchar2(10) for bit data)
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
VARCHAR2データ・タイ
プの互換性=ON
DDLでVARCHAR2が使用できる
データ・タイデータ・
列の
スケ
列名
プ・スキーマタイプ名
長さ
ール NULL
--------------- --------- ------------------- ---------- ----- -----C1
SYSIBM
VARCHAR
10
0 はい
C2
SYSIBM
VARCHAR
10
0 はい
2 レコードが選択されました。
11
Varchar2型でテーブルを作成
するとVarcharとして定義され
る
© 2011 IBM Corporation
非空白埋め比較セマンティクスと空白埋め比較セマンティクス
• 非空白埋め比較セマンティクス
• 以下の文字は異なるものとみなす
VARCHAR2 データ・タ
イプの互換性＝ON
• 'A'、'A_'、'A__' （'_'は空白文字）
• 空白埋め比較セマンティクス
• 空白文字をパティングして比較する
VARCHAR2 データ・タ
イプの互換性＝OFF
• 末尾の空白文字の違いは無視される
• 以下の文字は同じものとみなす
• 'A'、'A_'、'A__' （'_'は空白文字）
12
© 2011 IBM Corporation
PL/SQLサポート
• DB2 9.7では新たにPL/SQLをサポート
• DB2 9.7ではPL/SQL用とSQL PL用の２つのコンパイラを持つ
• それぞれのコンパイラから作成されたモジュールは同じランタイムで稼
動する
• 以下のEditionで使用可能
• DB2 Enterprise Server Edition
• DB2 Workgroup Server Edition
• DB2 Express Edition
13
© 2011 IBM Corporation
PL/SQLが記述される場所
• PL/SQLブロック（Anonymous Block）
PL/SQLブロック
declare
・・・
begin
・・・
end
• データベース外部に保持
実行
• PL/SQLストアード・プログラム
（データベースのオブジェクトとしてデータベース内にロジックを格納）
• プロシージャー
• ファンクション
• パッケージ
• トリガー
14
プロシー
ジャー
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;
© 2011 IBM Corporation
DB2とOracleのPL/SQL実行比較
• CLPPlusとSQL*Plusで同じPL/SQLブロックを実行
SQL*PlusからOracleに対して実行
CLPPlusからDB2に対して実行
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
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
DB250000I: コマンドは正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
SQL>
SQL>
CLPPlus: バージョン 1.0
Copyright (c) 2009, IBM CORPORATION.
rights reserved.
15
All
© 2011 IBM Corporation
PL/SQLを実行するには？
• DB2_COMPATIBILITY_VECTOR （レジストリ変数）
• PL/SQLコンパイラの使用可能にする
• PL/SQLを実行するためには設定は必須
• SET SQLCOMPAT PLSQL（CLPオプション）
• CLPにPL/SQLの区切り文字（/）を認識させるオプション
16
© 2011 IBM Corporation
パッケージ（モジュール）のサポート
• Oracleのパッケージに相当するオブジェクトとしてモジュールを
サポート
• 関数、プロシージャー、タイプ、変数などのオブジェクトをまと
めたオブジェクト
モジュー
ル
グローバル変数
SQLプロシージャー
SQLファンクション
・
・
・
• PL/SQLのパッケージと同じ機能を提供
• PL/SQLではCREATE PACKAGE文によりモジュールを作成
• SQL PLではCREATE MODULE文によりモジュールを作成
17
© 2011 IBM Corporation
モジュールの構成
• モジュールは仕様部と本体で構成される
• 仕様部
• オブジェクトの呼び出し方法の記述
• グローバル変数やユーザー定義タイプ（配列、連想配列、カーソルタイプ・・・）の宣言も行う
• 本体
• プロシージャーやファンクションを実装
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@
18
仕様部
本体
© 2011 IBM Corporation
モジュールとPL/SQL
• PL/SQLでパッケージを作成するとモジュールとして登録される
• PL/SQLではモジュールと同じ機能をパッケージで提供している
SQL PLでモジュールを作成
PL/SQLでパッケージ（モジュール）を作成
・CREATE MODULE文でModuleを作成後、ALTER
MODULE文で仕様部、本体を追加する
・CREATE PACKAGE文、CREATE PACKAGE
BODY文を使用して作成
CC:¥work>db2 -tvf pkgtest.sql
set sqlcompat plsql
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。
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 コマンドが正常に完了しました。
DB20000I
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 コマンドが正常に完了しました。
alter module mod1 add function get() returns int return var1
DB20000I SQL コマンドが正常に完了しました。
19
create or replace package pkg1 is
var1 int :=0;
procedure print;
procedure plus(p1 in int);
function get return int;
end;
仕様部
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;
DB20000I
SQL コマンドが正常に完了しました。
本体
© 2011 IBM Corporation
モジュールに関するシステムカタログ
• SYSCAT.MODULES
• モジュール情報
• SYSCAT.MODULEOBJECTS
• モジュールに含まれるオブジェクト情報
参照例
select char(modulename,5) modulename,dialect,moduletype from syscat.modules where modulename in ('PKG1','MOD1')
MODULENAME
---------MOD1
PKG1
DIALECT
---------DB2 SQL PL
PL/SQL
MODULETYPE
---------M
P
PL/SQL PackageにはPが入る
SQL PL作成されたModuleにはMが入る
2 レコードが選択されました。
select char(objectmodulename,10) objectmodulename,char(objectname,20) objectname,objecttype,published from syscat.moduleobjects
where objectmodulename = 'PKG1' order by 1
OBJECTMODULENAME
---------------PKG1
PKG1
PKG1
PKG1
OBJECTNAME
-------------------VAR1
GET
PRINT
PLUS
OBJECTTYPE
---------VARIABLE
FUNCTION
PROCEDURE
PROCEDURE
PUBLISHED
--------Y
Y
Y
Y
8 レコードが選択されました。
20
© 2011 IBM Corporation
スカラー関数の機能強化
• 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
(*) サポートする関数の一覧
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0011043.html
21
© 2011 IBM Corporation
形式・型変換関連のスカラー関数の使用例
• 関数
• フォーマットストリングを使用し、フォーマットの変換を行いながら型変換を行える
•
•
•
•
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' （文字）
TO_NUMBER使用例
TO_CHAR使用例
C:¥>db2 values to_number('$123,456,789','$999,999,999')
C:¥>db2 values to_char(123456789,'$999,999,999')
1
-----------------------------------------123456789
1
------------------------------------------------$ 123,456,789
1 レコードが選択されました。
22
数値型に変換
1 レコードが選択されました。
文字型に変換
© 2011 IBM Corporation
ビルトイン・パッケージ・ライブラリー（ビルトイン・モジュール）
• V9.7ではアプリケーション開発に役立つビルトイン・パッケージ・ライブラ
リーを多数提供
• パッケージ（モジュール）は、関数やプロシージャーや変数などをまとめ
たオブジェクトでSQLから呼び出すことが可能です。（PL/SQLやSQL
PLからも呼び出すことも可能）
• SQLのみでは出来ないような処理（例：ファイル操作、メッセージの出力
など）を行うことができます。
• Oracleと同じ名前で同じ機能を持つビルトイン・パッケージ・ライブラリー
を提供
• OracleのPL/SQLの開発では、ビルトイン・パッケージ･ライブラリーが
頻繁に利用されます。
• OracleのPL/SQLの移行の際に、使用されているビルトイン・パッケー
ジ･ライブラリーがDB2 V9.7でも提供されていれば書き直しなしでの移
行も可能です。
23
© 2011 IBM Corporation
ビルトイン・パッケージ・ライブラリー（ビルトイン・モジュール）
モジュール名
説明
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 メールを送信する機能を提供しま
す。
24
© 2011 IBM Corporation
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 コマンドが正常に完了しました。
DBMS_OUTPUTパッケージのPUTプ
ロシージャー、PUT_LINEプロシー
ジャーを呼び出して、メッセージ・
バッファーへの書き込みを行います。
CLPでメッセージバッ
ファーを読み出すための設
定
CALL proc1( '10' )
リターン状況 = 0
P1 = 10
25
メッセージ・バッファーか
ら読み出されたデータ
© 2011 IBM Corporation
UTL_DIR、UTL_FILE使用例
• UTL_DIRパッケージ
• ディレクトリを管理するためのプ
ロシージャーや関数を提供
• UTL_FILEパッケージ
• ファイルの読み取りや書き込みを
行うプロシージャーや関数を提供
UTL_DIRを使用してディレクトリの準備
call utl_dir.create_directory('mydir','C:¥work')
リターン状況 = 0
call utl_dir.get_directory_path('mydir',?)
出力パラメーターの値
-------------------------パラメーター名: PATH
パラメーター値: C:¥work
リターン状況 = 0
26
UTL_FILEを使用してファイルに出力
begin
declare 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
・・・省略・・・
CSV形式で
ファイル出
力
© 2011 IBM Corporation
Weak Typing
• DB2 9.5まではstrict(厳密な）データタイピング
• トレンドはweak(緩やかな)タイピング (PERL, RUBY, PHP, …)
• DB2 9.7での新しい振る舞い
• 数値と文字列間の暗黙的な変換（cast):
• 割当て
SET salary := ‘52000’
• 比較
WHERE salary > ‘52000’
• ビルトイン関数での利用
‘salary: ‘ || 52000 （文字列連結の例）
• NULL やパラメーター・マーカーの利用性向上
• 非型付きパラメーター・マーカーとNULLを伴う関数呼び出し
• この振る舞いにより、明示的なキャストが不要になりステートメント量が少なくな
ります。
27
© 2011 IBM Corporation
暗黙的な変換の例
V9.5
INTEGER型で定
義している。
$ db2 "select * from itest_w where salary > '10000‘"
SQL0401N The data types of the operands for the operation ">" are
not compatible. SQLSTATE=42818
エラー
V9.7
$ db2 "select * from itest_w where salary > '10000‘"
NAME
SALARY
---------------- ----------TEST
200000
NumericとString
間の暗黙的な変換。
OK
1 record(s) selected.
28
© 2011 IBM Corporation
データ・ディクショナリー互換ビュー
• 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
29
© 2011 IBM Corporation
データディクショナリー互換ビュー使用例
データディクショナリーのUSER_INDEXESを参照→CUSTOMER表のインデックスを参照
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
-------------------SQL090627173927420
SQL090627173928650
CUST_PHONET_XMLIDX
SQL090627173928640
CUST_PHONES_XMLIDX
・・・
INDEX_TYPE
-------------------XRGN
XVIP
XVIL
XVIP
XVIL
TABLE_NAME
-------------------CUSTOMER
CUSTOMER
CUSTOMER
CUSTOMER
CUSTOMER
データディクショナリーのTABを参照→ユーザーのテーブルを参照
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
・・・
30
© 2011 IBM Corporation
並行性制御に関するこれまでの機能強化
• 並行性を向上させる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でブロッキングのスキャンはロックウェイトしない
•
•
•
•
詳細は TechnicalFlash：「DB2 for LUW V9.5のLock Avoidance機能によって、V9.1までは発生していた
ロック競合が発生しなくなることがある」（DM-09-013）を参照
•
31
ケース１： WITH RS USE AND KEEP EXCLUSIVE LOCKS のXロック
ケース２：どの列を変更しなかったUPDATEのXロック
ケース３：更新された列以外で構成された索引ONLYスキャン
http://www-06.ibm.com/jp/domino01/mkt/cnpages1.nsf/page/default-000D266E
© 2011 IBM Corporation
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
（デフォルト）
Uncommitted Read
(非コミット読取り)
なし
READ UNCOMMITTED
32
© 2011 IBM Corporation
DB2 （V9.5以前）の読み取り一貫性
• DB2は最新のデータを読む
• 参照処理は、更新処理がコミットされるまで待つ
A
TRN1
TRN2
B
UPDATE
C
C’
UPDATE
COMMIT
D
D’
TRN2は、TRN1が
COMMITするまで
WAIT
SELECT
E
F
読むデータは常に最新だが、参照処理がロック待機する可能性あり
33
© 2011 IBM Corporation
Oracleの読み取り一貫性（Read Committed）
•
マルチ・バージョン一貫性制御 (Multi-Version Concurrency Control)
•
更新処理により、更新前イメージが専用の領域へ書き出される
•
参照処理はコミットを待たずに更新前イメージを読む
• Select発行時点でコミット済みのデータを読む
• 下記の例では、UPDATEがCOMMITされていたとしてもTRN2は更新前データを読む
SCN=10
TRN1
UPDATE
A
B
SCN=110
UPDATE
SCN=98
UNDOセグメント
C
C’
C
D
D’
D
SCN=29
SCN=100
TRN2
SELECT
E
F
読んだデータが最新とは限らないが、参照処理はロック待機しない
34
© 2011 IBM Corporation
DB2 9.7の読み取り一貫性（Currently Committed）
•
未コミットの更新があってもロック待機をしない
• 参照処理は、更新処理に伴うロックの開放を待たず、更新前のデータ
（コミット済みの最新データ）をログ（ログバッファー）から読む
• 常に最新のコミット済みデータを読む
A
B
トランザクションログ
（ログバッファー）
UPDATE
C
C’
C
UPDATE
D
D’
D
TRN1
TRN2
SELECT
E
F
参照処理はロック待機せず、かつコミット済みの最新データを読む
35
© 2011 IBM Corporation
同時実行性の向上： Currently Committed
別アプリから参照可能か
別アプリから更新可能か
DB2 9.5以前の
参照中の行を
Yes
基本的にYes（※）
CS 分離レベル
更新中の行を
No
No
別アプリから参照可能か
別アプリから更新可能か
Oracleの
参照中の行を
Yes
Yes
Read Committed分離レベル
更新中の行を
Yes
No
別アプリから参照可能か
別アプリから更新可能か
DB2 9.7以降の
参照中の行を
Yes
Yes
CS分離レベル w/CC
更新中の行を
Yes
No
Yes：参照 or 更新可
No ：ロックウェイト
※カーソルのある行であった場合は、一瞬だけ待つ。基本的に更新は可能。
読み取り処理が書き込み処理を妨害しない (ReadがLockを取らない）
書き込み処理が読み取り処理を妨害しない（ReadはLock行をバイパス)
36
© 2011 IBM Corporation
Currently Committedの設定方法
•
データベース構成パラメーター（CUR_COMMIT）
•
•
•
•
パッケージ作成時の指定（BIND コマンド）
•
•
•
ON
:分離レベルCSはCurrently Committedとして動く（新規作成DBのデフォルト）
AVAILABLE :アプリケーションから明示的に指定された場合にCurrently Committed有効
DISABLED :Currently Committed無効（MigrationされたDBでのデフォルト)
CONCURRENTACCESSRESOLUTIONオプションで指定
動的SQLであっても、使用するパッケージを明示的にBINDすることで制御可能
セッション単位の指定
•
JDBC、CLI、.NET等のアプリ・インターフェースからセッション単位で指定可能
CUR_COMMIT DB構成パラメーター
BINDオプショ
ン
セッションで指
定
•
CC設定の原則
• スコープが狭い設定パラ
メータがより優先される
• アクセスプラン決定時の設
定がセクションに書き込ま
れ、有効になる。
• 静的SQLでは注意
37
© 2011 IBM Corporation
並行性の向上 – Currently Committed まとめ
• Currently Committedの機能により、最新の情報を提供しな
がらもアプリケーションの並行性が向上
• 未コミットのINSERT行はスキップ
• 未コミットのUPDATE行は更新前データを戻す
• 未コミットのDELETE行は、参照対象
（未だDELETEされていない状態が最新の状態であるため）
• 既存のログをベースにした仕組みにり、更新と参照が競合した
場合にのみ、ログへのアクセスが発生する。オーバーヘッドが
少なく、パフォーマンスに優れている
• DB構成パラメータ：CUR_COMMIT、BINDオプションで設定可
能。有効/無効はEXPLAINで確認
38
© 2011 IBM Corporation
Oracle : FOR UPDATE

航空券の予約アプリケーションを作成することを考えてみます。
以下の手順で予約を実施することになります
１）空席の検索、２）空席の確認、３）予約
ロック制御をどのように行うべきでしょうか？
Oracle
trx1
(1)SELECT FOR UPDATE
空席を検索
23F席が最後の1席
⇒23Fの行にTXロックを取得
trx2
(2)SELECT FOR UPDATE
空席を検索
23Fの行にTXロックを取得しよう
とするがロック待機となる
(3)UPDATEして COMMIT
(4)SELECT FOR UPDATE文のロック
待機が解除され“空席なし”の結果となる
39
© 2011 IBM Corporation
DB2 : FOR UPDATE
航空券の予約アプリケーションを作成することを考えてみます。
以下の手順で予約を実施することになります
１）空席の検索、２）空席の確認、３）予約
ロック制御をどのように行うべきでしょうか？
DB2
trx1
(1)SELECT FOR UPDATE WITH RS
空席の検索
23F席が最後の1席
⇒23Fの行にUロックを取得
trx2
(2)SELECT FOR UPDATE WITH RS
空席検索
23Fの行にUロックを取得しようとす
るが、ロック待機となる
(3)UPDATEしてCOMMIT
(4)SELECT FOR UPDATE文のロック待ちが
解除され、”空席なし”の結果となる.
40
© 2011 IBM Corporation
DB2とOracleでのFOR UPDATEの違い
• Oracle
•
SELECT FOR UPDATEを実行した時に,それぞれの行に排他ロックが取得され
る
•
コミットされるまで排他ロックを取得
• DB2
•
SELECT FOR UPDATEを実行した時に,それぞれの行に更新ロックが取得され
る
•
以下のタイミングまで更新ロックを取得
• 次の行がフェッチされるまで(分離レベルCSでの読み取り)
• トランザクションがコミットされるまで(分離レベルRSでの読み取り)
• DB2でUPDATE前にSELECTする場合には, 以下のことに注意
41
•
デッドロックを避けるために、“FOR UPDATE”を使用して排他制御を行う
•
ロックをCOMMITするまで保持するように分離レベルRSを指定する
© 2011 IBM Corporation
DB2とOracleでのFOR UPDATEの違い
• “WITH RS USE AND KEEP UPDATE LOCKS”オプション
• SELECT文で複数行へのUPDATE LOCKを取得した場合に、
“ブロックフェッチ”が有効になる,
• 1行の場合には, FOR UPDATEと同じ動作
例
• SELECT * FROM STAFF WHERE ID=10 FOR UPDATE WITH RS
• SELECT * FROM STAFF WHERE ID=10 WITH RS USE AND KEEP UPDATE
LOCKS
• JOINに対しても使用できる
• FOR UPDATEはJOINに対しては使用できない
(Oracleでは、JOINに対してもFOR UPDATEを使用できる)
• UPDATE LOCKはJOINもとのテーブルすべてに取得ため、アプリケーションの同時
並行性に考慮が必要
42
© 2011 IBM Corporation
ロック待ちの違い
• DB2
• セッション単位でロックタイムアウトを設定可能
• SET LOCK TIMEOUT WAIT
• タイムアウトしないで待ち続ける
• SET LOCK TIMEOUT NOT WAIT
• 待たずにタイムアウトエラー（SQL0911）を返す
• "db2set DB2LOCK_TO_RB=STATEMENT"を設定すればステートメントレベル
のロールバックにできる
• SET LOCK TIMEOUT integer
• 指定された秒数待ち、タイムアウトする
• Oracle
• Oracleでは以下のような設定で、ステートメントレベルに設定できる
select * from emp where EMPNO=7369 for update nowait;
43
© 2011 IBM Corporation
Let’s go to Lab2!!
44
© 2011 IBM Corporation