ストアド・プロシージャによるインデックス使用状況の調査 (V5R4 以降)

以前、「テーブル使用情報の取得 (V5R4 以降)」でシステムテーブルへの問い合わせによるインデックスの使用状況の調査方法を紹介したことがありました。

今回は、「Analyze Database Index Usage with SYSPARTITIONINDEXSTAT」という記事にストアド・プロシージャによる同様の情報の取得方法が載っていたので、ちょっと実行してみたその結果の紹介です。


作成

V5R4 のマシンで、以下のように記事のサンプルコードのコピー&ペーストで実行してみました。
(当り前ですが)問題なく作成され、問題なく実行できます。

↓が CREATE PROCEDURE で作成した時の画面です。

実行例

実行結果です。

仕様として、あるライブラリー内のすべてのテーブルについてのインデックスを対象に、過去何ヶ月かにわたって使用されていないもの、のリストを出すようになっています。

ですので、最初の引数には、調査対象のテーブルが入っているライブラリー名を指定し、次の引数には、月数を指定するようになっています。

たとえば、QEOL に入っているテーブルについているインデックスについて、この 1月間使用されていないもののリストを出しているのが↓の実行例です。

同じく QEOL に入っているテーブルについているインデックスについて、使用されていない期間を 6ヵ月にしてみましょう。

↑のリストから 2008/9 に使用されているものが消えているのが確認できますね。

コンパイルオプションの設定

↑のように System i ナビゲータの「SQL スクリプトの実行」から CREATE PROCEDURE 文でストアド・プロシージャを作成する場合でのコンパイルオプションの指定には SET OPTION を使用します。

たとえば、コンパイルリストを出したい、デバッグ時にステートメントを見ることができるようにしたい、と言った場合には、↓のように、SET OPTION ステートメントを追加して実行することで可能になります。

CREATE PROCEDURE Unused_Indexes 
       (IN ischema VARCHAR(10), 
        IN imonths_notused INT) 

LANGUAGE SQL 
DYNAMIC RESULT SETS 1 
SET OPTION OUTPUT = *PRINT, DBGVIEW = *STMT 

BEGIN 

DECLARE ixlist CURSOR WITH RETURN FOR 

WITH candidate_list as ( 
  SELECT 
    CURRENT DATE - DATE(last_query_use) AS months_lastqry, 
    CURRENT DATE - DATE(last_statistics_use) AS months_laststats, 
    CURRENT DATE - DATE(last_used_timestamp) AS months_lastused, 
    index_schema, index_name, 
         index_type, 
    last_query_use, 
        last_statistics_use, 
    last_used_timestamp, 
    query_use_count, 
    query_statistics_count, 
    days_used_count 

     FROM qsys2.syspartitionindexstat 

     WHERE system_table_schema=ischema   
                    AND unique > 1 ) 

SELECT 
    index_schema, index_name, 
        index_type, 
    last_query_use, 
    last_statistics_use, 
    last_used_timestamp, 
    query_use_count, 
    query_statistics_count, 
    days_used_count 

FROM candidate_list 

WHERE (months_lastqry > imonths_notused*100 OR months_lastqry IS NULL) 
 AND  (months_laststats > imonths_notused*100 OR months_laststats IS NULL) 
 AND  (months_lastused > imonths_notused*100 OR months_lastused IS NULL) 

ORDER BY MIN( coalesce(months_lastqry,99999), 
              coalesce(months_laststats,99999), 
              coalesce(months_lastused,99999)) DESC; 

OPEN ixlist; 

END; 

実行結果を見てみましょう。

SQL ストアド・プロシージャは C の埋め込み SQL プログラムとして展開され、コンパイルされるのですが、↑の実行結果として 2つのコンパイルリストが出力されるようになっています。

CRTSQLCI のコンパイルリスト

CRTCMOD のコンパイルリスト

[Top Pageに戻る]

Ads by TOK2