DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE HISTOGRAM TEMPLATE statement

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