HP 3000 Manuals HP 3000 Manuals
Chapter 1 Creating and Using Tables with Row Level Locking
Basics of Creating Database Objects (SQL)
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, you 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.
[REV BEG]
* 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.[REV END]
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.
[REV BEG]
Tables created with PUBLICREAD, PUBLIC, and PUBLICROW options also have
the following initial authorities associated with them:[REV END]
* A PUBLICREAD table can be read by anyone who can start a DBE
session.
[REV BEG]
* A PUBLICROW or PUBLIC table can be read and updated by anyone who
can start a DBE session.[REV END]
A DBA or the table's owner can use the GRANT and REVOKE statements to
change these authorities.
[REV BEG]
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.[REV END]