HP 3000 Manuals HP 3000 Manuals
Defining How Data is Stored and Retrieved
To create database objects, you use data definition statements to define
the following:
* Tables
* Views
* Indexes
* Constraints
* Procedures
* Rules
Creating a Table
When you define a table, use the CREATE TABLE statement to accomplish the
following tasks:
1. Establish an automatic locking mode and default access
authorities.
2. Name the table.
3. Describe the columns.
4. Identify a DBEFileSet for storage of its rows.
The following example contains numbers that refer to the list of tasks
shown above:
1 ---2---
| | |
CREATE PUBLIC TABLE PurchDB.Parts
(PartNumber CHAR(16) NOT NULL, ---
PartName VARCHAR(30), | --3
SalesPrice DECIMAL (10,2)) ---
IN WarehFS
|
4
You can also specify native language characteristics and integrity
constraints at both the table and the column level.
Choosing the Locking Mode and Default Access Authorities.
ALLBASE/SQL uses one of four locking modes for controlling access to data
in a table by different transactions. A transaction is one or more SQL
statements that together perform a unit of work. The locking modes are
as follows:
* PRIVATE mode allows only one transaction at a time to access a
table for reading or updating. Locking is done at the table
level. PRIVATE is the default mode.
* PUBLICREAD mode allows multiple transactions to read a table, but
only one to update it. Locking is done at the table level.
* PUBLIC mode allows multiple transactions to concurrently read and
update a table. Locking is done at the page level.
* PUBLICROW mode allows multiple transactions to concurrently read
and update a table. Locking is done at the row level, which
permits greater concurrency than PUBLIC mode.
ALLBASE/SQL automatically uses the locking mode in the table definition
whenever you access a table. You can use the LOCK TABLE statement to
override automatic locking. You can use the ALTER TABLE statement to
permanently change the implicit locking mode.
Tables created with PUBLICREAD, PUBLIC, and PUBLICROW options also have
the following initial authorities associated with them:
* A PUBLICREAD table can be read by anyone who can start a DBE
session.
* A PUBLICROW or PUBLIC table can be read and updated by anyone who
can start a DBE session.
A DBA or the table's owner can use the GRANT and REVOKE statements to
change these authorities.
The choice of PUBLICROW rather than PUBLIC mode may result in a
transaction's obtaining more locks, since each row must be locked
individually. For more information about the quantity of locking in
PUBLIC and PUBLICROW tables, refer to the section "Effects of Page and
Row Level Locking" in the "Physical Design" chapter of the ALLBASE/SQL
Database Administration Guide.
Naming the Table and Columns.
The name you assign to a table or column can be up to 20 bytes long and
is governed by the rules in the "Names" chapter.
Defining the Columns.
You enclose the column definitions in parentheses, separating multiple
column definitions with a comma. At least one column must be defined.
Each column is defined by a name and a data type.
Specifying Data Types.
Data types describe the kind of data that can be stored in a column.
ALLBASE/SQL has five numeric data types, two string data types, four
date/time data types, and four binary data types as follows:
* Numeric data types:
DECIMAL
FLOAT
REAL
INTEGER
SMALLINT
* Character string data types:
CHAR(n)
VARCHAR(n)
* Date/time data types:
DATE
TIME
DATETIME
INTERVAL
* Binary string data types:
BINARY(n)
VARBINARY(n)
LONG BINARY(n)
LONG VARBINARY(n)
When you define a column to be of a certain data type, ALLBASE/SQL
ensures that each value stored in the column is in the range for the data
type. Some data types (CHAR(n), VARCHAR(n), BINARY(n), VARBINARY(n),
LONG BINARY(n) and LONG VARBINARY(n)) require a column length. CHAR(n)
has a default length of 1; VARCHAR(n) does not. Other data types allow
the specification of a precision (DECIMAL, FLOAT) and a scale (DECIMAL).
Data types also affect the operations you can perform on data. The "Data
Types" chapter defines the attributes of each data type as well as how
the type affects various operations.
Specifying Column Options.
You can also specify a NOT NULL, DEFAULT, native language, or constraints
option for each column. The native language and constraint options are
discussed in separate sections below.
When you define a column as NOT NULL, ALLBASE/SQL ensures that it
contains no null values. NULL is a special data type that indicates the
absence of a value.
The DEFAULT option allows you to specify a default value for a column.
If the DEFAULT option was defined for a column and a value is not
specified when an INSERT statement is executed, ALLBASE/SQL inserts the
default value. Default values are of the following types:
* Constant
* NULL
* Current date and/or time
The following example specifies column options:
CREATE TABLE PurchDB.Parts
(Column 1 char(20),
Column 2 DEFAULT NULL)
You cannot use the DEFAULT option for a LONG data type column.
Specifying a DBEFileSet
The table rows are stored in the DBEFiles previously associated with the
DBEFileSet named in the IN clause of the CREATE TABLE statement. If you
do not specify a DBEFileSet, rows for the table are stored in the SYSTEM
DBEFileSet. For best performance, explicitly specify a DBEFileSet other
than the SYSTEM DBEFileSet.
Specifying Native Language Tables and Columns
Use the LANG = TableLanguageName option in the CREATE TABLE statement to
specify a language other than the DBEnvironment's language. You can only
specify NATIVE-3000 or the current native language of the DBEnvironment.
CREATE TABLE NewTable
LANG = "NATIVE-3000"
(Column1 char(20),
Column2 char(10))
You must use double quotes around the name "NATIVE-3000" because it
contains a hyphen. Normally, native language names do not require
quotes. For more information on naming rules, refer to the "Names"
chapter.
Use the LANG = ColumnLanguageName option in the column definitions of the
CREATE TABLE statement to specify a column with a language different from
that of the table as a whole. For example:
CREATE TABLE NewTable
(Column1 char(20) LANG = "NATIVE-3000",
Column2 char(20))
Sorting and pattern matching follow the rules of the column language. In
order to maintain ASCII performance as much as possible, NATIVE-3000
column sorting and matching are done in ASCII.
By default, the language of a new table is the language of the
DBEnvironment, and the language of a new column is the language of the
table it belongs to.
Creating a View
A view is a table derived by placing a "window" over one or more tables
to let users or programs see only certain data. Views are useful for
limiting data visibility; they are also useful for pulling together data
from various tables for easier use. The tables from which data for the
view is derived are called base tables.
You define a view with the CREATE VIEW statement. The following are
components of a view definition:
1. Name of the view
2. Name of its columns
3. Definition of how to derive data for the view
4. Specification of WITH CHECK OPTION, if desired
The following example contains numbers that refer to the view components
listed above:
1
|
CREATE VIEW HiPrice
(PartNum, Price) --2
AS SELECT PartNumber, SalesPrice ---
FROM PurchDB.Parts |--3
WHERE SalesPrice > 1000 ---
View names are governed by the same rules as table names.
The columns in a view can have the same names as the columns in the
table(s) they are based on, or they can have different names. You only
need to include column names in a view definition if you are using
multiple base tables which have duplicate column names or if you want to
rename the columns. You enclose the names in parentheses, but omit data
types, which depend on the types of the columns in the base tables.
The derivation of the view is a SELECT statement. In the previous
example, the view is derived from the PurchDB.Parts table. Each row in
the view contains a part number and a price; only rows for parts costing
more than $1000 can be accessed through this view.
Unlike a table definition, a view definition does not require that you
specify where to store rows. A view is a SELECT statement stored in the
system catalog, not a physical copy of the data; ALLBASE/SQL extracts
data from physical tables at the time you use the view. Views can be
used for both retrieving and modifying data. Refer to "Updatability of
Queries" in the "SQL Queries" chapter for restrictions governing the use
of a view to change data in a base table.
The WITH CHECK OPTION for views is described in the "Constraints,
Procedures, and Rules" chapter.
Creating Indexes
You can create an index on one or more columns in a query. An index can
provide quick access to the data in your tables. For information on
indexes, section "Understanding Data Access Paths" later in this section.
Specifying Integrity Constraints
Using integrity constraints helps to ensure that a database contains only
valid data. Integrity constraints provide a way to check data within the
database system rather than by coding elaborate validation checks within
application programs. An integrity constraint is either a unique
constraint, a referential constraint, or a check constraint. These
constraints are described in the chapter "Constraints, Procedures, and
Rules."
Creating Procedures
You can define procedures to enforce relationships among database tables
or to automate nearly any operation in the DBEnvironment. The following
example shows creating a procedure to perform deletions from the
SupplyPrice table in the sample DBEnvironment PartsDBE:
CREATE PROCEDURE PurchDB.DelSupply(Part CHAR(16) NOT NULL) AS
BEGIN
DELETE FROM PurchDB.SupplyPrice
WHERE PartNumber = :Part;
END
The procedure definition includes a parameter declaration. The parameter
Part accepts a value into the procedure at run time. You execute the
procedure with a statement like the following example:
EXECUTE PROCEDURE PurchDB.DelSupply ('1123-P-01')
The effect of the procedure is to delete all rows in the SupplyPrice
table whose part number is 1123-P-01. For detailed information about
creating and using procedures, refer to the "Constraints, Procedures, and
Rules" chapter.
Creating Rules
Once a table is defined, you can create a rule that will execute a
procedure whenever a specific firing condition is met. For example, you
can define a rule that will execute a procedure to delete rows from the
SupplyPrice table whenever a specific part is dropped from the Parts
table in the sample DBEnvironment PartsDBE:
CREATE RULE PurchDB.RemovePart AFTER DELETE FROM PurchDB.Parts
EXECUTE PROCEDURE PurchDB.DelSupply (PartNumber)
Once the rule exists, you activate it by performing a DELETE:
DELETE FROM PurchDB.Parts
WHERE PartNumber = '1123-P-01'
For detailed information about creating and using rules, refer to the
"Constraints, Procedures, and Rules" chapter.