PREPARE PREPSTMT1 FROM STMT;
5. データベース管理
3-1. フィールド・プロシジャー（暗号化の拡張）
機能
– フィールド単位でトリガーの設定が可能
• 暗号化ロジックと組み合わせることで、フィールド単位での暗号化が可能
• フィールドプロシジャは暗号化専用の機能ではない
利用方法
– CREATE TABLE / ALTER TABLE で宣言する際に、下記の3つの処理を1つのプログラム
（フィールド・プロシジャー）に書いておくことが必須
• CREATE TABLE / ALTER TABLE で宣言される際の処理
• エンコーディング（暗号化）の処理
• デコーディング（復号化）の処理
– ILE C／RPG／COBOL 用のインクルードファイルが QSYSINC/H/SQLFP で提供 – トリガーが作動するタイミング
• アプリケーション、SQLからのアクセス
• ネイティブレコードレベルアクセス
• SELECT、RUNQRY、DSPPFM、CPYF、バイナリーFTP など
手順①：フィールド･プロシジャーのコーディング
– パラメータの渡し方やコーディングの作法はマニュアルに記載あり（かなり複雑）
– パラメータとして「機能コード」に宣言時には「8」、書込み時には「0」読取り時には「4」が入っ てプログラムが呼出されるので、それぞれに対応したコーディングを行う
– エンコードとデコードでデータが確実に元に戻るようにコーディングするのはユーザーの責任
• 誤ったコーディングをするとデータが壊れるため要注意
3-1. フィールド・プロシジャー（暗号化の拡張）
*************** Beginning of data *********************************************************
0001.00 #include "string.h" 100331 0002.00 #include <stdio.h> 100331 0003.00 #include <QSYSINC/H/SQLFP> 100331 0004.00 100331 0005.00 void encode(char *in, char *out, long length); 100331 0006.00 void reverse(char *in, char *out, long length); 100331 0007.00 100331 0008.00 main(int argc, void *argv[]) 100331 0009.00 { 100331 0010.00 short *funccode = argv[1]; 100331 0011.00 sqlfpFieldProcedureParameterList_T *optionalParms = argv[2]; 100331 0012.00 char *sqlstate = argv[7]; 100331 0013.00 struct sqlfpMessageText_t *msgtext = argv[8]; 100331 0014.00 int bytesToProcess; 100331 0015.00 sqlfpOptionalParameterValueDescriptor_T *optionalParmPtr; 100331 0016.00 100331 0017.00 if (optionalParms->sqlfpNumberOfOptionalParms != 1) 100331 0018.00 { 100331 0019.00 memcpy(sqlstate,"38001",5); 100331 0020.00 return; 100331 0021.00 } 100331 0022.00 100331 0023.00 optionalParmPtr = (void *)&(optionalParms->sqlfpParmList); 100331 0024.00 bytesToProcess = *((int *)&optionalParmPtr->sqlfpParmData); 100331 0025.00 100331 0026.00 if (*funccode == 8) /* create time */ 100331 0027.00 { 100331 0028.00 struct sqlfpParameterDescription_t *inDataType = argv[3]; 100331 0029.00 struct sqlfpParameterDescription_t *outDataType = argv[5]; 100331 0030.00 if (inDataType->sqlfpSqlType !=452 && 100331 0031.00 inDataType->sqlfpSqlType !=453 ) /* only support fixed length char */ 100331 0032.00 { 100331 0033.00 memcpy(sqlstate,"38002",5); 100331 0034.00 return; 100331 0035.00 } 100331
フィールド・プロシジャー：サンプルプログラム（ ILE C ）
フィールド・プロシジャー：サンプルプログラム（ ILE C ）
0038.00 100331 0039.00 else if (*funccode == 0) /* encode */ 100331 0040.00 { 100331 0041.00 char *decodedData = argv[4]; 100331 0042.00 char *encodedData = argv[6]; 100331 0043.00 encode(decodedData, encodedData, bytesToProcess); 100331 0044.00 } 100331 0045.00 else if (*funccode == 4) /* decode */ 100331 0046.00 { 100331 0047.00 char *decodedData = argv[4]; 100331 0048.00 char *encodedData = argv[6]; 100331 0049.00 reverse(encodedData, decodedData, bytesToProcess); 100331 0050.00 } 100331 0051.00 else /* unsupported option -- error */ 100331 0052.00 memcpy(sqlstate, "38003",5); 100331 0053.00 } 100331 0054.00 100331 0055.00 void encode(char *in, char *out, long length) 100331 0056.00 { 100331 0057.00 int i; 100331 0058.00 for (i=0;i<length; ++i) { 100331 0059.00 out[length - (i+1)] = in[i]; 100331 0061.00 } 100331 0064.00 } 100331 0065.00 void reverse(char *in, char *out, long length) 100331 0066.00 { 100331 0067.00 int i; 100331 0068.00 for (i=0;i<length; ++i) { 100331 0069.00 out[length - (i+1)] = in[i]; 100331 0071.00 } 100331 0074.00 } 100331
****************** End of data ************************************************************
手順②：フィールド・プロシジャーのコンパイル
– コンパイル時には、活動化グループACTGRP(*CALLER)を指定
Command Entry
Request level: 6 Previous commands and messages:
> CRTCMOD MODULE(KTEST71/FPROC) SRCFILE(KTEST71/QCSRC) Module FPROC was created in library KTEST71 on 03/31/10 at 18:49:10.
> CRTPGM PGM(KTEST71/FPROC) ACTGRP(*CALLER) Program FPROC created in library KTEST71.
3-1. フィールド・プロシジャー（暗号化の拡張）
手順③：フィールドプロシジャーを指定したテーブルの作成
– KTEST71/FPROCがField Procedure のILE プログラム – (10) はパラメータ
考慮点
– テーブル作成時にフィールド・プロシジャー が実行されるため、事前にフィールド・プロシジャ ーのコンパイルが完了していることが必須
– ストアード・プロシジャー等と異なり、CREATE PROCEDURE等でDBに対して宣言する必要 はない
Enter SQL Statements Type SQL statement, press Enter.
> create table ktest71.fptest(id char( 2), name char(10) fieldproc ktest71.fproc(10)) Table FPTEST created in KTEST71.
3-1. フィールド・プロシジャー（暗号化の拡張）
手順④：作成したテーブルへのデータの更新／表示
– ファイルをREAD/WRITE/UPDATEする度にフィールド･プロシジャーが実行される
– ただし、テストした限りは暗号化された値を見る術はなし。ユーザープログラムが読み取る前 にはデコードされるため、暗号化されたデータの確認は不可
– エンコード／デコートが非対称なフィールド・プロシジャーを意図的に作成したところ、正しく暗 号化されていたことは確認
– フィールド・プロシジャーを削除すると、データを読み書きが不可
Enter SQL Statements Type SQL statement, press Enter.
> INSERT INTO KTEST71.FPTEST VALUES('01', 'abcdefghij') 1 rows inserted in FPTEST in KTEST71.
> INSERT INTO KTEST71.FPTEST VALUES('02', 'jihgfedcba') 1 rows inserted in FPTEST in KTEST71.
===> select * from ktest71.fptest
Display Data Position to line . . .
....+....1....
ID NAME 01 abcdefghij
02 jihgfedcba
******** End of data ********
3-1. フィールド・プロシジャー（暗号化の拡張）
機能
– 組み込みSQLの結果セット（リザルト・セット）の利用により、柔軟なプログラミングが可能に – C、C++、COBOL、および ILE RPG
利用サンプル
– ILE RPGでストアードプロシジャ P1をCALLし、結果セットを受け取り
D MYRS1 S SQLTYPE(RESULT_SET_LOCATOR) D MYRS2 S SQLTYPE(RESULT_SET_LOCATOR)
…
C/EXEC SQL CALLP1(:parm1, :parm2, ...) C/END-EXEC
…
C/EXEC SQL ALLOCATE DESCRIPTOR ‘SQLDES1’
C/END-EXEC
…
C/EXEC SQL DESCRIBE PROCEDUREP1 USING DESCRIPTOR ‘SQLDES1’
C/END-EXEC
…
C/EXEC SQL ASSOCIATE LOCATORS(:MYRS1,:MYRS2) WITH PROCEDUREP1 C/END-EXEC
C/EXEC SQL ALLOCATEC1 CURSOR FOR RESULT SET:MYRS1 C/END-EXEC
C/EXEC SQL ALLOCATEC2 CURSOR FOR RESULT SET:MYRS2 C/END-EXEC
…
3-2. 組込み SQL での結果セット受取りのサポート
3-3. QSYS2.CANCEL_SQL プロシジャー
機能
– システム提供の QSYS2.CANCEL_SQLプロシジャーで応答のないSQL処理をキャンセル可能
• キャンセルされるのはSQLによるデータベース操作のみ
• ジョブは終了しない
利用上の考慮点
– ストアードプロシジャのため、SQL環境からCALLステートメントで実行 – 応答のないジョブとは別のジョブから実行
• 実行するジョブは *JOBCTL特殊権限またはDB2の QIBM_DB_SQLADM権限が必要
実行例
> STRSQL
> CALL QSYS2.CANCEL_SQL('015634/USER1/QPADEV000G') CALL statement complete.
(キャンセルする側)
> SELECT * FROM KTEST71.BITFILE WITH RR (キャンセルされる側)
ジョブ A が DFU からファイル①を更新
ジョブ B がオープンされているファイル①に対して、分離レベル RR で SELECT を実行
⇒ ジョブBはロック待ちで長時間停止
ジョブ C が、ジョブ B に対して QSYS2.CANCEL_SQL を実行
3-3. QSYS2.CANCEL_SQL プロシジャー
【ジョブA】 DFUからのデータ更新
【ジョブB】
SQLで照会：分離レベルRR
【ジョブC】
キャンセルプロシジャーを実行
SQL をキャンセルされたジョブ B には下記のどちらかのメッセージ が送られる
エスケープメッセージ
SQL0952 －Processing of the SQL statement ended. Reason code X.
SQL0901 － SQL system error.
3-3. QSYS2.CANCEL_SQL プロシジャー
3-4. 三部構成名称（ Three-part Alias ）
他のデータベースにあるテーブルを指す別名を定義可能
– 他のDBを参照する際にCONNECT、SET CONNECTIONのコーディングが不要となる
従来（ IBM i 6.1 以前）
– CREATE ALIAS MJATST.t1 FOR RDB2.MJATST.T1 CONNECT TO RDB2
SELECT * FROM MJATST.T1
IBM i 7.1
– SELECT * FROM RDB2.MJATST.T1
制限事項：
– 1つのSQL文から参照されるすべてのオブジェクトは1つのRDBになければならない
– 指定する別名はリモートRDBでの名前と同じでなければならない（ただしリモートでの別名を 指定することは可能）
2つの数値をビットデータとしてビット演算を実施
数値データに対するビットテスト／操作関数
– BITAND, BITANDNOT, BITOR, BITXOR, BITNOT
3-5. ビット関数演算
サンプルコーディング
SELECT ITEMID FROM ITEM
WHERE BITAND(PROPERTIES, 4) = 4 SELECT ITEMID FROM ITEM
WHERE BITAND(PROPERTIES, 40) <> 0 UPDATE ITEM
SET PROPERTIES = BITANDNOT(PROPERTIES, 2048) WHERE ITEM = 3412
UPDATE ITEM
SET PROPERTIES = BITOR(PROPERTIES, 16) WHERE ITEM = 3412
UPDATE ITEM
SET PROPERTIES = BITXOR(PROPERTIES, 1024) WHERE ITEM = 3412
VALUES BITNOT(CAST(2) AS SMALLINT))
3-5. ビット関数演算
3-6. 区分化表（パーティションテーブル）の拡張
区分化表とは
– テーブルを複数メンバーに格納し、一つとして扱う – テーブルの制限を超える場合に使用
• テーブルサイズが1.7テラバイト以上、またはレコード数が420億以上 – DB2 マルチシステム（OSオプション）のインストールが必要
DB2 for i 7.1 での拡張
– 区分化表で識別列（アイデンティティ・カラム）のサポート – 区分化表で参照制約のサポート
制限事項
– 識別列は区分化キーにはなれない – 親テーブルは次のいずれか：
• 区分化されていないテーブル
• ユニーク制約に使用されているユニークキーが参照制約に対して区分化されていない、
区分化されたテーブル
– 参照制約や識別列のある区分化表は旧リリース用には保管できない
指定したMQSeriesロケーションからメッセージを送信する MQSEND
されたMQSeriesロケーションからメッセージを（CLOBで）返し、キューからメッセージを除去する MQRECEIVECLOB
指定されたMQSeriesロケーションからのメッセージを（VARCHARで）返し、キューからメッセージを除去する MQRECEIVE
キューからメッセージを除去せずに指定されたMQSeriesロケーションからメッセージを（CLOBで）返す MQREADCLOB
キューからメッセージを除去せずに指定されたMQSeriesロケーションからのメッセージを（VARCHARで）返す MQREAD
機能 スカラー関数
指定されMQSeriesロケーションからメッセージとメッセージメタデータを含むテーブルをCLOBで返し、キューからメッセ ージを除去する
MQRECEIVEALLCLOB
指定されMQSeriesロケーションからメッセージとメッセージメタデータを含むテーブルをVARCHARで返し、キューから メッセージを除去する
MQRECEIVEALL
キューからメッセージを除去せずに指定されMQSeriesロケーションからメッセージとメッセージメタデータを含むテーブ ルをCLOBで返す
MQREADALLCLOB
キューからメッセージを除去せずに指定されMQSeriesロケーションからメッセージとメッセージメタデータを含むテーブ ルをVARCHARで返す
MQREADALL
機能 テーブル関数
サンプル
SQLコマンドから下記のMQ 関数の呼び出しが可能に
– プログラミングのシンプル化、可搬性(ポータビリティ)の向上
3-7. SQL 中で MQ Series 関数の呼出し
DB2 for i 7.1 最新情報
1. XML サポート
2. DB2 ファミリー、他の DBMS との互換性向上
1. MERGE
2. CREATE時のREPLACE オプション 3. ロック待機の回避による並行性の改善 4. グローバル変数
5. CALLステートメント中の表現の拡張 6. パラメータマーカーの拡張
7. ルーチンの中で配列のサポート
3. アプリケーション開発の強化
1. フィールドプロシジャ (FIELDPROCS)
2. 組込みSQLでの結果セット受取りのサポート 3. QSYS2.SQL_CANCEL プロシジャー
4. 三部構成名称 5. BIT スカラー関数 6. 区分化表の拡張
7. SQL中でMQ Series 関数の呼出し
4. パフォーマンス強化
1. SQE機能拡張
5. データベース管理
高性能な SQL 実行エンジン SQE
– SQEはDB2/400 V5R3以降にビルトイン
• OSリリース毎にSQLパフォーマンス、機能が向上
– DB2 for i パフォーマンスの向上
– SQLパフォーマンス分析・管理機能を提供
• EVI(Encoded Vector Indexes),
• MQT(Materialized Query Tables)
• SQLプラン・キャッシュ (IBM i ナビゲーターから分析)
DB2 for i 7.1 での機能拡張
– Adaptive Query Processing (AQP)
• クエリー実行中にクエリー・プランを最適化してパフォー マンスを向上
– SQEでの論理ファイル(LF)の処理をサポート
IBM i 7.1 での CQE( クラッシック・クエリー・エンジン）
– CQEも従来どおり提供。以下のようなSQL業界標準で はないインターフェースの処理時に使用
• Query/400
• 一部のベンダー製アプリケーション