公司技术沙龙讲座--DB2经验分享.ppt

DB2技术经验分享;SQL优化篇;使用db2advis对SQL进行索引建议 语法：db2advis –h 1、使用之前需要创建辅助表: $DB2HOME/sqllib/MISC/EXPLAIN.DDL 2、案例1：已存在的索引 3、案例2：不存在的索引;CHAR和VARCHAR 数据保存方式： VARCHAR的页溢出(OVERFLOW)： ;常见的SQL编写、优化和SQL高级应用 1、合理使用约束也可以增强查询性能 2、尽量把set value改写成 values into 3、使用new table和old table减少对数据库操作次数 4、如果允许的话，可以使用syscat.tables.card代替count(*) 5、如果只有查询，尽量把存储过程改用自定义函数 6、获取DB2错误码消息(sqlerrm、db2 ? sqlcode);7、模拟死锁、锁超时 (合理使用with ur也可以提高并发性) 导致锁超时：Lock table | Update table | Delete table | Insert table 改善update\delete出现的锁超时：在where语句后面指定的条件记录是唯一的可以加上with ur改善锁超时 或者使用环境变量DB2_EVALUNCOMMITTED 改善insert出现的锁超时：使用DB2_SKIPINSERTED环境变量 导致死锁：tran1 ? lock A , tran2 ? lock B | tran2 ? lock A , tran1 ? lock B 改善死锁最根本的还是需要针对应用的以上这种情况进行调整 8、常用的会话级命令 语法： db2 ? update command options db2 ? set current a)设置当前会话不自动提交 b)设置当前锁等待时间(lock timeout [not wait | :number]) c)设置当前数据库停顿;9、使用merge对多张表进行合并 10、常用的OLAP(online analytical processing)在线分析函数 ROW_NUMBER RANK COUNT MIN AVG SUM ;制造测试数据 1、递归SQL语法和基本概念 递归 SQL 在 DB2 中通过公共表表达式 (CTE，Common Table Expression) 来实现。递归 SQL 由递归 CTE 以及对递归 CTE 结果的查询组成。那什么是递归 CTE 呢？简言之，如果 CTE 中的 FULLSELECT 在 FROM 子句中引用到 CTE 本身，就是递归 CTE。递归 CTE 包含以下三个组成部分： 初始查询 初始查询是 CTE 中对基本表进行查询的部分。CTE 定义中的第一个 FULLSELECT 必须不包含对 CTE 自身的应用，即必须是初始查询。 递归查询 递归查询就是通过对 CTE 自身的引用，从而启动递归逻辑的查询。递归查询需要遵循以下几个规则 ： 递归查询和初始查询结果必须包含相同数量的数据列?? 递归查询和初始查询结果数据列的、长度等必须一致； 递归查询不能包含 GROUP BY 或者 HAVING 子句； 递归查询不能包含 Outer Join； 递归查询不能包含子查询 (Subquery)； 递归查询必须用 UNION ALL 联结。 终止条件 终止条件通常是隐性的，即如果前一次递归查询返回的结果集为空，则终止递归；但是也可以在递归查询中设定终止条件，如限定递归查询的深度等。 ;a)使用递归制造N条数据 b)把递归SQL声明成游标，然后把生成的数据导入表中 c)把递归SQL生成的数据导出文件中;日常问题解决方法(DBA篇幅) 执行update\delete\insert SQL语句时事务日志已满问题解决 删除表字段不能对表进行访问的问题解决 对表增加generated always字段后出现表挂起状态解决 使用db2pd + snapshot排除锁超时原因 对出现锁超时的应用进行排查(DB2_CAPTURE_LOCKTIMEOUT锁超时报告) 对被引用的数据库对象无法重命名问题解决(V9.5或者更早版本) TOP SQL捕获 使用empty table快速清理一张大表的全部测试数据 使用import\load replace快速清理一张大表的全部测试数据 使用not logged快速清理一张大表的部分数据