Upload Login /
...

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

by user

on
Category: Documents
>> Downloads: 14
106

views

Report

Comments

Description

Transcript

Oracle 第３章 からの移行を促進する新機能
＜２００９年１２月＞
第３章 Oracleからの移行を促進する新機能
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する
使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同
様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。
© Copyright IBM Japan Co., Ltd. 2009
内容
• Oracleからの
からの移行
からの移行
• DB2_COMPATIBILITY_VECTORレジストリ
レジストリ変数
レジストリ変数
• データタイプの
データタイプの互換性
• PL/SQLサポート
サポート
• パッケージサポート
• スカラー関数
スカラー関数
• ビルトイン関数
ビルトイン関数、
関数、パッケージ
• Weak Typing
• ディクショナリ互換
ディクショナリ互換ビュー
互換ビュー
• Currently Commited
• ロックの
ロックの考慮点
2
© 2009 IBM Corporation
IBM DB2 : Why DB2?
Reduce Operational Costs
1. 低コスト
運用管理の自動化,と
ストレージの最小化を
高いパフォーマンスで実現.
3
2. 信頼性
業界で実証された信頼性、
可用性、セキュリティー、
連続稼働の技術
3. 使いやすさ
開発、仮想
アプリケーションを
容易に実現
© 2009 IBM Corporation
なぜ移行を行えない？？
• 移行時に
移行時に問題となる
問題となる非互換機能
となる非互換機能
• Data types, locking model, weak typing,
packages, …
DB2
Oracle
• 非互換機能の
非互換機能の吸収 パフォーマンスの
パフォーマンスの劣化
これはDB2
V9.7以前
以前までの
これは
以前までの
の話です。
です。
• 開発チーム
開発チームの
チームのスキルが
スキルが足りない
ORACLE
4
DB2 V9.7の
の新機能を
新機能を
見て行きましょう！
きましょう！
DB2
© 2009 IBM Corporation
DB2_COMPATIBILITY_VECTOR レジストリ変数
• Oracleとの互換機能を有効にするレジストリ変数
• 設定方法
• 12ビットのビットでそれぞれの機能のON、OFFを決定します。このビッ
トの16進値を設定します。
• それぞれのビットに割り当てられている機能は次ページを参照
• DB2_COMPATIBILITY_VECTOR=ORAの設定も可能
• この設定はFFF（全ての機能をON）と同じように全ての互換フィーチャー
を有効にします。
• この設定はOracle との最大の互換性を実現する設定であり、Oracle互
換機能を使用する際の推奨値
• この設定により、DB2_DEFERRED_PREPARE_SEMANTICS=YESの設定
も暗黙的に有効になります。
• DB2_DEFERRED_PREPARE_SEMANTICS=YESは、Unicode,SBCS環
境でのみの推奨値
5
© 2009 IBM Corporation
DB2_COMPATIBILITY_VECTORで設定可能なフィーチャー
ビット位置 互換性フィーチャー
1 (0x01)
ROWNUM
2 (0x02)
3 (0x04)
4 (0x08)
5 (0x10)
6 (0x20)
7 (0x40)
未使用
外部結合演算子
階層照会
NUMBER データ・タイプ
VARCHAR2 データ・タイプ
DATE データ・タイプ
8 (0x80)
TRUNCATE TABLE
9 (0x100)
文字リテラル
10 (0x200) コレクション・メソッド
11 (0x400) データ・ディクショナリー互換
ビュー
12 (0x800) PL/SQL のコンパイル
説明
ROWNUM を ROW_NUMBER() OVER() の同義語として使用することを可能にし、
ROWNUM を SQL ステートメントの WHERE 節に含めることを許可します。
未使用
外部結合演算子 (+) のサポートを有効にします。
CONNECT BY 節を使用した階層照会のサポートを有効にします。
NUMBER データ・タイプおよび関連する数値処理を有効にします。
VARCHAR2 データ・タイプおよび関連する文字ストリング処理を有効にします。
DATE データ・タイプを日付と時間の値を結合した TIMESTAMP(0) として使用することを
可能にします。
TRUNCATE ステートメント用の代替セマンティクスを有効にします。IMMEDIATE がオプ
ションのキーワードであり、指定がない場合にデフォルトになります。TRUNCATE ステート
メントが論理作業単位内の最初のステートメントではない場合、TRUNCATE ステートメン
トが実行される前に暗黙的なコミット操作が実行されます。
バイト長が 254 以下である文字定数および GRAPHIC ストリング定数に、CHAR データ・
タイプまたは GRAPHIC データ・タイプ (VARCHAR データ・タイプでも VARGRAPHIC デー
タ・タイプでもない) を割り当てることを可能にします。
配列で first、last、next、previous などの演算を実行するメソッドの使用を可能にします。
また、配列内の特定の要素の参照に、大括弧の代わりに括弧を使用できるようにします。
例えば、array1(i) は array1 の要素 i を参照します。
データ・ディクショナリー互換ビューの作成を可能にします。
PL/SQL ステートメントおよび言語要素のコンパイルと実行を可能にします。
※網がけ部分の機能は、データベース作成前にレジストリ変数の設定が必要
6
© 2009 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 | find "互換性"
7
NUMBER データ・タイプの互換性
= ON
VARCHAR2 データ・タイプの互換性
= ON
データ・タイプ DATE の TIMESTAMP(0) への互換性
= ON
© 2009 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
© 2009 IBM Corporation
DATEデータタイプの互換性
• 「データ・タイプ DATE の TIMESTAMP(0) への互換性」がON
• DATEタイプがTIMESTAMP(0)として扱われる
• OracleのDATEと同様に秒までの精度を持つ
OFFの場合（従来のDB2のDATEタイプ）
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。
ONの場合（DATEタイプがOracleと同じ精度を持つ）
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
describe table tab1
データ・タイ データ・ 列の スケ
列名 プ・スキーマ タイプ名 長さ ール NULL
----- --------- ------------- ----- ----- -----C1
SYSIBM
DATE
4
0 はい
データ・タイ データ・ 列の スケ
列名 プ・スキーマ タイプ名 長さ ール NULL
----- --------- ------------- ----- ----- -----C1
SYSIBM
TIMESTAMP
7
0 はい
1 レコードが選択されました。
1 レコードが選択されました。
insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。
insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。
select * from tab1
select * from tab1
出力形式（精度）が異なる
C1
---------2009-04-22
9
C1
------------------2009-04-22-13.39.31
Date型でテーブルを作
成するとTimestamp(0)
として定義される
© 2009 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))
number
number
number
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
© 2009 IBM Corporation
VARCHAR2 データ・タイプの互換性
• 「VARCHAR2データ
データ・
タイプの互換性」
互換性」がON
データ・タイプの
• 表定義時の列タイプやプロシージャー内の変数タイプなどでVARCHAR2型を使用
することが出来る
• 実際には、VARCHARとして作成される
• 空文字（''）がNULLとして扱われる
• VARCHARの比較には非空白埋め比較セマンティクスが使用される
• 長さが最大 254 バイトまでの文字ストリング・リテラルのデータ・タイプは CHARになり、
254以上はVARCHARになる
VARCHAR2データ
データ・
データ・タイプの
タイプの
互換性=ON
互換性
create table tab1(c1 varchar2(10),c2
varchar2(10)
for bit data)
varchar2
varchar2
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
DDLでVARCHAR2が使用できる
データ・タイ データ・
列の
スケ
列名
プ・スキーマ タイプ名
長さ
ール NULL
--------------- --------- ------------------- ---------- ----- -----C1
SYSIBM
VARCHAR
10
0 はい
C2
SYSIBM
VARCHAR
10
0 はい
2 レコードが選択されました。
11
Varchar2型でテーブルを作成す
るとVarcharとして定義される
© 2009 IBM Corporation
非空白埋め比較セマンティクスと空白埋め比較セマンティクス
• 非空白埋め
非空白埋め比較セマンティクス
比較セマンティクス
• 以下の文字は異なるものとみなす
VARCHAR2 データ・タイ
プの互換性＝ON
• 'A'、'A_'、'A__' （'_'は空白文字）
• 空白埋め
空白埋め比較セマンティクス
比較セマンティクス
• 空白文字をパティングして比較する
VARCHAR2 データ・タイ
プの互換性＝OFF
• 末尾の空白文字の違いは無視される
• 以下の文字は同じものとみなす
• 'A'、'A_'、'A__' （'_'は空白文字）
12
© 2009 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
13
© 2009 IBM Corporation
PL/SQLが記述される場所
• PL/SQLブロック（Anonymous Block）
PL/SQLブロック
declare
・・・
begin
・・・
end
• データベース外部に保持
実行
• PL/SQLストアード・プログラム
（データベースのオブジェクトとしてデータベース内にロジックを格納）
• プロシージャー
• ファンクション
• パッケージ
• トリガー
14
プロシージャー
create procedure xx
is
・・・
ファンクション
begin
・・・
create function xxx
end
return varchar2
is
・・・
begin
・・・
end;
トリガー
create trigger xxx
before insert ・・・
on ・・・
for each row
declare
・・・
begin
・・・
end;
© 2009 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. All
rights reserved.
15
© 2009 IBM Corporation
PL/SQLを実行するには？
• DB2_COMPATIBILITY_VECTOR （レジストリ変数）
• PL/SQLコンパイラの使用可能にする
• PL/SQLを実行するためには設定は必須
• SET SQLCOMPAT PLSQL（CLPオプション）
• CLPにPL/SQLの区切り文字（/）を認識させるオプション
16
© 2009 IBM Corporation
パッケージ（モジュール）のサポート
• Oracleのパッケージに相当するオブジェクトとしてモジュールを
サポート
• 関数、
関数、プロシージャー、
プロシージャー、タイプ、
タイプ、変数などの
変数などのオブジェクト
などのオブジェクトをまと
オブジェクトをまと
めたオブジェクト
めたオブジェクト
モジュール
グローバル変数
SQLプロシージャー
SQLファンクション
・
・
・
• PL/SQLの
のパッケージと
パッケージと同じ機能を
機能を提供
• PL/SQLではCREATE PACKAGE文によりモジュールを作成
• SQL PLではCREATE MODULE文によりモジュールを作成
17
© 2009 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
仕様部
本体
© 2009 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 コマンドが正常に完了しました。
create or replace package pkg1 is
var1 int :=0;
procedure print;
procedure plus(p1 in int);
function get return int;
end;
仕様部
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 コマンドが正常に完了しました。
alter module mod1 add function get() returns int return var1
DB20000I SQL コマンドが正常に完了しました。
19
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 コマンドが正常に完了しました。
© 2009 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
© 2009 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
© 2009 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 レコードが選択されました。
1 レコードが選択されました。
文字型に変換
数値型に変換
22
© 2009 IBM Corporation
ビルトイン・パッケージ・ライブラリー（ビルトイン・モジュール）
• V9.7では
ではアプリケーション
役立つビルトイン・
ビルトイン・パッケージ・
パッケージ・ライブラ
ではアプリケーション開発
アプリケーション開発に
開発に役立つ
リーを
リーを多数提供
• パッケージ（モジュール）は、関数やプロシージャーや変数などをまとめ
たオブジェクトでSQLから呼び出すことが可能です。（PL/SQLやSQL
PLからも呼び出すことも可能）
• SQLのみでは出来ないような処理（例：ファイル操作、メッセージの出力
など）を行うことができます。
• Oracleと
と同じ名前で
名前で同じ機能を
機能を持つビルトイン・
ビルトイン・パッケージ・
パッケージ・ライブラリー
を提供
• OracleのPL/SQLの開発では、ビルトイン・パッケージ･ライブラリーが
頻繁に利用されます。
• OracleのPL/SQLの移行の際に、使用されているビルトイン・パッケー
ジ･ライブラリーがDB2 V9.7でも提供されていれば書き直しなしでの移
行も可能です。
23
© 2009 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
© 2009 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(
DBMS_OUTPUT.PUT 'P1 = ' );
CALL DBMS_OUTPUT.PUT_LINE(
DBMS_OUTPUT.PUT_LINE P1 );
END
DB20000I SQL コマンドが正常に完了しました。
DBMS_OUTPUTパッケージのPUTプロ
シージャー、PUT_LINEプロシージャーを
呼び出して、メッセージ・バッファーへの書
き込みを行います。
SET SERVEROUTPUT ON
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。
CLPでメッセージバッファーを
読み出すための設定
CALL proc1( '10' )
リターン状況 = 0
メッセージ・バッファーから読
み出されたデータ
P1 = 10
25
© 2009 IBM Corporation
UTL_DIR、UTL_FILE使用例
• UTL_DIRパッケージ
パッケージ
• ディレクトリを管理するためのプ
ロシージャーや関数を提供
• UTL_FILEパッケージ
パッケージ
• ファイルの読み取りや書き込みを
行うプロシージャーや関数を提供
UTL_DIRを使用してディレクトリの準備
call utl_dir.create_directory('mydir','C:¥work')
utl_dir.create_directory
リターン状況 = 0
call utl_dir.get_directory_path('mydir',?)
utl_dir.get_directory_path
出力パラメーターの値
-------------------------パラメーター名: PATH
パラメーター値: C:¥work
リターン状況 = 0
26
UTL_FILEを使用してファイルに出力
begin
declare v_filehandle
utl_file.file_type;
utl_file.file_type
declare isopen
boolean;
declare row
varchar(4000);
set v_filehandle = utl_file.fopen('mydir','myfile.csv','w');
utl_file.fopen
set isopen = utl_file.is_open(
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);
utl_file.put_line
end for;
call utl_file.fclose(v_filehandle);
utl_file.fclose
end
DB20000I SQL コマンドが正常に完了しました。
!type myfile.csv
A00,SPIFFY COMPUTER SERVICE DIV.,000010
B01,PLANNING,000020
C01,INFORMATION CENTER,000030
D11,MANUFACTURING SYSTEMS,000060
・・・省略・・・
CSV形式で
ファイル出力
© 2009 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
© 2009 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
© 2009 IBM Corporation
データ・ディクショナリー互換ビュー
• Oracleのデータ・ディクショナリの互換ビューを提供
• 以下の
以下のネーミング・
ネーミング・ルールを
ルールを持つビューを
ビューを作成
• USER_*
（例 USER_TABLES）
• ユーザーのオブジェクトを表示
• ALL_*
（例 ALL_TABLES）
• ユーザーがアクセス可能なオブジェクトを表示
• DBA_*
（例 DBA_TABLES）
• 全てのオブジェクトを表示
• Oracleと
と同様にそれぞれの
同様にそれぞれのビュー
Aliasが
が作成されているため
作成されているため、
にそれぞれのビューには
ビューにはPublic
には
されているため、
スキーマ指定
スキーマ指定なしでも
指定なしでも呼
なしでも呼び出し可能
(*) 作成される互換ビューの一覧
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.porting.doc/doc/c0054440.html
29
© 2009 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
© 2009 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
© 2009 IBM Corporation
DB2 9.7での機能強化
• CS分離
分離レベル
分離レベルの
レベルの機能拡張
新たな読
の導入
たな読み取り一貫性：
一貫性：Currently Committedの
• 読み取り一貫性の比較
• Currently Committedの挙動
• Currently Committedを有効に使うための設定
• 考慮点
参考：分離レベル
ISO分離
分離レベル
分離レベル
SERIALIZABLE
REPEATABLE READ
READ COMMITTED
READ UNCOMMITTED
32
分離レベル
分離レベルを
レベルを定義する
定義する現象
する現象（
現象（３種）
幻像読取りなし
幻像読取りなし
反復不能読取りなし
反復不能読取りなし
未コミット読取
コミット読取りなし
読取りなし
対応する
の分離レベル
対応するDB2の
する
分離レベル
対応する
の分離レベル
対応するOracleの
する
分離レベル
Repeatable Read
(反復可能読取
反復可能読取り
反復可能読取り)
SERIALIZABLE
反復不能読取りなし
反復不能読取りなし
未コミット読取
コミット読取りなし
読取りなし
Read Stability
(読取
読取り
読取り固定)
固定
なし
未コミット読取
コミット読取りなし
読取りなし
Cursor Stability （デフォルト）
デフォルト）
(カーソル
カーソル固定
カーソル固定)
固定
READ COMMITTED
（デフォルト）
デフォルト）
Uncommitted Read
(非
非コミット読取
コミット読取り
読取り)
なし
© 2009 IBM Corporation
DB2 （V9.5以前）の読み取り一貫性
• DB2は
は最新の
最新のデータを
データを読む
• 参照処理は、更新処理がコミットされるまで待つ
A
TRN1
B
UPDATE
C
C’
UPDATE
COMMIT
D
D’
TRN2
TRN2は、TRN1が
COMMITするまで
WAIT
SELECT
E
F
読むデータは
データは常に最新だが
最新だが、
だが、参照処理が
参照処理がロック待機
ロック待機する
待機する可能性
する可能性あり
可能性あり
33
© 2009 IBM Corporation
Oracleの読み取り一貫性 （Read Committed）
•
マルチ・
マルチ・バージョン一貫性制御
バージョン一貫性制御 (Multi-Version Concurrency Control)
•
更新処理により、更新前イメージが専用の領域へ書き出される
•
参照処理はコミットを待たずに更新前イメージを読む
• Select発行時点でコミット済みのデータを読む
• 下記の例では、UPDATEがCOMMITされていたとしてもTRN2は更新前データを読む
SCN=10
SCN=100
UNDOセグメント
B
TRN1
UPDATE
A
SCN=110
UPDATE
SCN=98
TRN2
C
C’
C
D
D’
D
SCN=29
SELECT
E
F
読んだデータ
んだデータが
データが最新とは
最新とは限
とは限らないが、
らないが、参照処理は
参照処理はロック待機
ロック待機しない
待機しない
34
© 2009 IBM Corporation
DB2 9.7の読み取り一貫性（Currently Committed）
•
未コミットの
コミットの更新があっても
更新があってもロック
があってもロック待機
ロック待機をしない
待機をしない
• 参照処理は、更新処理に伴うロックの開放を待たず、更新前のデータ
（コミット済みの最新データ）をログ（ログバッファー）から読む
• 常に最新のコミット済みデータを読む
A
B
トランザクションログ
（ログバッファー）
UPDATE
C
C’
C
UPDATE
D
D’
D
TRN1
TRN2
SELECT
E
F
参照処理は
参照処理はロック待機
ロック待機せず
待機せず、
せず、かつコミット
かつコミット済
コミット済みの最新
みの最新データ
最新データを
データを読む
35
© 2009 IBM Corporation
同時実行性の向上： Currently Committed
別アプリから
アプリから参照可能
から参照可能か
参照可能か
別アプリから
アプリから更新可能
から更新可能か
更新可能か
DB2 9.5以前
9.5以前の
以前の
参照中の
参照中の行を
Yes
Maybe
CS 分離レベル
分離レベル
更新中の
更新中の行を
No
No
別アプリから
アプリから参照可能
から参照可能か
参照可能か
別アプリから
アプリから更新可能
から更新可能か
更新可能か
Oracleの
Oracleの
参照中の
参照中の行を
Yes
Yes
Read Committed分離
Committed分離レベル
分離レベル
更新中の
更新中の行を
Yes
No
別アプリから
アプリから参照可能
から参照可能か
参照可能か
別アプリから
アプリから更新可能
から更新可能か
更新可能か
DB2 9.7以降
9.7以降の
以降の
参照中の
参照中の行を
Yes
Yes
CS分離
CS分離レベル
分離レベル w/CC
更新中の
更新中の行を
Yes
No
Yes：参照 or 更新可
No ：ロックウェイト
読み取り処理が
処理が書き込み処理を
処理を妨害しない
妨害しない (Readが
(ReadがLockを
Lockを取らない）
らない）
書き込み処理が
処理が読み取り処理を
処理を妨害しない
妨害しない（
しない（Readは
ReadはLock行
Lock行をバイパス)
バイパス)
36
© 2009 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構成
構成パラメーター
DB構成パラメーター
BINDオプション
BINDオプション
セッションで
セッションで指定
CC設定
設定の
設定の原則
• スコープが狭い設定パラ
メータがより優先される
• アクセスプラン決定時の設
定がセクションに書き込ま
れ、有効になる。
• 静的SQLでは注意
37
© 2009 IBM Corporation
並行性の
並行性の向上 – Currently Committed まとめ
• Currently Committedの
の機能により
機能により、
により、最新の
最新の情報を
情報を提供しな
提供しな
がらもアプリケーション
がらもアプリケーションの
アプリケーションの並行性が
並行性が向上
• 未コミットのINSERT行はスキップ
• 未コミットのUPDATE行は更新前データを戻す
• 未コミットのDELETE行は、参照対象
（未だDELETEされていない状態が最新の状態であるため）
• 既存の
既存のログを
ログをベースにした
ベースにした仕組
にした仕組みに
仕組みにり
みにり、更新と
更新と参照が
参照が競合した
競合した
場合にのみ
場合にのみ、
ログへのアクセス
発生する。
オーバーヘッドが
にのみ、ログへの
へのアクセスが
アクセスが発生する
する。オーバーヘッドが
少なく、
なく、パフォーマンスに
パフォーマンスに優れている
• DB構成
構成パラメータ
、BINDオプション
オプションで
構成パラメータ：
パラメータ：CUR_COMMIT、
オプションで設定可
能。有効/無効
で確認
有効 無効は
無効はEXPLAINで
38
© 2009 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
© 2009 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
© 2009 IBM Corporation
DB2とOracleでのFOR UPDAETの違い
• Oracle
•
•
SELECT FOR UPDATEを実行した時に,それぞれの行に排他ロックが取得され
る
コミットされるまで
コミットされるまで排他
されるまで排他ロック
排他ロックを
ロックを取得
• DB2
•
•
SELECT FOR UPDATEを実行した時に,それぞれの行に更新ロックが取得され
る
以下のタイミングまで更新ロックを取得
• 次の行がフェッチされるまで
フェッチされるまで(分離
されるまで 分離レベル
分離レベルCSでの
レベル での読
での読み取り)
• トランザクションが
トランザクションがコミットされるまで
コミットされるまで(分離
されるまで 分離レベル
分離レベルRSでの
レベル での読
での読み取り)
• DB2で
でUPDATE前
前にSELECTする
する場合
する場合には
場合には,
には 以下のことに
以下のことに注意
のことに注意
41
•
デッドロックを避けるために、“FOR UPDATE”を使用して排他制御を行う
•
ロックをCOMMITするまで保持するように分離レベルRSを指定する
© 2009 IBM Corporation
DB2とOracleでのFOR UPDAETの違い
• “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
© 2009 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
© 2009 IBM Corporation
Let’s go to Lab3!!
44
© 2009 IBM Corporation
Fly UP