HP 3000 Manuals HP 3000 Manuals
ALLBASE/SQL Enhancements
by Debbie Omuro and Trude Ko
Commercial Systems Division
The ALLBASE/SQL F.0 Release on MPE/iX Release 4.0 contains several major
enhancements that provide significant benefits in the areas of:
* Standards
* Performance
* Usability
* High Availability
* Tools
A general overview of the enhancements is included in this article. For
a detailed description of the new features, please refer to the
appropriate ALLBASE/SQL manual.
STANDARDS
In this release, several new enhancements are added that conform to
current or proposed ANSI SQL standards.
Stored Procedures
A stored procedure is a collection of SQL statements, local variable
declarations and assignments, control flow statements, and status
statements that are stored as sections in the system catalog. The
procedure can then be executed later directly through ISQL or a
preprocessed application, or indirectly through the triggering of a rule.
The benefits stored procedures offer are:
* Reduction of network traffic between applications and the
DBEnvironment, thereby improving performance.
* Additional security by controlling exactly which operations users
can perform on database objects.
* Reduction in coding by allowing procedures to be stored within the
database.
Procedure handling may be done either through ISQL or the programmatic
preprocessor interfaces. This includes procedure creation, procedure
execution, dropping a procedure, and procedure invalidation/revalidation.
This release does not support dynamic execution of stored procedures.
Rules
Rules allow you to specify generic constraints by tying stored procedures
to the operations performed on a table. A rule is defined in a CREATE
RULE statement that identifies a table, a type of data manipulation
statement, a search condition, and a stored procedure. The procedure is
executed whenever (1) the search condition evaluates to TRUE and (2) the
data manipulation statement used matches what is defined by the rule.
A procedure invoked by a rule can include data manipulation statements
that invoke rules, that in turn trigger the execution of other
procedures. Rules may be chained up to a maximum of 20 levels.
Outer Join
Up until this release, ALLBASE/SQL has supported Inner Joins, in which
the unmatched rows are excluded from the joined result. This Outer Join
enhancement allows for the unmatched rows in one of the tables to be
included in the joined result.
There are two types of Outer Joins: Asymmetric and Full. At this
release, Asymmetric Outer Joins are implemented. (If unmatched rows from
one table are preserved, then it is referred to as an Asymmetric Outer
Join; if rows from both tables are preserved, it is referred to as a Full
Outer Join.)
Check Constraints
ALLBASE/SQL now supports check constraints to conform to ANSI SQL
standards for integrity constraints. A check constraint restricts the
value of a column in a row based on constants or another column in the
same row. Check constraints can be defined on tables or views.
The check constraints are enforced in the execution of INSERT and UPDATE
statements. If check constraints are violated, integrity violations are
reported, and the statements have no effect. Check constraints are not
checked during DELETE statements since they cannot be violated by the
removal of a row from a table.
Add/Drop Constraints
ALLBASE/SQL now supports the adding and dropping of referential, unique,
and check constraints on a table using the ALTER TABLE statement. With
this enhancement, you can add constraints to columns in a table after the
table is defined. If adding the constraint causes a violation with the
existing data, an error is generated. You can also drop constraints
without dropping tables. Dropping a primary/unique constraint, which has
a foreign key referencing it, results in an error condition. It conforms
to proposed ANSI SQL2 standards and provides greater flexibility when
referential integrity is used.
Union Parentheses Support
This enhancement allows parentheses to dictate the order of evaluation of
an SQL statement that contains both UNION and UNION ALL operations.
CREATE SCHEMA
A new command, CREATE SCHEMA, now allows a grouping of individual DDL
(Data Definition Language) statements that define a database schema. The
execution of the DDLs inside the CREATE SCHEMA command does not differ
from the DDL statements when used individually. The benefit of the
CREATE SCHEMA command is to group the individual DDL statements in one
SQL statement. This centralizes the database creation commands into one
place, and increases the maintainability and portability of a database
environment.
Forward and circular reference on referential integrity constraints are
allowed within a CREATE SCHEMA statement. However, some of the existing
DDLs, such as CREATE DBEFILESET, CREATE DBEFILE, and CREATE TEMPSPACE are
not supported within the CREATE SCHEMA command.
Input Indicator Variables in Expressions
ALLBASE/SQL is now enhanced to allow the use of an input indicator
variable anywhere in an expression where host variables are permitted.
The input indicator variable specifies whether the associated host
variable value is to be used in the expression, or if the associated host
variable is to be NULL. This conforms to ANSI SQL 89 Level 2 standards.
UNION in CREATE VIEW
The use of UNION and UNION ALL within a CREATE VIEW statement is now
supported for standards conformance to ANSI SQL2. The CREATE VIEW
statement is modified to allow UNION and UNION ALL operations.
Deferred Constraints
Prior to this release, the integrity constraints in ALLBASE/SQL are
verified at the statement level. With the deferred constraints
enhancement, ALLBASE/SQL now allows the use of the new SET CONSTRAINTS
command to defer the checking of constraints until the end of the
transaction or until the constraint mode is set back to immediate. A new
SET DML ATOMICITY command is also added to allow you to specify update
atomicity at either the statement or row-level. Setting row-level
atomicity causes internal savepoints not to be generated. For optimal
performance of the LOAD command, you should defer constraints and set
update atomicity to row-level.
This enhancement improves LOAD performance and also makes the circular
referential constraints available.
Grantable Privileges
Prior to this release, only a database administrator (DBA) or owner of an
object could GRANT or REVOKE privileges on the object. With this new
release, ALLBASE/SQL provides another way to GRANT privileges by adding
the WITH GRANT OPTION clause. When the grantor uses this option to GRANT
a privilege, the grantee has that privilege and is able to GRANT that
same privilege to another user. However, the privileges are limited to
only table privileges.
If the WITH GRANT OPTION clause is not used on the GRANT command, the
GRANT works as before; that is, the grantee is not able to grant the
privilege to another user.
In addition, a new CASCADE option is added to the REVOKE command. If the
CASCADE option of the REVOKE command is specified, then any grants of the
privilege by the revokee are also revoked.
PERFORMANCE
The following information describes performance enhancements in this
release of ALLBASE/SQL:
TID Support
TID (Tuple ID) support allows direct access to rows in an ALLBASE/SQL
database environment. A TID is an internal pointer that identifies the
physical location of a tuple on disk. TID support provides the fastest
possible data access when an application needs to access a single row at
a time.
The TID for a specific row can be returned from SELECT statements, stored
in host variables, and used later for fast access to particular row(s).
Multi-Connect/Multi-Transactions
Multi-connect provides the capability to establish up to 32 simultaneous
database connections per process. (The actual number of database
connections, which can be established, may be further limited by the
memory available.) The application must explicitly connect to the
databases and explicitly specify which database is the current target for
its commands.
The Multi-connect feature has two variations: Single-transaction mode
and Multi-transaction mode. In the default mode, Single-transaction
mode, only one transaction may be active across the currently-connected
set of databases. In Multi-transaction mode, multiple transactions may
be active across the currently-connected set of databases. In addition,
in Multi-Transaction mode, only one transaction may be active at a time
per database connection. A default or user-specified timeout value is
used to provide deadlock protection in a Multi-transaction environment.
Concurrent B-Trees
This enhancement provides higher concurrency in an ALLBASE/SQL database
environment. With reduced locking on the index B-trees, you can expect
better performance because of the reduced time to traverse a path during
an index read or write.
Prior to this release, on an index scan all index pages traversed are
Share locked. With the new enhancement, on an index scan, index pages
are not locked.
Also prior to this release, on an index update, all index pages traversed
are Share locked and the updated leaf page is Exclusive locked. With the
new enhancement, on an index update, non-leaf pages are not locked, and
the updated leaf page is Intent-exclusive (IX) locked.
Timeout Enhancement
With the introduction of a timeout feature, indefinite wait periods when
a resource request is blocked are avoided. This feature allows you to
specify the maximum time a user is willing to wait for a resource.
The database administrator can globally set a maximum timeout limit and a
default timeout value. The user can also set a local timeout limit that
remains in effect until the timeout is reset or turned off, or until the
database connection is terminated.
The SET USER TIMEOUT statement is added to set the user timeout value.
The START DBE, START DBE NEW, START DBE NEWLOG, and ALTDBE (SQLUTIL)
statements are modified to set the maximum timeout limit and default
timeout value. The SHOWDBE (SQLUTIL) statement is modified to display
the maximum timeout limit and default timeout value.
Pseudo-Mapped I/O Enhancement
ALLBASE/SQL now allows you to specify DBEFiles using pseudo-mapped I/O.
This enhancement shortens I/O path lengths in ALLBASE/SQL by eliminating
operating system buffering of database pages, thus increasing database
performance. This feature is only appropriate for large DBEFiles
containing large tables accessed randomly. The MOVEFILE command in
SQLUTIL is modified to convert a file from mapped to pseudo-mapped, and
to prompt for which file access method, either mapped or pseudo-mapped,
will be used. A new SHOWACCESS command is also added to SQLUTIL to
display the file access method of the DBEFiles within a DBEnvironment.
Pseudo-mapped files cannot be dynamically expanded even if dynamic space
expansion is enabled.
Concurrent backup using SQLUTIL will not work on a DBEnvironment with
pseudo-mapped files. Although online backup using TurboSTORE will backup
the data correctly, pseudo-mapped file access is disabled for the
duration of the backup. In other words, the DBEnvironment must be down
for the duration of the TurboSTORE backup. You can change pseudo-mapped
files to mapped files using the MOVEFILE command before the concurrent
backup. When backup is complete, change them back to pseudo-mapped
files.
Memory Resident Data Buffer Pages Enhancement
This enhancement allows data buffer pages to remain memory resident to
improve database performance under certain circumstances. You should use
this enhancement only when the system is used primarily for ALLBASE/SQL
database access, and the number of data buffer pages configured for the
DBEnvironment is a small percentage of the overall system memory
configuration. The ALTDBE command in SQLUTIL is modified to specify
whether the data page buffers remain memory resident or not. The SHOWDBE
command is also modified to show this information.
USABILITY
The following enhancements increase the functionality and
user-friendliness of ALLBASE/SQL.
LOAD EXTERNAL Conversion of IBM Data
The LOAD EXTERNAL statement is enhanced to support loading of IBM data
types into ALLBASE/SQL columns. Input files with column data types of
16-bit integer, 32-bit integer, packed decimal, zoned decimal, ASCII or
EBCDIC can be loaded into ALLBASE/SQL column data types of INTEGER or
SMALLINT. Packed decimal, zoned decimal, ASCII or EBCDIC data can be
loaded into the ALLBASE/SQL column data type of DECIMAL. ASCII or EBCDIC
can be loaded into the ALLBASE/SQL column data type of CHAR or VARCHAR.
Finally, binary data can be loaded into an ALLBASE/SQL column data type
of BINARY.
Revalidate
ALLBASE/SQL invalidates sections when certain commands are executed, such
as: ADD DBEFILE, DROP DBEFILE, ALTER TABLE, DROP INDEX, DROP TABLE, DROP
VIEW, CREATE INDEX, UPDATE STATISTICS, and others. This causes a
temporary delay for the first application to access this section while
ALLBASE/SQL revalidates the section.
This enhancement introduces a new command, VALIDATE, which provides you
with an option to revalidate the section prior to running the
application. Module sections can be revalidated by specifying the module
names, or all sections can be revalidated by specifying the ALL MODULES
option. In order to validate any module, you must be the owner of the
module or have DBA or RUN authority. Procedure sections can be
revalidated by specifying the procedure names, or all sections can be
revalidated by specifying the ALL PROCEDURES option. In order to
validate any module, you must be the owner of the procedure or have DBA
or EXECUTE authority.
Migration Considerations: If you have migrated from an older release,
modules that have been preprocessed on the older release cannot be
validated unless the module has been:
1. RUN on the new release (RUN causes a VALIDATE to occur), or
2. Preprocessed on the new release
Once the module has run or preprocessed on this new release, the VALIDATE
command can be executed. If the module has not met one of these two
criteria, then an error message is given.
Query Access Plan Display
A new command, GENPLAN, is available with ISQL that allows you to display
access path information about SELECT, UPDATE or DELETE statements. The
SQL statement passed to GENPLAN is not executed; instead, access path
information is inserted into a temporary system table named SYSTEM.PLAN.
The access path information in SYSTEM.PLAN includes the selection of the
join order, the join method used (nested-loop or sort-merge join), and
access method (table or index scan).
If you are a knowledgeable user, you will understand the steps and
operations involved in the query. With this insight into the planned
execution of the query, you may be able to formulate the query
differently or make other design changes to improve performance.
String and Long Field Functions
Several new functions have been added to ALLBASE/SQL to allow for easier
manipulation of string data types and long field data types.
For any string type, the length and a subset of the string can be
obtained with the new functions, STRING_LENGTH and SUBSTRING. These two
functions take the following as input: any string type, substring
result, host variable, or string literal.
Long field columns in ALLBASE/SQL enable you to store a very large amount
of binary data. Before this release, in order to obtain the output
device type and output device name of a long field, a SELECT was done and
the output device type and output device name were extracted from the
long field descriptor. With this release of ALLBASE/SQL, the output
device type and output device name can be retrieved with two new
functions, OUTPUT_DEVICE and OUTPUT_NAME, without having to select the
data. (The output device returned indicates whether the output is in a
system file or shared memory.)
Throttle Support
Prior to this release, the maximum number of simultaneous active
transactions was set in the DBECon file and could range from two - 240.
If this limit is encountered, an attempt to start a new transaction would
result in DBERR 2404 (Maximum number of concurrent transactions
exceeded). Throttle support makes encountering this limit transparent to
the user with the throttle wait queue. If starting a new transaction
exceeds the transaction limit, the caller is automatically put on the
queue in FIFO order. Throttle waits are subject to timeouts to prevent a
distributed deadlock.
A new wait state, Throttle Wait (37), has been added to the list of wait
states in SYSTEM.CALL table.
Dynamic Parameter Substitution
Prior to this release, the SQL statement in a PREPARE had to be totally
defined. If, for example, you wanted to use different values in a WHERE
clause, you had to PREPARE a new section each time you chose a different
value. Thus,
PREPARE mycommand FROM 'SELECT * FROM table1 WHERE part=123';
must be prepared again when part is anything else. This enhancement
permits you to parameterize the section, allowing you to PREPARE it once
and execute it with different values. Dynamic parameters allow you to
include question marks (?) where actual values are to be determined
later. Thus, the previous example can become
PREPARE mycommand FROM 'SELECT * FROM table1 WHERE part=?';
Dynamic parameters may be used in place of constant expressions only in
the WHERE clause of UPDATE/DELETE/SELECT, in the INSERT VALUES clause and
in the UPDATE SET clause. A dynamic parameter may not represent a table
name, column name, or reserved word.
A new clause, USING, is added to the OPEN and EXECUTE statements to pass
input values to the dynamic parameters. Different input values can be
provided through EXECUTE and OPEN as many times as needed within a
transaction. The USING clause can pass either a host/indicator variable
or the data buffer in the descriptor area.
Passing a descriptor area is useful when the number and type of dynamic
parameters passed is unknown. To allow the application to find the
information about the number and type of parameters, an extension to
DESCRIBE allows SQLDA to be used for either INPUT or OUTPUT. DESCRIBE
INPUT allows the application to get format data and then set the values
accordingly.
Special Views
Special views are added to the ALLBASE/SQL system catalog to you with
access to descriptions for database objects to which you have access.
This allows you to view descriptions, without requiring explicit SELECT
authority on system catalog base tables. The owner name of these views
is CATALOG. These views are created internally, and SELECT authority is
granted to PUBLIC. No other privilege is granted on these views, so they
cannot be updated.
New RCB clause in START command
This enhancement gives you an option of setting the number of run time
control blocks at DBEnvironment start time. The START DBE, START DBE NEW
and START DBE NEWLOG commands are modified to allow the new RCB clause.
However, this enhancement does not update the run time control bock size
already specified in the DBECon file. SQLUTIL must be used to
permanently change the number of run time control blocks for an existing
DBE.
DYNAMIC option on C and Pascal Preprocessor
A new option is added to C and Pascal preprocessor to allow you to
convert your C and Pascal ALLBASE/SQL applications from static to
dynamic. With the
INFO="(DYNAMIC)"
string added to the C and Pascal preprocessor, the static application is
converted into a dynamic application. Thus, no sections are stored at
preprocess time, and host variables behave like dynamic parameters.
Owners of unqualified objects default to the user who runs the
application program.
C and Pascal Preprocessor Support for ALLBASE/DB2 CONNECT
Support for C and Pascal preprocessor access to IBM DB2 is added to
ALLBASE/DB2 CONNECT in this release. Prior to this release, the only
method HP 3000 users can use to access a DB2 database was through ISQL.
With this enhancement, your C and Pascal preprocessed application can
access your IBM DB2 databases, as well.
HIGH AVAILABILITY
The following enhancements categorized under High Availability increase
the flexibility of the ALLBASE/SQL backup and recovery capability, and
accessibility to ALLBASE/SQL databases.
Dynamic DBEFile Expansion
With this release of ALLBASE/SQL, you now have the option to create
dynamic DBEFiles. This enhancement allows you to specify the additional
increment of space to be added when a DBEFile capacity is reached and the
maximum size that the DBEFile may be expanded. If a DBEFile runs out of
capacity during execution of the SQL command, an additional increment of
space is added to the file. When all of the expandable DBEFiles in a
given DBEFileSet have been expanded to their limit, more DBEFiles need to
be added.
Usability Changes to ALLBASE/SQL High Availability
Several changes have been made relating to log files and the backup and
recovery from log files.
These enhancements include:
* The ability to create log files in different groups other than the
DBECon file group. With this feature, dual log files can be
placed in separate groups, and thus possibly different volumesets.
This creates a greater redundancy for your log files. MOVELOG,
SHOWLOG, RESTORELOG,and SETUPRECOVERY commands in SQLUTIL display
the optional group and account name.
* The display of log sequence numbers by SQLUtil through the
following log operation commands: SETUPRECOVERY, RECOVERYLOG,
RESCUELOG, STOREONLINE.
* The display of a message in RECOVERLOG, if a previously specified
time stamp in SETUPRECOVERY has been reached. The message
displayed states Time Stamp Reached.
ALLBASE Integration with System High Availability
MPE/iX has an alternative backup utility that is more powerful, and
allows for multiple drives and new storage devices. TURBOSTORE/XL II,
available on MPE/iX systems, allows access to multiple drives. This
option can be set and displayed using SQLUtil through SET BACKUP and
SHOWSET BACKUP.
TOOLS
This release introduces a new utility tool for ALLBASE/SQL product called
SQLVER.
SQLVER Utility
SQLVER.PUB.SYS utility checks the version strings of the ALLBASE/SQL
files, ALLBASE/NET files, and ALLBASE/SQL libraries. As it checks each
file, SQLVER returns any version incompatibilities and reports if the
file is missing.
A summary section includes the current ALLBASE/SQL version installed, the
number of missing files, and the number of version incompatibilities
found.
SQLVER can be run interactively or in batch-mode. In batch-mode, an
SQLVERERR JCW is set based on the outcome.
ALLBASE/NET ENHANCEMENTS
Starting with this release, ALLBASE/NET is bundled in with ALLBASE/SQL.
The following information describes the new ALLBASE/NET features.
Users File Wildcarding
You now can wildcard any part of the client logon (user.account.group) to
allow any user from a certain account, or a specific user regardless of
group or account, etc. Any combination of user account and group parts
can be wildcarded using the wildcard character @.
ALLBASE/NET Server Job in CS queue
ALLBASE/SQL F.0 contains a change to the job card of the ALLBASE/NET
server job. The ;PRI=CS option is now used by HPDASVR when it builds the
job card for the job that runs HPDADVR. By adding the ;PRI=CS option to
the ALLBASE/NET server's job card it is possible for the server job to
run in the C queue (the MPE interactive run queue). For sites that want
the ALLBASE/NET server jobs to run in the C queue, all that needs to be
done is to set the maximum job priority to CS using the CI JOBPRI
command.
It should be noted that the maximum job priority is a system wide value.
This implies that any one who uses the ;PRI=CS option is able to run
their job in the C queue. If a system administrator notices that jobs
that should be running in the D queue are actually running in the C
queue, the ALTPROC command can be used to put these jobs in their proper
queue.
SPECIAL NOTES
The impact of ALLBASE/SQL F.0 on your existing application is listed
below:
* You may see the log files fill up quicker due to the new
enhancements.
* Your SQLUTIL ALTDBE command files need to be updated to include
three new prompts in the F.0 release.
* The command history buffer has been expanded to hold the last 50
commands executed in ISQL.
* With this new release, ALLBASE/SQL logs at rollback time. If LOG
FULL condition is encountered, all active transactions are rolled
back.
SPECIAL CONSIDERATIONS
ALLBASE/SQL XL is auto-installable. However, if you are updating from an
earlier release of MPE/iX, you must perform the ALLBASE/SQL migration
before updating to MPE/iX Release 4.0.
NOTE If you are updating from an XL Release prior to 2.1, you must first
update to XL Release 2.1 and then perform the ALLBASE/SQL migration
before updating to MPE/iX Release 4.0.
To migrate your ALLBASE/SQL databases to this new release, run the
provided ALLBASE migration utility, SQLMIG.PUB.SYS. A backup of the DBE
should be done prior to running SQLMIG. The steps listed below also
appear in the ALLBASE/SQL Database Administration Guide (36216-90005).
These are the steps you must take to convert a DBE from the 2.1, or
later, format to the 4.0 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. (It is important to note that 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 make sure all parameters are OK.
* Backup the ALLBASE/SQL software (system backup will suffice). * Update
the operating system and the ALLBASE/SQL software. * Enter the command:
:RUN SQLMIG.PUB.SYS
* For each DBE that is 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 DBEFileSet:
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.
* Issue the MIGRATE command as follows:
MIGRATE=> MIGRATE 'DBEnvironmentName' FORWARD;
* Exit SQLMIG as follows:
SQLMIGRATE=> EXIT;
* 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 [AND DBELog2ID]
WITH PAGES = DBELogSize,
NAME = 'SystemFileName1' [AND 'SystemFileName2'];
This creates a new logfile under the current SQL version.
* Run SQLUTIL and issue a SHOWDBE command to check the parameters of the
new version of the DBEnvironment. If you wish to use ARCHIVE MODE
logging, run SQLUTIL and use the STOREONLINE command. Issue a SHOWLOG
command to verify that ARCHIVE MODE is set properly.
You can then exit SQLUTIL.
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 ALLBASE/SQL reference materials:
* ALLBASE/SQL Reference Manual (36216-90001)
* ALLBASE/ISQL Reference Manual (36216-90004)
* ALLBASE/SQL Database Administration Guide (36216-90005)
* ALLBASE/SQL Message Manual (36216-90009)
* ALLBASE/SQL Application Release F.0 Programming Bulletin
(36216-90063).