HP 3000 Manuals HP 3000 Manuals
HP ALLBASE/SQL Characteristics
System Catalog
A data dictionary does not have to be set up in HP ALLBASE/BRW for HP
ALLBASE/SQL tables. Therefore, there is no HP ALLBASE/BRW dictionary
(BRWDIC file) to maintain.
HP ALLBASE/BRW takes advantage of HP ALLBASE/SQL's System Catalog by
retrieving information on data definitions directly from the System
Catalog. This provides an active link to data definitions. If changes
are made in data structures, the changes are reflected in the System
Catalog.
The information stored in the System Catalog of a DBEnvironment includes
the following:
* the existence of HP ALLBASE/SQL tables and views
* the column names belonging to a table
* the datatype of a column with length, precision and scale
* whether a NULL value is allowed for a column
To define a report (programs BRWXL and BRWCOMP), you need the HP
ALLBASE/SQL production database or an HP ALLBASE/SQL test database with
the same table structures, owner names and access rights as the
production database.
Table Owner
The Define Table screen identifies the source tables used in the report.
For an HP ALLBASE/SQL table or view, the ownername.tablename is specified
in the Source Table field, and the DBEnvironment is specified in the
Location field.
The Define Table screen is the only screen in HP ALLBASE/BRW that
requires both the ownername and the tablename to be specified. Within HP
ALLBASE/BRW an HP ALLBASE/SQL table or view is identified by its table
name only.
Access Authorization
HP ALLBASE/SQL does not have passwords for DBEnvironments, tables, views
or columns. Access to an SQL database is controlled via authorities
granted to the users of a DBE. This information is kept in the SQL System
Catalog and checked against the MPE logon user running an SQL
application. A user is identified by HP ALLBASE/SQL as logon user@logon
account. Users defining and users running a report on an HP ALLBASE/SQL
DBEnvironment must have CONNECT authority to this DBEnvironment, and
SELECT authority on all tables and views they want to read.
The HP ALLBASE/BRW programs always open an HP ALLBASE/SQL DBEnvironment
with the CONNECT command. This requires the DBEnvironment parameter
Autostart Mode to be set to on. The on setting allows a DBEnvironment to
be opened with the CONNECT command instead of the STARTDBE command. The
DBEnvironment parameter "User Mode" should be set to multi to allow
multiple access to the DBEnvironment.
Transaction Handling and Locking
There are two ways of specifying locking within SQL: table lock mode and
transaction isolation level. Locking controls concurrency of different
applications running in the same DBEnvironment.
In HP ALLBASE/BRW, table lock mode is specified on the Tune Access
screen. Transaction isolation level is specified in the Configuration
file and may be overwritten for a single report on the Define Report
screen.
The default for table lock mode is none, and the transaction isolation
level default is RC (Read Committed). Refer to the HP ALLBASE/SQL manual
for a description of locking and concurrency.
Listed below is general information pertaining to Table Lock Mode and
Transaction Isolation Levels.
Table Lock Mode
SHARE
* Locks the whole table for the duration of the query.
* Allows concurrent reads by other applications, but prohibits
concurrency for data manipulation.
* Guarantees that nobody changes the table while BRW is executing
the query.
* Deadlock possibility only in combination with locks on other
tables.
Transaction Isolation Levels
RR
* Locks the data accessed by BRW for the duration of the query.
* Allows concurrent reads, but restricts concur rency for data
manipulation. Parts of the table not accessed by BRW might be
manipulated by other applications.
* Guarantees that the data accessed by BRW remains unchanged while
BRW is executing the query.
* Likelihood of deadlocks.
CS,RC
* Locks the data currently being accessed by BRW while reading.
Locks are not kept until the end of the query.
* Allows most concurrency together with committed data.
* Guarantees consistency for single data but not for the query as a
whole.
* Possibility of deadlocks.
RU
* No locks while reading the data.
* Allows most concurrency, but there is a likelihood for reading
uncommitted data. Is recommended if there is no need for an exact
report.
* No deadlocks with concurrent data manipulations.
The locking strategy should be chosen in accordance with the DBA
depending on the DBEnvironment and the applications running on it.