ここ数年の仕事として、IBMi上でプログラムを組むよりも、他言語へのマイグレーションのサポートで、現行システムの解析をすることが増えました。解析を円滑にするために、IBMiの内部情報を利用しやすくする為の道具を作成しているので、自分の復習も兼ねて記事としてまとめることにしました。
対象環境
IBMi:
ver.7.4 (一部情報は ver.7.1、ver.7.2 などのやや古い情報も含んでいます)
IBM i Access:
ver.1.1
「SQLスクリプトの実行」(JDBC)、Python + pypyodbc + i Access ODBC driver からの利用を想定していますが、エミュレータ内のSQL実行コマンドからの実行も可能です。
注意
主にSQLの機能を利用しています。利用対象は QSYS(システムライブラリ)にあるカタログ情報を利用してる為、全てのユーザーが利用可能ではありません。(ユーザーグループ:*QPGMR より権限の低いユーザーは参照すらできないと思います)
全ユーザー、及びSQLインターフェースを介して利用するために、QSYS2(SQL用ライブラリ)、SYSIBM(SQL用ライブラリ) に、同様のカタログが提供されており、IBMでも、そちらを利用することを推奨していますが、IBM i 固有の機能に由来する情報が欠落するため、今回はQSYS にあるカタログ情報を利用することにしました。
見えなくなる情報
- ソースファイルの情報(メンバー情報は無い為、多分必要ないです)
- DDSを使用しないで作成したファイルの情報(
CRTPF filename LEN(100)などで作成したもの)
情報源
QSYS/QADBXREF を利用します。
カラム情報は以下の通りです。
| Column | Length | Scale | Type | Bytes | Include | Text |
|---|---|---|---|---|---|---|
| DBXFIL | 10 | A | 10 | * | ファイル名 | |
| DBXLIB | 10 | A | 10 | * | ライブラリー名 | |
| DBXDIC | 10 | A | 10 | ﾃﾞｨｸｼｮﾅﾘｰ 名 | ||
| DBXOWN | 10 | A | 10 | * | 所有者の ﾕｰｻﾞｰ･ﾌﾟﾛﾌｧｲﾙ 名 | |
| DBXTXT | 50 | O | 50 | * | ﾌｧｲﾙ･ﾃｷｽﾄ | |
| DBXATR | 2 | A | 2 | * | PF- 物理 ,LF- 論理 ,TB-ﾃｰﾌﾞﾙ,VW-ﾋﾞｭｰ,IX- 索引 | |
| DBXLNK | 1 | A | 1 | * | E- 外部記述 , P-ﾌﾟﾛｸﾞﾗﾑ 記述， ﾌﾞﾗﾝｸ- 連係なし | |
| DBXSQL | 1 | A | 1 | I-IDDU,S-SQL,C-CRTDTADCT,X- 移行 ,ﾌﾞﾗﾝｸ- 連係なし | ||
| DBXTYP | 1 | A | 1 | * | D-ﾃﾞｰﾀ･ﾌｧｲﾙ ， S-ｿｰｽ･ﾌｧｲﾙ | |
| DBXNFL | 5 | 0 | S | 5 | * | ﾌｨｰﾙﾄﾞ の最大数 |
| DBXNKF | 5 | 0 | S | 5 | * | ｷｰ･ﾌｨｰﾙﾄﾞ の最大数 |
| DBXRDL | 11 | 0 | S | 11 | * | 最大 ﾚｺｰﾄﾞ 長 |
| DBXIDV | 11 | 0 | S | 11 | ﾃﾞｨｸｼｮﾅﾘｰ 内部 ﾌｧｲﾙ 定義 ID | |
| DBXREL | 1 | A | 1 | * | リレーショナル・ファイル : Y=YES,N=NO | |
| DBXLFI | 130 | A | 130 | * | 長いファイル名 | |
| DBXLFQ | 1 | A | 1 | 詳細 ﾌｧｲﾙ 名は引用符つき | ||
| DBXWCO | 1 | A | 1 | C-ｶｽｹｰﾄﾞ, Y-YES (ﾛｰｶﾙ), N-NO | ||
| DBXUPD | 1 | A | 1 | Y-YES, N-NO | ||
| DBXUNQ | 1 | A | 1 | * | D-NO, V-YES (NULL 重複可能）， U-YES, E-EVAP | |
| DBXREM | 2000 | G | 4002 | 詳細記述 | ||
| DBXDFN | 5000 | G | 10002 | * | SQL ビュー定義 | |
| DBXATS | 26 | Z | 26 | ファイル変更タイム・スタンプ | ||
| DBXLB2 | 130 | A | 130 | ライブラリー名 | ||
| DBXADB | 20 | A | 20 | リレーショナル・データベース別名 | ||
| DBXALB | 130 | A | 130 | ライブラリー別名 | ||
| DBXAFL | 130 | A | 130 | ファイル別名 | ||
| DBXAMB | 12 | A | 12 | メンバー別名 | ||
| DBXINSERT | 1 | A | 1 | Y-YES N-NO | ||
| DBX_PASP | 2 | 0 | B | 2 | 基本 ASP | |
| DBXSO | 1 | A | 1 | * | Y-S/O 論理， D-DYNSLT, N- 非 S/O 論理 | |
| DBXSYS | 1 | A | 1 | Y- システム・ | ||
| DBXPART | 2 | 0 | B | 2 | 区画数 | |
| DBXISPAN | 1 | A | 1 | INDEX OVER PARTITIONED TBL SPANS ALL MEMBERS | ||
| DBXDELETE | 1 | A | 1 | Y - はい , N - いいえ | ||
| DBXDEFINER | 10 | A | 10 | 定義者 | ||
| DBXDECFRND | 1 | A | 1 | 10 進浮動小数点丸めモード | ||
| DBXIDXSEL | 1 | A | 1 | 索引に選択基準がある : Y-YES N-NO | ||
| DBXIDXSELX | 2000 | G | 4002 | 索引 選択 基準 | ||
| DBXIDXSELU | 1 | A | 1 | 索引に UDF を含む選択基準がある | ||
| DBXIDXDVKY | 1 | A | 1 | 索引に派生キーがある : Y-YES N-NO | ||
| DBXIDXUDF | 1 | A | 1 | UDF を使用する索引 : Y-YES N-NO | ||
| DBXIDXINC | 1 | A | 1 | 索引に組み込みの式がある : Y-YES N-NO | ||
| DBXIDXINCX | 2000 | G | 4002 | 索引組み込み式 | ||
| DBXTXT2 | 50 | G | 102 | ファイルのテキスト | ||
| DBXCTL | 1 | A | 1 | ｱｸｾｽ 制御： ﾌﾞﾗﾝｸ- なし， R- 行， C- 欄， B- 両方 | ||
| DBXTMPTYP | 1 | A | 1 | テンポラル表タイプ： S-ｼｽﾃﾑ, H-ﾋｽﾄﾘｰ, N- なし | ||
| DBXONCMT | 1 | A | 1 | D- 行の削除， P- 行の保持， null -ﾃﾝﾎﾟﾗﾙ 表以外 | ||
| DBXLOGGED | 1 | A | 1 | N-ﾛｸﾞ されない， Y-ﾛｸﾞ される null-ﾃﾝﾎﾟﾗﾙ 表以外 | ||
| DBXONRLBK | 1 | A | 1 | D- 行の削除， P- 行の保持， null-ﾃﾝﾎﾟﾗﾙ 表以外 |
ぱっと見てわかる通り、カラム名と、意味が暗号化コード化されています。
この中から、include 列に "*" のついている列を抽出した"View"を作成しますが、作成に当たり、次のことを心がけています。(自分への戒め)
Readable
- ぱっと見て意味がわかるように、
暗号コードをきちんとした用語に変換する。(長すぎて見難くならない程度に) - カラム名で悩まないよう、SQLの標準的な名前を付ける。(ただし、従来のエミュレータからの利用も考慮して、なるべく10文字以内の識別子に収まるように多少の略号は使用します)
Reusable
- null の呪いに悩まされないよう、allow null なカラムに空白文字をセットしておく。
- 長い文字列は表示したときに見にくいので、トリミングしておく。
-
方言IBM i 固有のSQL機能の理由は控える。そもそもカタログ情報自体がDBMS毎に独自に実装されているので、移植性ゼロですが、標準語を忘れないように。
create view statement
以下が、view 作成のSQL文になります。
一般的には、識別子:小文字、SQLキーワード、関数:大文字で書くと思いますが、IBMiの識別子は大文字限定に近い為、逆転しています。(識別子を小文字にすると違和感がありすぎて、読み取るのに苦労する身体になってしまったので...)
create or replace view {TOOLLIB}.FILED as (
select DBXLIB as LIBRARY,
DBXFIL as FILE,
case DBXATR when 'PF' then 'PF'
when 'LF' then 'LF'
when 'TB' then 'TABLE'
when 'VW' then 'VIEW'
when 'IX' then 'INDEX'
else 'UNKNOWN' end as FILETYPE,
case DBXLNK when 'E' then 'EXTERNAL'
when 'P' then 'PROGRAM'
when ' ' then ' '
else 'UNKNOWN' end as LINK,
case DBXTYP when 'D' then 'DATA'
when 'S' then 'SOURCE'
else 'UNKNOWN' end as DATATYPE,
DBXNFL as COLUMNS,
DBXNKF as KEYCOLUMNS,
DBXRDL as LENGTH,
DBXREL as RELATIONAL,
case when DBXUNQ is null or DBXUNQ = 'D' then ' '
when DBXUNQ = 'V' then 'UNIQUE ALLOW NULL'
when DBXUNQ = 'U' then 'UNIQUE'
when DBXUNQ = 'E' then 'EVAP???'
else 'UNKNOWN' end as HAS_UNIQUE,
case DBXSO when 'Y' then 'SELECT/OMIT'
when 'D' then 'DYNAMIC SELECT'
when 'N' then ' '
else 'UNKNOWN' end as HAS_SELECT,
DBXTXT as trim(TEXT),
DBXOWN as OWNER,
DBXATS as CHANGEDATE,
DBXLFI as trim(LONGFILE),
coalesce(trim(DBXDFN), '') as STATEMENT
from QSYS.QADBXREF
WHERE DBXLIB NOT LIKE 'Q%'
AND DBXLIB NOT LIKE 'SYSIBM%'
);
解説(一部だけ)
読みやすくするため、case文を駆使して、翻訳を行っているのでview作成の為のSQL文が醜いですね。
これで、システムカタログから、リアルタイムのファイル情報を見やすく表示できるようになります。システムカタログの性質上、ファイルの定義変更を行うと即時に反映されるので、情報が古いという、よくある罠にはまることは避けられると思います。
また、実データを持たないため、メモリにも優しいです。(うろ覚えで申し訳ありませんが10～20KB位だったかと)
{TOOLLIB}
実際にviewを置くライブラリ名に置き換えてください。
一般的なコードページ(CCSID=5026)で入力できない"{ }"で囲ってあるのは、Pythonのフォーマット文字列で実際の値を埋め込むことを目論んでいる為です。(実際にはそこまで、Python側でごにょごにょできていません)
where句の条件
解析作業で利用することはあまり無いので、システムライブラリは除外しています。
そこも必要なら条件を書き換えて下さい。
LIBRARY
SQL標準で言えば「schema」にあたるので、名称を変えようか迷っていましたが、「馴れ」の問題がありますので、そのままにしておきました。
FILE
IBM i のファイルは 物理ファイル(Tableに相当)と論理ファイル(index に相当しますが、view の機能を兼ね備えています)を共に「ファイル」として同列に扱える為、そのままFILEとしてあります。(SQLで言えば、Table と View を同じ select 文でアクセスできることと似たようなものです)
FILETYPE
エミュレータの表示では...
物理ファイルも、SQLで作成したテーブルも、属性表示は同じ **"PF"ですが、内部では区別されています。ここでは、区別した属性を表示しています。
同様に論理ファイル、SQLで作成した index、SQLで作成した view は全て"LF"**と表示されますが、区別して表示しています。
HAS_UNIQUE
ユニーク制約があるファイルの場合に値がセットされます。3種類ありますが、以下の違いがあります。
- UNIQUE ALLOW NULL unique指定されていますが、一意識別するためのカラムの一部にnull値が許されています。null値のある行を除外した結果が一意になればOKとなります。
- UNIQUE unique指定あり、かつ、一意識別する為のカラムには null値は許されません。ただし、以下の "HAS_SELECT"との絡みがあるため、本当の意味でのUniqueとはなり得ません。→ Primary key とは限らないということです。
- EVAP カラムテキストのまま、よくわかりません。
HAS_SELECT
IBM i 固有機能である「選択/除外条件」(ファイルアクセス時に自動で事前定義されたwhere条件が適用されるもの)がある場合は、空白以外の文字が入ります。(Dynamic Selectと、そうではないものの違いがよくわかりません)
上の "HAS_UNIQUE"との絡みで、HAS_UNIQUE ='UNIQUE' and HAS_SELECT <> ''の場合、『選択/除外に定義した行の選択が行われた後で一意になればOK』という仕様となっています。その為、"UNIQUE"と書かれていても、条件外の行には重複が大量にあるということが起こり得ます、実際ありました、一杯...。その為、ここもあわせて確認しないと、SQL、というかリレーショナルデータベース的な意味の一意制約は満たしませんのでご注意を。
CHAGEDATE
ファイルの中身のデータの最終更新日ではなく、ファイル定義の最終更新日(or 一回も更新が無ければ作成日)となります。
以上となります。
この後も解析用のオブジェクト作成を投稿していく予定です。
今回は初回ということで、少し「前提」的な説明が長くなってしまいました。
引き続きよろしくお願いいたします。