SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-14
Using Null Values
The FREE RESOURCES statement is usually more efficient than CLOSE CURSOR
unless only a small percentage of defined cursors are active.
Cursors and Performance
When you use a cursor, the sequential block buffer may be invalidated frequently if
these two conditions are true:
•
The execution plan for the cursor uses RSBB or VSBB to access a table or view
from within a process.
•
The process also performs insertions, updates, or deletions against the same table
or view.
The result can be poor performance.
To improve performance, use the same cursor to do the updates or deletions. If you
must mix cursor retrievals (using sequential block buffering) with insertions into the
same table or view, disable sequential block buffering by using a CONTROL
TABLE . . . SEQUENTIAL READ OFF directive. For more information on this directive,
see the SQL/MP Reference Manual.
Using Null Values
A null value is a special symbol, independent of data type, that represents an unknown
or inapplicable value. A null value indicates that an item has no value.
A column that allows null values can be empty at any row position. In SQL, such a
column has two extra bytes associated with it in each row. A minus one (-)1 stored in
those two bytes indicates that the column is empty for that row. Unless a column
definition includes the NOT NULL clause to prohibit nulls or the column is part of the
primary key of the table, nulls are allowed.
Unless a column definition includes either the DEFAULT clause to specify some value
or the NO DEFAULT clause to prohibit any default value, a null value is used as the
default value. The default value for a column is the value the system inserts in a row
when an INSERT statement omits a value for a particular column or when a column is
added to a table.
Various scenarios exist in which a row in a table might contain no value for a specific
column, as for example, the following:
•
A database of telemarketing contacts might have AGE columns empty if contacts
did not give their age.
•
An order record might have a DATE_SHIPPED column empty until the order is
actually shipped.
•
An employee record for an international employee might not have a social security
number.
Note. A FREE RESOURCES statement is required for nonaudited tables to release locks.