HP 3000 Manuals HP 3000 Manuals
ALLBASE/SQL Enhancements
by Helene Betit, Eleanor Normile and Karen Poeschel
Commercial Systems Division
Product Overview
ALLBASE/SQL, version G.0 in this MPE/iX release, contains several major
enhancements that provide significant benefits in the areas of:
* Standards
* Performance
* High Availability
* Usability
* Tools
* Connectivity and Client/Server
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.
SET TRANSACTION and SET SESSION.
Two new commands, SET TRANSACTION and SET SESSION, are provided for
setting an Isolation Level as well as other transaction attributes at the
session or the transaction level. The new transaction attributes
increase flexibility, usability, and improve portability and performance.
The attributes that you can set are: Isolation Level, Priority, Label,
Constraint Checking Mode, DML Atomicity, FILL and PARALLEL FILL.
FIPS Flagger.
Federal Information Processing Standard (FIPS) 127.1 flagger support is
part of an SQL standard. If a statement or an extension does not conform
to FIPS 127.1 standard, a flag must be generated. With this release of
ALLBASE/SQL users can, optionally, flag non-standard SQL syntax by means
of a -f preprocessor option or with the SET FLAGGER command in ISQL.
Performance
Performance enhancements in this release of ALLBASE/SQL are described in
this section.
Access Plan Modification.
The Access Plan Modification feature allows users to provide instructions
to the optimizer regarding the access plan to be chosen for a query.
This can be used to override the default access plan generated by the
optimizer, when a different access plan is known to have better
performance. This enhancement can help you tune your queries for optimal
performance.
With the new SETOPT GENERAL statement you can specify the type of scan to
be used to access the tables in the query - serial, index or hash. You
can also specify the join method - sort-merge or nestedloop, to be used
to join the tables in the query. For queries that have an "order by" or
"group by" clause, you can specify that the index on the column of the
"order by" or "group by" clause be used for the scan. The SETOPT GENERAL
statement affects queries in the current transaction. The settings
specified by SETOPT GENERAL are cleared when the transaction ends or when
the SETOPT CLEAR command is executed.
The SETOPT command can be executed from ISQL or from an application
program. For example, to specify that index scans are to be used for the
query:
BEGIN WORK;
SETOPT GENERAL INDEXSCAN;
SELECT * FROM PurchDB.Parts;
COMMIT WORK;
NO OPTINFO Clause added to INSTALL Command.
A new clause is added to the ISQL INSTALL command syntax. The new
clause,
NO OPTINFO, specifies that the optimization information in the module
file is not to be installed into the DBEnvironment. This clause is used
when optimization information, specified by SETOPT, contained in the
module is not to be installed in the DBEnvironment. The default is
optimization information is installed along with the module.
For example, the statement INSTALL myfile NO OPTINFO installs the module
but drops the optimization information.
DROP SETOPTINFO clause added to VALIDATE command.
You can specify the access plan of a query with the SETOPT statement. To
validate a module or procedure without the user-specified access plan,
include the DROP SETOPTINFO clause in the VALIDATE statement. The
default access plan determined by ALLBASE/SQL is then stored in the
system catalog instead of the user-specified access plan.
Application Development Concurrency.
Four areas have been enhanced to provide increased performance and
concurrency: System Catalog tables, preprocessors, ISQL commands and SQL
commands.
The system catalog access method provides greater concurrency by allowing
you to specify either table, page, or row level locking of any system
tables owned by STOREDSECT or HPRDBSS, through the ALTER TABLE command.
The preprocessors are enhanced to allow you to optionally specify a
DBEFileSet, for storage of sections, when invoking a preprocessor. In
addition, you can now preprocess an application without storing sections
in the DBEnvironment. A module file is still generated, which can be
INSTALLed in a DBEnvironment using ISQL. If a DBEnvironment name is
specified, a database connection is established and errors/warnings are
generated for missing objects or authorizations. If no DBEnvironment
name is specified, the preprocessor is not connected to a database and
errors/warnings are not generated for missing objects or authorizations.
ISQL is enhanced to allow users to optionally specify a DBEFileSet when
installing sections from a module file in a DBEnvironment and to set a
default DBEFILESET.
Optimizer Enhancement.
With this enhancement, the optimizer uses a more efficient algorithm to
determine the best access plan for a query. The result is a significant
reduction in the time it takes the optimizer to generate the access plan.
This enhancement improves performance by reducing optimization time for
user applications.
TRUNCATE TABLE Command.
A new SQL statement TRUNCATE TABLE allows you to empty tables without
degradation of performance due to extensive logging. By using TRUNCATE
TABLE, you can empty a table but retain the table's structure. TRUNCATE
TABLE can be used to improve performance and simplify the process of
reorganizing a database.
I/O Performance Improvements.
In previous releases, the only sequential scan of a table was one that
performed a serial scan of all the DBEFiles in a DBEFileset. To improve
performance, a new parallel sequential scan can also be used internally
by ALLBASE/SQL to sequentially read tables. With a parallel sequential
scan, the tables are partitioned and multiple serial scans on these
partitions execute in parallel. Having multiple serial scans executing
in parallel allows the I/O for a serial scan to be spread across multiple
disk drives, thus improving performance.
LOAD Performance Improvements.
To improve the performance of LOADs, several changes have been made to
ALLBASE/SQL:
1. A new session attribute called FILL can be set that optimizes the
way we end all newly allocated pages. The new session FILL option
allows ALLBASE/SQL to take advantage of I/O aggregation and disk
parallelism. This enhancement only affects ALLBASE/SQL on MPE iX
when nonarchive mode logging is being used.
2. A new session attribute called PARALLEL FILL can be set that
allows you to execute multiple load processes that will run in
parallel.
3. A new search algorithm utilizes a binary search rather than a
sequential search of a DBEFileset to speed up the search for
available space.
4. Performance has been improved for creating and loading HASH
tables. When a HASH table is defined, the allocation and
formatting of the HASH pages have been deferred thus improving
performance when the HASH table is created. Deferring the
allocation of the HASH pages also allows the pages to be treated
as NOLOG pages when the table is being loaded. This improves LOAD
performance by eliminating the logging required for each tuple if
the page is allocated.
5. ISQL has been enhanced internally to provide better performance
when doing LOADs and UNLOADs. Also, the ISQL SET command has been
enhanced to allow you to set options that can improve the
performance of LOADs and UNLOADs. The new and modified SET
options are:
a. SET LOAD_BUFFER - Specifies a buffer size used in the LOAD
and UNLOAD commands. Option values are from 16384 to
132217727 bytes.
b. SET LOAD_ECHO - Specifies a status reporting method for
LOAD/UNLOAD commands. Option values are On, Off,
At_Commit.
c. SET AUTOLOCK - Specifies if ISQL needs to lock the data
table exclusively when the LOAD command is executed.
d. SET AUTOSAVE - The limit has been raised from 32767 rows to
21474836647 to give the user greater control of how often
COMMIT WORK is executed.
High Availability
The following enhancements increase the flexibility of the ALLBASE/SQL
backup and recovery capability and accessibility to ALLBASE/SQL
databases.
ALLBASE/SQL already has a feature set for database High Availability:
* Database shadowing through ALLBASE/REPLICATE (Refer to the article
"ALLBASE/REPLICATE" included in this Communicator
* DUAL LOG
* SWITCH LOG
* Online backup
* Dynamic Space Expansion
* Parallel CHECKPOINTs
* Parallel or nonstop backups or both
This version implements the following new features:
Partial Store and Restore..
The STORE, STOREONLINE and RESTORE commands in SQLUtil have been extended
to execute partial backup and partial recovery. Using partial store and
restore, you can backup and restore DBEnvironment Files, DBEnvironment
Filesets or combinations of both. This enhancement gives you more
flexibility in your backup and recovery strategies.
Listing files on the backup devices..
A new command in SQLUtil, STOREINFO, lists the physical names of the
files stored on the backup device.
Partial Rollforward Recovery..
In conjunction with partial backup and restore, recovery functionality
has been enhanced to allow partial rollforward. A new option, PARTIAL,
was added to the SETUPRECOVERY command in SQLUtil, to support partial
rollforward recovery. This can be used in situations where there is a
need to recover specific DBEFiles while allowing access to other
DBEFiles. This enhancement increases availability of the data and
provides finer granularity of the rollforward process.
DBEFiles and groups on MPE/iX..
This enhancement allows DBEFILES to be placed in groups other than the
group in which the DBECON file resides. This can be accomplished through
the CREATE DBEFile command in ISQL and with the MOVEFILE command in
SQLUtil. This helps you manage your DBEFiles to achieve higher
availability and better performance of the DBEnvironment.
Detached Database Objects.
With this release, you can detach a DBEFile or a DBEFileset from the
DBEnvironment. If a DBEFileset is detached, then all the DBEFiles in the
DBEFileset are detached. Similarly, you can attach a DBEFile or a
DBEFileset to the DBEnvironment. Detached DBEFiles are inaccessible for
normal operations and can, optionally, be stored offline to free up disk
space. This can be useful for data that is accessed infrequently, such
as tables containing historical data only, and that does not need to be
attached to the DBEnvironment all the time. Two new SQLUtil commands,
ATTACHFILE and DETACHFILE allow you to attach or detach DBEFiles or
DBEFilesets.
User initiated CHANGELOG..
In previous versions, a log switch was always initiated internally by the
system. There was no way for you to force the system to change to a new
log to allow for backing up the current, archive mode, log file. A new
command in SQLUtil called CHANGELOG is implemented to allow you to change
the log file as needed.
Console Messages logged to a file..
Certain system events are conveyed to you by writing messages to the
console. Starting with this version, messages can, optionally, be sent
to a file.
Physical file creation and deletion recoverable..
This version will create or delete the physical file as needed, when a
CREATE DBEFILE or DROP DBEFILE command is executed. In previous versions
the deletion had to be done through SQLUtil. Now the rollforward and
rollback recovery process deletes or creates physical files as necessary.
New and Updated SQLUtil Commands.
Several new commands have been added to SQLUtil and some existing
commands have been updated. These enhancements have been added to
provide support for new functionality introduced in this version of
ALLBASE/SQL , including partial backup and recovery, physical file
recovery, support for DBEFiles in different groups, new console messages,
and user initiated log switching. Following is a brief description of
each new SQLUtil command:
* ATTACHFILE - Attaches DBEFiles and DBEFilesets to the
DBEnvironment and makes them available for normal access.
* CHANGELOG - Causes the DBEnvironment to change to a new log file.
* DETACHFILE - Detaches DBEFiles and DBEFilesets from the
DBEnvironment and makes them unavailable for normal access.
* STOREINFO - Lists all files saved on the backup device.
The following is a description of the modified commands:
* MOVEFILE - Enhanced to move DBEFiles across groups and devices.
* RESTORE - Enhanced to support a partial restore. A new keyword,
PARTIAL, has been added to the RESTORE command to allow for
restoring a specific DBEFile instead of doing a full restore.
* SETUPRECOVERY - Enhanced to support a partial rollforward
recovery. A new keyword, PARTIAL, has been added to the
SETUPRECOVERY command. SETUPRECOVERY PARTIAL creates a temporary
DBEnvironment and initiates a partial recovery process.
* STORE - Enhanced to support a partial store. A new keyword,
PARTIAL, has been added to the STORE command to allow for storing
a specific DBEFile or DBEFileset.
* STOREONLINE - Enhanced to support a partial store. A new keyword,
PARTIAL, has been added to allow for storing a specific DBEFile or
DBEFileset.
NOTE In the case of the MOVEFILE command, extra prompts have been added
and existing scripts that use this command will need to be modified
accordingly. For all other commands the scripts are forward
compatible. See the "SQLUtil" section of the ALLBASE/SQL Database
Administration Guide (36216-90005) for more details about these new
and updated commands.
Usability
The enhancements described below increase either the functionality or
user-friendliness or both of ALLBASE/SQL.
Cursor Access to Multiple Row Result Sets in Procedures.
Procedures have been enhanced to allow inclusion of SELECT statements
with no INTO clause. Such Procedures can return multiple row results
sets to the caller. The caller can retrieve the results by defining a
cursor on the EXECUTE PROCEDURE statement in the application. This
cursor allows callers to process multiple row result sets from a
procedure one row at a time, either statically or dynamically. Access to
multiple row result sets from a procedure is read-only. When such a
procedure is executed interactively, ISQL fetches the result sets and
allows you to browse through them.
For procedures that return results of a single format, the result format
may be defined and stored in the system catalog, and retrieved through
the DESCRIBE RESULT command. You can also use dynamic input and output
parameters in an EXECUTE PROCEDURE statement, and DESCRIBE INPUT and
OUTPUT for a dynamically prepared EXECUTE PROCEDURE statement.
Support for 1023 Columns.
This enhancement increases the maximum number of columns per table from
the current limit of 255 to 1023. The new limit of 1023 columns is
supported for tables and views. The number of bytes per tuple remains
unchanged at a maximum of 4000 bytes per tuple. For query results, a
maximum of 1024 items can be returned. This allows for returning 1023
columns of a table plus the TID. The maximum number of sort columns is
also increased to 1023 and this is also the new limit for the number of
parameters in a procedure.
Case Insensitivity.
An optional [NOT] CASE SENSITIVE attribute is added to the character and
varchar type column attributes of the two commands CREATE TABLE and ALTER
TABLE. This makes it possible to search and compare such columns in a
case-insensitive manner. Such columns existing in b-tree indexes and
hash table keys are also collated in a case-insensitive manner for best
performance. This feature is supported for n-computer and native
languages. This enhancement also improves the portability of
ALLBASE/SQL.
ISQL HELP Text Improvements.
The HELP command was modified to allow you to request help for entire
commands, rather than just the verb, and to view them as individual
entries. You can now do a help on CREATE TABLE not just a HELP CREATE.
Options such as PARMS, EXAMPLE can be specified.
EXTRACT Command.
The new ISQL EXTRACT command allows you to extract a module or section,
from a DBEnvironment and place it into a module file. This allows a
module to be created from the current DBEnvironment without the need to
repreprocess the application.
The EXTRACT command is also useful when the SETOPT command is used to
modify the access plans of stored sections. You can modify the access
plans with the SETOPT command, then use the EXTRACT command to extract
the module (or updated sections) into a module file. This file can then
be installed into other DBEnvironments to duplicate the module changes
made with SETOPT.
New SQLGEN Commands.
Five new commands have been added to SQLGEN to provide support for the
access plan modification, the application development concurrency
enhancements, and the ALLBASE/REPLICATE product.
The new commands are:
* GENERATE MODOPTINFO - generates ALLBASE/SQL SETOPT and VALIDATE
commands to modify access plans of sections belonging to certain
modules.
* GENERATE PROCOPTINFO - generates ALLBASE/SQL SETOPT and VALIDATE
commands to modify access plans of sections belonging to certain
procedures.
* GENERATE SPACEAUTH - generates ALLBASE/SQL GRANT ON DBEFILESET
commands to grant SECTIONSPACE and TABLESPACE authority.
* GENERATE DEFAULTSPACE - generates ALLBASE/SQL SET DEFAULT
DBEFILESET commands to set default SECTIONSPACE and TABLESPACE to
a certain DBEFileset.
* GENERATE PARTITION - generates ALLBASE/SQL CREATE PARTITION
commands to recreate one or more of the partitions of the
DBEnvironment.
Extended Limits.
This enhancement removes certain ALLBASE/SQL limitations to facilitate
support for very large databases and very large numbers of simultaneous
users.
The features added in this enhancement are:
Support for 4 gigabyte LOG files.
The maximum size of a single DBE log file is increased from 2 gigabytes
to 4 gigabytes. A single DBEnvironment can have up to 34 log files
configured, providing a maximum of 136 gigabytes of log file space.
Pseudo Table Support for an unlimited number of users.
This enhancement removes the limitation of 240 users supported by the
system pseudotables and now provides support for an unlimited number of
users. The five system pseudotables affected by this enhancement are:
SYSTEM.USER, SYSTEM.COUNTER, SYSTEM.CALL, SYSTEM.ACCOUNT and
SYSTEM.TRANSACTION.
Increased memory for Data Buffer Pages and Run Time Control Block Pages.
Shared memory allocation for ALLBASE/SQL, on MPE/iX, has been enhanced to
significantly increase the limits on the number of run time control block
pages (previously 800) and data buffer pages (previously 16,000). With
this G.0 version of ALLBASE/SQL, users can specify up to 50,000 data
buffer pages and up to 2,000 run time control block pages.
The limits set prior to version G.0 were due to the fact that only one 4
Mb shared memory object was allocated for the control structures for the
data buffer pages, the log buffer pages and the run time control block
pages. Now, multiple shared memory objects (up to 72) are allocated for
these control structures. The restrictions on the number of run time
control block pages and data buffer pages is now dependent on the
combinations that can fit into up to seventy two 4 Mb shared memory
objects rather than one 4 Mb shared memory object. This significantly
increases the limits, allowing you to allocate enough data buffer pages
to keep the whole DBEnvironment in memory, for performance reasons, if
desired.
Prevention of Counter Overflow.
ALLBASE/SQL maintains several counters in shared memory to keep track of
statistical information such as number of transactions begun, ended or
aborted, number of page accesses, lock requests and so on. These
counters are accessed by querying the SYSTEM.COUNTER pseudotable.
The internal definition of these counters has been changed to provide
greater precision and to reduce the probability of counter overflow in
long running, or heavily loaded, DBEnvironments. Also the algorithm for
incrementing the transaction serial number counter has been improved to
reduce the probability of overflow of this counter.
In addition to decreasing the incidence of counter overflow, this version
provides better error handling for situations where overflow does occur.
In these cases, an error message is returned to you and a global rollback
is performed.
POSIX Support and MicroFocus COBOL.
The ALLBASE/SQL preprocessors, C, COBOL, FORTRAN and PASCAL, have been
enhanced to run under the POSIX (Portable Operating System Interface)
shell on MPE/iX. Under the POSIX shell, the ALLBASE/SQL preprocessors can
accept byte-stream format files as input and generate byte-stream format
files for the output source code. In addition to reading and generating
the byte-stream formatted files, the preprocessors accept filenames in
HFS (Hierarchical File System) format. The byte-stream formatted source
code can be compiled by the MicroFocus COBOL compiler or by the C
complier available on MPE/iX.
NOTE While the FORTRAN and PASCAL preprocessors can generate byte-stream
formatted source files, the existing FORTRAN and PASCAL compilers
are not capable of compiling source files of this format.
Application Thread Support.
ALLBASE/SQL can now be used in an application threaded environment on
MPE/iX. Internally, ALLBASE/SQL and the ALLBASE/SQL preprocessors
eliminated the use of unguarded global and static variables. Threaded
applications reduce the overhead of context switching and improve the
performance of OLTP applications.
Tools
This version of ALLBASE/SQL introduces an online diagnostic tool, SQLMON,
for monitoring the activity of an ALLBASE/SQL DBEnvironment. Also
introduced in this version is an auditing tool, SQLAudit, for auditing
changes made to the DBEnvironment.
SQLMON Tool.
SQLMON is an online diagnostic tool for monitoring the activity of an
ALLBASE/SQL DBEnvironment. SQLMON screens provide information on file
capacity, locking, I/O, logging, tables and indexes. They summarize
activity for the entire DBEnvironment, or focus on individual sessions,
programs or database components. SQLMON is a read-only utility, and
cannot modify any aspect of the DBEnvironment.
SQLMON is:
* A monitoring tool for tuning your DBEnvironment
* A trouble-shooting tool for detecting performance problems
* A development tool for application programmers
* A tool for learning about your DBEnvironment
* A tool to help the DBA manage your DBEnvironment.
SQLMON is divided into several subsystems, each having a number of
screens that provide different information related to the performance of
the DBEnvironment. The following subsystems are available:
* OVERVIEW - The OVERVIEW subsystem provides an overall perspective
of the DBEnvironment's internal activity.
* IO - The IO subsystem provides information about the performance
of the data and log buffer pools.
* LOAD - The LOAD subsystem provides information about the
throughput and response time of the applications running against
the DBEnvironment.
* LOCK - The LOCK subsystem provides information about LOCK activity
occurring within the DBEnvironment.
* SAMPLEIO - The SAMPLEIO subsystem provides information about the
I/O on tables, indexes, DBEFiles and DBEFilesets, as seen through
the sampling of the data buffer pool.
* STATIC - The STATIC subsystem provides information about the size,
indirection, sparseness and clustering of objects within the
DBEnvironment.
An extensive, context-sensitive online HELP facility is available, which
provides instructions on how to use the tool as well as guidelines on how
to tune your DBEnvironment.
For more information about SQLMON, refer to the ALLBASE/SQL Performance
and Monitoring Guidelines (36216-90102).
SQLAudit.
SQLAudit is an ALLBASE/SQL utility program that can be used to view
changes that have been made to a DBEnvironment. SQLAudit allows you to
audit all changes that have occurred during a specified interval. This
information can be used for administrative or security purposes.
Connectivity and Client Server
Enhancements to ALLBASE/SQL connectivity are described in this section.
ALLBASE/NET Enhancements.
Enhancements in ALLBASE/NET for G.0:
* Improved performance of ALLBASE/SQL Remote Database Access (RDA)
* More client connections allowed on a server system
* Finer DBA control of RDA server process
* Reduction in number of programs - there is one listener program
instead of three for ALLBASE/SQL RDA to MPE/iX
All listeners are now started through the ANSTART program: ANSTART
[ARPA, NS, NetWare]. They are stopped by ANSTOP [ARPA, NS, NetWare].
The status of listeners can be checked by the ANSTAT program: ANSTAT
[ARPA, NS, Net] [NUMSERVERS]. The NUMSERVERS option gives the number of
network connections to the server. Options are added to the ADD ALIAS
and CHANGE ALIAS to allow RPA and NS datacomm type values.
In the previous version each connection was generating a job. From this
version only one job is running per listener.
ALLBASE/NET ReDesign.
The ALLBASE/NET listener for NetWare has been changed to work with the
newly ported 3.11 version of Novell NetWare for UNIX (that is, Portable
NetWare).
Changed Restrictions for Executing NETUTIL Commands.
The requirements for executing the ADD ALIAS, CHANGE ALIAS, DELETE ALIAS,
ADD USER, CHANGE USER, DELETE USER, and SHOW USER commands on MPE/iX are
enhanced as follows:
1. Must be MANAGER.SYS (currently the only allowed user), or
2. Must have SM capability, or
3. Must have AM capability in the account specified in the command.
No changes have been made for executing these commands on HP-UX.
Migration Issues for SERVER/CLIENT using HP-UX version 10.0 or greater..
Beginning with HP-UX 10.0, ARPA will be the only TCP/IP interface for
data communication through ALLBASE/NET. HP-UX supports other data
communication types, however, ALLBASE/NET only supports ARPA. This
affects all new and existing applications that use ALLBASE/NET.
Remote database access applications that specify NS as the data
communication type will not work if the client and/or server machine is
an HP 9000 S700 or S800 running HP-UX 10.0 or greater. Server Node Name
entry must be changed from NS node name to ARPA host name. For the
NetUsers file, the "client Node Name" must be changed from the NS node
name to the ARPA host name. Two new commands are added to NETUTIL to
migrate the AliasDB and NetUsers files. MIGRATE ALIAS and MIGRATE USER
are added to migrate the AliasDB and NETUsers files.
Changes, described below, must be made to NETUTIL since this is where
"Datacomm Type" information is entered.
1. NETUTIL commands which prompt you to specify a "Datacomm Type",
i.e., ADD ALIAS and CHANGE ALIAS, will issue error or warning
messages when
a. The client and/or server machine is an HP 9000 S700 or S800
(the HP 9000 S300 and S400 are not supported on ALLBASE/SQL
G.0), and
b. NS is entered as the "Datacomm Type".
2. Existing applications that run on an HP 9000 S700 or S800 client
and/or server, and use NS as its "Datacomm Type" must now use ARPA
Services instead (see table 1-1 for all client / server
combinations that are affected). This will be accomplished by
making changes to the alias profiles in the AliasDB file on the
client system, and to the user profiles in the NETUsers file on
the server system. For the AliasDB file :
a. "Datacomm Type" must be changed from NS to ARPA, and
b. "Server Node Name" entry must be changed from the NS node
name to the ARPA host name
For the NetUsers file, the "Client Node Name" must be changed from
the NS node name to the ARPA host name.
ALLBASE/PC API/ODBC.
The ALLBASE/PC API/ODBC product is now bundled with ALLBASE/SQL.
PC API is an application programming interface that allows tools written
with either the PC API/GUPTA interface, or the ODBC interface, to access
ALLBASE/SQL and IMAGE/SQL, on a 3000 or a 9000 server, from a PC.
Windows socket (WIN.DLL) is now supported for PC/API (GUPTA and ODBC
interface).
Special Considerations
ALLBASE/SQL is auto-installable. However, if you are updating from an
earlier release of ALLBASE/SQL, you must perform the ALLBASE/SQL
migration to migrate your DBEnvironments to the G.0 format.
\ \ \ Important Details \ Please Read If you are updating from an
ALLBASE/SQL Release prior to E.1,
you must first update to
ALLBASE/SQL Release E.1 or F.0,
and then perform the ALLBASE/SQL
migration to update to release
G.0.
To migrate your ALLBASE/SQL databases to this new version, 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 DBEnvironment from the
E.1, or F.0, format to the G.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 the SHOWDBE command to make sure
all parameters are OK.
_________________________________________________________________
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
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;
Note that, since PREVIEW is not a read-only command, you should
make sure that you have a backup of the DBEnvironment prior to
issuing the PREVIEW command.
During the PREVIEW 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:
MIGRATE=> MIGRATE 'DBEnvironmentName' FORWARD;
When the forward migration has successfully completed, SQLMIG
purges the old log files and performs a START DBE NEWLOG to create
a new logfile using the parameters stored in the DBECON file. For
example:
START DBE NEWLOG BEGINNING (MON, JUL 19, 1993, 4:12 PM)
START DBE 'DBEname' NEWLOG
BUFFER = (100,24),
TRANSACTION = 2,
MAXIMUM TIMEOUT = NONE,
DEFAULT TIMEOUT = MAXIMUM,
RUN BLOCK = 37
LOG DBEFILE log1 WITH PAGES = 250,
NAME = 'DBELog1';
START DBE NEWLOG SUCCEEDED (MON, JUL 19, 1993, 4:13 PM)
The DBEnvironment is ready to be accessed! If you desire archive mode
logging, you must run SQLUtil and issue a STOREONLINE command.
7. Exit SQLMIG as follows:
SQLMIGRATE=> EXIT;
8. If the START DBE NEWLOG, issued by SQLMIG, should fail for any
reason, you may run ISQL and issue the START DBE NEWLOG from ISQL.
9. 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:
Up and Running with ALLBASE/SQL (36389-90011)
ALLBASE/SQL Reference Manual (36216-90001)
ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL (36216-90096)
ALLBASE/SQL Database Administration Guide (36216-90005)
ALLBASE/SQL Message Manual ( 36216-90009)
ALLBASE/SQL Advanced Application Programming Guide (36216-90100)
ALLBASE/NET User's Guide (36216-90031)
ALLBASE/SQL Performance and Monitoring Guidelines (36216-90102)
HP PC API User's Guide for ALLBASE/SQL and IMAGE/SQL (36216-90104)