Comments
Description
Transcript
DB2 V9.7新機能ワークショップ Oracle互換(前半)
DB2 V9.7新機能ワークショップ <第1.00版 2009年 7月> Oracle互換(前半) 本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。 この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合す る使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまた は同様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。 © Copyright IBM Japan Systems Engineering Co., Ltd. 2009 内容 • Oracle互換機能強化概要 互換機能強化概要 • DB2_COMPATIBILITY_VECTOR • データタイプの データタイプの互換性 • スカラー関数 スカラー関数の 関数の機能強化 • ビルトイン・ ビルトイン・パッケージ・ パッケージ・ライブラリー • PL/SQLサポート サポート • パッケージ( パッケージ(モジュール) モジュール)のサポート • 参考資料 2 © 2009 ISE Corporation Oracle互換機能強化概要 Oracle互換機能強化概要 3 © 2009 ISE Corporation Oracle互換機能の強化により移行が容易に Oracle固有の機能や動作 Oracle SQL・ ・データ型 データ型 PL/SQL ビルトイン関数 ビルトイン関数 ビルトイン・ ビルトイン・パッケージ Concurrency Control SQL*Plus Scripts ほとんどそのまま 理解できる 理解できる。 できる。 ほとんどそのまま動 ほとんどそのまま動く Your Applications Oracle Database 4 DB2 Database © 2009 ISE Corporation 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 • • • • 5 作成済み一時表 NEXTVAL/CURRVAL(シーケンスの呼び出し方法) MINUS(集合演算子 EXCEPTのAlias) Unnamed inline views © 2009 ISE Corporation PL/SQLサポート概要 • • • 6 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 © 2009 ISE Corporation DB2_COMPATIBILITY_VECTOR DB2_COMPATIBILITY_VECTOR 7 © 2009 ISE Corporation 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環境でのみの 推奨値となります。 以上により、こちらが それぞれの環境での推奨値 8 Unicode,SBCS環境 ・DB2_COMPATIBILITY_VECTOR=ORA ・DB2_DEFERRED_PREPARE_SEMANTICS=YES それ以外の環境 ・DB2_COMPATIBILITY_VECTOR=ORA ・DB2_DEFERRED_PREPARE_SEMANTICS=NO 暗黙的に設定されま すが明示指定される ことをお勧めします © 2009 ISE Corporation DB2_COMPATIBILITY_VECTORで設定可能なフィーチャー ビット位置 互換性フィーチャー 説明 1 (0x01) ROWNUM 2 (0x02) 3 (0x04) 未使用 外部結合演算子 ROWNUM を ROW_NUMBER() OVER() の同義語として使用することを可能にし、 ROWNUM を SQL ステートメントの WHERE 節に含めることを許可します。 未使用 外部結合演算子 (+) のサポートを有効にします。 4 (0x08) 階層照会 CONNECT BY 節を使用した階層照会のサポートを有効にします。 5 (0x10) NUMBER データ・タイプ NUMBER データ・タイプおよび関連する数値処理を有効にします。 6 (0x20) VARCHAR2 データ・タイプ VARCHAR2 データ・タイプおよび関連する文字ストリング処理を有効にします。 7 (0x40) DATE データ・タイプ DATE データ・タイプを日付と時間の値を結合した TIMESTAMP(0) として使用するこ とを可能にします。 8 (0x80) TRUNCATE TABLE TRUNCATE ステートメント用の代替セマンティクスを有効にします。IMMEDIATE がオ プションのキーワードであり、指定がない場合にデフォルトになります。TRUNCATE ステートメントが論理作業単位内の最初のステートメントではない場合、TRUNCATE ステートメントが実行される前に暗黙的なコミット操作が実行されます。 バイト長が 254 以下である文字定数および GRAPHIC ストリング定数に、CHAR デー 9 (0x100) 文字リテラル タ・タイプまたは GRAPHIC データ・タイプ (VARCHAR データ・タイプでも VARGRAPHIC データ・タイプでもない) を割り当てることを可能にします。 10 (0x200) コレクション・メソッド 配列で first、last、next、previous などの演算を実行するメソッドの使用を可能にしま す。また、配列内の特定の要素の参照に、大括弧の代わりに括弧を使用できるよう にします。例えば、array1(i) は array1 の要素 i を参照します。 11 (0x400) データ・ディクショナリー互換ビュー データ・ディクショナリー互換ビューの作成を可能にします。 12 (0x800) PL/SQL のコンパイル 9 PL/SQL ステートメントおよび言語要素のコンパイルと実行を可能にします。 © 2009 ISE Corporation 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 2009-06-30 09:29:08 0 0 SQL1064N DB2STOP の処理が正常に終了しました。 SQL1064N DB2STOP の処理が正常に終了しました。 C:¥work>db2start 2009-06-30 09:29:13 0 0 SQL1063N DB2START の処理が正常に終了しました。 SQL1063N DB2START の処理が正常に終了しました。 C:¥work>db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES DB2_COMPATIBILITY_VECTOR=ORA DB2INSTOWNER=・・・ ・・・ 10 © 2009 ISE Corporation データタイプの互換性 データタイプの互換性 11 © 2009 ISE Corporation データタイプの互換性 • NUMBER,VARCHAR2,DATEの の互換性 • Oracleからの移行時のデータタイプのマッピングやDDLの変更を最小限できる • Oracleがサポートしている以下のデータタイプとの互換性をサポート • • • • DATE NUMBER VARCHAR2 データタイプの の設定が データタイプの互換性を 互換性を有効にするには 有効にするには、 にするには、DB2_COMPATIBILITY_VECTORの 設定が必要 • データベース作成時に設定が行われている必要がある、データベース作成後は変更不可 • 以下のデータベース構成パラメーターの以下の項目で互換性サポートを有効化を確認できる • NUMBER データ・タイプの互換性 • VARCHAR2 データ・タイプの互換性 • データ・タイプ DATE の TIMESTAMP(0) への互換性 確認例 C:¥>db2 get db cfg for sample 12 | find "互換性" NUMBER データ・タイプの互換性 = ON VARCHAR2 データ・タイプの互換性 = ON データ・タイプ DATE の TIMESTAMP(0) への互換性 = ON © 2009 ISE Corporation DATEデータタイプの互換性 • 「データ・ データ・タイプ DATE の TIMESTAMP(0) への互換性 への互換性」 互換性」がON • DATEタイプがTIMESTAMP(0)として扱われる • OracleのDATEとの互換 • OracleのDATEは、秒までの精度を持つ データ・タイプ DATE の TIMESTAMP(0) への互換性=OFF create table tab1(c1 date) DB20000I SQL コマンドが正常に完了しました。 データ・タイプ DATE の TIMESTAMP(0) への互換性=ON describe table tab1 create table tab1(c1 date) DB20000I SQL コマンドが正常に完了しました。 Date型でテーブルを作成すると Timestamp(0)として定義される describe table tab1 データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL ------------- --------- ------------------- ---------- ----- -----C1 SYSIBM DATE 4 0 はい データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL --------- --------- ------------------- ---------- ----- -----C1 SYSIBM TIMESTAMP 7 0 はい 1 レコードが選択されました。 1 レコードが選択されました。 insert into tab1 values current date DB20000I SQL コマンドが正常に完了しました。 insert into tab1 values current date DB20000I SQL コマンドが正常に完了しました。 select * from tab1 select * from tab1 C1 ---------2009-04-22 1 レコードが選択されました。 13 出力形式(精度)が異なる C1 ------------------2009-04-22-13.39.31 1 レコードが選択されました。 © 2009 ISE Corporation 参考)TIMESTAMPの精度指定 • TIMESTAMPの の精度の 精度の指定が 指定が可能 • 互換機能(DB2_COMPATIBILITY_VECTOR)に関係なくV9.7から使用可能 • 小数点以下の秒数の精度を指定 • • • • 0から12までを指定可能(デフォルト:6) OracleのTimestamp型との互換 ← OracleのTimestampでも精度指定可能(指定可能な桁数 0-9) より高い精度を持つTIMESTAMP型の使用 低い精度を指定することでスペースの節約 values current 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 ) DB20000I SQL コマンドが正常に完了しました。 精度の指定により列の長さ が変わる 精度を短くとることでスペー スの節約が可能 1 -------------------------2009-04-22-18.53.07.156000 1 レコードが選択されました。 describe table tab1 列名 -------------C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 データ・タイ データ・ 列の スケ プ・スキーマ タイプ名 長さ ール NULL --------- ------------------- ---------- ----- -----SYSIBM TIMESTAMP 7 0 はい SYSIBM TIMESTAMP 8 1 はい SYSIBM TIMESTAMP 8 2 はい SYSIBM TIMESTAMP 9 3 はい SYSIBM TIMESTAMP 9 4 はい SYSIBM TIMESTAMP 10 5 はい SYSIBM TIMESTAMP 10 6 はい SYSIBM TIMESTAMP 11 7 はい SYSIBM TIMESTAMP 11 8 はい SYSIBM TIMESTAMP 12 9 はい SYSIBM TIMESTAMP 12 10 はい SYSIBM TIMESTAMP 13 11 はい SYSIBM TIMESTAMP 13 12 はい SYSIBM TIMESTAMP 10 6 はい 14 レコードが選択されました。 14 CURRENT TIMESTAMP特殊 レジスターも精度指 定可能 values current timestamp(0) 1 ------------------2009-04-22-18.53.07 1 レコードが選択されました。 values current timestamp(12) 1 -------------------------------2009-04-22-18.53.07.171000000000 1 レコードが選択されました。 © 2009 ISE Corporation NUMBER データ・タイプの互換性 • 「NUMBERデータ データ・ タイプの互換性」 互換性」がON データ・タイプの • 表定義時の列タイプやプロシージャー内の変数タイプなどでNUMBER型を使用する ことが出来る • 実際には以下のような型として作成される • NUMBER -> DECFLOAT(16) • NUMBER(p)->DECIMAL(p) • NUMBER(p,s)->DECIMAL(p,s) • 精度(p)は31まで NUMBERデータ データ・ データ・タイプの タイプの 互換性= 互換性=ON create table tab1(c1 number,c2 number(10),c3 number(10,5)) number number number DB20000I SQL コマンドが正常に完了しました。 DDLでNUMBERを使用することが出来ます。 describe table tab1 データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL ----------------- --------- ------------------- ---------- ----- -----C1 SYSIBM DECFLOAT 8 0 はい C2 SYSIBM DECIMAL 10 0 はい C3 SYSIBM DECIMAL 10 5 はい 3 レコードが選択されました。 精度を付けないNUMBER型でテーブルを作成すると DECFLOAT(16)型として定義されます。 精度、位取りを付けたNUMBER型はDECIMALとして 定義されます。 15 © 2009 ISE Corporation VARCHAR2 データ・タイプの互換性 • 「VARCHAR2データ データ・ タイプの互換性」 互換性」がON データ・タイプの • 表定義時の列タイプやプロシージャー内の変数タイプなどでVARCHAR2型を使用 することが出来る • 実際には、VARCHARとして作成される • 空文字('')がNULLとして扱われる • VARCHARの比較には非空白埋め比較セマンティクスが使用される • 長さが最大 254 バイトまでの文字ストリング・リテラルのデータ・タイプは CHARになり、 254以上はVARCHARになる create table tab1(c1 varchar2(10),c2 varchar2(10) for bit data) varchar2 varchar2 DB20000I SQL コマンドが正常に完了しました。 describe table tab1 VARCHAR2データ データ・ データ・タイプの タイプの 互換性=ON 互換性 DDLでVARCHAR2が使用できる データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL --------------- --------- ------------------- ---------- ----- -----C1 SYSIBM VARCHAR 10 0 はい C2 SYSIBM VARCHAR 10 0 はい 2 レコードが選択されました。 16 Varchar2型でテーブルを作成す るとVarcharとして定義される © 2009 ISE Corporation VARCHAR2 データ・タイプの互換性 ON/OFFの違い • 空文字( )の扱いとVARCHARの の末尾ブランク 空文字('') 末尾ブランクの ブランクの比較の違いについて確認 比較 VARCHAR2 データ・タイプの互換性=OFF VARCHAR2 データ・タイプの互換性=ON C:¥work¥asnclp>db2 -tvf varchar2test.sql create table tab1(c1 varchar(10)) DB20000I SQL コマンドが正常に完了しました。 C:¥work¥asnclp>db2 -tvf varchar2test.sql create table tab1(c1 varchar(10)) DB20000I SQL コマンドが正常に完了しました。 insert into tab1 values (null),(''),('a'),('a ') DB20000I SQL コマンドが正常に完了しました。 insert into tab1 values (null),(''),('a'),('a ') DB20000I SQL コマンドが正常に完了しました。 select c1,hex(c1) from tab1 select c1,hex(c1) from tab1 C1 2 ---------- -------------------- C1 ---------a a a a 61 6120 空文字(’’) OFF:0バイトの文字 ON:NULL 4 レコードが選択されました。 2 -------------------61 6120 4 レコードが選択されました。 select c1,hex(c1) from tab1 where c1 = 'a' select c1,hex(c1) from tab1 where c1 = 'a' C1 ---------a a C1 2 ---------- -------------------a 61 2 -------------------61 VARHCHARの比較 6120 OFF:末尾ブランクは無視される 2 レコードが選択されました。 ON:末尾ブランクも含めて比較 17 1 レコードが選択されました。 © 2009 ISE Corporation 参考)非空白埋め比較セマンティクスと空白埋め比較セマンティクス • 非空白埋め 非空白埋め比較セマンティクス 比較セマンティクス • 以下の文字は異なるものとみなす OracleのVARCHARの比較 はこちらが使用される • 'A'、'A_'、'A__' ('_'は空白文字) • 空白埋め 空白埋め比較セマンティクス 比較セマンティクス VARCHAR2 データ・タイプの 互換性=ONの時のDB2の VARCHARの比較はこちらが 使用される • 空白文字をパティングして比較する • 末尾の空白文字の違いは無視される • 以下の文字は同じものとみなす • 'A'、'A_'、'A__' ('_'は空白文字) 18 VARCHAR2 データ・タイプの 互換性=OFFの時のDB2の VARCHARの比較はこちらが 使用される © 2009 ISE Corporation スカラー関数の機能強化 スカラー関数の機能強化 19 © 2009 ISE Corporation スカラー関数の機能強化 • Oracleの のスカラー関数 スカラー関数と 関数と同じ名前で 名前で同じ動作をする 動作をする関数 をする関数の 関数の追加 • Oracleからの移行の際に、これらの関数によりSQLの書き換えが必要なく なる(Oracleの全関数をサポートしていないため注意) • 以下のような 以下のような関数 のような関数を 関数を新規で 新規でサポートまたは サポートまたは機能拡張 または機能拡張 • 形式・型変換関連 • TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_NUMBER, TO_CLOB • 日付操作関連 • EXTRACT, ADD_MONTHS, … • 文字列操作関連 • INITCAP, RPAD, LPAD, INSTR, REVERSE・・・ • その他 • DECODE, NVL, LEAST, GREATEST, BITAND 20 © 2009 ISE Corporation 形式・型変換関連のスカラー関数の使用例 • 関数 • フォーマットストリングを使用し、フォーマットの変換を行いながら型変換を行える • • • • TO_NUMBER(数値型への変換) TO_CHAR(文字型への変換) TO_DATE(日付型への変換) TO_TIMESTAMP(タイムスタンプへの変換) • キャストとの違い • フォーマットの変換を行いながら型変換を行う • 例 • • • • '$123,456,789' (文字)→ 123456789(数値) 123456789 (数値)→ '$123,456,789' (文字) '09/05/20' (文字)→ 2009-05-20-00.00.00(タイムスタンプ) 2009-05-20-00.37.24.843000(タイムスタンプ) → '09/05/20' (文字) TO_NUMBER使用例 TO_CHAR使用例 C:¥>db2 values to_number('$123,456,789','$999,999,999') C:¥>db2 values to_char(123456789,'$999,999,999') 1 -----------------------------------------123456789 1 ------------------------------------------------$ 123,456,789 1 レコードが選択されました。 21 数値型に変換 1 レコードが選択されました。 文字型に変換 © 2009 ISE Corporation ビルトイン・パッケージ・ライブラリー ビルトイン・パッケージ・ライブラリー (ビルトイン・モジュール) (ビルトイン・モジュール) 22 © 2009 ISE Corporation ビルトイン・パッケージ・ライブラリー(ビルトイン・モジュール) • V9.7では ではアプリケーション 役立つビルトイン・ ビルトイン・パッケージ・ パッケージ・ライブラ ではアプリケーション開発 アプリケーション開発に 開発に役立つ リーを リーを多数提供 • パッケージ(モジュール)は、関数やプロシージャーや変数などをまとめ たオブジェクトでSQLから呼び出すことが可能です。(PL/SQLやSQL PLからも呼び出すことも可能) • SQLのみでは出来ないような処理(例:ファイル操作、メッセージの出力 など)を行うことができます。 • Oracleと と同じ名前で 名前で同じ機能を 機能を持つビルトイン・ ビルトイン・パッケージ・ パッケージ・ライブラリー を提供 • OracleのPL/SQLの開発では、ビルトイン・パッケージ・ライブラリーが 頻繁に利用されます。 • OracleのPL/SQLの移行の際に、使用されているビルトイン・パッケー ジ・ライブラリーがDB2 V9.7でも提供されていれば書き直しなしでの移 行も可能です。 23 © 2009 ISE Corporation ビルトイン・パッケージ・ライブラリー(ビルトイン・モジュール) モジュール名 モジュール名 説明 DBMS_ALERT DBMS_ALERT モジュールは、アラートの登録、アラートの送受信を行うための一連のプロシージャーを備えてい ます。 DBMS_JOB DBMS_JOB モジュールは、ジョブの作成、スケジューリング、および管理のためのプロシージャーを提供します。 DBMS_LOB DBMS_LOB モジュールは、ラージ・オブジェクトを操作する機能を提供します。 DBMS_OUTPUT DBMS_OUTPUT モジュールは、メッセージ・バッファーにメッセージを書き込み (複数行のテキスト)、メッセージ・ バッファーからメッセージを取得する一連のプロシージャーを提供します。これらのプロシージャーは、メッセージ を標準出力に書き込む必要のあるアプリケーション・デバッグの際に役立ちます。 DBMS_PIPE DBMS_PIPE モジュールは、同じデータベースに接続されたセッション内またはセッション間のパイプを通して、 メッセージを送信するための一連のルーチンを提供します。 DBMS_SQL DBMS_SQL モジュールは、動的 SQL を実行するための一連のプロシージャーを提供します。したがって、さまざ まなデータ操作言語 (DML) ステートメントやデータ定義言語 (DDL) ステートメントをサポートします。 DBMS_UTILITY DBMS_UTILITY モジュールは、さまざまなユーティリティー・プログラムを提供します。 UTL_DIR UTL_DIR モジュールは、UTL_FILE モジュールで使用するディレクトリー別名を維持するための一連のルーチン を提供します。 UTL_FILE UTL_FILE モジュールは、データベース・サーバーのファイル・システム上のファイルとの間で読み取りおよび書き 込みを行うための一連のルーチンを提供します。 UTL_MAIL UTL_MAIL モジュールは、E メールを送信する機能を提供します。 UTL_SMTP UTL_SMTP モジュールは、SMTP (Simple Mail Transfer Protocol) を介して E メールを送信する機能を提供しま す。 24 © 2009 ISE Corporation DBMS_OUTPUT使用例 • DBMS_OUTPUTパッケージ パッケージ( パッケージ(モジュール) モジュール) • メッセージバッファーへの書き込み、読み込み行うためのプロシジャー、関数を提供 • 処理結果の出力、デバッグなどで役立ちます。 C:¥work>db2 -td@ -vf dbms_output_test.sql drop procedure proc1 DB20000I SQL コマンドが正常に完了しました。 CREATE PROCEDURE proc1( P1 VARCHAR(10) ) BEGIN CALL DBMS_OUTPUT.PUT( DBMS_OUTPUT.PUT 'P1 = ' ); CALL DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE P1 ); END DB20000I SQL コマンドが正常に完了しました。 DBMS_OUTPUTパッケージのPUTプロ シージャー、PUT_LINEプロシージャーを 呼び出して、メッセージ・バッファーへの書 き込みを行います。 SET SERVEROUTPUT ON DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。 CLPでメッセージバッファーを 読み出すための設定 CALL proc1( '10' ) リターン状況 = 0 P1 = 10 25 メッセージ・バッファーから読 み出されたデータ © 2009 ISE Corporation UTL_DIR、UTL_FILE使用例 • UTL_DIRパッケージ パッケージ • ディレクトリを管理するためのプ ロシージャーや関数を提供 • UTL_FILEパッケージ パッケージ • ファイルの読み取りや書き込みを 行うプロシージャーや関数を提供 UTL_DIRを使用してディレクトリの準備 call utl_dir.create_directory('mydir','C:¥work') utl_dir.create_directory リターン状況 = 0 call utl_dir.get_directory_path('mydir',?) utl_dir.get_directory_path 出力パラメーターの値 -------------------------パラメーター名: PATH パラメーター値: C:¥work リターン状況 = 0 26 UTL_FILEを使用してファイルに出力 begin declare v_filehandle utl_file.file_type; utl_file.file_type declare isopen boolean; declare row varchar(4000); set v_filehandle = utl_file.fopen('mydir','myfile.csv','w'); utl_file.fopen set isopen = utl_file.is_open( utl_file.is_open v_filehandle ); if isopen != true then return ; end if; for cur as (select deptno,deptname,mgrno from department) do set row = cur.deptno||','||cur.deptname||','||cur.mgrno; call utl_file.put_line(v_filehandle,row); utl_file.put_line end for; call utl_file.fclose(v_filehandle); utl_file.fclose end DB20000I SQL コマンドが正常に完了しました。 !type myfile.csv A00,SPIFFY COMPUTER SERVICE DIV.,000010 B01,PLANNING,000020 C01,INFORMATION CENTER,000030 D11,MANUFACTURING SYSTEMS,000060 ・・・省略・・・ CSV形式で ファイル出力 © 2009 ISE Corporation PL/SQLサポート PL/SQLサポート 27 © 2009 ISE Corporation PL/SQLとは • SQLの手続き型拡張機能としてOracle社が提供するプログラミング言語 • Oracle向けに独自に開発されているため、SQLのように標準仕様にのっとったものではない。 • Oracle Database の内部でSQLとの緊密な連携を行うことで業務処理を効率的に実行する。 • 反復処理や分岐といった制御構造を使って複雑な処理を行うことができる。 • なぜPL/SQLが必要? • SQLはデータの集合を操作するための言語であるが、実際の業務処理(ビジネス・ロジック)では手続き型の処理が必要な場 合もある。 Oracleでの PL/SQL実行例 制御文とSQLで 構成される 28 © 2009 ISE Corporation PL/SQLが記述される場所 • PL/SQLブロック(Anonymous Block) PL/SQLブロック declare ・・・ begin ・・・ end • データベース外部に保持 実行 • PL/SQLストアード・プログラム (データベースのオブジェクトとしてデータベース内にロジックを格納) • プロシージャー • ファンクション • パッケージ • トリガー 29 プロシージャー create procedure xx is ・・・ ファンクション begin ・・・ create function xxx end return varchar2 is ・・・ begin ・・・ end; トリガー create trigger xxx before insert ・・・ on ・・・ for each row declare ・・・ begin ・・・ end; © 2009 ISE Corporation 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 SURE (SQL Unified Runtime Engine) PL/SQL用とSQL PL用の2つの コンパイラを持つ それぞれのコンパイラから作成 されたモジュールは同じランタイム で稼動する DB2 Server Data base 30 © 2009 ISE Corporation DB2 9.7でPL/SQLを実行 カーソルを使って、DEPT 表から結果を読み取る。 前のページと全く同一 PL/SQLスクリプトを実施 PL/SQLユニークな文法 がDB2で利用できる。 31 © 2009 ISE Corporation DB2とOracleのPL/SQL実行比較 • CLPPlusと とSQL*Plusで で同じPL/SQLブロック ブロックを ブロックを実行 SQL*PlusからOracleに対して実行 CLPPlusからDB2に対して実行 SQL> set serveroutput on SQL> DECLARE 2 str VARCHAR2(30); 3 BEGIN 4 str := 'HELLO WORLD'; 5 DBMS_OUTPUT.PUT_LINE(str); 6 END; 7 / HELLO WORLD Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options に接続されました。 SQL> set serveroutput on SQL> DECLARE 2 str VARCHAR2(30); 3 BEGIN 4 str := 'HELLO WORLD'; 5 DBMS_OUTPUT.PUT_LINE(str); 6 END; 7 / HELLO WORLD DB250000I: コマンドは正常に完了しました。 PL/SQLプロシージャが正常に完了しました。 SQL> SQL> CLPPlus: バージョン 1.0 Copyright (c) 2009, IBM CORPORATION. All rights reserved. 32 © 2009 ISE Corporation PL/SQLを実行するには? • DB2_COMPATIBILITY_VECTOR (レジストリ変数) • PL/SQLコンパイラを使用可能にするかを指定 • PL/SQLを実行するためには設定は必須 • SET SQLCOMPAT PLSQL(CLPオプション) • CLPにPL/SQLの区切り文字(/)を認識させるオプション 33 © 2009 ISE Corporation 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 34 C:¥>db2stop force 2009-05-12 12:37:25 0 0 SQL1064N DB2STOP の処理が正常に終了 しました。 SQL1064N DB2STOP の処理が正常に終了しました。 C:¥>db2start 2009-05-12 12:37:31 0 0 SQL1063N DB2START の処理が正常に終 了しました。 SQL1063N DB2START の処理が正常に終了しました。 DB2_COMPATIBILITY_VECTOR=800設定 設定あり 設定あり 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; DB20000I PL/SQLブロック ブロックが ブロックが 正常に 正常に処理される 処理される SQL コマンドが正常に完了しました。 PL/SQL Test © 2009 ISE Corporation SET SQLCOMPAT PLSQL( (CLPオプション オプション) オプション) • CLPからPL/SQLを実行するためのオプション • SET SQLCOMPAT PLSQLを設定することで“/”をPL/SQLブロックの終わりと認識する • PL/SQLでは、“;”はステートメントの区切り。ブロック単位で実行させるために、ブロックの最 後を”/”で認識 • セッションの終わりまで有効 • SET SQLCONMPAT DB2 で元に戻す 35 © 2009 ISE Corporation 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 トリガーでは トリガーではサポート ではサポートされません サポートされません。 されません。 36 © 2009 ISE Corporation パッケージ(モジュール)のサポート パッケージ(モジュール)のサポート 37 © 2009 ISE Corporation パッケージ(モジュール)のサポート • モジュール • Oracleの のパッケージに パッケージに相当する 相当するオブジェクト するオブジェクト • 関数、 関数、プロシージャー、 プロシージャー、タイプ、 タイプ、変数などの 変数などのオブジェクト などのオブジェクトをまとめた オブジェクトをまとめたオブジェ をまとめたオブジェ クト Module • 以下のオブジェクトを含められる • • • • • • • • グローバル変数 SQLプロシージャー SQLファンクション SQLプロシージャー 外部プロシージャー 外部ファンクション SQLファンクション グローバルコンディション ・ ・ Moduleの初期化処理 ・ ユーザー定義タイプ(配列、連想配列、行タイプ、カーソルタイプ) グローバル変数 • PL/SQLの のパッケージと パッケージと同じ機能を 機能を提供 • PL/SQLではCREATE PACKAGE文によりモジュールを作成 • SQL PLではCREATE MODULE文によりモジュールを作成 38 © 2009 ISE Corporation モジュールの構成 • Moduleは仕様部と本体で構成される • 仕様部 • オブジェクトの呼び出し方法の記述 • グローバル変数やユーザー定義タイプ(配列、連想配列、カーソルタイプ・・・)の宣言も行う • 本体 • プロシージャーやファンクションを実装 CREATE MODULE moo@ 39 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@ 本体 © 2009 ISE Corporation モジュールのメリット • モジュール内オブジェクトの共用 • 変数、カーソル、型、例外、ローカルルーチンを共有できる • 名前衝突の回避 • プロシージャー名、ファンクション名、変数名の衝突を回避で きる • 情報の隠蔽 • モジュール内のみで使用可能なオブジェクトの作成が可能 • 権限のコントロール • モジュール単位に権限のgrant/revokeが可能 40 © 2009 ISE Corporation モジュールとPL/SQL • PL/SQLでパッケージを作成するとモジュールとして登録される • PL/SQLではモジュールと同じ機能をパッケージで提供している SQL PLでモジュールを作成 PL/SQLでパッケージ(モジュール)を作成 ・CREATE MODULE文でModuleを作成後、ALTER MODULE 文で仕様部、本体を追加する ・CREATE PACKAGE文、CREATE PACKAGE BODY文を使用して作成 CC:¥work>db2 -tvf pkgtest.sql set sqlcompat plsql DB20000I SET SQLCOMPAT コマンドが正常に完了しました。 C:¥work>db2 -tvf modtest.sql set sqlcompat db2 DB20000I SET SQLCOMPAT コマンドが正常に完了しました。 create module mod1 DB20000I SQL コマンドが正常に完了しました。 仕様部 alter module mod1 publish variable var1 int default 0 DB20000I SQL コマンドが正常に完了しました。 alter module mod1 publish procedure print DB20000I SQL コマンドが正常に完了しました。 本体 alter module mod1 add procedure print begin call dbms_output.put_line(var1); end DB20000I SQL コマンドが正常に完了しました。 alter module mod1 add procedure plus(in p1 int default 1) begin set var1 = var1 + p1; end DB20000I SQL コマンドが正常に完了しました。 alter module mod1 add function get() returns int return var1 DB20000I SQL コマンドが正常に完了しました。 41 仕様部 DB20000I SQL コマンドが正常に完了しました。 alter module mod1 publish procedure plus(in p1 int) DB20000I SQL コマンドが正常に完了しました。 alter module mod1 publish function get() returns int DB20000I SQL コマンドが正常に完了しました。 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 procedure print is begin dbms_output.put_line(var1); end; procedure plus(p1 in varchar2 default 1) is begin var1 := var1 + p1; end; function get return int is begin return var1; end; end; DB20000I SQL コマンドが正常に完了しました。 本体 © 2009 ISE Corporation モジュールに関するシステムカタログ • SYSCAT.MODULES • モジュール情報 • SYSCAT.MODULEOBJECTS • モジュールに含まれるオブジェクト情報 参照例 select char(modulename,5) modulename,dialect,moduletype from syscat.modules where modulename in ('PKG1','MOD1') MODULENAME ---------MOD1 PKG1 DIALECT ---------DB2 SQL PL PL/SQL MODULETYPE ---------M P PL/SQL PackageにはPが入る SQL PL作成されたModuleにはMが入る 2 レコードが選択されました。 select char(objectmodulename,10) objectmodulename,char(objectname,20) objectname,objecttype,published from syscat.moduleobjects where objectmodulename in ('PKG1','MOD1') order by 1 OBJECTMODULENAME ---------------MOD1 MOD1 MOD1 MOD1 PKG1 PKG1 PKG1 PKG1 OBJECTNAME -------------------VAR1 PLUS GET PRINT VAR1 GET PRINT PLUS 8 レコードが選択されました。 42 OBJECTTYPE ---------VARIABLE PROCEDURE FUNCTION PROCEDURE VARIABLE FUNCTION PROCEDURE PROCEDURE PUBLISHED --------Y Y Y Y Y Y Y Y MOD1に含まれるオブジェクト PKG1に含まれるオブジェクト © 2009 ISE Corporation モジュールへの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 9.7.0 -- データベース・コード・ページ: 1208 -- データベース照合シーケンス: SYSTEM_943 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 ・・・ 43 続き ------------------------------------------------------------モジュール・サポートの DDL ステートメント "DB2ADMIN"."MOD1" ------------------------------------------------------------- CREATE MODULE "DB2ADMIN"."MOD1"; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; 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"; ・・・・ © 2009 ISE Corporation モジュール制限事項 • モジュール内の個別のオブジェクトに特権を付与することはできません。モジュールに付与、またはモジュールから取 り消せるのは、EXECUTE 特権だけです。 • モジュールに含めることができるのは以下のデータベース・オブジェクトの定義だけです。 • SQL プロシージャー • SQL 関数 • 外部プロシージャー • 外部関数 • 作成済み条件 • モジュール初期化の際に暗黙的に実行されるモジュール初期化プロシージャー • ユーザー定義のデータ・タイプ定義 (特殊タイプ、配列タイプ、連想配列タイプ、行タイプ、およびカーソル・タイプ が含まれます) • すべてのデータ・タイプのグローバル変数 • モジュールの初期化に使用する SYS_INIT 初期化プロシージャーは、パラメーターを参照したり、結果セットを戻した りすることはできません。 • Data Studio ツールまたはコントロール・センター・ツールから、モジュールの定義、変更、初期化、またはドロップを 行うことはできません。 • 今回のバージョンでは、モジュールの使用に関して以下の制約事項が適用されます。 • 初期化プロシージャーの定義および実行はできません。 44 © 2009 ISE Corporation まとめ • Oracle互換機能強化概要 • DB2_COMPATIBILITY_VECTOR • データタイプの互換性 • スカラー関数の機能強化 • ビルトイン・パッケージ・ライブラリー • PL/SQLサポート • パッケージ(モジュール)のサポート 45 © 2009 ISE Corporation ブランク・ ブランク・ページ 46 © 2009 ISE Corporation 参考資料 参考資料 47 © 2009 ISE Corporation 参考資料内容 • Weak Typing • Public Synonym • 作成済み 作成済み一時表 • DB2_COMPATIBILITY_VECTORで で有効に 有効に出来る 出来る機能 • ROWNUM • 外部結合演算子(+) • 階層照会 • NUMBER データ・タイプ • VARCHAR2 データ・タイプ • DATE データ・タイプ • TRUNCATE TABLE • 文字リテラル • コレクション・メソッド • データ・ディクショナリー互換ビュー • PL/SQL のコンパイル 48 © 2009 ISE Corporation Weak Typing • DB2 9.5までは まではstrict(厳密 厳密な までは 厳密な)データタイピング • トレンドはweak(緩やかな)タイピング (PERL, RUBY, PHP, …) • DB2 9.7での での新 での新しい振 しい振る舞い • 数値と文字列間の暗黙的な変換(cast): • 割当て SET salary := ‘52000’ • 比較 WHERE salary > ‘52000’ • ビルトイン関数での利用 ‘salary: ‘ || 52000 (文字列連結の例) • NULL やパラメーター・マーカーの利用性向上 • 非型付きパラメーター・マーカーとNULLを伴う関数呼び出し • この振る舞いにより、明示的なキャストが不要になりステートメント量が少なくな ります。 49 © 2009 ISE Corporation 暗黙的な変換の例 V9.5 INTEGER型で定 義している。 $ db2 select "* from itest_w where salary > '10000'“ SQL0401N The data types of the operands for the operation ">" are not compatible. SQLSTATE=42818 エラー V9.7 $ db2 select "* from itest_w where salary > '10000'" NAME SALARY ---------------- ----------TEST 200000 NumericとString 間の暗黙的な変換。 OK 1 record(s) selected. 50 © 2009 ISE Corporation 非型付きパラメーター・マーカーのデータタイプ判別 • 非型付きパラメーター・マーカー • データタイプ指定されていないパラメーター・マーカー • 型付きパラメーター・マーカー ・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時まで据え置き、パラメーター・マーカーの入力値に基 づいたデータ・タイプの判別を行わせることが出来る • この機能を使用するには以下の条件が必要 • • 51 PREPAREが実行され、据え置き準備(DeferredPrepare)の使用が有効になっている レジストリ変数DB2_DEFERRED_PREPARE_SEMANTICS=YESが設定されている • DB2_DEFERRED_PREPARE_SEMANTICS=YESはUnicode,SBCS環境でのみの推奨値 © 2009 ISE Corporation 非型付きパラメーター・マーカーのデータタイプ判別テスト • 以下の をDB2_DEFERRED_PREPARE_SEMANTICSと 以下のSQLを deferPreparesの設定を変えて実行。 • SQL:values ? (パラメータマーカーにはsetStringで"111"を設定) C:¥work>java test5 DB2_DEFERRED_PREPARE_SEMANTICS=YES Prepare ! deferPrepares=true SQL : values ? この二つのパラメーターが設定されている場合には、パラメーター・マーカーの入 Execute ! 力値から、データタイプが判別されて、SQLの実行が成功します。 OK DATA : 111 -----------------------------------------------------------C:¥work>java test5 DB2_DEFERRED_PREPARE_SEMANTICS=NO SQL : values ? deferPrepares=true パラメーター・マーカーの入力値からのデータタイプ判別が行われずエラーとなります Prepare ! Execute ! エラー ステートメントで型なしパラメーター・マーカーが使用されているか、無効な NULL 値が含まれ ています。. SQLCODE=-418, SQLSTATE=42610, DRIVER=3.57.82 ------------------------------------------------------------DB2_DEFERRED_PREPARE_SEMANTICS=YES C:¥work>java test5 deferPrepares=false SQL : values ? deferPrepares=falseによりPrepareが先に行われるためパラメーター・マーカーの入 Prepare ! 力値が使用できずエラーとなります エラー ステートメントで型なしパラメーター・マーカーが使用されているか、無効な NULL 値が含まれ ています。. SQLCODE=-418, SQLSTATE=42610, DRIVER=3.57.82 52 © 2009 ISE Corporation 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、ニックネーム、表、またはビューに対して別名を定義可能 53 © 2009 ISE Corporation Public Synonym(Public Alias)使用例 他のユーザーから参照 • CREATE PUBLIC SYNONYM文 文 を使 用してPublic Aliasを を作成し して 作成し、他のユー ザーから ザーからPublic Aliasを を参照しています 参照しています から values current schema 1 ------------------------------------------------USER1 1 レコードが選択されました。 Public Alias作成 values current schema 1 ---------------------------------------------------DB2ADMIN 1 レコードが選択されました。 create table t1 (c1 char(20)) DB20000I SQL コマンドが正常に完了しました。 grant all on t1 to public DB20000I SQL コマンドが正常に完了しました。 insert into t1 values ('db2admin') DB20000I SQL コマンドが正常に完了しました。 create public synonym p_t1 for t1 DB20000I SQL コマンドが正常に完了しました。 select * from p_t1 C1 -------------------db2admin スキーマ指定なしで呼び出す とPublic Aliasが参照される 1 レコードが選択されました。 create table p_t1(c1 char(20)) DB20000I SQL コマンドが正常に完了しました。 insert into p_t1 values ('user1') DB20000I SQL コマンドが正常に完了しました。 select * from p_t1 C1 -------------------user1 Public Aliasと同じオブジェクトがス キーマ内にある場合はスキーマな いのオブジェクトが参照される 1 レコードが選択されました。 select * from syspublic.p_t1 syspublic DB2ADMINスキーマのT1表 の Public Alias P_T1を作成 C1 -------------------db2admin Public AliasはSYSPUBLICスキーマ に作成されます。 明示的にスキーマを指定してPublic Aliasにアクセスすることも可能 1 レコードが選択されました。 54 © 2009 ISE Corporation 作成済み一時表 • V9.7より より作成済 より作成済み 作成済み一時表が 一時表が使用可能 • CREATE GLOBAL TEMPORARY TABLE文で作成済み一時表を作成 • 作成された定義はカタログに格納され、複数のセッションからの定義 定義の共有が可能 定義 • V9.5までは、宣言済み一時表の使用のみ可能 作成済み一時表と宣言済み一時表の比較 作成済み一時表 宣言済み一時表 作成ステートメント CREATE GLOBAL TEMPORARY TABLE文 DECLARE GLOBAL TEMPORARY TABLE文 定義 カタログに格納される。一度作成しておけば使用時の宣言や作 成は不要 カタログには格納されず、セッション毎に宣言し、作成が必要 データへのアクセス セッション毎に独立してアクセス、SQLによるアクセスが可能 セッション毎に独立してアクセス、SQLによるアクセスが可能 データの永続性 セッション内のみで有効 セッション内のみで有効 コミット毎に行の削除または残す設定が可能 コミット毎に行の削除または残す設定が可能 スキーマ 指定可能 SESSIONスキーマに作成される ロッキング、リカバリー ロッキング、リカバリーは行われません。 ロッキング、リカバリーは行われません。 LOGGEDが指定されていれば、ローバックは可能 LOGGEDが指定されていれば、ローバックは可能 表スペース ユーザー一時表スペース ユーザー一時表スペース 索引 索引追加可能、表と同じ表スペースに配置 索引追加可能、表と同じ表スペースに配置 表特権 表特権の付与、取り消しが可能 なし 55 © 2009 ISE Corporation 作成済み一時表使用例 セッションAから作成済み一時表を使用 C:¥>db2 connect to test1 user db2admin using db2admin • 作成済み 作成済み一時表を 一時表を作成し 作成し、 複数の 複数のセッションから セッションから使用 から使用 データベース接続情報 データベース・サーバー SQL 許可 ID ローカル・データベース別名 = DB2/NT 9.7.0 = DB2ADMIN = TEST1 作成済み一時表の作成 C:¥>db2 "insert into temptab1 values (1,1)" DB20000I SQL コマンドが正常に完了しました。 C:¥>db2 -tvf temptable.sql create user temporary tablespace usertemp DB20000I SQL コマンドが正常に完了しました。 C:¥>db2 "select * from temptab1" create global temporary table temptab1 (c1 int,c2 int) int) in usertemp on commit preserve rows logged DB20000I SQL コマンドが正常に完了しました。 C1 C2 ----------- ----------1 1 1 レコードが選択されました。 セッションBから作成済み一時表を使用 C:¥>db2 connect to test1 user db2admin using db2admin list tables 表/ビュー スキーマ タイプ 作成時刻 ---------- ----------- ----- -------------------------G 2009-06-28-12.03.19.718002 TEMPTAB1 DB2ADMIN 1 レコードが選択されました。 作成済み一時表はカタログに定義が格納され ているため、LIST TABLESにも出力される タイプはGになる 56 データベース接続情報 データベース・サーバー SQL 許可 ID ローカル・データベース別名 セッション毎に = DB2/NT データは独立し 9.7.0 = DB2ADMIN = TEST1 ています。 C:¥>db2 "insert into temptab1 values (2,2)" DB20000I SQL コマンドが正常に完了しました。 C:¥>db2 "select * from temptab1" C1 C2 ----------- ----------2 2 1 レコードが選択されました。 © 2009 ISE Corporation 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) 57 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation 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 -----10 20 30 40 50 60 70 80 90 100 ID -----50 60 70 80 90 100 NAME --------Sanders Pernal Marenghi O'Brien Hanes Quigley Rothman James Koonitz Plotz 10行目以下 までを選択 NAME --------Hanes Quigley Rothman James Koonitz Plotz 5行目から10行目 までを選択 6 レコードが選択されました。 Explainを取得してOptimized Statementを確認 10 レコードが選択されました。 Explainを取得してOptimized Statementを確認すると ROWNUMBER() OVER()に REWRITEされているのがわかります 58 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) © 2009 ISE Corporation 外部結合演算子(+) • 外部結合演算子 (+) を使用して 使用して外部結合 して外部結合できます 外部結合できます。 できます。 • 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) 59 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation 外部結合演算子(+)使用例 • 外部結合を 外部結合を行う結合条件に 結合条件に外部結合演算子(+)を 外部結合演算子 を付ける C:¥>db2 "select deptno,deptname,empno from dept ,emp where deptno = workdept(+) (+)" (+) DEPTNO DEPTNAME ------ -----------------------------------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 48 レコードが選択されました。 外部結合により、 結合条件に合致 しない行も表示 60 EMPNO -----000010 000020 200330 200340 - Explainを取得し、Optimized Statementを参照すると OUTER JOIN句を使用した文にREWRITEされている 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") © 2009 ISE Corporation 階層照会 • 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) 61 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation 階層照会 使用例 • 以下の 以下の句を指定して 指定して取 して取り出す階層を 階層を指定する 指定する • START WITH句でルートになる条件を指定 • CONNECT BY句で親子の関係を表す条件を指定 • • PRIOR演算子を使用して親の列を参照できる 階層照会では 階層照会では以下 では以下のような 以下のような擬似列 のような擬似列、 擬似列、関数の 関数の使用が 使用が可能 • LEVEL擬似列 • • 階層照会でこの行のルートからの階層数を返す SYS_CONNECT_BY_PATH関数 • 階層照会でルート行からこの行までのパスを表すストリングを作成 上位の部門コード Department表データ DEPTNO DEPTNAME ------ -----------------------------A00 SPIFFY COMPUTER SERVICE DIV. B01 PLANNING C01 INFORMATION CENTER D01 DEVELOPMENT CENTER D11 MANUFACTURING SYSTEMS D21 ADMINISTRATION SYSTEMS E01 SUPPORT SERVICES E11 OPERATIONS E21 SOFTWARE SUPPORT F22 BRANCH OFFICE F2 ・・・省略・・・ MGRNO -----000010 000020 000030 000060 000070 000050 000090 000100 - ADMRDEPT -------A00 A00 A00 A00 D01 D01 A00 E01 E01 E01 LOCATION -------- Department表に対して階層照会を実施 select deptno, deptname, level,char(sys_connect_by_path sys_connect_by_path(deptno,'/'),30) hierarchy level sys_connect_by_path from department start with deptno = 'A00' connect by prior deptno = admrdept and deptno !='A00' DEPTNO DEPTNAME LEVEL ------ ---------------------------------- ----------A00 SPIFFY COMPUTER SERVICE DIV. 1 B01 PLANNING 2 C01 INFORMATION CENTER 2 D01 DEVELOPMENT CENTER 2 D11 MANUFACTURING SYSTEMS 3 D21 ADMINISTRATION SYSTEMS 3 E01 SUPPORT SERVICES 2 E11 OPERATIONS 3 E21 SOFTWARE SUPPORT 3 ・・・省略・・・ 62 HIERARCHY ---------------/A00 /A00/B01 /A00/C01 /A00/D01 /A00/D01/D11 /A00/D01/D21 /A00/E01 /A00/E01/E11 /A00/E01/E21 Department表の階層イメージ A00 B01 C01 D11 D01 E01 D21 E11 E21 ・・・ © 2009 ISE Corporation データタイプの互換性 • 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) 63 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation データタイプの互換性 • NUMBER,VARCHAR2,DATEの の互換性 • Oracleがサポートしている以下のデータタイプとの互換性をサポート • • • • DATE NUMBER VARCHAR2 データタイプの の設定が データタイプの互換性を 互換性を有効にするには 有効にするには、 にするには、DB2_COMPATIBILITY_VECTORの 設定が必要 • データベース作成時に設定が行われている必要がある、データベース作成後は変更不可 • 以下のデータベース構成パラメーターの以下の項目で互換性サポートを有効化を確認できる • NUMBER データ・タイプの互換性 • VARCHAR2 データ・タイプの互換性 • データ・タイプ DATE の TIMESTAMP(0) への互換性 確認例 C:¥>db2 get db cfg for sample | find "互換性" NUMBER データ・タイプの互換性 = ON VARCHAR2 データ・タイプの互換性 = ON データ・タイプ DATE の TIMESTAMP(0) への互換性 64 = ON © 2009 ISE Corporation 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) 65 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation TRUNCATE TABLE代替セマンティクス • この機能 TABLE文 文の以下の この機能を 機能を有効にすると 有効にするとTRUNCATE にすると 以下の2つの動作 つの動作が 動作が変わる 1.IMMEDIATEキーワードが必須ではなくなり、以下のような呼び出しが可能 • TRUNATE TABLE テーブル名 テーブル名 TRUNCATE TABLE構文 Oracleと同じ構文で呼 び出しができる .-TABLE-. .-DROP STORAGE--. >>-TRUNCATE--+-------+--table-name--+---------------+-----------> '-REUSE STORAGE-' .-IGNORE DELETE TRIGGERS--------. >--+-------------------------------+----------------------------> '-RESTRICT WHEN DELETE TRIGGERS-' .-CONTINUE IDENTITY-. >--+-------------------+--IMMEDIATE---------------------------->< IMMEDIATEキーワード がオプションになる 2.トランザクション中でもTRUNCATE TABLE文実行前に暗黙的なコミットが行われ、 TRUNCATE TABLE文が実行される • この機能が無効の場合には、トランザクション中にTRUNCATE TABLEを発行するとエラーと なる(SQL0428N) 66 © 2009 ISE Corporation TRUNCATE TABLE実行例 DB2_COMPATIBILITY_VECTOR=080の場合 DB2_COMPATIBILITY_VECTOR設定なしの場合 C:¥>db2 truncate table t1 C:¥>db2 truncate table t1 OFF DB21034E コマンドが、有効なコマンド行プロセッサー・コマ ンドでないため、 SQLステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0104N "truncate table T1" に続いて予期 いて予期しない 予期しないトークン しないトークン "END"END-OFOF-STATEMENT"が STATEMENT"が見つかりました。 つかりました。予期された 予期されたトークン されたトークンに トークンに "IMMEDIATE" IMMEDIATE"が含まれている可能性 まれている可能性があります 可能性があります。 があります。 SQLSTATE=42601 DB20000I ON SQL コマンドが正常に完了しました。 C:¥>db2 +c insert into t1 values (1,1) DB20000I SQL コマンドが正常に完了しました。 C:¥>db2 truncate table t1 DB20000I SQL コマンドが正常に完了しました。 トランザクション 中でも実行可能 IMMEDIATEが必須のキー ワードのため文法エラー トランザクション中に実行するとSQL0428エラー でステートメントは失敗する 67 C:¥>db2 truncate table t1 immediate DB20000I SQL コマンドが正常に完了しました。 C:¥>db2 +c insert into t1 values (1,1) DB20000I SQL コマンドが正常に完了しました。 C:¥>db2 truncate table t1 immediate DB21034E コマンドが、有効なコマンド行プロセッサー・コマ ンドでないため、 SQLステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0428N SQL ステートメントは ステートメントは、作業単位の 作業単位の最初の 最初のステート メントとしてのみ メントとしてのみ許可 としてのみ許可され 許可され ています。 ています。 SQLSTATE=25001 © 2009 ISE Corporation 文字リテラルの互換性 • バイト長 バイト長が 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) 68 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation 文字リテラルの互換性 • 254文字以下 文字以下の として扱 文字以下の文字リテラル 文字リテラルを リテラルをCHARとして として扱う。 • Oracleでは、文字リテラルはCHARとして扱われる • OracleでもCHARの比較は空白埋め比較セマンティクスが使用される VARCHAR2データ データ・ データ・タイプの タイプの互換性=ON 互換性 または 文字リテラル 文字リテラルの リテラルの互換性=ON 互換性 VARCHAR2データ データ・ データ・タイプの タイプの互換性=OFF 互換性 C:¥>db2 describe values 'A' C:¥>db2 describe values 'A' 列情報 列情報 リテラルのタイプはCHAR リテラルのタイプはVARCHAR 列の数: 1 列の数: 1 SQL タイプ タイプ長 ---------------- ----------452 CHARACTER 1 列名 -------1 名前長 ----------1 SQL タイプ タイプ長 -------------- ----------448 VARCHAR 1 列名 -------1 名前長 ----------1 C:¥>db2 "select 'TRUE' from dual where 'A' = 'A '" C:¥>db2 "select 'TRUE' from sysibm.sysdummy1 where 'A' = 'A '" 1 ---TRUE 1 ---TRUE 1 レコードが選択されました。 69 1 レコードが選択されました。 © 2009 ISE Corporation コレクション・メソッド • 配列で 、last、 、next、 、previous などの演算 配列で first、 などの演算を 演算を実行する 実行するメソッド するメソッドの メソッドの使用を 使用を可能にします 可能にします。 にします。 また、 また、配列内の 配列内の特定の 特定の要素の 要素の参照に 参照に、大括弧の 大括弧の代わりに括弧 わりに括弧を 括弧を使用できるようにします 使用できるようにします。 できるようにします。例 えば、 えば、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) 70 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation コレクション・メソッド • PL/SQL コレクション とは • • • 71 同じデータ・タイプを持つ、配列されたデータ・エレメントの集合。その集合内の個々のデータ項目を、括弧を使用した添字表 記法を使用して参照できます。 VARRAY、連想配列 コレクションメソッド • コレクションの情報を参照したり、変更を行う機能 • 以下のようなメソッドがあります。 コレクション・メソッド 説明 COUNT コレクション内のエレメントの数を返します。 DELETE コレクションからすべてのエレメントを削除します。 DELETE (n) 連想配列からエレメント n を削除します。 VARRAY コレクション・タイプから個々のエレメントを削除することはできません。 DELETE (n1, n2) n1 から n2 までのエレメントすべてを、連想配列から削除します。 VARRAY コレクション・タイプから個々のエレメントを削除することはできません。 EXISTS (n) 指定したエレメントが存在する場合は、TRUE を返します。 EXTEND コレクションに NULL エレメントを 1 つだけ付加します。 EXTEND (n) コレクションに NULL エレメントを n 個付加します。 EXTEND (n1, n2) コレクションに、n2 番目のエレメントのコピーを n1 個付加します。 FIRST コレクション内にある最小の索引番号を返します。 LAST コレクション内にある最大の索引番号を返します。 LIMIT VARRAY の場合にはエレメントの最大数、ネストした表の場合には NULL を返します。 NEXT (n) 指定したエレメントの直後に位置するエレメントの索引番号を返します。 PRIOR (n) 指定したエレメントの直前に位置するエレメントの索引番号を返します。 TRIM コレクションの末尾から、エレメントを 1 つだけ削除します。連想配列コレクション・タイプからエレメントをトリムすることはできません。 TRIM (n) コレクションの末尾から、エレメントを n 個削除します。連想配列コレクション・タイプからエレメントをトリムすることはできません。 © 2009 ISE Corporation データ・ディクショナリー互換ビュー • 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) 72 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation データ・ディクショナリー互換ビュー • 右の表にあるビューが作成される • それぞれのビューにはPublic Aliasが作成されているた め、スキーマ指定なしでも呼び出し可能 • ネーミング・ルール • USER_* • • ALL_* • • 一般 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 ユーザーがアクセス可能なオブジェクトを表示 全てのオブジェクトを表示 右のオブジェクト以外にも以下のオブジェクトも作成され る • 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 :ユーザーのテーブルを表示(スキーマ名と テーブル名とテーブルタイプのみの列を持つ) 73 定義されている 定義されているビュー されているビュー DBA_* • • ユーザーのオブジェクトを表示 カテゴ リー © 2009 ISE Corporation データディクショナリー互換ビュー使用例 データディクショナリーの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 -------------------SQL090627173927420 SQL090627173928650 CUST_PHONET_XMLIDX SQL090627173928640 CUST_PHONES_XMLIDX SQL090627173928590 CUST_NAME_XMLIDX SQL090627173928530 CUST_CID_XMLIDX PK_CUSTOMER SQL090627173927530 SQL090627173927510 INDEX_TYPE -------------------XRGN XVIP XVIL XVIP XVIL XVIP XVIL XVIP XVIL REG XPTH XPTH TABLE_NAME -------------------CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER CUSTOMER 12 レコードが選択されました。 74 © 2009 ISE Corporation 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) 75 互換性フィーチャー ROWNUM 未使用 外部結合演算子 階層照会 NUMBER データ・タイプ VARCHAR2 データ・タイプ DATE データ・タイプ TRUNCATE TABLE 文字リテラル コレクション・メソッド データ・ディクショナリー互換ビュー PL/SQL のコンパイル © 2009 ISE Corporation 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 76 C:¥>db2stop force 2009-05-12 12:37:25 0 0 SQL1064N DB2STOP の処理が正常に終了 しました。 SQL1064N DB2STOP の処理が正常に終了しました。 C:¥>db2start 2009-05-12 12:37:31 0 0 SQL1063N DB2START の処理が正常に終 了しました。 SQL1063N DB2START の処理が正常に終了しました。 DB2_COMPATIBILITY_VECTOR=800設定 設定あり 設定あり 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; DB20000I PL/SQLブロック ブロックが ブロックが 正常に 正常に処理される 処理される SQL コマンドが正常に完了しました。 PL/SQL Test © 2009 ISE Corporation