WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES table function - List of workload occurrences

A workload occurrence is a specific database connection whose attributes match with the definition of a workload and hence is associated with or assigned to the workload.

Syntax

>>-WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES--(--service_superclass_name--,-->

>--service_subclass_name--,--dbpartitionnum--)-----------------><

The schema is SYSPROC.

Table function parameters

service_superclass_name

An input argument of type VARCHAR(128) that specifies a valid service superclass name in the currently connected database. If the argument is null or an empty string, the data is retrieved for all the superclasses in the database for which the other parameters match.

service_subclass_name

Target service subclass for the workload occurrence. Any work submitted by this workload occurrence will run in this service subclass under the target service superclass with the exception of activities that are mapped, or remapped, to a different subclass.

dbpartitionnum

An input argument of type INTEGER that specifies a valid partition number in the same instance as the currently connected database. Indicate -1 for the current database partition, or -2 for all database partitions. If the null value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES function.

Example

If an administrator would like to see what workload occurrences are running on the system as a whole, the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES function can be called with a null value or an empty string for service_superclass_name and service_subclass_name, and -2 for dbpartitionnum.

  SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
         SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_NAME,
         SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
         SUBSTR(CHAR(COORD_PARTITION_NUM),1,4) AS COORDPART,
         SUBSTR(CHAR(APPLICATION_HANDLE),1,7) AS APPHNDL,
         SUBSTR(WORKLOAD_NAME,1,22) AS WORKLOAD_NAME,
         SUBSTR(CHAR(WORKLOAD_OCCURRENCE_ID),1,6) AS WLO_ID
  FROM TABLE(WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES
        (CAST(NULL AS VARCHAR(128)), CAST(NULL AS VARCHAR(128)), -2))
         AS SCINFO
  ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME, PART, APPHNDL,
         WORKLOAD_NAME, WLO_ID

Assuming that the system has four database partitions and is running two workloads at this time, the preceding query would produce a result like the following output:

SUPERCLASS_NAME     SUBCLASS_NAME      PART COORDPART ...
------------------- ------------------ ---- --------- ...
SYSDEFAULTMAINTENAN SYSDEFAULTSUBCLASS 0    0         ...
SYSDEFAULTSYSTEMCLA SYSDEFAULTSUBCLASS 0    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3    0         ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3    0         ...

... APPHNDL WORKLOAD_NAME          WLO_ID
... ------- ---------------------- ------
... -       -                      -
... -       -                      -
... 1       SYSDEFAULTUSERWORKLOAD 1
... 2       SYSDEFAULTUSERWORKLOAD 2
... 1       SYSDEFAULTUSERWORKLOAD 1
... 2       SYSDEFAULTUSERWORKLOAD 2
... 1       SYSDEFAULTUSERWORKLOAD 1
... 2       SYSDEFAULTUSERWORKLOAD 2
... 1       SYSDEFAULTUSERWORKLOAD 1
... 2       SYSDEFAULTUSERWORKLOAD 2

Usage note

The parameters have the effect of being ANDed together. That is, if one were to specify conflicting input parameters such as a service superclass SUP_A and subclass SUB_B such that SUB_B is not a subclass of SUP_A, no rows would be returned.

Information returned