|
|
|
|
|
|
|
|
Getting Started as an MPE/iX Programmer Programmer's Guide: HP 3000 Computer MPE/iX Computer Systems > Chapter 7 Data Management
HP SQL |
|
HP SQL is the relational model database management system (DBMS) module of ALLBASE/SQL. (Table 7-1 “ALLBASE/SQL Specifications” 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:
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 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:
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. 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. 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. 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. 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. 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:
Table 7-1 ALLBASE/SQL Specifications
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||
|
|
|
|||||||||
|
|||||||||
