関数以外のことができるSQL関数｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2020.03.25

関数以外のことができるSQL関数

Ted Holt 著

SQL SELECTステートメントはデータを取得するだけだということは誰もが知っています。データを変更したいなら、INSERT、UPDATE、DELETE、またはMERGEを使用しなければなりません。ところが、偉大な音楽家、ジョージ・ガーシュウィンに言わせれば、『 It Ain't Necessarily So（必ずしもそうじゃない）』ということのようです。データの変更は、SELECTステートメントから行うこともできますし、場合によっては、そうしたほうがよいケースもあります。以下に、その方法を紹介します。

ここでは、10%以上の削除済みレコードがある物理ファイルに対して、「物理ファイル・メンバー再編成（RGZPFM）」コマンドを実行するSELECTステートメントの例を使用します。もちろん、普通のCLでこうした処理を行うことができますし、実際にこのタスクを行う必要があるとしたら、そちらで行うと思います。しかし、読者の皆さんのシステムで実行できるシンプルな例が必要であり、そうなると、この例が要件にぴったりということになります。

まず、ライブラリー内のすべての物理ファイルメンバーの関連情報が必要です。これは、「ファイル記述の表示（DSPFD）」コマンドで行うことができます。

DSPFD FILE(SOMELIB/*ALL) TYPE(*MBRLIST) +
   OUTPUT(*OUTFILE) OUTFILE(QTEMP/FD)

SQLを使用してこのデータを取得したい場合は、 SYSPARTITIONSTAT カタログビューを見てみます。

次に、物理ファイルメンバーを再編成することができるSQL関数が必要です。

create or replace function mylib.ReorgPF
  (p_Library varchar(10), p_File varchar(10), p_Member varchar(10))
  returns integer
  modifies sql data
  returns null on null input
  begin
     declare v_Command   varchar(80);
     declare exit handler for sqlexception
        begin return 0; end;
     set v_command = 'RGZPFM FILE(' concat trim(p_Library) concat
                                '/' concat p_File    concat
                           ') MBR(' concat p_Member  concat ')';
     call qsys2.qcmdexc(v_Command);
     return 0;
  end;

何か奇妙に思えることがないでしょうか。あるはずです。関数は、0個以上の値を与えられて、値を返すルーチンです。この関数は、そうした典型に合致するものではありません。ここにあるものは、関数を装ったストアードプロシージャーなのです。

MODIFIES SQL DATA節に注目してください。この節がない場合、システムは再編成の処理を許可しません。

最後に、10%以上の削除済みレコードがあるメンバーを選択する照会が必要です（10%ではなく9.5%を指定しているのがポイントです）。

select MLLIB,MLFILE,MLNAME,MLNRCD,MLNDTR,
        (MLNDTR/(MLNRCD + MLNDTR)),
        ReorgPF(f.MLLIB,f.MLFILE,f.MLNAME)
  from qtemp.fd as f
 where MLNDTR > 0
   and (MLNDTR/(MLNRCD + MLNDTR)) >= .095;

最後の列に注目してください。選択された各ファイルメンバーに対して関数が実行されます。以下が、私のテストデータです。

MLLIB	MLFILE	MLNAME	MLNRCD	MLNDTR	式
SOMELIB	CUSTOMERS	CUSTOMERS	58	2	0.033333
SOMELIB	MFGORDDTL	MFGORDDTL	53	7	0.116667
SOMELIB	MFGORDHDR	MFGORDHDR	40	0	0
SOMELIB	PODTL	PODTL	10	0	0
SOMELIB	POHDR	POHDR	58	2	0.033333
SOMELIB	REPS	REPS	60	0	0
SOMELIB	SHIPDTL	SHIPDTL	58	2	0.033333
SOMELIB	SHIPHDR	SHIPHDR	0	0	invalid
SOMELIB	SLSORDDTL	SLSORDDTL	59	1	0.016667
SOMELIB	SLSORDHDR	SLSORDHDR	56	4	0.066667
SOMELIB	VENDORS	VENDORS	50	10	0.166667