最近给公司做了两期DB2技术培训的现场笔记

正在加载中...

试读已结束

继续阅读请 1 金币购买后下载

立即下载

资料简介:

1、
SQL优化

a)
使用db2explndb2advis查看SQL执行计划索引建议


i.
查看SQL的执行计划关键指标(tbscan,ixscan, nljoin,msjoin ,hsjoin)

基本使用:db2expln -d afa-user afa afa -q "select * from t_config " -g -o output.log

嵌套循环连接:select * fromafa.t1 join afa.t2 on 1=1

合并连接:select * from t1join t2 on t1.id=t2.id and t1.id < 100 order by t1.id

散列链接:select * from t1join t2 on t1.id=t2.id and t1.name=t2.name order by t1.id


db2batch –h


ii.
使用db2advis查看SQL索引建议

1.
基本使用db2advis-d afa -s “select t3.dept from afa.t3 t3 where t3.dept = 'a'” -o output.log

b)
char和varchar


i.
字段数据类型尽量使用char来代替字符,char类型性能稍比varchar好点。因为varchar是变长类型,所有是先读取数据长度再读取数据,分了两个步骤,而char是定长类型,读取数据长度和数据只用了一个步骤


ii.
使用VARCHAR可能会产生OVERFLOW


c)
常见的SQL编写、优化和SQL高级应用


i.
合理使用约束也可以增强查询性能

Create table tbname(col1 int ,col2 int,col3 int generated always as(col1 + col2),col4 int generated always as( case when col1 > col2 then col1 elsecol2 end))

                            查询1 取col1和col2之和的时候


Select sum(col1+col2) from tbname;

                            可以改成:


Select sum(col3) from tbname;

                            查询2 取col1和col2其中最大的一个


Select case when col1 > col2 then col1 else col2 end as colfrom tbname

                            可以改成 :Select col4 as col from tbname;


ii.
尽量把set value改写成 values into


iii.
new table和old table的使用

1.
with ot as ( select * fromold table(update t1 set id = 0 where id < 100) )


select 'old',id fromot


union all


select 'new',id fromt1 where id < 100 ;

2.
更新、查询一起做:SELECT * FROM NEW TABLE(UPDATE (SELECTTABNAME,REMOTETABNAME,CRTTIME,SYNCBEGINTIME,SYNCENDTIME,STATUS,REPEATMINUTE,NEXTSYNCTIME,ROWNUMBER()OVER() ROWNUM FROM AFA.T_SYNC_TABLES WHERE STATUS='1' and NEXTSYNCTIME >CURRENT TIMESTAMP ) SET STATUS='3',SYNCBEGINTI

3.


iv.
如果允许的话,可以使用syscat.tables.card代替count(*)

C:UsersAdministrator>db2 "select card from syscat.tableswhere tabname = 'T_HIST_SENDMSG'"

CARD

--------------------


4197446


1 条记录已选择。

C:UsersAdministrator>db2 "select count(1) from t_hist_sendmsg"

1

-----------


4197446


1 条记录已选择。


v.
合理使用with ur

1.
A端db2 +c"update t_config set setname='a' where id=90 with ur"

2.
B端db2"select * from t_config where id <> 90"


vi.
常用的会话命令(setcurrent)

1.
update command options using c off ,自动提交

2.
set current schema afa ,设置当前模式

3.
set write resume for database设置当前数据库停顿(禁止写入)

4.
db2 set current isolation ur 设置当前CLP的隔离模式


vii.
如果只有查询,尽量把存储过程改用自定义函数


viii.
避免死锁

1.
模拟死锁(+c)

a)
A端 lock tableA in exclusive mode同时 B端口lock table B in exclusive mode

b)
A 端lock table Bin exclusive mode同时B端 lock table A in exclusive mode

2.
改善死锁最根本的还是需要针对应用的以上这种情况进行调整

3.
最好在开发环境和测试环境开启所超时报告环境变量:DB2_CAPTURE_LOCKTIMEOUT=ON


ix.
DB2V9.7新特性cur_commit当前提交


d)
使用MERGE对多张表的数据进行合并

merge into t1 using t2 on t1.id = t2.id

when matched then update set t1.name = t2.name

when not matched then insert values (t2.id,t2.name)

@

e)
OLAP在线分析函数的基本使用


i.
olap排列函数

1.
rank() over() 分区内值重复时,使用“奥林匹克式”排列,“结在一起,同获第二”,经过结后,值不会从上一个值继续,而是跳过了中间的间隙,按"行号"开始。

2.
rense_rank() over() 分区内类似rank()over(),但是出现结之后,值会从上一个值继续。

3.
rownumber() over() 分区内从上到下进行排序,没有结概念。


ii.
olap标量-聚集函数

1.
sum() over(partition by ...) --计算分区内的值累加

2.
max() over(partition by ...) --计算分区内的最大

3.
min() over(partition by ...) --计算分区内的最小

4.
count() over(partition by ...)--计算分区内的总数

5.
avg() over(partition by ...)
--
计算分区内的平均值


iii.
实例:把各个部门薪水最低的员工薪水累加到薪水最高的员工身上

2、
利用递归SQL制造测试数据

a)
使用递归SQL生成测试数据直接插入表

insert into t1

with t(id,str)as ( values (1,'b') union all
selectid+1,'c' from t where id <= 100 )select * from t

b)
使用递归SQL声明游标方式进行LOAD进表

db2"declare mycursor cursor for with t(id,str) as ( values (1,'b') unionall
select id+1,'c' from t where id<= 100 )select * from t"

db2 "loadfrom mycursor of cursor insert into t1"

c)
使用递归SQL生成测试数据文件

db2 "exportto test.del of del with t(id,str) as ( values (1,'b') union all
select id+1,'c' from t where id <= 100)select * from t"

3、
日常问题解决方法(DBA篇幅)

a)
执行updatedeleteinsertSQL语句时事务日志已满问题解决


i.
增加LOGSECOND辅助日志数量(不需要重启数据库)


ii.
在当前事务打开不记日志特性:

1.
alter table t_invalidmsg activate not logged initially

2.
delete from t_invalidmsg

3.
该种方式不建议采用,如果在删除完成后,输入rollback会导致表无法访问,需要重建


iii.

b)
删除表字段不能对表进行更新的问题解决


i.
db2 "alter table c1 drop column COL2 " --删除表字段


ii.
db2 reorg table c1  –重组表后才可以进行对表更新

c)
对表增加generatedalways字段后出现表挂起状态解决


i.
db2 set integrity for c1 off –进行挂起表


ii.
db2 alter table c1 add column col2 generated always as (cast(col1 asint)) –增加自动生成字段


iii.
db2 set integrity for c1 immediate checked force generated –解挂并对历史数据进行生成


iv.

d)
使用db2pd +snapshot排除锁超时原因


i.
在A进行锁表:db2 +c "lock table c1 in exclusive mode"


ii.
在B进行查询:db2 "select * from c1"


iii.
在C查看锁等待:db2pd -d afa –wlock


iv.
在C查应用ID的状态:db2 getsnapshot for application applid *LOCAL.DB2INST1.120913113828

e)
杀掉一个代理


i.
db2pd –d afa -wlocks –取出AppHandl


ii.
db2 force application([AppHandl])

f)
对出现锁超时的应用进行排查(DB2_CAPTURE_LOCKTIMEOUT锁超时报告)

g)
对被引用的数据库对象无法重命名问题解决(V9.5或者更早版本)


i.
可以通过删除对象进行解决该问题

h)
TOP SQL捕获


i.
以下的表可以捕获:

1.
SYSIBMADM.TOP_DYNAMIC_SQL

2.
SYSIBMADM.MON_CURRENT_SQL

3.
SYSIBMADM.SNAPDYN_SQL

4.
SYSIBMADM.TOP_DYNAMIC_SQL

i)
使用empty table快速清理一张大表的全部测试数据


i.
db2 alter table t_hist_sendmsg activate not logged initially withempty table

j)
使用importloadreplace快速清理一张大表的全部测试数据


i.
db2 "load from empty.log of del replace into msg_biz_sms" –前提是empty.log这个文件必须存在(windows)

k)
使用not logged快速清理一张大表的部分数据

培训文件见附件或者emai:huangzhuxing@gmil.com

不足之处,请大家多多纠正!

2012-09-14
页数10
浏览2459
下载426

已下载用户的评价7.97分

您还未下载该资料,不能发表评价;
查看我的 待评价资源
byamingbyaming   小型机/X86服务器/常见品牌存储硬件运维 , NONE2019-02-20
有用
学习学习 谢谢
wuwenpinwuwenpin   软件开发工程师 , 南京2018-06-12
有用
学习学习 哈哈
dragoncxbdragoncxb   项目总监 , kunlun2018-06-12
有用
非常有用,值得收藏!
coolmenglongcoolmenglong   系统工程师 , 北京高伟达钽云科技有限公司2017-01-10
有用
学习学习 哈哈

贡献者

UniverseSae数据库管理员,待业
专业数据搬运工
X社区推广