Starting and stopping resource limit tables

You can create several resource limit tables and start and stop them as needed to support the type and amount of processing that occurs at different times.

About this task

At installation time, you can specify a default set of resource limit tables to be used each time that Db2 is restarted. The RLF subsystem parameter value controls the automatic start, and the value of the RLFTBL subsystem parameter specifies the tables to start when Db2 starts.

Db2 reads resource limit tables into memory to use for START RLIMIT command processing. When you update the active set of resource limit tables, Db2 detects the resource limit changes and automatically refreshes the in-memory resource limit tables with these changes. The new limits apply to any subsequent new threads. Existing threads continue to use their original limits until they pass certain internal checkpoints. For example, an SQL statement that builds a result table and fetches from it passes those checkpoints at intervals that might range from moments to hours. As a result, a change to a resource limit might not stop an active statement within the time that you expect.

Only one pair of resource limit tables, containing one table of each type (DSNRLSTxx or DSNRLMTxx ), can be active at any time.

Important:

In a data sharing group, automatic refresh of an in-memory resource limit table can occur only when the SQL statement that updates a resource limit table runs on a data sharing member on which that resource limit table is active.

For example, suppose that a data sharing group has two members: DB2A and DB2B. Resource limit table DSNRLST01 is active on data sharing member DB2A. If an application that is connected to DB2A performs an update on resource limit table DSNRLST01, automatic refresh to the in-memory table can occur.

However, if resource limit table DSNRLST01 is active on member DB2A, but the application that performs the update to DSNRLST01 is connected to member DB2B, automatic refresh cannot occur. After the application updates table DSNRLST01, you need to issue command -START RLIMIT ID=01 on member DB2A to make the change to the in-memory resource limit table DSNRLST01.

To start and stop limit tables:

• Start resource limit tables by issuing START RLIMIT commands.
  For example, you can issue the following command:

  

  -START RLIMIT ID=xx

  xx is the two-character identifier in the names of the set of tables that you want to start (DSNRLSTxx, DNSRLMTxx, or both if they exist).

  The specified limits apply to all subsequent threads.

• When the resource limit facility is already active, restart the resource limit facility by issuing START RLIMIT commands and specifying the identifier of a set of tables.
• Issue DISPLAY RLIMIT commands to determine the active set of resource limit tables.
• Stop resource limits by issuing STOP RLIMIT commands.
  For example, you can issue the following command:

  

  -STOP RLIMIT ID=xx

  xx is the two-character identifier in the names of the set of tables that you want to stop.

  All resource limits are stopped.

• Issue CANCEL THREAD commands to stop an active job that does not pick up the new limit when you restart the resource limit facility.

Example

You can use different resource limit tables for the day shift and the night shift. Assume that the DSNRLST01 and DSNRLST02 resource limit tables contain the following rows.

Table 1. Example resource limit table values for the day shift, in DSNRLST01

AUTHID	PLANNAME	LUNAME
BADUSER		LUDBD1
ROBYN		LUDBD1
		LUDBD1

Table 2. Example resource limit table values for the night shift, in DSNRLST02

AUTHID	ASUTIME	LUNAME
BADUSER	0	LUDBD1
ROBYN	NULL	LUDBD1
	50000	LUDBD1

In this example, you might issue the following command when the day shift begins:

-START RLIMIT ID=01

Then, when it is time for the night shift to begin, you might issue the following command to switch to the other table:

-START RLIMIT ID=02

After you issue this command, new threads from the ROBYN authorization ID from LUDBD1 can issue SQL statements without limits. Threads that already existed at the time that you issued the command continue under the limits that are specified in the DSNRLST01 table until the threads pass certain internal checkpoints.