IMAGE/SQL: Issues and answers concerning SQL tables

Software

Papers & Training

Java

HP Partners

News & Events

Register for more info

2.3 How are SQL tables physically stored?

Key points

• ALLBASE/SQL allows a one-to-one, a one-to-many, or a many-to-many mapping between SQL tables and MPE files. Typically, customers use a many-to-many mapping.

• Physical files are known as DBEFiles. You use the CREATE DBEFILE and ADD DBEFILE statements to add physical files to a DBEFileSet:

  isql => CREATE DBEFILESET partsfileset;
  
  isql => CREATE DBEFILE parts1 WITH PAGES=253, NAME='parts1',
  > TYPE=mixed;
  
  isql => ADD DBEFILE parts1 TO DBEFILESET partsfileset;
  isql => commit work;
  

  There is virtually no limit to the number of DBEFiles that can be added to a DBEFileSet.

• When you use the CREATE TABLE statement, you specify the DBEFileSet that you want the table to belong to:

  isql => CREATE PUBLIC TABLE Parts.Vendors 
  > (PartNumber char(16),
  >  VendorNumber INTEGER)
  > IN partsfileset;
  isql => commit work;
  

  Rows for the table can be stored on any of the DBEFiles that have been added to the DBEFileset.

• B-tree indexes are always created in the same DBEFileset as the table.

• DBEFiles come in three flavors: TABLE, INDEX, and MIXED. Data rows for SQL tables can only be inserted onto TABLE or MIXED DBEFiles. Index data can only be inserted onto INDEX or MIXED DBEFiles. An error is returned if you try to insert data and there is no space available on any of the appropriate DBEFiles.

• To improve performance, you can put INDEX and DATA DBEFiles on different disc drives.

• DBEFiles can be created so that they dynamically expand. When you use the CREATE DBEFILE statement, you can specify an initial size, a maximum size, and an increment value (that is, the number of pages to add each time the file needs to expand).

Page last updated on November 29, 1995