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 loc k is automatically gran ted for each page read or modi￿ed b y the transaction. (A page is a unit of data storage that con tains 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 prev ented from reading it sim ultaneously; they just can't update it. If data is being updated, ho wever, 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) ma y also be blocked explicitly. This feature is pro vided to allow the programmer greater ￿exibility in applications where it is adv antageous to loc k large portions of the database. Since explicit loc ks reduce concurrency, they are not recommended for general use.

Recovery

HP SQL ensures that the logical and ph ysical integrity of the database is protected in the event of a program abort, system failure, or destruction of the media on whic h the database resides.

Rollback recovery is an automatically activ ated recovery feature that ensures that the database is always in a logically consisten t state. HP SQL logs write transactions to a log ￿le on disc. In the ev ent of a system failure or program abort, HP SQL uses this log ￿le to automatically bac k out any partially completed transactions.

The rollback capability ma y also be invoked in an HP SQL program. This is a particularly valuable feature in an on-line application, as it allo ws a user who has en tered incorrect information to n ullify the transaction before its completion.

Rollforward reco very protects the ph ysical and logical in tegrity of the database against media failure. In the ev ent of a hardware or softw are failure, the transactions from the log ￿le are reapplied to a backup copy of the data to bring it up to the curren t state.

A simultaneously updated cop y of the log ￿les used for rollback or rollforward reco very may be kept on another disc to pro vide additional protection in the case of disc failure.

Database Creation

As part of the database design process, the database administrator (DBA) m ust decide how many databases should be included in eac h HP SQL DBEnvironment. A DBEnvironment may contain one or more databases. Since the DBEn vironment is the maximum scope for reco very, multiple databases that will be accessed b y way of a single logical transaction should be placed in the same DBEn vironment. The DBEnvironment is also the level at which the data is backed up. Therefore, unrelated databases should be placed in separate DBEn vironments.

After the DBA has designed the database structure on paper, he ma y easily create the database. A DBEn vironment must be con￿gured for the database unless it will be included with other databases in an existing DBEn vironment. The remaining step in creating an HP SQL database is to create the tables (CREA TE TABLE command), indexes (CREA TE INDEX command), and views (CREA TE VIEW command) that mak e up the database.

7-8 Data Management