CREATE OR REPLACE で DB2 for i オブジェクト管理を改善する
SQL ステートメントを簡略化して、セキュリティーとオブジェクトを簡単に管理する
最近のどの IBM i リリースにも、主要な SQL カテゴリーすべてに対する機能改善、機能強化、機能拡張が入っており、IBM i 7.1 も例外ではありません。リリース 7.1 では OR REPLACE 節が IBM から多くの DDL CREATE ステートメントに追加されました。SQL のビュー、トリガー、プロシージャー、グローバル変数、エイリアスで OR REPLACE 構文を使用できます。
OR REPLACE サポートが使用できる以前は、オブジェクトが既に存在していると CREATE ステートメントは失敗していました。多くのユーザーが行った一般的な回避方法は、DDL CREATE ステートメントを対応する drop ステートメントの先頭に付けることでした。あるいは、オブジェクトが既に存在したかどうか判断するロジックを追加していました。オプション指定した OR REPLACE 節が CREATE ステートメントに組み込まれている場合、データベースは同じ名前のオブジェクトの存在を認識し、それを削除してから CREATE 要求を処理します。
演算を変更し CREATE OR REPLACE テクノロジーを使用して達成できる、データ管理で最も目立つ改善点は次のとおりです。
- よりシンプルになった DDL SQL スクリプト
- セキュリティー許可の保持
- えりすぐりの置換で効率的かつ安全な SQL ビュー管理
CREATE OR REPLACE を使用してこうしたメリットを集める方法を探り、ついでに、この新しい節を使用しない方が良いケースについても学習しましょう。
オプションを調査する
SQL は、主に 3 種類のステートメントのカテゴリーまたはタイプに分割されます。
- データ定義言語 (DDL) ― データベース内のオブジェクトを定義または詳細化するステートメント (例: CREATE TABLE、ALTER TABLE、DROP TABLE)
- データ操作言語 (DML) ― データベース内のデータを変更するステートメント (例: INSERT、UPDATE、DELETE)
- 制御ステートメント ― DDL ステートメントと DML ステートメント周辺またはその間を結合するのに必要なステートメント (例: SET、IF-THEN-ELSE、BEGIN-END)
新しい OR REPLACE 節は多くの DDL ステートメントに適用されますが、すべてというわけではありません。SQL のビュー、トリガー、関数、プロシージャー、グローバル変数、シーケンス、およびエイリアスで新しい構文を使用できます。CREATE OR REPLACE VIEW は最もメリットが多いステートメントです。それについては、本記事の終わりまでにはわかるでしょう。デメリットをほとんど考える必要がないため、OR REPLACE 節をすべての対象ステートメントに採用することをお勧めします。
しばらくの間、エイリアス・オブジェクトを考えてみましょう。エイリアスは SQL 表またはビューの置換名に過ぎません。エイリアス・オブジェクトは DDM ファイルを使用して実装されています。その他の IBM i 外部オブジェクトのように、エイリアスには自らの権限があります。ほとんどの場合、これらの権限は DB2 for i に該当しません。エイリアスは、ある名前を別の名前と置き換える手段であるためです。名前の置換が完了すると、エイリアス・ターゲットの権限により、ユーザーの要求が無事完了するかどうか判断します。CREATE OR REPLACE ALIAS を使用するだけで、CREATE 処理は SQL0601 "object already exists (オブジェクト既に存在する)" 障害を回避できます。
その他の 6 つの SQL ステートメントは、SQL 許可が重要な永続オブジェクトに関連しています。シーケンスはデータ域 (*DTAARA) で実装されており、グローバル変数はサービス・プログラム (*SRVPGM) を使用し、SQL トリガーはプログラム (*PGM) を使用し、SQL プロシージャーはプログラムまたはサービス・プログラムの可能性があり、SQL 関数はサービス・プログラムを使用し、最後に SQL ビューは論理ファイル (*FILE) を使用します。
本記事の例では、DB2 の企業サンプル・データベースを使用しています。呼び出し元が提供したスキーマ名でデータベースをセットアップする場合は、以下のプロシージャー・コールを実行します。
図 1 は 7 つの置換可能なオブジェクトを網羅した作業ステートメントを示しています。これらのオブジェクトは、ボールド体を使用して強調しています。
図 1: 企業データベースの Create or replace の例
SQL 許可および OR REPLACE
OR REPLACE 節は、重要な DB2 for i セキュリティー改善を実現しています。それは、REPLACE 全体でデータベースが SQL 許可を管理および保存する、ということです。OR REPLACE 節が出る前は、DBA やアプリケーション・プログラマーは、セキュリティー・カスタマイズを傷つけないようにするため重労働を強いられていました。権限を詳しく調査せず、既存のオブジェクトを単にドロップすれば、一部のユーザーやグループはその許可を失う可能性が高いでしょう。おそらく、オブジェクトをドロップして作成した後に権限を再確立するために、必要な GRANT SQL ステートメントを指定するインストール・スクリプトの形で、より高度なロジックを構築すると思います。しかし、こうしたスクリプトでもまだ制限があります。ハードコーディングされたユーザーや設定のセットに依存しているためです。繰り返しますが、有効なカスタマイズは簡単に失われ、生産性に悪影響を及ぼす可能性があります。
OR REPLACE と ALTER を比べる
現在 OR REPLACE をサポートしている一部の SQL ステートメントには、ALTER SQL ステートメントという形で検討すべき「変 (代) えられる」アプローチがあります。ALTER SEQUENCE、ALTER FUNCTION、ALTER PROCEDURE SQL の各ステートメントにより呼び出し元は、その片割れの CREATE OR REPLACE と同じ多くの作業を実施できます。
ALTER SEQUENCE は、シーケンスを再始動、再展開、または調整できます。CREATE OR REPLACE SEQUENCE でこれらの作業を実施できますが、CREATE OR REPLACE を使用してリセット・シーケンス演算を展開するのは、やり方として無理があります。ALTER FUNCTION (外部) および ALTER PROCEDURE (外部) は定義の特性を調整でき、ALTER FUNCTION (SQL) および ALTER PROCEDURE (SQL) を使用して定義の特性を調整し、ルーチン本体を交換できます。CREATE OR REPLACE でも同じことができます。
ALTER と CREATE OR REPLACE は両方とも、オブジェクトの SQL 許可を保存しています。ALTER には、オブジェクトに対するコメントやラベルも保存するという利点があります。図 2 は同じ作業をする 2 つのステートメントを示しており、ラベルとコメントの異なる動作を監視するのに必要なクエリーも示しています。
図 2: STAFF_SEQ シーケンスの ALTER 対 REPLACE
すべてを考慮すると、オブジェクトが存在するとわかっている場合、ALTER の方が CREATE OR REPLACE より多くのことができるでしょう。
OR REPLACE を使用して SQL ステートメントを生成する
CREATE OR REPLACE について考える場合にグラフィック面があります。System i Navigator には Generate SQL という便利な機能が組み込まれています。Generate SQL は、データベース・オブジェクトの再作成に必要な DDL ステートメントを生成する、Generate Data Definition Language (QSQGNDDL) API へのグラフィカル・フロント・エンドです。アプリケーション・プログラムを作成するよりは、この生成された DDL を数回のクリックで取得する方がずっと簡単です。これをやってみたいと思いませんか？既存のデータ・モデルに基づいてオブジェクトのセットを新しく構築する素晴らしい方法です。この手法はまた、既存のオブジェクトへの戦術的な調整を行う効率的な方法でもあります。
System i Navigator for 7.1 は、Generate SQL ユーザー向けに新しい OR REPLACE オプションを組み込んで機能強化されました。そのオプションを選択すると、返された DDL には、REPLACE をサポートするあらゆる CREATE SQL ステートメントに OR REPLACE 節が追加されます。
図 3 は Generate SQL 機能の起動方法を示しています。このオプションは、例に示すように、複数のオブジェクトを選択した後にオブジェクト・レベル、スキーマ・レベルで使用できます。
図 3: System i Navigator－Schemas フォルダーから Generate SQL の Views を開始する
図 4 に選択できるオプションを示します。明確にするために 3 つのオプションを青色のボックスで囲みました。(1) この例は、既存のオプションを変更できるスクリプトの構築方法を示しています。そのため、SQL privilege statements オプションは選択されていません。CREATE OR REPLACE により許可が手つかずになっています。異なるデータベースで実行するスクリプトが構築されていた場合、OR REPLACE オプションと SQL 特権オプションの両方を使用したいと思うでしょう。(2) 既存のオブジェクトが置換される場合、ラベルとコメントは保存されないため Labels and comments オプションを選択しています。(3) 既存のオブジェクトがある場合、そのオブジェクトを交換したいので Replace clause for objects オプションを選択しています。
図 4: System i Navigator－Generate SQL オプション
図 5 は Generate SQL の出力で、Run SQL Scripts ウィンドウ内でユーザーに表示されています。
図 5: System i Navigator－Generate SQL の出力
従属ビュー －メンテナンス面の大きな悩みを OR REPLACE で解決
ビューは素晴らしいです。このおかげで、1 つまたは複数の表のデータを簡単に結合して変換し、従来の物理テーブルに見えるようユーザーに表示できます。他に、ビューで実現できる便利な作業として、一部のユーザーが見るデータを制限することがあります。従属ビューは、別のビューを参照するという定義 (照会) を持ったビューです。リレーショナル・データベースの作業に優れているユーザーにとり、これらの概念は快適で、データ・センター・アーキテクチャーの中核をなします。
ビューと従属ビューは、ごく普通のものです。これらのビューすべてについて、以下のような重要なメンテナンス作業があります。
- ユーザーとグループが正しい量の許可を得ていることを確認する。
- 業務のニーズに合わせてビューを拡張し、変更する。
- 作業 1 を中断せずに、作業 2 を実現する。
期待しすぎるのはもっともです。ビューをドロップすると、それを参照しているすべてのビューもドロップされます。DROP VIEW ではなく CREATE OR REPLACE VIEW を使用して、その後に CREATE VIEW を続けると、従属ビューと許可は手つかずのままになります。これで、オブジェクト管理が改善されました。
図 6 は、SYSIBM.SQLTABLEPRIVILEGES カタログを活用して CREATE OR REPLACE VIEW を使用するメリットを図示しています。このカタログは、表またはビューの既存の許可を確認する場合に使用できます。これらの例では、サンプル・データベースの 3 つのビューに焦点を当てています。
- VDEPMG1 ― DEPARTMENT 表と EMPLOYEE 表からの情報をまとめるビュー
- VASTRDE1 ― (従属) VDEPMG1 に基づくビュー
- VASTRDE2 ― (従属) VDEPMG1 に基づく別のビュー
図にするために、例では 3 つの GRANT ステートメントで始まり、権限を VASTRDE1 と VASTRDE2 に追加しています。この例で最も重要な点は、1 次ビュー VDEPMG1 を拡張して、PHONENO という新しい列を組み込んでいることです。また、コードのこれらの部分をボールド体にして強調しています。新しい列を追加しても、従属ビューに悪影響はないはずです。CREATE OR REPLACE を使用して仕事を終える限りは。
図 6: DROP VIEW と CREATE VIEW を使用して VDEPMG1 を拡張する
図 7 の上部の結果セットは、CREATE OR REPLACE VIEW を使用して列を VDEPMG1 に追加した後に、SQL 許可が手つかずになっていることを示しています。図 7 の下部の結果セットは、DROP VIEW と CREATE VIEW を使用して 3 つのビューすべてが再構築されていた場合の許可への影響を示しています。
図 7: SQLTablePrivileges の出力－表の許可への影響を対比する
サービス要件
CREATE OR REPLACE . . . SQL サポートは、ベース IBM i 7.1 リリースで実現されました。最新の DB2 for i PTF Group をインストールすることで DB2 for i の機能拡張と修正プログラムで常に最新にしておくことを常にお勧めしています。ページ XX の「もっと詳しく」に記載のリソースで、最新の状態に保つことができます。
構文のシュガー
料理するときに砂糖を加えれば甘くなりますが、新しい OR REPLACE 節も DB2 for i で DDL を操作する場合にも同じことが言えます。現代のデータ・センターは、セキュリティーに関してかつてないほど認識と期待が高まっています。セキュリティー自体、確かに CREATE OR REPLACE を採用する理由として申し分ありません。この節は、既存の許可を手つかずに保つことでセキュリティー改善をするためです。OR REPLACE を使用するもう 1 つ申し分のない動機は、それ自体別のビューで使用されている既存のビューを変更するのは骨が折れるプロセスだ、ということです。ただし、変更に OR REPLACE を使用すると、DB2 for i はあなたに代わって重労働を引き受けます。日々の作業を楽しく、さらに心地よくするため、そして当然、よりセキュアで正確にするためにも、CREATE OR REPLACE が自分の演算のどこにフィットできるか、時間を掛けて理解してください。
