DB2中使用事件监控器获取某个时间段里某个用户执行的SQL语句

DB2 专栏收录该内容
175 篇文章 2 订阅

需求:管理员想要监控在一段时间里,用户DB2TEST跑过哪些SQL语句,这个在DB2中如何做到?


答: 可以采用STATEMENT类型的事件监控器:


测试过程:

1.) 管理员用户连库、创建事件监控器,并运行,下面的事件监控器专为用户DB2TEST的执行的STATEMENT,并将STMT evm group写入到表T1中,表T1会被在表空间USERSPACE1中创建:

db2 "connect to sample"
db2 "create EVENT MONITOR evmonSQLforUser for STATEMENTS where AUTH_ID = 'DB2TEST' write to table STMT(table t1 in userspace1)"
db2 "set event monitor evmonSQLforUser state=1"

2.) 用户DB2TEST连接到数据库后执行SQL语句:

$ db2 "select ID from testdb1"
ID
-----------
        111
        911
  2 record(s) selected.

$ db2 "insert into test db1 values(100)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "db1 values" was found following "insert into
TEST ".  Expected tokens may include:  "<values>".  SQLSTATE=42601

$ db2 "insert into testdb1 values(100)"
DB20000I  The SQL command completed successfully.

$ db2 "create table testdb2(id int, name char(20)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "d int,
name char(20)".  Expected tokens may include:  ")".  SQLSTATE=42601

$ db2 "create table testdb2(id int, name char(20))"
DB20000I  The SQL command completed successfully.

3.)管理员用户关闭事件监控器 ,并查看监控结果,可以看到无论是正确的SQL语句,还是错误的SQL语句,只要是用户DB2TEST发出的,都有记录(不知道为啥,每个select语句要记录4次,请自动忽略掉后面3个,还有一些的STMT_TEXT结果是空的,也请忽略):

db2 "set event monitor evmonSQLforUser state=0"

C:\windows\system32>db2 "select START_TIME, substr(APPL_ID,1,40) as APPL_ID, substr(STMT_TEXT,1,80) as STMT_TEXT from t1"

START_TIME                 APPL_ID                                  STMT_TEXT
-------------------------- ---------------------------------------- ------------------------------------------------------
2016-05-26-12.50.34.077069 *LOCAL.DB2INST1.160526031015             select ID from testdb1
2016-05-26-12.50.34.078434 *LOCAL.DB2INST1.160526031015             select ID from testdb1
2016-05-26-12.50.34.078434 *LOCAL.DB2INST1.160526031015             select ID from testdb1
2016-05-26-12.50.34.078434 *LOCAL.DB2INST1.160526031015             select ID from testdb1
2016-05-26-12.50.34.122445 *LOCAL.DB2INST1.160526031015
2016-05-26-12.50.43.366093 *LOCAL.DB2INST1.160526031015             insert into test db1 values(100)
2016-05-26-12.50.43.381406 *LOCAL.DB2INST1.160526031015
2016-05-26-12.50.50.586494 *LOCAL.DB2INST1.160526031015             insert into testdb1 values(100)
2016-05-26-12.50.50.587564 *LOCAL.DB2INST1.160526031015
2016-05-26-12.51.20.025009 *LOCAL.DB2INST1.160526031015             create table testdb2(id int, name char(20)
2016-05-26-12.51.20.025477 *LOCAL.DB2INST1.160526031015
2016-05-26-12.51.22.823535 *LOCAL.DB2INST1.160526031015             create table testdb2(id int, name char(20))
2016-05-26-12.51.22.962340 *LOCAL.DB2INST1.160526031015

  13 record(s) selected.


补充说明:

1.) 如果使用db2 "describe table t1"去查看,会发现T1有很多字段,会占用表空间,如果觉得不需要,可以在创建事件监控器的时候使用INCLUDE仅指定所需要的字段:

db2 "create EVENT MONITOR evmonSQLforUser for STATEMENTS where AUTH_ID = 'DB2TEST' write to table STMT(table t1 in userspace1 INCLUDES(START_TIME,APPL_ID,STMT_TEXT))"


2.)T1中每个字段的含义,可以参考下面的链接:

https://ibm.biz/Bd49yY

3.)创建事件监控器的完整语法可以参考下面的链接:

https://ibm.biz/Bd49yq

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值