ALLBASE/SQL Databases

The largest unit in ALLBASE/SQL is the DBEnvironment, which can be seen logically as a collection of database objects or physically as a group of files. Objects are database structures.

Logical Concepts

Logically, the DBEnvironment is a structure which contains one or more relational databases. In ALLBASE/SQL, a database is a set of tables, views, and other objects that have the same owner.

The data in a relational database is organized in tables. A table is a two-dimensional structure of columns and rows:

                           The Parts Table
   ----------------+------------------------------+----------
   PARTNUMBER      |PARTNAME                      |SALESPRICE
   ----------------+------------------------------+---------- _
   1123-P-01       |Central Processor             |    500.00  |
   1133-P-01       |Communication Processor       |    200.00  |- rows
   1143-P-01       |Video Processor               |    180.00 _|
         |                       |                       |
         +-----------------------+-----------------------+
                                 |
                              columns

Often a table is referred to as a relation, and a row as a tuple. You can also think of a row as a record, and a column as a field in a file, or table.

A view is a table derived by placing a "window" over one or more tables to let users or programs view only certain data. A view derived from the Parts table shown above might look like this:

                   The PartsID View
   ----------------+------------------------------
   PARTNUMBER      |PARTNAME
   ----------------+------------------------------
   1123-P-01       |Central Processor
   1133-P-01       |Communication Processor
   1143-P-01       |Video Processor

The owner of a table or view can be one of the three following entities:

Individual as identified by the DBEUserID, which is the login name. An individual who logs in as Wolfgang is known to ALLBASE/SQL as Wolfgang.
Authorization group, a named collection of individuals or other groups. Wolfgang might be part of a group named Managers. A group must be created explicitly by using the CREATE GROUP statement.
Class, a name that identifies a user-defined abstraction, such as a department or a function. Wolfgang might use tables owned by a class called Marketing. A class is created implicitly when you create objects that have a class name as owner name.

Refer to Chapter 2 “Using ALLBASE/SQL” in this manual and to the chapter "Logical Design" in the ALLBASE/SQL Database Administration Guide for additional information about authorization groups and classes.

To use data in a database, you need to specify the names of the tables and views you need. You must also specify the owner name associated with the table or view unless you own it (or you have used the ISQL SET OWNER command). When accessing the Composers table, Wolfgang needs to specify only Composers. However, when accessing the quotas table, he needs to specify Marketing.Quotas because Marketing owns the Quotas table.

You also need the proper authority to access data. An authority is a privilege given to a user to perform a specific database operation, such as accessing certain tables and views and creating groups or tables. ALLBASE/SQL uses authorities to safeguard databases from access by unauthorized users. In the example above, before Wolfgang can access the Quotas table, he must be granted the authority to do so by the owner of the table.

If you have been granted the proper authorization, you access databases by first connecting to the DBEnvironment in which they reside:

CONNECT TO 'DBEnvironmentName'

Physical Concepts

Physically, the DBEnvironment is a collection of files for one or more logical databases.

A DBEFile is an HP-UX file. Most files in a DBEnvironment are DBEFiles. Data in the tables of logical databases is stored in one or more DBEFiles. Indexes are also stored in DBEFiles; an index is a structure that ALLBASE/SQL can use to quickly find data in a table.

A DBEFileSet is a collection of DBEFiles. You associate physical storage with a DBEFileSet by adding DBEFiles to the DBEFileSet. Each DBEFileSet can have more than one DBEFile, but a single DBEFile cannot contain data for more than one DBEFileSet.

When you create a table, you can specify the DBEFileSet with which the table and its indexes will be associated. This causes physical storage space for the table and indexes to be allocated from the DBEFiles associated with the specified DBEFileSet. Title not available illustrates the relationships among tables, DBEFiles, and DBEFileSets.

Figure 1-2 How Tables, DBEFiles, and DBEFileSets Are Related

A DBEFileSet specifies the files that contain data for one or more tables associated with the DBEFileSet. These tables do not have to be in the same database. Title not available illustrates that, while a DBEFileSet can contain data for all the tables in a database, a DBEFileSet can also contain data for some of the tables in a database, or for tables in more than one database. Thus DBEFileSets offer a way to allocate data storage independently of how users think about the data.

Figure 1-3 Databases and DBEFileSets

A DBEnvironment, illustrated in Title not available, houses the DBEFiles for one or more ALLBASE/SQL databases, plus the following, which contain information for all databases in the DBEnvironment:

A DBECon file. This file contains information about the DBEnvironment configuration, such as the size of various buffers and other startup parameters. The name of the DBECon file is the same as the name of the DBEnvironment.
A system catalog. The system catalog is a collection of tables and views that contain data describing DBEnvironment structure and activity. The parts of the system catalog necessary for DBEnvironment startup reside in a DBEFile known by default as DBEFile0. All system catalog DBEFiles are associated with a DBEFileSet called SYSTEM.
One or two log files. A log file contains a log of DBEnvironment changes. ALLBASE/SQL uses log files to undo (roll back) or redo (roll forward) changes made in the DBEnvironment. The log files are known by default as DBELog1 and DBELog2.

Figure 1-4 Elements of an ALLBASE/SQL DBEnvironment

Most database users need not be concerned with the physical aspects of ALLBASE/SQL databases beyond knowing which DBEnvironment contains the databases they want to access.

ALLBASE/SQL Databases

Technical documentation

» Table of Contents

» Index

Logical Concepts

Physical Concepts