Troubleshooting
Problem
Why is a table not listed in the get snapshot output upon a select operation?
Symptom
Create a table:
C:\Program Files\IBM\SQLLIB\BIN>db2 "create table TAB1 (col1 int)"
DB20000I The SQL command completed successfully.
Verify the table monitor switch state:
1) C:\Program Files\IBM\SQLLIB\BIN>db2 "select table_sw_state from table (snap_get_switches(-1)) as T"
TABLE_SW_STATE
--------------
0
1 record(s) selected.
2) C:\Program Files\IBM\SQLLIB\BIN>db2 get dbm cfg | FIND "DFT_MON_TABLE"
Table (DFT_MON_TABLE) = OFF
3) Insert data into the table:
C:\Program Files\IBM\SQLLIB\BIN>db2 "insert into TAB1 values (1)"
4) Query the table:
C:\Program Files\IBM\SQLLIB\BIN>db2 "select * from TAB1"
COL1
-----------
1
1 record(s) selected.
Run the getsnapshot command (notice the table isn't listed):
C:\Program Files\IBM\SQLLIB\BIN>db2 get snapshot for tables on test1 |
FIND "Table Name" =====> Table TAB1 isn't listed here
Table Name = SYSTABLES
Table Name = HMON_ATM_INFO
Cause
TABLES ON database-alias
Provides information about tables in a specified database. This will include only those tables that have been accessed since the TABLE recording switch was turned ON.
get snapshot:
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001945.html
rows_read requires table switch turned on (select):
The number of rows read from the table.
| Table | table | Table |
Diagnosing The Problem
Check if table monitor switch is turned on using "update monitor switches" command or DBFT_MON_TABLE database manager configuration parameter.
You can use update monitor or update dbm cfg using "DFT_MON_TABLE" to
turn on table monitoring switch. update dbm cfg" will change the
default settings, anyone doing get snapshot will be affected by it.
"update monitor switches" will change the settings only for you. If
somebody else does a snapshot (and dft_mon_xxx was off) they wouldn't
be affected by your "update monitor switches". I used update monitor
switches in my example.
db2 "select table_sw_state from table (snap_get_switches(-1)) as T"
Table monitor switch can be turned on
db2 get dbm cfg | FIND "DFT_MON_TABLE"
Table (DFT_MON_TABLE) = OFF
Resolving The Problem
Table snapshot command will report the table for rows_read (select) only when the table switch is turned on.
Turn on the table monitor switch:
C:\Program Files\IBM\SQLLIB\BIN>db2 "update monitor switches using table on" DB20000I The UPDATE MONITOR SWITCHES command completed successfully.
Verify the table monitor switch state:
C:\Program Files\IBM\SQLLIB\BIN>db2 "select table_sw_state from table
(snap_get_switches(-1)) as T" =====> Switch state changed to 1
TABLE_SW_STATE
--------------
1
1 record(s) selected.
Issue the select query:
C:\Program Files\IBM\SQLLIB\BIN>db2 "select * from TAB1"
COL1
-----------
1
1 record(s) selected.
The table is now listed in the snapshot output:
C:\Program Files\IBM\SQLLIB\BIN>db2 get snapshot for tables on test1 | FIND "Table Name" =====> Table TAB1 is now listed
Table Name = SYSTABLES
Table Name = TAB1
Table Name = SYSPLAN
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21986519