### SQL/MF Version 6 Release Notes

SQL/Monitoring Facility Version 6 includes the following new facilities and enhancements:

- AutoPrep Facility
- Enhanced Authorization scheme
- Catalog Navigator
- Command Recorder Enhancements
- Lockwait Recorder Enhancements
- Runstats Enhancements
- Notification Enhancements
- New Statistics
- New Logging Criteria
- SQLCSU Enhancements
- Governor Enhancements
- UAP3 Enhancements
- Table Editor Enhancements
- New monitor commands
- Miscellaneous Enhancements

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.