本文へジャンプ

データベース/データ管理  >  DB2 Developer Domain  >  ライブラリー  >  技術白書-DB2ファミリー関連  >  
　 　
　

さらにもう1つの上級スクリプト記述に関する記事



Paul Yip著
IBMトロント研究所
2002年11月
　
　
コンテンツ
はじめに
DB2 V8.1におけるSQLプロシージャー言語（SQL PL）スクリプト記述の新機能
SQLステートメントの正常実行の検査
外部プログラムを呼び出す便利なワザ
表スペース文節を使用しない柔軟な表を作成するDDLスクリプトの記述
まとめ
著者について
著者
Paul Yip著
IBMトロント研究所

はじめに
この記事は、前回の記事「Advanced SQL Procedural Scripting in DB® V7.2（DB2® V7.2の上級SQLプロシージャー・スクリプト記述）」の続編です。この記事では、新しい洞察、ヒントおよび便利なワザを紹介します。これは、前回の記事に対して読者から寄せられたフィードバックおよびその後の追加質問に対して執筆した記事のため、あまりきちんとした構成になっていません。というより、実際のところ、構成はありません。しかし各セクションの見出しから、必要な個所を素早く見つけ出すことができるはずです。

すべての例はUNIX®用に記述されています。少し変更すれば（説明があります）、Windows®でも同様に動作します。

DB2 V8.1におけるSQLプロシージャー言語（SQL PL）スクリプト記述の新機能
DB2 Universal Databaseバージョン8における主な拡張機能は次の2つです。

  • パーティション・データベースのサポート
  • FETCH FIRST n ROWSのサポート

パーティション・データベースのサポート
トリガーとスクリプト記述についてのSQL PL使用に関する筆者の別の記事を読んでいれば、冒頭部の注に気付いたと思います。

サンプル・コードは、DB2パーソナル・エディション、ワークグループ・エディション、およびエンタープライズ・エディション用に開発され、テストされたものです。これらサンプル・コードを使用して問題が生じた場合は、筆者に連絡してください。

これは、エンタープライズ拡張エディション（EEE）では、動的複合ステートメントのSQL PLはサポートされていないことを密かに伝えたものです。DB2バージョン8では、この制限はなくなっています。傍注ですが、エンタープライズ・エディションとエンタープライズ拡張エディションはバージョン8でエンタープライズ・サーバー・エディション（ESE）に統合されました。

FETCH FIRST n ROWSのサポート
FETCH FIRST n ROWSのサポートによって、複数の列が結果として抽出される際に、結果セットの変数割当てを制限します。この文節の使用方法をリスト1に示します。

リスト1. 副選択を持つFETCH FIRST n ROWSの使用例

　 
  BEGIN ATOMIC
    DECLARE v_name VARCHAR(30);
    SET v_name = (SELECT firstnme || ' ' || lastname
                     FROM employee
                     FETCH FIRST 1 ROWS ONLY);
SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT=v_name;
END@

FETCH FIRST 1 ROWS ONLY文節を使用しない場合、SELECTが複数の行を戻すと、変数v_nameには単一の値しか割り当てることができないため、エラーが発生します。読者のなかには、DB2バージョン7.2での次善の策として、ラベル付きFORループとLEAVEステートメントを使用して最初の行のみを取り出させるカーソルをシミュレートすることを思い付いた方もいるかもしれません。

FETCH FIRST 1 ROWS ONLY文節は、最初の行のみが戻されることを保証するため、（たとえSELECTが行を戻さず、NULLが戻されたとしても）SETステートメントが失敗することは絶対にありません。文節が目的どおりに機能していることを示すため、上記の例では、カスタム・メッセージを持つSIGNAL SQLSTATEを使用しています。このメッセージは、EMPLOYEE表の1番目の従業員名です。

　 
  $ db2 -td@ -f fig1.db2

DB21034E  The command was processed as an SQL statement because it was not a valid
  Command Line Processor command.  During SQL processing it returned:
SQL0438N  Application raised error with diagnostic text: "CHRISTINE HAAS".
SQLSTATE=80000
上に戻る

　

SQLステートメントの正常実行の検査
1組のアプリケーション・インストール・スクリプトがあるシナリオを想像してください。たとえば、基本データをデータベースに挿入する1つのスクリプトがあるとします。最初のスクリプトに正常終了しなかったオペレーションが存在する場合、インストール第2段階の一部であるもう1つのスクリプトを絶対に実行しないようにします。このような場合の唯一の処理方法は、最初のスクリプトが重要なSQLオペレーションの成功／失敗をログに記録して、2番目のスクリプトを実行すべきかどうかを決定できるようにすることです。

ここで、最近筆者の注意を引いた、ちょっと便利なワザを紹介します。この例をセットアップするには、次のように2つの表を作成します。

　 
db2 "create table SQL_LOG (description VARCHAR(20), status SMALLINT)"
db2 "create table T1 (c1 INT NOT NULL UNIQUE)"

SQL_LOG表は、重要なSQL実行の成功／失敗のトラッキングに使用します。表T1は、例を進行するために使用します。表T1の大きな特徴は、C1列で定義されたユニーク制約を持っていることです。

リスト2のステートメントを見てください。

リスト2. シェル・スクリプト記述とDB2戻りコード

　 
db2 "insert into T1 values (1)"
db2 "insert into SQL_LOG values ('insert first value', $?)"
db2 "insert into T1 values (1)"
db2 "insert into SQL_LOG values ('insert second value', $?)"

Windows：　「$?」を「%errorlevel%」に置き換えます。

このスクリプトはそんなに難しいものではありません。数値1がT1に2回挿入されています。T1に値を挿入するたびに、オペレーションの説明および値「$?」とともにINSERTをSQL_LOG表に対して実行します。T1への2番目の挿入は、ユニーク制約のために失敗します。

変数「$?」は、直前のSQLステートメント実行の戻りコードを保持しています。「$?」が0（ゼロ）の場合、ステートメントは成功です。「$?」が1の場合、ステートメントは警告付きで成功です。その他の値は、失敗を意味します。

ここで、SQL_LOG表のすべての列を選択した場合の出力を示します。予想通り、2番目のエントリーは失敗を示しています。

　 
SELECT * FROM SQL_LOG

DESCRIPTION          STATUS
-------------------- ------
insert first value        0
insert second value       4

  2 record(s) selected.

制約事項：この機能は、シェル・スクリプト記述だけでサポートされています。DB2スクリプトでは無効です。すなわち、例のように各ステートメントのプレフィックスが「db2」であるスクリプトの場合のみ、「$?」および「%errorlevel%」が値を戻します。

上に戻る


外部プログラムを呼び出す便利なワザ
元の記事「Advanced SQL Procedural Scripting（上級SQLプロシージャー・スクリプト記述）」では、動的複合SQLステートメントを使用したSQL PLでのスクリプト記述方法を説明しました。また、OSコマンドに感嘆符「!」のプレフィックスを付けてこのようなコマンドを呼び出す方法についても解説しました。この2つの機能をいろいろ試していれば、複合SQLステートメント内ではOSコマンドの呼出しがサポートされていないことに気付いたかもしれません。つまり、これらのいずれか一方は実行できますが、両方を一緒に実行することはできません。

リスト3は、サポートされていない構文例を示します。

リスト3. 「!」は動的複合ステートメント内ではサポートされない

　 
BEGIN ATOMIC
  IF (CURRENT DATE < DATE('2002-12-25')) THEN
    ! echo "SQL PL Book available soon!" >> messages.log
  ELSE
    ! echo "SQL PL Book now available!" >> messages.log
  END IF;
END@

動的複合ステートメントは、SQL PLのスクリプト記述、トリガー、およびユーザー定義関数（UDF）をサポートするのに使用されるため、すべて同じ制限をもっています。この制限に対処するにはどうしたらよいでしょうか？

幸いなことに、純粋なSQLでは使用できないアクションをUDFを使って実行する、ちょっとしたワザがあります。UDFは、（SQL以外の）外部言語で記述可能であり、CREATE FUNCTIONステートメントを使用してデータベースに登録できます。この例では、外部言語としてJava?を使用します。

Javaコードは、OS呼出しの実行など、通常のJavaアプリケーションの（ほとんどの）機能を実行できます。外部関数は登録後、通常のデータベース関数が使用できるところであればどこにでも使用できます。リスト4は、外部プログラムの呼出しを容易にするJavaコードを示します。

リスト4. os_cmdUDF.javaの外部UDFコード

　 
import java.io.*;

public class os_cmdUDF {
    public static int os_cmd(String cmd) {
        Runtime rt = Runtime.getRuntime();
        Process p=null;
        int success = 0;
        try {
            p = rt.exec(cmd);
        }
        catch (IOException e) {
            success = -1;
        }
        return (success);
    }
}
  1. 上記コードをテキスト・エディターにコピーして、os_cmdUDF.javaファイルとして保存します。Javaソース・ファイルでは、ファイル名はケース・センシティブであり、クラス名と一致する必要があるため、命名には十分注意しなければなりません。上記のコードは、クラス名はos_cmdUDF、メソッド名はos_cmd()ととても単純であり、単一のStringパラメーターをとります。Javaに精通していなくても心配はいりません。理解すべき大事なことは、os_cmd()が受け取るパラメーターはOS呼出しとして実行されるということです。OS呼出しが成功した場合、関数は0を戻し、失敗した場合は-1を戻します。

  2. この外部JavaプログラムをDB2関数として構築するには、コードをコンパイルしてsqllib/functionディレクトリーにコピーし、CREATE FUNCTIONステートメントを発行する必要があります。

    a. このJavaプログラムをコンパイルするには、次のコマンドを実行します。
    　 
    javac os_cmdUDF.java
    　 　
    b. その結果、Javaプログラムのコンパイル形式であるos_cmdUDF.classというファイルが作成されます。次に、os_cmdUDF.classファイルをsqllib/functionディレクトリーにコピーします。
    　 
    cp os_cmdUDF.class sqllib/function/
    　 　
    c. 最後のステップとして、CREATE FUNCTIONステートメントを使用してJava UDFを登録します。データベースに接続した後、リスト5に示すステートメントを発行します。

    リスト5. CREATE PROCEDUREを使用した外部UDFの登録
    　 
    CREATE FUNCTION OS_CMD ( IN  VARCHAR(1000))
RETURNS INTEGER
EXTERNAL NAME 'os_cmdUDF!os_cmd'
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT DETERMINISTIC
NO SQL
EXTERNAL ACTION


  3. これで、コマンドをパラメーターとしてos_cmd() UDFに渡すことによって、外部OS呼び出しを実行できるようになりました。
    リスト6は、新しい関数を使用するための上記DB2スクリプトの変更方法を示します。

    リスト6. os_cmd()の使用例
    　 
    BEGIN ATOMIC
  DECLARE status INT DEFAULT 0;

  DECLARE script VARCHAR(20)
          DEFAULT '/home/db2inst1/sqllib/write_msg.sh ';

  IF (CURRENT DATE < DATE('2002-11-30')) THEN
    SET status = os_cmd(script || ' DB2 SQL PL Book Available Soon!');
  ELSE
    SET status = os_cmd(script || ' DB2 SQL PL Book Now Available!');
  END IF;

  IF (status <> 0) THEN
    SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='OS command failed';
  END IF;
END@


この例では、パラメーターとして渡されたテキストをログ・ファイルに付加するwrite_msg.shというUNIXのスクリプトを実行しようと試みています。リスト3のコードとの違いは太字で強調表示されています。絶対パスによるスクリプト名が変更された場合の便宜上、スクリプト名を保持するために変数「script」が追加されています。

推奨： あいまいさを排除するために、呼び出されたスクリプトの絶対パスを指定します。

リスト7に示すように、write_msg.sh内のコードは単純です。

リスト7. write_msg.shの内容

　 
# Simple message writer script
echo $@  >> /home/db2inst1/application/messages.log

UNIX：　確実にwrite_msg.shスクリプトが自身の実行ビット・セットを持ち、message.logファイルがDB2分離ユーザーID（db2fenc1）によって記述できるようにします。

Windows：　スクリプト名にwrite_msg.batを使用し、「$@」を「%*」に置き換えて、適切なWindowsのパス構文を使用します。

UDFを十分に活用するために、UDFの戻りコードをチェックするコードがさらに追加されています。OSコマンドが何らかの理由で失敗した場合、SQLSTATE 80000が戻されます。

ところで、このサンプル・コードではよくわからなかった場合は、SQL PLに関する新しい本が近日刊行される予定ですので、ご利用ください。（『DB2 SQL Procedural Language for Linux, Unix and Windows』、IBM Press/Prentice Hall、 ISBN 0-13-100772-6）この本（ドラフト）の抜粋は、http://www7b.boulder.ibm.com/dmdd/library/techarticle/0209yip/splbook.htmlでご覧いただけます。また、ISBN番号によってあらゆるオンライン書店で予約できます。

この新しい外部オペレーション実行方法を自由に使用できる場合の可能性を想像してみてください。

  • スカラー関数が有効な場合はいつも、SQLステートメント、他の関数、トリガー、およびストアード・プロシージャーの一部としてUDFが使用できます。
  • OSスクリプトを呼び出すことによって、ほぼ全部のOSオペレーションを実行できます。

この新しい方法によって何かクリエイティブなことを実現した場合は、ぜひ著者にご一報ください。

上に戻る

　

表スペース文節を使用しない柔軟な表を作成するDDLスクリプトの記述
表スペース文節を使って表を作成するパラメーター化されたスクリプトの記述方法はないかと、これまでに何度か尋ねられました。デフォルト表スペース（USERSPACE1）以外の特定表スペース内で表を作成する標準的な手法は、CREATE TABLEコマンドの一部として表スペース名を指定することです。たとえば、表スペースTS1に表T1を作成するには、たぶん次のDDLステートメントを使用するでしょう。

　 
CREATE TABLE T1 (c1 INT) IN TS1;

頻繁に使用されているが、異なる環境で実行されるたびに表スペース名が変わる1組のDDLスクリプトがあるシナリオを考えます（たとえば、それぞれ独自の表スペース命名規則を持つ多くの顧客がいる場合）。次のようなコマンドを使用して、できる限り柔軟なDDLスクリプトを作成できるようにしたいと思うはずです。

　 
CREATE TABLE T1 (c1 INT) IN $v_tablespace

これはDB2スクリプトでは現在サポートされていないため、ここでは、この問題を解決する方法をお教えします。この解決策では、CREATE TABLEステートメントがIN文節を指定していない場合に、DB2が使用する表スペースの決定方法を利用します。

DB2の表スペース選択方法
DB2は、次のルールに従って表の表スペースを選択します。

  1. 存在する場合、ユーザーがUSE特権と十分なページ・サイズを持っているIBMDEFAULTGROUP区分グループ内の表スペースを選択します。
  2. 存在する場合、ユーザーがUSE特権と十分なページ・サイズを持っている表スペースを選択します。
  3. エラー（SQLSTATE 42727）を発行します。

複数の表スペースが適合する場合、USE特権の認可者に応じて優先権が与えられます。

  1. 許可ID
  2. 許可IDを持つグループ
  3. PUBLIC

それでもまだ複数の表スペースが適する場合、最終的な選択はデータベース・マネージャーによって行われます。

これらのルールを念頭におき、この解決策は表スペースへのアクセス権の操作を基にしています。この解決策を機能させるには、表の作成に使用したユーザーIDは特別な権限（DBADMなど）を一切持つことができず、どの表スペースの所有者になることもできませんが、通常、これは問題ではありません。なぜなら、顧客がSYSADMユーザーとして表スペースを作成し、その後非特権ユーザーとして表や他のオブジェクトを作成するケースをよく見かけるからです。

スクリプトを使えばさらに簡単
ソリューションの詳細手順は次のとおりです。

  1. USERSPACE1を含むすべてのユーザー表スペースから、表を作成するユーザーのUSE特権を取り消します。
  2. 表を作成したい表スペースにUSE特権を付与します。
  3. 非特権ユーザーとしてデータベースに接続し、表を作成します。

次のような要件を想定します。

  • application1の表は、APP1_TS表スペースに配置しなければならない。
  • application2の表は、APP2_TS表スペースに配置しなければならない。
  • 表スペース名変更時の配置を容易にするため、表スペース名は完全にパラメーター化しなければならない。

上記要件を満たすには、パラメーター表示要件のためOSスクリプトとDB2スクリプトを混合する必要があります。他の全スクリプト（app1_tables.ddlとapp2_tables.ddl）を呼び出すマスター・スクリプトとして機能するOSスクリプト（createtables.batまたはcreatetable.sh）を利用します。ユーザーuser1は、非特権ユーザーです。

application1のDDLスクリプト（app1_tables.ddl）は、次のとおりです。

　 
CONNECT TO sample USER user1@
CREATE TABLE tab1 (c1 INT)@

application2のDDLスクリプト（app2_tables.ddl）は、次のとおりです。

　 
CONNECT TO sample USER user1@
CREATE TABLE tab2 (c1 INT)@

次のマスター・スクリプトは、SYSADMユーザーから呼び出され、2つのパラメーター（application1の表スペース名とapplication2の表スペース名）を取ります。解説をわかりやすくするために行番号が記載されていますが、これはスクリプトの一部ではありません。

リスト8. マスター・スクリプト（createtab.sh）

　 
1: db2 CONNECT TO sample
2: db2 REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC
3: db2 GRANT USE OF TABLESPACE $1 TO user1
4: db2 -td@ -f app1_tables.ddl
5: db2 CONNECT TO sample
6: db2 REVOKE USE OF TABLESPACE $1 FROM user1
7: db2 GRANT USE OF TABLESPACE $2 TO user1
8: db2 -td@ -f app2_tables.ddl
9: db2 CONNECT TO sample
10: db2 REVOKE USE OF TABLESPACE $2 FROM user1

Windows：　「$」を「%」に置き換えます。

では、マスター・スクリプトを簡単に見ていきましょう。

  • 1行目では、カレント（SYSADM）ユーザーのIDを使用してデータベースに接続します。
  • 2行目では、（全データベースにデフォルトで存在する）PUBLICのUSERSPACE1からUSE特権を取り消します。この時点で、非特権ユーザーが使用できる表スペースはありません。
  • 3行目では、2番目のパラメーターで指定された表スペースのUSE特権を付与します。DB2の表スペース選択ルールから、4行目でapplication1のDDLスクリプト（app1_tables.ddl）実行時にすべての表は適切な表スペースに配置されます。
  • スクリプトが終了すると、6行目でapplication1の表スペースの特権を取り消します。
  • 7行目から10行目では、application2に対して同じ手順を繰り返します。

この例を実演するために、SYSADMユーザーを使用して2つの表スペース（APP1_TSとAPP2_TS）を作成します。

　 
CREATE TABLESPACE APP1_TS
MANAGED BY SYSTEM USING ('app1_ts')

CREATE TABLESPACE APP2_TS
MANAGED BY SYSTEM USING ('app2_ts')

次に、SYSADMとして以下のコマンドを発行します。

　 
createtab.sh app1_Ts app2_ts

application1とapplication2のサブスクリプトが呼び出されると、user1のパスワードを求めるプロンプトが表示されます。スクリプトが終了すると、APP1_TS内にTAB1が、APP2_TS内にTAB2が作成されていることがわかります。

　 
select substr(t.tabname,1,10), substr(ts.tbspace,1,10)
from syscat.tables t, syscat.tablespaces ts
where t.tbspaceid=ts.tbspaceid and
tabname like 'TAB%' and
t.tabschema not like 'SYS%'


1          2
---------- ----------
TAB1       APP1_TS
TAB2       APP2_TS

  2 record(s) selected.

この例を実行後、PUBLICに対してUSERSPACE1のUSE特権を復元してもかまいません。

　 
db2 GRANT USE OF TABLESPACE USERSPACE1 TO PUBLIC


上に戻る


まとめ
この記事では、元の記事「Advanced SQL Procedural Scripting（上級SQLプロシージャー・スクリプト記述）」へのフィードバックおよび顧客からの新しいFAQにお応えして、相互に関連性のないヒントと便利なワザを紹介しました。動的複合SQLに対するSQL PLの新機能を取り上げた後、SQLステートメントの戻りコードの取得、UDFを使用した外部プログラムの呼び出し、および表スペースに依存しないDDLスクリプトの作成に関するヒントなど、スクリプトの機能拡張に使用可能ないくつかの便利な手法を紹介しました。

謝辞
レビューおよびサポートをしていただいたSerge Rielau、Tyronne Mayadunne、Ted Wassermanの各氏に対し、謝意を表します。

著者について
Paul Yipは、分散プラットフォーム用DB2を開発しているIBM トロント研究所に勤務するデータベース・コンサルタントです。主な仕事は、他のデータベースからDB2へのアプリケーションの移行について企業を支援することであり、あちこちへ飛んでトラブルを解決してまわることも多々あります。DB2に関するいくつかの記事やホワイト・ペーパーの著者であり、『DB2 SQL Procedural Language for Linux, UNIX and Windows』（Prentice Hall 2003、ISBN: 0131007726）の共同執筆者でもあります。プロシージャー・スクリプトに関する別の記事の解説を読んでみたいですか？

メール・アドレス：ypaul@ca.ibm.com

特記事項
本記事には、サンプル・コードが含まれています。IBMは、読者（「ライセンス所有者」）に、このサンプル・コードを使用する非排他的でロイヤルティーのないライセンスを付与するものとします。ただし、このサンプル・コードは現状のまま提供され、黙示の商品性の保証、特定目的への適合性の保証、および権利の不侵害の保証を含む、明示または黙示の保証なく提供されるものです。IBMとIBMのライセンス交付者は、ソフトウェアの使用の結果としてライセンス所有者が受けた損害については、一切の責任を負いません。いかなる場合においても、このソフトウェアの使用または使用不能の結果として生ずる逸失売上げ、逸失利益、データの損失、直接的損害、間接的損害、特別の損害、結果的損害、付随的損害、あるいは懲罰的損害賠償について、原因の如何また責任論の種類の如何にかかわらず、たとえこの種の損害発生の可能性があることをIBMがあらかじめ警告されている場合であっても、IBMとIBMのライセンス交付者は一切責任を負わないものとします。

原文はこちら

上に戻る