テーブルについているインデックスのリスト (V5R2 以降)

こちらで iSeries ナビゲーターを使用した、あるテーブルに対してのインデックスの一覧を出すやり方を紹介していますが、
V5R3 の iSeries アクセスに依存した機能なので、それがない場合は利用することができません。
つまり、V5R3 の iSeries アクセスが手元にない、とか、サーバーが V5R2 の場合は SI16313 を諸般の事情で適用できない、といったような場合です。

今回は、SQL ストアド・プロシージャを使った同様の機能を見てみようと思います。
SQL のみを使ったプロシージャですので、V5R2 以降であれば特に問題なく実行できると思います。V5R2 では新しい SQE というデータベースエンジンが導入され、そのコスト・ベースのオプティマイザーにとって、どういうインデックスがどのテーブルにどのカラムについて存在しているかという情報は致命的に重要です。

JDBC などを使ったプログラム等や V5R2 のクライアント・アクセスでも、あるテーブルについてのインデックスのリストを一覧することができます。
IBM ロチェスターの人によって作られたサンプルで、以下のリンクに載っています。まあ、あくまでサンプルですし、サポートがどうこうということはありません。オープンソースみたいなものですね。というかまさしくそうかも。

DB2 UDB for iSeries: SQL Procedure Example - Index Listing

さすがに iSeries ナビゲーターのように "最終使用日付" といったようなものは出てきません。あくまで、どんなインデックスがどのテーブルについて定義されているか、を確認できるだけです。

こういったサンプルのコーディング例が http://www-1.ibm.com/servers/eserver/iseries/db2/db2code.html に載っています。
いろいろ参考にしてみてください。


作成方法はいたって簡単で、上記リンクからコピー & ペーストし、必要であれば何らかの修正 (たとえば QGPL ライブラリーに作成されるようになっているので別のライブラリーを指定するとか、"."でわかれているが実行環境によっては"/"にするとか) をして実行するだけです。
長いプログラムなので、5250 の STRSQL による対話型 SQL 実行画面では一度にコピーしきれません。iSeries ナビゲーターの「SQL スクリプトの実行」がやりやすいでしょう。

以下が「SQL スクリプトの実行」での実行結果です。

ライブラリーとテーブルを指定して実行してみましょう。

このテーブルについて、あらかじめ CUST_IDX_01 というインデックスを作成してあったので、それがリストされているのが確認できます。

SQL ストアド・プロシージャの本文ですが、リンクしているサイトがなくなってしまっては困りますし、とりあえずコピー & ペースト用に転載しておきます。

CREATE PROCEDURE qgpl.indexlist(TableName VARCHAR(128), SchemaName VARCHAR(128) ) 
LANGUAGE SQL
RESULT SET 1
BEGIN

DECLARE ixTable VARCHAR(128);
DECLARE ixTableSchema VARCHAR(128);

DECLARE c1 CURSOR WITH RETURN FOR 
WITH SQL_Indices 
(index_name,  index_schema, is_unique, system_index_name, system_index_schema)
  AS ( SELECT index_name,  index_schema, is_unique, system_index_name, 
  system_index_schema       
    FROM qsys2.sysindexes
      WHERE table_name = ixTable   AND table_schema = ixTableSchema ),
Const_Indices (index_name, index_schema, is_unique) 
  AS ( SELECT constraint_name, constraint_schema, 
    CASE WHEN constraint_type = 'FOREIGN KEY' THEN 'D'  ELSE 'U' END  
      FROM qsys2.syscst 
        WHERE table_name = ixTable AND table_schema = ixTableSchema 
                and constraint_type<>'CHECK'),
KeyedLF_Indices (index_name, index_schema, is_unique) 
  AS ( SELECT dbxfil, dbxlib, dbxunq 
    FROM qsys.qadbxref, qsys.qadbfdep 
      WHERE dbxatr='LF' AND dbxnkf>0 AND dbffdp=dbxfil AND dbfldp=dbxlib 
          AND dbffil=ixTable 
      AND dbflib=ixTableSchema),
KeyedPF_Index (index_name, index_schema, is_unique,dbxfil) 
AS (SELECT dbxlfi,dbxlib,dbxunq,dbxfil 
  FROM qsys.qadbxref 
    WHERE dbxatr='PF'  AND dbxnkf>0  AND dbxlib=ixTableSchema AND dbxlfi=ixTable
    AND NOT EXISTS ( SELECT 1 FROM qsys.qadbfcst 
    WHERE dbxlib=dbccfl AND dbxfil=dbccff AND dbccty='PRIMARY KEY')  )
/* -----------------------------------------------------------------------------*/
/* SQL Indices */
/*------------------------------------------------------------------------------*/
SELECT                                            
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(SQL_Indices.index_name, 1, 30)  
    ELSE ' '
      END as INDEX_NAME,                                       
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(SQL_Indices.index_schema, 1, 10)
    ELSE ' '
      END as INDEX_SCHEMA,                                       
      is_unique,                     
      SUBSTRING(column_name, 1, 30) as KEY_COLUMN,             
      ordinal_position,                          
      ordering   ,               
      SQL_Indices.system_index_name,                       
      SQL_Indices.system_index_schema                    
    FROM  SQL_Indices,   qsys2.syskeys B                             
    WHERE SQL_Indices.system_index_name = B.system_index_name  
      AND SQL_Indices.system_index_schema = B.system_index_schema
UNION
/* -----------------------------------------------------------------------------*/
/* Constraint Indices */
/*------------------------------------------------------------------------------*/
SELECT                                            
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(Const_Indices.index_name, 1, 30)  
    WHEN ordinal_position > 1 THEN ' '
    END,                                       
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(Const_Indices.index_schema, 1, 10)
    WHEN ordinal_position > 1 THEN ' '
    END,                                       
    is_unique,                     
    SUBSTRING(column_name, 1, 30),             
    ordinal_position,                          
    'A' , 
    index_name,
    index_schema
  FROM  Const_Indices,   qsys2.syskeycst B                             
  WHERE Const_Indices.index_name = B.constraint_name
    AND Const_Indices.index_schema = B.constraint_schema
UNION
/* -----------------------------------------------------------------------------*/
/* Keyed LF Indices */
/*------------------------------------------------------------------------------*/
  SELECT                                            
    CASE                                       
      WHEN dbkpos = 1              
        THEN SUBSTRING(KeyedLF_Indices.index_name, 1, 10)  
      ELSE ' '
      END,                                       
    CASE                                       
      WHEN dbkpos = 1              
        THEN SUBSTRING(KeyedLF_Indices.index_schema, 1, 10)
      ELSE  ' '
      END,                                       
      is_unique,                      
      dbkfld,
      dbkpos,
      dbkord,                         
      index_name,
      index_schema
    FROM  KeyedLF_Indices,   qsys.qadbkfld B        
    WHERE KeyedLF_Indices.index_name = dbkfil   
      AND KeyedLF_Indices.index_schema = dbklib 
    UNION
/* -----------------------------------------------------------------------------*/
/* Keyed PF Indices */
/*------------------------------------------------------------------------------*/
SELECT                                            
  CASE                                       
    WHEN dbkpos = 1              
      THEN SUBSTRING(KeyedPF_Index.index_name, 1, 10)  
    ELSE ' '
    END,                                       
  CASE                                       
    WHEN dbkpos = 1              
      THEN SUBSTRING(KeyedPF_Index.index_schema, 1, 10)
    ELSE  ' '
    END,                                       
    is_unique,                     
    dbkfld, 
    dbkpos,
    dbkord,                         
    index_name,
    index_schema
  FROM  KeyedPF_Index,   qsys.qadbkfld B        
  WHERE KeyedPF_Index.index_name = dbkfil   
    AND KeyedPF_Index.index_schema = dbklib 
  ORDER BY 7,8,5 FOR READ ONLY;

SELECT table_name, table_schema INTO ixTable, ixTableSchema 
  FROM qsys2.systables 
  WHERE   (table_name=UPPER(TableName) and table_schema=UPPER(SchemaName)) OR
     ( system_table_name=UPPER(TableName) 
         and system_table_schema=UPPER(SchemaName));

/* Open the cursor so the index information can be returned as a result set */
OPEN c1;

END 

[Top Pageに戻る]

Ads by TOK2