The CREATE HISTOGRAM TEMPLATE statement defines a template
describing the type of histogram that can be used to override one
or more of the default histograms of a service class or a work class.
Invocation
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).
Authorization
The privileges
held by the authorization ID of the statement must include WLMADM
or DBADM authority.
Syntax
>>-CREATE HISTOGRAM TEMPLATE--template-name--------------------->
>--HIGH BIN VALUE--bigint-constant-----------------------------><
Description
- template-name
- Names the histogram template. This is a one-part name. It
is an SQL identifier (either ordinary or delimited). The name
must not identify an existing histogram template at the current server
(SQLSTATE 42710). The name must not begin with the characters
'SYS' (SQLSTATE 42939).
- HIGH BIN VALUE bigint-constant
- Specifies the top value of the second to last bin (the last bin
has an unbounded top value). The units depend on how the histogram
is used. The maximum value is 268 435 456.
Rules
- A workload management (WLM)-exclusive SQL statement must be followed
by a COMMIT or a ROLLBACK statement (SQLSTATE 5U021). WLM-exclusive
SQL statements are:
- CREATE HISTOGRAM TEMPLATE, ALTER HISTOGRAM TEMPLATE, or DROP (HISTOGRAM
TEMPLATE)
- CREATE SERVICE CLASS, ALTER SERVICE CLASS, or DROP (SERVICE CLASS)
- CREATE THRESHOLD, ALTER THRESHOLD, or DROP (THRESHOLD)
- CREATE WORK ACTION SET, ALTER WORK ACTION SET, or DROP (WORK ACTION
SET)
- CREATE WORK CLASS SET, ALTER WORK CLASS SET, or DROP (WORK CLASS
SET)
- CREATE WORKLOAD, ALTER WORKLOAD, or DROP (WORKLOAD)
- GRANT (Workload Privileges) or REVOKE (Workload Privileges)
- A WLM-exclusive SQL statement cannot be issued within a global
transaction (SQLSTATE 51041) such as, for example, an XA transaction.
Notes
- Only one uncommitted WLM-exclusive SQL statement at a time is
allowed across all partitions. If an uncommitted WLM-exclusive SQL
statement is executing, subsequent WLM-exclusive SQL statements will
wait until the current WLM-exclusive SQL statement commits or rolls
back.
- Changes are written to the system catalog, but do not take effect
until they are committed, even for the connection that issues the
statement.
Example
Create a histogram template named
LIFETIMETEMP on service class PAYROLL in service superclass ADMIN
that will override the default activity lifetime histogram template
with a new high bin value of 90 000
, which represents 90 000 milliseconds.
This will produce a histogram with exponentially increasing bin ranges,
ending with a bin whose range is 90 000 to infinity.
CREATE HISTOGRAM TEMPLATE LIFETIMETEMP
HIGH BIN VALUE 90000
CREATE SERVICE CLASS PAYROLL
UNDER ADMIN ACTIVITY LIFETIME HISTOGRAM TEMPLATE LIFETIMETEMP