Comments
Description
Transcript
Oracle DB2 2013年1月
OracleからDB2 10への移行
－ DB2の機能、ツール、および移行ポイント －
2013年1月
日本アイ・ビー・エム株式会社
本資料掲載事項は、ある特定の環境・使用状況においての正確性がIBMによって確認されていますが、すべての環境において同様の結果が得られる保証は
ありません。これらの技術を自身の環境に適用する際には、自己の責任において十分な検証と確認を実施いただくことをお奨めいたします。
1
© 2013 IBM Corporation
内容
DB2とOracleの用語
Oracleからの移行を容易にするDB2の機能
移行ロードマップ
移行ツール
Oracleからの移行ポイント
※当資料はDB2 V10.1 for Linux, UNIX, and Windows Fix Pack 1の環境に基づいています。
2
© 2013 IBM Corporation
DB2とOracleの用語
3
© 2013 IBM Corporation
DB2とOracleの用語
設計
4
Oracle
DB2
インスタンス
インスタンスまたはデータベース・マネージャー
オンラインREDOログ
アクティブ・ログ
アーカイブ・ログ
アーカイブ・ログ
非アーカイブ・ログ・モード
循環ロギング
データ・ファイル
コンテナー
SYSTEM 表スペース
SYSCATSPACE 表スペース
表スペース
表スペース
エクステント
エクステント
データ・ブロック
データ・ページ
セグメント
ストレージ・オブジェクト
パーティション表
パーティション表
マテリアライズ・ビュー
マテリアライズ照会表 (MQT)
グローバル索引
非パーティション索引
ローカル索引
パーティション索引
データ・ディクショナリー
システム・カタログ
© 2013 IBM Corporation
DB2とOracleの用語
設定/メモリー
アプリ使用機能
運用
5
Oracle
DB2
ORACLE_SID 環境変数
DB2INSTANCE 環境変数
init.ora および Server Parameter File (SPFILE)
データベース・マネージャー構成ファイルおよびデータベース構成ファイル
System Global Area (SGA)
インスタンス共有メモリーおよびデータベース共有メモリー
Program Global Area (PGA)
アプリケーション共有メモリーおよびエージェント専用メモリー
User Global Area (UGA)
アプリケーション・グローバル・メモリー
データ・バッファー・キャッシュ
バッファー・プール
ラージ・プール
ユーティリティー・ヒープ
ライブラリー・キャッシュ
パッケージ・キャッシュ
データ・ディクショナリー・キャッシュ
カタログ・キャッシュ
グローバル一時表
グローバル一時表
カーソル共有
ステートメント・コンセントレーター
データベース・リンク
ニックネーム
DUAL 表
DUAL 表
Oracle Call Interface (OCI)
コール・レベル・インターフェース (CLI)
セッション
セッション、データベース接続
バインド変数
パラメータ・マーカー
動的なパフォーマンス・ビュー
スナップショット・モニター SQL 管理ビュー
アラート・ログ
管理通知ログ
bdump ディレクトリー
診断ログ (db2diag.log)
startup nomount
db2start
© 2013 IBM Corporation
Oracleからの移行を容易にするDB2の機能
6
© 2013 IBM Corporation
移行における課題
Transact-SQL
Oracle の
SQL および
PL/SQL
■
DB2 の SQL/PL
(SQL/PSM に基づく)
アプリケーションが、選択した RDBMS に特化している
– 異なる RDBMS へ移行するにはコードの大幅な書き直しが必要
– 既存スキルを活用できない
DB2は、これらの問題を解決するための機能を提供しており、その拡張を継続的に行っている
7
© 2013 IBM Corporation
Oracleの各機能に対応するDB2の機能
Oracle
– データ・タイプ
– データ・ディクショナリー
– SQL
• 基本的なDDL
• データベース・リンク
• マテリアライズド・ビュー
• …
– 同時実行制御
– PL/SQL言語
– PL/SQLパッケージ
– JDBC
– Pro*C
– OCI
– C言語のAPI
– Oracle Forms
– SQL*Plus
– SQL*Loader
– RAC
–…
DB2
– 互換フィーチャー
– 互換フィーチャー
– SQL
• ツール
• Federation(フェデレーション)
• マテリアライズ照会表、CDC※
• …
– 同時実行制御(Currently Committed)
– 互換フィーチャー
– 互換フィーチャー
– JDBC
– 組み込みSQL(互換フィーチャー付)
– DB2CI(Oracle互換)
– CLIがDB2本来のC言語用APIだが、
DB2CIもOracle互換APIとして提供
– パートナー製品の使用(Java Forms)
– CLPPlus
– IMPORT, LOAD, INGESTユーティリティ
– DB2 pureScale
–…
※ InfoSphere Change Data Capture
8
© 2013 IBM Corporation
互換フィーチャーの設定: DB2_COMPATIBILITY_VECTOR レジストリ変数
この変数をセットすることで、他DBとの互換フィーチャーを有効にする
db2set コマンドでセット
ビットごとに互換フィーチャーが定義
– 例:
• 1ビット目: ROWNUM疑似列
• 2ビット目: DUAL表
有効化するビットを1にして16進数(HEX)で表す、または予約ワードをセット
– 予約ワード
• ORA
Oracle互換フィーチャーを最大限に活用
• SYB
Sybase互換フィーチャーを最大限に活用
• MYS
MySQL互換フィーチャーを最大限に活用
–例
• db2set DB2_COMPATIBILITY_VECTOR=FFFF
• db2set DB2_COMPATIBILITY_VECTOR=ORA
(ORAは20FFFと同じ)
設定を変えたらDB2インスタンスの再起動を行う
$ db2stop
$ db2start
9
© 2013 IBM Corporation
DB2_COMPATIBILITY_VECTORで設定可能な互換フィーチャー
ビット位置
互換性フィーチャー
1 (0x01)
2 (0x02)
3 (0x04)
4 (0x08)
5 (0x10)
6 (0x20)
7 (0x40)
8 (0x80)
ROWNUM
DUAL
外部結合演算子
階層照会
NUMBER ※
VARCHAR2 ※
DATE ※
TRUNCATE TABLE
9 (0x100)
文字リテラル
10 (0x200)
11 (0x400)
12 (0x800)
13 (0x1000)
14 (0x2000)
17 (0x10000)
18 (0x20000)
コレクションのメソッド
コメント
ROW_NUMBER() OVER() の同義語
DUAL ダミー表を使用可能化
外部結合演算子 (「+」記号) のサポートを使用可能化。
CONNECT BY 節を使用した階層照会のサポート。
NUMBER データ・タイプおよび関連した数値処理のサポート
VARCHAR2 型と NVARCHAR2 データ・タイプおよび関連した文字ストリング処理
DATE データ・タイプを TIMESTAMP(0) データ・タイプとして変換処理可能
TRUNCATE ステートメントの代替セマンティック
CHAR データ・タイプまたは GRAPHIC データ・タイプ (VARCHAR データ・タイプまたは
VARGRAPHIC データ・タイプの代替)
配列での演算を実行するためのメソッド (first、last、next、およびprevious など) を
使用可能化。
配列内の特定のエレメントを参照するときに大括弧の代わりに小括弧を使用可能化。
例: 「array1(i)」は「array1」のエレメント「i」を参照する。
データ・ディクショナリー互換ビュー ※ データ・ディクショナリー互換ビューはデータベースの作成時に作成
PL/SQL コンパイル
PL/SQL ステートメントのコンパイルおよび実行の可能化
インセンシティブ・カーソル
SELECT ステートメントが明示的に FOR UPDATE を指定しない場合、WITH RETURN
で定義されたカーソルがインセンシティブになる。
INOUT パラメーター
INOUT パラメーター宣言に対する DEFAULT の指定
SQL データ・アクセス・レベルの強制 実行時にルーチンが SQL データ・アクセス・レベルを強制可能。
Oracleデータベース・リンク構文
Oracleデータベース・リンク構文を有効にする
※ データベース作成前に設定必要
参照URL:
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html
10
© 2013 IBM Corporation
互換フィーチャー例
以下の互換フィーチャーを有効にする
– ビット5 (0x10): Oracle NUMBER互換
– ビット6 (0x20): Oracle VARCHAR2互換
– ビット7 (0x40): Oracle DATE互換
– ビット11(0x400): Oracleデータ・ディクショナリー互換ビュー
0x10 + 0x20 + 0x40 + 0x400 = 0x470
設定はDB2インスタンスを再起動して有効になる。その後、データベースを作成する
$ db2set DB2_COMPATIBILITY_VECTOR=470
$ db2stop
$ db2start
$ db2sample -name oradb ← サンプルDBを作成
データ・タイプ互換の確認
– データ・タイプ互換の場合、DB構成情報で確認可能
$ db2 get db cfg for oradb
…
NUMBER データ・タイプの互換性
VARCHAR2 データ・タイプの互換性
データ・タイプ DATE の TIMESTAMP(0) への互換性
…
11
= ON
= ON
= ON
© 2013 IBM Corporation
Oracleデータ・ディクショナリー互換ビューの確認
Oracleデータ・ディクショナリー互換ビューの例
– 接続ユーザーが作成した表情報の取得
select * from user_tables
– 接続ユーザーが作成したビュー情報の取得
select * from user_views
参照URL
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.porting.doc/doc/c0054440.html
DB2が提供しているカタログ・ビューも引き続き使用可能
– 接続ユーザーが作成した表名(スキーマ名.表名)の取得
select tabschema, tabname from syscat.tables where type='T' and
owner=current user
– 接続ユーザーが作成したビュー名とビュー定義の取得
select viewschema, viewname, text from syscat.views where
owner=current user
参照URL
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0011297.html
12
© 2013 IBM Corporation
互換フィーチャーの追加設定
互換フィーチャーの追加設定前
Oracle特有の
外部結合演算子
$ db2 -td/
db2 => select lastname,deptname from employee e, department d where e.workdept(+)=d.deptno/
SQL0104N "(+" に続いて予期しないトークン ")"
が見つかりました。予期されたトークンに "(" が含まれている可能性があります。
SQLSTATE=42601
db2 => set serveroutput on/
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。
db2 => declare
i number;
PL/SQL
begin
i := 10;
dbms_output.put_line('i=' || i);
end;
/
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL0104N "BEGIN-OF-STATEMENT" に続いて予期しないトークン "declare i number;
begin i := 10;" が見つかりました。予期されたトークンに "<values>"
が含まれている可能性があります。 LINE NUMBER=1. SQLSTATE=42601
13
© 2013 IBM Corporation
互換フィーチャーの追加設定
すべての互換フィーチャーをオンに設定変更して、既存のデータベースにアクセス
$ db2set DB2_COMPATIBILITY_VECTOR=ORA
$ db2stop
$ db2start
$ db2 connect to oradb
$ db2 -td/
Oracle特有の
外部結合演算子
すべての互換フィーチャーをオンに設定した後の動作
db2 => select lastname,deptname from employee e, department d where e.workdept(+)=d.deptno/
LASTNAME
DEPTNAME
--------------- -----------------------------------HAAS
SPIFFY COMPUTER SERVICE DIV.
…
BRANCH OFFICE I2
BRANCH OFFICE J2
48 レコードが選択されました。
これは右外部結合。DB2本来の書き方は次のようになる（SQL92の規約に基づく）
db2 => select lastname,deptname from employee e right outer join department
d on e.workdept=d.deptno/
14
© 2013 IBM Corporation
互換フィーチャーの追加設定
すべての互換フィーチャーをオンに設定した後の動作
db2 => set serveroutput on/
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。
db2 => declare
i number;
begin
i := 10;
dbms_output.put_line('i=' || i);
end;
/
DB20000I SQL コマンドが正常に完了しました。
PL/SQL
(Oracle互換フィーチャー)
i=10
db2 => begin
declare i integer;
set i = 10;
call dbms_output.put_line('i=' || i);
end
/
DB20000I SQL コマンドが正常に完了しました。
SQL PL (DB2本来の機能も引き続き利用可能)
i=10
15
© 2013 IBM Corporation
PL/SQL互換フィーチャー
ネイティブ PL/SQL サポート: DB2 エンジンに PL/SQL コンパイラーも同梱
– エミュレーションなし（PL/SQLとSQL PLを同じレベルで実行可能）
– ソース・レベルのデバッグおよびプロファイル
エディター
PL/SQLもSQL PLも
アーキテクチャーは同じ
Data Studio
PL/SQL
コンパイラー
SQL PL
コンパイラー
デバッガー
DB2 サーバー
SURE
(SQL Unified Runtime Engine)
プロファイラー
データベース
16
© 2013 IBM Corporation
DB2 10 の組み込みパッケージ
フィーチャー
説明
DBMS_ALERT
クロス・セッション・セマフォー
DBMS_DDL
DDL オブジェクトの難読化
DBMS_JOB
ジョブ・スケジューラー、管理のプロシージャー
DBMS_LOB
LOB を操作する機能
DBMS_OUTPUT
「画面出力によるデバッグ」および簡単なレポート作成
DBMS_PIPE
クロス・セッション・データ・パイプ
DBMS_SQL
動的 SQL を実行するためのプロシージャー・セット
DBMS_UTILITY
さまざまな関数およびプロシージャー
MONREPORT
データのモニターとテキスト・レポートの生成
UTL_DIR
ディレクトリーの別名を維持するためのルーチン
UTL_FILE
サーバー・サイドの I/O API
UTL_MAIL
E メール送信機能(SMTPはDB構成情報定義を利用）
UTL_SMTP
SMTP を指定したEメール送信機能
参照URL
• http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0053670.html
17
© 2013 IBM Corporation
NUMBERデータ・タイプのサポート
NUMBERデータ・タイプは以下のように変換される
Oracle
DB2
数値のタイプ
NUMBERのみ指定
DECFLOAT(16) ※
浮動小数点
NUMBER(最大桁数)
DECIMAL(最大桁数)
整数
NUMBER(最大桁数, 小数点以下桁数)
DECIMAL(最大桁数, 小数点以下桁数)
固定小数点
※ 値が16桁を超える場合、DECFLOAT(34) へ変更する
例
db2 => create table t1 (n1 number, n2 number(10), n3 number(20, 5));
db2 => describe table t1;
データ・タイ
列名
プ・スキーマ
------------------------------- --------N1
SYSIBM
N2
SYSIBM
N3
SYSIBM
データ・
列の
スケ
タイプ名
長さ
ール
------------------- ---------- ----DECFLOAT
8
0
DECIMAL
10
0
DECIMAL
20
5
NULL
-----はい
はい
はい
db2 => insert into t1 values (10, 100, 1000.11), (200, 2000, 20000.22222);
db2 => select * from t1;
N1
N2
N3
------------------------ ------------ ---------------------10
100.
1000.11000
200
2000.
20000.22222
18
© 2013 IBM Corporation
端数の丸め込み方法
DECFLT_ROUNDING データベース構成パラメーターで設定
DB2の場合、デフォルトは ROUND_HALF_EVEN
– 最も近い値に丸める
– 正確に等間隔の場合、末尾が偶数になるように丸める
– 例:
db2 => create table t2 (n1 number, n2 number(5,2));
db2 => insert into t2 values (1234567890.1234565, 123.455);
N1
N2
------------------------ ------1234567890.123456 123.46
1が付くと切り上げの方が近くなる
db2 => delete from t2;
db2 => insert into t2 values (1234567890.12345651, 123.465);
N1
N2
------------------------ ------1234567890.123457 123.46
Oracleの場合、四捨五入 (ROUND_HALF_UP)
– Oracleと同じ、四捨五入にする場合
update db cfg for [DB名] using DECFLT_ROUNDING ROUND_HALF_UP
参照URL
– http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.config.doc/doc/r0052298.html
19
© 2013 IBM Corporation
振る舞いが変わる主な機能 (VARCHAR2)
VARCHAR データ・タイプに自動変換
この互換フィーチャーをオンにすると、VARCHARデータ・タイプの振る舞いが変わる
20
VARCHAR2互換なし
VARCHAR2互換
空文字('')とNULLの区別
区別する
区別なし
(空文字はNULLに変換される)
非NULL制約
空文字の挿入可
空文字を挿入できない
文字列の比較
文字列末尾の空白の違いは
無視して比較
文字列末尾の空白が違うものは
違う文字として扱う
© 2013 IBM Corporation
振る舞いが変わる主な機能 (VARCHAR2)
VARCHAR2としてもよい
（VARCHARに自動変換)
例
VARCHAR2互換なし
create table t3 (c1 int,
c2 varchar(10) not null);
insert into t3 values (1, 'hello');
insert into t3 values (2, 'hello ');
create table t3 (c1 int,
c2 varchar(10) not null);
insert into t3 values (1, 'hello');
insert into t3 values (2, 'hello ');
select * from t3 where c2='hello';
C1
C2
----------- ---------- 文字列末尾の空白文字
の違いを無視
1 hello
2 hello
select * from t3 where c2='hello';
C1
C2
----------- ---------1 hello
insert into t3 values (3, '');
DB20000I SQL コマンドが正常に完了しました。
insert into t3 values (3, '');
SQL0407N NULL 値の NOT NULL 列
"TBSPACEID=3, TABLEID=7, COLNO=1"
への代入は許されていません。
SQLSTATE=23502
select * from t3;
C1
C2
----------- ---------1 hello
2 hello
3
21
VARCHAR2互換
非NULL制約でも空文字の挿入可
空文字はNULLに変換される
© 2013 IBM Corporation
振る舞いが変わる主な機能 (DATE)
OracleのDATE型は時間を保持（時分秒）
DB2のDATE型は時間を保持しない
この互換フィーチャーをオンにすると、以下のような振る舞いとなる
DATE互換なし
DATE互換
DATE型の列
DATE型のまま
TIMESTAMP(0)に変換
CURRENT DATE
特殊レジスター
DATE値を戻す
TIMESTAMP(0)値を戻す
日付の演算
+ 1 day などと記述（1日加算)
+ 1 で1日加算
- 2/24 で2時間減算
+ 3/24/60 で3分加算
以下の表記はDATE型ではエラーと
なる（TIMESTAMP型で使用可能)
- 2 hour （2時間減算）
+ 3 minute （3分加算）
22
© 2013 IBM Corporation
振る舞いが変わる主な機能 (DATE)
例: DATE互換なし
create table t4 (c1 int, c2 date);
select colname,typename,scale from syscat.columns where tabname='T4';
COLNAME TYPENAME
SCALE
-------- ----------- ----C1
INTEGER
0
C2
DATE
0
insert into t4 values (1, '2012-08-25');
insert into t4 values (2, '2012-09-05-10.20.30');
select * from t4;
C1
C2
時間は削除(無視)されて挿入される
----------- ---------1 2012-08-25
2 2012-09-05
select to_char(c2,'YYYY/MM/DD HH24:MI:SS') from t4;
1
---------------------2012/08/25 00:00:00
2012/09/05 00:00:00
select current date - 5 day from sysibm.sysdummy1;
1
---------2012-08-30
23
© 2013 IBM Corporation
振る舞いが変わる主な機能 (DATE)
例: DATE互換
create table t4 (c1 int, c2 date);
select colname,typename,scale from syscat.columns where tabname='T4';
COLNAME TYPENAME
SCALE
-------- ----------- ----C1
INTEGER
0
C2
TIMESTAMP
0
insert into t4 values (1, '2012-08-25');
insert into t4 values (2, '2012-09-05-10.20.30');
select * from t4;
C1
C2
----------- ------------------1 2012-08-25-00.00.00
2 2012-09-05-10.20.30
select to_char(c2,'YYYY/MM/DD HH24:MI:SS') from t4;
1
---------------------2012/08/25 00:00:00
2012/09/05 10:20:30
select current date - 3 + 14/24 from dual;
1
------------------2012-09-02-01.58.45
24
© 2013 IBM Corporation
同時実行制御
分離レベル
ISO分離レベル
DB2
Oracle
SERIALIZABLE
REPEATABLE READ (RR)
SERIALIZABLE
REPEATABLE READ
READ STABILITY (RS)
なし
READ COMMITTED
CURSOR STABILITY (CS)
• Currently Committed機能
(CUR_COMMIT=ON|OFF)
READ COMMITTED
READ UNCOMMITTED
UNCOMMITTED READ (UR)
なし
(*) 青字がデフォルト
DB2の分離レベル CS では、Currently Committed の設定が可能
– DB構成パラメータ CUR_COMMIT で設定
– デフォルトは ON
db2 get db cfg for [DB名]
…
CS 分離レベルで現在コミット済みの結果を戻す (CUR_COMMIT) = ON
…
25
© 2013 IBM Corporation
同時実行制御
Oracle の READ COMMITTED
– データの読み取り
• SQL文発行時点のコミット済みデータを取得
UNDOセグメントを使って実現（ログとUNDOセグメントの両方に書き込み）
• 別のトランザクションがデータを更新中でも、それがコミットされるのを待たない
• SQL文発行後にコミットされたデータは読まない
SCN = システム変更番号
SCN=32
TRN1
UPDATE
UNDOセグメント
B
SCN=102
UPDATE
COMMIT
A
SCN=64
C
C’
C
D
D’
D
SCN=76
SCN=100
TRN2
SELECT
E
F
読んだデータは最新とは限らないが、参照処理のロック待機は発生しない
UNDOセグメントを使って実現（ログとUNDOセグメントの両方に書き込み）
26
© 2013 IBM Corporation
同時実行制御
DB2 の Currently Committed
– データを読み取る際、最新のコミット済みデータを取得し、別のトランザクションが
データを更新中でも、それがコミットされるのを待たない
• ログのみを使って実現
A
TRN1
UPDATE
B
トランザクションログ
（ログバッファー）
C
C’
C
UPDATE
D
D’
D
TRN2
SELECT
E
F
読むデータは常に最新で、参照処理のロック待機も発生しない
ログのみを使って実現
27
© 2013 IBM Corporation
データベース・リンク、マテリアライズド・ビューの移行
Oracleの代表的なDB連携
– データベース・リンク
• リアルタイムの外部Oracleへの仮想アクセス
Oracle
Oracle
DBLINK
SQL
Oracle
Oracle
– マテリアライズド・ビュー
• トリガー・ベースのテーブルの差分レプリケーション
Oracle
ﾏﾃﾘｱﾗｲｽﾞﾄﾞ
･ﾋﾞｭｰ
SQL
28
Oracle
差分トリガー
© 2013 IBM Corporation
データベース・リンク、マテリアライズド・ビューの移行
フェデレーションとInfoSphere Change Data Captureが解決！
– フェデレーション
• リアルタイムに外部の多様なDBへの仮想アクセス
DB2
Oracle
フェデレーション
DB2
SQL
SQL Server
さまざまな
データソースを
サポート
（含非RDBデータ)
– InfoSphere Change Data Capture
• ログベースのテーブルの差分レプリケーション
DB2
Oracle
CDC
ログベースで
高速、軽量
CDC
SQL
ログ
29
© 2013 IBM Corporation
フェデレーション機能によるニックネーム・アクセス
ニックネームは実体をもたない仮想的な表
リモートの表やビュー、ストアードプロシージャの仮想オブジェクト
–
ローカル・オブジェクトのように操作
リアルタイムの参照・更新が可能
マルチソースのJoinが可能
多種多様なデータソースを
表のイメージでアクセス
ユーザー・アプリケーション
SELECT *
FROM ORAEMP
WHERE -------DB2 10.1 Fix Pack 1で以下の構文もサポート
(Oracle互換フィーチャー:0x20000)
SELECT *
FROM SCOTT.EMP@ORACLE
WHERE --------
30
DB2
ニックネーム
DB2ADMIN.ORAEMP
Oracle
ソース表
ORACLE.SCOTT.EMP
CREATE NICKNAME ORAEMP
FOR ORACLE. SCOTT.EMP
© 2013 IBM Corporation
IBM InfoSphere Change Data Capture
Point
①
高速、軽量、低負荷な差分レプリケーション
– データベースのログから差分データを抽出
幅広いDB、プラットフォームのサポート
– Oracle, DB2 for LUW, iSereis, z/OS, SQL Server, Sybase, Teradata, Netezza,
MQ, JMS, BEAなど
Point
②
Point
簡単な操作
③
– nonプログラミング。GUIによる容易な定義や操作
モニタリング機能と構成
データベース
Information
Server
CDC
DB2, Oracle,
Microsoft SQL
Server等
31
データベース
ログ
ソース側エンジン
TCP/IP
CDC
ターゲット側エンジン
DataStage
メッセージ・
キュー
フラット・
ファイル
© 2013 IBM Corporation
SQL*Loaderの移行
データを取り込むためのツール
DB2では、従来から、LOAD, IMPORTユーティリティを提供
– LOAD
• データベース・コンテナー（ストレージ）に、フォーマット済みページを直接書き
込む（処理が高速なので大量のデータ挿入に向いている）
• 参照整合性制約等のチェックは、別途、SET INTEGRITYコマンドを発行する必要が
ある（それまでロードした表にアクセスできない）
• トリガーは起動しない、等の制約がある
– IMPORT
• INSERT文で実現
• 尐量のデータ挿入に向いている
– 参照URL
• http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.dm.doc/doc/r0004639.html
DB2 10 から、さらに、INGESTユーティリティを提供
– INGESTコマンド処理中も、アプリケーションからのデータ・アクセスが可能
– INSERT/REPLACEの他に、UPDATE/DELETE/MERGEが可能（簡易ETL処理が可能）
– 再開可能なエラー発生時、最後にコミットした時点から再開が可能
– 継続的なデータ挿入や、簡単なETL処理を行いたい場合に向いている
– 参照URL
• http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.dm.doc/doc/c0057237.html
32
© 2013 IBM Corporation
OracleのSQL*LoaderとDB2のLOAD
• SQL*Loader のコントロール・ファイル
• 固定長フォーマットをロードするコントロールファイルの例
• 固定長フォーマットの場合、POSITION() 指定を METHOD Lを用いた
指定に変更する
Oracle SQL*Loader コントロールファイル
LOAD DATA
INFILE '/home/ora_usr/accounts.dat'
INTO TABLE accounts
( acct_id POSITION(0001:0003) NUMBER
,dept_code POSITION(0004:0006) CHAR
,acct_desc POSITION(0009:0100) VARCHAR2
,max_employees POSITION(0101:0103) NUMBER
,current_employees POSITION(0104:0106) NUMBER
,num_projects POSITION(0107:0107) NUMBER )
33
DB2 Load コマンド
LOAD FROM
'/home/ora_usr/accounts.dat'
OF ASC
METHOD L
( 0001 0003
,0004 0006
,0009 0100
,0101 0103
,0104 0106 )
INSERT INTO accounts
( acct_id
,dept_code
,acct_desc
,max_employees
,current_employees );
© 2013 IBM Corporation
OracleのSQL*LoaderとDB2のLOAD
• 可変長データの場合のコントロールファイル
• 可変長データの場合のコマンド比較
ロードするデータファイル (accounts.dat)
101,"ACT","Major Bank Co.",30,11,4
301,"ACT","Large Telco Inc.",30,0,4
101,"IT","Huge Software Co.",50,0,4
203,"MKT","Basic Insurance Co.",15,0,3
Oracle SQL*Loader コントロールファイル
INFILE '/home/ora_usr/accounts.dat'
INTO TABLE accounts
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED
BY '"'
( acct_id
,dept_code
,acct_desc
,max_employees
,current_employees
,num_projects )
34
DB2 Load コマンド
LOAD FROM
'/home/ora_usr/accounts.dat'
OF DEL
MODIFIED BY CHARDEL"" COLDEL,
METHOD P (1, 2, 3, 4, 5 )
INSERT INTO accounts
( acct_id
,dept_code
,acct_desc
,max_employees
,current_employees );
© 2013 IBM Corporation
INGESTユーティリティ
シンプルなETL (Extract, Transform, Load) が実現可能
– 例1: 基本的なシンタックス
INGEST FROM FILE my_file.txt FORMAT DELIMITED INSERT INTO my_table;
– 例2: 固定長データの取り込み
INGEST FROM FILE my_file.txt FORMAT POSITIONAL(
$field1 POSITION(1:8) INTEGER EXTERNAL,
$field2 POSITION(10:19) DATE ’yyyy-mm-dd’,
$field3 POSITION(25:34) CHAR(10))
INSERT INTO my_tableVALUES($field1, $field2, $field3);
– 例3: 取り込んだデータを変換して挿入
INGEST FROM FILE my_file.txt FORMAT DELIMITED BY ‘|' (
$prod_ID CHAR(8),
$description CHAR(32),
$price DECIMAL(5,2) EXTERNAL,
$sales_tax DECIMAL(4,2) EXTERNAL,
$shipping DECIMAL(3,2) EXTERNAL )
INSERT INTO my_table(prod_ID, description, total_price)
VALUES($prod_id, $description, $price + $sales_tax + $shipping);
35
© 2013 IBM Corporation
INGESTユーティリティ
UPDATE/MERGE/DELETE等も可能
– 例4: キーが一致するレコードの更新
INGEST FROM FILE myfile.txt FORMAT DELIMITED (
$key1 INTEGER EXTERNAL,
$key2 INTEGER EXTERNAL,
$data1 CHAR(8),
$data2 CHAR(32),
$data3 DECIMAL(5,2) EXTERNAL )
UPDATE my_table SET (data1, data2, data3) = ($data1, $data2, $data3)
WHERE (key1 = $key1) AND (key2 = $key2);
– 例5: キーが一致するレコードがあったら更新、なかったら挿入
INGEST FROM FILE myfile.txt FORMAT DELIMITED (
$key1 INTEGER EXTERNAL,
$key2 INTEGER EXTERNAL,
$data1 CHAR(8),
$data2 CHAR(32),
$data3 DECIMAL(5,2) EXTERNAL )
MERGE INTO my_table
ON (key1 = $key1) AND (key2 = $key2)
WHEN MATCHED THEN UPDATE SET (data1, data2, data3) = ($data1, $data2,
$data3)
WHEN NOT MATCHED THEN INSERT VALUES($key1, $key2, $data1, $data2, $data3);
36
© 2013 IBM Corporation
INGESTユーティリティ（リカバリー機能）
• エラー時の処理
o 事前にリスタート表を作成
db2 "CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', NULL, NULL)"
o 以下のコマンドが失敗したら。。。
INGEST FROM FILE my_file.txt FORMAT DELIMITED (
$field1 INTEGER EXTERNAL,
$field2 DATE 'mm/dd/yyyy',
$field3 CHAR(32) )
このDB内でユニークな名前を付ける
RESTART NEW 'ingestjob001'
INSERT INTO my_table VALUES($field1, $field2, $field3);
o 次のコマンドで再開可能（CONTINUEの代わりにTERMINATEで再開中止)
INGEST FROM FILE my_file.txt FORMAT DELIMITED (
$field1 INTEGER EXTERNAL,
$field2 DATE 'mm/dd/yyyy',
$field3 CHAR(32) )
RESTART CONTINUE 'ingestjob001'
INSERT INTO my_table VALUES($field1, $field2, $field3);
37
© 2013 IBM Corporation
INGESTユーティリティ
アーキテクチャー
– 3つのフェーズから構成
• Transporter, Formatter, Flusher
• マルチスレッドによる並列処理（Formatter, FlusherはINGEST SETコマンドで
スレッド数指定）
– DPF最適化
• データが保存されるパーティションに直接データを投入
その他の機能
– 処理状況を表示するモニターコマンド (INGEST LIST, INGEST GET STATS)
– コミットのタイミング等、各種設定 (INGEST SET)
Transporter
Formatter
Formatter
1つまたは複数の
ファイル/パイプ
Flusher
Flusher
Formatter
Transporter
Formatter
SQL
SQL
SQL
DB2
Flusher
マルチスレッド・モデル（1つのINGESTコマンド単位）
38
© 2013 IBM Corporation
移行ロードマップ
39
© 2013 IBM Corporation
移行ロードマップ
アセスメント
& PoC
データベース
移行
アプリケーション
移行
機能検証
1. アセスメント & PoC
– 移行対象となる環境についての調査・理解
ツール
正確
– 移行ポイントと作業量の見積もり
• 主に机上による変更点の洗い出し（ポーティング・アセスメント）
• オプション：一部分を実際に移行して検証（PoC: Proof of Concept）
2. データベース移行
– データベース・オブジェクト(表やPL/SQLなど)の移行
– データの移行
3. アプリケーション移行
– アプリケーションを DB2 用に移行
4. 機能検証
– 一連のテストを実施
40
ツール
迅速
自動化
最小限の変更
テスト
© 2013 IBM Corporation
移行ツール
41
© 2013 IBM Corporation
新しい移行ツール: Database Conversion Workbench (DCW)
従来のMEETツール、IDMT(IBM Data Movement Tool)ツールを統合
IBM Data Studioにプラグイン
2012年12月現在、DCW 1.0 がリリース
移行ツールの利用効果
– 移行計画の精度向上
– 移行の一部自動化による移行コスト削減
42
© 2013 IBM Corporation
DCWの機能
DCW 1.0 の主な機能
– DCW Task Launcher
• 統合ヘルプ環境。各処理手順をステップ・バイ・ステップで提供
– DDL Extraction
• OracleのデータベースからDDL定義を抽出
– Compatibility Evaluation and Code Conversion
• 互換率、非互換ポイントの提示と、一部シンタックスの自動変換
– Split DDL
• 1つのDDLファイルをオブジェクト・タイプごとに整理して分割
– Package Visualizer
• オブジェクト間の関係をグラフ表示
– Data Movement
• ソース・データベースからデータを抽出し、ターゲット・データベースに
ロード
43
© 2013 IBM Corporation
DCWを使用した移行の流れ
オブジェクト定義情報
Oracle
DDL
データ
DDL Extraction
Split DDL
Package
Visualizer
Oracle DDL
変換されたDDL
分割されたDDL
Compatibility Evaluation and
Code Conversion
Data Movement
DCW Task
Launcher
Database Conversion Workbench (DCW)
DB2 for LUW
44
© 2013 IBM Corporation
DCWプロジェクト
最初にDCWプロジェクトを作成する
45
© 2013 IBM Corporation
DCW Task Launcher
Task Launcherで処理したい内容を選択してゆくと、ウィザードが表示される
46
© 2013 IBM Corporation
DDL Extraction
以下のステップでDCWプロジェクトにDDLファイルが生成される
1. Extract DDL by creating a custom extraction script
DDLを抽出するためのスクリプトを生成して実行 (.outファイルの生成)
2. Import a DDL file
DCWプロジェクトに抽出したDDLファイル(.out)をインポート(.sqlファイルに変換される)
DDLを抽出するためのスクリプト生成
スクリプトの実行
> sqlplus user/pwd@sid @gen_ddl.sql
DDLファイルのインポート
47
© 2013 IBM Corporation
Compatibility Evaluation and Code Conversion (1/2)
互換率、非互換ポイントの提示と、一部シンタックスの自動変換
自動変換されたDDL、PL/SQL
(一部手動変換)
互換率、非互換ポイントの暗号化されたレポート
（[email protected] に暗号解除依頼のメール送付）
48
© 2013 IBM Corporation
Compatibility Evaluation and Code Conversion (2/2)
暗号解除されたレポート
49
© 2013 IBM Corporation
Split DDL
1つのDDLファイルをオブジェクト・タイプごとに整理して分割
50
© 2013 IBM Corporation
Package Visualizer (1/2)
オブジェクト間の関係をグラフ表示
① 以下の設定を事前に行う
• Database connected modeをチェック（データベースに接続して情報を取得）
• Database configuration（データベース接続設定）
Offline mode はDCW1.0では未サポート
② DB Objects Explorerビューを表示
51
© 2013 IBM Corporation
Package Visualizer (2/2)
③ DB Objects Explorerビューで、参照したいオブジェクトをダブルクリック
52
© 2013 IBM Corporation
Data Movement (1/3)
事前にターゲット・データベースにDDLを登録しておき、ソース・データベースからデータを
抽出し、ターゲット・データベースにロード
① ターゲット・データベースにDDLを登録
53
© 2013 IBM Corporation
Data Movement (2/3)
② ソース・データベースのデータをフラット・ファイルに抽出、または、
パイプ経由で直接ターゲット・データベースにロード
54
© 2013 IBM Corporation
Data Movement (3/3)
③ソース・データベースのデータをフラット・ファイルに抽出した場合、
フラット・ファイルのデータをターゲット・データベースにロード
55
© 2013 IBM Corporation
Oracleからの移行ポイント
※ 主な移行ポイントについて述べており、すべての移行ポイントを網羅
している訳ではありません
※ また、Oracle互換フィーチャーの使用 (DB2_COMPATIBILITY_VECTOR=ORA)
を前提としています
56
© 2013 IBM Corporation
CREATE TABLEのPCTFREEオプション
PCTFREEは、データの更新時にサイズが増えることを考慮し、挿入時に空きスペースを
あらかじめ確保しておくために利用する
DB2では、ALTER TABLE文でPCTFREEの値をセットしてから、データをロードするか、
データの再編成(reorg)を行う
–例
alter table t1 pctfree 10;
load from data.del of del insert into t1;
set integrity for t1 immediate checked;
(※) set integrity コマンドは、参照整合性制約等のチェックが必要な場合発行する
57
© 2013 IBM Corporation
Oracleのファンクション索引の移行
Oracleのファンクション索引の移行のポイント
– ファンクション索引に利用しているファンクションの結果を格納する列を表に追加する
– 追加する列のデータ・タイプはファンクションの戻りのデータ・タイプに合わせる
– ファンクション索引の代わりに、追加した列に対して索引を作成する
– generated always as を用い、データの挿入/変更時に自動更新されるようにする
– implicitly hiddenを指定して列を隠してもよい
• 隠してもSELECT文で明示的に列名を指定すると参照可能
• 隠さないとINSERT文で各列名を明示的に指定しないとデータが挿入できなくなる
– ファンクションを利用して参照するSQL文の修正は不要（DB2が自動的に索引を選択）
例:
– SUBSTR2(C2, 2, 3)で検索する場合
• select .. from … where SUBSTR2(C2, 2, 3)='xxx'
– 表作成時に定義する場合
• create table [表名] (…, c2 varchar(20), c2_substr varchar(3) generated always as
(substr2(c2, 2, 3)) implicitly hidden);
• 上記、追加する列名を C2_SUBSTR としたが、他の列名と異なるなら何でもよい
– 表が定義されており、列を追加する場合（以下の３つのステップを実行する）
• set integrity for [表名] off;
• alter table [表名] add column c2_substr varchar(3) generated always as (substr2(c2,
2, 3)) implicitly hidden;
• set integrity for [表名] immediate checked force generated;
– 列名を表に追加したら、その列に索引を作成する
• create index xxx on [表名] (c2_substr);
58
© 2013 IBM Corporation
VIEW定義内でのORDER BYの使用
VIEW定義内で ORDER BY を使用している場合、以下のように変換する
– Oracle
create view v1 as select … from t1 … order by c1
– DB2
create view v1 as select * from (select … from t1 … order by c1)
59
© 2013 IBM Corporation
ORDER BY xx ASC|DESC NULLS FIRST|LAST
ORDER BYでNULLの扱いを指定している場合、以下のように変換する
– Oracle
select … from t1 … order by c1 nulls first
– DB2
select … from t1 … order by row_number() over (order by c1 nulls
first)
• DESC NULLS LAST等も同様に動作
60
© 2013 IBM Corporation
SELECT文の FOR UPDATE の移行
Oracle で SELECT文に FOR UPDATE を付加している場合
– DB2では WITH RS を付ける。複数の表が対象の場合は、WITH RS USE AND KEEP
UPDATE LOCKS に変換する
– Oracle
select … from t1 where … for update;
– DB2
select … from t1 where … for update with rs;
select … from t1, t2 where … with rs use and keep update locks;
61
© 2013 IBM Corporation
SELECT文の NOWAIT の移行
Oracleで SELECT文に NOWAIT を付加している場合
– DB2ではSELECT文の前に TIMEOUT設定を変える "SET CURRENT LOCK TIMEOUT"
ステートメントを発行し、SELECT文の発行後に、設定を元に戻す
– Oracle
select … from t1 where … for update nowait;
– DB2
set current lock timeout not wait;
select … from t1 where … for update with rs;
set current lock timeout null;
※ set 文は、発行したセッションでのみ設定が変更されるので、
他のセッションへの影響なし
タイムアウト時の動作
– タイムアウトすると、トランザクションがロールバックされる（デフォルトの動作）
– 発行したSELECT文のみエラーとする場合、以下の設定を行う
db2set DB2LOCK_TO_RB=STATEMENT
※ DB2インスタンス・レベルでの設定となるため、影響範囲がインスタンス全体に渡る
62
© 2013 IBM Corporation
DB2のロック・タイムアウト設定
デフォルトは、DB構成パラメーター LOCKTIMEOUT の値が使用される
$ db2 get db cfg for [DB名]
…
ロック・タイムアウト (秒)
…
(LOCKTIMEOUT) = -1
※ -1 はデフォルトでロック・タイムアウトなし
SET CURRENT LOCK TIMEOUT ステートメントには、待ち時間をセットすることも可能
参照URL: SET CURRENT TIMEOUT
– http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0011874.html
63
© 2013 IBM Corporation
LENGTH / LENGTHB 関数の移行
OracleのLENGTH/LENGTHB関数の仕様
Oracleの関数名
仕様
LENGTH('文字列')
文字列の文字数を返す
LENGTHB('文字列')
文字列のバイト数を返す
DB2への移行時、以下のように変換する
64
Oracleの関数名
DB2の関数名
LENGTH('文字列')
LENGTH('文字列', CODEUNITS32)
LENGTHB('文字列')
LENGTH('文字列') または
LENGTH('文字列', OCTETS)
© 2013 IBM Corporation
SUBSTR / INSTR 関数の移行
DB2への移行時、以下のように変換する
Oracleの関数名
DB2の関数名
SUBSTR(…)
SUBSTR2(…) ※ または、
SUBSTRING('文字列', x, y, CODEUNITS32)
INSTR('文字列', '検索文字列')
INSTR('文字列', '検索文字列', x)
INSTR('文字列', '検索文字列', x, y)
INSTR('文字列', '検索文字列', 1, 1, CODEUNITS32)
INSTR('文字列', '検索文字列', x, 1, CODEUNITS32)
INSTR('文字列', '検索文字列', x, y, CODEUNITS32)
※ SUBSTR2(…)は、CODEUNITS16で処理 (JIS第3,4水準を使用していなければCODEUNITS16でも
問題なし)
SUBSTRB / INSTRB 関数はOracleと同じなので、変換不要
65
© 2013 IBM Corporation
TO_CHAR 関数の移行
数値、日付等を文字列の指定の書式にフォーマットする
数値フォーマットのFM等、異なる点があるので確認が必要（FMは以下のように変換する）
例
– Oracleの例
結果
OracleのTO_CHAR
TO_CHAR(-1234.56, '9,999,999.99')
'
-1,234.56'
TO_CHAR(-1234.56, 'FM9,999,999.99')
'-1,234.56'
– DB2の例（最初の例はOracleと同じなので変換不要）
結果
DB2のTO_CHAR
TO_CHAR(-1234.56, '9,999,999.99')
'
-1,234.56'
TRIM(TO_CHAR(-1234.56, '9,999,999.99'))
'-1,234.56'
参照URL: VARCHAR_FORMAT関数 (= TO_CHAR関数)
– http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0007110.html
66
© 2013 IBM Corporation
SYSTIMESTAMPの移行
OracleのSYSTIMESTAMPは、CURRENT TIMESTAMPに置き換える
– Oracleの使用例
select TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') from
dual;
– DB2への変換
select TO_CHAR(CURRENT TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS')
from dual;
SYSDATEはDB2でも使用可能 (CURRENT DATE と同じ)
67
© 2013 IBM Corporation
DBMS_APPLICATION_INFO および V$SESSION の移行(1/2)
クライアント情報を付加して、セッション(アプリケーション)情報と関連付ける
DB2では、以下の情報を付加できる
– ApplicationName, ClientAccountingInformation, ClientHostname, ClientUser
Javaでの付加
– ConnectionオブジェクトのsetClientInfoメソッドの利用
DataSource ds = (DataSource)ctx.lookup("java:/comp/env/jdbc/xxx");
Connection con = ds.getConnection();
con.setClientInfo("ClientUser", "User1");
con.setClientInfo("ApplicationName", "My Java Application1");
– 参照URL
• http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_t0052428.html
プロシージャーでの付加
– WLM_SET_CLIENT_INFOプロシージャーの利用
db2 => call WLM_SET_CLIENT_INFO('User2', null, 'My CLP2', null, null);
リターン状況 = 0
– 参照URL
• http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0053116.html
68
© 2013 IBM Corporation
DBMS_APPLICATION_INFO および V$SESSION の移行(2/2)
アプリケーション情報の取得
– SYSIBMADM.SNAPAPPL_INFO管理ビューの利用
db2 => select agent_id,
substr(appl_name,1,20) appl_name,
substr(appl_id,1,30) appl_id,
substr(tpmon_client_userid,1,10) tpmon_client_userid,
substr(tpmon_client_app,1,25) tpmon_client_app
from sysibmadm.snapappl_info;
AGENT_ID APPL_NAME
APP
-------- ------------------------------344 db2bp.exe
311 db2bp.exe
317 db2fw1
323 db2jcc_application
cation1
316 db2fw0
...
APPL_ID
TPMON_CLIENT_USERID TPMON_CLIENT_
------------------------------ ------------------- ------------*LOCAL.DB2.120911234210
*LOCAL.DB2.120911232102
*LOCAL.DB2.120911232110
127.0.0.1.50469.120911232312
User2
User1
My CLP2
My Java Appli
*LOCAL.DB2.120911232109
-
-
db2 => list application;
Auth Id
Application
Name
-------- -------------MOHKAWA db2bp.exe
MOHKAWA db2bp.exe
MOHKAWA db2jcc_applica
69
Appl.
Handle
---------344
311
323
Application Id
DB
# of
Name
Agents
---------------------------------------- -------- ----*LOCAL.DB2.120911234210
SAMPLE
1
*LOCAL.DB2.120911232102
SAMPLE
1
127.0.0.1.50469.120911232312
SAMPLE
1
© 2013 IBM Corporation
PL/SQL: ブロック内での型宣言
DB2 10 で対応されたので変換の必要なし
（DB2 9.7 までは PL/SQL のパッケージ定義で宣言）
例:
– Oracle & DB2
create or replace function func1(i in number) return varchar2
is
type type1 is record (
c1 number,
c2 varchar2(10));
type arr_type1 is table of type1 index by binary_integer;
type arr_type2 is varray(5) of varchar(10);
t1_arr arr_type1;
t2_arr arr_type2;
begin
t1_arr(1).c1 := i;
t1_arr(1).c2 := 'hello';
t1_arr(2).c1 := i + 1;
t1_arr(2).c2 := 'ciao';
t2_arr(1) := 'cava';
t2_arr(2) := 'ola';
return t1_arr(1).c2 || t1_arr(1).c1 || ', ' || t1_arr(2).c2
|| t1_arr(2).c1 || ', ' || t2_arr(1) || ', ' || t2_arr(2);
end;
70
© 2013 IBM Corporation
PL/SQL: ブロック内でのFUNCTION宣言
ブロック内でのFUNCTION宣言は、PROCEDURE宣言に書き換える
（DB2 10で対応。DB2 9.7 までは PL/SQL のパッケージに変換）
例:
– Oracle
create or replace function func1(i in number) return number is
function sub1 (j in number) return number is
begin
return j + 100;
end;
begin
return sub1(i);
end;
– DB2
create or replace function func1(i in number) return number is
i2 number;
procedure sub1 (j in number, j2 out number) is
begin
j2 := j + 100;
end;
begin
sub1(i, i2);
return i2;
end;
71
© 2013 IBM Corporation
PL/SQL: CURSORの宣言
Oracleでは、CURSOR定義内で、そのFUNCTION/PROCEDUREで宣言したパラメータが
使用可能
FUNCTION xxx(…) RETURN xxx IS
p1 VARCHAR2;
…
CURSOR c1(a1 VARCHAR2, a2 VARCHAR2) IS
SELECT … FROM … WHERE xxx = p1 and yyy = a1 and zzz = a2;
…
DB2
– DB2の場合、CURSOR定義内で使用できる変数は、そこに渡された引数のみ
– 上記の場合、p1 変数を使用することはできない。以下のようなエラーとなる
• SQL0206N 使用されているコンテキストで、"P1"は無効です。
– p1 変数も使用する場合、以下のように、引数として渡す
FUNCTION xxx(…) RETURN xxx IS
p1 VARCHAR2;
…
CURSOR c1(a1 VARCHAR2, a2 VARCHAR2, p1 VARCHAR2) IS
SELECT … FROM … WHERE xxx = p1 and yyy = a1 and zzz = a2;
…
72
© 2013 IBM Corporation
PL/SQL: RETURNING INTOにおけるBULK COLLECTの利用
RETURNING INTO は、DELETE/INSERT/UPDATEにより削除/挿入/更新された行を戻す
Oracleでは、BULK COLLECTと組み合わせることで複数行を得ることができる
DB2では、NEW TABLE関数(※)(INSERT/UPDATE時)、OLD TABLE関数(DELETE時)を利用
※ トリガー使用時、トリガー処理後のデータを得る場合、FINAL TABLE関数を利用
– Oracle
– DB2
73
declare
type t1row is record ( n number,
c varchar2(10) );
type t1row_array is varray(10) of t1row;
rows t1row_array;
begin
update t1 set c2='hello' where c1>=100
returning c1,c2 bulk collect into rows;
...
end;
declare
type t1row is record ( n number,
c varchar2(10) );
type t1row_array is varray(10) of t1row;
rows t1row_array;
begin
select c1,c2 bulk collect into rows from
new table(update t1 set c2='hello' where c1>=100);
...
end;
© 2013 IBM Corporation
PL/SQL: ROLLBACK時のCURSORの保持
DB2ではROLLBACKするとCURSORはクローズされる（COMMITでは保持される）
ROLLBACK後もCURSORを保持したい場合は、以下のようにSAVEPOINTを利用する
Oracle
<<cur1_loop>>
loop
fetch cur1 into x1, x2
exit when cur1%notfound;
…
rc := 0;
begin
…
exception
when … then
rc := -1;
end;
if rc != 0 then
rollback;
goto cur1_loop;
end if;
commit;
end loop cur1_loop;
close cur1;
74
DB2
<<cur1_loop>>
loop
fetch cur1 into x1, x2;
exit when cur1%notfound;
savepoint sp1 on rollback retain cursors;
…
rc := 0;
begin
…
exception
when … then
rc := -1;
end;
if rc != 0 then
rollback to savepoint sp1;
goto cur1_loop;
end if;
commit;
end loop cur1_loop;
close cur1;
© 2013 IBM Corporation
PL/SQL: DBMS_SQLパッケージ
DEFINE_COLUMN / COLUMN_VALUE プロシージャー
– 使用するデータタイプに応じたプロシージャーに書き換える
• 例:
DBMS_SQL.DEFINE_COLUMN_VARCHAR(cur, 1, c1, 16);
DBMS_SQL.COLUMN_VALUE_VARCHAR(cur, 1, c1);
DBMS_SQL.xxx_TABLE タイプ (例: DBMS_SQL.VARCHAR2_TABLE)
– DB2では、暗黙的に定義されていないので、使用前に以下のような定義を行う
TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
75
© 2013 IBM Corporation
JDBCの移行
DB2 JDBCドライバーが含まれる JAR ファイルをCLASSPATH環境変数に追加
– sqllib/java ディレクトリー下にある以下のファイルのうち、1つを追加
– db2jcc4.jar（JDBC 4.0 以降の機能、および、JDBC 3.0 以前の機能をサポート）
– db2jcc.jar （JDBC 3.0 以前の機能のみサポート）
JDBCドライバー・クラス名および接続URLの変更
– Oracle
• JDBCドライバー・クラス名: oracle.jdbc.OracleDriver
• 接続URLフォーマット（ユーザー名/パスワードなし）
jdbc:oracle:thin:@[ホスト名]:[ポート番号]:[SID]
• 接続URLフォーマット（ユーザー名/パスワード付）
jdbc:oracle:thin:[ユーザー名]/[パスワード]@[ホスト名]:[ポート番号]:[SID]
– DB2
• JDBCドライバー・クラス名: com.ibm.db2.jcc.DB2Driver
• 接続URLフォーマット（ユーザー名/パスワードなし）
jdbc:db2://[ホスト名]:[ポート番号]/[DB名]
• 接続URLフォーマット（ユーザー名/パスワード付）
jdbc:db2://[ホスト名]:[ポート番号]/[DB名]:user=[ユーザー名];password=[パス
ワード];
76
© 2013 IBM Corporation
Pro*Cの移行
SQL文をプログラムに埋め込む
プリコンパイラーを使用して、C言語に変換
プログラム例
EXEC SQL BEGIN DECLARE SECTION;
char user[20], password[20], dbname[9];
int c1;
char c2[11];
EXEC SQL END DECLARE SECTION;
strcpy(user, "scott"); strcpy(password, "xxx"); strcpy(dbname, "orcl");
/* データベースへの接続 */
EXEC SQL CONNECT :user IDENTIFIED BY :password USING :dbname;
/* カーソル宣言 */
EXEC SQL DECLARE cur CURSOR FOR SELECT C1, C2 FROM T1;
/* カーソルのオープン */
EXEC SQL OPEN cur;
for (;;) {
/* レコードの取得（フェッチ） */
EXEC SQL FETCH cur INTO :c1, :c2;
if (sqlca.sqlcode == 1403)
break;
printf("%d %s¥n", c1, c2);
}
/* カーソルのクローズ */
EXEC SQL CLOSE cur;
/* コミットして切断 */
EXEC SQL COMMIT WORK RELEASE;
77
© 2013 IBM Corporation
Pro*Cの移行（組み込みSQL）
DB2では組み込みSQLが、OracleのPro*Cに相当
SQL文の実行形式
– DB2とOracleともに、1つの静的SQL(※)実行方法と、4つの動的SQL実行方法がある
(※) 静的SQLについては次ページ参照
組み込みSQLの仕様（Pro*Cとの違い）
– データベースに接続して、プリコンパイルを行う（埋め込まれたSQL文の検証）
– プログラム実行前に、そのパッケージ(※)をデータベースにバインドしておく
– パッケージ(※)のバインド
• 埋め込まれた静的SQL文の実行計画（アクセス・プラン）の生成と保持
• プログラム実行時は、保持されたアクセス・プランに基づいて実行
（バインド時のオプションにより、動的SQLのように扱うこともできる）
※ Oracleのパッケージとは意味が違うので要注意
互換フィーチャー
– プリコンパイル・オプション COMPATIBILITY_MODE ORA をセットする
典型的なプリコンパイル・シンタックス
prep prog1.sqc bindfile package using prog1 compatibility_mode ora
参照URL: 移行の互換フィーチャーの使用可能化
– http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.embed.doc/doc/c0059083.html
78
© 2013 IBM Corporation
組み込みSQLプログラムと静的SQL
静的SQLはSQL文を直接プログラムに書く（動的SQLは変数にSQL文をセットする）
DB2の静的SQL
– バインド、またはリバインドした時に、SQL文の実行計画（アクセス・プラン）が生成
され、データベースに作成されたパッケージ・オブジェクトに保持される
– 実行時は、パッケージに保存されているアクセス・プランに従って実行される
• アクセス・プランを動的に作成する必要なし
組み込みSQLプログラム開発・実行の流れ(C言語用の場合)
組み込みSQLプログラム(xxx.sqc)
プリコンパイル（DB2 PRECOMIILE …)
C言語用プログラム(xxx.c)
バインド・ファイル(xxx.bnd)
コンパイル
バインド
リバインド
静的SQLのアクセス・プランの生成・再生成
実行形式プログラム
パッケージ
（静的SQLのアクセス・
プランを含む）
79
© 2013 IBM Corporation
組み込みSQLプログラムにおけるマクロ
マクロの使用（例: #ifdefの使用, 配列の要素数をラベルで定義）
– DB2もOracleもサポート
– DB2は別のコンパイラのプリプロセッサを利用（オプションで指定）
prep prog1.sqc bindfile package using prog1
preprocessor "xlC -P -DDB2=1" COMPATIBILITY_MODE ORA
※ AIXのコンパイラ xlC を使用した場合の例
マクロ使用例（含エラーメッセージ取得APIの違い）
#ifdef ORA
sqlglm(emsg, &buf_len, &emsg_len);
printf("%.*s¥n", emsg_len, emsg);
#elif DB2
sqlaintp(emsg, sizeof(emsg), 0, &sqlca);
printf("%s¥n", emsg);
#endif
80
© 2013 IBM Corporation
Pro*Cプログラム
DB2の組み込みSQLプログラムに移行
SQLの実行形式
– 静的SQL：DB2とOracleでほぼ同じ
例
EXEC SQL DECLARE cur CURSOR FOR SELECT C1, C2 FROM T1;
EXEC SQL OPEN cur;
EXEC SQL FETCH cur INTO :c1, :c2;
EXEC SQL EXECUTE IMMEDIATE :sql;
EXEC SQL PREPARE stmt FROM :sql;
EXEC SQL EXECUTE stmt USING :p1, :p2;
EXEC SQL PREPARE stmt FROM :sql;
EXEC SQL DECLARE cur CURSOR FOR stmt;
EXEC SQL OPEN cur USING :p1, :p2;
EXEC SQL FETCH cur INTO :c1, :c2;
– 動的SQL
• 方法1: DB2とOracleでほぼ同じ
SQL文の実行
• 方法2: DB2とOracleでほぼ同じ
SQL文の実行（入力ホスト変数をセット可能）
• 方法3: DB2とOracleでほぼ同じ
SQL文の実行（入力ホスト変数をセット可能、結果列の値を取得可能）
• 方法4:
入力ホスト変数の数、結果の列数等が動的に構成可能
指定はSQLDA構造体を使用。SQLDA構造体がDB2とOracleで違う
81
© 2013 IBM Corporation
Pro*C: プログラムの変換例
プリコンパイラーオプション COMPATIBILITY_MODE ORA で対応
プログラム例
…
/* データベースへの接続 */
EXEC SQL CONNECT :user IDENTIFIED BY :password USING :dbname;
/* カーソル宣言 */
EXEC SQL DECLARE cur CURSOR FOR SELECT C1, C2 FROM T1;
/* カーソルのオープン */
DB2本来のシンタックスは、
EXEC SQL OPEN cur;
EXEC SQL CONNECT TO :dbname USER :user USING :password;
for (;;) {
/* レコードの取得（フェッチ） */
EXEC SQL FETCH cur INTO :c1, :c2;
#ifdef ORA
if (sqlca.sqlcode == 1403) {
#elif DB2
if (sqlca.sqlcode == 100) {
#endif
break;
}
printf("%d %s¥n", c1, c2);
}
この例では、2箇所の書き換えが必要
/* カーソルのクローズ */
EXEC SQL CLOSE cur;
/* コミットして切断 */
#ifdef ORA
EXEC SQL COMMIT WORK RELEASE;
#elif DB2
EXEC SQL COMMIT;
EXEC SQL CONNECT RESET;
#endif
82
© 2013 IBM Corporation
Pro*C: VARCHAR構造体について（char型の利用との違い）(1/2)
VARCHAR param[x] は、Oracle互換モードで、以下の構造体に変換され、OracleのPro*Cプログラムが
そのまま利用できる
struct {
short len;
char arr[x];
} param;
VARCHAR型でDB2サーバーとデータをやり取りする場合
– この変数を使用してSQL文で戻されるデータを受けると、arrに文字列の値（NULLターミネーターな
し）、lenにその長さがセットされる。
– この変数に値をセットしてDB2サーバーとやり取りする場合も、上記のようにセットする。
char型でDB2サーバーとデータをやり取りする場合
– char型の変数を使用してSQL文で戻されるデータを受けると、文字列の値をNULLターミネーター付
きでセットする。
• NULLターミネーターがセットされるため、1バイト余計に変数の領域を確保する
例（T1表の定義: create table t1 (c1 int, c2 varchar2(10))）
83
int c1;
VARCHAR c2_1[10];
char c2_2[10+1];
…
EXEC SQL DECLARE cur CURSOR FOR SELECT C1, C2, C2 FROM T1;
…
EXEC SQL FETCH cur INTO :c1, :c2_1, :c2_2;
NULLターミネートされない
printf("c2_1=%.*s¥n", c2_1.len, c2_1.arr);
printf("c2_2=%s¥n", c2_2);
© 2013 IBM Corporation
NULLターミネートされる
Pro*C: VARCHAR構造体について（char型の利用との違い）(2/2)
Oracleの場合、列のデータ・タイプがVARCHAR2であっても、C言語のchar型でデータを
受信すると、値は固定長となり、足りない文字はスペースが入り、NULLターミネーターが
付く（必ず文字列の長さが列のデータ・タイプの長さとなり、NULLターミネーターが付
く）
OracleのPro*Cプログラム
char型でデータを取得すると
"hello˽˽˽˽˽"
"ciao˽˽˽˽˽˽"
Oracle
VARCHAR2(10)
'hello'
'ciao˽˽'
DB2の場合、列のデータ・タイプがVARCHAR(VARCHAR2)なら、C言語のchar型でデータ
を受信すると、その値がそのままセットされ、NULLターミネーターが付く
DB2の組み込みSQLプログラム
char型でデータを取得すると
"hello"
"ciao˽˽"
84
DB2
VARCHAR2(10)
'hello'
'ciao˽˽'
© 2013 IBM Corporation
Pro*C: 構造体を用いたデータ取得（NULLチェック方法）
Oracle, DB2ともに、複数の列のデータをC言語の構造体で取得することが可能
NULLかどうかチェックするためのインジケータの指定方法が異なる
85
Oracle
DB2
EXEC SQL BEGIN DECLARE SECTION;
char user[20], password[20], dbname[9];
typedef struct {
int c1;
char c2[11];
} T1;
T1 t1;
typedef struct {
short c1;
short c2;
} T1_IND;
T1_IND t1_ind;
EXEC SQL END DECLARE SECTION;
…
EXEC SQL DECLARE cur CURSOR FOR
SELECT C1, C2 FROM T1;
…
EXEC SQL FETCH cur INTO :t1 :t1_ind;
…
if (t1_ind.c1 == -1) {
/* C1がNULLの場合の処理 */
…
}
if (t1_ind.c2 == -1) {
/* C2がNULLの場合の処理 */
…
}
EXEC SQL BEGIN DECLARE SECTION;
char user[20], password[20], dbname[9];
typedef struct {
int c1;
char c2[11];
} T1;
T1 t1;
short t1_ind[2];
EXEC SQL END DECLARE SECTION;
…
EXEC SQL DECLARE cur CURSOR FOR
SELECT C1, C2 FROM T1;
…
EXEC SQL FETCH cur INTO :t1 :t1_ind;
…
if (t1_ind[0] == -1) {
/* C1がNULLの場合の処理 */
…
}
if (t1_ind[1] == -1) {
/* C2がNULLの場合の処理 */
…
}
© 2013 IBM Corporation
Pro*C: FETCH … INTO のデータなし処理
データなしのエラー番号が違う（Oracleの場合:1403, DB2の場合:100）
– DB2では以下のようなプログラムになる
while (1) {
EXEC SQL FETCH …;
if (sqlca.sqlcode == 100) {
break;
}
sqlca.sqlcode は SQLCODE と置き換えること
…
も可能
例:
if (SQLCODE == 100) {
EXEC SQL WHENEVER NOT FOUND DO BREAK文で抜けることも可能
– 終了したら EXEC SQL WHENEVER NOT FOUND CONTINUE を発行する
EXEC SQL WHENEVER SQLERROR GOTO SqlErrorTerminate;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while (1) {
EXEC SQL FETCH …;
…
}
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
86
© 2013 IBM Corporation
Pro*C: ストアード・プロシージャー呼び出し
DB2では、EXEC SQL CALL文で対応
無名ブロック(begin … end) を使用する場合、動的SQLにする
Oracleの例
– 以下のようにプロシージャーの第1,3引数を入力, 第2,4引数を出力とする
EXEC SQL EXECUTE
begin
proc1(:in1, :out1, :in2, :out2);
end;
END-EXEC;
DB2: EXEC SQL CALL文で対応する場合
EXEC SQL CALL proc1(:in1, :out1, :in2, :out2);
DB2: 無名ブロックを使用する場合
動的SQL内の:変数は、? としてもよい
例:
begin proc1(?, ?, ?, ?); end;
char stmt[256];
…
strcpy(stmt, "begin proc1(:in1, :out1, :in2, :out2); end;");
EXEC SQL PREPARE sql FROM :stmt;
EXEC SQL EXECUTE sql into :in1, :out1, :in2, :out2 using :in1,
:out1, :in2, out2;
引数が入力か出力かに関係なく、順に定義する
87
© 2013 IBM Corporation
© IBM Corporation 2013. All Rights Reserved.
ワークショップ、セッション、および資料は、IBMまたはセッション発表者によって準備され、それぞれ独自の見解を反映したものです。
それらは情報提供の目的のみで提供されており、いかなる参加者に対しても法律的またはその他の指導や助言を意図したものではなく、ま
たそのような結果を生むものでもありません。本プレゼンテーションに含まれている情報については、完全性と正確性を帰するよう努力し
ましたが、「現状のまま」提供され、明示または暗示にかかわらずいかなる保証も伴わないものとします。本プレゼンテーションまたはそ
の他の資料の使用によって、あるいはその他の関連によって、いかなる損害が生じた場合も、IBMは責任を負わないものとします。 本プレ
ゼンテーションに含まれている内容は、IBMまたはそのサプライヤーやライセンス交付者からいかなる保証または表明を引きだすことを意
図したものでも、IBMソフトウェアの使用を規定する適用ライセンス契約の条項を変更することを意図したものでもなく、またそのような
結果を生むものでもありません。
本プレゼンテーションでIBM製品、プログラム、またはサービスに言及していても、IBMが営業活動を行っているすべての国でそれらが使
用可能であることを暗示するものではありません。本プレゼンテーションで言及している製品リリース日付や製品機能は、市場機会または
その他の要因に基づいてIBM独自の決定権をもっていつでも変更できるものとし、いかなる方法においても将来の製品または機能が使用可
能になると確約することを意図したものではありません。本資料に含まれている内容は、参加者が開始する活動によって特定の販売、売上
高の向上、またはその他の結果が生じると述べる、または暗示することを意図したものでも、またそのような結果を生むものでもありませ
ん。
パフォーマンスは、管理された環境において標準的なIBMベンチマークを使用した測定と予測に基づいています。ユーザーが経験する実際
のスループットやパフォーマンスは、ユーザーのジョブ・ストリームにおけるマルチプログラミングの量、入出力構成、ストレージ構成、
および処理されるワークロードなどの考慮事項を含む、数多くの要因に応じて変化します。したがって、個々のユーザーがここで述べられ
ているものと同様の結果を得られると確約するものではありません。
記述されているすべてのお客様事例は、それらのお客様がどのようにIBM製品を使用したか、またそれらのお客様が達成した結果の実例と
して示されたものです。実際の環境コストおよびパフォーマンス特性は、お客様ごとに異なる場合があります。
以下は、International Business Machines Corporationの米国およびその他の国における商標。IBMの全商標のリストについては、
www.ibm.com/legal/copytrade.shtmlをご覧ください。
AIX, CICS, CICSPlex, DB2, DB2 Universal Database, i5/OS, IBM, the IBM logo, IMS, iSeries, Lotus, OMEGAMON, OS/390, Parallel Sysplex,
pureXML, Rational, RCAF, Redbooks, Sametime, Smart SOA, System i, System i5, System z , Tivoli, WebSphere, z/OS.
JavaおよびすべてのJava関連の商標およびロゴは Sun Microsystems, Inc.の米国およびその他の国における商標。
Microsoft, Windows, Windows NT および Windowsロゴは Microsoft Corporationの米国およびその他の国における商標。
Intel, Intelロゴ, Intel Inside, Intel Insideロゴ, Intel Centrino, Intel Centrinoロゴ, Celeron, Intel Xeon, Intel SpeedStep, Itanium, Pentium は
Intel Corporationまたは子会社の米国およびその他の国における商標または登録商標。
UNIXはThe Open Groupの米国およびその他の国における登録商標。
Linuxは、Linus Torvaldsの米国およびその他の国における商標。
その他の会社名、製品名およびサービス名等はそれぞれ各社の商標。
88
© 2013 IBM Corporation