db2數據庫常用調試檢測數據庫語句

db2數據庫常用調試檢測數據庫語句

connect to [數據庫名] user [操作用戶名] using [密碼]

連接到數據庫指定數據庫，不包含中括號

reorgchk on table EAS.T_User

reorgchk update statistics on table all

select tabname, colcount, status FROM syscat.tables WHERE tabschema NOT LIKE 'SYS%' ORDER BY tabname

狀態是否正常 狀態列 N = 正常；C = 待審核

get snapshot for bufferpools on 數據庫名

檢測快照緩沖池命中率>95%？

get snapshot for Locks on EASdb

檢測快照鎖

get snapshot for Locks on EASdb
get snapshot for all on EASdb

檢測快照鎖

select index_hit_ratio_percent from sysibmadm.bp_hitratio

緩沖池命中率

select agent_id,rows_selected,rows_read from sysibmadm.application_performance

查看系統性能

select elapsed_time_min,appl_status,agent_id from sysibmadm.long_running_sql order by elapsed_time_min desc fetch first 5 rows only

查看運行時間最長的應用

select * from sysibmadm.top_dynamic_sql order by num_executions desc

查看運行次數最多的SQL

select tabschema, tabname from syscat.tables where stats_time is null

查看沒有統計信息的表

select tabname from syscat.indexes where stats_time is null

查看沒有統計信息的索引

reorg table EAS.T_City

重組單表

reorg indexes all for table 表名

只重組索引

runstats on table EAS.t_group and detailed indexes all

重新統計表和索引

list applications show detail

顯示所用應用情況

list tablespaces show detail

顯示所用表空間情況

create unique index t_groupIdCODEname on eas.t_group (G_ID asc,G_CODE,G_NAME) allow reverse scans

創建可以反向掃描的唯一索引，在需要排序時

create index t_groupIdCODEname on eas.t_group (G_ID asc) include(G_CODE,G_NAME)

創建include的索引

create table eas.t1(c1 int,c2 double,c3 double generated always as c1+c2,c4 generated always as( case when c1>c2 then 1 else null end))

創建具有計算列的表

一般再有大量的刪除，修改，增加時需要reorg table，在進行runstats on table