HP 3000 Manuals HP 3000 Manuals
New Features for ALLBASE/SQL
by Peter Kane
Data and Languages Division
This ALLBASE/SQL release contains further improvements in performance as
well as a few new features.
NEW FEATURES
The two new features in this release are:
* Data Hashing. A special Hash Storage Structure can now be defined on
a table in addition to a B-Tree Index. This Hash access method can
provide quicker retrievals for keys which are always accessed
directly (i.e. searches are always done with equalities). For
columns where retrievals are done with predicates such as LIKE,
BETWEEN, >, <, etc. it is better to remain using a B-Tree Index,
because hashed access will not be chosen by the optimizer for
retrievals of these types.
* Unlimited Data Buffer Pool. Currently the number of Data Buffers is
limited to 720. With this new release, the number of Data Buffers
will only be limited by the amount of virtual memory on the system.
Data Hashing
Data Hashing is another method of retrieval of data. If the exact key
value is given in a query, the key value is used as input to an address
calculation formula (this process is known as "hashing"). The Address
calculated is used to retrieve the requested row.
Hashing can give a performance improvement for exact key retrievals due
to decreased I/O, pathlength, and locking conflicts over the other
retrieval methods of B-Tree access and serial reads. Hashing will not be
advantageous for queries that do not request exact key retrievals.
The hash structure used for this access method must be created at CREATE
TABLE time. An example of the syntax is below:
CREATE PUBLIC TABLE PurchDB.Vendors
(VendorNumber INTEGER NOT NULL,
VendorName CHAR(30) NOT NULL,
ContactName CHAR(30),
PhoneNumber CHAR(15),
VendorStreet CHAR(30) NOT NULL,
VendorCity CHAR(20) NOT NULL,
VendorState CHAR(2) NOT NULL,
VendorZipCode CHAR(10) NOT NULL,
VendorRemarks VARCHAR(60) )
UNIQUE HASH ON (VendorNumber)
PAGES=101
IN PurchFS;
The keywords UNIQUE HASH ON specify that a hash structure is to be
created on the column VendorNumber. Up to 15 columns can be specified
for the hash key. The PAGES keyword is used to specify how many pages
are to be allocated for the primary hash space. Therefore, in the above
example, 101 pages are used for primaries. It is recommended that the
number of primary pages be a prime number for hashing efficiency. The
primary pages can only be allocated from an empty DBEFILE. Secondaries
(those records that hash to filled pages) are stored separate from the
primary hash space in what is known as overflow pages.
A new system view, SYSTEM.HASH has been added so that a data base
administrator can retrieve information pertaining to the hashed tables in
the DBEnvironment. The information stored includes data such as the
number of primary pages containing data, the number of primary pages that
are greater than 50% full, the average chain length for non-empty buckets
(a bucket is defined as a primary page, while a chain length is the
number of overflow pages attached to the bucket), and the number of
buckets with overflow chains.
* Only one hash structure can be created per table.
* A hash structure must be created at the time the table is created.
* A hash structure can not be dropped, except by dropping the table on
which it was defined.
* Updates are not allowed to any hash key column value. The user must
delete the tuple containing the old key column value, and insert a
new tuple with the new key column value.
* There must be enough empty DBEFiles of type table or mixed to contain
the primary pages for the hash table data at the time the table is
created.
Performance Improvements
Two enhancements have been made which can directly improve performance.
The first is the ability to specify as many data buffer pages as can be
allocated by MPE XL with regard to the system configuration. This can
improve performance for applications with large numbers of concurrent
users and large amounts of data.
The second is the improvement in unique record retrievals. ALLBASE/SQL
has been further tuned for unique index retrievals via the use of a new
simpler retrieval function.
SPECIAL CONSIDERATIONS
ALLBASE\SQL XL is auto-installable. However, if you are updating from
MPE XL version A.30.00 to MPE XL version A.40.00 you must migrate all SQL
Database Environments (DBEs) after the update before you can access them.
This is done by running the provided utility SQLMIG.PUB.SYS. A backup of
the DBE should be done prior to running SQLMIG.
If you are updating from MPE XL versions prior to A.30.00, you must first
update to MPE XLA version A.30.00 and perform the appropriate SQL
migration before updating to MPE XL version A.40.00.
The steps listed below also appear in the ALLBASE/SQL Database
Administration Guide (P/N 36216-60007). These are the steps you must
take to convert a DBE from the 2.0 format to the 2.1 format:
1. Prior to updating the operating system and ALLBASE/SQL software,
do the following for each DBEnvironment that will be migrated:
a. Run ISQL.PUB.SYS and issue a START DBE command. This ensures
that the DBEnvironment is logically consistent in the event
that it has not been accessed since a system failure occurred.
b. Run SQLUTIL.PUB.SYS and issue the STORE command to backup each
DBEnvironment. Note:Log files are not stored using this
command. Application programs associated with the
DBEnvironment must be backed up separately.
In addition, you should use SHOWDBE to note whether ARCHIVE
MODE logging is on or off. This information will be used
after the DBEnvironment is migrated.
2. Backup the ALLBASE/SQL software (system backup will suffice).
3. Update the operating system and the ALLBASE/SQL software.
4. Enter the command:
:RUN SQLMIG.PUB.SYS
If you are a new SQLMigrate user, you may want to issue the
SQLMigrate SET VERBOSE ON command to receive more detailed
messages during your session, as follows:
SQLMIGRATE=> set verbose on:
5. For each DBE that will be migrated you can check for potential
errors during the migration by using the PREVIEW command below:
SQLMIGRATE=>PREVIEW 'DBEnvironmentName' FORWARD;
The DBE is not modified during this operation. During this check,
you may receive messages that there is not enough space in the
SYSTEM DBEFileSet. If this occurs use the following commands to
create a new DBEFile and add it to the SYSTEM BDEFileSet:
SQLMIGRATE=> CREATE DBEFILE DBEFileName WITH PAGES = DBEFileSize,
NAME = 'SystemFileName';
SQLMIGRATE=> ADD DBEFILE DBEFileName TO DBEFILESET SYSTEM;
Note that the syntax of these commands is the same as in ISQL.
Repeat this step until no errors are encountered and SQLMigrate
returns the message:
The proposed migration should be successful.
6. Issue the MIGRATE command as follows:
SQLMIGRATe=> MIGRATE 'DBEnvironmentName' FORWARD TO '2100';
7. Exit SQLMIG as follows:
SQLMIGRATE=> EXIT;
8. Run ISQL as follows and issue a START DBE NEWLOG command as
follows:
:RUN ISQL.PUB.SYS
isql=> START DBE 'DBEnvironmentName' NEWLOG
[DUAL LOG]
LOG DBEFILE DBELog1ID[AN DBELog2ID]
WITH PAGES = DBELogSize,
NAME = 'SystemFileName1'[AND 'SystemFileName2'];
This creates a new logfile under the current SQL version.
9. Run SQLUTIL and ensure that the ARCHIVE MODE is set properly.
SQLMIG will always set the ARCHIVE MODE to OFF. If you are using
ARCHIVE MODE logging you must use ALTDBE to reset the logging
mode.
You can then exit SQLUTIL.
If you are using ARCHIVE MODE logging you must then run ISQL and
issue the following commands:
isql=> CONNECT to 'DBEnvironmentName';
isql=> BEGIN ARCHIVE;
isql=> COMMIT ARCHIVE;
You can then exit ISQL as follows:
isql=> EXIT;
At this point the DBE should be ready for access.
ADDITIONAL INFORMATION
For additional details on usage of these new features, please refer to
the following HP SQL reference materials:
* ALLBASE/XL SQL Reference Manual (P/N 36216-60005)
* ALLBASE/XL Database Administration Guide (P/N 36216-60007)
* ALLBASE/XL C Application Programming Guide (P/N 36216-60009)
* ALLBASE/XL FORTRAN Application Programming Guide (P/N 36216-60015)