This Db2 for z/OS News from the Lab blog entry was originally published on 2019-04-19.

By Tammie Dang, Euna Park, and Jennie Chang.

Db2 12 for z/OS introduced the capability to specify resource limits for static SQL statements, and APAR PH07658 recently introduced the following resource limit facility (RLF) usability enhancements in Db2 12:

• A new Db2 capability that automatically refreshes the in-memory resource limit tables
• The ability to specify resource limit tables that are already active when issuing the START RLIMIT command
• A new SCOPE(GROUP) keyword in the START RLIMIT command

Resource limits for static SQL statements:

In previous Db2 versions, limits specified in the resource limit facility applied only to dynamic SQL statements. With Db2 12, you can now specify reactive governing for static SQL statements by adding new rows in resource limit tables. This enables you to cancel any poorly performing SQL statements that consume too many resources. You can set certain RLFENABLE subsystem parameter values to indicate whether the resource limits apply to dynamic SQL statements only, static SQL statements only, or all SQL statements.

Automatic refresh capability:

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 now detects the resource limit changes and automatically refreshes the in-memory resource limit tables with these changes. The new limits that you specify apply to any subsequent new threads. Existing threads continue to use their original limits until they pass certain internal checkpoints.

Before the introduction of this automatic refresh capability, in-memory resource limit tables were not updated with changes in active resource limit tables until the resource limit facility was restarted. As a result, the changes did not take effect immediately for subsequent threads. This enhancement optimizes the process for updating in-memory resource limit tables and ensures that new resource limits are applied to new SQL threads.

Starting resource limit tables that are already active:

When issuing a START RLIMIT command, you can now specify the two-character ID of a set of resource limit tables that are already active. You no longer need to first stop active tables with the STOP RLIMIT command before starting them again.

The SCOPE(GROUP) keyword in START RLIMIT:

If you need to start the resource limit facility for an entire data sharing group, you can now add the SCOPE(GROUP) keyword to the START RLIMIT command for the command to take effect on all members, rather than individually issuing it on each member. You can still issue the command for a single member by selecting LOCAL as the SCOPE option. If the SCOPE option is not specified in the command, LOCAL is used as the default value.

Tammie Dang and Euna Park are software developers for Db2 for z/OS. Jennie Chang is a technical writer for Db2 for z/OS.

#Db2Znews