HP 3000 Manuals

ALLBASE/SQL Database Administration Guide

Creating Hash Structures 

You create a hash structure at the same time you create a table, using
the CREATE TABLE statement, as in the following syntax.

       [PUBLIC    ]
CREATE [PUBLICREAD] TABLE [Owner.]TableName [LANG = TableLanguageName]
       [PRIVATE   ]

({ColumnName ColumnDataType [LANG = ColumnLanguageName] [NOT NULL]}

[,...]) [UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]

[IN DBEFileSetName]

Refer to the ALLBASE/SQL Reference Manual for complete syntax and
semantics.

The following sequence of statements shows how to create the
PurchDB.Vendors table using a hash structure.  Commands to create and add
a dedicated DBEFile to contain the hash structure are also shown, since
there must be sufficiently large empty DBEFiles of type TABLE or MIXED
available when you issue the CREATE TABLE statement in creating a hash
structure:

     isql=> BEGIN WORK;

     isql=> CREATE DBEFile PurchHashF1
     > WITH PAGES = 120, NAME = 'PurchHF1',
     > TYPE = TABLE;

     isql=> ADD DBEFile PurchHashF1
     > TO DBEFILESET PurchFS;

     isql=> CREATE PUBLIC TABLE PurchDB.Vendors
     > (VendorNumber      INTEGER           NOT NULL,
     >  VendorName        CHAR(30)          NOT NULL,
     >  ContactName       CHAR(30),
     >  PhoneNumber       CHAR(15),
     >  VendorStreet      CHAR(30)          NOT NULL,
     >  VendorCity        CHAR(20)          NOT NULL,

     >  VendorState       CHAR(2)           NOT NULL,
     >  VendorZipCode     CHAR(10)          NOT NULL,
     >  VendorRemarks     VARCHAR(60) )
     > UNIQUE HASH ON (VendorNumber) PAGES = 101
     > IN PurchFS;

     isql=> COMMIT WORK;

Note that all these statements are shown as part of the same transaction.
This ensures that the DBEFiles you create will not be used by any other
transaction before the hash structure is created.

MPE/iX 5.5 Documentation