DB2使用workload manager控制某个用户读取行数的限制

DB2中，可以使用workload manager控制某个用户的SQL语句返回的行数，具体可以参考下面的链接：

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0807wangjin/

同样的，这儿只是我的测试，让用户db2test访问表时，最多只能读取15行数据：

1. DB2Admin 用户定义一个 SuperClass，命名为 DB2ADMIN_SC
Create service class DB2ADMIN_SC DISABLE;
Create service class read_worktype_subclass under DB2ADMIN_SC DISABLE;


2. 对该 Work Identity 的连接属性只指定了系统用户为 DB2ADMIN，这样所有来自用户 DB2ADMIN 的连接都会归属到 DB2ADMIN_WID 这个 Work Identity 之下。并且把该 Work Identity 和之前定义的 Super Class DB2ADMIN_SC 关联起来。这样，DB2ADMIN 发出的所有工作都在 Service Class DB2ADMIN_SC 下活动。
Create workload DB2ADMIN_WID system_user('DB2ADMIN') disable service class DB2ADMIN_SC POSITION AT 1;
Grant usage on workload DB2ADMIN_WID to PUBLIC;


3. 点击 Work Type tab 来创建一个新的工作类，向导会提示先创建一个 Work Type Sets。这里定义一个名为 DB2ADMIN_WTS 的工作类集。然后在该 Work Type Sets 之下创建一个名为 READ_WORKTYPE，类型为 READ 的 Work Type，
Create work class set DB2ADMIN_WTS(work class READ_WORKTYPE work type read position at 1);
create work action set db2admin_sc for service calss db2admin_sc using work class set DB2ADMIN_WTS(work action read_worktype_to_read_worktype_subclass on work class read_worktype map activity to read_worktype_subclass) disable;


4. 最后，也是解决之前提出的问题的关键一步，就是创建 Limits。这里，需要对某一个用户源发出的某一类工作做出限制，所以创建一个如下图所示的 Limits。在这个 CASE 中用户源就是 DB2ADMIN，工作类型就是 READ。然后对 Work Identity 指定上面已经定义的 DB2ADMIN_WID，Work Type 指定已定义的 READ_WORKTYPE，并创建一个 Condition 作为 Limits


create threshold "SQL Rows Returned Rule" for service class Read_worktype_subclass under db2admin_sc activities enforcement database disable when SQLROWSRETURNED > 15 stop execution;


alter service class db2admin_sc enable;
alter service class read_worktype_subclass under db2admin_sc entable;
alter workload db2admin_wid enable;
alter work action set db2admin_sc enable;
alter threshold "SQL Row Returned Rule" enable;


http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0807wangjin/


1.） 创建service class,名子叫read_worktype_subclass，它的super class是 db2test_sc
db2 "create service class DB2TEST_SC disable"
db2 "create service class read_worktype_subclass under DB2TEST_SC disable"


2.)为db2test用户创建workload， 名子叫DB2TEST_WID,并关联到super class
db2 "create workload DB2TEST_WID system_user('DB2TEST') disable service class DB2TEST_SC position at 1"
db2 "grant usage on workload DB2TEST_WID  to public"


3.) 创建class set和action set
db2 "create work class set DB2TEST_WTS(work class READ_WORKTYPE work type read position at 1)" //READ_WORKTYPE是名子
db2 "create work action set DB2TEST_SC for service class DB2TEST_SC using work class set DB2TEST_WTS(work action read_worktype_to_read_worktype_subclass on work class READ_WORKTYPE map activity to read_worktype_subclass) disable"


4.) 
db2 "create threshold \"SQL Rows Returned Rule\" for service class read_worktype_subclass under db2test_sc activities enforcement database disable when SQLROWSRETURNED > 15 stop execution"


5.)
db2 "alter service class db2test_sc enable"
db2 "alter service class read_worktype_subclass under db2test_sc enable"
db2 "alter workload DB2TEST_WID enable"
db2 "alter work action set DB2TEST_SC enable"
db2 "alter threshold \"SQL Rows Returned Rule\" enable"






=========




C:\windows\system32>db2 connect to sample user db2test using db2test


   数据库连接信息


 数据库服务器         = DB2/NT64 9.7.11
 SQL 授权标识         = DB2TEST
 本地数据库别名       = SAMPLE




C:\windows\system32>db2 "create table t1(id int)"
DB20000I  SQL 命令成功完成。


C:\windows\system32>db2 "insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)"
DB20000I  SQL 命令成功完成。


C:\windows\system32>db2 "select * from t1"


ID
-----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11
         12
         13
         14
         15


  15 条记录已选择。




C:\windows\system32>db2 "insert into t1 values(16)"
DB20000I  SQL 命令成功完成。


C:\windows\system32>db2 "select * from t1"


ID
-----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11
         12
         13
         14
         15
SQL4712N  已超出阈值 "SQL Rows Returned Rule"。原因码："8"。  SQLSTATE=5U026


C:\windows\system32>