SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Because the locks used in Step 1 of the operation are exclusive, they have no special priority over
other locks that can also be issued on the objects. Therefore, to enable the exclusive locks required
by these functions, you might need to manage the application activity as follows:
1. During Step 1, do not compile programs that require access to the metadata involved for
update or that refer to affected objects.
2. During Step 2, you can resume application transaction activity.
3. During Step 3, quiesce application transaction activity so that locks are not in contention.
These situations can arise during the operation of long-running utility functions:
• For large tables, audit trail space can be exceeded during the course of the operation, resulting
in termination of the operation and backout by the TMF subsystem. This condition is minimized
if you allow NonStop SQL/MX to manage TMF transactions.
NOTE: HP recommends that you do not initiate a user-defined TMF transaction for long-running
DDL operations. Utility operations are not supported in user-defined TMF transactions.
• If the operation cannot acquire the exclusive lock when required, NonStop SQL/MX terminates
the operation abnormally after a predetermined period of time. Remember that the operation
requires the simultaneous availability of all file labels that must be changed during the
operation. The lock timeout value is currently 60 seconds and cannot be changed.
In a similar way, certain other statements or commands present concurrency issues that can affect
the result of the operation. When you are duplicating, backing up, or moving data from one object
to another, these functions do not require sustained exclusive access to the source objects. The
only exclusive access involved is similar to that required in Step 3 at the end of the function. During
a DUP operation, you cannot perform DML operations or utilities that update the data being
duplicated. You can, however, perform read operations.
Keeping Statistics Current
When you update statistics, information about a table is updated in the histogram tables so that
the information more accurately represents the current content and structure of the SQL/MX
database.
UPDATE STATISTICS collects and generates physical and logical statistics on SQL objects. Physical
statistics refer to the index level, nonempty block count, and EOF for partitions of SQL/MX or
SQL/MP tables and indexes. Logical statistics refer to high and low values in columns, unique
entry counts in NonStop SQL/MP, and histogram information in NonStop SQL/MX.
To generate SQL/MX physical and logical statistics on SQL/MX objects, run SQL/MX UPDATE
STATISTICS. To generate optimal physical and logical statistics on SQL/MP tables, first run SQL/MP
UPDATE STATISTICS to update SQL/MP physical statistics, including index levels, and then run
SQL/MX UPDATE STATISTICS.
For more information, see the SQL/MX Reference Manual.
For more information on when and why you should update statistics, see the SQL/MX Query
Guide.
Managing SQL/MX Buffer Space
This subsection contains this information:
• “How DP2 Manages and Reuses Query Plan Fragments” (page 295)
• “Causes and Symptoms of Query Plan Fragment Reuse Failures” (page 296)
• “Reduction of Plan Fragment Size for Unique Queries” (page 296)
• “Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures” (page 296)
294 Enhancing SQL/MX Database Performance