IBM Support

[Db2] 一時表を使用している SQL やコマンドを特定する方法 (V10.5以降)

Question & Answer


Question

一時表を大量に使用している SQL やコマンドを特定する良い方法はありますか。
以下を参照していましたが、非推奨の機能が利用されているため、それらの機能を利用しない方法を確認したい。

Answer

MON_GET_TABLE 表関数、MON_GET_CONNECTION 表関数、MON_CURRENT_SQL 管理ビュー、MON_GET_UTILITIES 表関数 を利用して、一時表を大量に使用している実行中の SQL およびコマンドを一覧できます。
下記サンプル SQL を参考にしてください。
>cat mon_tmptbl.sql105
SELECT
T3.*,
T3.TOTAL_DATA_PAGES+T3.TOTAL_LOB_PAGES+T3.TOTAL_LONG_PAGES+T3.TOTAL_INDEX_PAGES+T3.TOTAL_XDA_PAGES+T3.TOTAL_COL_PAGES  TOTAL_TEMP_PAGES,
SUBSTR(T6.STMT_TEXT,1,256) SUB_STMT_TEXT
FROM
(
SELECT
T1.APPLICATION_HANDLE,
T2.TBSP_ID,
T2.MEMBER,
SUM(coalesce(T2.DATA_OBJECT_L_PAGES,0)) TOTAL_DATA_PAGES,
SUM(coalesce(T2.LOB_OBJECT_L_PAGES,0)) TOTAL_LOB_PAGES,
SUM(coalesce(T2.LONG_OBJECT_L_PAGES,0)) TOTAL_LONG_PAGES,
SUM(coalesce(T2.INDEX_OBJECT_L_PAGES,0)) TOTAL_INDEX_PAGES,
SUM(coalesce(T2.XDA_OBJECT_L_PAGES,0)) TOTAL_XDA_PAGES,
SUM(coalesce(T2.COL_OBJECT_L_PAGES,0)) TOTAL_COL_PAGES
FROM
TABLE(MON_GET_CONNECTION(NULL,-2)) T1,
TABLE(MON_GET_TABLE(NULL,NULL,-2)) T2
WHERE
SUBSTR(T2.TABSCHEMA,1,INSTR(T2.TABSCHEMA,'><')) = '<' || T1.APPLICATION_HANDLE || '>'
GROUP BY
T1.APPLICATION_HANDLE, T2.TBSP_ID, T2.MEMBER
ORDER BY
T1.APPLICATION_HANDLE, T2.TBSP_ID, T2.MEMBER
) T3
JOIN
(
(
SELECT
APPLICATION_HANDLE,
STMT_TEXT
FROM
SYSIBMADM.MON_CURRENT_SQL T4
WHERE
ACTIVITY_TYPE not in ('LOAD')
)
UNION ALL
(
SELECT
APPLICATION_HANDLE,
UTILITY_DETAIL --STMT_TEXT
FROM
TABLE(MON_GET_UTILITY(-2)) T5
WHERE
UTILITY_TYPE in ('LOAD', 'REORG') AND
COORD_MEMBER = MEMBER
)
) T6
ON
T3.APPLICATION_HANDLE = T6.APPLICATION_HANDLE ;
■ 出力例 (1)
$ db2 -tf mon_tmptbl.sql105
APPLICATION_HANDLE   TBSP_ID              MEMBER TOTAL_DATA_PAGES     TOTAL_LOB_PAGES      TOTAL_LONG_PAGES     TOTAL_INDEX_PAGES    TOTAL_XDA_PAGES      TOTAL_COL_PAGES      TOTAL_TEMP_PAGES     SUB_STMT_TEXT                                                                                                                                                                                                                                                   
-------------------- -------------------- ------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  10                    1      0                13686                    0                    0                    0                    0                    0                13686 select t1.id, t1.name, t1.job, t1.dept from staff0 t1, staff1 t2 where t1.id = t2.id group by t1.id, t1.name, t1.job, t1.dept

                  24                    1      0                18392                    0                    0                    0                    0                    0                18392 REORG TABLE "AESEB15 "."STAFF2" ALLOW READ ACCESS                                                                                                                                                                                                               
                  25                    1      0                12498                    0                    0                    0                    0                    0                12498 [LOADID: 88.2020-08-20-23.08.03.589309.0 (2;22)] [*LOCAL.aeseb15.200820032155] OFFLINE LOAD DEL AUTOMATIC INDEXING INSERT COPY YES AESEB15 .STAFF3                                                                                                              
                 348                    1      0                    1                   32                    0                    0                    0                    0                   33 SELECT T3.*, T3.TOTAL_DATA_PAGES+T3.TOTAL_LOB_PAGES+T3.TOTAL_LONG_PAGES+T3.TOTAL_INDEX_PAGES+T3.TOTAL_XDA_PAGES+T3.TOTAL_COL_PAGES  TOTAL_TEMP_PAGES, SUBSTR(T6.STMT_TEXT,1,256) SUB_STMT_TEXT FROM ( SELECT T1.APPLICATION_HANDLE, T2.TBSP_ID, T2.MEMBER, SUM(c
  4 レコードが選択されました。
上記 SQL では、一時表のサイズを APPLICATION_HANDLE、MEMBER、および、オブジェクト種類 (DATA, LOB, LONG, INDEX, COL) ごとにサイズを合計し、さらに、それらを合計して、APPLICATION_HANDLE、MEMBER ごとの合計サイズを TOTAL_TEMP_PAGES として出力しています。
また、STMT_TEXT は見やすくするため、 出力を 256 文字に制限したものを SUB_STMT_TEXT として出力しています。
なお、上記出力には、モニター用 SQL 自体も含まれています。
■ 出力例 (2)
複数データベース・パーティション構成の場合には、一時表が使用されたパーティションごとに使用量を確認できます。
以下は、4 パーティション構成の例です。
$ db2 -tf mon_tmptbl.sql105
APPLICATION_HANDLE   TBSP_ID              MEMBER TOTAL_DATA_PAGES     TOTAL_LOB_PAGES      TOTAL_LONG_PAGES     TOTAL_INDEX_PAGES    TOTAL_XDA_PAGES      TOTAL_COL_PAGES      TOTAL_TEMP_PAGES     SUB_STMT_TEXT                                                                                                                                                                                                                                                   
-------------------- -------------------- ------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  72                    1      0                13784                    0                    0                    0                    0                    0                13784 select t1.id, t1.name, t1.job, t1.dept from staff0 t1, staff1 t2 where t1.id = t2.id group by t1.id, t1.name, t1.job, t1.dept

                  72                    1      1                13800                    0                    0                    0                    0                    0                13800 select t1.id, t1.name, t1.job, t1.dept from staff0 t1, staff1 t2 where t1.id = t2.id group by t1.id, t1.name, t1.job, t1.dept

                  72                    1      2                13808                    0                    0                    0                    0                    0                13808 select t1.id, t1.name, t1.job, t1.dept from staff0 t1, staff1 t2 where t1.id = t2.id group by t1.id, t1.name, t1.job, t1.dept

                  72                    1      3                13792                    0                    0                    0                    0                    0                13792 select t1.id, t1.name, t1.job, t1.dept from staff0 t1, staff1 t2 where t1.id = t2.id group by t1.id, t1.name, t1.job, t1.dept

                  92                    1      0                    4                  128                    0                    0                    0                    0                  132 SELECT T3.*, T3.TOTAL_DATA_PAGES+T3.TOTAL_LOB_PAGES+T3.TOTAL_LONG_PAGES+T3.TOTAL_INDEX_PAGES+T3.TOTAL_XDA_PAGES+T3.TOTAL_COL_PAGES  TOTAL_TEMP_PAGES, SUBSTR(T6.STMT_TEXT,1,256) SUB_STMT_TEXT FROM ( SELECT T1.APPLICATION_HANDLE, T2.TBSP_ID, T2.MEMBER, SUM(c
                 117                    1      0                 6096                    0                    0                    0                    0                    0                 6096 [LOADID: 1373.2020-08-20-22.23.46.499844.0 (2;22)] [*N0.dpfb510.200820132323] OFFLINE LOAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE DPFB510 .STAFF3                                                                                                       
                 117                    1      1                 6236                    0                    0                    0                    0                    0                 6236 [LOADID: 1373.2020-08-20-22.23.46.499844.0 (2;22)] [*N0.dpfb510.200820132323] OFFLINE LOAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE DPFB510 .STAFF3                                                                                                       
                 117                    1      2                 6844                    0                    0                    0                    0                    0                 6844 [LOADID: 1373.2020-08-20-22.23.46.499844.0 (2;22)] [*N0.dpfb510.200820132323] OFFLINE LOAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE DPFB510 .STAFF3                                                                                                       
                 117                    1      3                 6660                    0                    0                    0                    0                    0                 6660 [LOADID: 1373.2020-08-20-22.23.46.499844.0 (2;22)] [*N0.dpfb510.200820132323] OFFLINE LOAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE DPFB510 .STAFF3                                                                                                       
               65614                    1      0                 3820                    0                    0                    0                    0                    0                 3820 REORG TABLE "DPFB510 "."STAFF2" ALLOW READ ACCESS                                                                                                                                                                                                               
               65614                    1      1                 3928                    0                    0                    0                    0                    0                 3928 REORG TABLE "DPFB510 "."STAFF2" ALLOW READ ACCESS                                                                                                                                                                                                               
               65614                    1      2                 1940                    0                    0                    0                    0                    0                 1940 REORG TABLE "DPFB510 "."STAFF2" ALLOW READ ACCESS                                                                                                                                                                                                               
               65614                    1      3                 1824                    0                    0                    0                    0                    0                 1824 REORG TABLE "DPFB510 "."STAFF2" ALLOW READ ACCESS                                                                                                                                                                                                               
  13 record(s) selected.

■ 解説

一時表のスキーマ名は、"<agent_id><auth_id>" の形式をとるため、これを元に一時表を使用するアプリケーションとの突合せを行い、表示しています。

  • agent_id は、一時表を作成しているアプリケーションのアプリケーション・ハンドルです。
  • auth_id は、 アプリケーションがデータベースに接続するときに使用する許可 ID です。
また一時表の名前は "TEMP (n, m)" の形式をとります。
  • n は表スペース ID です。
  • m は table_file_id モニター・エレメントです。
■ 運用上の考慮点
自動的に生成される一時表とは異なり、ユーザーが宣言した一時表 (※) の場合は、上記サンプル SQL では、アプリケーションおよび SQL を特定できません。
一時表を宣言する DECLARE GLOBAL TEMPORARY TABLE 文は、db2pd の DynamicCache (コマンド: db2pd -db <dbname> -dynamic) の出力から確認してください。また、DECLARE GLOBAL TEMPORARY TABLE 文で宣言された一時表に実際にデータを挿入してる SQL につきましては、同様に、db2pd の DynamicCache (コマンド: db2pd -db <dbname> -dynamic) の出力、あるいは、まだ実行中であれば、MON_CURRENT_SQL 管理ビューから確認してください。
※ ユーザーが宣言した一時表は、表スナップショットで表タイプが "一時表" あるいは "Temporary" のもの、または db2pd の TCB Table Information 出力で ObjClass が "Temp" の表で、表名が "TEMP (n, m)" の命名規則に従わないものです。

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlYAAU","label":"Database Objects-\u003ETables"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.5.0;11.1.0;11.5.0"}]

Document Information

Modified date:
23 August 2023

UID

ibm16319971

Page Feedback