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

本篇文章主要介绍了"DB2中使用事件监控器获取某个时间段里某个用户执行的SQL语句"，主要涉及到sql语句,事件监控方面的内容，对于DB2感兴趣的同学可以参考一下： 需求：管理员想要监控在一段时间里，用户DB2TEST跑过哪些SQL语句，这个在DB2中如何做到？答： 可以采用STATEMENT类型的事件监控器：测试过程：1....

需求：管理员想要监控在一段时间里，用户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:  "".  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仅指定所需要的字段：