DB2数据库开发规范草稿 V1.3.doc

DB2 数据库开发规范 上海银行 信息技术部 第1 页 共 7 页 上海银行 Bank of Shanghai D DB B2 2 数数据据库库开开发发规规范范 DB2 数据库开发规范 上海银行 信息技术部 第2 页 共 7 页 版本号版本日期修订者描述备注 1 02014 12 4王成初稿 1 12014 12 8王成补充WAS调整内容 1 22014 12 11王成补充Load工具使用说明 补充临时表使用的说明 1 32015 3 27王成补充load对HADR影响 的说明 DB2 数据库开发规范 上海银行 信息技术部 第3 页 共 7 页 目录 目录 第一章第一章表表 4 1 建表 4 第二章第二章大数据量清理和装载大数据量清理和装载 4 2 数据清理 4 3 数据装载 4 第三章第三章临时表临时表 5 第四章第四章数据库连接数据库连接 5 第五章第五章性能性能 5 4 统计更新 5 5 索引的使用 6 6 锁和脏读 6 DB2 数据库开发规范 上海银行 信息技术部 第4 页 共 7 页 第一章第一章表表 1 1 建表建表 对于新建的每个表 都需要把 public 用户的权限去除 因此创建每个新表的 sql 语句后 需 要添加如下语句 revoke all on tablename from public 同一数据库中的表应该由同一个用户来创建 减少用户复杂性 数据库中的表应该建在所分配的 tablespace 禁止建到未经允许的他人的 tablespace 上 第二章第二章大数据量清理和装载大数据量清理和装载 2 2 数据清理数据清理 要删除大表中的大部分数据 则不能直接 delete 因为会产生长事务及锁资源溢出的问题 可以根据表的使用情况区分处理 1 对于可以停断的应用系统 可以新建不带日志的表 把 需要保留的数据插入新表 然后通过表换名的方法实现 2 对于不能停断的应用系统 则要 分批提交以降低事物长度 比如每删除 10 万行就提交 以免造成长事物而回滚 如果全部删除表中的所有数据 使用 truncate 不要使用 delete 3 3 数据装载数据装载 关于 import import 默认是 allow no access 即在表上会有独占锁 会导致其他人不能访问 如需 同时访问此表 设置为 allow write access 如下列会锁表 db2 import from test del of del insert into test 建议改为下列方式 db2 import from test del of del insert into test allow write access 关于 Load 默认 load 也是 allow no access 即在表上会有独占锁 会导致其他人不能访问 如需 同时访问此表 load 之前的数据 设置为 allow read access Load 默认使用 copy no 会导致所属表空间状态为 backup pending 即同一表空间上的 DB2 数据库开发规范 上海银行 信息技术部 第5 页 共 7 页 所有表都只能查询 不能写 切记 load 时一定要加 nonrecoverable 参数或者 copy yes 参数 建议使用 nonrecoverable 测试环境数据库不开归档日志模式 Load 不会导致 backup pending 但是生产环境是开归档的 不带 copy yes 或者 norecoverable 会造成 backup pending Nonrecoverable 这种方式不记数据库日志 导入速度快 完成后要对数据库做全备 否 则数据恢复时此表不能恢复出来 因为不记归档日志 或者需要通过重建受影响的表 语 法如下 db2 load from xxx del of del insert into shcema tablename nonrecoverable 对于使用了 HADR 数据库同步技术的系统 目前只有信用卡核心系统 后续还会有互联网 核心和 Emao 要使用 copy yes 否则会造成 HADR 损坏 Copy yes 会在 load 结束后对表 空间做一次全备 备份出来的文件不要删除 可以用于 HADR 追加或者增量恢复 并且 load 之前把 HADR 关闭 load 完成之后 把 load 期间产生的备份数据复制到 HADR 同样的目录 最后 HADR 并且验证 但是由于要对整个表空间进行备份 时间会较长 语法如下 db2 load from xxx del of del insert into shcema tablename copy yes to data 鉴于 Load 所带来的这么多问题 安全起见 建议不要再使用 load 可以改用 import 之 类的 即使要用 一定要提前告知数据库管理员 以确定对数据库的影响 在进行过数据装载后一定要对表进行统计更新 以保持系统表信息和实际表信息的同步 第三章第三章临时表临时表 创建临时表之后 及时提交 以免对系统表长时间锁定 第四章第四章数据库连接数据库连接 应用应尽量实现重连机制 以免数据库重启或者切换后必须重启应用 对数据库的连接数应该设置合理 以免过多占用数据库服务器内存资源 第五章第五章性能性能 4 4 统计更新统计更新 重要系统应用自行收集统计信息 没有统计信息 即使有索引可能也不会使用 特殊表的注意事项 如果数据库中存在某些特殊的表 如早上有大量数据 晚上就清空的表 对于这种周期性伸缩的表 应该在表满的时候做收集统计信息 DB2 数据库开发规范 上海银行 信息技术部 第6 页 共 7 页 运行 runstats 之后 要发出 commit 以释放锁 并且需要重新 bind 静态语句和存储过程 以使用新的统计信息 绑定时 逐个绑定 可以通过下列查询确认表是否被统计过 db2 select name stats time from syscat systables where name 或者通过下列查询是否存在未统计的表 db2 select name stats time from syscat systables where stats time is null Runstats 默认为 allow write access 不会锁表 5 5 索引的使用索引的使用 对于经常访问的表中字段 并且其唯一性较强 即取值比较分散 建议对其建立索引 少建唯一性差的索引 索引太多会影响优化器选择 多用 join 连接 少用子查询 使用 join 时 join 字段上尽量有索引 使用 matches like 匹配时要从字段的第一位开始匹配 否则不会用到索引 在查询条件中如果对字段使用函数 如 trim 等 不会使用索引 对于多字段的联合索引 只有当 SQL 语句中 where 过滤条件用到相应的字段 出现在联合索 引的最前面几个字段 才会使用索引 如建立联合字段索引 a b c d where 字段 b 1 and a 1 之类会使用 而 where 字段 b 1 and c 1 之类就不会使用 因为没有包含 a 字段 新建索引之后 要对表收集统计信息 db2 runstats on table and detailed indexes all 并对静态语句 主要是嵌入 C 程序 进行绑定 静态语句索引才会被使用 动态语句不需要 重新绑定 6 6 锁和脏读锁和脏读 对于分析型 我的内容 SQL 建议查询设置隔离级别为脏读 select from tab with UR 这样不会产生因为所查询记录被锁而导致的查询失败 对于查询 修改的记录 要通过索引访问方式修改 如果通过全表扫描 由于对记录不确定 则会所有记录上锁 会增大被堵塞的概率 降低事务长度 及时提交 缩短持有锁的时间 降低锁冲突 DB2 客户端的级别会覆盖 DB2 server 的默认级别 所以要注意所使用客户端的隔离级别设置 对于 WAS 中间件 默认级别为 RS 要降低隔离级别为