SQLでよく使っているツール｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2019.05.16

SQLでよく使っているツール

Paul Tuohy 著

カンファレンスで何度も尋ねられる質問に、「SQLでxが何を表しているか、どうしたら分かるのですか」という質問があります。この記事では、SQLスクリプトの実行（Run SQL Scripts）でSQLの作業を行うときに私がよく使う4つのツール、すなわち、VALUES、SYSIBM.SYSDUMMY1、グローバル変数、システムカタログについて説明します。

VALUES

あるSQL関数がどのように機能するか理解しようとしているときに、最初に使用するのはVALUESステートメントです。VALUESは、直接、式から結果を導き出します。たとえば、次のステートメントでは、

values upper('paul');

次の結果セットが生成されます。

式には複数の値を指定できます。括弧ですべての式を囲むと、結果は1行になります。次のステートメントでは、

values  (upper('paul'), lower('PAUL'), trim('   Tuohy  '));

次の結果セットが生成されます。

括弧で囲まない場合は、1つの式につき1つの行が得られることになります。次のステートメントでは、

values  upper('paul'), lower('PAUL'), trim('   Tuohy  ');

次の結果セットが生成されます。

括弧で囲まないときは注意する必要があります。また、それぞれの式が同じデータタイプを返すようにする必要があります。一種のUNIONを実行していることになります。間違えた場合、たとえば、次のようなステートメントでは、

values upper('paul'), int(123.45);

次の結果セットが返されます。

これは求めていた結果ではありません。

結果セットの複数の式を見てみると、VALUESに関する問題点の1つは列見出しということになります。SELECT節のようにASで名前を割り当てることができません。ここから、次のSYSIBM.SYSDUMMY1に進みます。

SYSIBM.SYSDUMMY1

名前が示すように、SYSIBM.SYSDUMMY1はダミー表です。この表には、1つの行と1つの列が入っています。列名はIBMREQDで、「Y」という値が入っています。SYSIBM.SYSDUMMY1は、Web上およびKnowledge CenterのSQLのサンプルで幅広く使用されています。ここではその内容については触れません。SELECTステートメントで使用できる表が必要なだけです。

select upper('paul') upper_, 
       lower('PAUL') lower_, 
       trim('   Tuohy  ') trim_ 
from sysibm.sysdummy1;

このステートメントの結果は以下になります。

グローバル変数

ほとんどの場合、シンプルなリテラル（前の例の場合のように）を使用するのは問題ありませんが、大きいリテラル値（XMLまたはJSON）や、特定のデータタイプである必要がある値（XMLまたはストアードプロシージャーの呼び出しのためのパラメーター。つまり、リテラルが、あるデータタイプへキャストされる必要があるかもしれない場合）を処理することになる場合もあります。

グローバル変数は、値がジョブに特有である変数を使用できるようにします。グローバル変数は、列名またはリテラルが使用されているどこでも使用することができます。以下は、グローバル変数を作成する例です。

CREATE VARIABLE PTARTICLES.MY_LONG_NAME_VAR 
  FOR SYSTEM NAME MYVAR 
  VARCHAR(1000) 
  DEFAULT 'Some default text';

これで、リテラルまたは列を参照するどこでもグローバル変数を参照することができます。

values MY_LONG_NAME_VAR; 
values MYVAR;
select MYVAR from sysibm.sysdummy1;
set MYVAR = 'Different Text';

グローバル変数に関して注目すべき点を以下に示します。

グローバル変数の値は、ジョブに特有です。グローバル変数の値を変更しても、同じくそのグローバル変数を使用している他のどのジョブにも影響は及びません。
（SQLスクリプトの実行（Run SQL Scripts）で）Reconnect（再接続）する場合は、新しいバージョンのグローバル変数を処理することになります。新しいジョブを始めたばかりということです。
修飾名を使用せずにグローバル変数を参照したい場合は、スキーマ（グローバル変数を含む）がPATHディレクティブに含まれる必要があります。SET SCHEMAではそれが行われません。
グローバル変数を作成することは、結果としてサービスプログラムを作成することになるため、意味の分かりやすいシステム名にしたい場合は、システム名は重要です。

システムカタログ

あまり詳しくないデータベースでSQLスクリプトの実行（Run SQL Scripts）を使用するときの難題の1つは、列が表またはビューでどのような名前になっているかを知ることです。ここは、強力なプロンプト機能が役に立つ場面です（入力に応じて動作してくれます）。しかし、必要とする情報はすべて、システムカタログで入手できます。

システムカタログは、システム上のすべてのデータベース定義を相互参照するデータベースです。次のようにすると、表内の列をリストすることができます。

select column_name, system_column_name,
       column_text, data_type, 
       length, numeric_scale 
from qsys2.syscolumns
where table_schema = 'SIDDBV2'
and table_name = 'ALUMNI'
order by ordinal_position;

「Display Results in Separate Window」（結果を別ウィンドウに表示する）オプションを使用した場合は、必要に応じて参照できるレコードレイアウトになり、以下のように、結果ウィンドウから列名をコピーすることができます。