例外表の追加の列を含むサンプル例外マッピング

次のサンプル・パラメータ・ファイルは、以降のCDRの例で使用されるソース表およびターゲット表のエラー処理と複雑な例外マッピングを示しています。この例では、例外表にソース表と同じ行があり、コンテキスト・データを取得するための追加の列もあります。

ノート:

この例では、Replicatパラメータ・ファイルに必要なその他のパラメータ(プロセス名やログイン資格証明、特定のデータベース・タイプに必要なオプションのパラメータなど)を意図的に省略してあります。改行を使用してパラメータ文を複数の行に分割するときは、各行の末尾でアンパサンド(&)を使用します。

    -- REPERROR error handling: DEFAULT represents all error types. DISCARD
    -- writes operations that could not be processed to a discard file.
REPERROR (DEFAULT, DISCARD)
    -- Specifies the discard file.
DISCARDFILE /users/ogg/discards/discards.dsc, PURGE
    -- The regular MAP statement with the CDR parameters
MAP fin.src, TARGET fin.tgt, &
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL), &
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_mod_time)), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_mod_time)), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD)) 
);
    -- Starts the exceptions MAP statement by mapping the source table to the -- exceptions table.
MAP fin.src, TARGET fin.exception, &
    -- directs Replicat only to map operations that caused the error specified
    -- in REPERROR.
EXCEPTIONSONLY, &
    -- directs Replicat to convert all the exceptions to inserts into the
    -- exceptions table. This is why there cannot be a primary key constraint
    -- on the exceptions table.
INSERTALLRECORDS &
    -- SQLEXEC query to select the values from the target record before the
    -- Replicat statement is applied. These are mapped to the *_target
    -- columns later.
SQLEXEC (id qry, query 'select name, phone, address, salary, balance, & comment, last_mod_time from fin.tgt where name = :p1', PARAMS(p1 = name )), &
    -- Start of the column mapping, specifies use default column definitions.
COLMAP ( &
    -- USEDEFAULTS maps the source columns to the target exceptions columns
    -- that receive the after image that Replicat applied or tried to apply.
    -- In this case, USEDEFAULTS can be used because the names and definitions 
    -- of the source and target exceptions columns are identical; otherwise
    -- the columns must be mapped explicitly in the COLMAP clause.
USEDEFAULTS, &
    -- captures the timestamp when the resolution was performed.
res_date = @DATENOW (), &
    -- captures and maps the DML operation type.
optype = @GETENV ('LASTERR', 'OPTYPE'), &
    -- captures and maps the database error number that was returned.
dberrnum = @GETENV ('LASTERR', 'DBERRNUM'), &
    -- captures and maps the database error that was returned.
dberrmsge = @GETENV ('LASTERR', 'DBERRMSG'), &
    -- captures and maps the name of the target table
tabname = @GETENV ('GGHEADER', 'TABLENAME'), &
    -- If the names and definitions of the source columns and the target
    -- exceptions columns were not identical, the columns would need to
    -- be mapped in the COLMAP clause instead of using USEDEFAULTS, as 
    -- follows:
       -- name_after = name, &
       -- phone_after = phone, &
       -- address_after = address, &
       -- salary_after = salary, &
       -- balance_after = balance, &
       -- comment_after = comment, &
       -- last_mod_time_after = last_mod_time &
    -- maps the before image of each column from the trail to a column in the
    -- exceptions table.
name_before = @BEFORE (name), &
phone_before = @BEFORE (phone), &
address_before = @BEFORE (address), &
salary_before = @BEFORE (salary), &
balance_before = @BEFORE (balance), &
comment_before = @BEFORE (comment), &
last_mod_time_before = @BEFORE (last_mod_time), &
    -- maps the results of the SQLEXEC query to rows in the exceptions table
    -- to show the current image of the row in the target.
name_current = qry.name, &
phone_current = qry.phone, &
address_current = qry.address, &
salary_current = qry.salary, &
balance_current = qry.balance, &
comment_current = qry.comment, &
last_mod_time_current = qry.last_mod_time)
;

現在のルーチンがすべての状況で予定どおり動作することを確認したら、解決ルーチンのオーバーヘッドを削減するために、例外表に記録されるデータの量を減らすことができます。