概要
今回、RDBのビューを検証することにしました。今まで利用する機会が無かったもので。
ビューとは?
ズバリ、下記の説明でバッチリ理解できるでしょう。
ビューの実態は単なる「名前を付けたSELECT文」
(引用元:『スッキリわかるSQL入門』)
つまり、実態も持たない、仮想テーブルという認識で間違いないでしょう。
メリットとしましては、「機密フィールド」を隠蔽してセキュアにしたり、複雑なSQLをまとめてシンプル化を図ったりといったところ。
デメリットは、乱発してしまった場合、ロジックが分離することでカオスが生じてしまいがち・・・合っているのかなぁ~。
DB2 for iでの動作確認
まあ、わからないことは実際に「手を動かして試す」に限ります!
事前準備
適当に、野菜テーブルと肉テーブルを作りました(かなり適当です)。
SQLを実行できるツール、もしくは5250エミュレータで「STRSQL」コマンドでSQL文を打てるようにします。
CREATE TABLE TIGERDB.TVEGETABLE ( ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NO CACHE), NAME CHAR(32), PRICE INT, MEMO CHAR(40), CRT_TS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UPD_TS TIMESTAMP NOT NULL, PRIMARY KEY(ID) ) RCDFMT RECVEG -- TIGERDB にテーブル TVEGETABLE が作成されましたが,ジャーナル処理されていません。 INSERT INTO TIGERDB.TVEGETABLE (NAME, PRICE, MEMO,UPD_TS) VALUES (' トマト ',198, '', '0001-01-01-00.00.00') -- TIGERDB の TVEGETABLE に 1 行が挿入された。 INSERT INTO TIGERDB.TVEGETABLE (NAME, PRICE, MEMO,UPD_TS) VALUES (' ごぼう ',240, '', '0001-01-01-00.00.00') -- TIGERDB の TVEGETABLE に 1 行が挿入された。 CREATE TABLE TIGERDB.TMEAT ( ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NO CACHE), NAME CHAR(32), PRICE INT, MEMO CHAR(40), CRT_TS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UPD_TS TIMESTAMP NOT NULL, PRIMARY KEY(ID) ) RCDFMT RECMEAT -- TIGERDB にテーブル TMEAT が作成されましたが,ジャーナル処理されていません。 INSERT INTO TIGERDB.TMEAT (NAME, PRICE, MEMO,UPD_TS) VALUES (' 豚肉 ',500, '', '0001-01-01-00.00.00') -- TIGERDB の TMEAT に 1 行が挿入された。 INSERT INTO TIGERDB.TMEAT (NAME, PRICE, MEMO,UPD_TS) VALUES (' 牛肉 ',800, '', '0001-01-01-00.00.00') -- TIGERDB の TMEAT に 1 行が挿入された。 INSERT INTO TIGERDB.TMEAT (NAME, PRICE, MEMO,UPD_TS) VALUES (' 鶏肉 ',350, '', '0001-01-01-00.00.00') -- TIGERDB の TMEAT に 1 行が挿入された。
ビューの作成
2つのテーブルを準備し、データが格納されたところで、ビューを作りました。
野菜と肉を結合したビューです。
CREATE VIEW TIGERDB.VITEM AS (SELECT 'VEGETABLE' AS CATEGORY, NAME, PRICE FROM TIGERDB.TVEGETABLE UNION SELECT 'MEAT' AS CATEGORY, NAME, PRICE FROM TIGERDB.TMEAT ) -- ビュー VITEM が TIGERDB に作成された。
ビューの調査
ビューを使用する
さっそく、作成したビューを使ってみましょう!
SELECT * FROM TIGERDB.VITEM ORDER BY PRICE -- CATEGORY NAME PRICE -- VEGETABLE トマト 198 -- VEGETABLE ごぼう 240 -- MEAT 鶏肉 350 -- MEAT 豚肉 500 -- MEAT 牛肉 800
オッケー!! 想定通りの参照ができました。
ビューSQLの確認
ここで気になるのは、仕様書等がなくて、どうやってビューの中身(SQL)を確認したらいいのか? ということです。
ACSの「SQLスクリプトの実行」を選択し、下記のSQL文を打つことで、中身を参照できました。これができれば、まずは安心です。
select * from sysibm.views where TABLE_NAME = 'VITEM'
ビューの書き込みテスト
ビューはあくまで読み取り専用で使いたいのですが、更新操作はできるのでしょうか?
上のSQLの検索結果内に、「IS_UPDATABLE = NO」とありますので、初期状態ではできなさそうですが、
UPDATE VITEM SET PRICE = 352 WHERE NAME = ' 鶏肉 ' -- TIGERDB のビュー,索引,テーブル VITEM は読み取り専用です。
案の定、更新不可でした。この方が助かります。
DSPDBRコマンドで確認
さて、気になるところは、AS400からはビューがどう映っているのでしょうか?
まずは、DSPDBRコマンドで、参照元テーブル(野菜)で調べてみました。
DSPDBR FILE(TIGERDB/TVEGETABLE)
データベース関係表示 DSPDBR コマンド入力 ファイル . . . . . . . . . . . . . . . . . : FILE TVEGETABLE ライブラリー . . . . . . . . . . . . . . : TIGERDB メンバー . . . . . . . . . . . . . . . . . : MBR *NONE レコード様式 . . . . . . . . . . . . . . . : RCDFMT *NONE 出力 . . . . . . . . . . . . . . . . . . . : OUTPUT * 仕様 ファイルのタイプ . . . . . . . . . . . . . : 物理 ファイル . . . . . . . . . . . . . . . . . : TVEGETABLE ライブラリー . . . . . . . . . . . . . . : TIGERDB メンバー . . . . . . . . . . . . . . . . : *NONE レコード様式 . . . . . . . . . . . . . . : *NONE 従属ファイルの数 . . . . . . . . . . . . : 1 指定されたファイルに従属するファイル 従属ファイル ライブラリー 依存関係 JREF 制約 VITEM TIGERDB データ
従属ファイルとして、ビューが出力されました。なるほど。
WRKOBJコマンドで確認
次にビューの属性を見てみます。今回は、WRKOBJコマンドを使いました。
WRKOBJ OBJ(TIGERDB/VITEM)
オブジェクトの処理 オプションを入力して,実行キーを押してください。 2= 権限の編集 3= コピー 4= 削除 5= 権限の表示 7= 名前の変更 8= 記述の表示 13= 記述の変更 OPT オブジェクト タイプ ライブラリー 属性 テキスト VITEM *FILE TIGERDB LF
ビューは、論理ファイル扱いでありました。
言われてみると、納得してしまいます。論理ファイルでは、フィールドを抜き出せたり、抽出条件を指定できたり、複数のテーブルを結合できたりしますからね。まさに、ビューとほぼ同じ役割です。
マテリアライズドビュー
少しビューを学習してみると、マテリアライズドビューという存在に気づきます。
仮想テーブルのビューに対して、こちらは実態ありのビューテーブル。
レスポンスを良くするために登場! しかし、リフレッシュするタイミングを決めたり、リソースをたくさん消費しまうなど、すぐに取り扱うには敷居が高そうです(そうでも無いのかな!?)。
まとめ
ビューについて実際に手を動かしながら、試してみました。
マテビューは試していませんが、通常のビューならば、これから出番がありそうです。
しかし、前段でも書きましたが、頻発すると苦労するシーンが容易に想像できるので、しっかり判断のうえ、利用していこうと思っております。
次回は「スキーマ変更した後、Viewがどうなるか?」について掲載する予定です。