AS400/IBMi ストアドプロシージャを登録する

あまりよく解かっていませんが自分の備忘録のために記事を書きます。

なんかストアドプロシージャを使えば一連のロジックをまとめることができるらしいのです。ストア（保管）ですね。今回はSQLだけですが、SQL以外にもストアできるみたいです。

そんなのCL内にSQLを書いてコンパイルすればいいのではと思うかもしれませんし、私も今のところよく解ってないのでそう思っているのですが、どうやらDBMS内に保存できるらしいのです。テーブルのレコードとして。おお！それなら管理が楽そう（いや、むしろ面倒かも）たぶんそんな感じです。

登場人物は商品マスタと単価マスタです。２つはリレーショナルとして関連していますが、商品マスタがベースになります。１対多です。ゆえに商品マスタが消えると単価マスタにある該当レコードは削除されてしかるべきです。

そんなの商品マスタを消したときに単価マスタも消すロジックが走れば良いと思いますが、ウチの場合は複数のライブラリにあるので消すのが面倒です。また単価マスタに残っていてもすぐには困りませんし（いつか困る）

そのとき下記のようなSQLを走らせることになりますが、

DELETE ONELIB.TANKAF WHERE DAI = '13' AND CYU = '61' AND REN = '011';
DELETE TWOLIB.TANKAF WHERE DAI = '13' AND CYU = '61' AND REN = '011';
DELETE THRLIB.TANKAF WHERE DAI = '13' AND CYU = '61' AND REN = '011';
DELETE FOULIB.TANKAF WHERE DAI = '13' AND CYU = '61' AND REN = '011';
DELETE FIVLIB.TANKAF WHERE DAI = '13' AND CYU = '61' AND REN = '011';

これを使いまわしするときに、CODEの値を替え間違える可能性があります。そんなとき変数を使えると便利なので調べてみたのですが変数を使う方法ってBEGINとかDECLAREを使えばできるっぽいのですが、下記のようなコードを書いてもジャーナルがどうのこうのでうまく行きませんでした。

-- うまく行かなかったコード
BEGIN ATOMIC

-- 変数の宣言
DECLARE @DAI CHAR(2);
DECLARE @CYU CHAR(2);
DECLARE @REN CHAR(3);

-- 変数への値の設定
SET @DAI = '13';
SET @CYU = '61';
SET @REN = '011';

-- 各テーブルに対するDELETE文の実行
DELETE FROM ONELIB.TANKAF WHERE DAI = @DAI AND CYU = @CYU AND REN = @REN;
DELETE FROM TWOLIB.TANKAF WHERE DAI = @DAI AND CYU = @CYU AND REN = @REN;
（以下略）

END

色々調べていくとストアドプロシージャを登録しておくのが良いらしいのです。

文例はこんな感じです。

CREATE PROCEDURE ライブラリ.プロシージャ名 (IN 変数 型(字数), ..)
LANGUAGE SQL
BEGIN
    SQL文
END;

私の場合はこんな感じです。

CREATE PROCEDURE HOGELIB.DLTTANKAF
(IN V_DAI CHAR(2), IN V_CYU CHAR(2), IN V_REN CHAR(3))
LANGUAGE SQL
BEGIN
    DELETE FROM ONELIB.TANKAF WHERE DAI = V_DAI AND CYU = V_CYU AND REN = V_REN;
    DELETE FROM TWOLIB.TANKAF WHERE DAI = V_DAI AND CYU = V_CYU AND REN = V_REN;
    DELETE FROM THELIB.TANKAF WHERE DAI = V_DAI AND CYU = V_CYU AND REN = V_REN;
    DELETE FROM FOULIB.TANKAF WHERE DAI = V_DAI AND CYU = V_CYU AND REN = V_REN;
    DELETE FROM FIVLIB.TANKAF WHERE DAI = V_DAI AND CYU = V_CYU AND REN = V_REN;
END;

これを下記で実行すると動きます。

CALL HOGELIB.DLTTANKAF('13','61','012');

７秒くらい時間かかりますが、無事完了して全てのライブラリのファイルから指定したレコードが削除されます。

でも、これ絶対忘れるやつですね。そうです、１年も経つと作ったこと自体を忘れてしまうやつです。間違いないです。

なので管理しなければなりません。ではどうやって登録したプロシージャを確認するのかといえば、下記になります。

STRPDMの2でライブラリを指定して照会する
QSYS2.SYSPROCSをSQLのSELECT文で見に行く

これで見ることができます。PDMで見るとタイプは*PGMになっていて、属性はCLEになっています。5番で表示しても登録したSQL文はよくわからないです。

またQSYS2.SYSPROCSを確認すると膨大なレコードが存在し、確かにその1行に登録した名前がありました。具体的には

SPECIFIC_SCHEMA = HOGELIB
SPECIFIC_NAME = DLTTANKAF
ROUTINE_BODY = SQL
ROUTINE_DEFINITION = SQL文

という感じでした。それぞれにCREATE PROCEDUREで指定したものが確認できました。

気になる点1 BEGIN以降しか見れない

PDMは全く見れませんがQSYS2の見方で見ると一応SQL文は見ることができました。しかしBEGINとENDで指定した箇所だけでした。ゆえにCREATE PROCEDUREの後の変数の方とかその他指定したものは見れないことになります。

これでは実質なにを登録したのかがわかりません。

調べてみると、ストアドプロシージャはしばしば重要なビジネスロジックやデータベース操作が含まれていて、ソースコードを後悔することは潜在的なリスクを招く可能性があるので、厳密には作者しか見れないようにしているそうです。

それもそうですね。SQLの中身が見れたらSQLインジェクションで攻撃できる可能性もありますので。ゆえに見れなくしているっぽいです。

そうなると作る側の私は、登録したプロシージャの管理だけでなく、ソースコードまでキッチリ管理しておかなければいけないということです。

気になる点2 長いプロシージャ名は書けない

今回の機能は全てのライブラリの単価ファイルのレコードを削除するというものです。なので、最初はDLT_ALL_TANKAF_ROWみたいな名前にしようと思っていたのですが、というか登録したのですが、PDMで見るとなぜかHOGELIB\/DLT_A00001という名前に置き換えられていました。

これはAS400のファイル名が10文字しか入らないからですね。これだと登録した私も迷ってしまいますし、別の名前と重複したら怖いので、なるべく10文字で収まるようにしたほうが良いと思います。

ちなみに長い名前を指定した場合、QSYS2で見ると

SPECIFIC_NAME = 元の名前
ROUTINE_NAME = 元の名前
EXTERNAL_NAME = 変換後の名前

になっていました。

気になる点3 プロシージャを消すには

いづれ内容を修正したい衝動に駆られると思いますが、それは削除＆登録で済むのでともかく削除です。削除するのに思い当たるのは、照会した際のPDMから4で消すか、QSYS2のレコードをDELETEで消すかですが、聞いたところDROP PROCEDUREという命令があるそうです。

私の場合はこちらになりました

DROP PROCEDURE HOGELIB.DLTTANKAF;

実行したところちゃんとPDMの2からも、QSYS2からも消えていました。消し方はこの方がいいですね。

ちなみに前述した、長いプロシージャ名を指定して短い仮名をつけられた場合は、ちゃんとその長いプロシージャ名を指定してDROPをしないと怒られてしまいますのでご注意を。プロシージャ名を指定していないので、そりゃそうですよね。

ちなみにPDMやQSYS2から直接消すことは試していません。当たり前ですが、IBMi内でおかしなデータが残るかもしれないので、やらない方がいいと思います。

気になる点4 プロシージャの保存場所

プロシージャの保存場所、すなわちライブラリの指定についてです。IBMiはQSYSというシステムライブラリとユーザライブラリが分類としてありますが、プロシージャはユーザライブラリに作るほうがいいそうです。また、プロシージャ専用のライブラリを作って保管するのがいいようです。

まとめ

使いどころは私もこれからですが、管理するのが大変そうなので、キッチリしようと思っています。一連の操作はACSのRunSQLScriptsでやると思いますが、ソースコードは名前を付けて保存でファイル化するので、フォルダで管理しようと思います。Gitで管理といっても逆に面倒な気がしますし。

プロシージャ側はプロシージャ専用ライブラリを作るか、既存のライブラリに突っ込むならQSYS2.SYSPROCSの該当行を照会できるようにWebで作っておくか、ですね。

何かいい方法やこういう使い方があるよと教えて頂ける優しい方がいらっしゃればコメント欄にお願いします。