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.

If the resource limit facility is active and you restart it without stopping it, any jobs that are active continue to use their original limits, and all new jobs use the limits that are specified in the new table.

If you stop the resource limit facility while a statement is running, it runs with no limit, but its processing time continues to accumulate. If you later restart the resource limit facility, the new limit takes effect for an active job only when the job passes one of several 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 DNSRLMTxx ), can be active at any time.

Procedure

To start and stop limit tables:

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

    Begin general-use programming interface information.

    -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). End general-use programming interface information.

    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 an inactive set of tables.
    The resource limit facility is re-started and the limits in the specified tables are applied to all subsequent threads.
  • 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:

    Begin general-use programming interface information.

    -STOP RLIMIT ID=xx
    xx is the two-character identifier in the names of the set of tables that you want to stop. End general-use programming interface information.

    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:

Begin general-use programming interface information.
-START RLIMIT ID=01
End general-use programming interface information.

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

Begin general-use programming interface information.
-START RLIMIT ID=02
End general-use programming interface information.

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.