(Microsoft PowerPoint - \221\3462\217\315 Oracle\214\335\212\267\201i\221O\224\274\201j.ppt)

Transcription

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

2 内容 Oracle 互換機能強化概要 DB2_COMPATIBILITY_VECTOR データタイプの互換性 スカラー関数関数の機能強化 ビルトイン パッケージパッケージ ライブラリー PL/SQL サポート パッケージ ( モジュール ) のサポート 参考資料 ISE Corporation

3 Oracle 互換機能強化概要 Oracle 互換機能強化概要 ISE Corporation

4 Oracle 互換機能の強化により移行が容易に Oracle 固有の機能や動作 Oracle SQL データデータ型 PL/SQL ビルトイン関数ビルトイン パッケージ Concurrency Control SQL*Plus Scripts Your Applications ほとんどそのまま理解できるできる ほとんどそのまま動く Oracle Database DB2 Database ISE Corporation

5 Oracle 互換機能概要 データタイプ NUMBER VARCHAR2 TIMESTAMP(n) DATE BOOLEAN INDEX BY VARRAY ROW TYPE Ref Cursor ビルトイン関数 形式 型変換関連 (TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_NUMBER, TO_CLOB) 日付操作関連 (EXTRACT, ADD_MONTHS, ) 文字列操作関連 (INITCAP, RPAD, LPAD, INSTR, REVERSE ) その他 (DECODE, NVL, LEAST, GREATEST, BITAND) ビルトイン パッケージ ライブラリー DBMS_OUTPUT UTL_FILE DBMS_ALERT DBMS_PIPE DBMS_JOB DBMS_LOB DBMS_SQL フィーチャー CONNECT BY 句を使用した階層照会 外部結合演算子 (+)-join DUAL 表 ROWNUM 擬似列 TRUNCATE TABLE ステートメント Public synonym 作成済み一時表 NEXTVAL/CURRVAL( シーケンスの呼び出し方法 ) MINUS( 集合演算子 EXCEPT の Alias) Unnamed inline views ISE Corporation

6 PL/SQL サポート概要 PL/SQL の機能 All logic EXCEPTION User Defined Exceptions Constant variables FOR over range over SELECT over cursor %TYPE %ROWTYPE BULK COLLECT/FETCH FORALL Autonomous transaction PL/SQL の記述場所 Anonymous block Scalar function Procedure Package Trigger パッケージオブジェクトのサポート CREATE PACKAGE CREATE PACKAGE BODY Replace package body PKG [BODY] VARIABLE CURSOR TYPE EXCEPTION SYNONYM ON PACKAGE ISE Corporation

7 DB2_COMPATIBILITY_VECTOR ISE Corporation

8 DB2_COMPATIBILITY_VECTOR レジストリ変数 Oracle との互換機能を有効にするレジストリ変数 Oracle 互換機能の中にはこのレジストリ変数の設定が必要なものがあります 設定方法 12 ビットのビットでそれぞれの機能を ON OFF を決定します このビットの 16 進値を設定します それぞれのビットに割り当てられている機能は次ページを参照 全ての互換機能を有効にするには FFF( =0x01+0x02+ +0x400+0x800) を指定 DB2_COMPATIBILITY_VECTOR=ORA の設定も可能 この設定は FFF と同じように全ての互換フィーチャーを有効にします この設定は Oracle との最大の互換性を実現する設定であり Oracle 互換機能を使用する際の推奨値となります またこの設定により DB2_DEFERRED_PREPARE_SEMANTICS=YES の設定も暗黙的に有効になります DB2_DEFERRED_PREPARE_SEMANTICS=YES は Unicode,SBCS 環境でのみの推奨値となります 以上により こちらがそれぞれの環境での推奨値 Unicode,SBCS 環境 DB2_COMPATIBILITY_VECTOR=ORA DB2_DEFERRED_PREPARE_SEMANTICS=YES それ以外の環境 DB2_COMPATIBILITY_VECTOR=ORA DB2_DEFERRED_PREPARE_SEMANTICS=NO 暗黙的に設定されますが明示指定されることをお勧めします ISE Corporation

9 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 ステートメントおよび言語要素のコンパイルと実行を可能にします ISE Corporation

10 DB2_COMPATIBILITY_VECTOR の設定 DB2_COMPATIBILITY_VECTOR DB2_DEFERRED_PREPARE_SEMANTICS を設定後 インスタンスの再起動をして設定を有効にします 以下の機能は データベース作成時に DB2_COMPATIBILITY_VECTOR を設定しておかなければ 機能を有効に出来ません VARCHAR,NUMBER,DATE のデータタイプの互換性 データディクショナリー互換ビュー DB2_COMPATIBILITY_VECTOR 設定例 C: work>db2set DB2_COMPATIBILITY_VECTOR=ORA C: work>db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES C: work>db2stop force :29: SQL1064N DB2STOP の処理が正常に終了しました SQL1064N DB2STOP の処理が正常に終了しました C: work>db2start :29: SQL1063N DB2START の処理が正常に終了しました SQL1063N DB2START の処理が正常に終了しました C: work>db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES DB2_COMPATIBILITY_VECTOR=ORA DB2INSTOWNER= ISE Corporation

11 データタイプの互換性 ISE Corporation

12 データタイプの互換性 NUMBER,VARCHAR2,DATEの互換性 Oracleからの移行時のデータタイプのマッピングやDDLの変更を最小限できる Oracleがサポートしている以下のデータタイプとの互換性をサポート 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 ISE Corporation

13 DATE データタイプの互換性 データデータ タイプタイプ DATE の TIMESTAMP(0) への互換性互換性 が ON DATEタイプがTIMESTAMP(0) として扱われる OracleのDATEとの互換 OracleのDATEは 秒までの精度を持つ データ タイプ DATE の TIMESTAMP(0) への互換性 =OFF create table tab1(c1 date) describe table tab1 データ タイデータ 列の スケ 列名 プ スキーマタイプ名 長さ ール NULL C1 SYSIBM DATE 4 0 はい 1 レコードが選択されました データ タイプ DATE の TIMESTAMP(0) への互換性 =ON create table tab1(c1 date) Date 型でテーブルを作成すると describe table tab1 Timestamp(0) として定義される データ タイデータ 列の スケ 列名 プ スキーマタイプ名 長さ ール NULL C1 SYSIBM TIMESTAMP 7 0 はい 1 レコードが選択されました insert into tab1 values current date insert into tab1 values current date select * from tab1 C レコードが選択されました 出力形式 ( 精度 ) が異なる select * from tab1 C レコードが選択されました ISE Corporation

14 参考 )TIMESTAMP の精度指定 TIMESTAMPの精度精度の指定指定が可能 互換機能 (DB2_COMPATIBILITY_VECTOR) に関係なくV9.7から使用可能 小数点以下の秒数の精度を指定 0 から 12 までを指定可能 ( デフォルト :6) Oracle の Timestamp 型との互換 Oracle の Timestamp でも精度指定可能 ( 指定可能な桁数 0-9) より高い精度を持つ TIMESTAMP 型の使用 低い精度を指定することでスペースの節約 create table tab1( c0 timestamp(0),c1 timestamp(1),c2 timestamp(2), c3 timestamp(3),c4 timestamp(4),c5 timestamp(5), c6 timestamp(6),c7 timestamp(7),c8 timestamp(8), c9 timestamp(9),c10 timestamp(10),c11 timestamp(11), c12 timestamp(12),c13 timestamp ) describe table tab1 データ タイデータ 列のスケ列名プ スキーマタイプ名長さール NULL C0 SYSIBM TIMESTAMP 7 0 はい C1 SYSIBM TIMESTAMP 8 1 はい C2 SYSIBM TIMESTAMP 8 2 はい C3 SYSIBM TIMESTAMP 9 3 はい C4 SYSIBM TIMESTAMP 9 4 はい C5 SYSIBM TIMESTAMP 10 5 はい C6 SYSIBM TIMESTAMP 10 6 はい C7 SYSIBM TIMESTAMP 11 7 はい C8 SYSIBM TIMESTAMP 11 8 はい C9 SYSIBM TIMESTAMP 12 9 はい C10 SYSIBM TIMESTAMP はい C11 SYSIBM TIMESTAMP はい C12 SYSIBM TIMESTAMP はい C13 SYSIBM TIMESTAMP 10 6 はい 精度の指定により列の長さが変わる 精度を短くとることでスペースの節約が可能 values current timestamp レコードが選択されました values current timestamp(0) レコードが選択されました values current timestamp(12) CURRENT TIMESTAMP 特殊レジスターも精度指定可能 14 レコードが選択されました 1 レコードが選択されました ISE Corporation

15 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 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 として定義されます ISE Corporation

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

17 VARCHAR2 データ タイプの互換性 ON/OFF の違い 空文字 ('') の扱いとVARCHARの末尾末尾ブランクブランクの比較比較の違いについて確認 VARCHAR2 データ タイプの互換性 =OFF C: work asnclp>db2 -tvf varchar2test.sql create table tab1(c1 varchar(10)) insert into tab1 values (null),(''),('a'),('a ') select c1,hex(c1) from tab1 C a 61 a レコードが選択されました select c1,hex(c1) from tab1 where c1 = 'a' 空文字 ( ) OFF:0 バイトの文字 ON:NULL C a 61 VARHCHARの比較 a 6120 OFF: 末尾ブランクは無視される 2 レコードが選択されました ON: 末尾ブランクも含めて比較 VARCHAR2 データ タイプの互換性 =ON C: work asnclp>db2 -tvf varchar2test.sql create table tab1(c1 varchar(10)) insert into tab1 values (null),(''),('a'),('a ') select c1,hex(c1) from tab1 C a 61 a レコードが選択されました select c1,hex(c1) from tab1 where c1 = 'a' C a 61 1 レコードが選択されました ISE Corporation

18 参考 ) 非空白埋め比較セマンティクスと空白埋め比較セマンティクス 非空白埋め比較比較セマンティクス 以下の文字は異なるものとみなす 'A' 'A_' 'A ' ('_' は空白文字 ) 空白埋め比較比較セマンティクス 空白文字をパティングして比較する 末尾の空白文字の違いは無視される 以下の文字は同じものとみなす 'A' 'A_' 'A ' ('_' は空白文字 ) Oracle の VARCHAR の比較はこちらが使用される VARCHAR2 データ タイプの互換性 =ON の時の DB2 の VARCHAR の比較はこちらが使用される VARCHAR2 データ タイプの互換性 =OFF の時の DB2 の VARCHAR の比較はこちらが使用される ISE Corporation

19 スカラー関数の機能強化 ISE Corporation

20 スカラー関数の機能強化 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 ISE Corporation

21 形式 型変換関連のスカラー関数の使用例 関数 フォーマットストリングを使用し フォーマットの変換を行いながら型変換を行える 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 レコードが選択されました 文字型に変換 ISE Corporation

22 ビルトイン パッケージ ライブラリー ( ビルトイン モジュール ) ISE Corporation

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

25 DBMS_OUTPUT 使用例 DBMS_OUTPUT パッケージ ( モジュール ) メッセージバッファーへの書き込み 読み込み行うためのプロシジャー 関数を提供 処理結果の出力 デバッグなどで役立ちます C: work>db2 -td@ -vf dbms_output_test.sql drop procedure proc1 CREATE PROCEDURE proc1( P1 VARCHAR(10) ) BEGIN CALL DBMS_OUTPUT.PUT( 'P1 = ' ); CALL DBMS_OUTPUT.PUT_LINE( P1 ); END SET SERVEROUTPUT ON DB20000I SET SERVEROUTPUT コマンドが正常に完了しました CALL proc1( '10' ) DBMS_OUTPUT パッケージの PUT プロシージャー PUT_LINE プロシージャーを呼び出して メッセージ バッファーへの書き込みを行います CLP でメッセージバッファーを読み出すための設定 リターン状況 = 0 P1 = 10 メッセージ バッファーから読み出されたデータ ISE 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!type myfile.csv A00,SPIFFY COMPUTER SERVICE DIV., B01,PLANNING, C01,INFORMATION CENTER, D11,MANUFACTURING SYSTEMS, 省略 CSV 形式でファイル出力 ISE Corporation

27 PL/SQL サポート PL/SQL サポート ISE Corporation

28 PL/SQL とは SQLの手続き型拡張機能としてOracle 社が提供するプログラミング言語 Oracle 向けに独自に開発されているため SQLのように標準仕様にのっとったものではない Oracle Database の内部でSQLとの緊密な連携を行うことで業務処理を効率的に実行する 反復処理や分岐といった制御構造を使って複雑な処理を行うことができる なぜ PL/SQL が必要? SQL はデータの集合を操作するための言語であるが 実際の業務処理 ( ビジネス ロジック ) では手続き型の処理が必要な場合もある Oracle での PL/SQL 実行例 制御文と SQL で構成される ISE Corporation

29 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; ISE Corporation

30 DB2 9.7 での PL/SQL サポート DB2 9.7 では新たに PL/SQL をサポート V9.5 までは SQL PL のみ Editor DB2 9.7 では New PL/SQL Compiler SQL PL Compiler PL/SQL 用と SQL PL 用の 2 つの コンパイラを持つ SURE (SQL Unified Runtime Engine) DB2 Server それぞれのコンパイラから作成 されたモジュールは同じランタイム で稼動する Data base ISE Corporation

31 DB2 9.7 で PL/SQL を実行 カーソルを使って DEPT 表から結果を読み取る 前のページと全く同一 PL/SQL スクリプトを実施 PL/SQL ユニークな文法が DB2 で利用できる ISE Corporation

32 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> ISE Corporation

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

34 DB2_COMPATIBILITY_VECTOR=800 の設定 DB2_COMPATIBILITY_VECTOR=800を設定設定することで PL/SQLを処理処理できるようになる DB2_COMPATIBILITY_VECTOR=800を設定 C: >db2set DB2_COMPATIBILITY_VECTOR=800 DB2_COMPATIBILITY_VECTOR=800 設定なし C: work>db2 -tvf plsqltest2.sql!db2set findstr DB2_COMPATIBILITY_VECTOR set sqlcompat plsql DB20000I SET SQLCOMPAT コマンドが正常に完了しました set serveroutput on DB20000I SET SERVEROUTPUT コマンドが正常に完了しました declare v1 varchar(100) := 'PL/SQL Test'; begin dbms_output.put_line(v1); end; PL/SQL ブロックの処理に失敗 DB21034E コマンドが 有効なコマンド行プロセッサー コマンドでないため SQLステートメントとして処理されました SQL 処理中に そのコマンドが返されました SQL0104N "BEGIN-OF-STATEMENT" に続いて予期しないトークン "declare v1 varchar" が見つかりました 予期されたトークンに "<values>" が含まれている可能性があります LINE NUMBER=2. SQLSTATE=42601 C: >db2stop force :37: SQL1064N DB2STOP の処理が正常に終了しました SQL1064N DB2STOP の処理が正常に終了しました C: >db2start :37: SQL1063N DB2START の処理が正常に終了しました SQL1063N DB2START の処理が正常に終了しました C: work>db2 -tvf plsqltest2.sql!db2set findstr DB2_COMPATIBILITY_VECTOR DB2_COMPATIBILITY_VECTOR=800 set sqlcompat plsql DB20000I SET SQLCOMPAT コマンドが正常に完了しました set serveroutput on DB20000I SET SERVEROUTPUT コマンドが正常に完了しました declare v1 varchar(100) := 'PL/SQL Test'; begin dbms_output.put_line(v1); end; PL/SQL Test DB2_COMPATIBILITY_VECTOR=800 設定あり PL/SQL ブロックが正常に処理処理される ISE Corporation

35 SET SQLCOMPAT PLSQL(CLP オプション ) CLP から PL/SQL を実行するためのオプション SET SQLCOMPAT PLSQL を設定することで / を PL/SQL ブロックの終わりと認識する PL/SQL では ; はステートメントの区切り ブロック単位で実行させるために ブロックの最後を / で認識 セッションの終わりまで有効 SET SQLCONMPAT DB2 で元に戻す ISE Corporation

36 PL/SQL サポート制約事項 以下の製品製品エディションエディションではでは PL/SQL ステートメントのコンパイルコンパイルおよびおよび実行実行はサポートサポートされません DB2 Express DB2 Express-C DB2 Personal Edition パーティション データベースデータベース環境環境ではでは PL/SQL 関数およびおよびトリガートリガーは作成作成できませんできません データベースが Unicode データベースとしてとして定義定義されていないされていない場合場合 PL/SQL ステートメントおよび PL/SQL コンテキストにおいてにおいて NCLOB データ タイプタイプの使用使用はサポートサポートされません Unicode データベースではでは NCLOB データ タイプタイプは DB2 DBCLOB データ タイプタイプにマップマップされますされます XMLTYPE データ タイプタイプはサポートサポートされませんされません TYPE 宣言は 関数関数 プロシージャープロシージャー トリガートリガー およびおよび無名無名ブロックブロックではではサポートサポートされませんされません FOR EACH STATEMENT オプションは PL/SQL トリガーではではサポートサポートされませんされません ISE Corporation

37 パッケージ ( モジュール ) のサポートパッケージ ( モジュール ) のサポート ISE Corporation

38 パッケージ ( モジュール ) のサポート モジュール Oracleのパッケージパッケージに相当相当するするオブジェクト 関数 プロシージャープロシージャー タイプタイプ 変数変数などのなどのオブジェクトオブジェクトをまとめたをまとめたオブジェクト 以下のオブジェクトを含められる SQL プロシージャー SQL ファンクション 外部プロシージャー 外部ファンクション グローバルコンディション Module の初期化処理 ユーザー定義タイプ ( 配列 連想配列 行タイプ カーソルタイプ ) グローバル変数 PL/SQLのパッケージパッケージと同じ機能機能を提供 PL/SQL では CREATE PACKAGE 文によりモジュールを作成 SQL PL では CREATE MODULE 文によりモジュールを作成 Module グローバル変数 SQL プロシージャー SQL ファンクション ISE Corporation

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

40 モジュールのメリット モジュール内オブジェクトの共用 変数 カーソル 型 例外 ローカルルーチンを共有できる 名前衝突の回避 プロシージャー名 ファンクション名 変数名の衝突を回避できる 情報の隠蔽 モジュール内のみで使用可能なオブジェクトの作成が可能 権限のコントロール モジュール単位に権限の grant/revoke が可能 ISE Corporation

41 モジュールと 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 alter module mod1 publish variable var1 int default 0 alter module mod1 publish procedure print 仕様部 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; 仕様部 本体 alter module mod1 publish procedure plus(in p1 int) create or replace package body pkg1 is procedure print is begin alter module mod1 publish function get() returns int dbms_output.put_line(var1); 本体 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; 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; end; end; alter module mod1 add function get() returns int return var ISE Corporation

42 モジュールに関するシステムカタログ 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 in ('PKG1','MOD1') order by 1 OBJECTMODULENAME OBJECTNAME OBJECTTYPE PUBLISHED MOD1 VAR1 VARIABLE Y MOD1 PLUS PROCEDURE Y MOD1 GET FUNCTION Y MOD1 PRINT PROCEDURE Y PKG1 VAR1 VARIABLE Y PKG1 GET FUNCTION Y PKG1 PRINT PROCEDURE Y PKG1 PLUS PROCEDURE Y MOD1 に含まれるオブジェクト PKG1 に含まれるオブジェクト 8 レコードが選択されました ISE Corporation

43 モジュールへの db2look の対応 db2lookの-mod オプションによりによりモジュールモジュールの DDLの生成生成が可能 -modオプション -mod: Generate DDL statements for Module このオプションは -e および -x/xd オプションとともに使用できます DDL 生成例 C: SQLLIB samples sqlpl>db2look -d testdb -e -mod -- USER は以下のとおりです : DB2ADMIN -- 表の DDL の作成 -- この CLP ファイルの作成に使用した DB2LOOK のバージョン : "9.7" -- タイム スタンプ : 2009/05/19 23:18:34 -- データベース名 : TESTDB -- データベース マネージャーのバージョン : DB2/NT Version データベース コード ページ : データベース照合シーケンス : SYSTEM_943 続き モジュール サポートの DDL ステートメント "DB2ADMIN"."MOD1" CREATE MODULE "DB2ADMIN"."MOD1"; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; CONNECT TO TESTDB; モジュール サポートの DDL ステートメント "DB2ADMIN"."PKG1" create or replace package pkg1 is var1 int :=0; procedure print; procedure plus(p1 in int); function get return int; end; ; create or replace package body pkg1 is alter module mod1 publish variable var1 int default 0; SET CURRENT SCHEMA = "DB2ADMIN"; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; alter module mod1 PUBLISH procedure print ; SET CURRENT SCHEMA = "DB2ADMIN"; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; alter module mod1 PUBLISH procedure plus(in p1 int default 1) ; SET CURRENT SCHEMA = "DB2ADMIN"; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; ISE Corporation

44 モジュール制限事項 モジュール内の個別のオブジェクトに特権を付与することはできません モジュールに付与 またはモジュールから取り消せるのは EXECUTE 特権だけです モジュールに含めることができるのは以下のデータベース オブジェクトの定義だけです SQL プロシージャー SQL 関数 外部プロシージャー 外部関数 作成済み条件 モジュール初期化の際に暗黙的に実行されるモジュール初期化プロシージャー ユーザー定義のデータ タイプ定義 ( 特殊タイプ 配列タイプ 連想配列タイプ 行タイプ およびカーソル タイプが含まれます ) すべてのデータ タイプのグローバル変数 モジュールの初期化に使用する SYS_INIT 初期化プロシージャーは パラメーターを参照したり 結果セットを戻したりすることはできません Data Studio ツールまたはコントロール センター ツールから モジュールの定義 変更 初期化 またはドロップを行うことはできません 今回のバージョンでは モジュールの使用に関して以下の制約事項が適用されます 初期化プロシージャーの定義および実行はできません ISE Corporation

45 まとめ Oracle 互換機能強化概要 DB2_COMPATIBILITY_VECTOR データタイプの互換性 スカラー関数の機能強化 ビルトイン パッケージ ライブラリー PL/SQLサポート パッケージ ( モジュール ) のサポート ISE Corporation

46 ブランク ページ ISE Corporation

47 参考資料 ISE Corporation

48 参考資料内容 Weak Typing Public Synonym 作成済み一時表 DB2_COMPATIBILITY_VECTORで有効有効に出来出来る機能 ROWNUM 外部結合演算子 (+) 階層照会 NUMBER データ タイプ VARCHAR2 データ タイプ DATE データ タイプ TRUNCATE TABLE 文字リテラル コレクション メソッド データ ディクショナリー互換ビュー PL/SQL のコンパイル ISE Corporation

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

50 暗黙的な変換の例 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 間の暗黙的な変換 ISE Corporation

51 非型付きパラメーター マーカーのデータタイプ判別 非型付きパラメーター マーカー 型付きパラメーター マーカー CAST 関数によって明示的に型指定が行われているパラメーターマーカー 例 :values cast(? as char(20)) データタイプ指定されていないパラメーター マーカー 例 : select * from t1 where c1 =? values? PREPARE 時にコンテキストからデータタイプが判別できない場合にはエラーとなる select * from t1 where c1 =? PREPARE 時に列 C1 のタイプからパラメーター マーカーのタイプを判別 values? PREPARE 時にはデータタイプの判別が出来ないためエラー V9.7 より 非型付きパラメーター マーカーのデータタイプ判別を OPEN EXECUTE 時まで据え置き パラメーター マーカーの入力値に基づいたデータ タイプの判別を行わせることが出来る この機能を使用するには以下の条件が必要 PREPARE が実行され 据え置き準備 (DeferredPrepare) の使用が有効になっている レジストリ変数 DB2_DEFERRED_PREPARE_SEMANTICS=YES が設定されている DB2_DEFERRED_PREPARE_SEMANTICS=YES は Unicode,SBCS 環境でのみの推奨値 ISE Corporation

52 非型付きパラメーター マーカーのデータタイプ判別テスト 以下の SQLをDB2_DEFERRED_PREPARE_SEMANTICSと deferpreparesの設定を変えて実行 SQL:values? ( パラメータマーカーにはsetStringで "111" を設定 ) OK エラー エラー C: work>java test5 Prepare! SQL : values? Execute! DATA : C: work>java test5 SQL : values? Prepare! Execute! ステートメントで型なしパラメーター マーカーが使用されているか 無効な NULL 値が含まれています. SQLCODE=-418, SQLSTATE=42610, DRIVER= C: work>java test5 SQL : values? Prepare! DB2_DEFERRED_PREPARE_SEMANTICS=YES deferprepares=true この二つのパラメーターが設定されている場合には パラメーター マーカーの入力値から データタイプが判別されて SQL の実行が成功します DB2_DEFERRED_PREPARE_SEMANTICS=NO deferprepares=true パラメーター マーカーの入力値からのデータタイプ判別が行われずエラーとなります DB2_DEFERRED_PREPARE_SEMANTICS=YES deferprepares=false deferprepares=falseによりprepareが先に行われるためパラメーター マーカーの入力値が使用できずエラーとなります ステートメントで型なしパラメーター マーカーが使用されているか 無効な NULL 値が含まれています. SQLCODE=-418, SQLSTATE=42610, DRIVER= ISE Corporation

53 Public Synonym(ALIAS の機能拡張 ) V9.7 から Public Aliasを使用できる スキーマなしでの呼び出しでCURRENT SCHEMAと関係なくオブジェクトの参照が可能 1ステートメント (CREATE PUBLIC ALIAS) で全てのユーザーにオブジェクトが公開できる Aliasを付けられるオブジェクトが増えた V9.7 New Alias (PrivateまたはPublic) ニックネーム モジュール シーケンス 表 ビュー CREATE [PUBLIC] SYNONYM 文を使用して作成可能 V9.5 まで Private Aliasのみ使用可能 Alias を付けられるオブジェクト 他の Private Alias ニックネーム 表 またはビューに対して別名を定義可能 ISE Corporation

54 Public Synonym(Public Alias) 使用例 他のユーザーから参照 CREATE PUBLIC SYNONYM 文を使用して Public Aliasを作成作成し 他のユーザーから Public Aliasを参照参照しています Public Alias 作成 values current schema DB2ADMIN 1 レコードが選択されました create table t1 (c1 char(20)) grant all on t1 to public insert into t1 values ('db2admin') create public synonym p_t1 for t1 values current schema USER1 1 レコードが選択されました select * from p_t1 C db2admin 1 レコードが選択されました create table p_t1(c1 char(20)) insert into p_t1 values ('user1') select * from p_t1 C user1 1 レコードが選択されました スキーマ指定なしで呼び出すと Public Alias が参照される Public Alias と同じオブジェクトがスキーマ内にある場合はスキーマないのオブジェクトが参照される DB2ADMIN スキーマの T1 表の Public Alias P_T1 を作成 select * from syspublic.p_t1 C db2admin 1 レコードが選択されました Public Alias は SYSPUBLIC スキーマに作成されます 明示的にスキーマを指定して Public Alias にアクセスすることも可能 ISE Corporation

55 作成済み一時表 V9.7 より作成済作成済み一時表一時表が使用可能 CREATE GLOBAL TEMPORARY TABLE 文で作成済み一時表を作成 作成された定義はカタログに格納され 複数のセッションからの定義定義の共有が可能 V9.5までは 宣言済み一時表の使用のみ可能 作成済み一時表と宣言済み一時表の比較 作成ステートメント定義データへのアクセスデータの永続性スキーマロッキング リカバリー表スペース索引表特権 作成済み一時表 CREATE GLOBAL TEMPORARY TABLE 文 カタログに格納される 一度作成しておけば使用時の宣言や作成は不要 セッション毎に独立してアクセス SQL によるアクセスが可能 セッション内のみで有効 コミット毎に行の削除または残す設定が可能 指定可能 ロッキング リカバリーは行われません LOGGED が指定されていれば ローバックは可能 ユーザー一時表スペース 索引追加可能 表と同じ表スペースに配置 表特権の付与 取り消しが可能 宣言済み一時表 DECLARE GLOBAL TEMPORARY TABLE 文カタログには格納されず セッション毎に宣言し 作成が必要セッション毎に独立してアクセス SQLによるアクセスが可能セッション内のみで有効コミット毎に行の削除または残す設定が可能 SESSIONスキーマに作成されるロッキング リカバリーは行われません LOGGEDが指定されていれば ローバックは可能ユーザー一時表スペース索引追加可能 表と同じ表スペースに配置なし ISE Corporation

56 作成済み一時表使用例 作成済み一時表一時表を作成作成し 複数のセッションセッションからから使用 作成済み一時表の作成 C: >db2 -tvf temptable.sql create user temporary tablespace usertemp create global temporary table temptab1 (c1 int,c2 int) in usertemp on commit preserve rows logged list tables 表 / ビュースキーマタイプ作成時刻 TEMPTAB1 DB2ADMIN G レコードが選択されました 作成済み一時表はカタログに定義が格納されているため LIST TABLES にも出力される タイプは G になる セッション A から作成済み一時表を使用 C: >db2 connect to test1 user db2admin using db2admin データベース接続情報 データベース サーバー = DB2/NT SQL 許可 ID = DB2ADMIN ローカル データベース別名 = TEST1 C: >db2 "insert into temptab1 values (1,1)" C: >db2 "select * from temptab1" C1 C レコードが選択されました セッション B から作成済み一時表を使用 C: >db2 connect to test1 user db2admin using db2admin データベース接続情報 データベース サーバー = DB2/NT SQL 許可 ID = DB2ADMIN ローカル データベース別名 = TEST1 C: >db2 "insert into temptab1 values (2,2)" C: >db2 "select * from temptab1" C1 C レコードが選択されました セッション毎にデータは独立しています ISE Corporation

57 ROWNUM 擬似列 行番号をを返す擬似列を使用できる 行番号による絞込みなどに使用されます 例 :20 行目から 30 行目までを返す DB2_COMPATIBILITY_VECTOR=001 でこの機能を有効にします ROWNUM を ROW_NUMBER() OVER() の同義語として使用することを可能にします データベース作成後でも有効にできます ビット位置 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 のコンパイル ISE Corporation

58 ROWNUM の使用例 10 行目までの行を返す 5 行目から 10 行目までの行を返す C: >db2 "select id,name from staff where rownum <= 10" C: >db2 "select id,name from staff where rownum between 5 and 10" ID NAME Sanders 20 Pernal 30 Marenghi 40 O'Brien 50 Hanes 60 Quigley 70 Rothman 80 James 90 Koonitz 100 Plotz 10 レコードが選択されました 10 行目以下までを選択 ID NAME Hanes 60 Quigley 70 Rothman 80 James 90 Koonitz 100 Plotz 6 レコードが選択されました 5 行目から 10 行目までを選択 Explain を取得して Optimized Statement を確認 Explain を取得して Optimized Statement を確認すると ROWNUMBER() OVER() に REWRITE されているのがわかります Optimized Statement: SELECT Q3.$C0 AS "ID", Q3.$C1 AS "NAME" FROM (SELECT Q2.$C0, Q2.$C1, ROWNUMBER() OVER () FROM (SELECT Q1."ID", Q1."NAME" FROM SOTA.STAFF AS Q1) AS Q2) AS Q3 WHERE (Q3.$C2 <= 10) AND (5 <= Q3.$C2) ISE Corporation

59 外部結合演算子 (+) 外部結合演算子 (+) を使用使用してして外部結合外部結合できますできます DB2_COMPATIBILITY_VECTOR=004 でこの機能機能を有効有効にします データベース作成後作成後でもでも有効有効にできます ビット位置 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 のコンパイル ISE Corporation

60 外部結合演算子 (+) 使用例 外部結合を行う結合条件結合条件に外部結合演算子 (+) を付ける C: >db2 "select deptno,deptname,empno from dept,emp where deptno = workdept(+) (+)" DEPTNO DEPTNAME EMPNO A00 SPIFFY COMPUTER SERVICE DIV B01 PLANNING 省略 E21 SOFTWARE SUPPORT E21 SOFTWARE SUPPORT H22 BRANCH OFFICE H2 - I22 BRANCH OFFICE I2 - G22 BRANCH OFFICE G2 - D01 DEVELOPMENT CENTER - F22 BRANCH OFFICE F2 - J22 BRANCH OFFICE J2 - Explain を取得し Optimized Statement を参照すると OUTER JOIN 句を使用した文に REWRITE されている 48 レコードが選択されました Optimized Statement: 外部結合により 結合条件に合致しない行も表示 SELECT Q2."DEPTNO" AS "DEPTNO", Q2."DEPTNAME" AS "DEPTNAME", Q1."EMPNO" AS "EMPNO" FROM SOTA.EMPLOYEE AS Q1 RIGHT OUTER JOIN SOTA.DEPARTMENT AS Q2 ON (Q2."DEPTNO" = Q1."WORKDEPT") ISE Corporation

61 階層照会 CONNECT BY 節を使用した階層照会のサポートを有効にします DB2_COMPATIBILITY_VECTOR=008 でこの機能を有効にします データベース作成後でも有効にできます ビット位置 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 のコンパイル ISE Corporation

62 階層照会使用例 以下の句を指定指定してして取り出す階層階層を指定指定する START WITH 句でルートになる条件を指定 CONNECT BY 句で親子の関係を表す条件を指定 PRIOR 演算子を使用して親の列を参照できる 階層照会ではでは以下以下のようなのような擬似列擬似列 関数関数の使用使用が可能 LEVEL 擬似列 階層照会でこの行のルートからの階層数を返す SYS_CONNECT_BY_PATH 関数 階層照会でルート行からこの行までのパスを表すストリングを作成 Department 表に対して階層照会を実施 Department 表データ 上位の部門コード DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION A00 SPIFFY COMPUTER SERVICE DIV A00 - B01 PLANNING A00 - C01 INFORMATION CENTER A00 - D01 DEVELOPMENT CENTER - A00 - D11 MANUFACTURING SYSTEMS D01 - D21 ADMINISTRATION SYSTEMS D01 - E01 SUPPORT SERVICES A00 - E11 OPERATIONS E01 - E21 SOFTWARE SUPPORT E01 - F22 BRANCH OFFICE F2 - E01 - 省略 select deptno, deptname, level,char(sys_connect_by_path sys_connect_by_path(deptno,'/'),30) hierarchy from department start with deptno = 'A00' connect by prior deptno = admrdept and deptno!='a00' Department 表の階層イメージ DEPTNO DEPTNAME LEVEL HIERARCHY A00 SPIFFY COMPUTER SERVICE DIV. 1 /A00 B01 PLANNING 2 /A00/B01 C01 INFORMATION CENTER 2 /A00/C01 D01 DEVELOPMENT CENTER 2 /A00/D01 D11 MANUFACTURING SYSTEMS 3 /A00/D01/D11 D21 ADMINISTRATION SYSTEMS 3 /A00/D01/D21 E01 SUPPORT SERVICES 2 /A00/E01 E11 OPERATIONS 3 /A00/E01/E11 E21 SOFTWARE SUPPORT 3 /A00/E01/E21 省略 B01 C01 D01 D11 D21 A00 E01 E11 E ISE Corporation

63 データタイプの互換性 Oracle 固有のデータタイプ NUMBER,VARCHAR2,DATE タイプの使用使用を可能可能にします DB2_COMPATIBILITY_VECTOR NUMBER データ タイプタイプの互換性互換性は 010 データベース作成時に決定され 作成後は変更できません ビット位置 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 のコンパイル ISE Corporation

64 データタイプの互換性 NUMBER,VARCHAR2,DATEの互換性 Oracleがサポートしている以下のデータタイプとの互換性をサポート DATE NUMBER VARCHAR2 データタイプの互換性互換性を有効有効にするにはにするには DB2_COMPATIBILITY_VECTORの設定設定が必要 データベース作成時に設定が行われている必要がある データベース作成後は変更不可 以下のデータベース構成パラメーターの以下の項目で互換性サポートを有効化を確認できる NUMBER データ タイプの互換性 VARCHAR2 データ タイプの互換性 データ タイプ DATE の TIMESTAMP(0) への互換性 確認例 C: >db2 get db cfg for sample find " 互換性 " NUMBER データ タイプの互換性 VARCHAR2 データ タイプの互換性 = ON = ON データ タイプ DATE の TIMESTAMP(0) への互換性 = ON ISE Corporation

65 TRUNCATE TABLE 代替セマンティクス TRUNCATE ステートメント用の代替セマンティクスを有効にします IMMEDIATE がオプションのキーワードであり 指定がない場合にデフォルトになります TRUNCATE ステートメントが論理作業単位内の最初のステートメントではない場合 TRUNCATE ステートメントが実行される前に暗黙的なコミット操作が実行されます DB2_COMPATIBILITY_VECTOR=080 でこの機能を有効にします データベース作成後でも有効にできます ビット位置 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 のコンパイル ISE Corporation

66 TRUNCATE TABLE 代替セマンティクス この機能機能を有効有効にすると TRUNCATE TABLE 文の以下以下の 2 つの動作動作が変わる 1.IMMEDIATE キーワードが必須ではなくなり 以下のような呼び出しが可能 TRUNATE TABLE テーブル名 TRUNCATE TABLE 構文.-TABLE-..-DROP STORAGE--. >>-TRUNCATE table-name > '-REUSE STORAGE-' Oracle と同じ構文で呼び出しができる.-IGNORE DELETE TRIGGERS > > '-RESTRICT WHEN DELETE TRIGGERS-'.-CONTINUE IDENTITY-. > IMMEDIATE >< IMMEDIATE キーワードがオプションになる 2. トランザクション中でも TRUNCATE TABLE 文実行前に暗黙的なコミットが行われ TRUNCATE TABLE 文が実行される この機能が無効の場合には トランザクション中に TRUNCATE TABLE を発行するとエラーとなる (SQL0428N) ISE Corporation

67 TRUNCATE TABLE 実行例 DB2_COMPATIBILITY_VECTOR=080 の場合 C: >db2 truncate table t1 C: >db2 +c insert into t1 values (1,1) C: >db2 truncate table t1 トランザクション中でも実行可能 ON IMMEDIATE が必須のキーワードのため文法エラー トランザクション中に実行すると SQL0428 エラーでステートメントは失敗する DB2_COMPATIBILITY_VECTOR 設定なしの場合 C: >db2 truncate table t1 OFF DB21034E コマンドが 有効なコマンド行プロセッサー コマンドでないため SQLステートメントとして処理されました SQL 処理中に そのコマンドが返されました SQL0104N "truncate table T1" に続いていて予期予期しないしないトークン "END-OF OF-STATEMENT" が見つかりましたつかりました 予期予期されたされたトークントークンに "IMMEDIATE IMMEDIATE" が含まれているまれている可能性可能性がありますがあります SQLSTATE=42601 C: >db2 truncate table t1 immediate C: >db2 +c insert into t1 values (1,1) C: >db2 truncate table t1 immediate DB21034E コマンドが 有効なコマンド行プロセッサー コマンドでないため SQLステートメントとして処理されました SQL 処理中に そのコマンドが返されました SQL0428N SQL ステートメントは 作業単位作業単位の最初最初のステートメントとしてのみとしてのみ許可許可されています SQLSTATE= ISE Corporation

68 文字リテラルの互換性 バイト長が 254 以下であるである文字定数文字定数および GRAPHIC ストリング定数定数に CHAR データ タイプまたは GRAPHIC データ タイプタイプ (VARCHAR データ タイプタイプでも VARGRAPHIC データ タイプタイプでもない ) を割り当てることをてることを可能可能にしますにします DB2_COMPATIBILITY_VECTOR=100 でこの機能機能を有効有効にします データベース作成後でも有効にできます ビット位置 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 のコンパイル ISE Corporation

69 文字リテラルの互換性 254 文字以下の文字文字リテラルリテラルを CHAR として扱う Oracleでは 文字リテラルはCHARとして扱われる OracleでもCHARの比較は空白埋め比較セマンティクスが使用される C: >db2 describe values 'A' 列情報 列の数 : 1 SQL タイプタイプ長列名名前長 CHARACTER C: >db2 "select 'TRUE' from dual where 'A' = 'A '" TRUE VARCHAR2 データ タイプタイプの互換性 =ON または文字リテラルリテラルの互換性 =ON リテラルのタイプは CHAR C: >db2 describe values 'A' 列情報 列の数 : 1 SQL タイプタイプ長列名名前長 VARCHAR C: >db2 "select 'TRUE' from sysibm.sysdummy1 where 'A' = 'A '" TRUE VARCHAR2 データ タイプタイプの互換性 =OFF リテラルのタイプは VARCHAR 1 レコードが選択されました 1 レコードが選択されました ISE Corporation

70 コレクション メソッド 配列で first last next previous などの演算演算を実行実行するするメソッドメソッドの使用使用を可能可能にしますにします また 配列内配列内の特定特定の要素要素の参照参照に 大括弧大括弧の代わりにわりに括弧括弧を使用使用できるようにしますできるようにします 例えば array1(i) は array1 の要素 i を参照参照しますします DB2_COMPATIBILITY_VECTOR=200 でこの機能機能を有効有効にします データベース作成後でも有効にできます ビット位置 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 のコンパイル ISE Corporation

71 コレクション メソッド PL/SQL コレクションとは 同じデータ タイプを持つ 配列されたデータ エレメントの集合 その集合内の個々のデータ項目を 括弧を使用した添字表記法を使用して参照できます VARRAY 連想配列 コレクションメソッド コレクションの情報を参照したり 変更を行う機能 以下のようなメソッドがあります コレクション メソッド COUNT DELETE DELETE (n) DELETE (n1, n2) EXISTS (n) EXTEND EXTEND (n) EXTEND (n1, n2) FIRST LAST LIMIT NEXT (n) PRIOR (n) TRIM TRIM (n) 説明コレクション内のエレメントの数を返します コレクションからすべてのエレメントを削除します 連想配列からエレメント n を削除します VARRAY コレクション タイプから個々のエレメントを削除することはできません n1 から n2 までのエレメントすべてを 連想配列から削除します VARRAY コレクション タイプから個々のエレメントを削除することはできません 指定したエレメントが存在する場合は TRUE を返します コレクションに NULL エレメントを 1 つだけ付加します コレクションに NULL エレメントを n 個付加します コレクションに n2 番目のエレメントのコピーを n1 個付加します コレクション内にある最小の索引番号を返します コレクション内にある最大の索引番号を返します VARRAY の場合にはエレメントの最大数 ネストした表の場合には NULL を返します 指定したエレメントの直後に位置するエレメントの索引番号を返します 指定したエレメントの直前に位置するエレメントの索引番号を返します コレクションの末尾から エレメントを 1 つだけ削除します 連想配列コレクション タイプからエレメントをトリムすることはできません コレクションの末尾から エレメントを n 個削除します 連想配列コレクション タイプからエレメントをトリムすることはできません ISE Corporation

72 データ ディクショナリー互換ビュー Oracle のデータ ディクショナリー互換ビューの作成を可能にします DB2_COMPATIBILITY_VECTOR=400 でこの機能を有効にします この機能を有効にするとデータベース作成時に 自動的に Oracle のデータ ディクショナリー互換ビューを作成されます データベース作成時に作成されるため デーベース作成時に設定されている必要があります ビット位置 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 のコンパイル ISE Corporation

73 データ ディクショナリー互換ビュー 右の表にあるビューが作成される それぞれのビューには Public Alias が作成されているため スキーマ指定なしでも呼び出し可能 ネーミング ルール USER_* ユーザーのオブジェクトを表示 ALL_* ユーザーがアクセス可能なオブジェクトを表示 DBA_* 全てのオブジェクトを表示 右のオブジェクト以外にも以下のオブジェクトも作成される TABS :USER_TABLES の ALIAS IND :USER_INDEXES の ALIAS COLS :USER_TAB_COLUMNS の ALIAS SIN :USER_SYNONYMS の ALIAS SEQ :USER_SEQUENCES の ALIAS DICT :DICTIONARY の ALIAS OBJ :USER_OBJECTS の ALIAS CAT :USER_CATALOGのALIAS TAB : ユーザーのテーブルを表示 ( スキーマ名と テーブル名とテーブルタイプのみの列を持つ ) カテゴリー 一般 表またはビュー プログラミング オブジェクト セキュリティー 定義されているされているビュー DICTIONARY DICT_COLUMNS USER_CATALOG DBA_CATALOG ALL_CATALOG USER_DEPENDENCIES DBA_DEPENDENCIES ALL_DEPENDENCIES USER_OBJECTS DBA_OBJECTS ALL_OBJECTS USER_SEQUENCES DBA_SEQUENCES ALL_SEQUENCES USER_TABLESPACES DBA_TABLESPACES USER_CONSTRAINTS DBA_CONSTRAINTS ALL_CONSTRAINTS USER_CONS_COLUMNS DBA_CONS_COLUMNS ALL_CONS_COLUMNS USER_INDEXES DBA_INDEXES ALL_INDEXES USER_IND_COLUMNS DBA_IND_COLUMNS ALL_IND_COLUMNS USER_TAB_PARTITIONS DBA_TAB_PARTITIONS ALL_TAB_PARTITIONS USER_PART_TABLES DBA_PART_TABLES ALL_PART_TABLES USER_PART_KEY_COLUMNS DBA_PART_KEY_COLUMNS ALL_PART_KEY_COLUMNS USER_SYNONYMS DBA_SYNONYMS ALL_SYNONYMS USER_TABLES DBA_TABLES ALL_TABLES USER_TAB_COMMENTS DBA_TAB_COMMENTS ALL_TAB_COMMENTS USER_TAB_COLUMNS DBA_TAB_COLUMNS ALL_TAB_COLUMNS USER_COL_COMMENTS DBA_COL_COMMENTS ALL_COL_COMMENTS USER_TAB_COL_STATISTICS DBA_TAB_COL_STATISTICS ALL_TAB_COL_STATISTICS USER_VIEWS DBA_VIEWS ALL_VIEWS USER_VIEW_COLUMNS DBA_VIEW_COLUMNS ALL_VIEW_COLUMNS USER_PROCEDURES DBA_PROCEDURES ALL_PROCEDURES USER_SOURCE DBA_SOURCE ALL_SOURCE USER_TRIGGERS DBA_TRIGGERS ALL_TRIGGERS USER_ERRORS DBA_ERRORS ALL_ERRORS USER_ARGUMENTS DBA_ARGUMENTS ALL_ARGUMENTS USER_ROLE_PRIVS DBA_ROLE_PRIVS ROLE_ROLE_PRIVS SESSION_ROLES USER_SYS_PRIVS DBA_SYS_PRIVS ROLE_SYS_PRIVS SESSION_PRIVS USER_TAB_PRIVS DBA_TAB_PRIVS ALL_TAB_PRIVS ROLE_TAB_PRIVS USER_TAB_PRIVS_MADE ALL_TAB_PRIVS_MADE USER_TAB_PRIVS_RECD ALL_TAB_PRIVS_RECD DBA_ROLES ISE Corporation

74 データディクショナリー互換ビュー使用例 データディクショナリーの 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 DB2ADMIN EMP_PHOTO TABLE DB2ADMIN EMP_RESUME TABLE DB2ADMIN PROJECT TABLE DB2ADMIN IN_TRAY TABLE DB2ADMIN ORG TABLE DB2ADMIN STAFF TABLE DB2ADMIN SALES TABLE DB2ADMIN EMP_ACT TABLE DB2ADMIN STAFFG TABLE 12 レコードが選択されました データディクショナリーの 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 SQL XVIP CUSTOMER CUST_NAME_XMLIDX XVIL CUSTOMER SQL XVIP CUSTOMER CUST_CID_XMLIDX XVIL CUSTOMER PK_CUSTOMER REG CUSTOMER SQL XPTH CUSTOMER SQL XPTH CUSTOMER 12 レコードが選択されました ISE Corporation

75 PL/SQL のコンパイル PL/SQLのコンパイルコンパイルを可能可能にしますにします DB2_COMPATIBILITY_VECTOR=800 でこの機能機能を有効有効にします データベース作成後でも有効にできます ビット位置 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 のコンパイル ISE Corporation

76 DB2_COMPATIBILITY_VECTOR=800 の設定 DB2_COMPATIBILITY_VECTOR=800を設定設定することで PL/SQLを処理処理できるようになる DB2_COMPATIBILITY_VECTOR=800を設定 C: >db2set DB2_COMPATIBILITY_VECTOR=800 DB2_COMPATIBILITY_VECTOR=800 設定なし C: work>db2 -tvf plsqltest2.sql!db2set findstr DB2_COMPATIBILITY_VECTOR set sqlcompat plsql DB20000I SET SQLCOMPAT コマンドが正常に完了しました set serveroutput on DB20000I SET SERVEROUTPUT コマンドが正常に完了しました declare v1 varchar(100) := 'PL/SQL Test'; begin dbms_output.put_line(v1); end; PL/SQL ブロックの処理に失敗 DB21034E コマンドが 有効なコマンド行プロセッサー コマンドでないため SQLステートメントとして処理されました SQL 処理中に そのコマンドが返されました SQL0104N "BEGIN-OF-STATEMENT" に続いて予期しないトークン "declare v1 varchar" が見つかりました 予期されたトークンに "<values>" が含まれている可能性があります LINE NUMBER=2. SQLSTATE=42601 C: >db2stop force :37: SQL1064N DB2STOP の処理が正常に終了しました SQL1064N DB2STOP の処理が正常に終了しました C: >db2start :37: SQL1063N DB2START の処理が正常に終了しました SQL1063N DB2START の処理が正常に終了しました C: work>db2 -tvf plsqltest2.sql!db2set findstr DB2_COMPATIBILITY_VECTOR DB2_COMPATIBILITY_VECTOR=800 set sqlcompat plsql DB20000I SET SQLCOMPAT コマンドが正常に完了しました set serveroutput on DB20000I SET SERVEROUTPUT コマンドが正常に完了しました declare v1 varchar(100) := 'PL/SQL Test'; begin dbms_output.put_line(v1); end; PL/SQL Test DB2_COMPATIBILITY_VECTOR=800 設定あり PL/SQL ブロックが正常に処理処理される ISE Corporation