静的 SQL と動的 SQL の違い

静的 SQL と動的 SQL はそれぞれ異なる環境に適しています。目的のアプリケーションに静的 SQL または動的 SQL のどちらが適しているか判別するには、両者間の違いを検討する必要があります。

ホスト変数付き静的 SQL の柔軟性

入門的な概念

静的 SQL を使用する場合、SQL ステートメントの形式はプログラムを変更しないかぎり、変更できません。しかし、静的ステートメントは、ホスト変数を使用して、より柔軟性を持たせることができます。

例: 下記の例では、UPDATE ステートメントは、どの従業員の給与でも更新できます。バインド時には、給与を更新する必要があることはわかっていますが、実行時まで誰の給与をいくらに更新するかはわかりません。

01  IOAREA.
    02  EMPID              PIC X(06).
    02  NEW-SALARY         PIC S9(7)V9(2) COMP-3.
⋮ (Other declarations)
READ CARDIN RECORD INTO IOAREA
  AT END MOVE 'N' TO INPUT-SWITCH.
⋮ (Other COBOL statements)
EXEC SQL
  UPDATE DSN8C10.EMP
    SET SALARY = :NEW-SALARY
    WHERE EMPNO = :EMPID
END-EXEC.

ステートメント (UPDATE) は変化しませんし、基本構造も変わりませんが、入力は UPDATE ステートメントの結果を変更できます。

動的 SQL の柔軟性

あるプログラムがタイプと構造が異なるいくつかの SQL ステートメントを実行しなければならない場合は、どうなるでしょうか。タイプと構造が非常に多数のため、それぞれのモデルをプログラムに含めることができない場合、そのプログラムには動的 SQL が必要になります。

次のいずれかのプログラムを使用して、動的 SQL を実行できます。

Db2 Query Management Facility (QMF): ほとんどのSQL ステートメントを受け入れるDb2への代替インターフェースを提供します。
SPUFI: 入力データ・セットから SQL ステートメントを受け取って、その処理と実行を動的に行います。
Db2 command line processor: UNIXシステムサービス環境からSQLステートメントを受け入れます。

動的 SQL の制限

動的に使用できない SQL ステートメントがいつかあります。

動的 SQL 処理

動的 SQL を使用しているプログラムは、SQL ステートメントを文字ストリング形式で入力として受け取り、あるいはそれを生成します。プログラミングが単純化できるケースとは、複数の SELECT ステートメントを使用しないプログラムを作成可能な場合、または戻される値のタイプも個数も既知の SELECT ステートメントだけを使用するプログラムを作成可能な場合です。最も一般的なケースでは、実行される SQL ステートメントについては事前に何も分かっていないので、プログラムは通常、以下のようなステップをとります。

パラメーター・マーカーも含めた入力データを、SQL ステートメントに変換する。
実行する SQL ステートメントを準備して、結果表の記述を取得する。
取り出したデータを入れておくための十分な主ストレージを、SELECT ステートメント用に確保する。
ステートメントを実行し、あるいはデータ行をフェッチする。
戻された情報を処理する。
SQL 戻りコードを処理する。

静的 SQL と動的 SQL のパフォーマンス

Db2 データにアクセスするには、SQLステートメントにアクセスパスが必要です。 SQLステートメントのパフォーマンスに大きく影響する要因は2つあります。 Db2 が実行時にアクセスパスを決定するのにかかる時間と、そのアクセスパスが効率的であるかどうかです。 Db2 これらのいずれかのタイミングで、ステートメントのアクセスパスを決定します

SQL ステートメントを含むプランまたはパッケージをバインドする時
SQL ステートメントを実行する時

Db2 がアクセス・パスを決定する時刻は、以下の要因によって異なります。

ステートメントが静的に実行されるか、動的に実行されるか
ステートメントが入力ホスト変数を含んでいるかどうか
ステートメントに宣言済みグローバル一時表が含まれているかどうか

入力ホスト変数をもたない静的 SQL ステートメント

入力ホスト変数を含まない静的SQLステートメントの場合、 Db2計画またはパッケージをバインドするときにアクセスパスを決定します。この組み合わせは、プログラム実行時にはすでにアクセス・パスが決定しているので、最適なパフォーマンスが得られます。

入力ホスト変数をもつ静的 SQL ステートメント

入力ホスト変数を持つ静的SQLステートメントの場合、アクセスパスを Db2決定する時間は、指定したREOPTバインドオプションREOPT（NONE）またはREOPT（ALWAYS）によって異なります。 REOPT(NONE) がデフォルトです。 REOPT(AUTO) または REOPT(ONCE) を指定しないでください。これらのオプションは、動的ステートメントにのみ適用可能です。 Db2 静的SQL文については、REOPT(ONCE)とREOPT(AUTO)を無視します。なぜなら、は動的SQL文のみをキャッシュするからです。 Db2

REOPT（NONE）を指定すると、Db2入力変数がない場合と同様に、バインド時にアクセスパスを決定します。

REOPT（ALWAYS）を指定すると、Db2次のタイプの入力変数の値を使って、バインド時と実行時にアクセスパスを決定します。

ホスト変数
パラメーター・マーカー
特殊レジスター

Db2 実行時にステートメントのアクセスパスを決定するために余分な時間をかからなければなりません。しかし、 Db2 が変数値を使用して大幅に改善されたアクセス経路を決定した場合、全体的なパフォーマンスの改善が見られるかもしれません。 REOPT(ALWAYS)により、 Db2 は既知のリテラル値を使用してステートメントを最適化します。文字通りの値を知ることで、 Db2 は、列に偏ったデータが含まれている場合に、より効率的なアクセス経路を選択できるようになります。 Db2 パーティショニングされたテーブルスペースの制限キーにホスト変数を使用した検索条件がある場合、どのパーティションが条件を満たすかを認識することもできます。

Db2 REOPT（ALWAYS）を使うと、最適化を最初からやり直すことはありません。たとえば、Db2リテラル値に基づいてクエリ変換を実行しません。その結果、REOPT(ALWAYS) で最適化されたホスト変数を使用する静的 SQL ステートメントと、明示的なリテラル値を使用する類似した SQL ステートメントとで、異なるアクセス・パスが選択されることがあります。

動的 SQL ステートメント

動的SQLステートメントの場合、Db2ステートメントの準備時に実行時にアクセスパスを決定します。動的ステートメントを繰り返し準備するコストにより、静的 SQL ステートメントよりもパフォーマンスが低下することがあります。しかし、同じ SQL ステートメントを頻繁に実行する場合には、動的ステートメント・キャッシュを使用して、それらの動的ステートメントを準備する回数を減らすことができます。

入力ホスト変数をもつ動的 SQL ステートメント

入力ホスト変数を指定した動的 SQL ステートメントを含むアプリケーションのバインド時には、REOPT(NONE) オプションの代わりに、REOPT(ALWAYS) バインド・オプション、REOPT(ONCE) バインド・オプション、または REOPT(AUTO) バインド・オプションを使用することを検討してください。

動的ステートメント・キャッシュを使用しないときは、REOPT(ALWAYS) を使用します。 Db2 ステートメントの各EXECUTEまたはOPEN時に、ステートメントのアクセスパスを決定します。このオプションによってステートメントの最適なアクセス・パスが確保されますが、REOPT(ALWAYS) を使用することで動的 SQL ステートメントを頻繁に使用するためのリソース使用量が増加する可能性があります。

したがって、REOPT(ALWAYS) オプションは、大ボリュームで 1 秒未満の照会のための良い選択肢ではありません。大ボリュームで高速に実行される照会では、準備を繰り返し実行するコストのほうが、ステートメントを実行するコストを上回ることがあります。 REOPT（ALWAYS）オプションで処理されるステートメントは、動的ステートメントキャッシュが有効になっている場合でも、 Db2 REOPT（ALWAYS）が指定されているとアクセスパスを再利用できないため、動的ステートメントキャッシュから除外されます。

動的ステートメント・キャッシュを使用するときは、REOPT(ONCE) または REOPT(AUTO) を使用します。

REOPT(ONCE) を指定すると、 Db2 は、ステートメントの最初の EXECUTE または OPEN 時にのみステートメントのアクセス・パスを決定します。 DB2 は、そのアクセス・パスを動的ステートメント・キャッシュに保管して、ステートメントが無効になるか、キャッシュから除去されるまで使用します。このアクセス・パスの再利用によって、入力ホスト変数を含む、使用頻度の高い動的 SQL ステートメントの準備コストが減少します。ただし、動的ステートメントのパラメーター・マーカー値の変更は考慮されません。
REOPT(ONCE) オプションは、 SPUFI、 DSNTEP2、 DSNTEP4、 DSNTIAULおよび QMF Db2 などの随時クエリアプリケーションに最適です。これは、デフォルトのフィルター係数見積もりを使うのではなく、 CURRENT DATE や CURRENT TIMESTAMP などの特殊レジスターのリテラル値のステートメントを最適化できるからです。
REOPT（AUTO）を指定すると、 Db2実行時にアクセスパスを決定します。パラメータマーカーを使用してステートメントを実行するたびに、Db2新しいアクセスパスによってパフォーマンスが向上する可能性が高いと判断された場合は、新しいアクセスパスが生成されます。

効率的な最適化のための PREPARE ステートメントのコーディング

オーバーヘッドを最小限に抑えるには、PREPARE ステートメントをコーディングする必要があります。 REOPT(AUTO)、 REOPT(ALWAYS)、および REOPT(ONCE) を使うと、 Db2 は、ステートメントに対する OPEN または EXECUTE を処理するときと同時に SQL ステートメントを準備します。つまり、Db2 は、DEFER(PREPARE)を指定した場合と同じようにステートメントを処理します。ただし、次の状況では、Db2ステートメントを2回準備します。

プログラムが、OPEN ステートメントの前に DESCRIBE ステートメントを発行する
INTO パラメーター指定の PREPARE ステートメントを発行する

最初の準備では、Db2 は入力変数値を使用せずにアクセス・パスを決定します。次の準備では、Db2 は入力変数値を使用してアクセス・パスを決定します。この追加の準備によって、パフォーマンスが低下する可能性があります。

REOPT(ALWAYS)を指定すると、Db2 は、実行されるたびにステートメントを2回ほど準備します。

REOPT(ONCE) を指定すると、 Db2 は、ステートメントがキャッシュに保管されていない場合にのみ、ステートメントを 2 回準備します。ステートメントが準備され、キャッシュに保管された場合、 Db2 は、ステートメントの保管バージョンを使ってDESCRIBE ステートメントを完了します。

REOPT(AUTO) を指定すると、Db2 は最初は、入力変数値を使用せずにステートメントを準備します。ステートメントがキャッシュに保管された後、後続のOPEN または EXECUTEの場合、 Db2 は、入力変数の値に従って、新規アクセス・パスが必要かどうかを決定します。

カーソルを使用するステートメントに対しては、プログラム内で OPEN の後に DESCRIBE ステートメントを置くことで、2 度の準備を避けることができます。

予測管理を使用し、かつ REOPT(ALWAYS) か REOPT(ONCE) のいずれかを指定してバインドした動的 SQL ステートメントが予測管理の警告しきい値を超えると、アプリケーションは警告の SQLCODE を受け取りません。しかし、OPEN ステートメントまたは EXECUTE ステートメントからのエラー SQLCODE は受け取ります。