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"