DB2 V9.7新機能ワークショップ Oracle互換（前半）

by user

on 15 сентября 2016

Category: Documents

>> Downloads: 74

542

views

Report

Comments

Description

Download DB2 V9.7新機能ワークショップ Oracle互換（前半）

Transcript

DB2 V9.7新機能ワークショップ Oracle互換（前半）

DB2 V9.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用の２つの
コンパイラを持つ
それぞれのコンパイラから作成
されたモジュールは同じランタイム
で稼動する
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データ
データ・
データ・タイプの
タイプの互換性は
互換性は０１０
•
データベース作成時に決定され、作成後は変更できません。
ビット位置
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
にすると
以下の２つの動作
つの動作が
動作が変わる
１．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キーワード
がオプションになる
２．トランザクション中でも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