DB2 10.5 for Linux, UNIX, and Windows

Taking corrective action using the default administration workload

The default administration workload SYSDEFAULTADMWORKLOAD is a special DB2-supplied workload definition that is not subject to any DB2® thresholds. Use this workload to take corrective action that cannot otherwise be performed, such as altering prohibitive threshold definitions that prevent all activities from running in a workload.

Before you begin

Use the SET WORKLOAD command (or the WLM_SET_CLIENT_INFO procedure) to assign a connection to the default administration workload SYSDEFAULTADMWORKLOAD.

Although you require no special authority to use the SET WORKLOAD command, you require ACCESSCTRL, DATAACCESS, DBADM, SECADM, or WLMADM authority to assign a connection to the default administration workload. Otherwise, SQL0552N is returned during workload assignment.

About this task

Because this workload is not affected by thresholds, it has limited workload management control and is not recommended for use in submitting regular day-to-day work.

Procedure

To assign a connection to the default administration workload, issue the SET WORKLOAD command as follows: 
SET WORKLOAD TO SYSDEFAULTADMWORKLOAD
When the command takes effect depends on when you issue it:
  • If you issue the SET WORKLOAD TO SYSDEFAULTADMWORKLOAD command before the connection to the database, after the connection is established, it is assigned to SYSDEFAULTADMWORKLOAD at the beginning of the first unit of work.
  • If you issue the SET WORKLOAD TO SYSDEFAULTADMWORKLOAD command at the beginning of a unit of work, after a connection to the database is established, the connection is assigned to SYSDEFAULTADMWORKLOAD when the first request that is not an sqleseti (Set Client Information) request is submitted.
  • If you issue the SET WORKLOAD TO SYSDEFAULTADMWORKLOAD command at the middle of a unit of work, after a connection is established, the connection is assigned to SYSDEFAULTADMWORKLOAD at the beginning of the next unit of work.
When a connection is assigned to SYSDEFAULTADMWORKLOAD, workload reassignment is performed at the beginning of the next unit of work if either of the following situations occurs:
  • You revoke SYSADM or DBADM authority from the session user. In this situation, SQL0552N is returned.
  • You issue a SET WORKLOAD TO AUTOMATIC command. This command indicates that the next unit of work should not be assigned to the SYSDEFAULTADMWORKLOAD workload and that a normal workload evaluation is to be performed at the beginning of the next unit of work. For more information, see Workload assignment.

Example

The following example shows how you can use the SYSDEFAULTADMWORKLOAD workload to take corrective action when no other corrective action is possible.

If you create a severely prohibitive concurrency threshold so that no activities can execute, because the threshold is always being exceeded, the same threshold can prevent you from correcting the problem. To be able to alter the prohibitive threshold, you must first set the workload so that the work runs in the default administration workload. Because activities running in this workload are not subject to thresholds, you can correct the problem and set the workload (for your ID) back to the default behavior.

The threshold that is the cause of the problem is created accidentally with the following statement. Concurrency should have been set to 100 but was set to 0. This threshold effectively prevents any activity from executing:

CREATE THRESHOLD PROHIBITIVE FOR DATABASE ACTIVITIES
   ENFORCEMENT DATABASE WHEN CONCURRENTDBCOORDACTIVITIES > 0
   STOP EXECUTION
Note: This statement is intended only to show you how a severely prohibitive threshold might be created. You should not issue this statement.

If you attempt to execute even just a simple SELECT statement, an error is returned, because concurrency is set to 0:

SELECT * FROM SYSCAT.TABLES

SQL4712N  The threshold "PROHIBITIVE" has been exceeded.  Reason code = "6".
SQLSTATE=5U026

Before you can take corrective action, you must set the workload to the default administration workload:

SET WORKLOAD TO SYSDEFAULTADMWORKLOAD

This statement can be issued only by someone with ACCESSCTRL, DATAACCESS, DBADM, SECADM, or WLMADM authority and causes any connection to be assigned to the SYSDEFAULTADMWORKLOAD workload, where activities are not subject to the prohibitive threshold.

The problem can now be corrected by altering the threshold so that activities can run: 

ALTER THRESHOLD PROHIBITIVE WHEN CONCURRENTDBCOORDACTIVITIES > 100 STOP EXECUTION

Once corrected, change the workload back so that the connection will no longer be assigned to SYSDEFAULTADMWORKLOAD but to whatever workload it was assigned to before:

SET WORKLOAD TO AUTOMATIC

The same SELECT statement used before should now complete successfully:

SELECT * FROM SYSCAT.TABLES

...

DB20000I  The SQL command completed successfully.