PowerPoint プレゼンテーション

Transcription

1 Oracle から DB2 10 への移行 - DB2 の機能 ツール および移行ポイント 年 1 月 日本アイ ビー エム株式会社 本資料掲載事項は ある特定の環境 使用状況においての正確性が IBM によって確認されていますが すべての環境において同様の結果が得られる保証はありません これらの技術を自身の環境に適用する際には 自己の責任において十分な検証と確認を実施いただくことをお奨めいたします 1

2 内容 DB2とOracleの用語 Oracleからの移行を容易にするDB2の機能 移行ロードマップ 移行ツール Oracleからの移行ポイント 当資料は DB2 V10.1 for Linux, UNIX, and Windows Fix Pack 1 の環境に基づいています 2

3 DB2 と Oracle の用語 3

4 DB2 と Oracle の用語 設計 Oracle インスタンスオンラインREDOログアーカイブ ログ非アーカイブ ログ モードデータ ファイル SYSTEM 表スペース表スペースエクステントデータ ブロックセグメントパーティション表マテリアライズ ビューグローバル索引ローカル索引データ ディクショナリー DB2 インスタンスまたはデータベース マネージャーアクティブ ログアーカイブ ログ循環ロギングコンテナー SYSCATSPACE 表スペース表スペースエクステントデータ ページストレージ オブジェクトパーティション表マテリアライズ照会表 (MQT) 非パーティション索引パーティション索引システム カタログ 4

5 DB2 と Oracle の用語 設定 / メモリー アプリ使用機能 運用 Oracle ORACLE_SID 環境変数 init.ora および Server Parameter File (SPFILE) System Global Area (SGA) Program Global Area (PGA) User Global Area (UGA) データ バッファー キャッシュラージ プールライブラリー キャッシュデータ ディクショナリー キャッシュグローバル一時表カーソル共有データベース リンク DUAL 表 Oracle Call Interface (OCI) セッションバインド変数動的なパフォーマンス ビューアラート ログ bdump ディレクトリー startup nomount DB2 DB2INSTANCE 環境変数データベース マネージャー構成ファイルおよびデータベース構成ファイルインスタンス共有メモリーおよびデータベース共有メモリーアプリケーション共有メモリーおよびエージェント専用メモリーアプリケーション グローバル メモリーバッファー プールユーティリティー ヒープパッケージ キャッシュカタログ キャッシュグローバル一時表ステートメント コンセントレーターニックネーム DUAL 表コール レベル インターフェース (CLI) セッション データベース接続パラメータ マーカースナップショット モニター SQL 管理ビュー管理通知ログ診断ログ (db2diag.log) db2start 5

6 Oracle からの移行を容易にする DB2 の機能 6

7 移行における課題 Transact-SQL Oracle の SQL および PL/SQL DB2 の SQL/PL (SQL/PSM に基づく ) アプリケーションが 選択した RDBMS に特化している 異なる RDBMS へ移行するにはコードの大幅な書き直しが必要 既存スキルを活用できない DB2 は これらの問題を解決するための機能を提供しており その拡張を継続的に行っている 7

8 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

9 互換フィーチャーの設定 : 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

10 DB2_COMPATIBILITY_VECTOR で設定可能な互換フィーチャー ビット位置互換性フィーチャーコメント 1 (0x01) ROWNUM ROW_NUMBER() OVER() の同義語 2 (0x02) DUAL DUAL ダミー表を使用可能化 3 (0x04) 外部結合演算子外部結合演算子 ( + 記号 ) のサポートを使用可能化 4 (0x08) 階層照会 CONNECT BY 節を使用した階層照会のサポート 5 (0x10) NUMBER NUMBER データ タイプおよび関連した数値処理のサポート 6 (0x20) VARCHAR2 VARCHAR2 型と NVARCHAR2 データ タイプおよび関連した文字ストリング処理 7 (0x40) DATE DATE データ タイプを TIMESTAMP(0) データ タイプとして変換処理可能 8 (0x80) TRUNCATE TABLE TRUNCATE ステートメントの代替セマンティック 9 (0x100) 文字リテラル CHAR データ タイプまたは GRAPHIC データ タイプ (VARCHAR データ タイプまたは VARGRAPHIC データ タイプの代替 ) 10 (0x200) コレクションのメソッド 配列での演算を実行するためのメソッド (first last next および previous など ) を使用可能化 配列内の特定のエレメントを参照するときに大括弧の代わりに小括弧を使用可能化 例 : array1(i) は array1 のエレメント i を参照する (0x400) データ ディクショナリー互換ビュー データ ディクショナリー互換ビューはデータベースの作成時に作成 12 (0x800) PL/SQL コンパイル PL/SQL ステートメントのコンパイルおよび実行の可能化 13 (0x1000) インセンシティブ カーソル SELECT ステートメントが明示的に FOR UPDATE を指定しない場合 WITH RETURN で定義されたカーソルがインセンシティブになる 14 (0x2000) INOUT パラメーター INOUT パラメーター宣言に対する DEFAULT の指定 17 (0x10000) SQL データ アクセス レベルの強制実行時にルーチンが SQL データ アクセス レベルを強制可能 18 (0x20000) Oracle データベース リンク構文 Oracle データベース リンク構文を有効にする データベース作成前に設定必要 参照 URL:

11 互換フィーチャー例 以下の互換フィーチャーを有効にする ビット 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) への互換性 = ON = ON = ON 11

12 Oracle データ ディクショナリー互換ビューの確認 Oracle データ ディクショナリー互換ビューの例 接続ユーザーが作成した表情報の取得 select * from user_tables 接続ユーザーが作成したビュー情報の取得 select * from user_views 参照 URL 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 12

13 互換フィーチャーの追加設定 互換フィーチャーの追加設定前 $ db2 -td/ Oracle 特有の外部結合演算子 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=

14 互換フィーチャーの追加設定 すべての互換フィーチャーをオンに設定変更して 既存のデータベースにアクセス $ 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

15 互換フィーチャーの追加設定 すべての互換フィーチャーをオンに設定した後の動作 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

16 PL/SQL 互換フィーチャー ネイティブ PL/SQL サポート : DB2 エンジンに PL/SQL コンパイラーも同梱 エミュレーションなし (PL/SQL と SQL PL を同じレベルで実行可能 ) ソース レベルのデバッグおよびプロファイル エディター PL/SQL も SQL PL もアーキテクチャーは同じ PL/SQL コンパイラー SQL PL コンパイラー Data Studio DB2 サーバー デバッガー SURE (SQL Unified Runtime Engine) プロファイラー データベース 16

17 DB2 10 の組み込みパッケージ フィーチャー DBMS_ALERT DBMS_DDL DBMS_JOB DBMS_LOB DBMS_OUTPUT DBMS_PIPE DBMS_SQL DBMS_UTILITY MONREPORT UTL_DIR UTL_FILE 説明 クロス セッション セマフォー DDL オブジェクトの難読化 ジョブ スケジューラー 管理のプロシージャー LOB を操作する機能 画面出力によるデバッグ および簡単なレポート作成 クロス セッション データ パイプ 動的 SQL を実行するためのプロシージャー セット さまざまな関数およびプロシージャー データのモニターとテキスト レポートの生成 ディレクトリーの別名を維持するためのルーチン サーバー サイドの I/O API UTL_MAIL E メール送信機能 (SMTP は DB 構成情報定義を利用 ) UTL_SMTP SMTP を指定した E メール送信機能 参照 URL 17

18 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; データ タイデータ 列の スケ 列名 プ スキーマタイプ名 長さ ール NULL N1 SYSIBM DECFLOAT 8 0 はい N2 SYSIBM DECIMAL 10 0 はい N3 SYSIBM DECIMAL 20 5 はい db2 => insert into t1 values (10, 100, ), (200, 2000, ); db2 => select * from t1; N1 N2 N

19 端数の丸め込み方法 DECFLT_ROUNDING データベース構成パラメーターで設定 DB2の場合 デフォルトは ROUND_HALF_EVEN 最も近い値に丸める 正確に等間隔の場合 末尾が偶数になるように丸める 例 : db2 => create table t2 (n1 number, n2 number(5,2)); db2 => insert into t2 values ( , ); N1 N が付くと切り上げの方が近くなる db2 => delete from t2; db2 => insert into t2 values ( , ); N1 N Oracle の場合 四捨五入 (ROUND_HALF_UP) Oracle と同じ 四捨五入にする場合 update db cfg for [DB 名 ] using DECFLT_ROUNDING ROUND_HALF_UP 参照 URL 19

20 振る舞いが変わる主な機能 (VARCHAR2) VARCHAR データ タイプに自動変換 この互換フィーチャーをオンにすると VARCHAR データ タイプの振る舞いが変わる VARCHAR2 互換なし VARCHAR2 互換 空文字 ('') とNULLの区別 区別する 区別なし ( 空文字はNULLに変換される ) 非 NULL 制約空文字の挿入可空文字を挿入できない 文字列の比較 文字列末尾の空白の違いは無視して比較 文字列末尾の空白が違うものは違う文字として扱う 20

21 振る舞いが変わる主な機能 (VARCHAR2) 例 VARCHAR2 互換なし create table t3 (c1 int, c2 varchar(10) not null); insert into t3 values (1, 'hello'); insert into t3 values (2, 'hello '); 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 '); select * from t3 where c2='hello'; C1 C 文字列末尾の空白文字の違いを無視 1 hello 2 hello select * from t3 where c2='hello'; C1 C hello insert into t3 values (3, ''); DB20000I SQL コマンドが正常に完了しました select * from t3; C1 C hello 2 hello 3 非 NULL 制約でも空文字の挿入可 insert into t3 values (3, ''); SQL0407N NULL 値の NOT NULL 列 "TBSPACEID=3, TABLEID=7, COLNO=1" への代入は許されていません SQLSTATE=23502 空文字は NULL に変換される 21

22 振る舞いが変わる主な機能 (DATE) Oracle の DATE 型は時間を保持 ( 時分秒 ) DB2 の DATE 型は時間を保持しない この互換フィーチャーをオンにすると 以下のような振る舞いとなる DATE 互換なし DATE 互換 DATE 型の列 DATE 型のまま TIMESTAMP(0) に変換 CURRENT DATE 特殊レジスター DATE 値を戻す 日付の演算 + 1 day などと記述 (1 日加算 ) 以下の表記は DATE 型ではエラーとなる (TIMESTAMP 型で使用可能 ) - 2 hour (2 時間減算 ) + 3 minute (3 分加算 ) TIMESTAMP(0) 値を戻す + 1 で 1 日加算 - 2/24 で 2 時間減算 + 3/24/60 で 3 分加算 22

23 振る舞いが変わる主な機能 (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, ' '); insert into t4 values (2, ' '); select * from t4; C1 C 時間は削除 ( 無視 ) されて挿入される select to_char(c2,'yyyy/mm/dd HH24:MI:SS') from t4; /08/25 00:00: /09/05 00:00:00 select current date - 5 day from sysibm.sysdummy1;

24 振る舞いが変わる主な機能 (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, ' '); insert into t4 values (2, ' '); select * from t4; C1 C select to_char(c2,'yyyy/mm/dd HH24:MI:SS') from t4; /08/25 00:00: /09/05 10:20:30 select current date /24 from dual;

25 同時実行制御 分離レベル 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 UNCOMMITTED UNCOMMITTED READ (UR) なし READ COMMITTED DB2 の分離レベル CS では Currently Committed の設定が可能 DB 構成パラメータ CUR_COMMIT で設定 デフォルトは ON db2 get db cfg for [DB 名 ] CS 分離レベルで現在コミット済みの結果を戻す (CUR_COMMIT) = ON 25

26 同時実行制御 Oracle の READ COMMITTED データの読み取り SQL 文発行時点のコミット済みデータを取得 UNDO セグメントを使って実現 ( ログと UNDO セグメントの両方に書き込み ) 別のトランザクションがデータを更新中でも それがコミットされるのを待たない SQL 文発行後にコミットされたデータは読まない SCN = システム変更番号 TRN1 UPDATE UPDATE COMMIT SCN=32 SCN=102 SCN=64 A B C C D D E F UNDO セグメント C D SCN=76 SCN=100 TRN2 SELECT 26 読んだデータは最新とは限らないが 参照処理のロック待機は発生しない UNDO セグメントを使って実現 ( ログと UNDO セグメントの両方に書き込み )

27 同時実行制御 DB2 の Currently Committed データを読み取る際 最新のコミット済みデータを取得し 別のトランザクションがデータを更新中でも それがコミットされるのを待たない ログのみを使って実現 TRN1 UPDATE UPDATE A B C C D D E トランザクションログ ( ログバッファー ) C D TRN2 SELECT F 読むデータは常に最新で 参照処理のロック待機も発生しないログのみを使って実現 27

28 データベース リンク マテリアライズド ビューの移行 Oracle の代表的な DB 連携 データベース リンク リアルタイムの外部 Oracle への仮想アクセス Oracle Oracle SQL DBLINK Oracle Oracle マテリアライズド ビュー トリガー ベースのテーブルの差分レプリケーション Oracle Oracle SQL マテリアライス ト ヒ ュー 差分トリガー 28

29 データベース リンク マテリアライズド ビューの移行 フェデレーションと InfoSphere Change Data Capture が解決! フェデレーション リアルタイムに外部の多様な DB への仮想アクセス DB2 Oracle SQL フェデレーション DB2 SQL Server さまざまなデータソースをサポート ( 含非 RDB データ ) InfoSphere Change Data Capture ログベースのテーブルの差分レプリケーション DB2 Oracle CDC CDC ログベースで高速 軽量 SQL ログ 29

30 フェデレーション機能によるニックネーム アクセス リモートの表やビュー ストアードプロシージャの仮想オブジェクト ローカル オブジェクトのように操作 リアルタイムの参照 更新が可能 マルチソースの Join が可能 ニックネームは実体をもたない仮想的な表 多種多様なデータソースを表のイメージでアクセス ユーザー アプリケーション SELECT * FROM ORAEMP WHERE DB2 ニックネーム Oracle ソース表 DB Fix Pack 1 で以下の構文もサポート (Oracle 互換フィーチャー :0x20000) DB2ADMIN.ORAEMP ORACLE.SCOTT.EMP SELECT * FROM SCOTT.EMP@ORACLE WHERE CREATE NICKNAME ORAEMP FOR ORACLE. SCOTT.EMP 30

31 IBM InfoSphere Change Data Capture Point 1 Point 2 Point 3 高速 軽量 低負荷な差分レプリケーション データベースのログから差分データを抽出 幅広い DB プラットフォームのサポート Oracle, DB2 for LUW, isereis, z/os, SQL Server, Sybase, Teradata, Netezza, MQ, JMS, BEA など 簡単な操作 non プログラミング GUI による容易な定義や操作 モニタリング機能と構成 データベース DB2, Oracle, Microsoft SQL Server 等 データベースログ CDC ソース側エンジン TCP/IP CDC ターゲット側エンジン Information Server DataStage メッセージ キュー フラット ファイル 31

32 SQL*Loader の移行 データを取り込むためのツール DB2 では 従来から LOAD, IMPORT ユーティリティを提供 LOAD データベース コンテナー ( ストレージ ) に フォーマット済みページを直接書き込む ( 処理が高速なので大量のデータ挿入に向いている ) 参照整合性制約等のチェックは 別途 SET INTEGRITY コマンドを発行する必要がある ( それまでロードした表にアクセスできない ) トリガーは起動しない 等の制約がある IMPORT INSERT 文で実現 尐量のデータ挿入に向いている 参照 URL DB2 10 から さらに INGEST ユーティリティを提供 INGEST コマンド処理中も アプリケーションからのデータ アクセスが可能 INSERT/REPLACE の他に UPDATE/DELETE/MERGE が可能 ( 簡易 ETL 処理が可能 ) 再開可能なエラー発生時 最後にコミットした時点から再開が可能 継続的なデータ挿入や 簡単な ETL 処理を行いたい場合に向いている 参照 URL 32

33 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 ) DB2 Load コマンド LOAD FROM '/home/ora_usr/accounts.dat' OF ASC METHOD L ( , , , , ) INSERT INTO accounts ( acct_id,dept_code,acct_desc,max_employees,current_employees ); 33

34 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 ) 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 ); 34

35 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

36 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

37 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

38 INGEST ユーティリティ アーキテクチャー 3 つのフェーズから構成 Transporter, Formatter, Flusher マルチスレッドによる並列処理 (Formatter, Flusher は INGEST SET コマンドでスレッド数指定 ) DPF 最適化 データが保存されるパーティションに直接データを投入 その他の機能 処理状況を表示するモニターコマンド (INGEST LIST, INGEST GET STATS) コミットのタイミング等 各種設定 (INGEST SET) Transporter Formatter Flusher SQL 1 つまたは複数のファイル / パイプ Transporter Formatter Formatter Formatter Flusher Flusher SQL SQL DB2 マルチスレッド モデル (1 つの INGEST コマンド単位 ) 38

39 移行ロードマップ 39

40 移行ロードマップ アセスメント & PoC データベース移行 アプリケーション移行 機能検証 1. アセスメント & PoC 移行対象となる環境についての調査 理解 移行ポイントと作業量の見積もり 主に机上による変更点の洗い出し ( ポーティング アセスメント ) オプション : 一部分を実際に移行して検証 (PoC: Proof of Concept) ツール正確迅速 2. データベース移行 データベース オブジェクト ( 表や PL/SQL など ) の移行 データの移行 3. アプリケーション移行 アプリケーションを DB2 用に移行 4. 機能検証 一連のテストを実施 ツール 自動化 最小限の変更 テスト 40

41 移行ツール 41

42 新しい移行ツール : Database Conversion Workbench (DCW) 従来の MEET ツール IDMT(IBM Data Movement Tool) ツールを統合 IBM Data Studio にプラグイン 2012 年 12 月現在 DCW 1.0 がリリース 移行ツールの利用効果 移行計画の精度向上 移行の一部自動化による移行コスト削減 42

43 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

44 DCW を使用した移行の流れ DDL Oracle オブジェクト定義情報 データ DDL Extraction Split DDL Oracle DDL 変換された DDL 分割された DDL Package Visualizer Compatibility Evaluation and Code Conversion Data Movement Database Conversion Workbench (DCW) DCW Task Launcher DB2 for LUW 44

45 DCW プロジェクト 最初に DCW プロジェクトを作成する 45

46 DCW Task Launcher Task Launcher で処理したい内容を選択してゆくと ウィザードが表示される 46

47 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 DDL ファイルのインポート 47

48 Compatibility Evaluation and Code Conversion (1/2) 互換率 非互換ポイントの提示と 一部シンタックスの自動変換 自動変換された DDL PL/SQL ( 一部手動変換 ) 48 互換率 非互換ポイントの暗号化されたレポート (askdcw@ca.ibm.com に暗号解除依頼のメール送付 )

49 Compatibility Evaluation and Code Conversion (2/2) 暗号解除されたレポート 49

50 Split DDL 1 つの DDL ファイルをオブジェクト タイプごとに整理して分割 50

51 Package Visualizer (1/2) オブジェクト間の関係をグラフ表示 1 以下の設定を事前に行う Database connected mode をチェック ( データベースに接続して情報を取得 ) Database configuration( データベース接続設定 ) Offline mode は DCW1.0 では未サポート 2 DB Objects Explorer ビューを表示 51

52 Package Visualizer (2/2) 3 DB Objects Explorer ビューで 参照したいオブジェクトをダブルクリック 52

53 Data Movement (1/3) 事前にターゲット データベースに DDL を登録しておき ソース データベースからデータを抽出し ターゲット データベースにロード 1 ターゲット データベースに DDL を登録 53

54 Data Movement (2/3) 2 ソース データベースのデータをフラット ファイルに抽出 または パイプ経由で直接ターゲット データベースにロード 54

55 Data Movement (3/3) 3 ソース データベースのデータをフラット ファイルに抽出した場合 フラット ファイルのデータをターゲット データベースにロード 55

56 Oracle からの移行ポイント 主な移行ポイントについて述べており すべての移行ポイントを網羅している訳ではありません また Oracle 互換フィーチャーの使用 (DB2_COMPATIBILITY_VECTOR=ORA) を前提としています 56

57 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

58 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 としたが 他の列名と異なるなら何でもよい 表が定義されており 列を追加する場合 ( 以下の 3 つのステップを実行する ) 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

59 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

60 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

61 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

62 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

63 DB2 のロック タイムアウト設定 デフォルトは DB 構成パラメーター LOCKTIMEOUT の値が使用される $ db2 get db cfg for [DB 名 ] ロック タイムアウト ( 秒 ) (LOCKTIMEOUT) = -1-1 はデフォルトでロック タイムアウトなし SET CURRENT LOCK TIMEOUT ステートメントには 待ち時間をセットすることも可能 参照 URL: SET CURRENT TIMEOUT 63

64 LENGTH / LENGTHB 関数の移行 Oracle の LENGTH/LENGTHB 関数の仕様 Oracle の関数名 LENGTH(' 文字列 ') LENGTHB(' 文字列 ') 仕様 文字列の文字数を返す 文字列のバイト数を返す DB2 への移行時 以下のように変換する Oracle の関数名 LENGTH(' 文字列 ') LENGTHB(' 文字列 ') DB2 の関数名 LENGTH(' 文字列 ', CODEUNITS32) LENGTH(' 文字列 ') または LENGTH(' 文字列 ', OCTETS) 64

65 SUBSTR / INSTR 関数の移行 DB2 への移行時 以下のように変換する Oracle の関数名 SUBSTR( ) INSTR(' 文字列 ', ' 検索文字列 ') INSTR(' 文字列 ', ' 検索文字列 ', x) INSTR(' 文字列 ', ' 検索文字列 ', x, y) DB2 の関数名 SUBSTR2( ) または SUBSTRING(' 文字列 ', x, y, CODEUNITS32) INSTR(' 文字列 ', ' 検索文字列 ', 1, 1, CODEUNITS32) INSTR(' 文字列 ', ' 検索文字列 ', x, 1, CODEUNITS32) INSTR(' 文字列 ', ' 検索文字列 ', x, y, CODEUNITS32) SUBSTR2( ) は CODEUNITS16 で処理 (JIS 第 3,4 水準を使用していなければ CODEUNITS16 でも問題なし ) SUBSTRB / INSTRB 関数は Oracle と同じなので 変換不要 65

66 TO_CHAR 関数の移行 数値 日付等を文字列の指定の書式にフォーマットする 数値フォーマットの FM 等 異なる点があるので確認が必要 (FM は以下のように変換する ) 例 Oracle の例 Oracle の TO_CHAR DB2 の例 ( 最初の例は Oracle と同じなので変換不要 ) 結果 TO_CHAR( , '9,999,999.99') ' -1,234.56' TO_CHAR( , 'FM9,999,999.99') DB2 の TO_CHAR '-1,234.56' 結果 TO_CHAR( , '9,999,999.99') ' -1,234.56' TRIM(TO_CHAR( , '9,999,999.99')) '-1,234.56' 参照 URL: VARCHAR_FORMAT 関数 (= TO_CHAR 関数 ) 66

67 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

68 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 プロシージャーでの付加 WLM_SET_CLIENT_INFO プロシージャーの利用 db2 => call WLM_SET_CLIENT_INFO('User2', null, 'My CLP2', null, null); リターン状況 = 0 参照 URL 68

69 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 APPL_ID TPMON_CLIENT_USERID TPMON_CLIENT_ APP db2bp.exe *LOCAL.DB db2bp.exe *LOCAL.DB User2 My CLP2 317 db2fw1 *LOCAL.DB db2jcc_application User1 My Java Appli cation1 316 db2fw0 *LOCAL.DB db2 => list application; Auth Id Application Appl. Application Id DB # of Name Handle Name Agents MOHKAWA db2bp.exe 344 *LOCAL.DB SAMPLE 1 MOHKAWA db2bp.exe 311 *LOCAL.DB SAMPLE 1 MOHKAWA db2jcc_applica SAMPLE 1 69

70 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

71 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

72 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

73 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 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; 73 DB2 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;

74 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; 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; 74

75 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

76 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

77 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); } 77 /* カーソルのクローズ */ EXEC SQL CLOSE cur; /* コミットして切断 */ EXEC SQL COMMIT WORK RELEASE;

78 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: 移行の互換フィーチャーの使用可能化 78

79 組み込み SQL プログラムと静的 SQL 静的 SQL は SQL 文を直接プログラムに書く ( 動的 SQL は変数に SQL 文をセットする ) DB2 の静的 SQL バインド またはリバインドした時に SQL 文の実行計画 ( アクセス プラン ) が生成され データベースに作成されたパッケージ オブジェクトに保持される 実行時は パッケージに保存されているアクセス プランに従って実行される アクセス プランを動的に作成する必要なし 組み込み SQL プログラム開発 実行の流れ (C 言語用の場合 ) 組み込み SQL プログラム (xxx.sqc) プリコンパイル (DB2 PRECOMIILE ) C 言語用プログラム (xxx.c) バインド ファイル (xxx.bnd) コンパイルバインドリバインド 実行形式プログラム 静的 SQL のアクセス プランの生成 再生成 79 パッケージ ( 静的 SQL のアクセス プランを含む )

80 組み込み 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

81 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; 動的 SQL 方法 1: DB2 と Oracle でほぼ同じ SQL 文の実行 EXEC SQL EXECUTE IMMEDIATE :sql; 方法 2: DB2 と Oracle でほぼ同じ SQL 文の実行 ( 入力ホスト変数をセット可能 ) EXEC SQL PREPARE stmt FROM :sql; EXEC SQL EXECUTE stmt USING :p1, :p2; 方法 3: DB2 と Oracle でほぼ同じ SQL 文の実行 ( 入力ホスト変数をセット可能 結果列の値を取得可能 ) 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; 方法 4: 入力ホスト変数の数 結果の列数等が動的に構成可能 指定は SQLDA 構造体を使用 SQLDA 構造体が DB2 と Oracle で違う 81

82 Pro*C: プログラムの変換例 プログラム例 /* データベースへの接続 */ 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; #ifdef ORA if (sqlca.sqlcode == 1403) { #elif DB2 if (sqlca.sqlcode == 100) { #endif break; } printf("%d %s n", c1, c2); } プリコンパイラーオプション COMPATIBILITY_MODE ORA で対応 DB2 本来のシンタックスは EXEC SQL CONNECT TO :dbname USER :user USING :password; この例では 2 箇所の書き換えが必要 82 /* カーソルのクローズ */ EXEC SQL CLOSE cur; /* コミットして切断 */ #ifdef ORA EXEC SQL COMMIT WORK RELEASE; #elif DB2 EXEC SQL COMMIT; EXEC SQL CONNECT RESET; #endif

83 Pro*C: VARCHAR 構造体について (char 型の利用との違い )(1/2) VARCHAR param[x] は Oracle 互換モードで 以下の構造体に変換され Oracle の Pro*C プログラムがそのまま利用できる VARCHAR 型で DB2 サーバーとデータをやり取りする場合 この変数を使用して SQL 文で戻されるデータを受けると arr に文字列の値 (NULL ターミネーターなし ) len にその長さがセットされる この変数に値をセットして DB2 サーバーとやり取りする場合も 上記のようにセットする char 型で DB2 サーバーとデータをやり取りする場合 char 型の変数を使用して SQL 文で戻されるデータを受けると 文字列の値を NULL ターミネーター付きでセットする NULL ターミネーターがセットされるため 1 バイト余計に変数の領域を確保する 例 (T1 表の定義 : create table t1 (c1 int, c2 varchar2(10))) 83 struct { short len; char arr[x]; } param; 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; printf("c2_1=%.*s n", c2_1.len, c2_1.arr); NULLターミネートされない printf("c2_2=%s n", c2_2); NULLターミネートされる

84 Pro*C: VARCHAR 構造体について (char 型の利用との違い )(2/2) Oracleの場合 列のデータ タイプがVARCHAR2であっても C 言語のchar 型でデータを受信すると 値は固定長となり 足りない文字はスペースが入り NULLターミネーターが付く ( 必ず文字列の長さが列のデータ タイプの長さとなり NULLターミネーターが付く ) OracleのPro*Cプログラム Oracle char 型でデータを取得すると "hello " "ciao " VARCHAR2(10) 'hello' 'ciao ' DB2 の場合 列のデータ タイプが VARCHAR(VARCHAR2) なら C 言語の char 型でデータを受信すると その値がそのままセットされ NULL ターミネーターが付く DB2 の組み込み SQL プログラム char 型でデータを取得すると "hello" "ciao " DB2 VARCHAR2(10) 'hello' 'ciao ' 84

85 Pro*C: 構造体を用いたデータ取得 (NULL チェック方法 ) Oracle, DB2 ともに 複数の列のデータを C 言語の構造体で取得することが可能 NULL かどうかチェックするためのインジケータの指定方法が異なる 85 Oracle 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 の場合の処理 */ } DB2 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の場合の処理 */ }

86 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

87 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); 動的 SQL 内の : 変数は? としてもよい DB2: 無名ブロックを使用する場合例 : char stmt[256]; begin proc1(?,?,?,?); end; 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

88 IBM Corporation All Rights Reserved. ワークショップ セッション および資料は IBM またはセッション発表者によって準備され それぞれ独自の見解を反映したものです それらは情報提供の目的のみで提供されており いかなる参加者に対しても法律的またはその他の指導や助言を意図したものではなく またそのような結果を生むものでもありません 本プレゼンテーションに含まれている情報については 完全性と正確性を帰するよう努力しましたが 現状のまま 提供され 明示または暗示にかかわらずいかなる保証も伴わないものとします 本プレゼンテーションまたはその他の資料の使用によって あるいはその他の関連によって いかなる損害が生じた場合も IBM は責任を負わないものとします 本プレゼンテーションに含まれている内容は IBM またはそのサプライヤーやライセンス交付者からいかなる保証または表明を引きだすことを意図したものでも IBM ソフトウェアの使用を規定する適用ライセンス契約の条項を変更することを意図したものでもなく またそのような結果を生むものでもありません 本プレゼンテーションで IBM 製品 プログラム またはサービスに言及していても IBM が営業活動を行っているすべての国でそれらが使用可能であることを暗示するものではありません 本プレゼンテーションで言及している製品リリース日付や製品機能は 市場機会またはその他の要因に基づいて IBM 独自の決定権をもっていつでも変更できるものとし いかなる方法においても将来の製品または機能が使用可能になると確約することを意図したものではありません 本資料に含まれている内容は 参加者が開始する活動によって特定の販売 売上高の向上 またはその他の結果が生じると述べる または暗示することを意図したものでも またそのような結果を生むものでもありません パフォーマンスは 管理された環境において標準的な IBM ベンチマークを使用した測定と予測に基づいています ユーザーが経験する実際のスループットやパフォーマンスは ユーザーのジョブ ストリームにおけるマルチプログラミングの量 入出力構成 ストレージ構成 および処理されるワークロードなどの考慮事項を含む 数多くの要因に応じて変化します したがって 個々のユーザーがここで述べられているものと同様の結果を得られると確約するものではありません 記述されているすべてのお客様事例は それらのお客様がどのように IBM 製品を使用したか またそれらのお客様が達成した結果の実例として示されたものです 実際の環境コストおよびパフォーマンス特性は お客様ごとに異なる場合があります 以下は International Business Machines Corporation の米国およびその他の国における商標 IBM の全商標のリストについては をご覧ください 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