このステートメントは、 ユーザー定義の外部表関数をアプリケーション・サーバーに登録する場合に使用されます。
表関数 は、SELECT の FROM 文節で使用することができ、 行を一度に 1 行戻すことによって、SELECT に表を戻します。
呼び出し
このステートメントは、アプリケーション・プログラムに組み込むか、 または動的 SQL ステートメントを使用して発行することができます。 このステートメントは、動的に準備可能な実行可能ステートメントです。 しかし、バインド・オプション DYNAMICRULES BIND を適用する場合、 ステートメントを動的に準備することはできません (SQLSTATE 42509)。
許可
このステートメントの許可 ID には、以下の特権が少なくとも 1 つ含まれている必要があります。
非分離の関数を作成するには、ステートメントの許可 ID の特権に 以下の特権の少なくとも 1 つが含まれている必要があります。
分離関数を作成する場合には、さらに別の権限や特権は必要ありません。
許可 ID の権限が不十分で、操作を実行できない場合には、 エラー (SQLSTATE 42502) になります。
構文
>>-CREATE FUNCTION--function-name-------------------------------> >----(--+--------------------------------------------------------+---)-> | .-,------------------------------------------------. | | V | | '----+----------------+--data-type1--+-------------+--+--' '-parameter-name-' '-AS LOCATOR--' >----*----------------------------------------------------------> .-,------------------------------------------. V | >----RETURNS TABLE--(-----column-name--data-type2--+-------------+--+---)-> '-AS LOCATOR--' >----*----+--------------------------+--*-----------------------> '-SPECIFIC--specific-name--' >----EXTERNAL--+-----------------------+---*--------------------> '-NAME--+-'string'---+--' '-identifier-' (1) >----LANGUAGE--+-C----+---------*-------------------------------> +-JAVA-+ '-OLE--' >----PARAMETER STYLE--+-DB2SQL-----+---*------------------------> '-DB2GENERAL-' .-NOT DETERMINISTIC--. .-FENCED-----. >-----+--------------------+--*----+------------+--*------------> | (2) | '-NOT FENCED-' '-DETERMINISTIC------' .-RETURNS NULL ON NULL INPUT--. >-----+-----------------------------+--*--NO SQL--*-------------> | (3) | '-CALLED ON NULL INPUT--------' .-EXTERNAL ACTION----. .-NO SCRATCHPAD----------. >-----+--------------------+--*----+------------------------+---> '-NO EXTERNAL ACTION-' | .-100----. | '-SCRATCHPAD--+--------+-' '-length-' .-NO FINAL CALL--. >----*----+----------------+--*---DISALLOW PARALLEL---*---------> '-FINAL CALL-----' .-NO DBINFO--. >-----+------------+--*----+-----------------------+--*---------> '-DBINFO-----' '-CARDINALITY--integer--' >-----+------------------------------+------------------------->< '-TRANSFORM GROUP--group-name--'
注:
説明
暗黙または明示の修飾子を含む名前、 およびパラメーターの数と各パラメーターのデータ・タイプ (データ・タイプの長さ、 精度、または位取りの各属性には関係なく) は、 カタログに記述されている関数を指定するものであってはなりません (SQLSTATE 42723)。 非修飾名とパラメーターの数およびデータ・タイプとの組み合わせは、 そのスキーマ内では当然固有ですが、複数のスキーマ間で固有である必要はありません。
2 つの部分からなる名前を指定する場合、 "SYS" で始まる schema-name (スキーマ名) は使用できません (SQLSTATE 42939)。
述部のキーワードとして使用される多くの名前は、システム使用として予約されており、 function-name として使用することはできません (SQLSTATE 42939)。 それに含まれる名前は、 SOME、 ANY、 ALL、 NOT、 AND、 OR、 BETWEEN、 NULL、 LIKE、 EXISTS、 IN、 UNIQUE、 OVERLAPS、 SIMILAR、 MATCH、 および 基本述部に記載されている比較演算子です。
関数のシグニチャーに何らかの差異があれば、同じ名前を複数の関数に使用することができます。 禁止されてはいませんが、外部ユーザー定義関数の名前として、 組み込み関数と同じ名前を指定すべきではありません。
パラメーターのない関数も登録可能です。 この場合、指定するデータ・タイプがない場合でも、括弧はコーディングする必要があります。 たとえば、
CREATE FUNCTION WOOFER() ...
その対応するすべてのパラメーターのタイプがまったく同じである場合でも、 1 つのスキーマ中に名前が同じ 2 つの関数があってはなりません。 このタイプの比較では長さ、精度、および位取りは考慮されません。 したがって、CHAR(8) と CHAR(35)、また DECIMAL(11,2) と DECIMAL (4,3) は、 それぞれ同じタイプとみなされます。 さらに、DECIMAL と NUMERIC などのように、 この目的で複数のタイプが同じタイプとして扱われることがあります。 シグニチャーが重複していると、SQL エラー (SQLSTATE 42723) になります。
たとえば、次のステートメントの場合、
CREATE FUNCTION PART (INT, CHAR(15)) ... CREATE FUNCTION PART (INTEGER, CHAR(40)) ... CREATE FUNCTION ANGLE (DECIMAL(12,2)) ... CREATE FUNCTION ANGLE (DEC(10,7)) ...
2 番目と 4 番目のステートメントは、 重複する関数とみなされ、エラーになります。
次の例は、パラメーター定義の AS LOCATOR 文節の使用法を示しています。
CREATE FUNCTION foo ( CLOB(10M) AS LOCATOR, IMAGE AS LOCATOR) ...
ここで、IMAGE は LOB タイプの 1 つに基づく特殊タイプであると想定します。
また、引き数のプロモーション目的には、AS LOCATOR 文節の効果はないことに注意してください。 この例では、タイプはそれぞれ CLOB と IMAGE であると見なされるので、 関数に CHAR 引き数または VARCHAR 引き数が最初の引き数として渡されます。 同様に、関数シグニチャーに対して AS LOCATOR の効果はありません。 関数シグニチャーは、(a) "関数解決" と呼ばれるプロセスによって DML で参照された場合、 (b) COMMENT ON や DROP などの DDL ステートメントで参照された場合に 関数をマッチングする際に使用されます。 実際に、この文節はシグニチャーの指定のない COMMENT ON や DROP で使用しても、 しなくても構いません。
LOB 以外のタイプ、または LOB に基づく特殊タイプに対して AS LOCATOR を指定すると、 エラー (SQLSTATE 42601) が発生します。
関数が FENCED の場合、AS LOCATOR 文節は指定できません (SQLSTATE 42613)。
この文節で使用できる有効なタイプについては、 *** ページで説明されています。
specific-name は、既存の関数名 と同じであっても構いません。
修飾子を指定しない場合、function-name に使用された修飾子が使用されます。 修飾子を指定する場合は、 function-name の明示修飾子または暗黙修飾子と同じでなければなりません。 そうでない場合、エラー (SQLSTATE 42882) になります。
specific-name の指定がない場合、 固有の名前がデータベース・マネージャーによって生成されます。 生成される固有の名前は、 SQL の後に文字のタイム・スタンプが続く名前です (SQLyymmddhhmmssxxx)。
NAME 文節を指定しない場合、"NAME function-name" が想定されます。
'string' オプションは、最大 254 文字のストリング定数です。 ストリングに使用される形式は、指定した LANGUAGE によって異なります。
指定する string (ストリング) は、 作成しているユーザー定義関数を実行するためにデータベース・マネージャーが呼び出すライブラリー名と、 そのライブラリー中の関数名です。 ライブラリー (およびそのライブラリー中の関数) は、 CREATE FUNCTION ステートメントの実行時に存在している必要はありません。 ただし、関数が SQL ステートメントで使用される時点では、 そのライブラリーとそのライブラリー内の関数が存在していなければならず、 しかもデータベース・サーバーのマシンからアクセス可能でなければなりません。
>>-'--+-library_id-------+---+-------------+---'--------------->< '-absolute_path_id-' '-!--func_id--'
単一引用符内に、余分なブランクを使用することはできません。
UNIX 系システムの library_id が 'myfunc' で、 データベース・マネージャーが /u/production から実行されている場合、 ライブラリー /u/production/sqllib/function/myfunc から関数を探索します。
OS/2 および Windows 32 ビット オペレーティング・システム の場合、library_id が関数ディレクトリーにないと、 データベース・マネージャーは LIBPATH または PATH を探します。
OS/2 では、library_id に 9 文字以上を指定しないようにします。
たとえば、UNIX 系システムで '/u/jchui/mylib/myfunc' を指定すると、 データベース・マネージャーは /u/jchui/mylib の中から myfunc 関数を探します。
OS/2 および Windows 32 ビット オペレーティング・システム の場合、 'd:\mylib\myfunc' を指定すると、 データベース・マネージャーは d:\mylib ディレクトリーから myfunc.dll ファイルをロードします。
OS/2 では、この指定の最後の部分 (すなわち dll の名前) は、9 文字以上にしないでください。
たとえば、UNIX 系システムで 'mymod!func8' と指定すると、 データベース・マネージャーはライブラリー $inst_home_dir/sqllib/function/mymod を調べて、 そのライブラリー内の入り口点 func8 を使用します。
OS/2、および Windows 32 ビット オペレーティング・システム の場合 'mymod!func8' を指定すると、 データベース・マネージャーは mymod.dll ファイルをロードして、 そのダイナミック・リンク・ライブラリー (DLL) の func8() 関数を呼び出します。
ストリングの形式が正しくない場合には、エラー (SQLSTATE 42878) になります。
いずれの場合も、すべての外部関数の本体は、 データベースのすべての区分で使用可能なディレクトリーにある必要があります。
指定する string には、 作成中のユーザー定義関数を実行するためにデータベース・マネージャーが呼び出す、 任意指定の jar ファイル、クラス識別子、およびメソッド識別子が含まれています。 クラス識別子とメソッド識別子は、 CREATE FUNCTION ステートメントの実行時には存在している必要はありません。 jar_id を指定する場合、識別子は、 CREATE FUNCTION ステートメントの実行時に存在していなければなりません。 ただし、関数を SQL ステートメントで使用する時点で、 メソッド識別子は存在しなければならず、 データベース・サーバーのマシンからアクセス可能でなければなりません。
>>-'----+------------+--class_id---+-.-+--method_id--'--------->< '-jar_name :-' '-!-'
単一引用符内に、余分なブランクを使用することはできません。
指定する string は、 作成中のユーザー定義関数を実行するためにデータベース・マネージャーが呼び出す、 OLE のプログラム識別子 (progid) またはクラス識別子 (clsid)、およびメソッド識別子です。 プログラム識別子またはクラス識別子、およびメソッド識別子は、 CREATE FUNCTION ステートメントの実行時に存在している必要はありません。 ただし、関数を SQL ステートメントで使用する時点で、 メソッド識別子は存在していなければならず、 データベース・サーバーのマシンからアクセス可能でなければなりません。 そうでない場合、エラー (SQLSTATE 42724) になります。
>>-'--+-progid-+---!--method_id--'----------------------------->< '-clsid--'
単一引用符内に、余分なブランクを使用することはできません。
progid は、データベース・マネージャーには解釈されず、 実行時に OLE API に転送されるだけです。 指定する OLE オブジェクトは、作成可能である必要があり、 実行時バインディング (ディスパッチに基づくバインディングとも呼ばれる) を サポートしている必要があります。
{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}
ここで 'n' は英数字です。 clsid は、データベース・マネージャーには解釈されず、 実行時に OLE API に転送されるだけです。
LANGUAGE OLE は、 DB2 (Windows 32 ビット オペレーティング・システム 版) で保管されたユーザー定義関数に対してのみサポートされます。
LANGUAGE OLE DB 外部表関数の作成の詳細は、CREATE FUNCTION (OLE DB 外部表) を参照してください。
DB2GENERAL の同義語として値 DB2GENRL が使用可能です。
関数が FENCED として登録されると、データベース・マネージャーは、 その内部資源 (データ・バッファーなど) を隔離して、 その関数からアクセスされないようにします。 多くの関数は、FENCED または NOT FENCED のどちらかで実行するように選択することができます。 一般に、FENCED として実行される関数は、 NOT FENCED として実行されるものと同じようには実行されません。
警告: | 適切にコード化、検討、および検査されていない関数に NOT FENCED を使用すると、
DB2 の保全性に危険を招く場合があります。
DB2 では、発生する可能性のある一般的な不注意による障害の多くに対して、
いくつかの予防措置がとられていますが、
NOT FENCED ユーザー定義関数が使用される場合には、完全な保全性を確保できません。
FENCED を使用すればデータベースの保全性を大幅に確保できるものの、 適切にコード化、検討、および検査して FENCED UDF を使用しなければ、 DB2 に障害が発生することがあります。 |
ほとんどのユーザー定義関数は、FENCED または NOT FENCED のどちらでも実行できるはずです。 LANGUAGE OLE を指定した関数には、FENCED のみを指定できます (SQLSTATE 42613)。
FENCED から NOT FENCED に変更するには、 関数を削除して再作成し、関数を再登録する必要があります。 ユーザー定義関数を NOT FENCED として登録するには、SYSADM 権限、DBADM 権限、 または特殊権限 (CREATE_NOT_FENCED) が必要です。
関数が FENCED の場合、AS LOCATOR 文節は指定できません (SQLSTATE 42613)。
RETURNS NULL ON NULL INPUT が指定されており、 表関数 OPEN が実行されるときに、関数の引き数のいずれかがヌル値の場合、 ユーザー定義関数は呼び出されません。 試行した表関数スキャンの結果は、空の表になります (行のない表)。
CALLED ON NULL INPUT が指定されると、 引き数がヌル値か否かに関係なくユーザー定義関数が呼び出されます。 これは、ヌル値を戻す場合も、通常の (ヌル値以外の) 値を戻す場合もあります。 ただし、ヌルの引き数値の有無のテストは UDF が行う必要があります。
値 NULL CALL は、上位互換またはファミリーの互換性のために、 CALLED ON NULL INPUT の同義語として使うことができます。 同様に、NOT NULL CALL は、RETURNS NULL ON NULL INPUT の同義語として使えます。
SCRATCHPAD を指定すると、ユーザー定義関数の最初の呼び出し時に、 その外部関数によって使用されるスクラッチパッドにメモリーが割り振られます。 このスクラッチパッドには、次の特性があります。
SELECT A.C1, B.C2 FROM TABLE (UDFX(:hv1)) AS A, TABLE (UDFX(:hv1)) AS B WHERE ...
NO FINAL CALL が指定またはデフォルト指定されている場合は、 DB2 が OPEN 呼び出しごとにスクラッチパッドを初期化し直すので、 外部表関数は CLOSE 呼び出し時に、 スクラッチパッドに固定されているすべての資源に対して終結処理を行います。 FINAL CALL または NO FINAL CALL の判別、 およびスクラッチパッドの関連する動作は、重要な考慮事項です。 表関数が副照会または結合で使用されるときは、 ステートメントの実行中に複数の OPEN 呼び出しが生じ得るので、特に重要です。
(上で概説したように、FINAL CALL/NO FINAL CALL キーワードは、 スクラッチパッドの再初期化を制御するために使用され、 スクラッチパッドに固定されている資源を表関数が解放する時期を指示します。)
SCRATCHPAD を指定すると、ユーザー定義関数を呼び出すたびに、 スクラッチパッドをアドレス指定する外部関数に追加の引き数が渡されます。
NO SCRATCHPAD を指定すると、 外部関数に対してスクラッチパッドは割り振られず、渡されません。
外部表関数の場合、どのオプションが選択されたかにかかわらず、 呼び出しタイプ引き数は常に存在します。 この引き数とその値の詳細については、アプリケーション開発の手引き を参照してください。
エラー発生時の、これらの呼び出しの表 UDF 処理については、 アプリケーション開発の手引き に説明があります。
DBINFO を指定すると、以下の情報を含む構造が UDF に渡されます。
構造の詳細、および構造が UDF にどのようにして渡されるかについては、 アプリケーション開発の手引き を参照してください。
表関数に対して CARDINALITY 文節の指定がない場合、 DB2 はデフォルト値として有限の値を想定します (RUNSTATS ユーティリティーが 統計を収集していない表に対して想定される値と同じ)。
警告: 関数が事実上無限のカーディナリティーを持っている場合 (すなわち、 呼び出されるといつでも行を戻し、"end-of-table" 条件を戻さない)、 正しく機能するために "end-of-table" 条件を必要とする照会は無限に実行されるので、 照会を中断させる必要があります。 このような照会の例として、GROUP BY および ORDER BY を使用する照会があります。 このような UDF は書かないことをお勧めします。
注
例
例 1: 以下の例では、 テキスト管理システムにおいて既知の各文書の 1 つの文書識別列からなる行を戻す表関数を登録しています。 最初のパラメーターは指定された対象領域をマッチングし、 2 番目パラメーターには指定された文字列が入ります。
単一セッションのコンテキスト内では UDF は常に同じ表を戻すため、 UDF は DETERMINISTIC として定義されています。 DOCMATCH からの出力を定義する RETURNS 文節に注意してください。 それぞれの表関数に対して、FINAL CALL を指定する必要があります。 さらに、この表関数は並列して実行できないので、 DISALLOW PARALLEL キーワードが追加されています。 DOCMATCH の出力のサイズは大きく変動しますが、 DB2 最適化プログラムにとって有用な CARDINALITY 20 が 代表値として指定されています。
CREATE FUNCTION DOCMATCH (VARCHAR(30), VARCHAR(255)) RETURNS TABLE (DOC_ID CHAR(16)) EXTERNAL NAME '/common/docfuncs/rajiv/udfmatch' LANGUAGE C PARAMETER STYLE DB2SQL NO SQL DETERMINISTIC NO EXTERNAL ACTION NOT FENCED SCRATCHPAD FINAL CALL DISALLOW PARALLEL CARDINALITY 20
例 2: 以下の例では、 Microsoft Exchange のメッセージのメッセージ・ヘッダー情報と、 部分的なメッセージ・テキストの検索に使用する OLE 表関数を登録しています。 この表関数を実装するコードの例については、アプリケーション開発の手引き を参照してください。
CREATE FUNCTION MAIL() RETURNS TABLE (TIMERECIEVED DATE, SUBJECT VARCHAR(15), SIZE INTEGER, TEXT VARCHAR(30)) EXTERNAL NAME 'tfmail.header!list' LANGUAGE OLE PARAMETER STYLE DB2SQL NOT DETERMINISTIC FENCED CALLED ON NULL INPUT SCRATCHPAD FINAL CALL NO SQL EXTERNAL ACTION DISALLOW PARALLEL