目的：
测试DB2负载管理(WLM,对不同操作分配不同代理优先级,在超过指定CPU资源后仍未完成的,降低优先级,类似于Oracle的ResourceManager);本脚本摘录自DB2安装目录admin_scripts/wlmtiersdefault.db2。
版本：Windows DB2 Express-C V9.7
说明:由于该版本不支持该特性,抱错SQL8029N 找不到所请求功能的有效许可证密钥,没有测试.
操作：
使用sqllib/misc/wlmevmon.ddl创建WLM事件监视器;
需要权限DBADM或WLMADM;
步骤:
1,创建服务类,层次关系如下:
------------WLM_TIERS
-- +----------+-----------+
-- | | |
--WLM_SHORT WLM_MEDIUM WLM_LONG
connect to sample@
-- Create service superclass WLM_TIERS
CREATE SERVICE CLASS WLM_TIERS@
-- Create service subclasses WLM_SHORT, WLM_MEDIUM, WLM_LONG
CREATE SERVICE CLASS WLM_SHORT UNDER WLM_TIERS@
CREATE SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS@
CREATE SERVICE CLASS WLM_LONG UNDER WLM_TIERS@
2,创建服务类重新影射阈值
--WLM_SHORT --> WLM_MEDIUM --> WLM_LONG
-- Create thresholds to remap activities from WLM_SHORT to WLM_MEDIUM
-- to WLM_LONG service subclasses based on processor time used in service class
CREATE THRESHOLD WLM_TIERS_REMAP_SHORT_TO_MEDIUM FOR
SERVICE CLASS WLM_SHORT UNDER WLM_TIERS ACTIVITIES
ENFORCEMENT DATABASE PARTITION WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_MEDIUM@
CREATE THRESHOLD WLM_TIERS_REMAP_MEDIUM_TO_LONG FOR
SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS ACTIVITIES
ENFORCEMENT DATABASE PARTITION WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_LONG@
3,创建工作类集,工作动作集
-- Create work class set WLM_TIERS_WCS to identify activities
-- that can be remapped by CPUTIMEINSC threshold.
CREATE WORK CLASS SET WLM_TIERS_WCS
( WORK CLASS WLM_DML_WC WORK TYPE DML,
WORK CLASS WLM_CALL_WC WORK TYPE CALL,
WORK CLASS WLM_OTHER_WC WORK TYPE ALL )@
-- Create work action set WLM_TIERS_WAS to map activities that
-- can be remapped by CPUTIMEINSC threshold to service subclass
-- WLM_SHORT. All other activities are mapped to service subclass
-- WLM_MEDIUM and will not get remapped.
--DML-->WLM_SHORT
--CALL-->WLM_SHORT
--OTHER-->WLM_MEDIUM
CREATE WORK ACTION SET WLM_TIERS_WAS FOR SERVICE CLASS WLM_TIERS
USING WORK CLASS SET WLM_TIERS_WCS
( WORK ACTION WLM_DML_WA ON WORK CLASS WLM_DML_WC
MAP ACTIVITY TO WLM_SHORT,
WORK ACTION WLM_CALL_WA ON WORK CLASS WLM_CALL_WC
MAP ACTIVITY TO WLM_SHORT,
WORK ACTION WLM_OTHER_WC ON WORK CLASS WLM_OTHER_WC
MAP ACTIVITY TO WLM_MEDIUM )@
4,设置不同服务类代理优先级
-- Create a stored procedure to determine operating system and to set service
-- class agent priorities. The operating system must be determined before
-- setting agent priority because the range of values for agent
-- priority differs between Windows and UNIX operating systems.
-- Service class properties:
--
-- Service Class Agent Priority Prefetch Priority
-- (UNIX / Win)
-- ---------------------------------------------------
-- WLM_SHORT -10 / 3 High
-- WLM_MEDIUM 0 / 0 Medium
-- WLM_LONG 10 / -3 Low
-- Default System -15 / 5 High
-- Default Maint. 15 / -5 Low
CREATE PROCEDURE checkPlatformAndSetAgentPriority()
LANGUAGE SQL
--------------------------------------------------------
-- SQL Stored Procedure checkPlatformAndSetAgentPriority
--------------------------------------------------------
BEGIN
DECLARE isWindows VARCHAR(256);
DECLARE stmt VARCHAR(300);
SELECT os_name INTO isWindows
FROM SYSIBMADM.ENV_SYS_INFO
WHERE UCASE(os_name) LIKE '%WIN%' OR UCASE(os_name) LIKE '%NT%';
-- #PROPERTY# (UNIX) Set agent priority for service classes on UNIX
-- operating systems. A lower numeric value means higher priority. A higher
-- numeric value means lower priority. The valid range for agent priority on
-- UNIX is -20 to 20.
IF isWindows IS NULL
THEN
SET stmt = 'ALTER SERVICE CLASS WLM_SHORT UNDER WLM_TIERS AGENT PRIORITY -10';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS AGENT PRIORITY 0';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_LONG UNDER WLM_TIERS AGENT PRIORITY 10';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTSYSTEMCLASS AGENT PRIORITY -15 ';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTMAINTENANCECLASS AGENT PRIORITY 15';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
-- #PROPERTY# (Windows) Set agent priority for service classes on Windows
-- operating systems. A higher numeric value means higher priority. A lower
-- numeric value means lower priority. The valid range for agent priority on
-- Windows is -6 to 6.
ELSE
SET stmt = 'ALTER SERVICE CLASS WLM_SHORT UNDER WLM_TIERS AGENT PRIORITY 3';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS AGENT PRIORITY 0';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_LONG UNDER WLM_TIERS AGENT PRIORITY -3';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTSYSTEMCLASS AGENT PRIORITY 5 ';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTMAINTENANCECLASS AGENT PRIORITY -5';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
END IF;
END@
-- Call the checkPlatformAndSetAgentPriority stored procedure to
-- set agent priorities for service classes
CALL checkPlatformAndSetAgentPriority()@
-- Drop the checkPlatformAndSetAgentPriority stored procedure
DROP PROCEDURE checkPlatformAndSetAgentPriority@
5,设置不同服务类预取(PREFETCH)优先级
-- #PROPERTY# Set prefetch priority for service classes. Valid values for
-- prefetch priority are HIGH, MEDIUM, LOW or DEFAULT (MEDIUM).
ALTER SERVICE CLASS WLM_SHORT UNDER WLM_TIERS PREFETCH PRIORITY HIGH@
ALTER SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS PREFETCH PRIORITY MEDIUM@
ALTER SERVICE CLASS WLM_LONG UNDER WLM_TIERS PREFETCH PRIORITY LOW@
ALTER SERVICE CLASS SYSDEFAULTSYSTEMCLASS PREFETCH PRIORITY HIGH@
ALTER SERVICE CLASS SYSDEFAULTMAINTENANCECLASS PREFETCH PRIORITY LOW@
6,创建服务类重新影射阈值并记录事件监视器日志
-- #PROPERTY# Set the maximum in service class processor time before
-- remapping and the checking period. The maximum in service class processor
-- time determines how much processor time an activity can consume in
-- a service class before being remapped to the target service class.
-- For example, if you want an activity to remain in service class
-- WLM_SHORT for a shorter period before being remapped to WLM_MEDIUM,
-- decrease the CPUTIMEINSC threshold value for WLM_TIERS_REMAP_SHORT_TO_MEDIUM.
-- The checking period determines how long to wait between checks for threshold
-- violation. For serial ESE instances, set the checking period to be
-- the same as the processor time before remap. For DPF or SMP instances,
-- set a lower value for the checking period than the processor time
-- before remap.
--
-- When one of these thresholds is violated and an activity is remapped
-- to the next service subclass, an event monitor record is written to
-- the threshold violations event monitor. This way, you can see
-- how many activities are moved between the tiers service subclasses.
-- Logging an event monitor record incurs a small performance cost. Once
-- the system is tuned and the threshold violation event monitor records
-- are no longer needed, simply remove the 'LOG EVENT MONITOR RECORD'
-- clause from the ALTER THRESHOLD statements.
ALTER THRESHOLD WLM_TIERS_REMAP_SHORT_TO_MEDIUM WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_MEDIUM LOG EVENT MONITOR RECORD@
ALTER THRESHOLD WLM_TIERS_REMAP_MEDIUM_TO_LONG WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_LONG LOG EVENT MONITOR RECORD@
7,启用工作负载管理器
-- Alter SYSDEFAULTUSERWORKLOAD to map workload to WLM_TIERS service class
ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD SERVICE CLASS WLM_TIERS@
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-702325/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-702325/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
