db2数据库常用调试检测数据库语句
connect to [数据库名] user [操作用户名] using [密码]
连接到数据库指定数据库，不包含中括号
reorgchk on table EAS.T_User
检测是否需要重组表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