DB2部分查询SQL

部分SQL */ --添加主键 alter TABLE TABLE_SCHEMA.TABLE_NAME add constraint PK_TABLE_NAME primary key(COL1 ,COL2); --收集统计信息runsats db2 "runstats on table tbschema.tbname with distribution on all columns and indexes all" /* 以下为数据库监控部分 */ --监控最耗费用户CPU的时间的动态SQL select STMT_TEXT from sysibmadm.snapdyn_sql order by total_usr_cpu_time desc fetch first 5 rows only --监控访问热点对象执行次数最多的SQL,<TableName>指热点表名 select STMT_TEXT,num_executions from sysibmadm.snapdyn_sql where stmt like ‘%<TableNameForHotIndex>%‘ order by num_executions desc fetch first 5 rows only --监控访问热点对象执行次数最多的SQL,<TableNameForHotIndex>指热点索引 select STMT_TEXT,num_executions from sysibmadm.snapdyn_sql where stmt like ‘%<TableNameForHotIndex>%‘ order by pool_index_p_reads desc fetch first 5 rows only --监控运行时间最长的SQL select * from sysibmadm.long_running_sql order by elapsed_time_min desc fetch first 1 rows only --监控运行次数最多的SQL select stmt_text,num_executions from sysibmadm.top_dynamic_sql order by num_executions desc fetch first 5 row only --监控排序次数最多的SQL select stmt_text,stmt_sorts from sysibmadm.top_dynamic_sql order by stmt_sorts desc fetch first 5 row only --日志写入速度 db2 "select (log_write_time_ns/1000000)/num_log_write_io as average_log_wirte_ms from sysibmadm.snapdb" --锁升级及死锁 db2 "select lock_escals,deadlocks from sysibmadm.snapdb" --获取所有管理视图 db2 list tables for schema SYSIBMADM --查看表结构 db2look -d test -e -nofed -t test.test1 | awk ‘/CREATE TABLE/,/;/{print}‘ --查看执行计划 db2 connect to test db2 "explain plan for select distinct contno from table_schema.table_name" db2exfmt -d test -1 -o /tmp/a.exfmt more /tmp/a.exfmt --修改表名 RENAME TABLE_SCHEMA.OLD_TABLENAME TO NEW_TABLENAME; --修改表字段 ALTER TABLE TABLE_SCHEMA.OLD_TABLENAME ALTER COLUMNS SET DATA TYPE VARCHAR(10); --修改字段名称【DB2不允许修改字段名称，但是可以删除后新增字段】 ALTER TABLE TABLE_SCHEMA.TABLE_NAME DROP COLUMNS; ALTER TABLE TABLE_SCHEMA.TABLE_NAME ADD COLUMNS VARCHAR(10);

参考资料：

DB2设计、管理与性能优化艺术 --王飞鹏 、 李玉明 、 朱志辉 、 王富国 等  著  胡伟民  校 

DB2部分查询SQL

标签：nstat   for   信息   运行时间   cpu   次数   sel   sed   awk