ITPub博客

首页 > 数据库 > 数据库开发技术 > DB2 WLM

DB2 WLM

原创 数据库开发技术 作者:zchbaby2000 时间:2018-09-28 14:17:18 0 删除 编辑

最近遇到一个问题,一个不合理的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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: db2 hadr_spool_limit
请登录后发表评论 登录
全部评论
喜欢各种数据库相关的开发,架构设计,运维和性能优化
  • 博文量
    242
  • 访问量
    91173