SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Understand the Schema Ownership Rule
Starting with the SQL/MX Release 3.1, new security features are introduced. For more information
on these features, see the SQL/MX Reference Manual.
Avoid Using Duplicate Catalogs on Multiple Distributed Nodes
If you create catalogs with the same name on two or more nodes, each node can access only the
objects in the local catalog. You cannot join the tables or indexes in the same-named catalogs on
different nodes.
Understanding SQL/MX Table and File Structures
A relational database consists of two structural levels, the logical and the physical:
• The logical level includes the tables and views you access directly through SQL statements.
When you request an operation on the database or a display of its contents, you work with
the database at the logical level.
• The physical level underlies the logical level and is composed of physical files on disks.
Each SQL/MX table and index, and each partition of a table or index, is implemented as a pair
of Guardian physical files representing:
• A data fork, where the user data resides
• A resource fork, where the runtime metadata resides
If a table or index is not partitioned, then it has just one data fork and one resource fork.
Table and index files are managed by the SQL/MX file system and are accessed implicitly through
the DP2 disk process. When a table or index is dropped, both its data fork and resource fork are
also dropped.
The data you insert into tables and views is stored in these underlying files. Tables and indexes
are associated with their corresponding physical files through entries in the system metadata. Views
are associated with physical files through their underlying tables.
When you create a table, you establish the characteristics of the underlying file by specifying them
as parameters in the CREATE TABLE statement. That is, you create the file implicitly through this
statement rather than directly through an explicit file-creation statement.
Data transfers to and from the files are done in terms of logical records and key fields within those
records. Each file has a unique clustering key associated with it, which contains a unique value
used to order and identify records in the file.
The large block support feature enables you to create tables on physical disk blocks of size 32768
bytes (32 KB) instead of the default size of 4096 bytes (4 KB). You can specify the disk block size,
while creating a table, using the CREATE TABLE command. Increasing the disk block size allows
packing more records in each disk block and is convenient for reading multiple records sequentially.
However, the large block support feature might not be useful for random access, where only one
or a few records are accessed at a time. Therefore, you must select a disk block size depending
on the type of most frequently used queries to the database. By default, the table is created with
4096 bytes (4 KB) disk block size.
SQL/MX tables use the key-sequenced file structure. Other table and file structure types are not
supported by NonStop SQL/MX.
“Using Keys in SQL/MX Tables and Indexes” (page 30) describes the key types used with SQL/MX
tables and indexes. “The Key-Sequenced File Structure” (page 31) discusses the structure of
key-sequenced files. An understanding of the key-sequenced file structure can help you plan the
best use of disk storage space when sizing your SQL/MX database, implementing economical
table-access methods, and analyzing various performance trade-offs. This understanding is also
essential for anyone using the FCHECK utility to operate on physical file structures.
Understanding SQL/MX Table and File Structures 29