HP 3000 Manuals HP 3000 Manuals
Chapter 5 DATABASE TOOLS
New Features for ALLBASE/XL
by Andy Hering/Peter Kane
Data and Languages Division
XL Release 2.0 contains a greatly-enhanced version of ALLBASE/XL. This
version contains many exciting new features along with some major
performance enhancements which should affect all HP SQL users.
NEW FEATURES
The new features in this product can be grouped into the following
categories:
1. ANSI and X/OPEN Conformance - which includes such features as
Nested Queries, UNION, and the new predicates IN, ANY, ALL, SOME,
and EXISTS.
2. Performance Features - which includes the new feature Read
Uncommitted, and the performance-related enhancements such as the
optimizer enhancements and the buffer pool enhancements.
3. Programmer Features - which contains a discussion on the new
feature Keep Cursor and the new REFETCH command.
ANSI SQL Level 1 Conformance
ALLBASE/XL now meets ANSI SQL Level 1 conformance with the following
features:
* Subquery or Nested Query. A subquery (nested query) is a SELECT FROM
WHERE expression that is inside another such expression, or is
embedded within a predicate or a special predicate (i.e., IN, ANY,
ALL, SOME, or EXISTS).
* Preprocessor SQLCODE Variable. In previous releases, ALLBASE/XL
results were returned in the field SQLCA.SQLCODE. The current ANSI
proposal does not specify the SQLCA record only the variable SQLCODE.
To meet the standard, SQLCODE will be recognized if used in place of
SQLCA.SQLCODE.
* MIN/MAX on CHAR Types. The MIN and MAX aggregate functions will be
executable on character strings.
* Variable Precision Float. ALLBASE/XL will allow the specification of
floating point precision. The precision will be the length of the
mantissa. The new syntax will be FLOAT(P) where P is the number of
bits of precision (from 25 through 53, with the default being 53).
* Comment Marker. Comments will be allowed on the end of an ALLBASE/XL
statement by denoting them with the marker "--". The comments extend
to the next <CR>.
* Datatype Keywords and Defaults. New keywords and default lengths are
introduced for several existing ALLBASE/XL datatypes. As a result,
the following keywords, abbreviations, and default lengths will be
allowed:
CHAR[ACTER] == CHAR(1)
DEC[IMAL] == DECIMAL(15) == DECIMAL(15,0)
INT[EGER] == INTEGER
NUMERIC == DECIMAL(15,0)
* Aggregate Over Empty Sets. In previous releases, if ALLBASE/XL
computed an aggregate function on an empty table or column, nothing
was returned (no zero or NULL, just an empty blank space).
In order to comply with the ANSI standard, a NULL value will be
returned for aggregate functions SUM, AVG, MAX, and MIN computed over
empty sets, and the value of 0 will be returned for the COUNT
function computed over an empty set.
* View as SELECT *. This new functionality will allow commands like:
Create view MYVIEW as SELECT *....
Create view MYVIEW as SELECT <tablename>.*
Create view MYVIEW as SELECT <correlation name>.*
Create view MYVIEW as SELECT <username>.<tablename>.*
* SELECT * with GROUP BY. In previous releases, a table of the form tx
(c1, c2, c3), (where tx is the table, and c1, c2, c3 are the columns)
would not allow a query of the form
SELECT * FROM tx GROUP BY c1, c2, c3;
X/OPEN SQL Conformance
ALLBASE/XL now meets X/OPEN conformance with the following features:
* UNION and UNION with ORDER BY Clause. The union of two sets is the
set of all elements belonging to either or both of the original sets.
Since a table is a set of rows, it is possible to obtain the union of
all the rows contained in one or both of the specified tables. It is
also possible to obtain the union of the rows returned by a pair of
select statements, each of which have limiting WHERE clauses. An
ORDER BY clause can be applied to the table which is the result of a
UNION operation. Thus, statements of the following type will be
permitted:
==> SELECT empno, workdept
FROM table1
WHERE workdept = 'DLD'
UNION
SELECT empnum, department
FROM table2
WHERE department = 'DLD'
ORDER BY 1;
The columns from both tables must be union compatible (essentially of
the same data type).
* NULL Handling Conformance. ANSI and X/OPEN standards specify how
NULL values will be handled in the following operations:
- Expression and comparison evaluation
- GROUP BY and ORDER BY clauses
- SELECT DISTINCT
- Set Functions (aggregates)
- UNIQUE INDEXES
ALLBASE/XL did not completely conform to these standards prior to
Release 2.0. Previously, a NULL value was considered unique,
whereas ANSI and X/OPEN consider all NULLS to have the same value
when it comes to sorting, grouping, and uniqueness. This
enhancement will cause NULLS to conform with the appropriate ANSI
and X/OPEN standards.
* ADD Multiple Columns in ALTER TABLE. Prior to Release 2.0, when new
columns were added to a table using the ALTER TABLE command, only one
column could be added at a time. X/OPEN specifies a slightly
different syntax, and allows the addition of more than one column at
a time. To comply with X/OPEN standard, a new syntax was added for
the ALTER TABLE command to allow statements such as:
ALTER TABLE MYTABLE ADD (col1 INTEGER, col2 CHAR(34),.......);
The new syntax is enclosed in parentheses. In order to ease
migration, the old syntax will still be permitted for the addition of
only one column to a table.
* SQLWarn Values and SQLWarn Structure. SQL uses SQLWarn variables to
pass status information back to the user. ALLBASE/XL implements
these variables as an array within the SQLCA record structure.
X/OPEN requires that these variables be eight distinct, 2-byte
variables, SQLWARN0 through SQLWARN7.
In addition, X/OPEN specifies that certain SQLWARN variables will be
used for specific purposes:
SQLWARN1: if 'W', at least 1 character string value was truncated
when it was stored in a host variable.
SQLWARN2: if 'W', at least 1 NULL value was eliminated from the
argument set of a function (note that aggregates are the only
functions in ALLBASE/XL at this time).
SQLWARN3: if 'W', the number of host variables specified in a
SELECT or FETCH statement is unequal to the number of columns being
operated on by the statement.
* DESC Index. Prior to Release 2.0, ALLBASE/XL indexes operate only in
ascending order (except in the special case of the ORDER BY clause).
The direction of order could not be specified in the CREATE INDEX
command. With the implementation of this enhancement, indexes can be
specified during creation to scan in either ascending or descending
order. The order specification is optional, and will default to
ascending order if omitted.
A new column will be added to the SYSTEM.INDEX to show scan
direction.
* Correlated Subqueries. In certain special cases, the value of a
subquery depends on the value of a column in the row of some outer
query. In this case, the subquery has to be evaluated once for each
row of the outer query. Such a subquery is called a correlated
subquery. An example of query type supported by this enhancement is:
==> Get the part number of parts weighing more than the average in
the same color group
SELECT DISTINCT (P1.PNO)
FROM P P1
WHERE P1.WEIGHT > (SELECT AVG(P2.WEIGHT)
FROM P P2
WHERE P1.COLOR = P2.COLOR);
__________________________________________________________________
NOTE P1 and P2 are used here as "correlation names". Although
they serve as a shorthand version of the tablename, they are,
more importantly, a means of distinguishing table references.
__________________________________________________________________
* DROP INDEX. Since the X/OPEN Standard requires that index names be
unique across the entire database, the DROP INDEX command does not
need to specify the tablename for the index. ALLBASE/XL requires
unique index names only across a single table, thus the table name
must be specified in the DROP INDEX command. In order to be
compatible with the X/OPEN standards, the X/OPEN syntax is being
added for DROP INDEX. The following command will now be permitted:
DROP INDEX MYINDEX
The table name will no longer have to be specified. However, in
order to use this syntax, all index names must be unique across the
entire Data Base Environment (DBE). There is a performance penalty
involved; it takes much more time to search a list of indexes that
apply to the entire DBE than a short list applying to only one table.
If performance of the DROP INDEX is important, use the table name in
the syntax.
DATE/TIME Data Type
ALLBASE/XL will provide a datatype designed specifically to handle DATES,
TIME, and intervals.
Performance Features
Performance/Tuning enhancements made to ALLBASE/XL include the following:
* SORT/MERGE JOIN. In previous releases, ALLBASE/XL only employed the
nested loop join algorithm as the join method. The nested loop
algorithm is expensive when tables are too large to fit into the
memory buffer. The sort/merge join algorithm is a more efficient
join algorithm for large tables which do not have indices on their
joined columns or have Btree sizes larger than the buffer cache.
* UPDATE using INDEX. UPDATES that contain equal predicates will be
able to take advantage of available indexes, which will vastly
improve performance.
* OR using INDEX. Certain types of queries that use the OR operator
will be able to take advantage of available indexes, which will
vastly improve performance.
* LIKE using INDEX. Certain types of queries that use the LIKE operator
will be able to take advantage of available indexes, which will
vastly improve performance.
* KEEP CURSOR. ALLBASE/XL will now allow the user to keep the cursor
position after a "COMMIT WORK" or a "ROLLBACK WORK". This feature
will be at the cursor (scan) level.
* View Migration. This new feature will allow SQLGEN to recreate view
definitions. In addition, users will now be able to see the
definitions by executing a SELECT against a new system catalog view
called SYSTEM.VIEWDEF.
DBCORE
* Read Uncommitted. The Read Uncommitted isolation level will permit
users to read an entire table without placing any locks on it, with
the exception of extremely short consistency locks. It is the lowest
isolation level degree that can be provided and still guarantee
physical integrity of the database.
Programmer Features
* COMMIT/KEEP Cursor. ALLBASE/XL will allow the user to keep the
cursor position after a "COMMIT WORK" or a "ROLLBACK WORK". This
feature will be at the cursor (scan) level.
* REFETCH command. The REFETCH statement allows an application to
refetch the previously read row from an open cursor. This can be of
great use in conjunction with KEEP CURSOR in applications using
terminal reads. Consider the following example, where a user is
interactively reading records and possibly updating them:
DECLARE CURSOR C1 FOR SELECT COL1,COL2 FROM T1 WHERE COL3=:host FOR
UPDATE of COL2;
OPEN CURSOR C1 KEEP NOLOCKS;
FETCH;
COMMIT WORK; << to release locks >>
loop: while there are still rows do
display results to user
prompt user for possible changes
if changes then
REFETCH;
check to see if record has changed - responsibility of
application!
if not changed then
UPDATE...WHERE CURRENT OF C1;
FETCH; << next record >>
COMMIT WORK;
else
COMMIT WORK; << to release locks >>
print error message.
else
FETCH; << next record>>
end of loop;
You should note that the validation in the above example is the
responsibility of the application.
Usability Enhancements
Usability enhancements made to ALLBASE/XL include the following:
* ISQL error handling consistency. Prior to Release 2.0, ISQL would
not list how many rows were successfully loaded if a load terminated
due to a violation of index uniqueness, exceeding data space or
similar error. ISQL will now return such information.
* Trap Pascal and file system errors in SQLCORE. Pascal run-time errors
and file system errors have caused ALLBASE/XL to abort instead of
giving useful error messages describing the error encountered.
Errors such as writing past EOF or trying to divide by zero are
common culprits. This enhancement will return a new error message in
the form
Internal error (!Error!) (DBERR xxx)
where !#1 is the name of the subsystem in which the error occurred
(e.g., MPE XL or Pascal Run-Time), and where !#2 is the error number.
* Raise default for Data Page Buffers. When a DBE is created using the
default number of data buffer pages, only 15 pages are allocated.
For most applications, this number of pages is so small that
performance suffers due to increased I/O costs.
This new feature will increase the number of default data buffer
pages to 100, which will give maximum performance for most
applications.
* Add PURGEALL command to SQLUTIL. Currently, two commands are required
to purge an entire DBE; PURGEDBE and PURGEFILE. In addition, the user
of these commands must be superuser. PURGEALL will handle the entire
operation of purging both the DBE and the associated log files. The
user will not have to have superuser capability in order to use the
PURGEALL command.
* Migration Utility. A migration utility has been provided with this
XL release. This utility will allow you to migrate ALLBASE/XL
databases from Release 1.2 to Release 2.0.
The Migration Utility has several commands. One command is called
PREVIEW, and allows you to 'preview' the migration. This preview
will alert you to any problems that may occur in the migration
process and will allow you to resolve the problems before the actual
migration. You should continue to use the PREVIEW command until all
problems have been flagged and fixed. Once this is done, you can
then execute the MIGRATE command, which will actually perform the
migration.
ADDITIONAL INFORMATION:
For additional details on usage of these new features, please refer to
the following HP SQL reference materials:
* Allbase/XL HP SQL Reference Manual (P/N 36216-60005)
* Allbase/XL HP SQL Database Administration Guide (P/N 36216-60007)
* Allbase/XL HP SQL C Application Programming Guide (P/N 36216-60017)
* Allbase/XL HP SQL FORTRAN Application Programming Guide (P/N
36216-60015)