SQLEXEC

有効な対象

ExtractおよびReplicat

摘要

SQLEXECパラメータでは、Oracle GoldenGate処理の範囲でストアド・プロシージャ、問合せまたはデータベース・コマンドを実行します。SQLEXECにより、Oracle GoldenGateはデータベースと直接通信し、データベースによってサポートされている処理を実行できます。この処理は、列変換のための値の取得など、同期プロセスの一部であっても、データベース内でアクションを実行するストアド・プロシージャの実行など、データの抽出またはレプリケートと関係なくてもかまいません。

ノート: SQLEXECは最小限のグローバリゼーション・サポートを提供します。ソース・キャプチャのキャプチャ・パラメータ・ファイルでSQLEXECを使用するには、ソース.prmファイルのクライアント文字セットが同じであるか、ソース・データベースの文字セットのスーパーセットであることを確認します。

SQLEXECは、次のように機能します。

ノート: SQLEXECONBEFOREIMAGEパラメータは、ビフォア・イメージ・レコードに対するSQLEXECの実行をサポートします。

ノート: EXECタイプがTRANSACTION、SOURCEROWまたはONCEの場合、SQLEXECONBEFOREIMAGEパラメータは、PK更新または統合更新レコードでは無視されます。

単独SQLEXEC

単独SQLEXECパラメータとは、パラメータ・ファイルのルート・レベルで使用され、TABLEまたはMAPパラメータと無関係に機能するパラメータです。次に、単独SQLEXECパラメータを使用するためのガイドラインを示します。

単独SQLEXECの使用に関するその他の情報

SQLEXECの使用方法の詳細は、SQLEXECを使用したコマンド、ストアド・プロシージャおよび問合せの実行を参照してください。

単独SQLEXECの構文

SQLEXEC
{'call <procedure_name>()' | '<SQL_query>' | '<database_command>'}
[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]
[ONEXIT]
[, THREADS (<threadID>[, <threadID>][, ...][, <thread_range>[, <thread_range>][, ...])]

'コール <procedure_name ()'>

実行するストアド・プロシージャ名を指定します。文は一重引用符で囲みます。callキーワードは必須です。次に、単独SQLEXECを使用したプロシージャの実行方法の例を示します。

SQLEXEC 'call prc_job_count ()'

<'SQL_query'>

実行する問合せ名を指定します。問合せは一重引用符で囲で囲みます。データベースで必要とされる同じ形式で、大/小文字を区別するオブジェクト名を指定します。次に、単独SQLEXECを使用した問合せの実行方法の例を示します。

SQLEXEC ' select x from dual '

複数の行にわたる問合せでは、各行で一重引用符を使用します。最良の結果を得るために、各開始引用符の後ろおよび各終了引用符の前に(または少なくとも各終了引用符の前に)空白を入力してください。

<'database_command'>

データベース・コマンドを実行します。次に、単独SQLEXECを使用したデータベース・コマンドの実行方法の例を示します。

SQLEXEC 'SET TRIGGERS OFF'

EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}

単独ストアド・プロシージャまたは問合せを、定義済間隔で実行します。たとえば:

SQLEXEC 'call prc_job_count ()' EVERY 30 SECONDS

間隔は正の整数である必要があります。

ONEXIT

ExtractまたはReplicatプロセスが正常に停止したときにSQLを実行します。たとえば:

SQLEXEC 'call prc_job_count ()' ONEXIT

THREADS (<threadID>[, <threadID>][, ...][, <thread_range>[, <thread_range>][, ...])

調整Replicatで指定したスレッド(複数または複数)に対してのみSQLEXECを実行します。

<threadID>[, <threadID>][, ...]

threadID, threadID, threadIDというフォーマットでスレッドIDまたはスレッドのカンマ区切りリストを指定します。

[, <thread_range>[, <thread_range>][, ...]

スレッドの範囲をthreadIDlow-threadIDhighの形式で指定するか、範囲のカンマ区切りリストをthreadIDlow-threadIDhigh, threadIDlow-threadIDhighの形式で指定します。

threadID, threadID, threadIDlow-threadIDhighのように、これらの形式を組み合せて指定することもできます。

THREADS句を使用しない場合、ADD REPLICATコマンドによってこのReplicatグループに構成されたすべてのスレッドでSQLが実行されます。ただし、SQLがバリア・トランザクションの基準を満たす場合、実際のスリーダー・マッピングにかかわらず、SQLEXEC文全体がスレッド0で処理されます。

TABLEまたはMAPパラメータ内でのSQLEXEC

SQLEXECパラメータをTABLEまたはMAPパラメータ内で使用すると、パラメータの受入れ可否にかかわらず、ストアド・プロシージャまたは問合せを実行できます。次に、MAPまたはTABLE文内で使用する場合のSQLEXECの依存関係および制約事項を示します。

TABLEまたはMAP内でのSQLEXECの構文

SQLEXEC (
{SPNAME <procedure_name>[, ID <logical_name>] |
   ID <logical_name>, QUERY ' <SQL_query> '}
{, PARAMS [OPTIONAL | REQUIRED] <parameter_name> = {<source_column> | <OGG_function>} |
   NOPARAMS}
[, AFTERFILTER | BEFOREFILTER]
[, ALLPARAMS {OPTIONAL | REQUIRED}]
[, ERROR {IGNORE | REPORT | RAISE | FINAL | FATAL}]
[, EXEC {MAP | ONCE | TRANSACTION | SOURCEROW}][, MAXVARCHARLEN <bytes>]
[, PARAMBUFSIZE <bytes>]
[, TRACE]
[, ...]
[, BEFORE_col1 = @BEFORE(col1),
)

SPNAME <procedure_name>[, ID <logical_name>]

ストアド・プロシージャを実行します。

SPNAME <procedure_name>

実行するプロシージャ名を指定します。

次に、lookupという名前のストアド・プロシージャを1回実行する例を示します。このケースでは、実際のプロシージャ名を使用します。論理名は必要ありません。

SQLEXEC (SPNAME lookup), PARAMS (param1 = srccol)), &
COLMAP (targcol = lookup.param1);

ID <logical_name>

プロシージャの論理名(オプション)を定義します。たとえば、lookupという名前のプロシージャの論理名をlookup1、lookup2などのようにできます。このオプションは、MAP文内でプロシージャを複数回実行するときに使用します。1つのMAP文で、プロシージャを最大20回実行できます。プロシージャの実行が1回の場合、IDは必要ありません。

次に、IDオプションを使用して、参照表から値を取得するストアド・プロシージャの複数回実行できるようにする例を示します。値は、ターゲット列にマッピングされます。

SQLEXEC (SPNAME lookup, ID lookup1, &
  PARAMS (long_name = current_residence_state)), &
SQLEXEC (SPNAME lookup, ID lookup2, &
  PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, current_residence_state_long = lookup
1.long_name, & birth_state_long = lookup2.long_name);

`ID , QUERY ' '

問合せを実行します。

ID <logical_name>

問合せの論理名を定義します。問合せ結果から値を抽出するには、論理名が必要です。<ID logical_name>は、問合せによって戻される列値を参照します。

QUERY '<SQL_query>'

データベースに対して実行するSQL問合せの構文を指定します。問合せは、SELECT文の結果を返すか、INSERT、UPDATEまたはDELETE文を実行できます。SELECT文は1行のみ返す必要があります。行が複数返される場合は、最初の行のみ処理されます。SELECT文にはINTO ...句を指定しないでください。問合せは、問合せを実行するデータベースの有効な標準問合せ言語を使用している必要があります。ほとんどの問合せでは、入力パラメータ用のプレースホルダが必要です。問合せ内でのパラメータの指定方法は、次のようにデータベースのタイプによって異なります。

'SELECT NAME FROM ACCOUNT WHERE SSN = :SSN AND ACCOUNT = :ACCT'
'SELECT NAME FROM ACCOUNT WHERE SSN = ? AND ACCOUNT = ?'

問合せは1行に収め、一重引用符で囲む必要があります。どのデータベースでも、パラメータ名を引用符で囲む必要はありません。

次に、OracleおよびSQL Serverの問合せにそれぞれSQLEXEC問合せを使用する例を示します。

Oracleの例:

MAP sales.account, TARGET sales.newacct, &
  SQLEXEC (ID lookup, &
  QUERY 'select desc_col into desc_param from lookup_table &
  where code_col = :code_param', &
  PARAMS (code_param = account_code)), &
  COLMAP (newacct_id = account_id, newacct_val = lookup.desc_param);

SQL Serverの例:

MAP sales.account, TARGET sales.newacct, &
  SQLEXEC (ID lookup, &
  QUERY 'select desc_col into desc_param from lookup_table &
  where code_col = ?', &
  PARAMS (p1 = account_code)), &
  COLMAP (newacct_id = account_id, &
  newacct_val = lookup.desc_param);

PARAMS [OPTIONAL | REQUIRED] <parameter_name> = {<source_column> | <OGG_function>} | NOPARAMS

プロシージャまたは問合せがパラメータを受け付けるかどうかを定義し、受け付ける場合はパラメータを入力ソースにマップします。PARAMS句またはNOPARAMSのいずれかを使用する必要があります。

OPTIONAL REQUIRED

パラメータ値が見つからないときに、プロシージャまたは問合せを実行するかどうかを決定します。

OPTIONALは、SQLの実行にパラメータ値が必要ないことを示します。必要なソース列がデータベース操作で見つからない場合、またはソース列が見つからないために列変換ファンクションを完了できない場合でも、SQLは実行されます。OPTIONALは、Oracle以外のすべてのデータベースのデフォルトです。Oracleでは、ストアド・プロシージャ定義を取得するときに、パラメータがオプションかどうかが自動的に判別されます。

REQUIREDは、パラメータ値が存在している必要があることを示します。パラメータ値が存在しない場合、SQLは実行されません。

<parameter_name> = {<source_column> | <OGG_function>}

パラメータ名を、入力を提供する列またはファンクションにマップします。入力パラメータおよび出力パラメータとしてSQLEXECでサポートされるデータ型は次のとおりです。

<parameter_name>は、次のいずれかです。

SQLEXEC (ID appphone, QUERY ' select per_type from ps_personal_data '
    ' where emplid = :vemplid '
    ' and per_status = 'N' and per_type = 'A' ',
    PARAMS (vemplid = emplid)),
TOKENS (applid = @GETVAL(appphone.per_type));
SQLEXEC (ID appphone, QUERY ' select per_type from ps_personal_data '
    ' where emplid = ? '
    ' and per_status = 'N' and per_type = 'A' ',
    PARAMS (p1 = emplid)),
TOKENS (applid = @GETVAL(appphone.per_type));

<source_column>は、入力を提供するソース列の名前です。デフォルトでは、指定した列が(レコードには更新された列の値のみが含まれるために)ログに存在しない場合、このパラメータはプロシージャまたは問合せでこのパラメータに指定されているデフォルト値を使用します。

<OGG_function>は、入力を提供するために実行するOracle GoldenGate列変換ファンクションの名前です。「表および列マッピング関数」を参照してください。

ストアド・プロシージャまたは問合せからの出力値を入力としてFILTERまたはCOLMAP句に渡すには、次の構文を使用します。

{<procedure_name> | <logical_name>}.<parameter>

ここで:

次の例は、Oracleのソース表およびターゲット表と、参照表のセットに適用されます。これらの例では、ストアド・プロシージャの単一インスタンスおよびストアド・プロシージャの複数インスタンスに、これらの表のパラメータを渡す方法を示します。

ソース表cust:

custid                     Number
current_residence_state    Char(2)
birth_state                Char(2)

ターゲット表cust_extended:

custid                          Number
current_residence_state_long    Varchar(30)
birth_state_long                Varchar(30)

参照表state_lookup

abbreviation     Char(2)
long_name        Varchar(30)

次に、参照表から値を取得するために1回実行されるストアド・プロシージャの使用例を示します。cust表からのレコードを処理するとき、Oracle GoldenGateは、列マップを実行する前にlookupストアド・プロシージャを実行します。プロシージャ内のlong_nameパラメータは、COLMAP文のターゲット列birth_state_longにマッピングされるbirth_stateソースcolumn.The値からの入力を受け付けます。

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

次に、IDオプションを使用して、参照表から値を取得するストアド・プロシージャの複数回実行できるようにする例を示します。値は、ターゲット列にマッピングされます。

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, ID lookup1, &
PARAMS (long_name = current_residence_state)), &
SQLEXEC (SPNAME lookup, ID lookup2, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, current_residence_state_long = lookup1.long_name, & birth_state_long = lookup2.long_name);
AFTERFILTER BEFOREFILTER

AFTERFILTERおよびBEFOREFILTERでは、MAP文のFILTER句との関連でストアド・プロシージャまたは問合せをいつ実行するかを指定します。

AFTERFILTER

FILTER文の後にSQL文を実行します。これにより、フィルタが成功しないかぎり、SQLの実行のオーバーヘッドをスキップできます。これがデフォルト値です。

BEFOREFILTER

SQLの結果をフィルタで使用できるように、FILTER文の前にSQLを実行します。

次に、BEFOREFILTERの使用例を示します。

SQLEXEC (SPNAME check, NOPARAMS, BEFOREFILTER)
ALLPARAMS [OPTIONAL REQUIRED]

ALLPARAMSは、ストアド・プロシージャまたは問合せの実行のために、指定されたすべてのパラメータが存在している必要があるかどうかを決定するグローバル・ルールとして使用します。ALLPARAMSで設定するグローバル・ルールよりも、PARAMS句内で設定する個別のパラメータのルールのほうが優先されます。

OPTIONAL

すべてのパラメータが存在しているかどうかにかかわらず、SQLの実行を許可します。これがデフォルト値です。

REQUIRED

SQLを実行するために、すべてのパラメータが存在している必要があります。

次に、OPTIONALの使用例を示します。

SQLEXEC (SPNAME lookup,
PARAMS (long_name = birth_state, short_name = state),
ALLPARAMS OPTIONAL)
ERROR {IGNORE REPORT RAISE FINAL FATAL}

ERRORでは、ストアド・プロシージャまたは問合せに関連するエラーに対するレスポンスを定義します。明示的なエラー処理の指定がない場合、Oracle GoldenGateプロセスはエラーを検出すると異常終了します。プロシージャでプロセスにエラーを返し、ERRORでレスポンスを指定するようにします。

Oracle GoldenGate 21cでは、この機能を使用して、タイプSQLエラーに基づいて異なる動作を指定できます。

次の例は、ストアド・プロシージャ参照の実行時にSQLエラー・コード1403または1405が検出された場合の異常終了ERプロセスを示します。その他のエラーはすべて報告され、レプリケーションは続行されます。

SQLEXEC ( SPNAME lookup,
  PARAMS( long_name = birth_state, short_name = state),
  ERROR REPORT, ERROR FATAL (1403, 1405) );

IGNORE

Oracle GoldenGateに、ストアド・プロシージャまたは問合せに関連するすべてのエラーを無視させ、処理を継続させます。このパラメータ抽出の結果は、"紛失した列"状態になります。これがデフォルト値です。

REPORT

ストアド・プロシージャまたは問合せに関連するすべてのエラーが破棄ファイルにレポートされるようにします。このレポートは、エラーの原因の追跡に役立ちます。ここには、エラーの説明と、プロシージャまたは問合せとやり取りしたパラメータの値の両方が含まれます。Oracle GoldenGateで、エラーのレポート後、処理が続けられます。

RAISE

REPERRORパラメータで設定されたルールに従ってエラーを処理します。Oracle GoldenGateは、エラーを処理する前に、現在のMAP文に関連する他のストアド・プロシージャまたは問合せの処理を続行します。

FINAL

RAISEと似ていますが、プロシージャまたは問合せに関連するエラーを検出したときに、残りのストアド・プロシージャおよび問合せがバイパスされます。エラー処理は、エラーの直後に起動されます。

FATAL

プロシージャまたは問合せに関連するエラーを検出したときに、即座にOracle GoldenGateを異常終了させます。

EXEC {MAP ONCE TRANSACTION SOURCEROW}

EXECでは、MAP文のストアド・プロシージャまたは問合せを実行する頻度、および出力パラメータを抽出する場合に結果を有効とみなす期間を制御します。

MAP

プロシージャまたはクエリが指定されている各ソースターゲットテーブルマップに対して、プロシージャーまたはクエリーを1回実行します。MAPを使用する場合、同一のソース表を持つそれ以降のマップでは結果は無効になります。MAPはデフォルトです。

次に、デフォルトのMAPの誤った使用例を示します。MAPはデフォルトであるため、SQLEXEC文に明示的にリストする必要はありません。この例では、1つのソース表を別個のMAP文で2つの異なるターゲット表にマップしています。この場合、結果は最初のマッピングに対してのみ有効です。プロシージャlookupの結果は、2番目のMAPパラメータの実行までに無効になってしまうため、2番目のMAPは"紛失した列"の状態となります。各MAPが有効な結果を返すようにこの機能を正しく実装するには、SOURCEROWを使用する必要があります。

MAP sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, PARAMS (param1 = srccol)), &
COLMAP (targcol = lookup.param2);

MAP sales.srctab, TARGET sales.targtab2, &
COLMAP (targcol2 = lookup.param2);

ONCE

Oracle GoldenGateの実行中、関連するMAP文の最初の呼出し時にプロシージャまたは問合せを1回実行します。結果は、プロセスが実行しているかぎり有効です。

次に、ONCEの使用例を示します。

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state), EXEC ONCE), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

TRANSACTION

プロシージャまたは問合せをソース・トランザクションで1回実行します。結果は、トランザクションのすべての操作に有効です。

次に、TRANSACTIONの使用例を示します。

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state), EXEC TRANSACTION), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

SOURCEROW

プロシージャまたは問合せをソース行操作で1回実行します。このオプションは、ソース表を複数のターゲット表と同期し、プロシージャまたは問合せの結果がソース-ターゲット・マッピングのたびに呼び出される場合に使用します。

次に、SOURCEROWの使用例を示します。このケースでは、プロシージャが各ソース行操作で実行されるため、2番目のMAPは有効な値を返します。

MAP sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, PARAMS (param1 = srccol), EXEC SOURCEROW), &
COLMAP (targcol = lookup.param2);

MAP sales.srctab, TARGET sales.targtab2, &
COLMAP (targcol2 = lookup.param2);

MAXVARCHARLEN <bytes>

MAXVARCHARLENでは、ストアド・プロシージャまたは問合せ内のパラメータの出力値に割り当てる最大バイト長を指定します。この最大値を超える出力値は切り捨てられます。明示的にMAXVARCHARLEN句を使用しない場合のデフォルトは、255バイトです。有効な値の範囲は、50から32767バイトです。

次に、出力値のバイト長を100に制限する例を示します。

MAXVARCHARLEN 100

PARAMBUFSIZE <bytes>

PARAMBUFSIZEでは、入力パラメータと出力パラメータの両方を含むSQLEXECパラメータ情報を保持するメモリー・バッファに許可する最大バイト数を指定します。明示的にPARAMBUFSIZE句を使用しない場合のデフォルトは、10,000バイトです。有効な値の範囲は、1000から2000000バイトです。Oracle GoldenGateは、パラメータに割り当てられたメモリーと最大値との差が500バイト以内に達するたびに警告を発行します。

次に、バッファを15,000バイトに増やす例を示します。

PARAMBUFSIZE 15000
TRACE {ALL ERROR}

TRACEでは、SQLEXECの入力および出力パラメータをレポート・ファイルに記録します。

次に、SQLEXECトレースが有効になっているレポート・ファイルの例を示します。

Input parameter values...
LMS_TABLE: INTERACTION_ATTR_VALUES
   KEY1: 2818249
  KEY2: 1
Report File:
From Table MASTER.INTERACTION_ATTR_VALUES to MASTER.INTERACTION_ATTR_VALUES:
       #  inserts:      0
       #  updates:      0
       #  deletes:      0
       #  discards:     1

  Stored procedure GGS_INTERACTION_ATTR_VALUES:
       attempts:        2
       successful:      0

ALL

呼び出された各プロシージャまたは問合せの入力および出力パラメータをレポート・ファイルに書き込みます。これがデフォルト値です。

ERROR

SQLエラーの発生後にのみ、呼び出された各プロシージャまたは問合せの入力および出力パラメータをレポート・ファイルに書き込みます。