db2学习笔记-数据移动&监控器

*注册变量&环境变量
db2set -i
#实例概要文件设置的注册表值
db2set -g
#全局概要文件设置的注册表值
db2set -all
#所有定义的注册表值
db2set 变量名 -all
#变量名所在等级
db2set 变量名=new value
db2ilist
db2set -h

*管理服务器配置设置
db2 get admin cfg

*数据库管理器配置参数
db2 get dbm cfg
#数据库管理参数当前值
db2 update dbm cfg using

#更改数据库管理参数，p代表参数，V代表参数值

*数据库配置参数
db2 get db cfg
db2 get db cfg for dbname
db2 update db cfg for using

#db2 update db cfg using logarchmeth1 disk:c:\arclog\ immediate
db2 reset db cfg for db_name

*实例&数据库
db2start
db2stop force
#启动&停止实例

db2 force applications all
db2 terminate
db2stop
#stop application connect

db2 attach to
db2start

*自动存储器
db2 create database lgxtest on d:\db2 dbpath on e:
d:\db2 存储器路径
e: 数据库路径

*数据移动
支持的文件格式
1，PC/IXF
2，DEL
3，ASC
4，WSF

db2 export to c:\test.sql of del select a from lgx2
db2 create table lgx3 like lgx2
db2 import from c:\test.sql  of del insert into lgx3
db2 load from c:\test.sql of del insert into lgx3
#归档模式load时，表所在的表空间置于备份挂起状态。可在load时加上nonrecoverable
db2 import from c:\test.sql  of del commitcount 5 insert into lgx3
#避免产生的事务过大，使当前日志已满，对导入的数据分次提交

*load
1，装入阶段，将数据写入表
2，构建阶段，生成索引
3，删除阶段，除去导致唯一违例的行
4，索引复制阶段，将索引数据从系统临时表空间复制到原始表空间

db2 create table lgx1_exception like lgx1
db2 alter table lgx1_exception add column time timestamp
db2 load from test.txt of del insert into lgx1 fro exception lgx1_exception
#异常捕获
db2 load from c:\test.txt of del modified by dumpfile=c:\dumpfile insert into lgx1
#记录格式不正确的行
db2 load from c:\test.txt of del rowcount 3 insert into lgx1
#导入三行数据
db2 load from c:\test.txt of del warningcount 1 insert into lgx1
#有一个warning，则导入失败
db2 load from c:\test.txt of del replace into lgx1 statistics yes with distribution and detailed indexes all
#导入过程中生成统计数据

db2move sample export -sn lgx -ts userspace1
#schema:lgx
#导出表空间userspace1中的表，默认所有表
db2move sample_1 import

*runstats
db2 CONNECT TO LGXTEST
db2 RUNSTATS ON TABLE ADMINISTRATOR.LGX5 ON ALL COLUMNS ALLOW WRITE ACCESS ;
db2 runstats on table ADMINISTRATOR.LGX5 with distribution on columns(a,b)
db2 runstats on table ADMINISTRATOR.LGX5 with distribution on key columns
db2 runstats on table ADMINISTRATOR.LGX5 and index all
db2 runstats on table ADMINISTRATOR.LGX5 and sampled detailed indexes all
db2 COMMIT WORK;
db2 CONNECT RESET;
#gather statistics

*reorg
db2 reorgchk update statistics on table ADMINISTRATOR.LGX5
db2 reorg table ADMINISTRATOR.LGX5 user TEMPSPACE1
db2 reorg table ADMINISTRATOR.LGX5 index ind_1

*snapshot
db2 get monitor switches
db2 update monitor switches using lock on statement on
db2 get health snapshot for dbm
db2 get snapshot for dbm
db2 get snapshot for db on lgxtest
db2 get snapshot for applications on lgxtest
db2 get snapshot for bufferpools on lgxtest
db2 get snapshot for locks on lgxtest
db2 get snapshot for tablespaces on lgxtest
db2 get snapshot for tables on lgxtest
db2 get snapshot for dynamic sql on lgxtest

*event type
DEADLOCKS
DEADLOCKS WITH DETAILS
STATEMENTS
create event monitor evm_name for eventtype write to file ‘directory’
 
#例：create event monitor mymonitor for deadlocks, statements 
write to file ‘c:\temp’ buffersize 8
maxfiles 20 maxfilesize 1024
manualstart

#事件缓冲区为8*4k=32k,由缓冲区写进目标文件
#最大文件个数为20
#文件大小为1024*4k=4M

  -把事件监控器打开 
        set event monitor mymonitor state 1
     注：1为打开，0为关闭
         事件监控器开始工作，当所有应用断掉连接后，将事件记录下来
  -查看事件细节
       db2evmon –path ‘c:\temp’
         
#create event monitor my_event for statements write to table stmt
SELECT * FROM ADMINISTRATOR.STMT_MY_EVENT;

select * from syscat.eventmonitors
#查看事件监视器
db2 drop event monitor my_event
#drop event

+++++++sample+++++++
1.
create event monitor stmt for statement
write to table
stmt(table monitor.sqlstmt,
in(mytablespace),
includes(agent_id),
appl_id,
start_time,
stop_time,
stmt_text,
total_sort_time)
buffersize 1024
nonblocked
manualstart
2.
db2 set event monitor stmt state 1
3.
db2 set event monitor stmt state 0
4.
select stmt_text,stop_time-start_time as time from ADMINISTRATOR.STMT_STMT
order by stop_time-start_time desc fetch first 10 rows only
+++++++sample+++++++

*running monitor
db2 get alert configuration for dbm