HP 3000 Manuals HP 3000 Manuals
ALLBASE/SQL Enhancements
by Eleanor Normile
Commercial Systems Division
This version of ALLBASE/SQL included with XL Release 3.0 contains several
major enhancements that provide significant benefits in the following
areas:
* Standards.
* Performance.
* High Availability.
* Usability.
* ISV Tools Support.
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
The following describes some of the new enhancements and the standards
that they conform to.
Integrity Constraints
ALLBASE/SQL now supports integrity constraints. This enhancement
conforms to ANSI SQL standards for integrity constraints.
Key features of this enhancement are support of:
* PRIMARY KEY, a form of Unique Constraint (as is UNIQUE).
* FOREIGN KEY/REFERENCES, a Referential Constraint.
* CONSTRAINT NAME, a constraint identifier.
* REFERENCES Privilege, an authority allowing the user to refer to
the named table's primary key.
Using integrity constraints helps to ensure that a database contains only
valid data. Integrity constraints provide a way to check data within the
database system rather than by coding elaborate validation checks within
application programs. Constraints are defined at the column or the table
level and they are automatically controlled and checked by ALLBASE/SQL
once they are defined. If there is a constraint on a column, each time a
value is inserted, altered or deleted, ALLBASE/SQL performs the necessary
checks. If the constraint is not satisfied then none of the rows are
processed and an error message is generated.There are two types of
constraints, unique constraints and referential constraints.
Referential Constraint
A referential constraint requires that the value in a column or columns
of a table, called the referencing table, must either be null or match
the values of a column or columns of a unique constraint in another
table, called the referenced table. To establish a referential
constraint, a unique constraint must be defined on the referenced table's
column or column list and a referential constraint must be defined on the
referencing table's column or column list.
Unique Constraint
A unique constraint requires that no two rows in a table contain the same
value in a given column or list of columns. Unique constraints can be
defined as either UNIQUE or PRIMARY KEY. The difference between the two
types of unique constraint is that, if a PRIMARY KEY is placed on a
column or column list, the column name(s) can be omitted from the
referential constraint syntax in the definition of the referencing table.
Additionally, a primary key can be specified only once per table. A
given unique constraint need not be referenced by a referential
constraint but a referential constraint can only refer to a unique
constraint.
New DATE/TIME Functions
This release contains seven new functions for use with the DATE/TIME data
types. These functions provide flexibility for inputting and retrieving
date/time data from the database. The new functions include four new
input functions, two new output functions and an ADD_MONTHS function.
* ADD_MONTHS
The ADD_MONTHS function adds an integer number of months to a DATE
or DATETIME column. Only the months portion of the value is
affected and, if necessary, the years. The day portion of the
date is unchanged unless adding the months createsan invalid date
(such as '1990-02-30'). In this case, the day is set to the last
day of the month for that year, and ALLBASE/SQL generates a
warning message indicating the adjustment.
* TO_DATE, TO_TIME, TO_DATETIME and TO_INTERVAL
These four input function convert character values into date/time
values and produce a result which is of the DATE, TIME, DATETIME
or INTERVAL type, respectively. This allows the user to enter
date/time values in a format other than the default format. These
input functions can be used in the VALUES or WHERE clause of an
INSERT, SELECT, UPDATE or DELETE command.
* TO_CHAR and TO_INTEGER
These two output functions allow the user to specify the output
format of any type of date/time column by using a date/time output
function in the select listand/or the WHERE clause of the [BULK]
SELECT command.
New Data Types
A new short float data type, two new binary data types and a new type
descriptorare available for use in this release.
New data types added are BINARY, VARBINARY, LONG BINARY, LONG VARBINARY
and REAL (or FLOAT( p).
* BINARY and VARBINARY
BINARY data is stored as a fixed length of left-justified bytes.
It is zero padded up to the fixed length you have specified.
VARBINARY data is stored as a variable length of left-justified
bytes. You specify the maximum possible length. As with other
data types, you use the CREATE TABLE or ALTER TABLE command to
define a binary or varbinary column. Up to 3996 bytes can be
stored in such a column.
BINARY and VARBINARY data types are compatible with each other and
with CHAR and VARCHAR data types. They can be used with all
comparison operators and the aggregate functions MIN and MAX; but
arithmetic operations are not allowed.
* LONG BINARY and LONG VARBINARY
LONG BINARY and LONG VARBINARY are stored in the database just as
BINARY and VARBINARY data, except that its maximum possible length
is practically unlimited. The LONG descriptor allows the amount
of data in a column to exceed 3996 bytes. You use the CREATE
TABLE or ALTER TABLE command to specify the column as either LONG
BINARY or LONG VARBINARY.
LONG BINARY and LONG VARBINARY are compatible with each other but
not with other data types. Also, the concept of inputting and
accessing long column data differs from that of other data types.
Several restrictions exist for LONG columns, for example, they
cannot be used asindex columns and they can only be referenced in
the select list not in the WHERE clause of a query.
* REAL or FLOAT( p) where p = 1 to 23
In conformance to ANSI SQL86 level 2 specifications, ALLBASE/SQL
now offers the option of specifying the precision of floating
point data. You have the choice of a 4-byte or an 8-byte floating
point number. In addition, a new keyword (REAL) is provided in
this release. The REAL (4-byte float) data type could be useful
when the number you are dealing with is very small, and you do not
require a great deal of precision. REAL or FLOAT( p) consists of
an exponent and a mantissa. The precision, p, is a positive
integer that specifies the number of significant binary digits in
the mantissa. The value of p can be from 1 to 23. The default
(using REAL) is 23. The range of negative numbers that can be
represented is -3.402823E+38 to -1.175495E-38. The range of
positive numbers that can be represented is 3.402823E+38 to
1.175495E-38.
Escape Character for the LIKE Predicate
An enhancement for the LIKE predicate gives users the option of
specifying a single character as an escape character. When using the
LIKE predicate to determine whether an expression contains a particular
character string pattern, the two wild card characters (underscore and
percent sign) can now be escaped. Thus, when necessary, either of these
characters can be searched for. This enhancement conforms to ANSI SQL
standards.
Dynamic Positioned UPDATE and DELETE
You can now dynamically assign the value of a cursor when your program
uses the UPDATE WHERE CURRENT or the DELETE WHERE CURRENT commands. You
UPDATE or DELETE based on cursor position of the dynamic cursor. The
full functionality of ALLBASE/SQL's SELECT statement is supported. Prior
to this release, it was not possible to update or delete data using a
dynamic cursor.
Default Column Value
In previous releases of ALLBASE/SQL, when a table is created, a column
can be specified as either NULL or NOT NULL. When a row is inserted into
a table, and a specific column is not in the insert list, an error
results if the column has been defined as NOT NULL. Otherwise, if the
column allows a NULL value, a NULL is inserted.
In this release you can choose a default value other than NULL. Simply
use the DEFAULT clause when you create or alter a table. Then, when you
insert data, and a given column is not in the insert list, the specified
default value is inserted. Or when you alter a table, adding a column to
existing rows, every occurrence of the column is initialized to the
default value.
Valid default options are:
* NULL.
* USER (this indicates the current DBEUser ID).
* A constant.
* Result of the CURRENT_DATE, CURRENT_TIME or CURRENT_DATETIME
functions.
(This conforms to ANSI SQL1 level 2 with addendum-1 and FIPS 127
standards).
PERFORMANCE
The following enhancements increase the performance of ALLBASE/SQL.
New Sorting Method
ALLBASE/SQL uses a new sort method which improves the performance of the
sort operation. Performance gains are expected in the areas of Index
Creation, for SELECTs containing ORDER BY, GROUP BY, DISTINCT, UNION and
some joined queries when ALLBASE/SQL uses the sort-merge method. The new
sort method will be used for sections compiled with release.
A new command, CREATE TEMPSPACE allows users to control where the sorting
is done. CREATE TEMPSPACE defines and creates a TempSpace. A TempSpace
is a location where ALLBASE/SQL will create temporary files to store
temporary data when performing a sort. The location and characteristics
of the TempSpace are stored in the system catalog and the temporary files
are created only when needed. When no longer needed the files are
automatically deleted thus making the space available for use again.
However, it is not necessary to define a TempSpace in order to use this
new sort. If no temporary space is defined, sorting will be done in the
logon group.
Keep Cursor With NOLOCKS on Sort Operation
As of this release, you can use the KEEP CURSOR WITH NOLOCKS option for a
cursorthat involves sorting, whether through the use of a DISTINCT or
ORDER BY clause,or as the result of a join operation. However, for kept
cursors involving sorting, ALLBASE/SQL does not ensure data integrity.
It is the users responsibility to ensure data integrity by verifying the
continued existence of a row before updating it or using it as the basis
for updating some other table. For an updatable cursor, you can either
use the REFETCH or SELECT command to verify the continued existence of a
row. For a cursor that is non-updatable you must use the SELECT command.
Multi-Processor Support
This release of ALLBASE/SQL provides support for multi-processors.
However, this functionality is dependent on the availability of
multi-processor systems.
USABILITY
The following describes the enhancements to limits and statements.
30-Byte Host Variable Names
Past releases of ALLBASE/SQL allowed host variable names of up to 20
bytes. Withthis release, the limit is increased to 30 bytes providing
added flexibility forapplication programmers. This increase means your
host variable names can consist of up to 30 ASCII characters or
single-byte native language characters, or up to 15 2-byte native
language characters, or any mixture of single-byte or 2-bytecharacters
having a total length no greater than 30 bytes. This limit applies toall
supported languages. However, for COBOL only single-byte characters are
supported.
Support for COBOL COPY Statement
ALLBASE/SQL now supports the COBOL COPY statement. The preprocessor
scans your source code and inserts the indicated copylib modules into the
preprocessed code. The REPLACING clause, if specified, is expanded
during compilation (not during preprocessing). Two new directives are
used in your source code to set and unset the COPY statement feature.
These are:
$SQL COPY Turns on ALLBASE/SQL COPY statement processing
$SQL NOCOPY Turns off ALLBASE/SQL COPY statement processing
These two compiler directives may be used at any point in the source code
to selectively expand copy code. If the application has many COPY
statements some of which reference modules containing ALLBASE/SQL
commands, and you want to expand only the ALLBASE/SQL copy code, then you
can delimit the appropriate COPY statements with the $SQL COPY and $SQL
NOCOPY directives. If you want all copy code expanded at preprocessing
time put the $SQL COPY statement at the beginning of your file.
COBOL Pre-Processor SQL COLUMN Naming
Previous releases of the COBOL pre-processor did not fully enforce SQL
column naming conventions. As of this release, the pre-proccessor will
force column names to conform to SQL naming conventions. Any character
not allowed by SQL will be flagged as a syntax error. Rules for column
names are documented in the ALLBASE/SQL Reference manual page 4-1. This
change will impact users who have coded COBOL applications using hyphens
in the column names. Previous releases of the pre-processor converted
the hyphens to underscores. With this release, a column name containing
a hyphen will be flagged as a syntax error and the program will not
compile. To minimize the impact on users, an option has been provided to
tell the pre-processor whether or not to convert the hyphens to
underscores. This option has been implemented using a JCW named
HPSQLPPhyphen. When the JCW is set to the value 1 the COBOL
pre-processor will convert hyphens to underscores. The JCW should be set
before invoking the pre-processor. If the JCW is not set, or if it is
set to a value other than 1, the pre-processor will flag hyphens in SQL
column names as syntax errors.
To enable the JCW: SETJCW HPSQLPPHYPHEN 1
To display the JCW: SHOWJCW
To disable the JCW: SETJCW HPSQLPPHYPHEN 0
HIGH AVAILABILITY
The following enhancements increase the flexibility of ALLBASE/SQL.
Concurrent Backup
This release of ALLBASE/SQL supports concurrent backup with archive
logging. That is, you can backup the DBEnvironment without having to
shut it down. This provides higher availability since users can
be accessing the database while thebackup is in process. The
SQLUtil STOREONLINE command stores a copy of the DBEnvironment and
initiatesarchive logging, if it's not already on. However, it does not
store any log files containing transactions which are in process at the
time the STOREONLINE command is issued. For this reason, the online
backup is not usable for rollforward recovery until you have backed up
all log files that contain transactions which were in process at the time
the STOREONLINE command was issued.
NOTE This feature requires the TurboStore software product.
Log Switching
This release of ALLBASE/SQL supports the use of multiple log files and
provides the ability to automatically switch from one log file to another
when the first log file becomes full. This provides greater flexibility
with logging and ensures higher availability of the DBEnvironment since
you can recover easily from a LOG FULL condition without having to shut
the DBEnvironment down. This feature is supported with both archive and
nonarchive log mode.
New and Updated SQLUTIL Commands
To support the new logging and backup features in ALLBASE/SQL, several
new commands have been added to SQLUTIL and some existing commands has
been modified.
The new SQLUTIL commands are:
ADDLOG Adds a new log file to the DBEnvironment.
MOVELOG Moves a log file from one location to another.
PURGELOG Purges a log file that is no longer needed.
RESCUELOG Stores a copy of a log file without accessing the
DBECON file.
RESTORELOG Restores a log file previously backed up with the
STORELOG command.
SHOWLOG Displays information about the log files
associated with the DBEnvironment.
STORELOG Stores a copy of an archive log file that is ready
for backup.
STOREONLINE Backs up a DBEnvironment to tape or serial disk
and enables archive logging if it is not already
in effect.
The modified SQLUTIL commands are:
ALTDBE The Archive Mode parameter is no longer supported
with this command. Therefore, you cannot change
archive mode with ALTDBE. To change archive mode,
use the START DBE NEWLOG command. Scripts using
the ALTDBE command that were prepared for use with
earlier releases of ALLBASE/SQL will not work with
this release unless they are edited to remove the
response for archive mode.
SHOWDBE SHOWDBE no longer displays the Archive Mode
setting or the Log File Names.
STORE STORE no longer allows the user to enable archive
mode. Instead, it displays a warning telling the
user to use the BEGIN ARCHIVE and COMMIT ARCHIVE
commands in ISQL to support RollForward Recovery.
Alternatively, users can use the new STOREONLINE
command.
For a full description of these new and changed commands refer to the
SQLUtil section of the ALLBASE/SQL Database Administration Guide. Also
refer to the Backup and Recovery section for information on the new
backup and logging features.
TOOLS SUPPORT
This release of ALLBASE/SQL provides support for Independent Software
Vendor (ISV) tools, thus providing users with greater flexibility and
wider choice in the selection of application development environments.
Among the ISV tools which will be integrated with ALLBASE/SQL are:
* INGRES Tools:
The Ingres 6.2 application development tools with ALLBASE/SQL
include the INGRES4GL, for application development; the ABF
"programmer's workbench environment; the Ingres Forms System for
display management and the Ingres QBF "query-by-forms" tool.
* COGNOS Tools:
The Cognos application development tools with ALLBASE/SQL include
the Powerhouse 4GL environment, ARCHITECT, for application
building and documenting;QUICK for OLTP applications; QUIZ for
data reporting and formatting; and QTP forvolume transaction
processing.
* INFOCENTRE Tools:
The InfoCentre application development tools with ALLBASE/SQL
include Speedware 4GL, REACTOR, for application development;
DESIGNER for designing and building the application; and
DOCUMENTOR for generating end-user application manuals.
* INFORMATION BUILDERS INCORPORATED Tools:
The Information Builders application development tools with
ALLBASE/SQL include the FOCUS 4GL for application development; the
FOCUS Report Writer for report writing and data analysis; and the
FOCUS Screen Manager for screen design.
NEW AND UPDATED DOCUMENTATION
The following two new manuals are now available for ALLBASE/SQL.
* Up and Running with ALLBASE/SQL (P/N 36389-90011)
* ALLBASE/SQL Programmer's Bulletin for XL Release 3.0 (P/N
36216-90044)
The Up and Running with ALLBASE/SQL primer is an introductory guide to
helpnew users get started with ALLBASE/SQL quickly. The Programmer's
Bulletin is a guide to new features introduced in 3.0, for programmers.
It is important for programmers to read this bulletin as the individual
Application Programmer'sGuides for the four supported programming
languages are not being updated at this time.
In addition to the new manuals, a new chapter has been added to the
ALLBASE/SQL Reference Manual entitled "Concurrency Control Through Locks
and Isolation Levels". This chapter contains an in-depth description of
the various methods employed by ALLBASE/SQL to provide concurrency
control for mulituser DBEnvironments.
The reference manuals have also been updated to reflect the new features
added in this release. Since the information in this article is a
general overview of these new features, please refer to the appropriate
reference manuals for an in-depth description of the new functionality.
MIGRATION ISSUES
ALLBASE/SQL XL is auto-installable. However, if you are updating from an
earlier XL release you must perform the ALLBASE/SQL migration before
updating to XL Release 3.0.
NOTE If you are updating from XL release prior to 2.0 you must first
update to XL Release 2.0 and then perform the ALLBASE/SQL migration
before updating to XL Release 3.0.
The ALLBASE/SQL migration is done by running the provided 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 (P/N 36216-90005). These are the steps you must
take to convert a DBE from the 2.0, or later, format to the 3.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. 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 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.
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 [AND 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/SQL Reference Manual (P/N 36216-90001)
* Allbase/SQL Database Administration Guide (P/N 36216-90005)
* ALLBASE/SQL Programmer's Bulletin for MPE XL Release 3.0 (P/N
36216-90044)