SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-13
Checking Data Integrity
•
Perform FUP RELOADs on tables and indexes after an unusually large number of
INSERT, UPDATE, and DELETE operations have been performed on them.
Partitions are automatically reloaded after many partition management operations
have been performed on them.
Checking Data Integrity
NonStop SQL/MX provides data integrity checking when constraints are defined for a
table. When a row is added or altered, NonStop SQL/MX verifies that the new data
satisfies all constraints.
You can check data integrity within program code or with the SQL/MX constraint
mechanism. Each method has benefits and performance issues you should consider
for your application. For more information, see the information on creating constraints
in Section 7, Creating an SQL/MX Database.
Evaluate your application to determine the best use of data checking: constraints
versus program code.
The issues involved in having data integrity checks in program code or defined in the
database through constraints are:
•
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 NonStop SQL/MX. This checking reduces
unnecessary SQL 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 programs so that applications
move easily from one set of users to another.
°
Constraints simplify the change process to a simple, online process. If you add
one constraint, the system immediately applies the constraint to all subsequent
transactions. The constraint creation process also checks the existing table to
ensure that all existing rows conform to the new constraint.