The CREATE WORKLOAD statement defines a workload.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
The privileges held by the authorization ID of the statement must include WLMADM or DBADM authority.
>>-CREATE WORKLOAD--workload-name-------------------------------> .---------------------------. V | >----| connection-attributes |-+--| workload-attributes |-------> .-POSITION LAST-----------------------. >--+-------------------------------------+----------------------> '-POSITION--+-BEFORE--workload-name-+-' +-AFTER--workload-name--+ '-AT--position----------' .-COLLECT ACTIVITY METRICS--NONE---------. >--+----------------------------------------+-------------------> | .-BASE-----. | '-COLLECT ACTIVITY METRICS--+----------+-' '-EXTENDED-' .-COLLECT ACTIVITY DATA--NONE----------------------------------------------. >--+--------------------------------------------------------------------------+--> '-COLLECT ACTIVITY DATA--| collect-on-clause |--| collect-details-clause |-' .-COLLECT AGGREGATE ACTIVITY DATA--NONE---------. >--+-----------------------------------------------+------------> | .-BASE-----. | '-COLLECT AGGREGATE ACTIVITY DATA--+----------+-' '-EXTENDED-' .-COLLECT LOCK TIMEOUT DATA--WITHOUT HISTORY------------------. >--+-------------------------------------------------------------+--> '-COLLECT LOCK TIMEOUT DATA--+-NONE-------------------------+-' '-WITH HISTORY--+------------+-' '-AND VALUES-' .-COLLECT DEADLOCK DATA--WITHOUT HISTORY------------------. >--+---------------------------------------------------------+--> '-COLLECT DEADLOCK DATA----WITH HISTORY--+------------+---' '-AND VALUES-' .-COLLECT LOCK WAIT DATA--NONE--------------------------. >--+-------------------------------------------------------+----> '-COLLECT LOCK WAIT DATA--| collect-lock-wait-options |-' .-COLLECT UNIT OF WORK DATA--NONE-------------. >--+---------------------------------------------+--------------> | .-BASE---------. | '-COLLECT UNIT OF WORK DATA--+--------------+-' '-PACKAGE LIST-' >--| histogram-template-clause |------------------------------->< connection-attributes .-----------------. (1) V | |--------+-ADDRESS--(----'address-value'-+--)---------------------------+--| | .--------------------. | | V | | +-APPLNAME--(----'application-name'-+--)-----------------------+ | .----------------------. | | V | | +-SYSTEM_USER--(----'authorization-name'-+--)------------------+ | .----------------------. | | V | | +-SESSION_USER--(----'authorization-name'-+--)-----------------+ | .----------------------. | | V | | +-SESSION_USER GROUP--(----'authorization-name'-+--)-----------+ | .----------------------. | | V | | +-SESSION_USER ROLE--(----'authorization-name'-+--)------------+ | .-----------. | | V | | +-CURRENT CLIENT_USERID--(----'user-id'-+--)-------------------+ | .---------------------------. | | V | | +-CURRENT CLIENT_APPLNAME--(----'client-application-name'-+--)-+ | .--------------------. | | V | | +-CURRENT CLIENT_WRKSTNNAME--(----'workstation-name'-+--)------+ | .---------------------. | | V | | '-CURRENT CLIENT_ACCTNG--(----'accounting-string'-+--)---------' workload-attributes .-ENABLE--. .-ALLOW DB ACCESS----. |--+---------+--+--------------------+--------------------------> '-DISABLE-' '-DISALLOW DB ACCESS-' .-SERVICE CLASS SYSDEFAULTUSERCLASS-------------------------------------. >--+-----------------------------------------------------------------------+--| '-SERVICE CLASS--service-class-name--+--------------------------------+-' '-UNDER--service-superclass-name-' collect-on-clause .-DATABASE PARTITION-. .-ON COORDINATOR--+--------------------+-. |--+----------------------------------------+-------------------| | .-DATABASE PARTITIONS-. | '-ON ALL--+---------------------+--------' collect-details-clause .-WITHOUT DETAILS---------------------------. |--+-------------------------------------------+----------------| | .-,---------------. | | V (2) | | '-WITH----+-DETAILS-----+-+--+------------+-' '-SECTION-----' '-AND VALUES-' collect-lock-wait-options |--●------------------------------------------------------------> >--FOR LOCKS WAITING MORE THAN--+-wait-time--+-SECONDS------+-+--> | '-MICROSECONDS-' | '-1 SECOND--------------------' .-WITHOUT HISTORY--------------. >--●--+------------------------------+--●-----------------------| '-WITH HISTORY--+------------+-' '-AND VALUES-' histogram-template-clause .-ACTIVITY LIFETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-. |--●--+----------------------------------------------------------+--> '-ACTIVITY LIFETIME HISTOGRAM TEMPLATE--template-name------' .-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-. >--●--+-----------------------------------------------------------+--> '-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE--template-name------' .-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-. >--●--+-------------------------------------------------------------+--> '-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE--template-name------' .-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-. >--●--+---------------------------------------------------------------+--> '-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE--template-name------' .-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-. >--●--+------------------------------------------------------------------+--●--| '-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE--template-name------'
An IPv4 address must not contain leading spaces and is represented as a dotted decimal address. An example of an IPv4 address is 192.0.2.1. The value localhost or its equivalent representation 127.0.0.1 will not result in a match; the real IPv4 address of the host must be specified instead. An IPv6 address must not contain leading spaces and is represented as a colon hexadecimal address. An example of an IPv6 address is 2001:0DB8:0000:0000:0008:0800:200C:417A. IPv4-mapped IPv6 addresses (::ffff:192.0.2.1, for example) will not result in a match. Similarly, localhost or its IPv6 short representation ::1 will not result in a match. A domain name is converted to an IP address by the domain name server where a resulting IPv4 or IPv6 address is determined. An example of a domain name is corona.example.com. When a domain name is converted to an IP address, the result of this conversion could be a set of one or more IP addresses. In this case, an incoming connection is said to match the ADDRESS attribute of a workload object if the IP address from which the connection originates matches any of the IP addresses to which the domain name was converted.
When creating a workload object, you should specify domain name values for the ADDRESS attribute instead of static IP addresses, particularly in Dynamic Host Configuration Protocol (DHCP) environments where a device can have a different IP address each time it connects to the network.
The default limit on the number of past activities to be kept by any one application is 250. If the number of past activities is greater than the limit, only the newest activities are reported. This default value can be overridden using the registry variable DB2_MAX_INACT_STMTS to specify a different value. You can choose a different value for the limit to increase or reduce the amount of system monitor heap used for past activity information.
The default limit on the number of past activities to be kept by any one application is 250. If the number of past activities is greater than the limit, only the newest activities are reported. This default value can be overridden using the registry variable DB2_MAX_INACT_STMTS to specify a different value. You can choose a different value for the limit to increase or reduce the amount of system monitor heap used for past activity information.
This value can be any non-negative integer. Use a valid duration keyword to specify an appropriate unit of time for wait-time. The minimum valid value for the wait-time parameter is 1000 microseconds.
The default limit on the number of past activities to be kept by any one application is 250. If the number of past activities is greater than the limit, only the newest activities are reported. This default value can be overridden using the registry variable DB2_MAX_INACT_STMTS to specify a different value. You can choose a different value for the limit to increase or reduce the amount of system monitor heap used for past activity information.
Some of the information reported in a unit of work event are system level request metrics. The collection of these metrics is controlled independently from the collection of the unit of work data. The request metrics are controlled with the COLLECT REQUEST METRICS clause on superclass, or using the mon_req_metrics database configuration parameter. The service super class which the workload is associated with, or the service super class of the service subclass which the workload is associated with, must have the collection of request metrics enabled in order for the request metrics to be present in the unit of work event. If the request metrics collection is not enabled, the value of the request metrics will be zero.
The size of the collected package list is determined by the value of the mon_pkglist_sz database configuration parameter. If this value is 0, then the package list is not collected even if the PACKAGE LIST option is specified.
In a partitioned database environment, the package list is only available on the coordinator member. The BASE level will be collected on remote members.
Some of the information reported in a unit of work event are system level request metrics. The collection of these metrics is controlled independently from the collection of the unit of work data. The request metrics are controlled with the COLLECT REQUEST METRICS clause on superclass, or using the mon_req_metrics database configuration parameter. The service super class which the workload is associated with, or the service super class of the service subclass which the workload is associated with, must have the collection of request metrics enabled in order for the request metrics to be present in the unit of work event. If the request metrics collection is not enabled, the value of the request metrics will be zero.
CREATE WORKLOAD CAMPAIGN
SESSION_USER GROUP ('FINANCE')
CREATE WORKLOAD PAYROLL
SESSION_USER ROLE ('HR')
CURRENT CLIENT_APPLNAME ('SALARYSYS') SERVICE CLASS MEDIUMSC
UNDER HRSC POSITION AFTER CAMPAIGN
CREATE WORKLOAD NEWCAMPAIGN
SESSION_USER GROUP ('FINANCE')
APPLNAME ('DB2BP.EXE') SERVICE CLASS MARKETINGSC
POSITION BEFORE CAMPAIGN
The running workload
occurrence of CAMPAIGN continues to run until the current unit of
work completes, at which time a workload re-evaluation takes place,
and the connection could then be remapped to workload NEWCAMPAIGN. CREATE WORKLOAD REPORTS
APPLNAME ('appl1', 'appl2', 'appl3')
SYSTEM_USER ('BOB', 'MARY')
CREATE WORKLOAD EMPLOYEES
APPLNAME ("app1", "app2")
COLLECT LOCK TIMEOUT DATA WITH HISTORY
CREATE WORKLOAD FINANCE
APPLNAME ("app1", "app2")
COLLECT DEADLOCK DATA
COLLECT LOCK TIMEOUT DATA
CREATE WORKLOAD MANAGERS
APPLNAME ("app1", "app2")
COLLECT DEADLOCK DATA WITH HISTORY AND VALUES
CREATE WORKLOAD MANAGERS
APPLNAME ("app1", "app2")
COLLECT LOCK WAIT DATA FOR LOCKS WAITING MORE THAN 5 SECONDS WITH HISTORY
CREATE WORKLOAD ACCRECS
SESSION_USER GROUP ('ACCOUNTING')
APPLNAME ('accrec*')
SERVICE CLASS ACCOUNTNGSC
CREATE WORKLOAD CAMPAIGN
APPLNAME ('appl1')
COLLECT UNIT OF WORK DATA BASE
CREATE WORKLOAD DOMAINWORKLOAD
ADDRESS ('aviator.example.com')
CREATE WORKLOAD IPWORKLOAD1
ADDRESS ('192.0.2.11')
CREATE WORKLOAD IPWORKLOAD2
ADDRESS ('2001:db8:519:13:204:acff:fe57:6135')
CREATE WORKLOAD IPWORKLOAD3
ADDRESS ('2001:db8::202:55ff:fe9a:6eee')