例外表

例外表は、IMMEDIATE CHECKED オプションを指定した SET INTEGRITY ステートメントを使用して検査する対象として指定された表の定義を模倣して作成されたユーザー作成の表です。例外表は、検査対象の表の行のうち制約に違反しているもののコピーを保管するのに使用されます。

ロード・ユーティリティーによって使用される例外表はここで説明されているものと同じなので、それらは SET INTEGRITY ステートメントでの検査中に再使用できます。

例外表の作成規則

例外表を作成する際の規則は、次のとおりです。

表がセキュリティー・ポリシーによって保護される場合、例外表も同じセキュリティー・ポリシーによって保護されることが必要です。
例外表の最初の n 列は、検査対象の表の列と同じです。名前、データ・タイプ、および長さなど、すべての列属性が同じでなければなりません。保護されている列については、列を保護するセキュリティー・ラベルが両方の表で同じであることが必要です。
例外表のすべての列は、制約やトリガーに束縛されないようにする必要があります。制約には、参照整合性、チェック制約、さらには挿入時にエラーの原因となるユニーク索引の制約が付帯します。
例外表の第 (n+1) 列は、オプションの TIMESTAMP 列です。これは、データを検査するための SET INTEGRITY を発行する前に例外表内の行が削除されなかった場合に、同じ表の SET INTEGRITY ステートメントによる検査の連続呼び出しを検出するのに使用します。タイム・スタンプの精度は、0 から 12 までのいずれかの値で、割り当てられる値は CURRENT TIMESTAMP 特殊レジスターの結果です。
第 (n+2) 列は、CLOB(32K) タイプまたはそれより大きいタイプでなければなりません。この列は、行内のデータが違反している制約の名前を示すために使用されるもので、オプションではありますが、なるべく使用するようにしてください。この列を用意しなかった場合 (例えば、元の表の列数が既に可能な最大値になっていた場合にはそれが可能)、制約違反が検出された行だけがコピーされます。
(n+1) 列と (n+2) 列の両方を備えた例外表を作成する必要があります。
上記の追加列に、特定の名前の制約はありません。しかし、タイプの指定は、正確でなければなりません。
それ以外の列は使用できません。
生成される列 (IDENTITY プロパティーも含む) が元の表にある場合は、例外表の対応する列に、生成されるプロパティーを指定しないでください。
データのチェックのために SET INTEGRITY ステートメントを呼び出すユーザーは、例外表に対して INSERT 特権を保持している必要があります。
例外表は、データ・パーティション表、範囲がクラスター化された表、またはデタッチされた表であってはなりません。
例外表は、マテリアライズ照会表またはステージング表であってはなりません。
例外表には、従属する REFRESH IMMEDIATE マテリアライズ照会表、または従属する PROPAGATE IMMEDIATE ステージング表があってはなりません。

「メッセージ」列の情報の構造は次のとおりです。

表 1. 例外表のメッセージ列の構造
フィールド番号	内容	サイズ	コメント
1	制約違反の数	5 バイト	先頭に "0" を付加して右揃え
2	最初の制約違反の種類	1 バイト	"D" - 削除規則 : カスケード違反 "F" - 外部キー違反 "G" - 生成列違反 "I" - ユニーク索引違反 ^a "K" - チェック制約違反 "L" - LBAC 書き込み規則違反 "P" - データ・パーティション違反 "S" - 無効な行セキュリティー・ラベル "X" - XML 列に定義された索引の違反 ^d
3	制約/列^b /索引 ID^c の長さ	5 バイト	先頭に "0" を付加して右揃え
4	制約名/列名^b/索引 ID^c	直前のフィールドで指定される長さ
5	区切り記号	3 バイト	<space><colon><space>
6	次の制約違反の種類	1 バイト	"D" - 削除規則 : カスケード違反 "F" - 外部キー違反 "G" - 生成列違反 "I" - ユニーク索引違反 "K" - チェック制約違反 "L" - LBAC 書き込み規則違反 "P" - データ・パーティション違反 "S" - 無効な行セキュリティー・ラベル "X" - XML 列に定義された索引の違反^d
7	制約/列/索引 ID の長さ	5 バイト	先頭に "0" を付加して右揃え
8	制約名/列名/索引 ID	直前のフィールドで指定される長さ
.....	.....	.....	違反ごとにフィールド 5 から 8 を繰り返す。
^a SET INTEGRITY ステートメントを使用した検査の際には、それがアタッチ操作の後でない限り、ユニーク索引違反は起こりません。しかし、FOR EXCEPTION オプションを選択した場合に LOAD を実行すると、それが報告されます。ただし、LOAD はチェック制約、生成列、外部キー、削除カスケード、データ・パーティションに関するいずれの違反も例外表に報告しません。 ^b 生成列の式をカタログ・ビューから取り出すには、 select ステートメントを使用します。例えば、フィールド 4 が MYSCHEMA.MYTABLE.GEN_1 の場合、SELECT SUBSTR(TEXT, 1, 50) FROM SYSCAT.COLUMNS WHERE TABSCHEMA='MYSCHEMA' AND TABNAME='MYNAME' AND COLNAME='GEN_1'; は、式の最初の 50 バイトを AS (<expression>) の形式で戻します。 ^c カタログ・ビューから索引 ID を取り出すには、 select ステートメントを使用します。例えば、フィールド 4 が 1234 であれば、 SELECT INDSCHEMA, INDNAME FROM SYSCAT.INDEXES WHERE IID=1234 となります。 ^d XML 列に定義された索引の違反の場合、制約名、列名、または索引 ID フィールドによって、整合性違反の索引を持つ XML 列を特定できます。これは、整合性違反を持つ索引は特定しません。が発生している XML 列の名前のみを特定します。例えば、メッセージ列の値「X00006XTCOLZ」は、XTCOL2 列のいずれかの索引で発生した索引違反を特定します。

例外表での行の処理

例外表の情報は、さまざまな方法で処理できます。データを訂正して、行を元の表に再挿入できます。

元の表に INSERT トリガーがないなら、例外表に対する副照会の入った INSERT ステートメントを発行することによって、修正した行を転送します。

INSERT トリガーがあり、トリガーを起動することなく例外表からの修正済みの行によるロード操作を完了したい場合は、次のような方法があります。

目的に合わせて明示的に定義された列において、値に応じて起動されるように INSERT トリガーを設計します。
例外表からのデータをアンロードして、ロード・ユーティリティーを使用してそれを付加します。その場合、データを再検査するにあたっては、制約検査の対象は付加された行だけに限定されないことに注意してください。
関連するシステム・カタログ・ビューのトリガー定義テキストを保存します。次に、INSERT トリガーをドロップし、INSERT を使用して修正された行を例外表から転送します。最後に、保存したトリガー定義を使ってトリガーを再作成します。

例外表から行を挿入する際にトリガーが起動しないように防止する明示的な機能はありません。

ユニーク索引の違反に対しては、行ごとに 1 つの違反しか報告されません。

LONG VARCHAR、LONG VARGRAPHIC、または LOB データ・タイプの値が表の中に入っている場合、ユニーク索引違反があっても、その値は例外表に挿入されません。

例外表の照会

例外表のメッセージ列の構造は、前述の制約の名前、長さ、および区切り文字を連結したリストです。この情報は、照会可能です。

例えば、すべての違反のリストを取得し、各行ごとに制約名だけを繰り返すとします。元の表 T1 に C1 と C2 という 2 つの列があるとします。また、対応する例外表 E1 には、T1 のものと対応する列 C1 および C2 があり、さらにメッセージ列 MSGCOL があると想定します。以下の照会では再帰を使用して、行ごとに 1 つの制約名を示します (複数の違反がある行は繰り返します)。

WITH IV  (C1, C2, MSGCOL, CONSTNAME, I, J) AS
 (SELECT C1, C2, MSGCOL,
     CHAR(SUBSTR(MSGCOL, 12,
                 INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,7,5)),5,0)))),
     1,
     15+INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,7,5)),5,0))
    FROM E1
  UNION ALL
  SELECT C1, C2, MSGCOL,
      CHAR(SUBSTR(MSGCOL, J+6,
                  INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,J+1,5)),5,0)))),
      I+1,
      J+9+INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,J+1,5)),5,0))
    FROM IV
    WHERE I < INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,1,5)),5,0))
 ) SELECT C1, C2, CONSTNAME FROM IV;

特定の制約に違反したすべての行のリストを作成するには、前述の照会を次のように拡張します。

WITH IV  (C1, C2, MSGCOL, CONSTNAME, I, J) AS
 (SELECT C1, C2, MSGCOL,
      CHAR(SUBSTR(MSGCOL, 12,
                  INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,7,5)),5,0)))),
      1,
      15+INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,7,5)),5,0))
    FROM E1
  UNION ALL
  SELECT C1, C2, MSGCOL,
      CHAR(SUBSTR(MSGCOL, J+6,
                 INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,J+1,5)),5,0)))),
      I+1,
      J+9+INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,J+1,5)),5,0))
    FROM IV
    WHERE I < INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,1,5)),5,0))
 ) SELECT C1, C2, CONSTNAME FROM IV WHERE CONSTNAME = 'constraintname';

次の照会を使用して、すべてのチェック制約違反を取得できます。

WITH IV  (C1, C2, MSGCOL, CONSTNAME, CONSTTYPE, I, J) AS
  (SELECT C1, C2, MSGCOL,
       CHAR(SUBSTR(MSGCOL, 12,
                   INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,7,5)),5,0)))),
       CHAR(SUBSTR(MSGCOL, 6, 1)),
       1,
       15+INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,7,5)),5,0))
     FROM E1
   UNION ALL
   SELECT C1, C2, MSGCOL,
       CHAR(SUBSTR(MSGCOL, J+6,
                  INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,J+1,5)),5,0)))),
       CHAR(SUBSTR(MSGCOL, J, 1)),
       I+1,
       J+9+INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,J+1,5)),5,0))
     FROM IV
     WHERE I < INTEGER(DECIMAL(VARCHAR(SUBSTR(MSGCOL,1,5)),5,0))
 ) SELECT C1, C2, CONSTNAME FROM IV WHERE CONSTTYPE = 'K';