DB2カタログを理解して活用する｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2011.09.12

DB2カタログを理解して活用する

ケント・ミリガン著

データベース・オブジェクトに関する豊富な情報を活用する

DB2 for iでは必要な管理作業のほとんどが自動化されているので、DB2 for iのインストールを構成しているインフラストラクチャの一部に気づいていないIBM iの開発者が多いようです。嬉しいことに、IBM iの開発者はこれらの詳細について心配する必要がありません。しかし悪いことに、プログラマの作業をもっと楽にしてくれる可能性のあるDB2の機能を知らないままになってしまうかもしれません。DB2カタログがまさにこの例です。

DB2カタログはカタログ･ビューとテーブルの集合で構成されており、システム上のすべてのDB2オブジェクトに関する情報が含まれています。DB2オブジェクトはシステム上で作成されて削除されますので、カタログ･オブジェクトは自動的に更新されてデータベースの最新の状態が反映された状態になっています。カタログ･オブジェクトは、DB2がデータベースを管理するのを舞台裏から助けると同時に、データベースの貴重な詳細情報を保存してくれます。

ビューとテーブルは一般的には簡単に問い合わせることができますが、DB2カタログを使用するとプログラマはデータベース･オブジェクトに関する豊富な情報に簡単にアクセスすることができます。この簡単でしかも直接データベースへアクセスする方法はいろいろなやり方で活用することができます。すばやくレポートを作成してデータベース中の大きな列をすべて見つけたり、データベースの命名規則を利用者が守っているかを調べるなどといった広範な分析をしたりすることができます。レポートに加えて、DB2カタログ･オブジェクトを使用したユーティリティを作成して新しいリリースに必要なデータベースの変更を自動化することができます。

本稿では、こうした種類のレポートやユーティリティの例を紹介することで皆さんが自分で作成できるようにし、また、DB2カタログに保存されている情報の種類について理解を深めていただきます。

カタログに保存されているもの

DB2カタログの主な目的とするところはマスター･ファイリング･システムとして機能することです。このシステムにより、DB2はシステム上のすべてのデータベース･オブジェクトを記録しておくことができます。カタログ･オブジェクト内の各オブジェクト用に保管されているデータは、一般的に「メタデータ」と呼ばれています。メタデータとはデータベース･オブジェクトに関するデータのことです。DB2カタログには、従来型のテーブルや物理ファイルからトリガやユーザ定義関数などといった手続きデータベース･オブジェクトにいたるまで、すべての種類のDB2オブジェクトのメタデータが含まれています。IBM i リリース7.1のDB2カタログのデータベース･オブジェクトの一覧を以下に示します。

エイリアス
制約
大域変数
インデックス
パッケージ
関数やプロシージャのパラメータ
スキーマ
シーケンス
ストアード・プロシージャ
テーブル
トリガ
ユーザ定義関数
ユーザ定義型
ビュー
XMLスキーマ・レポジトリ

このリストではSQLの用語を使用していますが、DB2カタログはデータベース・オブジェクトがSQLで作成されたのかDDSで作成されたのかを問わず、すべてのデータベース・オブジェクトを記録するので安心してください。

DB2カタログの構成要素は、図―1に示す通り、多くの異なるレベル、タイプ、クラスのオブジェクトからなっています。カタログ・オブジェクトのデータ・ソースは破線の下にあるオブジェクトの一番下にあります。DB2カタログ・オブジェクトの主要なメタデータのソースは、一般的にはデータベース相互参照ファイルとして知られているQSYSカタログ・テーブルです。データベース相互参照ファイルは、システム上のデータベースが作成、削除、変更されるたびにDB2によって更新されるコアのカタログ・オブジェクトです。これらの相互参照ファイルは任意のシステム・ユーザが修正しないように守られています。これらのファイルのデータを変更することが許されているのはDB2だけです。

DB2カタログのその他のデータ・ソースはDB2システム・レベルのテーブル関数の集合です。テーブル中の現在の行数やクエリー・オプティマイザがインデックスを使用した総合計時間などといった、セキュリティ特権や統計データを提供するカタログ・ビューがこれらの関数を利用します。DB2 for iはセキュリティ特権の詳細や統計データをカタログ中に保存しないので、こうした場合にはテーブル関数を使用する必要があります。これらの属性は個々のオブジェクト自体の中に保存されています。したがって、メタデータの一部を個々のデータベース・オブジェクトからプログラムを使って抽出するにはテーブル関数を使用します。動的に抽出すると、テーブル関数に依存しているカタログ・ビューがパフォーマンス上の問題を引き起こすことがあります。

SYSIBMライブラリやデータベース相互参照ファイル中のカタログ・テーブル関数を直接アクセスすることはお勧めしません。これらのデータ・ソース・オブジェクトが図―1中の破線の下にあるもう1つの理由がこれです。システムの相互参照ファイルにアクセスすることもお勧めしません。というのは、相互参照ファイルはデフォルトではpublic読み取り権限が付与されない状態で出荷されているからです。SYSIBMテーブル関数を直接起動するのもお勧めしません。これはSYSIBMテーブル関数が特定の独立外部記憶プール(IASP: Independent Auxiliary Storage Pool)用に作成されているからです。QSYS2ライブラリ中のカタログ･テーブル関数を直接使用することに関しては問題ありません。

カタログ･データに基づいたユーティリティやレポートの構築の道を探りながら、破線より上にあるカタログ･オブジェクトには自由にアクセスしてかまいません。ビューとテーブルの集合はプログラムからアクセスされることを想定して設計されています。これはpublic読み取り権限がデフォルトで利用可能になっている理由の1つです。public読み取り権限は必要に応じて削除することができますし、個々のシステム･ユーザに付与させることもできます。IBM i Navigatorやデータ･モデリング･ツールなどといったデータベース・ユーティリティを使用したことがあれば、これらのカタログ･オブジェクトをすでに間接的に使用している可能性が高いです。さらに、ODBC、ADO.NET、JDBCなどといったデータベース・ミドルウェアには、カタログ･データに依存したメタデータAPIがあります。

図―1では3つの色を使用して、実際には3つの異なるクラスの消費可能なカタログ･オブジェクトがあるという事実を表しています。3つのすべてのクラスのカタログ･オブジェクトには本質的には同じデータが含まれていますが、異なる列名と異なるメタデータの表現でメタデータを表しています。3つのカタログ･クラスには重複がありますが、システム上のディスク空間を無駄にしているわけではありません。というのは、これらのオブジェクトのほとんどはデータを一切含んでいないビューだからです。これらの3つのクラスのカタログは以下の通りです。

DB2 for iのカタログ･ビューとテーブル:

これらのカタログ･ビューやテーブルはIBM i に固有の定義を使用しています。固有な定義を使用してはいますが、これらのオブジェクトはANS標準やISO標準をモデルとしています。これらのビューやテーブルはQSYS2ライブラリ中にあり、すべてオブジェクト名の先頭に「SYS」がついています。また、これらのオブジェクト定義はDB2 for i のすべてのリリースで互換性があります。

ODBCとJDBCのカタログ･ビュー:

このカタログ･ビューは、ODBCのSQLColumns関数などといったODBCとJDBCのメタデータAPIの要求を満たすように特別に設計されています。このビューはDB2 for Linux、Unix、Windows (LUW)などの製品が提供するビューと互換性があります。これらのビュー自体はSYSIBMライブラリ内にあり、オブジェクト名の先頭に「SQL」がついていることで識別できます。これらのビュー定義はリリースが変わると変更になる場合がありますが、これはODBCやJDBCのメタデータAPIへの将来の変更に対応するためのものです。

ANSとISOのカタログ･ビュー:

驚くことではないですが、これらのビューの定義はANSとISOのSQL標準に準拠しています。これらのビュー定義はSQL標準が時とともに変わっていくにつれて変更されていきます。このカタログ･クラスは実際には2つのバージョンがあり、異なるライブラリ中にあります。

ANSおよびISOのSQL標準に完全に準拠しているビューはINFORMATION_SCHEMAという名前のスキーマ中に存在します。DB2 for i上では、このスキーマ名はQSYS2と同義語です。ユーザがこれらのカタログ･ビューのいずれかにアクセスするときは、アクセスが許可されているDB2オブジェクト用の行だけを返します。
このカタログの2つ目のバージョンはSYSIBMライブラリ中にあります。これらのビュー定義はまったく同じものなのですが、このビューはユーザがどのオブジェクトへの参照を許可されているかにかかわらずすべてのDB2オブジェクトに関するデータを返します。

図―2では、カタログの各クラス内にある個々のオブジェクトを一覧にすることで別のレベルの色分けをしています。このDB2オブジェクト･カタログの一覧はIBM i リリース7.1現在のものです。

カタログ･データの使用

DB2カタログを構成しているオブジェクトとデータの型を理解いただけたと思いますので、日常の作業を簡素化するために使用する方法について説明しましょう。DB2カタログ･データの最も一般的な使用方法の1つが、クイック参照を記述してデータベース中の特定のDB2タイプや属性の使用を検証したり、データベース･オブジェクトがどのように定義されているかを確認したりすることです。たとえば、データベース用のディスク記憶要件に懸念があって、システム上のすべての「long」列を探さなければならないことがあります。5000バイト以上のすべての列を識別するのは以下のクエリをお気に入りのSQLインタフェースから実行するだけなのです。

SELECT
　 column_name, table_name,
　 length, data_type
FROM qsys2.syscolumns
WHERE length > 5000
　ORDER BY length DESC

このクエリ文をIBM i Navigator Run SQLインタフェースから実行すると、図―3に示す出力結果が得られます。

今までに、DSPFFD (Display File Field Description)コマンドが作成した出力ファイルに対してレポートを作成し、この種のデータベース定義分析をされたことがある開発者もいるでしょう。出力ファイルを使用するというこのソリューションはうまくいくことはうまくいくのですが、その一方で出力ファイルを使用するのは、DB2カタログ･データに対してクエリを発行することと比べるといくつか不利な点があることを理解してください。まず、出力ファイルにシステム上のすべてのデータベース･オブジェクトのメタデータを入れるのは時間がかかり、必要となるディスク記憶域空間はもとより、CPUと入出力資源を大量に消費します。次に、出力ファイルにデータがいったん入ると、システム上でデータベース･オブジェクトが作成、削除、更新されるたびにこのメタデータを更新するのは皆さんの責任になります。本質的には、出力ファイル中のデータはほとんど常に古い状態にあることになります。このデータベース･メタデータを動的に取り出すシステムAPIがありますが、上述のSELECT文を使用する方がシステムAPIを起動するのに必要なコードを書くよりずっと簡単であるということに、すべての開発者がうなずくと思います。

メタデータの分析にSQLが利用できることで、高度なレポートを簡単に作成してデータベースに関してより深い理解を得ることができます。図―4には各列の長さをその最大長、最小長、平均長と比較する、より複雑なレポートの例を示してあります。length_calc共通テーブル表現は、SYSCOLUMNSカタログ･ビュー中にある列メタデータを要約することで、テーブルのさまざまな列の長さの総計を計算します。そしてこの総計の計算はSYSCATALOG中の個々の列データと一緒になって、図―5に示す詳細な分析レポートを生成します。

ユーティリティの例

DB2カタログ・オブジェクトに保存されているメタデータの分析が、SQLベースのレポートでいかに簡単に行えるかがおわかりいただけたと思いますので、DB2カタログ･オブジェクトを活用してデータベース管理を簡素化しているユーティリティ･プログラムの例を見てみましょう。

データベースにアクセスするのに開発者がSQLを使用し始めると、多くのプログラマが突き当たる壁が複数メンバー物理ファイル中にあるデータへのアクセスです。デフォルトでは、複数メンバー物理ファイルだけを参照するSQL文が最初のメンバー中にあるデータを処理することができます。幸いなことに、IBMはCREATE ALIAS文に対して特殊な構文を追加することでこの問題を解決しており、これにより複数メンバー物理ファイルの2番目のメンバーに保存されているデータへSQLでアクセスすることが可能になります。以下に示すのは、複数メンバー・ファイルの2番目のメンバーの内容に対する問い合わせをSELECT文でできるようにしたCREATE ALIAS文の例です。

　　　CREATE ALIAS membr2_alias
　　　　FOR mylib/myfile(member2)

　　　SELECT fld1, fld3 FROM member_alias WHERE fld2>0

IBM iの開発者の多くはエイリアス･ベースのアプローチを上手に使用して複数メンバー・ファイルへのSQLアクセスを可能にしています。このアプローチで問題となるのは、SQLでアクセスする必要のあるすべてのメンバー用にSQLエイリアスを作成しなければならないことです。エイリアスを作成するのは、データベース中の複数メンバー･ファイルに大量のメンバーが入っていると、退屈で時間のかかる作業となりえます。ありがたいことに、DB2カタログに保存されているデータを利用するユーティリティを簡単に作成してこの処理を自動化することができます。DB2カタログにはすべてのデータベース･オブジェクトの名前が含まれていますから、取り出したオブジェクト名をSQL文のテキストでラップするユーティリティを作成して、ある特定の作業に対してSQL文を生成して実行することが簡単に行えます。図―6に示したものと似たようなユーティリティを作成してDB2カタログ･データからCLコマンド文字列を生成したことが何度かあります。

ここで例として挙げたカタログ･ユーティリティはQSYS2中のSYSPARTITIONSTATカタログ･ビューから戻されるデータに依存しています。このカタログ･ビューはSQLパーティション･テーブルのあるすべてのパーティションと複数メンバー物理ファイルのすべてのメンバーに対して列を返します。通常のSQLテーブルと単一メンバー物理ファイルはSYSPARTITIONSTATビューによる1つの列で表現されます。

1つのメンバーに対して1つの列を返すカタログ･ビューに基づいて、図―6に示すユーティリティが入力として与えられた複数メンバー・ファイルの各メンバーに対するすべての列を繰り返し処理することで、CREATE ALIAS文を生成して実行しているのがおわかりいただけるでしょう。この例では、ユーティリティ自体がSQLのストアード･プロシージャとして実装されています。私は通常、カタログ・ユーティリティを作成するときにSQLストアード・プロシージャを使用します。とても簡単だからなのですが、SQLをサポートしている言語であればどの言語を使用してもかまいません。

このユーティリティの一番大事な部分は、指定された複数メンバー・ファイルのメンバー・データを取り出すSELECT文です。このSELECT文がメンバー情報を単に戻すのではなくて、CREATE ALIAS文用のSQLキーワードをメンバー情報のまわりに結合するという点に注意してください。このようにキーワードを結合(ラッピング)することで、SELECT文は生成されたCREATE ALIAS文を戻すことができるようになります。

SQLのTrim関数がシステム・スキーマ、テーブル、そしてSELECT文が返すメンバー名にラップされ、長さ10文字未満のシステム名に付いてくる空白文字を削除することができます。エイリアス名はメンバー名と同じにしなければなりませんが、このユーティリティが「ALSxxxx_」という文字列をエイリアス名の先頭に付けてくれます。この先頭の数字部分はカタログ・ビューのpartition_number列から生成されます。partition_numberには複数メンバー・ファイル内の通常のメンバー位置(メンバー#1、メンバー#2など)を表す整数があります。生成されたCREATE ALIAS文字列中にpartition_numberの値を含めるには、Digits関数を使用してpartition_numberの数値を文字列に変換しなければなりません。生成されたCREATE ALIAS文をSELECT文が返してきたら、このプロシージャはPREPARE文とEXECUTE文を使用して生成されたSQL文を動的に実行します。次に、プロシージャ中のFORループがSELECT文をファイル中の次のメンバー用の列に進めて、入力された複数メンバー・ファイルのすべてのメンバーが処理されるまで繰り返します。

ストアード・プロシージャが作成されたら、好きなSQLインタフェースから次のような簡単なCALL文でこのユーティリティを起動することができます。

　CALL create_alias('MYFILE','MYLIB')

指定された入力ファイルに4つのメンバーが含まれているとすると、このユーティリティは図―7に示すようなSQL CREATE ALIAS文を生成して実行します。

このプロシージャにCREATE ALIAS文を実行させる代わりに、ストアード・プロシージャに生成された文を返すようにさせることも簡単にできます。このバージョンのストアード・プロシージャをIBM i NavigatorのRun SQL Scriptsインタフェースから起動するときは、生成されたSQL文を結果セット・ウィンドウからコピーして任意のSQL文で実行することができます。この方法を使用するとSQL文をソース・メンバーにコピーするというオプションも利用することができ、そうするとそのSQL文を変更管理プロセスやツールで管理することができます。実行したSQL文のコピーを取得するのが重要な場合は、もう1つのオプションとしてユーティリティは変更せずにそのままにして、IBM i Navigator Generate SQLインタフェースを使用して、ユーティリティがエイリアスを生成した後にCREATE ALIASを生成するという方法があります。

IBM i Navigator Generate SQLインタフェース自体はシステムが提供するユーティリティで、カタログ・データを利用します。それに基づいて、DB2カタログ・データを使用する独自のユーティリティを作成してSQLソースを生成するのは、わざわざ最初から作り直すようなものです。データベース・オブジェクト用のSQLソースを生成するプログラミング・インタフェースをお探しなのであれば、IBM i Navigator General SQLサポートを活用したQSQGNDDLシステムAPIと同じAPIをユーティリティで使用することができます。

まとめ

DB2カタログ･オブジェクトに対してレポートやユーティリティを書き始めたら、QSYS2やSYSIBM中のカタログ・オブジェクトを直接参照するSQLビューを作成する場合は、作成したSQLビューがリリースのアップグレードやDatabase Group PTFのインストールの際にシステムによって削除される可能性があるという点に注意してください。IBM iの新しいリリースをインストールするとDB2カタログ・オブジェクトを参照しているSQLビューは必ずすべて削除されます。新しいDatabase Group PTFを読み込んでもビューが削除される場合もありますし、削除されない場合もあります。削除されるかどうかはPTFが解決しようとしている問題によって異なります。

本稿をお読みいただいたことでDB2カタログに保存されているメタデータをどのように使用するのかについて見識を高めていただき、データベースをよりよく理解いただいてしかもデータベースの保守と管理の作業の一部を自動化していただければ幸いです。本稿でご紹介したレポートやユーティリティは、DB2カタログ・データを使用して作業を簡素化するたくさんの方法のうちのほんの一部に過ぎません。前述のURLからSQLリファレンス中のドキュメントを参照いただき、日常の作業を簡素化するカタログ・オブジェクトをぜひ見つけてみてください。