HP 3000 Manuals HP 3000 Manuals
HP SQL
HP SQL is the relational model database management system (DBMS) module
of ALLBASE/SQL. (Table 7-1 shows ALLBASE/SQL specifications useful to
programmers. For detailed information on ALLBASE/SQL components, refer
to the Data Management Series.)
HP SQL allows views to be created. A view is a table derived by defining
a filter over one or more tables to let users or programs view only
certain data in the tables. Views improve security by allowing users to
access only the data they have a need to know. Since the view is not
actually a physical table, use of views does not result in redundant
data. When data in a table is updated, all views that use the data are
automatically updated.
An HP SQL query requires that the programmer specify only the data
needed, minimizing the amount of preplanning and coding necessary. A
non-procedural interface allows the query of HP SQL databases without
specifying data access path information. You can operate on entire sets
of data at one time, rather than on one record at a time. The major
features of HP SQL are:
* Relational data model allows you to specify required data, without
specifying the retrieval method.
* Language preprocessors allow the same statements that are used for
an interactive query to be embedded in HP Pascal/iX and HP COBOL
II/XL application programs.
* Concurrent access allows multiple users to simultaneously access
data.
* Specification of levels of access privileges maintain security for
users and groups of users.
* Data independence allows you to make changes to the database
structure without requiring modification of applications.
* You can define views that allow a user or group of users to see
parts of one or more tables as a single, virtual, table. These
act as a filter to customize a table.
* Query optimizer reduces requirement for query planning details.
* B-tree indexes support fast data access.
* User controlled transactions ensure that data is always in a
consistent state.
* Automatic locking ensures data integrity in a multiuser
environment by preventing access to data while it is being
updated.
* Automatic rollback recovery preserves logical data integrity due
to a soft crash.
* You can invoke rollback capability in a program to allow erroneous
data, usually generated in an on-line situation, to be removed
before the transaction is completed.
* Rollforward recovery preserves logical and physical data integrity
due to a hard crash.
* Dynamic restructuring allows you to change data structure, table
capacities, and security without unloading and loading the
database.
* Null data values allow use of fields that are relevant to some,
but not all, records in a table.
HP SQL does not require you to define explicit relationships between data
sets. When you perform a query, it determines relationships by matching
values between fields common to two or more data sets. If you consider a
data set to be analogous to a table, a given field in the data set would
occur as a column in the table. Data from any number of tables that
share a common column can be related, as needed, and you can define them
to be an HP SQL database. The query optimizer determines the best data
access strategy based on factors such as the presence of indexes and the
relative sizes of accessed tables.
Interactive SQL (ISQL) is the interactive interface to HP SQL. It
provides the functionality of a data definition language (DDL) and a data
manipulation language (SQL). DDL allows the database administrator to
control all aspects of database creation and modification. SQL allows
the programmer or frequent user to interactively query a database.
Security
Security is maintained by allowing specification of appropriate levels of
access privileges to individual users or groups of users.
HP SQL allows read (SELECT) access and write access privileges to be
assigned at the table level. Read or write access restriction at a finer
granularity than the table level, such as at the column level, may be
obtained by defining a view of the table that omits the sensitive
information. Modification (UPDATE) authority may be granted at the
column level without requiring that a view be specified. Write access
may be assigned to allow any combination of the following capabilities:
* Row modification (UPDATE)
* Row insertion (INSERT)
* Row deletion (DELETE)
The database administrator (DBA) assigns access privileges by grouping
users with common access needs into authorization groups. The particular
read and write authorities are then assigned by the DBA to each of these
groups. Users with unique access requirements may also be granted
privileges directly.
Logical Transaction
A logical transaction is a series of database modifications of which
either all or none must be performed to leave the database in a
consistent state. The particular grouping of modifications defined by
the user to be a transaction varies depending on the particular
application. An example of a transaction is an accounting entry to pay a
bill: the credit to the cash account and debit to the accounts payable
account must both be performed to avoid leaving the accounts out of
balance. This concept of a logical transaction is essential to ensuring
that data integrity is maintained when multiple users are concurrently
accessing the database or in the event of system failure.
Concurrency
HP SQL preserves data integrity, when multiple users are accessing a
database, through a comprehensive locking scheme based on the transaction
concept. When a user begins a transaction, a lock is automatically
granted for each page read or modified by the transaction. (A page is a
unit of data storage that contains 4,096 characters.) This ensures that
no one else may update the data on those pages while the user is reading
or updating them. If data is only being read, then other users are not
prevented from reading it simultaneously; they just can't update it. If
data is being updated, however, data integrity is ensured by preventing
the other users from reading or updating the data. When the user's
transaction is completed, all acquired locks are automatically released.
Tables (data sets) may also be blocked explicitly. This feature is
provided to allow the programmer greater flexibility in applications
where it is advantageous to lock large portions of the database. Since
explicit locks reduce concurrency, they are not recommended for general
use.
Recovery
HP SQL ensures that the logical and physical integrity of the database is
protected in the event of a program abort, system failure, or destruction
of the media on which the database resides.
Rollback recovery is an automatically activated recovery feature that
ensures that the database is always in a logically consistent state. HP
SQL logs write transactions to a log file on disc. In the event of a
system failure or program abort, HP SQL uses this log file to
automatically back out any partially completed transactions.
The rollback capability may also be invoked in an HP SQL program. This
is a particularly valuable feature in an on-line application, as it
allows a user who has entered incorrect information to nullify the
transaction before its completion.
Rollforward recovery protects the physical and logical integrity of the
database against media failure. In the event of a hardware or software
failure, the transactions from the log file are reapplied to a backup
copy of the data to bring it up to the current state.
A simultaneously updated copy of the log files used for rollback or
rollforward recovery may be kept on another disc to provide additional
protection in the case of disc failure.
Database Creation
As part of the database design process, the database administrator (DBA)
must decide how many databases should be included in each HP SQL
DBEnvironment. A DBEnvironment may contain one or more databases. Since
the DBEnvironment is the maximum scope for recovery, multiple databases
that will be accessed by way of a single logical transaction should be
placed in the same DBEnvironment. The DBEnvironment is also the level at
which the data is backed up. Therefore, unrelated databases should be
placed in separate DBEnvironments.
After the DBA has designed the database structure on paper, he may easily
create the database. A DBEnvironment must be configured for the database
unless it will be included with other databases in an existing
DBEnvironment. The remaining step in creating an HP SQL database is to
create the tables (CREATE TABLE command), indexes (CREATE INDEX command),
and views (CREATE VIEW command) that make up the database.
Database Restructuring
HP SQL provides a full set of database restructuring capabilities. HP
SQL supports dynamic restructuring for commonly required structural
changes. Dynamic restructuring allows users to continue to access data,
except for the affected areas, during restructuring.
HP SQL provides dynamic restructuring for the following cases:
* Expanding table capacities
* Altering security designations
* Adding columns
* Adding or deleting indexes, views, and tables.
Table 7-1. ALLBASE/SQL Specifications
---------------------------------------------------------------------------------------------
- Specifications - HP SQL -
---------------------------------------------------------------------------------------------
| Data Types: | Packed decimal (IEEE standard) |
| | double-precision floating point (8 byte) |
- - Integer (2 & 4 byte) -
- - Fixed length (<3996 bytes) -
- - Character -
- - Variable length (<3996 bytes) -
- - Character -
- Languages: - COBOL -
- - Pascal -
- Passwords/Security Groups - Unlimited Groups -
- Maximum Database Parameters - -
- Tables (sets) per database - Unlimited -
- Records per table (set) - Unlimited -
- Record length - 3996 bytes -
- Columns (items) per table (set) - 255 -
- Field (item) length - 3996 bytes -
- Sub-items per item - n/a -
- Children per parent - n/a -
- Indexes per table - Unlimited -
- Search items per set - n/a -
- Columns per index - 15 -
- Sort items per path - n/a -
---------------------------------------------------------------------------------------------