DB2 for i 7.1で大域的に考え、ローカルで実行する｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2011.10.24

DB2 for i 7.1で大域的に考え、ローカルで実行する

スコット･フォースティー著

大域変数を使用してSQLアプリケーションの記述を簡素化する

インターネットが成熟したことで物事を地球規模で考えることがかなり容易になりました。インターネットは世界を今まで以上に相互接続しやすくしてくれ、より多くの情報資源が利用可能になって、より多くの情報に基づいた意思決定を支援し、仕事においても私生活においても今まで以上の効率と生産性と価値を手に入れる可能性をもたらしてくれました。

情報は変革を可能にします。今日の若者たちの中でデューイ十進分類法の果たす役割の重要性を理解している人が何人いるでしょうか。家電製品に障害が発生したとき、印刷されたマニュアルを読むでしょうか。

情報量が膨張したこの新しい世界がiの世界をも変革したのです。DB2 for IBM i on 7.1は、SQLの大域変数を追加することでアプリケーション開発者がより効率的にそして生産性高く作業ができるように、多くの新しい資源を提供しています。大域変数はデータベース中で定義され、さまざまなジョブで使用される際にローカルの自律性を持ちますので、新たな有益なテクニックをiにもたらします。本稿では、大域変数を理解していただき、それをどのように定義して使用するのか、そのもたらす価値は何かについて説明します。

自問してみてください

ジョブ中でSQLのプロシージャ、関数、トリガを初めて呼び出したのを知る一番簡単な方法をお探しですか。アプリケーションや環境の異なる部分間では簡単には共有できないデータや知識に基づいて、既存のアプリケーションのロジックを改良する必要が生じたことはありませんか。既存のプロシージャの入力パラメータが増え続けていて、そのパラメータのほとんどがアプリケーションの次の層にパラメータを渡すことができるようにするためだけに存在している、ということはありませんか。

上記の質問への答えが1つでも「はい」であれば本稿を読み進んでSQLのプロシージャ、関数、トリガの中で大域変数をうまく利用する方法を理解してください。

本稿中での例について

私自身もプログラマであるので、学習用教材においても実際に動作する例を見たいと思っています。本稿に記載されている例はDB2のサンプル・コーポレート・データベースに基づいています。このプロシージャ呼び出しを実行して、呼び出し側提供の次のスキーマ名でデータベースを設定してください。

CALL QSYS.CREATE_SQL_SAMPLE('GVARSAMP');

大域変数

大域変数は追加のデータ記述言語(DDL: Data Definition Language)オブジェクトです。使用方法の観点から言えば、大域変数は単一行単一列のSQLテーブルに大変似ています。大域変数を一度定義しておけばSQLプログラマはその値を変更、参照することができます。

大域変数は永続的なSQLオブジェクトです。大域変数はホスト変数と同様に1つの値を持ちます。大域変数はSQLアプリケーション内でアプリケーションのホスト変数と同様の方法で使用(参照)できます。また大域変数はテーブルの列名の代わりに使用することもできます。

基本的な使用法を理解するためにはいくつかの簡単な例を紹介するのがわかりやすいでしょう。

例1:　ジョブ内で初めて使用する場合

以下に示す例1は、SQLアプリケーションが特に大域変数に関連付けられた条件付きロジックを簡単に導入する方法を示しています。このIF文のチェックが各ジョブに対して1回ずつTHENブロックを実行し、設定または1回だけ実行するロジックを導入する有効な方法を提供します。

CREATE VARIABLE QGPL.FirstUseInJob CHAR(1) DEFAULT 'Y';

IF (FirstUseInJob = 'Y' ) THEN
　SET FirstUseInJob = 'N' ;
　/* do something once in a job here */
END IF;

例2: 導出結果の簡単な導入

以下に示す例2は変数を使用して変更のなかった値を導出する方法です。この変数はデフォルト値用の式を持っており、アプリケーションが結果を取り出したり構築したりする際に文を準備、束縛、実行しなくても済むようにしています。

SET PATH "GVARSAMP","SYSIBM" ;
SET SCHEMA "GVARSAMP" ;

CREATE VARIABLE MostRecentlyHiredEmp VARCHAR(100) DEFAULT
　(SELECT FIRSTNME CONCAT ' ' CONCAT MIDINIT CONCAT ' ' CONCAT LASTNAME FROM EMPLOYEE ORDER BY HIREDATE DESC FETCH FIRST 1 ROWS ONLY);

values(MostRecentlyHiredEmp);

これにより「EVA D PULASKI」が返されます。

例3:　大域変数に関連付けられた条件付きロジック

この例は図―1に示した通り、変数を使用して特殊な場合または条件付ロジックを導入する方法を示しています。この例ではSQLのトリガの場合を示していますが、この考え方はどのSQLコードのボディでも使用できます。この例でのトリガは、SALESテーブルに列が挿入されてSales Regionが大域変数の値に一致したときに追加のアクションを取るようになっています。大域変数をこの例のように使用するのはIF文の中でリテラル値をハードコードするより良い方法です。というのは、トリガを再構築せずに大域変数を変更することができるからです。

基礎の構築

他の永続的SQLオブジェクトと同様にSQL文を使用して大域変数を管理することができます。大域変数SQLの例を以下に示します。

CREATE VARIABLE:
変数を作成する。新しいQSYS2/SYSVARIABLESカタログに列を挿入する。*SRVPGMオブジェクトを作成する。
DROP VARIABLE:
変数を削除する。QSYS2/SYSVARIABLESカタログから列を削除する。*SRVPGMオブジェクトを破壊する。
COMMENT ON VARIABLE:
変数の記述または意図的目的を追加または置換する。
GRANT or REVOKE:
大域変数を使用する権限を追加または削除する。変数が大域的に定義されていてもその使用を制限することができる。
LABEL ON VARIABLE:
変数用の列見出しを追加または置換する。
SET VARIABLE:
1つ以上の大域変数の値を変更する。
その他の文:
列名が使用できるところのほとんどで大域変数を使用することができる。

大域変数の重要な点の1つが、各接続に対してその大域変数の固有の「コピー」があるということです。大域変数は接続ごとにスコープが決められています。「接続」とはアクティベーション・グループとローカルまたはリモートのアプリケーション・サーバーとの間の関連付けのことを言います。また接続はセッションまたはSQLセッションとも呼ばれます。

セッションにおける変数のスコープの考え方は、変数を使用することの重要な利点を強調しています。大域変数にもプライバシーは組み込まれています。それぞれの接続には固有の変数のインスタンスがあります。ですから大域変数に機密情報を入れることも可能です。設計上は、他のジョブがこの変数の値を参照することはできません。ある接続中で変数を使い終わった場合は、以後その変数を使用しなければよいのです。変数の値を見えなくしたいあるいは消去したい場合は、SET文を使用して変数をそのデフォルト値に戻すことができます。

コミットメント制御を使用しているときは、変数に対してなされた変更はトランザクションに影響を与えません。コミット操作やロールバック操作は変数の値に何の影響も与えません。

インスタンス化のパワー

インスタンス化とは、変数を最初に参照(あるいは更新)する前にセッション内でその変数を確立するために必要なDB2　for iの中での作業のことです。変数がSET文の代入先になっている場合は、データベースは必要な内部コンストラクトを構築して値を割り当てるだけ良いのです。

インスタンス化の素晴らしい点は変数を最初に使用するときがその変数への参照であり、その変数が作成された時の値がデフォルト値であるということです。変数は以下の4つの異なるタイプのデフォルト値で作成することができます。

定数
CREATE VARIABLE GVARSAMP.RegionVariable VARCHAR(15)
DEFAULT 'Ontario'
特殊なレジスタ
CREATE VARIABLE GVARSAMP.DateVariable DATE
DEFAULT CURRENT DATE
大域変数(異なる変数への参照)
CREATE VARIABLE GVARSAMP.WorkVariable VARCHAR(15)
DEFAULT GVARSAMP.RegionVariable
式
CREATE VARIABLE DayofMonth VARCHAR(100)
DEFAULT ('Today is day ' concat rtrim(char(dayofmonth(current date))) concat ' of the month')

高可用性と障害回復

大域変数は高可用性や保存/回復用のソリューションにも登場します。大域変数が作成される時に、その大域変数のシステム名を使用したサービス･プログラム・オブジェクト(*SRVPGM)が作成されます。その大域変数のデフォルト値に式が含まれている場合は、このサービス・プログラム・オブジェクトには変数とそのアクセス計画を再作成するのに十分な情報が含まれています。

高可用性ソリューションはこのサービス･プログラムの作成を認識し、ターゲット(バックアップ用)マシンへの複製用のオブジェクトを計画します。サービス･プログラムが回復された時は、QSYS2/SYSVARIABLESカタログが更新されてDB2 for i内で使用できるように変数が確立されます。

同様に、システム保存や特定のライブラリを回復するときは回復処理がSYSVARIABLESとSYSVARIABLEDEP(変数のデフォルト値の式が参照するオブジェクト)を更新します。

前述のインスタンス化セクションにおける4つの変数例を見ると、変数GVARSAMP.EmployeeVariableだけが単一行の形式のエントリをSYSVARIABLEDEPカタログに持ち、変数EmployeeVariableがテーブルGVARSAMP.Employeeに依存していることを示しています。DB2 for iは、DROP文を処理する際にRESTRICT処理またはCASCADE処理とともにこの依存関係の詳細を利用します。

例4: ビューの定義中で大域変数を使用する

大域変数はホスト変数と違ってビューの定義中でも使用できます。このことがより高度で柔軟なビューを定義する際にもたらす可能性を考えてみましょう。図―2に示した例でこの考え方を説明しています。最初のステップは永続的変数、この例の場合RegionVariable、を定数のデフォルト値「Ontario」で作成することです。この変数が一旦作成されたらWHERE節を付けてSalesテーブルのビューを作成します。このビューが上記の大域変数を参照します。ビューが参照されたときの大域変数の値によって、ターゲットとなるテーブルからどの行がクエリから返されるのかが決まります。DDL文が完了すると、ビューの結果はRegionVariableの値に依存するということをこの例は示しています。各接続に固有の変数のコピーがあるということを思い出してください。アプリケーション側でやらなければならないのはその接続にとって意味のある値を設定するか、熟考されたデフォルト値に依存するか、のいずれかのみです。

例5: 大域変数のセキュリティを設定する

大域変数は永続的なオブジェクトです。SQLテーブルなどの他の永続的オブジェクトと同様に、SQL権限制御と非SQL権限制御があります。新しい変数を導入するときは、その変数にアクセスできる必要のあるユーザーは誰か、変数の値を変更することができる権限を制限する必要があるか否かを検討してください。

下記に示す例5はRegionVariableの権限スキーマを説明しています。

変数gvarsamp.RegionVariableのすべての権限をPUBLICからREVOKEする
変数gvarsamp.RegionVariableのAppTeamへのREAD権限をGRANTする
変数gvarsamp.RegionVariableのSuperUser1へのWRITE権限をGRANTする

最初のステップは変数に対するすべての権限をPUBLIC(全ユーザー)から無効にすることです。新規に作成された変数はデフォルトではPUBLICが変数を使用できないようになっていますので、このステップは厳密には必須ではありません。2番目のステップはAppTeamグループ・プロファイルに読み取り権限を付与することです。AppTeamプロファイルを(直接または借用権限経由で)使用して実行されるプログラムはすべてこの変数の値にアクセスすることができます。最後のステップは変数の値を変更できる権限を与えるユーザーを設定することです。

System i Navigator

System i Navigatorのバージョン7.1には以下の大域変数の機能があります。

大域変数用のSchemasフォルダ・ビュー(後述)
構文チェッカーつきcreate variableダイアログ
Run SQLスクリプト大域変数値ビューワ
SQLパフォーマンス･モニタ分析

図―3に示す通り、バージョン7.1でSchemasフォルダが強化され、スキーマやライブラリ内で変数を参照、理解、管理することが容易になりました。

生成と再配布

図―4と図―5には変数を再作成するのに使用するSQLを生成する方法を示しています。これらのスクリプトは容易に生成でき、便利なバックアップ機能や変更制御への素早い入力、同じあるいは似たような変数を他の場所に配布するための簡単な方法を提供します。生成されたSQLには変数の権限に対して行われた変更が含まれている点に注意してください。生成された出力に権限を含めることは、DDLオブジェクト用にSQLを生成する際に取り得る多数の選択肢のうちの1つです。

変数構築環境の再検討

Explain SQLオプションを使用して、変数を構築するときに使用できる構築オプションを理解することができます。図―6は変数RegionVariableの出力例を示しています。ここでは、デフォルト値やcreate variable文など、Explain出力の最も興味深い点をいくつか強調しました。

大域変数の新規作成

図―7と図―8はSystem i Navigatorのcreate variableダイアログを使用して新しい変数を作成する方法を説明しています。この機能はSQL文を構築する際に助けが欲しい時に便利です。このダイアログにはデフォルト値の式の構文の妥当性をチェックする便利な機能と、式が返す値をプレビューするオプションが含まれています。ここで示した例は変数ApplicationHiddenParameterForPayrollを作成しています。この変数にはデフォルトの式があり、その式はトップ営業マンの身元を決定するというものです。この変数の値は営業マンの姓と営業担当地区を結合したものになっています。ダイアログを完了すると、OKを選択してDB2 for iに変数を作成させるか、Show SQLオプションを選択してCREATE VARIABLE SQL文を表示させるかのいずれかを選択できます。

注: デフォルト値の構文は、SQLリファレンスのCREATE VARIABLE SQL文に記載されているものと正確に一致していなければなりません。デフォルト値に式を使用するときは、その式を括弧で囲まなければなりません。

Run SQLスクリプトとGlobal Variables

図―9と図―10はアプリケーション開発者にとって便利な機能を示しています。Run SQLスクリプトはバージョン7.1で機能強化され、Global Variablesタブが含まれるようになりました。この新しいタブにはRefreshボタンがあり、ユーザーは現在のセッションの中でインスタンス化されたすべての大域変数とそれらのすべての現在値を確認することができます。

図―9は4つの大域変数へのクエリに使用する簡単なVALUES文です。図―10はRefreshボタンが押された後の変数の値を示しています。この機能をなぜ使いたいと思うのでしょうか。この機能は使用中のすべての変数を表示してくれるので、変数がインスタンスされたか否かを推測する必要がないのです。Run SQLスクリプトからアプリケーションをデバッグしている場合は、現在値を理解しておくことが重要です。またこのダイアログには変数の定義、詳細説明、コメントへ直接移動するオプションもあります。

データベース・モニタ

SQLパフォーマンス・モニタ(別名、データベース・モニタ)には各文における大域変数の使用に関する情報が含まれています。この情報は新しいデータベース・モニタ・レコード識別子(3012)に取り込まれます。このレコードがモニタの出力に表示されたときは、1つのレコードが変数に関連付けられたSQL文内で使用されている変数をマッピングし、また、そのSQL文が実行されたときにその変数の値を提供します。

前述の例4をSQLパフォーマンス・モニタという観点でもう一度見てみましょう。最初のselect文でモニタ・データを取得する場合は、この文が今回の接続内の大域変数に対する最初の参照であることがわかっています。変数はDB2 for iがデフォルト値の「Ontario」を提供するように構成されています。図―11に示すように、モニタの出力の分析中に「Work with SQL Statement and Variables」オプションを使用する場合は、クエリ文の前にその変数に対するSET文が来ているのがわかります。この高度な分析機能はSystem i Navigator固有のものです。

実行されたSQL文は以下の通りです。

select distinct(SALES_PERSON) from GVARSAMP.view_on_sales;

「Work with SQL Statement and Variables」が返したSQL文は次の通りです。

SET GVARSAMP.REGIONVARIABLE = 'Ontario' ;
select distinct(SALES_PERSON) from GVARSAMP.view_on_sales;

大域変数の話となればVisual Explainユーザーのことを忘れたわけではありません。クエリのFinal形式用のVisual Explainダイアログには、図―12に示す通り、そのクエリが使用する大域変数の名前と値が含まれています。

まとめ

2011年、IBMでは創立100周年を祝っています。本稿を書きながらトーマス・J・ワトソン・シニアが言った「世界中のあらゆる問題は、世界中の人がそれについて考えようとするだけで解決する」という言葉を思い出しました。大域変数サポートの追加などといったDB2 for i 7.1の新しい機能について「考えて」見てください。

大域変数は、アプリケーションに対して必要最小限の変更をするだけでビジネス・ロジックとアプリケーションの動作を適合させる方法を提供するので、すばやく元が取れるものです。大域変数が多くのSQLアプリケーションにおいて必勝ソリューションとなり、皆さんにとって価値のあるものであると私たちは「考え」ています。