AutoPrep Facility
The purpose of AutoPrep is to replace dynamic command sequences with
a static (compiled) sequence when a given SQL statement is executed frequently.
The AutoPrep control file names the dynamic packages for which AutoPrep
must be enabled and defines the execution frequency that will trigger AutoPrep.
When the AutoPrep trigger has been reached for a particular SQL statement
text, a DB2 prep is automatically performed by the AutoPrep server. Subsequently,
the SQL statement will execute in static mode, using the AutoPrep package.
Benefits
-
AutoPrep avoids the (significant) CPU overhead associated with intensive
dynamic statement prepare. Since PC database access, e-business and ERP applications
often execute entirely
using dynamic SQL, the AutoPrep facility will drastically reduce the
CPU load and response times in such applications.
-
AutoPrep avoids DB2 catalog contention and deadlocks.
-
AutoPrep implicitly provides compiled access for applications that are
unable to generate packages, for example, PC applications that access
the DB2/VM database.
Authorization
The new authorization scheme allows to grant access to each logical
access function within SQL/MF. Authorizations are granted to groups and
individual users are connected to a named group. Access can be granted
in public or private mode. Private access limits access to the monitor
data collected for that particular user.
For example:
-
a DBA user may be connected to a group that grants public access to all
SQL/MF functions
-
developers may be connected to a group that allows private access to the
monitor tables and disables system related functions (e.g.issuing DB2 commands)
Catalog Navigator
The Catalog Navigator is part of the Objects menu. The facility
provides interactive access to all DB2/VM catalog tables. Using the Related
List concept, dependencies between objects in different catalog tables
(for instance: all indexes for a table, all grants on a package etc.) can
be viewed easily, without the need of submitting ad hoc SQL statements.
Command
Recorder Enhancements
-
The command recorder now stores:
-
the number of dataspace page faults during an SQL statement
-
the number of dispatcher calls during an SQL statement
-
the statement elapsed time
-
The new recorder level 4 stores all hostvariables associated with an SQL
statement.
-
Previously, the recording facility was always initiated on a periodical basis.
The enhanced facility provides for "recording on demand".
Using the MONITOR RECORD command, recording can be initiated for named
users, packages and terminals.
Lockwait
Recorder Enhancements
-
The Lockwait recorder shows the duration of a lock.
-
All hostvariables are included in the locked statement text.
Runstats Enhancements
-
The Runstats can be kept in a DB2 table. The RunStats report then is able
to show statistics by package, date and time.
-
The Runstats function allows to view all recorder entries, if any,
for a given package section.
New Statistics
-
Referential integrity "plans" are included in the command statistics
table
-
Following statistics have been added to the package statistics table:
-
user CPUtime
-
checkpoint wait time
-
total elapsed time
-
maximum elapsed time
-
total number of rows processed
Notification
Enhancements
-
The LOGUSAGE notification criterion sends an alert when the DB2/VM
log is N percent full. If the destination of the alert is a PROP machine,
appropriate action can be taken.
-
The ESCALATE criterion sends an alert when a lock escalation occurs.
-
The DYN_COST criterion sends an alert when a running dynamic SQL statement
exceeds a defined SQL cost.
New Logging Criteria
-
The LOG EXCLUDE statement prevents exception logging for named packages.
-
The LOG PERIOD statement limits exception logging to a defined
period.
-
Multiple SQLCS CONFIG files, in association with the MONITOR CONFIG
command, allow to establish different SQL/MF configurations for different
periods of time.
Governor Enhancements
-
The Governor can be disabled during weekends.
-
The new MAX_DYN_COST restriction prevents execution of dynamic SQL
statements that exceed a defined SQL cost.
UAP3 Enhancements
Following new items are presented to the SQLMUAP3 exit:
-
the agent's R/W state
-
the agent location (terminal name)
Table Editor
Enhancements
-
A generalized graphical output format is provided for all numerical columns
in all SQL/MF tables.
-
When a user modifies the layout of a monitor report, the modified
layout is saved as a "template" and reused on subsequent invocations of
that report.
-
The SQL statement text is now formatted by default.
-
Monitor Table column help is provided on all reports. Column help shows
the meaning of the columns displayed in the report.
SQLCSU Enhancements
-
Graphical (SQLPULSE-like) output can be obtained from the "command detail"
function.
-
Running SQL/MF components are visible in SQLCSU (if the SQLMF log database
is monitored).
New monitor commands
-
The SHOW IDBSPACE command allows a user process to monitor usage of
internal DBspaces by active agents. The command shows for each agent:
-
the active package name
-
the number of internal DBspaces used by the current package section
-
the number of internal DBspaces used by the LUW
-
The ENDLINKS command can be used prior to DB2/VM shutdown to terminate
active pseudo-agents, that is, users that have an APPC link to the
database without being in LUW.
Miscellaneous
Enhancements
-
The Dynamic Alias Facility allows end-users to execute dynamic SQL
statements without having authorities on the tables referenced by the
statement. The facility activates an "alias userid" during the PREPARE phase
of the dynamic statement.
-
The SET ISOLATION configuration command allows to override the
isolation level for specified packages during package execution.
-
Support is provided for VM/ESA alternate userids, established using Diagnose x'D4'.
-
Data can be extracted from the Command and Lockwait Recorder files using
an EXEC supplied by SQL/MF.
-
A generalized hardcopy function is provided via the PA3 key.
-
SQL/MF provides an EXEC to reorganize the Exception table and the System
Monitor tables.