NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
F-9
FILEINFO Command
must be familiar with the three physical file organizations available for SQL tables: key-
sequenced, entry-sequenced, and relative.
•
Key-sequenced file organization
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.
•
Entry-sequenced file organization
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.
•
Relative file organization
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 OSS Programmer's Guide.)
See the NonStop SQL/MP Installation and Management Guide for information about
choosing the most effective file organizations for your tables.
FILEINFO Command
FILEINFO is an SQLCI utility that displays information about the versions and physical
characteristics of tables, indexes, views, collations, Enscribe files, and OSS files.