DB2 WLM

最近遇到一个问题，一个不合理的SQL运行占用了400G的临时表空间，差点把数据库搞down，因此对某些用户的查询要做限制，我这里限制条件

1.返回1百万和记录

or

2 .运行时间超过30分钟

对这样的查询，force掉

####create service class, name is read_worktype_subclass, its super class is read_sc
db2 "create service class read_sc disable"
db2 "create service class read_worktype_subclass under read_sc disable"


####create workload for user readusr, the workload name is read_WID, and attach the workload to super class
db2 "create workload read_WID system_user('READUSR') disable service class read_sc position at 1"
db2 "grant usage on workload read_WID to public"


####create class set and action set
db2 "create work class set read_WCS(work class READ_WORKTYPE work type read position at 1)"
db2 "create work action set read_WAS for service class read_sc using work class set read_WCS(work action read_worktype_to_read_worktype_subclass on work class READ_WORKTYPE map activity to read_worktype_subclass) disable"

####Create threshold

db2 "create threshold ROWS_RETURNED_THRESH for service class read_worktype_subclass under read_sc activities enforcement database disable when SQLROWSRETURNED > 1000000 stop execution"
db2 "create threshold DBMAX1HOURRUNTIME for service class read_worktype_subclass under read_sc activities enforcement database disable WHEN ACTIVITYTOTALTIME > 30 minutes stop execution"

####enable all of the objects created
db2 "alter service class read_sc enable"
db2 "alter service class read_worktype_subclass under read_sc enable"
db2 "alter workload read_WID enable"
db2 "alter work action set read_WAS enable"
db2 "alter threshold ROWS_RETURNED_THRESH enable"
db2 "alter threshold DBMAX1HOURRUNTIME enable"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/725820/viewspace-2215148/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/725820/viewspace-2215148/