SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-14
Determining the Organization of the Physical File
Creating Entry-Sequenced Tables
Entry-sequenced files are designed for sequential access. They consist of variable-
length records. New records are always appended to the end of the file; as a result, the
records in the file are arranged physically in the order in which they were added to the
file. Existing records can be updated, but they cannot be deleted. A user performing
update operations can update rows but cannot delete them and cannot lengthen or
shorten values in varying-length columns (VARCHAR, NCHAR VARYING).
This example creates an entry-sequenced table. This table is nonaudited, and the
primary extent is preallocated to ensure enough space.
>> CREATE TABLE \SYS1.$VOL3.LOGS.TRANSEQ
+> (TRANSEQ_NUM NUMERIC (10) NO DEFAULT NOT NULL,
+> CLASS_CODE PIC 9(4) DEFAULT SYSTEM,
+> CLASS_STATUS NUMERIC (6) DEFAULT SYSTEM,
+> STATUS_MSG PIC X(45) DEFAULT SYSTEM)
+> EXTENT (10000,1000)
+> CATALOG \SYS1.$VOL3.ADMIN
+> ALLOCATE 1
+> NO AUDIT
+> ORGANIZATION ENTRY SEQUENCED;
--- SQL operation complete.
Creating Relative Tables
Relative files consist of fixed-length physical records accessed by relative record
number. A record number is an ordinal value and corresponds directly to the record’s
position in the file. The first record is identified by record number zero. Succeeding
records are identified by ascending record numbers in increments of one.
You can refer to specific records within a relative table either by their primary key
(relative record number) or by the content of other key fields denoted by an index, such
>> CREATE VIEW =REPORDS
+> AS SELECT SALESREP,ORDERNUM,DELIV_DATE,ORDER_DATE
+> FROM =ORDERS
+> CATALOG =MCAT
+> FOR PROTECTION;
--- SQL operation complete.
>> CREATE INDEX =XORDCUS
+> ON =ORDERS (CUSTNUM)
+> KEYTAG “OC”
+> EXTENT (100,50)
+> BLOCKSIZE 2048
+> MAXEXTENTS 24
+> ICOMPRESS
+> CATALOG =MCAT;
--- SQL operation complete.
Example 5-1. Creating a Table and Dependent Objects (page2of2)