メニューボタン
IBMi海外記事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

このケースでは、ファイルMFGORDDTLおよびVENDORSが再編成されました。

私の中の潔癖主義者は、関数は決してデータを変更するべきでない、と言います。一方、現実主義者は、「 決して 」とか「 必ず」という言葉は使用すべきではない、と言います。すべてがオープンで包み隠すようなことが一切ないのであれば、このような関数を使用することに問題があるとは思えません。

サイト内全文検索

PAGE TOP