【第10回】ステップアップ – データベース：DDL

 はじめに

IBM i 7.3 の発表からすでに一年以上が経過しました。この新しいバージョンの機能は、私にとってとても衝撃でした。今まで地道に進んできたオープン化が、このバージョンによって一気に明らかになってきたと感じたのです。そう思う理由の一つは、すでに紹介した FFRPG や、オープンソース開発環境の Orion / git のサポートにより、他のプラットフォームの技術者を IBM i に取り込むという「戦略」が垣間見えたからです。 そしてもう一つはテンポラル・テーブル機能と監査列のサポートでした。この2つの機能は DB2 データベースの機能拡張なのですが、DDS とコマンドだけでは実現できない機能です。これまでもそういった機能（フィールド単位の機密保護など）はありましたが、あまり日の目を見ることはなかった気がします。しかし、この2つの機能は IBM i 7.3 ユーザーであればすぐにでも使ってもらいたい機能です。 テンポラル・テーブル機能と監査列を使用するには SQL インターフェースが必要です。そこで今回は、DB2 に対する SQL インターフェースを中心に、どのようなことができるのかをまとめて解説します。キーワードは「ステップアップ」です。

SQL について

SQL（Structured Query Language：構造化照会言語）は、皆さんよくご存知のように、リレーショナル・データベースの操作を行うための言語です。業界標準として広く普及しており、SQL に関する書籍や Web での情報にも事欠きません。前回の記事でも書きましたが、IBM i も SQL インターフェースを持っていますので、IBM i に詳しくない技術者でもデータベースを操作することは可能です。実際、クライアント・サーバー・システムのデータベース・サーバーとして IBM i を使用し、xDBC を介して他言語から IBM i を利用している例はとてもたくさんあります。 SQL 命令は大きく以下の種類に分けることができます。

• DDL（Data Definition Language）
  • テーブルやビューの作成や変更および削除などの定義に関する命令
• DML（Data Manipulation Language）
  • データの操作に関する命令
• DCL（Data Control Language）
  • アクセス権限などを操作する命令

IBM i では業界標準への準拠をマニュアルでも記載しており、「ISO（国際標準化機構）9075:2011」の標準オプションの使用が推奨されています。 IBM i で SQL を利用するにはいくつかのインターフェースがあります。5250 からは、対話形式で SQL を実行可能な画面を提供してくれる STRSQL コマンドと、実行する SQL 文を事前にソース・メンバーに記述しておき、それを実行する RUNSQLSTM コマンド、単一の SQL 文を実行する RUNSQL コマンドがあります。 また IBM i Access Client Solutions が提供する Run SQL Scripts 機能を使用して、Windows や  Mac などのクライアントから SQL を実行することも可能です。 RUN SQL Scripts では SQL の例文を選択して画面に貼り付け、それを修正して実行することも可能です。実行した SQL 文をテキスト・ファイルとして保存しておくことも可能なので、このインターフェースはぜひ使っていただきたいと思います。

DDS の代替としてのインターフェース

それでは SQL の DDL に焦点をあてて解説したいと思います。 SQL でテーブルやビューなどを作成、変更および削除するには以下の命令を使用します。

• CREATE 文（テーブル、ビュー、インデックスの作成）
• ALTER 文（変更）
• DROP 文（削除）

前回の記事で、IBM i のデータベースの多くは DDS を利用して作成されていると書きました。DDS はレコードの形を定義する文字通りの「仕様書」であり、実際のデータベース・ファイルを作成するのは CRTPF / CRTLF コマンドです。仕様書をソース・ファイルに登録し、コマンドを実行するという 2 ステップが必要になるわけですね。 これに対し、SQL には DDS で記述するような「仕様書」を別に作成するという概念はありません。ひとつ例を挙げてみましょう。以下は簡単なテーブルを作成する CREATE 文です。 上記例でお分かりのように、SQL の場合は作成する CREATE 文にそのテーブルに含めるフィールドの名前やデータタイプ、あるいはレコード様式名などを直接記述しています。この文を実行することによりテーブルが作成されるのです。STRSEU + CRTPF / CRTLF が一つの CREATE 文で実行できるわけですね。 それではファイルの変更についてみていきましょう。物理ファイルを変更するコマンドに CHGPF コマンドがあります。このコマンドは大きく２つの機能があります。

• オブジェクトの属性に関する変更
  • 初期レコード数
  • アクセス・パスの保守の種類
  • ファイルおよびレコード待機時間
  • 削除済みレコードの再利用など
• データベースの構造の変更

データベースの構造の変更には、やはり DDS が必要になります。事前に変更後のレコードの形を DDS で記述してその後 CHGPF を実行します。 SQL ではCREATE 文同様に追加、変更および削除するカラム（フィールド）を ALTER 文に直接記述します。変更後の仕様書を事前に登録する必要はありません。 他のプラットフォームで RDBMS を使用してきたユーザーにとって、IBM i ネイティブのインターフェースは色々な点で戸惑いがあると思います。DDS の登録とコマンドの実行という、IBM i 特有の作法に慣れるまでにはかなりの時間がかかることでしょう。しかし、SQL インターフェースでも作成および変更等は同様に行うことができますので、DDS を知らないデータベース技術者でも、IBM i の開発プロジェクトにすんなり参加することができると思います。

既存のデータベース資産との共存

データベース・ファイル（物理ファイル）を作成するインターフェースは、DDS + CRTPF と SQL の 2 通りであることはすでに述べた通りですが、作成されるオブジェクトは共に *FILE であり、属性は PF-DTA です。DSPFD で記述を参照すればどちらのインターフェースで作成されたか判別はもちろん可能ですが、2 つの違いはほとんどありません。実際、DDS から作成された物理ファイルに対して SQL 文を実行することは可能ですし、SQL で作成されたテーブルを DFU などのツールで操作することもできるのです。 これは IBM i ユーザーが作成するプログラムも同様です。RPG プログラムの F 仕様書に記述するファイルは SQL で作成したテーブルでも構いません。通常の物理ファイルと同様に READ / CHAIN / UPDATE / DELETE 命令を使用してレコード・レベル・アクセスを行うことができます。このことから分かるように、ファイルがどちらのインターフェースで作成されたかは関係なく、同じデータベースとしてどのインターフェースからも処理することが可能なのです。 また、この後で紹介するテンポラル表などは SQL インターフェースでのみ設定できますが、対象となるファイルは DDS + CRTPF で作成されたファイルでも構いません。通常新しい機能を実装する場合は、過去に作成されたものはその機能の対象から除外されることが多いのですが、IBM i は過去作成されたファイルも 7.3 のテンポラル表に変更できるのです。IBM i の過去の資産の継承の DNA は、こういったところでもしっかり守られているのですね。 前回の記事でも触れましたが、既存の物理ファイル（DDS / CRTPFで作成されたファイル）からそのファイルを作成するための DDL を自動生成させる機能も提供されています。手動で行う場合には i ナビゲータ / Navigator for i が利用可能です。また、QSQGNDDL API も提供されています。文字通り（Generate Data Definition Language）指定したオブジェクトから DDL を生成し、それを指定したソース・ファイルのメンバーに出力してくれます。 生成されたソースをテキスト・ファイル化して保存しておけば、必要に応じて（もちろん他の IBM i でも）再実行可能であり、VCS 等でバージョン管理を行えば IBM i のデータベース定義のバージョン管理を実行することもできますね。 先ほど、2 つのインターフェースで作成されたファイルにはほとんど違いはないと書きましたが、細かく見ると注意すべき相違点もあります。例えば、SIZE と REUSEDLT はそれぞれ異なります。まず SIZE についてみていきましょう。

• CRTPF で作成されたファイル
  • 初期レコード数：10,000
  • 増分レコード数：1,000
  • 最大増分値：3
• CREATE 文で作成されたテーブル
  • 初期レコード数：*NOMAX

SQL で作成されたテーブルの SIZE は、CRTPF コマンドの省略値は使用されていないのが分かります。プログラム・エラー等で無制限にレコードが追加されてしまうのを防ぐ目的であえて CRTPF コマンドの初期値を使用している場合には、CREATE 文でテーブルを作成した後で CHGPF で SIZE を指定し直す必要があります。 REUSEDLT は以下の用に設定されます。

• CRTPF で作成されたファイル
  • *NO
• CREATE 文で作成されたテーブル
  • *YES

これも必要に応じて CHGPF で設定し直す必要があるかもしれません。

システム期間テンポラル表

それでは 7.3 で利用可能になった、SQL インターフェースを介して実現可能なシステム期間テンポラル表を紹介します。 この機能は一言で言うと、データベースのタイムトラベルを可能とするものです。通常、データベースのレコードは今現在の状態しか記録していませんが、この機能を使うことで過去のある時点でそのデータの内容がどうだったのかを参照することを可能にしてくれます。V7.3 以前のシステムでもこれと類似した機能をプログラムで実装したシステムはあるかと思いますが、そのロジックは複雑になるであろうことは容易に想像できます。 システム期間テンポラル表を使えば、データベースに変更が発生する都度、その変更前の状態をシステムが自動的に保管し、参照する際にも過去の日時を SQL 文で指定するだけで RDBMS が該当のデータを抽出してくれます。また監査列の機能（後述）を使用することにより、誰がどのジョブからそのデータを変更および削除したのかという情報をシステムに記録させることも可能となり、変更履歴の記録とその監査目的でもこの機能を利用することができます。 システム期間テンポラル表を利用するには以下のステップが必要です。

• どのファイルを対象とするかを決定
  • マスター系のファイルは対象にするが、トランザクション系は対象外にするなど
• この機能を実現するために必要なフィールドを追加
  • ALTER TABLE
• 変更前および削除されたレコードを保存するための履歴表を作成
  • CREATE TABLE <履歴表> LIKE <対象ファイル名>
• バージョン管理を追加して履歴管理を開始
  • ALTER TABLE < 対象ファイル名> ADD VERSIONING USE HISTORY TABLE <履歴表>

バージョン管理が追加されるとそれ以降にデータベースに対する変更があった場合に、システムが自動的に変更前の情報を履歴表に追加してくれます。当然ですが、その変更を行ったインターフェースは何であっても構いません。RPG プログラムでも、DFU でも、あるいは別サーバーから xDBC でも、変更があれば必ずシステムがそれを記録します。 システム期間テンポラル表の対象ファイルはジャーナル処理されていることが前提となります。歴史的に見て、IBM i ではジャーナル処理はオプションとして捉えられており、コミット機能を利用するなどのごく限られたケースでしかジャーナル処理されていないのが実情だと思います。しかし、SQL インターフェースではジャーナル処理が前提であること（SQL インターフェースでライブラリーを作成すればそのライブラリーに作成されるファイルは自動でジャーナル処理対象となる）、システム期間テンポラル表もこれが前提であることなどを考えると、今後はデータベースのジャーナル処理は必須と考えておくべきでしょう。ジャーナル処理を実施するために必要なオブジェクトの作成およびセットアップコマンドについてはここでは省きますが、以下コマンドを実行することでオブジェクト作成時に意識しなくても自動的にジャーナル処理を開始することができますので覚えておいてください。

• STRJRNLIB LIB(<ライブラリー名>) JRN(<ジャーナル名>)

上記コマンドを実行すると、その後指定したライブラリーに作成される物理ファイル / テーブルは自動的にジャーナルが開始されます。

監査列

監査列も IBM i 7.3 の新機能です。データベースのレコードに対して変更があった場合に、変更のタイプやそれを行ったユーザーをシステムが自動的に記録します。このフィールドのことを監査列といいます。 監査列に設定可能な値は以下の 3 種類があります。

• データ変更タイプ
• 特殊レジスター
  • データベース・マネージャーによって定義される情報
• 組込グローバル関数
  • データベース・マネージャーによって用意されるクライアント情報

データ変更タイプは、そのレコードの最終変更が挿入（I）か更新（U）かを表す値がセットされます。先の履歴表では、削除（D）を表す値をセットさせることも可能です。 特殊レジスターを使用すれば、その変更を行った OS 情報やユーザー名やプログラム名などを自動的に記録させることができます。 組込グローバル関数を使用すれば、IBM i のジョブ識別情報（ジョブ番号/ユーザー名/ジョブ名）やクライアントの IP アドレス、ポート番号などを記録することができます。 DFU で変更した場合と、Run SQL Scripts 機能で UPDATE 文で更新した場合にそれぞれの監査列にどのような値がセットされるかは以下のサンプルで確認してください。 上記の値をセットするように監査列をセットアップするには以下の SQL 文を実行します。 特殊レジスターおよび組込グローバル関数に関しては、以下のサイトを参照してください。

• 特殊レジスター
• 組込グローバル関数

注意点

IBM i の *FILE オブジェクトは、データベース・ファイル（物理ファイル・論理ファイル / テーブルDDS・ビュー・インデックス）の他に、表示装置ファイル、印刷装置ファイル、通信ファイルなどがあることは前回の記事で説明したした通りです。そしてデータベース・ファイル・オブジェクトに関しては DDS + CRTPF でも DDL でもどちらでも作成可能であることも理解いただけたと思います。 しかし、データベース以外のファイルを作成するためには DDS + CRTxxxF が必要であり、DDL では対応できないことに注意してください。DDL はあくまでもデータベース・オブジェクトを定義するためのインターフェースです。対話型プログラム、印刷プログラムなどの IBM i 特有のシステムを開発および保守するためには、依然として DDS が必要であることを覚えておいてください。

おわりに

他のプラットフォームの技術者が IBM i のプロジェクトに参加する場合に障壁になっているものはいくつかありますが、IBM i 7.3 でその多くが解決されてきていると思います。

• 開発環境
  • SEU / PDM などの CUI 環境から RDi / Orion などの GUI 環境へ
• 言語
  • 定位置記入形式の RPG から フリーフォーム RPG へ
  • PHP、js、Java などのオープンな言語のサポート
• データベース
  • DDS / CRTPF インターフェースから SQL インターフェースへ

また、今回の記事では触れませんでしたが、SQL を RPG に組み込むことも可能です。これは RPGⅣ だけではなく、RPGⅢ でも可能な点は特に強調しておきたいと思います。 SQL は IBM i の技術者にとって今後は必須の技術になるでしょう。以前の記事でも書きましたが、IBM i の技術者も上記のツールや言語などに慣れ親しみ、様々なプラットフォームの技術者とともに、IBM i の開発に取り組んで行かなければなりません。 日々押し寄せてくる新しい技術に対して、最新の 7.3 の機能を使ってたくさんの技術者とともに挑戦し、エンドユーザーの要求にさらに迅速に答えていくシステムにステップアップしていきたいですね。 次回は IBM i のユーザーが利用するインターフェースについて解説したいと思います。GUI 環境や 5250 環境など、ACS を中心に説明していく予定です。お楽しみに！