Oracle Database Provider for DRDAのSQL翻訳および例

4.1 SQL翻訳プロセスの概要

SQL翻訳の概念は、Oracle Databaseリリース12cで導入されました。この機能により、DB2などの外部のSQL文を、Oracle Databaseが正しく使用できるSQL構文に翻訳できるようになります。SQL翻訳自体は、主にサードパーティから翻訳用に提供されるSQLトランスレータにより実装されます。SQLトランスレータは、入力SQLを検査し、場合によってはOracle SQLの構文に準拠するように入力SQLを変更します。SQL翻訳プロファイルは、SQL Translator Interface Packageで指定されます。このプロファイルにより、使用するSQLトランスレータが指定されます。

SQL翻訳プロファイルは、SQL TRANSLATION PROFILE型のスキーマ・レベルのオブジェクトです。ATTR_TRANSLATOR属性を介してPL/SQLパッケージを参照します。このパッケージはSQL Translator Interface Packageと呼ばれます。このパッケージは、SQL翻訳プロファイルがアクティブなときにSQL翻訳を実行するサードパーティのSQLトランスレータを指定します。一度に1つのSQL翻訳プロファイルだけを有効にできます。

SQL翻訳プロファイルは、ユーザー間で共有できます。一般には、すべてのユーザーが1つのSQL翻訳プロファイルを一連のパッケージで共有しますが、これは必須ではありません。

各DB2パッケージは、そのパッケージで保持されている属性によってSQL翻訳プロファイルに関連付けられます。DB2パッケージに対して指定されたSQL翻訳プロファイルに関連付けられているSQLトランスレータは、そのDB2パッケージ内のSQL文を準備する際に使用されます。

4.1.1 SQL翻訳の実装

翻訳が実行されるようにするには、次の操作が、示されている順序で実施される必要があります。

SQLトランスレータを取得します。
そのトランスレータを参照するSQL Interface Packageを作成します。
SQL Interface Packageを参照するSQL翻訳プロファイルを作成します。

この手順はインスタンスの存続期間中に1回だけ行う必要があります。ただし、SQL翻訳を使用するにはこれを少なくとも1回は実行する必要があります。

複数のトランスレータを使用する場合や、異なるSQL翻訳プロファイルが必要な場合には、このプロセスを繰り返します。
SQL翻訳プロファイルとDB2パッケージを関連付けます。

この手順は、作成するパッケージごとに実行する必要があります。

パッケージを翻訳プロファイルに関連付ける前にパッケージを作成する必要はありません。パッケージの名前だけが必要となります。この手順では、特定のパッケージがすでに存在しているかどうかは検証されません。
実行時には、ユーザーはパッケージを介してOracle Database Provider for DRDAにSQLテキストを渡します。
Oracle Database Provider for DRDAは、SQLテキストを取得すると、パッケージにSQL翻訳プロファイルが関連付けられているかどうかを確認してから、SQLテキストの解析および実行時にそのSQL翻訳プロファイルを有効にするように設定します。
Oracle Database Provider for DRDAがSQLテキストを実行のために準備した後で、Oracle Databaseが現在のSQL翻訳プロファイルを使用してSQL文を翻訳し、実行します。

4.1.2 SQL翻訳の要件

SQL翻訳は、次の状況に該当する場合にのみ正常に完了します。

次のコマンドを使用してSQL翻訳プロファイルがセッションに対して有効になっている必要があります。
```
ALTER SESSION SET SQL_TRANSLATION_PROFILE
```
受信するSQL文が外部の構文またはOracle以外のSQL言語で記述されていることを、プロセスが指定している必要があります。この項で説明するすべてのケースでは、SQL言語はDB2 SQLのバリエーションです。

Oracle Database Provider for DRDA製品の場合、前述の2つの条件が結合されます。つまり、DB2パッケージにSQL翻訳プロファイルが関連付けられている場合、SQL文は外部構文で記述されていると想定されます。また、そのパッケージでSQLを翻訳するときにはSQL翻訳プロファイルに関連付けられているSQLトランスレータがコールされます。

4.2 DB2固有のSQL構文の翻訳に関する詳細

クライアント・アプリケーションがOracle Database Provider for DRDAに送信するSQLコンストラクトのほとんどは直接実行されますが、一部のDB2 SQLコンストラクトはOracleによって認識されません。Oracle Databaseインスタンスを使用するように再構成されたアプリケーションによって発行されたDB2 SQL文を翻訳する際に発生する既知の問題について検討してください。

SQL翻訳プロファイルが存在する場合、そのプロファイルに関連付けられているSQLトランスレータは、これらのSQL文を変更するように設計されています。これにより、アプリケーションが同等または類似の操作をOracle SQLで実行し、予期されている結果を戻すことができます。

4.2.1 DB2の特殊レジスタ

Oracle Databaseでは、現在の時刻を取得するCURRENT TIME特殊レジスタ・コンストラクトはサポートされていません。次の例に示すように、このコンストラクトを呼び出すとORA-00936エラーが発生します。

SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1

4.2.2 DB2 SQLファンクションおよびプロシージャ

Oracleでは、DB2で定義されているファンクションの一部がサポートされていません。たとえば、CEILINGファンクションはOracleには存在しませんが、Oracle SQL構文にはこのファンクションと互換性のあるCEILファンクションがあります。

4.2.3 DB2名前付きデータ型

Oracleでは、BIGINTなどのいくつかの基本的なSQLデータ型が定義されていません。アプリケーションがOracleに対して実行される場合、列値または定数をBIGINTとしてキャストすると、エラーが発生します。次の例では、BIGINTが有効なOracleデータ型として認識されていないためため、ORA-00902エラーが発生します。

SELECT CAST(12345678912 AS BIGINT) from SYSIBM.SYSDUMMY1

4.2.4 DB2構文の文

DB2のINSERT文には、Oracleで解釈されない構文の句が含まれています。このような例としては、次のコード例に示すisolation句があります。

INSERT INTO SCOTT.DEPT VALUES(50, "FARMING", "SPRINGFIELD") WITH CHG

4.3 SQL Translator Interface Package

SQL翻訳プロファイルは、DBMS_SQL_TRANSLATOR.CREATE_PROFILE()プロシージャを使用して作成されたSQL TRANSLATION PROFILE型のスキーマ・レベルのオブジェクトです。SQL Translator Interface Packageは、特定の形式のPL/SQLパッケージであり、サードパーティ提供のトランスレータ・オブジェクトを参照する一方で、それ自身がSQL翻訳プロファイルから参照されます。SQL Translator Interface Packageは、SQL翻訳プロファイルおよびサードパーティ提供のSQLトランスレータ・オブジェクトに接続します。

4.3.1 SQL Translator Interface Packageについて

SQL翻訳プロファイルは、固定形式のPL/SQLラッパー・パッケージであるSQL Translator Interface Packageを参照します。セッションでSQL TRANSLATION PROFILEが設定されると、すべてのSQLがSQL Translator Interface Packageに関連付けられているサードパーティのSQLトランスレータによって翻訳されることが指定されます。SQL Translator Interface Packageのプロシージャtranslate_sql()により翻訳が実行されます。

OracleからはSQLトランスレータは提供されないことに注意してください。かわりに、サードパーティ・ベンダーからSQLトランスレータを取得するか、または内部で開発する必要があります。Oracleでは、SQL翻訳プロファイルを作成および管理するための様々な管理スクリプトが用意されています。

4.3.2 SQL Translator Interface Packageの作成

例4-1に、SQL翻訳プロファイルとともに使用される単純なSQL Translation Interface Packageを示します。言語と名前の指定は、サードパーティのSQLトランスレータの言語タイプおよびコール可能な名前に関連しています。SYSDBA権限でOracle Databaseにログインした後に、次のパッケージ宣言を行う必要があります。パッケージ名は、SQL翻訳プロファイルのTRANSLATOR_ATTR属性の値です。

例4-1 SQL Translator Interface Packageの作成

create or replace package SYSIBM.DBTooSQLTranslator as
  procedure translate_sql(
    sql_text in CLOB, 
   translated_text out CLOB);
  procedure translate_error(
    error_code in BINARY_INTEGER,
    translated_code out BINARY_INTEGER,
    translated_sqlstate out VARCHAR2);
end;
/
create or replace package body SYSIBM.DBTooSQLTranslator as
  procedure translate_sql(
    sql_text in CLOB,
    translated_text out CLOB ) 
    as language JAVA
  name /* actually the "signature" of the third-party callable */
  /* procedure associated with translate_sql    */
   'DBTooSQLApiInterface.translateSQL(oracle.sql.CLOB, oracle.sql.CLOB[])';
 
   procedure translate_error(error_code in BINARY_INTEGER,
    translated_code out BINARY_INTEGER,
    translated_sqlstate out VARCHAR2) as
  language JAVA
  name /* actually the "signature" of the third-party callable */
  /* procedure associated with translate_error  */
 'DBTooSQLApiInterface.translateError(oracle.sql.CLOB, oracle.sql.CLOB[])';
 end;
 /

4.3.3 SQL Translator Interface PackageへのEXECUTE権限の付与

SQL Translator Interface Packageは実行時にコールされるため、EXECUTE権限が有効になっている必要があります。例4-2に、このアクセス権限を付与する方法を示します。

例4-2 SQL Translator Interface PackageへのEXECUTE権限の付与

GRANT EXECUTE ON SYSIBM.DBTooSQLTranslator TO DRDAAS_USER_ROLE

4.3.4 SQL翻訳プロファイルの作成

SQL翻訳プロファイルは、CREATE SQL TRANSLATION PROFILE権限およびTRANSLATE ANY SQL権限を持つユーザーが作成および管理できます。「DRDAAS_TRANS_ADMINロールを持つユーザーへの必要な権限の付与」の項に、これら2つの権限をDRDAAS_TRANS_ADMINに付与する方法を示しています。これらの権限は、既存のSYSDBA権限を持つユーザーが付与できます。

ADMIN OPTION句により、DRDAAS_TRANS_ADMINが他のOracleユーザーに対しTRANSLATE ANY SQL権限をGRANT(付与)できるようになります。このようにして、「DRDAAS_USER_ROLEロールを持つユーザーへの必要な権限の付与」に示すように、DRDAAS_TRANS_ADMINにより、DRDAAS_USER_ROLEを持つ複数のユーザーが翻訳機能を使用できるようになります。

実際のSQL翻訳プロファイルは、「SQL翻訳プロファイルの作成および管理」に示すスクリプトを使用して管理できます。管理IDには、CREATE SQL TRANSLATION PROFILEを実行するために必要な権限がすでに付与されている必要があることに注意してください。

関連トピック

4.3.4.1 管理者ロールを使用した翻訳権限の付与

例4-3 DRDAAS_TRANS_ADMINロールを持つユーザーへの必要な権限の付与

GRANT CREATE SQL TRANSLATION PROFILE TO DRDAAS_TRANS_ADMIN;
GRANT TRANSLATE ANY SQL TO DRDAAS_TRANS_ADMIN WITH ADMIN OPTION;

4.3.4.2 ユーザー・ロールを使用した翻訳権限の付与

例4-4 DRDAAS_USER_ROLEを持つユーザーへの翻訳権限の付与

GRANT TRANSLATE ANY SQL TO DRDAAS_USER_ROLE;

4.3.4.3 SQL翻訳プロファイルの作成および管理

例4-5 SQL翻訳プロファイルの作成および管理

declare
  PROFILE_DOES_NOT_EXIST exception;
  pragma EXCEPTION_INIT(PROFILE_DOES_NOT_EXIST, -24252);
  /* profile_name is the nsme of the SQL Translation Profile */
  /* created here.  */
  profile_name VARCHAR2(32) := 'DRDAAS_TRANS_ADMIN.MY_PROFILE';
 
  /* SYSIBM is the schema in which the SQL Translator Interface */
  /* package (viz., SYSIBM.DBTooSQLTranslator) is found.   */
  sql_trnsltr_intfc_schema VARCHAR2(32) := 'SYSIBM';
 
  /* DBTooTranslator is the unqualified package name of the SQL */
  /* Translator Interface Package */
  sql_trnsltr_intfc_pkgnm VARCHAR2(32) := 'DBTooSQLTranslator';
 
  sql_trnsltr_intfc_pkg VARCHAR2(128);
  grant_cmd VARCHAR2(256);
  cursor_id NUMBER;

begin
  sql_trnsltr_intfc_pkg := sql_trnsltr_intfc_schema || '.'  ||
    sql_trnsltr_intfc_pkgnm;
  begin
    DBMS_SQL_TRANSLATOR.DROP_PROFILE(profile_name);
    exception
      WHEN PROFILE_DOES_NOT_EXIST THEN NULL; /* ignore if non-existant */
  end;
  /* Create SQL Translation Profile */
  DBMS_SQL_TRANSLATOR.CREATE_PROFILE(profile_name);
  /* Associate the SQL Translator Interface Package denoted by */
  /* sql_trnsltr_intfc_pkg with this profile    */
  DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE(profile_name,
    DBMS_SQL_TRANSLATOR.ATTR_TRANSLATOR,
    sql_trnsltr_intfc_pkg);	
  /* Mark this SQL Translation Profile as "registered" */
  DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE(profile_name,
    DBMS_SQL_TRANSLATOR.ATTR_TRANSLATION_REGISTRATION,
    DBMS_SQL_TRANSLATOR.ATTR_VALUE_TRUE);
  /* The owner of the SQL Translator Interface Package must have */
  /* full authority for the SQL TRANSLATION PROFILE    */
  grant_cmd := 'GRANT ALL ON SQL TRANSLATION PROFILE ' ||
    profile_name || ' TO ' || sql_trnsltr_intfc_schema;
  cursor_id := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(cursor_id, grant_cmd, DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(cursor_id);
  /* Let all with DRDAAS_USER_ROLE have access to the SQL Translation profile.  */
  grant_cmd := 'GRANT USE ON SQL TRANSLATION PROFILE ' ||
    profile_name || ' TO DRDAAS_USER_ROLE';
  cursor_id := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(cursor_id, grant_cmd, DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(cursor_id);
end;
/

4.4 サードパーティのSQLトランスレータの使用

サードパーティのトランスレータを使用しするには、トランスレータのファイルとオブジェクトがディレクトリ$ORACLE_HOME/rdbms/drdaas/jlibにインストールされている必要があります。

問題が発生した場合は、DBMS_JAVA.SET_OUTPUT()プロシージャを使用して、サーバー側のエラー・メッセージをDBMS_OUTPUTにリダイレクトします。

OracleでのJavaの使用の詳細は、Oracle® Database Java開発者ガイドを参照してください。

4.4.1 単一オブジェクトとしてロードされたサードパーティSQLトランスレータの使用

サードパーティのSQLトランスレータがJavaで作成されている場合、例4-6はSYSDBAユーザーによってSQL*Plus環境で実行されます。例4-6では、DBMS_JAVA.LOADJAVA()プロシージャを使用してSYSIBMスキーマにオブジェクトをロードしています。この例では1つのサードパーティ・オブジェクトDBTooSQLAPI.jarがロードされます。

例4-6 サードパーティのSQLトランスレータのロード(単一オブジェクト)

begin
  DBMS_JAVA.LOADJAVA('-definer -genmissing -schema SYSIBM ' || '
  ' rdbms/drdaas/jlib/DBTooSQLAPI.jar', 
  '((* SYSIBM)(* PUBLIC)(* -))');
end;
/

4.4.2 複数オブジェクトとしてロードされたサードパーティSQLトランスレータの使用

サードパーティのトランスレータが複数オブジェクトで構成されている場合、各コンポーネントをLOADJAVAコールに指定する必要があります。例4-7では、2つのトランスレータ・オブジェクト、DBTooSQLAPI.jarおよびDBTooMainClass.classが指定されています。

例4-7 サードパーティのSQLトランスレータのロード(複数オブジェクト)

begin
  DBMS_JAVA.LOADJAVA('-definer -genmissing -schema SYSIBM ' ||
  ' rdbms/drdaas/jlib/DBTooMainClass.class' ||
  ' rdbms/drdaas/jlib/DBTooSQLAPI.jar',
  '((* SYSIBM)(* PUBLIC)(* -))');
end;
/

4.5 トランスレータ管理スクリプトの使用

Oracle Databaseには、翻訳プロファイルを管理するdrdasqtt_translator_setup.sqlスクリプトが付属しています。このスクリプトは、SYSDBA権限を持つユーザーがSQL*Plusで起動する必要があります。次の情報の入力が求められます。

SQL Translator Interface Packageスキーマ(SYSIBMなど)。
SQL Translator Interface Package名(DBTooTranslatorなど)。
SQL翻訳プロファイル・スキーマ(DRDAAS_TRANS_ADMINなど)。
SQL翻訳プロファイル名(MY_PROFILEなど)。
サードパーティ・トランスレータの言語タイプ(Javaなど)。
サードパーティ・ベンダーから提供されるファイルまたはオブジェクトの名前。複数を指定する場合は、次のコードに示すように4つの一重引用符でリストを囲み、各項目を空白で区切ります。
```
''''rdbms/drdaas/jlib/DBTooMainClass.class rdbms/drdaas/jlib/DBTooSQLAPI.jar''''
```
SQL翻訳に使用される、サードパーティ・ベンダー提供のファイルまたはオブジェクトでのエントリのシグネチャ(エントリ名と引数の記述)。Javaベースのサードパーティ・コードの場合、 javapプログラムからシグネチャを取得できます。空白を含むシグネチャは二重引用符で囲む必要があることに注意してください。
エラー・コードの翻訳に使用される、サードパーティ・ベンダー提供のファイルまたはオブジェクト内のエントリのシグネチャ(エントリ名と引数の記述)。Javaベースのサード・パーティ・コードの場合は、javapプログラムを介してシグネチャを取得できます。空白を含むシグネチャは二重引用符で囲む必要があることに注意してください。

4.6 SQLトランスレータ・プロファイルの検証

次の手順では、SQL翻訳プロファイルが正しくインストールされ、適切に有効化されていることを確認します。

SQLトランスレータ・プロファイルの構成を検証するには、次のようにします。

SYSDBA権限を使用してOracle Databaseにログインします。
トランスレータ・プロファイルがOracle Databaseにロードされていることを確認します。
```
SELECT * FROM ALL_SQL_TRANSLATION_PROFILES;
```
DRDAAS_USER_ROLE権限を持つIDを使用してログインします。
ロールが設定されていることを確認します。
```
SET ROLE DRDAAS_USER_ROLE;
```
セッションのSQL翻訳プロファイルを、SQL翻訳プロファイルの作成時に指定された値に設定します。
```
ALTER SESSION SET SQL_TRANSLATION_PROFILE = DRDAAS_TRANS_ADMIN.MY_PROFILE;
```
次のコマンドを実行します。
```
ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';
SELECT CAST(1234567 AS BIGINT) FROM DUAL;
ALTER SESSION SET EVENTS = '10601 trace name context off';
SELECT CAST(1234567 AS BIGINT) FROM DUAL;
```
最初のSELECTは正常に完了し、2番目のSELECTは失敗します。ALTER SESSION SET EVENTSコマンドは、SQLが次のいずれかであることを指定します。
- 外部構文(trace name context forever, level 32)
- ネイティブOracle構文(trace name context off)
これは、SQL*Plusを使用している場合にのみ機能します。

4.7 SQL翻訳プロファイルの変更

場合によっては、SQL翻訳プロファイルを変更して、DB2パッケージのSQL TRANSLATION PROFILE属性が新しいSQL翻訳プロファイルを参照するようにする必要があります。

DB2パッケージは通常セットで提供され、DB2パッケージの名前はクライアントによって決定されます。ただし、クライアントがODBCを使用してOracle Database Provider for DRDAにアクセスする場合、パッケージ名はODBCドライバによって決定します。

パッケージ・セットのSQL翻訳プロファイル属性を設定するための2つのスクリプトがOracleから提供されています。

DataDirect ODBCドライバがOracle Database Provider for DRDAにアクセスする場合、drdaas/adminディレクトリにあるdrdasqt_set_profile_dd.sqlスクリプトを使用します。
IBM ODBCドライバがOracle Database Provider for DRDAにアクセスする場合、drdaas/adminディレクトリにあるdrdasqt_set_profile_ibm.sqlスクリプトを使用します。ネイティブ・クライアント・アプリケーションもこのスクリプトを使用することがありますが、この場合は拡張する必要があります。

これらのスクリプトをコピーし、他のDB2パッケージ・セットで使用できるように変更できます。

また、各スクリプトはDRDAAS_ADMIN_ROLE権限を持つユーザーがSQL*Plusで実行する必要があります。スクリプトでは、様々なパッケージが参照するプロファイル(DRDAAS_TRANS_ADMIN.MY_PROFILEなど)の修飾名の入力が求められます。また、デフォルトのパッケージ・コレクション・スキーマ(通常はNULLID)の入力が求められます。