SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
F-8
File Organizations
File Organizations
SQL DDL statements create and modify tables and indexes and the physical Guardian
files that hold tables and indexes. To select parameters for your DDL statements, you
must be familiar with the three physical file organizations available for SQL tables:
key-sequenced, entry-sequenced, and relative.

In key-sequenced files, records are stored in sequence by primary key or
clustering key. The key can be supplied by the user, generated by the system, or
built from values supplied by the user and a value generated by the system. You
cannot update columns in a primary or clustering key.
You can insert, update, or delete data in rows, shorten or lengthen values in
varying-length character columns, and alter table definitions to add columns. You
can also add, move, or drop partitions.
Tables are often stored in key-sequenced files, and indexes are always stored in
key-sequenced files.

In entry-sequenced files, each new record is added to the logical end of the file.
The primary key is a system-generated record address. You can add or update
rows, but you cannot delete them. You cannot shorten or lengthen values in
varying-length character columns and you cannot alter table definitions to add
columns. You can add or move partitions, but you cannot drop partitions.

In relative files, records are stored at relative record locations specified by either
the user or the file system. The primary key is the relative record number. You can
insert, update, or delete rows, and you can shorten or lengthen values of
varying-length character columns. You can alter a table definition to add columns if
the original record length defined for the table is large enough to include the added
columns.
Guardian files that do not contain SQL objects but that have key-sequenced,
entry-sequenced, or relative file organization are also called Enscribe files or structured
files.
A fourth type of Guardian file—unstructured—is also used on NonStop systems, but
never for SQL tables or indexes. SQL programs in Guardian files are stored in
unstructured files. Edit files—text files that can be read by the EDIT or TEDIT text
editors and by many other Guardian utilities—are unstructured files with file code 101.
(OSS users can convert files created with the vi text editor to EDIT files with the
CTOEDIT command described in the Open System Services Programmer's Guide.)
SLACK Sets percent of slack in blocks if not specified by DSLACK or
ISLACK. Default is 15 percent.
TABLECODE Sets tablecode. Default is 0.
VERIFIEDWRITES Controls verification of writes to disk. Default is no verification.