db2日常命令，离线备份，监控优化步骤

最新推荐文章于 2022-02-26 13:55:22 发布

ywswen

最新推荐文章于 2022-02-26 13:55:22 发布

阅读量660

点赞数

文章标签： db2 监控

本文链接：https://blog.csdn.net/ywswen/article/details/78994288

版权

最近db2项目投产，运维同事搭建完数据库后，剩余投产工作内容积累的命令如下，mark一下

db2 "drop database amsTest"

cd /home/db2inst2/

mkdir db2data
mkdir db2log

//建表

db2 "create database amsTest using codeset utf-8 territory CN"

//创建缓冲池
db2 connect to amsTest

//修改日志路径
db2 "update db cfg for amsTest using NEWLOGPATH /home/db2inst2/db2log"

chown -R db2inst1:db2iadm1 db2data

db2 "create bufferpool bp32k all nodes size -1 pagesize 32k"
//表空间使用缓冲池

db2 "create tablespace tbs32k pagesize 32K MANAGED BY DATABASE USING(FILE '/home/db2inst2/db2data/amsTesttablespace01' 128000) EXTENTSIZE 80 bufferpool bp32k"

//db2 "drop tablespace tbs32k"
//增加临时表空间
//CREATE TEMPORARY TABLESPACE TEMPSPACE01 PAGESIZE 32K MANAGED BY DATABASE USING(FILE '/home/db2/db2root/NODE0000/TBS/tablespace01' 128000) EXTENTSIZE 80 bufferpool bp32k;
db2 "CREATE TEMPORARY TABLESPACE TEMPSPACE01 PAGESIZE 32K MANAGED BY DATABASE USING(FILE '/home/db2inst2/db2data/amsTesttmptablespace01' 128000) EXTENTSIZE 80 bufferpool bp32k"

--查看db2的端口
tail -10 /etc/services |grep DB2_db2inst2
显示如下内容
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
DB2_db2inst1_3 60003/tcp
DB2_db2inst1_4 60004/tcp

db2 update dbm cfg using SVCENAME DB2_db2inst2
db2ilist
db2stop force
db2start
db2 get dbm cfg |grep SVC

db2 get dbm cfg |grep "SVCENAME"

netstat -an |grep 60000

db2_kill
ipclean

--离线数据库备份

db2 backup database amsTest with 2 buffers buffer 1024 parallelism 1 without prompting

db2 restore db amshrbcb from /home/db2inst2/db2bak taken at 20171013104624 with 2 buffers buffer 1024 parallelism 1 without prompting

db2 restore db amsTest from /home/db2inst2/db2bak taken at 20171013101332 with 2 buffers buffer 1024 parallelism 1 without prompting

mv /home/db2inst2/db2bak/AMSTEST.0.db2inst2.DBPART000.20171013101332.001 /home/db2inst2/db2bak/

db2set db2comm=TCPIP

--db2数据库监控

db2top -d NEWSEAL -u db2inst2 -p db2inst2
l -是查询session
D -查看sql语句
a -查看l下具体的session的语句
L -查看D下具体的sql ID对应的语句

vi t1.sql :wq推出保存
db2expln -database newseal -i -g -stmtfile t1.sql -terminator ';' -output t1.exp

db2expln -database newseal -i -g -stmtfile t2.sql -terminator ';' -output t2.exp、

db2expln -database newseal -i -g -stmtfile t3.sql -terminator ';' -output t3.exp

消耗值：Estimated Cost = 148207.421875
全表扫描：TBSCAN
Estimated Cost = 148333.359375

Estimated Cost = 11146.720703
Estimated Cost = 70565.273438

db2 "describe indexes for table Pack"
查看索引

db2 "describe indexes for table CardImage"