Most modern application development is done using dynamic
SQL. But some features work only with static SQL and others only with dynamic
SQL. DB2 12 for z/OS delivers functionality that minimizes the difference
between static and dynamic SQL.
Dynamic
plan stability brings the plan stability feature of static
SQL to dynamic SQL. With plan stability for static SQL, you can use the
PLANMGMT parameter of REBIND to save off old access paths that can be switched
back to active if the new access paths are inefficient for any reason.
DB2 12 introduces dynamic plan stability, which is a
little different but should prove to be quite useful. You can choose to
stabilize dynamic SQL access paths by storing them in the DB2 system catalog. DB2
will not just automatically start to capture all dynamic access paths. There
are options for selecting which queries to stabilize, so you do not have to
store all dynamic SQL. This is controlled by the command:
-START
DYNQUERYCAPTURE
When a dynamic statement is run, DB2 will look in the
dynamic statement cache first. If it is not found there, DB2 will look in
the system catalog before resorting to a full prepare. This is particularly
useful when statements are flushed from the DSC and a prepare would otherwise
be required.
You can label a set of stabilized SQL statements into a
group known as a stabilization group. This makes it easier for DBAs to track
and manage stabilized queries.
Click here for more details on Dynamic Plan Stability.
So dynamic plan stability can make your dynamic SQL more
static. But there is another new DB2 12 capability that can make your static
SQL more dynamic: static Resource Limit Facility (RLF). The RLF, heretofore,
could only be used to govern dynamic SQL statements. RLF tables, manipulated by
DBAs, contain limits that make sure that dynamic SQL statements do not consume
too many resources such as CPU, I/O, locks, etc. This enables improperly tested
SQL or poor resource planning from disrupting performance.
But dynamic SQL is not the only type of SQL that could
cause performance issues; static SQL transactions can benefit from the same
controls. DB2 12 extends the RLF to support static SQL statements thereby
improving the ability to avoid problem applications from dominating your system’s
resource consumption.
Specifically, you can set up reactive governing for static
SQL statements by adding new rows in resource limit facility tables. Static SQL
statements will be governed by rows where RLFFUNC='A' (for DSNRLSTxx tables)
and RLFFUNC='B' (for DSNRLMTxx tables).
You can control whether resource limits apply to dynamic
only, static only or all SQL statements using the DSNZPARM RLFENABLE.
Acceptable values are DYNAMIC, STATIC, or ALL and the default is
DYNAMIC. Furthermore, you can specify the default resource limit actions for
static SQL statements by setting two DSNZPARMS:
- RLFERRSTC for local statements
- RLFERRDSTC for remote statements
In each case, the acceptable values are NOLIMIT, NORUN, or
a number between 1 and 500000. You use NOLIMIT to let any static SQL
statement that does not correspond to a row in the resource limit table run
freely. Alternately, NORUN indicates that any static SQL statement that does
not correspond to a row in the resource limit table is prevented from running.
Finally, if you specify a number between 1 and 500000, that will be the number
of service units to use as the default resource limit. If the limit is
exceeded, the SQL statement is terminated.
If you need more details on setting up resource limits in
general, you can find that information in the IBM manuals and online here.
But the bottom line is that DB2 12 gives users more and better options for managing both their dynamic and static SQL performance. And that is definitely a good thing!
