SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-23
Checking Data Integrity
•
Generic locks provide a good solution for the application problem in which a table
lock is not acceptable, but the application needs so many locks in a transaction
that the number might exceed the maximum allowed by the system.
The application problem occurs because the disk process allows a maximum number
of locks per process on a partition. An application that examines a large number of
rows with the REPEATABLE ACCESS protocol can cause the disk process to escalate
row locks to a table lock; however, table locks are not acceptable to many applications.
The application can direct the disk process not to escalate row locks to a table lock by
specifying the CONTROL TABLE statement that includes the TABLELOCK OFF option.
Using this option, however, the application might generate an error from the disk
process if the disk process uses up the control block space for locks. The application
can use generic locking to acquire the needed locks with a reduced risk of exceeding
the lock limit.
Checking Data Integrity
SQL/MP provides data integrity checking when constraints are defined for a table.
When a row is added or altered, the SQL disk process verifies that the new data
satisfies any constraints.
Checking data integrity can be performed within program code or with the SQL
constraint mechanism. Each method has benefits and performance issues you should
consider for your application:
•
Data integrity checking in program code
°
Data checking performed in the requester before sending the data to the server
is the quickest method of data checking and reduces unnecessary server calls.
The requester checks data upon input to ensure the data conforms to certain
ranges coded into the requester.
°
Data checking can be performed within an application program or server
program before the data is sent to the disk process. This checking reduces
unnecessary disk process calls, but still requires the program code to have the
data range values.
°
Maintaining programs, requesters, or servers to programmatically check data
input can require additional programming time. In addition, your site must have
methods or programs to verify that the existing tables conform to the new data
checks.
°
Programs with hard-coded validity checking cannot move as easily from one
set of users to another as programs without hard-coded values.
•
Data integrity checking by constraints
°
Constraints can greatly enhance the flexibility of the programs so that
applications move easily from one set of users to another.