<4D F736F F F696E74202D2091E682528FCD2D4F C6582A982E782CC88DA8D7382F091A B782E990568B40945C2E707074>

Transcription

1 <2009 年 12 月 > 第 3 章 Oracle からの移行を促進する新機能 本書に含まれている情報は 正式な IBM のテストを受けていません また 明記にしろ 暗黙的にしろ なんらの保証もなしに配布されるものです この情報の使用またはこれらの技術の実施は いずれも 使用先の責任において行われるべきものであり それらを評価し 実際に使用する環境に統合する使用先の判断に依存しています それぞれの項目は ある特定の状態において正確であることが IBM によって調べられていますが 他のところで同じまたは同様の結果が得られる保証はありません これらの技術を自身の環境に適用することを試みる使用先は 自己の責任において行う必要があります Copyright IBM Japan Co., Ltd. 2009

2 内容 Oracle からの移行 DB2_COMPATIBILITY_VECTOR レジストリ変数 データタイプの互換性 PL/SQL サポート パッケージサポート スカラー関数 ビルトイン関数関数 パッケージ Weak Typing ディクショナリ互換互換ビュー Currently Commited ロックの考慮点 IBM Corporation

3 IBM DB2 : Why DB2? Reduce Operational Costs 1. 低コスト運用管理の自動化, とストレージの最小化を高いパフォーマンスで実現. 2. 信頼性業界で実証された信頼性 可用性 セキュリティー 連続稼働の技術 3. 使いやすさ開発 仮想アプリケーションを容易に実現 IBM Corporation

4 なぜ移行を行えない?? 移行時に問題問題となるとなる非互換機能 Data types, locking model, weak typing, packages, DB2 Oracle 非互換機能の吸収 パフォーマンスの劣化 開発チームチームのスキルスキルが足りない これは DB2 V9.7 以前までのの話ですです DB2 V9.7の新機能新機能を見て行きましょう! ORACLE DB IBM Corporation

5 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 環境でのみの推奨値 IBM Corporation

6 DB2_COMPATIBILITY_VECTOR で設定可能なフィーチャー ビット位置 1 (0x01) 2 (0x02) 3 (0x04) 4 (0x08) 5 (0x10) 6 (0x20) 7 (0x40) 8 (0x80) 9 (0x100) 10 (0x200) 11 (0x400) 12 (0x800) 互換性フィーチャー ROWNUM 未使用外部結合演算子階層照会 NUMBER データ タイプ VARCHAR2 データ タイプ DATE データ タイプ TRUNCATE TABLE 文字リテラルコレクション メソッドデータ ディクショナリー互換ビュー 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 ステートメントおよび言語要素のコンパイルと実行を可能にします 網がけ部分の機能は データベース作成前にレジストリ変数の設定が必要 IBM Corporation

7 データタイプの互換性 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 データ タイプの互換性 VARCHAR2 データ タイプの互換性 データ タイプ DATE の TIMESTAMP(0) への互換性 = ON = ON = ON IBM Corporation

8 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 はい C3 SYSIBM TIMESTAMP 10 6 はい 精度の指定により列の長さが変わる 精度を短くとることでスペースの節約が可能 14 レコードが選択されました IBM Corporation

9 DATE データタイプの互換性 データ タイプ DATE の TIMESTAMP(0) への互換性 がON DATEタイプがTIMESTAMP(0) として扱われる OracleのDATEと同様に秒までの精度を持つ OFF の場合 ( 従来の DB2 の DATE タイプ ) ON の場合 (DATE タイプが Oracle と同じ精度を持つ ) 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 C 出力形式 ( 精度 ) が異なる 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 C Date 型でテーブルを作成すると Timestamp(0) として定義される IBM Corporation

10 NUMBER データ タイプの互換性 NUMBER データ タイプタイプの互換性互換性 が ON 表定義時の列タイプやプロシージャー内の変数タイプなどでNUMBER 型を使用することが出来る 実際には以下のような型として作成される NUMBER -> DECFLOAT(16) NUMBER(p)->DECIMAL(p) NUMBER(p,s)->DECIMAL(p,s) 精度 (p) は31まで NUMBER データ タイプタイプの create table tab1(c1 number,c2 number(10),c3 number(10,5)) 互換性 =ON 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 として定義されます IBM Corporation

11 VARCHAR2 データ タイプの互換性 VARCHAR2 データ タイプタイプの互換性互換性 が ON 表定義時の列タイプやプロシージャー内の変数タイプなどでVARCHAR2 型を使用することが出来る 実際には VARCHARとして作成される 空文字 ('') がNULLとして扱われる VARCHAR の比較には非空白埋め比較セマンティクスが使用される 長さが最大 254 バイトまでの文字ストリング リテラルのデータ タイプは CHAR になり 254 以上は VARCHAR になる create table tab1(c1 varchar2(10),c2 varchar2(10) for bit data) DB20000I SQL コマンドが正常に完了しました VARCHAR2 データ タイプタイプの互換性 =ON describe table tab1 DDL で VARCHAR2 が使用できる データ タイデータ 列の スケ 列名 プ スキーマタイプ名 長さ ール NULL C1 SYSIBM VARCHAR 10 0 はい C2 SYSIBM VARCHAR 10 0 はい 2 レコードが選択されました Varchar2 型でテーブルを作成すると Varchar として定義される IBM Corporation

12 非空白埋め比較セマンティクスと空白埋め比較セマンティクス 非空白埋め比較比較セマンティクス 以下の文字は異なるものとみなす 'A' 'A_' 'A ' ('_' は空白文字 ) VARCHAR2 データ タイプの互換性 =ON 空白埋め比較比較セマンティクス 空白文字をパティングして比較する 末尾の空白文字の違いは無視される 以下の文字は同じものとみなす 'A' 'A_' 'A ' ('_' は空白文字 ) VARCHAR2 データ タイプの互換性 =OFF IBM Corporation

13 PL/SQL サポート DB2 9.7 では新たに PL/SQL をサポート DB2 9.7 では PL/SQL 用と SQL PL 用の 2 つのコンパイラを持つ それぞれのコンパイラから作成されたモジュールは同じランタイムで稼動する 以下の Editionで使用可能 DB2 Enterprise Server Edition DB2 Workgroup Server Edition IBM Corporation

14 PL/SQL が記述される場所 PL/SQL ブロック (Anonymous Block) データベース外部に保持 PL/SQL ブロック declare begin end 実行 PL/SQL ストアード プログラム ( データベースのオブジェクトとしてデータベース内にロジックを格納 ) プロシージャー ファンクション パッケージ トリガー プロシージャー 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; IBM Corporation

15 DB2 と Oracle の PL/SQL 実行比較 CLPPlusとSQL*Plusで同じPL/SQL ブロックを実行 CLPPlus から DB2 に対して実行 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> SQL*Plus から Oracle に対して実行 Oracle Database 11g Enterprise Edition Release 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> IBM Corporation

16 PL/SQL を実行するには? DB2_COMPATIBILITY_VECTOR ( レジストリ変数 ) PL/SQLコンパイラの使用可能にする PL/SQLを実行するためには設定は必須 SET SQLCOMPAT PLSQL(CLPオプション ) CLPにPL/SQLの区切り文字 (/) を認識させるオプション IBM Corporation

17 パッケージ ( モジュール ) のサポート Oracle のパッケージに相当するオブジェクトとしてモジュールをサポート 関数 プロシージャープロシージャー タイプタイプ 変数変数などのなどのオブジェクトオブジェクトをまとめたオブジェクト PL/SQLのパッケージパッケージと同じ機能機能を提供 モジュール グローバル変数 SQL プロシージャー SQL ファンクション PL/SQL では CREATE PACKAGE 文によりモジュールを作成 SQL PL では CREATE MODULE 文によりモジュールを作成 IBM Corporation

18 モジュールの構成 モジュールは仕様部と本体で構成される 仕様部 オブジェクトの呼び出し方法の記述 グローバル変数やユーザー定義タイプ ( 配列 連想配列 カーソルタイプ ) の宣言も行う 本体 プロシージャーやファンクションを実装 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@ 仕様部本体 IBM Corporation

19 モジュールと PL/SQL PL/SQL でパッケージを作成するとモジュールとして登録される PL/SQL ではモジュールと同じ機能をパッケージで提供している SQL PL でモジュールを作成 CREATE MODULE 文で Module を作成後 ALTER MODULE 文で仕様部 本体を追加する 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 コマンドが正常に完了しました 仕様部 PL/SQL でパッケージ ( モジュール ) を作成 CREATE PACKAGE 文 CREATE PACKAGE BODY 文を使用して作成 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 コマンドが正常に完了しました 仕様部 本体 alter module mod1 publish procedure plus(in p1 int) create or replace package body pkg1 is DB20000I SQL コマンドが正常に完了しました procedure print is begin alter module mod1 publish function get() returns int dbms_output.put_line(var1); DB20000I SQL コマンドが正常に完了しました 本体 end; procedure plus(p1 in varchar2 default 1) is alter module mod1 add procedure print begin call begin dbms_output.put_line(var1); end var1 := var1 + p1; DB20000I SQL コマンドが正常に完了しました end; function get return int is alter module mod1 add procedure plus(in p1 int default 1) begin set var1 begin = var1 + p1; end return var1; DB20000I SQL コマンドが正常に完了しました end; end; alter module mod1 add function get() returns int return var1 DB20000I SQL コマンドが正常に完了しました DB20000I SQL コマンドが正常に完了しました IBM Corporation

20 モジュールに関するシステムカタログ 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 レコードが選択されました PL/SQL Package には P が入る SQL PL 作成された Module には M が入る 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 レコードが選択されました IBM Corporation

21 スカラー関数の機能強化 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 (*) サポートする関数の一覧 IBM Corporation

22 形式 型変換関連のスカラー関数の使用例 関数 フォーマットストリングを使用し フォーマットの変換を行いながら型変換を行える TO_NUMBER( 数値型への変換 ) TO_CHAR( 文字型への変換 ) TO_DATE( 日付型への変換 ) TO_TIMESTAMP( タイムスタンプへの変換 ) キャストとの違い フォーマットの変換を行いながら型変換を行う 例 '$123,456,789' ( 文字 ) ( 数値 ) ( 数値 ) '$123,456,789' ( 文字 ) '09/05/20' ( 文字 ) ( タイムスタンプ ) ( タイムスタンプ ) '09/05/20' ( 文字 ) TO_NUMBER 使用例 C: >db2 values to_number('$123,456,789','$999,999,999') TO_CHAR 使用例 C: >db2 values to_char( ,'$999,999,999') $ 123,456,789 1 レコードが選択されました 数値型に変換 1 レコードが選択されました 文字型に変換 IBM Corporation

23 ビルトイン パッケージ ライブラリー ( ビルトイン モジュール ) V9.7 ではアプリケーションアプリケーション開発開発に役立役立つビルトインビルトイン パッケージパッケージ ライブラリーを多数提供 パッケージ ( モジュール ) は 関数やプロシージャーや変数などをまとめたオブジェクトで SQL から呼び出すことが可能です (PL/SQL や SQL PL からも呼び出すことも可能 ) SQL のみでは出来ないような処理 ( 例 : ファイル操作 メッセージの出力など ) を行うことができます Oracleと同じ名前名前で同じ機能機能を持つビルトインビルトイン パッケージパッケージ ライブラリーを提供 Oracle の PL/SQL の開発では ビルトイン パッケージ ライブラリーが頻繁に利用されます Oracle の PL/SQL の移行の際に 使用されているビルトイン パッケージ ライブラリーが DB2 V9.7 でも提供されていれば書き直しなしでの移行も可能です IBM Corporation

24 ビルトイン パッケージ ライブラリー ( ビルトイン モジュール ) モジュール名 DBMS_ALERT DBMS_JOB DBMS_LOB DBMS_OUTPUT DBMS_PIPE DBMS_SQL DBMS_UTILITY UTL_DIR UTL_FILE UTL_MAIL UTL_SMTP 説明 DBMS_ALERT モジュールは アラートの登録 アラートの送受信を行うための一連のプロシージャーを備えています DBMS_JOB モジュールは ジョブの作成 スケジューリング および管理のためのプロシージャーを提供します DBMS_LOB モジュールは ラージ オブジェクトを操作する機能を提供します DBMS_OUTPUT モジュールは メッセージ バッファーにメッセージを書き込み ( 複数行のテキスト ) メッセージ バッファーからメッセージを取得する一連のプロシージャーを提供します これらのプロシージャーは メッセージを標準出力に書き込む必要のあるアプリケーション デバッグの際に役立ちます DBMS_PIPE モジュールは 同じデータベースに接続されたセッション内またはセッション間のパイプを通して メッセージを送信するための一連のルーチンを提供します DBMS_SQL モジュールは 動的 SQL を実行するための一連のプロシージャーを提供します したがって さまざまなデータ操作言語 (DML) ステートメントやデータ定義言語 (DDL) ステートメントをサポートします DBMS_UTILITY モジュールは さまざまなユーティリティー プログラムを提供します UTL_DIR モジュールは UTL_FILE モジュールで使用するディレクトリー別名を維持するための一連のルーチンを提供します UTL_FILE モジュールは データベース サーバーのファイル システム上のファイルとの間で読み取りおよび書き込みを行うための一連のルーチンを提供します UTL_MAIL モジュールは E メールを送信する機能を提供します UTL_SMTP モジュールは SMTP (Simple Mail Transfer Protocol) を介して E メールを送信する機能を提供します IBM Corporation

25 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' ) DBMS_OUTPUT パッケージの PUT プロシージャー PUT_LINE プロシージャーを呼び出して メッセージ バッファーへの書き込みを行います CLP でメッセージバッファーを読み出すための設定 リターン状況 = 0 P1 = 10 メッセージ バッファーから読み出されたデータ IBM Corporation

26 UTL_DIR UTL_FILE 使用例 UTL_DIR パッケージ call utl_dir.create_directory('mydir','c: work') リターン状況 = 0 ディレクトリを管理するためのプロシージャーや関数を提供 UTL_FILE パッケージ ファイルの読み取りや書き込みを行うプロシージャーや関数を提供 UTL_DIR を使用してディレクトリの準備 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 call utl_dir.get_directory_path('mydir',?) 出力パラメーターの値 パラメーター名 : PATH パラメーター値 : C: work リターン状況 = 0 DB20000I SQL コマンドが正常に完了しました!type myfile.csv A00,SPIFFY COMPUTER SERVICE DIV., B01,PLANNING, C01,INFORMATION CENTER, D11,MANUFACTURING SYSTEMS, 省略 CSV 形式でファイル出力 IBM Corporation

27 Weak Typing DB2 9.5 までは strict( 厳密な ) データタイピング トレンドは weak( 緩やかな ) タイピング (PERL, RUBY, PHP, ) DB2 9.7 での新しいしい振る舞い 数値と文字列間の暗黙的な変換 (cast): 割当て SET salary := 比較 WHERE salary > ビルトイン関数での利用 salary: ( 文字列連結の例 ) NULL やパラメーター マーカーの利用性向上 非型付きパラメーター マーカーと NULL を伴う関数呼び出し この振る舞いにより 明示的なキャストが不要になりステートメント量が少なくなります IBM Corporation

28 暗黙的な変換の例 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 record(s) selected. OK Numeric と String 間の暗黙的な変換 IBM Corporation

29 データ ディクショナリー互換ビュー Oracle のデータ ディクショナリの互換ビューを提供 以下のネーミングネーミング ルールルールを持つビュービューを作成 USER_* ( 例 USER_TABLES) ユーザーのオブジェクトを表示 ALL_* ( 例 ALL_TABLES) ユーザーがアクセス可能なオブジェクトを表示 DBA_* ( 例 DBA_TABLES) 全てのオブジェクトを表示 Oracleと同様同様にそれぞれのにそれぞれのビュービューには Public Aliasが作成作成されているためされているため スキーマ指定指定なしでもなしでも呼び出し可能 (*) 作成される互換ビューの一覧 IBM Corporation

30 データディクショナリー互換ビュー使用例 データディクショナリーの 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 INDEX_TYPE TABLE_NAME SQL XRGN CUSTOMER SQL XVIP CUSTOMER CUST_PHONET_XMLIDX XVIL CUSTOMER SQL XVIP CUSTOMER CUST_PHONES_XMLIDX XVIL 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 IBM Corporation

31 並行性制御に関するこれまでの機能強化 並行性を向上向上させる 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 でブロッキングのスキャンはロックウェイトしない ケース 1 : WITH RS USE AND KEEP EXCLUSIVE LOCKS の X ロック ケース 2 : どの列を変更しなかった UPDATE の X ロック ケース 3 : 更新された列以外で構成された索引 ONLY スキャン 詳細は TechnicalFlash: DB2 for LUW V9.5 の Lock Avoidance 機能によって V9.1 までは発生していたロック競合が発生しなくなることがある (DM ) を参照 IBM Corporation

32 DB2 9.7 での機能強化 CS 分離レベルレベルの機能拡張新たなたな読み取り一貫性 :Currently Committedの導入 読み取り一貫性の比較 Currently Committed の挙動 Currently Committed を有効に使うための設定 考慮点 参考 : 分離レベル ISO 分離レベル 分離レベルレベルを定義定義するする現象 (3 種 ) 対応する DB2の分離分離レベル 対応する Oracleの分離分離レベル SERIALIZABLE 幻像読取りなし反復不能読取りなし未コミットコミット読取読取りなし Repeatable Read ( 反復可能読取り ) SERIALIZABLE REPEATABLE READ 反復不能読取りなし未コミットコミット読取読取りなし Read Stability ( 読取り固定 ) なし READ COMMITTED 未コミットコミット読取読取りなし Cursor Stability ( デフォルト ) ( カーソル固定 ) READ COMMITTED ( デフォルト ) READ UNCOMMITTED Uncommitted Read ( 非コミットコミット読取読取り ) なし IBM Corporation

33 DB2 (V9.5 以前 ) の読み取り一貫性 DB2は最新最新のデータデータを読む 参照処理は 更新処理がコミットされるまで待つ A TRN1 B TRN2 UPDATE UPDATE COMMIT C C D D E TRN2 は TRN1 が COMMIT するまで WAIT SELECT F 読むデータデータは常に最新最新だがだが 参照処理参照処理がロックロック待機待機するする可能性可能性あり IBM Corporation

34 Oracle の読み取り一貫性 (Read Committed) マルチ バージョンバージョン一貫性制御 (Multi-Version Concurrency Control) 更新処理により 更新前イメージが専用の領域へ書き出される 参照処理はコミットを待たずに更新前イメージを読む Select 発行時点でコミット済みのデータを読む 下記の例では UPDATEがCOMMITされていたとしてもTRN2は更新前データを読む TRN1 SCN=10 A B UNDO セグメント TRN2 SCN=100 UPDATE UPDATE SCN=110 C C D D C D SCN=29 SELECT SCN=98 E F 読んだんだデータデータが最新最新とはとは限らないがらないが 参照処理参照処理はロックロック待機待機しない IBM Corporation

35 DB2 9.7 の読み取り一貫性 (Currently Committed) 未コミットコミットの更新更新があってもがあってもロックロック待機待機をしない 参照処理は 更新処理に伴うロックの開放を待たず 更新前のデータ ( コミット済みの最新データ ) をログ ( ログバッファー ) から読む 常に最新のコミット済みデータを読む TRN1 UPDATE UPDATE A B C C D D E トランザクションログ ( ログバッファー ) C D TRN2 SELECT F 参照処理はロックロック待機待機せずせず かつかつコミットコミット済みのみの最新最新データデータを読む IBM Corporation

36 同時実行性の向上 : Currently Committed 別アプリアプリからから参照可能参照可能か 別アプリアプリからから更新可能更新可能か DB2 9.5 以前の 参照中の行を Yes Maybe 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 行をバイパス ) IBM Corporation

37 Currently Committed の設定方法 データベース構成パラメーター (CUR_COMMIT) ON : 分離レベル CS は Currently Committed として動く ( 新規作成 DB のデフォルト ) AVAILABLE : アプリケーションから明示的に指定された場合に Currently Committed 有効 DISABLED :Currently Committed 無効 (Migration された DB でのデフォルト ) パッケージ作成時の指定 (BIND コマンド ) CONCURRENTACCESSRESOLUTION オプションで指定 動的 SQL であっても 使用するパッケージを明示的に BIND することで制御可能 セッション単位の指定 JDBC CLI.NET 等のアプリ インターフェースからセッション単位で指定可能 CUR_COMMIT DB 構成パラメーター BIND オプションセッションで指定 CC 設定の原則 スコープが狭い設定パラメータがより優先される アクセスプラン決定時の設定がセクションに書き込まれ 有効になる 静的 SQL では注意 IBM Corporation

38 並行性の向上 Currently Committed まとめ Currently Committedの機能機能によりにより 最新最新の情報情報を提供提供しながらもアプリケーションアプリケーションの並行性並行性が向上 未コミットの INSERT 行はスキップ 未コミットの UPDATE 行は更新前データを戻す 未コミットの DELETE 行は 参照対象 ( 未だ DELETE されていない状態が最新の状態であるため ) 既存のログログをベースベースにしたにした仕組仕組みにみにり 更新更新と参照参照が競合競合した場合にのみにのみ ログログへのへのアクセスアクセスが発生発生するする オーバーヘッドオーバーヘッドが少なくなく パフォーマンスパフォーマンスに優れている DB 構成パラメータ :CUR_COMMIT BIND オプションで設定可能 有効 / 無効は EXPLAINで確認 IBM Corporation

39 Oracle : FOR UPDATE 航空券の予約アプリケーションを作成することを考えて見ます 以下の手順で予約を実施することになります 1) 空席の検索 2) 空席の確認 3) 予約 ロック制御をどのように行うべきでしょうか? Oracle trx1 (1)SELECT FOR UPDATE 空席を検索 23F 席が最後の 1 席 23F の行に TX ロックを取得 (3)UPDATE して COMMIT trx2 (2)SELECT FOR UPDATE 空席を検索 23F の行に TX ロックを取得しようとするがロック待機となる (4)SELECT FOR UPDATE 文のロック待機が解除され 空席なし の結果となる IBM Corporation

40 DB2 : FOR UPDATE 航空券の予約アプリケーションを作成することを考えて見ます 以下の手順で予約を実施することになります 1) 空席の検索 2) 空席の確認 3) 予約 ロック制御をどのように行うべきでしょうか? DB2 trx1 trx2 (1)SELECT FOR UPDATE WITH RS 空席の検索 23F 席が最後の 1 席 23F の行に U ロックを取得 (2)SELECT FOR UPDATE WITH RS 空席検索 23F の行に U ロックを取得しようとするが ロック待機となる (3)UPDATE して COMMIT (4)SELECT FOR UPDATE 文のロック待ちが解除され 空席なし の結果となる IBM Corporation

41 DB2 と Oracle での FOR UPDAET の違い Oracle DB2 SELECT FOR UPDATE を実行した時に, それぞれの行に排他ロックが取得される コミットされるまでされるまで排他排他ロックロックを取得 SELECT FOR UPDATE を実行した時に, それぞれの行に更新ロックが取得される 以下のタイミングまで更新ロックを取得 次の行がフェッチフェッチされるまで ( 分離レベル CS での読み取り ) トランザクションがコミットコミットされるまで ( 分離レベル RS での読み取り ) DB2でUPDATE 前にSELECT する場合場合には, 以下のことにのことに注意 デッドロックを避けるために FOR UPDATE を使用して排他制御を行う ロックをCOMMITするまで保持するように分離レベルRSを指定する IBM Corporation

42 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 もとのテーブルすべてに取得ため アプリケーションの同時並行性に考慮が必要 IBM Corporation

43 ロック待ちの違い DB2 セッション単位でロックタイムアウトを設定可能 Oracle SET LOCK TIMEOUT WAIT タイムアウトしないで待ち続ける SET LOCK TIMEOUT NOT WAIT 待たずにタイムアウトエラー (SQL0911) を返す "db2set DB2LOCK_TO_RB=STATEMENT" を設定すればステートメントレベルのロールバックにできる SET LOCK TIMEOUT integer 指定された秒数待ち タイムアウトする Oracle では以下のような設定で ステートメントレベルに設定できる select * from emp where EMPNO=7369 for update nowait; IBM Corporation

44 Let s go to Lab3!! IBM Corporation