HP 3000 Manuals HP 3000 Manuals
Running SQLMigrate
SQLMigrate is an ALLBASE/SQL utility that lets you migrate a
DBEnvironment between releases of ALLBASE/SQL.
To run SQLMigrate, use the following command:
:RUN SQLMIG.PUB.SYS
You will see the SQLMigrate banner and this prompt:
SQLMIGRATE=>
Terminate SQLMigrate commands with a semicolon.
The SQLMigrate commands are described in the appendix, "SQLMigrate," of
the ALLBASE/SQL Database Administration Guide.
Forward Migration
There are two cases of forward migration to consider:
1. Migrating a DBEnvironment without audit logging into audit logging
releases.
2. Migrating a DBEnvironment with audit logging into audit logging
releases.
For both cases, all tables are placed in the default partition. In the
first case, no audit logging is enabled for the DBEnvironment. In the
second case, the only audit logging element allowed is DATA because
SQLMigrate does not support any other audit elements.
If you migrate an audit logging DBEnvironment, it should not be migrated
to a non-audit logging DBEnvironment. Therefore, you must specify AUDIT
LOG in any START DBE NEWLOG statement that SQLMigrate performs.
Steps for Forward Migration.
1. Prepare for updating the operating system and the ALLBASE/SQL
software, by doing the following steps:
a. Run ISQL and issue a START DBE statement for each
DBEnvironment that will be migrated. This ensures that the
DBEnvironment is logically consistent.
b. Run SQLUtil 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.
c. Backup the ALLBASE/SQL software.
d. Backup the operating system, if you are updating the
operating system.
2. Update the operating system (if required) using installation
instructions.
3. Update the ALLBASE/SQL software using installation instructions.
4. Run the migration program:
:RUN SQLMIG.PUB.SYS
5. Issue the SHOW VERSIONS command to find which versions can be used
as the version parameter:
SQLMIGRATE=>SHOW VERSIONS;
VERSION RELEASE
E HP36216-02A.E
F HP36216-02A.F
G HP36216-02A.G
SQLMIGRATE=>
6. Issue the PREVIEW command in SQLMigrate for each DBEnvironment
that will be migrated. This checks for errors that might occur
during migration.
PREVIEW 'DBEnvironmentName' FORWARD [TO 'Version'];
The version parameter is optional. The most recent version
supported by SQLMigrate is the default.
If you receive the message that there is not enough space in the
SYSTEM DBEFileSet to complete the migration successfully, the
number of DBEFile pages needed is returned. Use the following
commands in SQLMigrate to create a new DBEFile and add it to the
SYSTEM DBEFileSet:
CREATE DBEFILE DBEFileName WITH PAGES = DBEFileSize, NAME
='SystemFileName';
ADD DBEFILE DBEFileName TO DBEFILESET SYSTEM;
Repeat this step until no errors are encountered and SQLMigrate
returns the message:
The proposed migration should be successful.
7. Issue the MIGRATE command to modify the DBEnvironment so that it
is compatible with this release of ALLBASE/SQL. This is the
MIGRATE FORWARD syntax:
MIGRATE 'DBEnvironmentName' FORWARD [TO 'Version'];
The version parameter is optional. The most recent version
supported by SQLMigrate is the default.
When the MIGRATE command is finished, SQLMigrate automatically
purges the old user log files and performs a START DBE NEWLOG
statement to create new user log files. The options of the START
DBE NEWLOG statement match the startup parameters contained in the
DBECon file. SQLMigrate does not issue a START DBE NEWLOG
statement if the NEWLOG option has been set to OFF.
If the START DBE NEWLOG statement fails, you must exit from
SQLMigrate, run ISQL, and issue a START DBE NEWLOG statement.
This creates a new log file that is compatible with the target
release of ALLBASE/SQL. You cannot use the DBEnvironment until the
START DBE NEWLOG statement executes successfully.
8. Backup the migrated DBEnvironment immediately after the START DBE
NEWLOG statement completes successfully.
If you wish to use nonarchive logging, run SQLUtil and use the
STORE command.
For archive logging, you should choose one of the following:
a. If you have TurboSTORE software, do a concurrent backup
using the SQLUtil STOREONLINE command.
b. If you do not have TurboSTORE, issue the following SQL
statements in single user mode in ISQL:
:RUN ISQL.PUB.SYS
isql=> BEGIN ARCHIVE;
isql=> COMMIT ARCHIVE;
Exit from ISQL and run SQLUtil, then issue the STORE
command.
9. Run SQLUtil to check the DBEnvironment.
a. Issue the SHOWDBE command to check the parameters of the
new version of the DBEnvironment.
b. Use the ALTDBE command if changes are necessary.
c. Use the SHOWLOG command to display current log information.
You may have received a message stating that stored sections were
invalidated. This is to be expected. Stored sections will be
revalidated automatically when they are executed. For some customers,
production may be faster if revalidation is done before that time.
Revalidation can be accomplished by preprocessing the application
programs that contain the stored sections.
If you are migrating from release F (or later), you can revalidate stored
sections with the VALIDATE statement in ISQL.
If you encounter errors during any step of the migration that you do not
understand, write down the error number and what you were doing, and
contact your HP Service Representative or Response Center.
JCWs Set by SQLMigrate
When running SQLMigrate in batch mode, your job can check the following
JCWs to ensure that SQLMigrate completed successfully.
Table E-1. JCWs Set by SQLMigrate
--------------------------------------------------------------
| | | |
| JCW Name | Contents | Range |
| | | |
--------------------------------------------------------------
| | | |
| MIGERR | number of SQL errors | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| MIGWARN | number of SQL warnings | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| JCW | FATAL if SQL error | FATAL or 0 |
| | encountered, | |
| | 0 if SQL error not | |
| | encountered. The system JCW | |
| | is set only if the SET | |
| | EXIT_ON_DBERR command is ON. | |
| | | |
--------------------------------------------------------------
Backwards Migration
Previous releases do not support partitions. Therefore, there is only
one supported option for backwards migration of a DBEnvironment with
audit logging in use. That option is to place all the tables in the
default partition with DATA as the only audit logging element specified.
Then the DBEnvironment can be migrated backwards unchanged.
If the audit elements include something other than DATA, one of the
following may happen:
* The migration is not allowed to proceed.
* The elements are reset to be only DATA.
One reason for a backwards migration is to restore a DBEnvironment to its
state before it was migrated to a new release.
Steps for Backwards Migration
1. Issue the SHOW VERSIONS command to find which versions can be used
as the version parameter:
SQLMIGRATE=>SHOW VERSIONS;
VERSION RELEASE
E HP36216-02A.E
F HP36216-02A.F
G HP36216-02A.G
SQLMIGRATE=>
2. Prior to restoring the backup version of the operating system and
ALLBASE/SQL, do the following for each DBEnvironment that will be
migrated:
a. Run ISQL and issue a START DBE statement. This ensures
that the DBEnvironment is logically consistent.
b. Run SQLUtil and issue the STORE command to make a backup of
the DBEnvironment.
Note: Log files are not stored using this command.
Application programs associated with the DBEnvironment must
be backed up separately.
c. Backup the ALLBASE/SQL software.
d. Backup the operating system, if you just updated it.
3. Run SQLMigrate:
:RUN SQLMIG.PUB.SYS
4. Issue the PREVIEW command in SQLMigrate to check for errors that
might occur during migration. The DBEnvironment is not modified
during this command. The syntax for the PREVIEW of a backward
migration is:
PREVIEW 'DBEnvironmentName' BACKWARD TO 'Version';
If you receive the message that there is not enough space in the
SYSTEM DBEFileSet to complete the migration successfully, the
number of DBEFile pages needed is returned. To increase the
number of pages, add another DBEFile to the SYSTEM DBEFileSet.
Use the following syntax in SQLMigrate:
CREATE DBEFILE DBEFileName WITH PAGES =DBEFileSize, NAME
='SystemFileName';
ADD DBEFILE DBEFileName TO DBEFILESET SYSTEM;
Repeat this step until no errors are encountered and SQLMigrate
returns the message:
The proposed migration should be successful.
5. Issue the MIGRATE command to modify your DBEnvironment to make it
compatible with the old release of ALLBASE/SQL. This is the
MIGRATE BACKWARD syntax:
MIGRATE 'DBEnvironmentName' BACKWARD TO 'Version';
6. Restore the backup version of ALLBASE/SQL. If necessary, restore
the backup version of the operating system.
7. For each DBEnvironment that was backward migrated, do the
following:
a. Run ISQL and issue the START DBE NEWLOG statement. This
creates a new log file that is compatible with the old
release of ALLBASE/SQL.
Note that you will not be able to use your DBEnvironment
after it has been migrated until this step has been
completed.
SQLMigrate does not automatically perform a START DBE
NEWLOG statement during backward migration.
b. Run SQLUtil and issue the SHOWDBE command to check the
parameters of the new version of the DBEnvironment. Use
the ALTDBE command if changes are necessary.
c. Use the following steps, if you wish to enable archive mode
logging:
i. If you have TurboSTORE software, run SQLUtil and use
the STOREONLINE command.
ii. If you do not have TurboSTORE, issue the following
SQL statements from ISQL:
isql=> BEGIN ARCHIVE;
isql=> COMMIT ARCHIVE;
Exit from ISQL and run SQLUtil, then issue the STORE
command.
d. Drop views and any stored sections created under the later
release, because these views and sections are no longer
usable. Refer to the DROP VIEW and DROP MODULE commands in
the ALLBASE/SQL Reference Manual. Do not try to install
modules that were created under the release you are
migrating back from as they will not be compatible with the
release of ALLBASE/SQL you are migrating to.
You may have received a message stating that stored sections were
invalidated. This is to be expected. Stored sections will be
revalidated automatically when they are executed. For some customers,
production may be faster if revalidation is done before that time.
Revalidation can be accomplished by preprocessing the application
programs that contain the stored sections.
If you are migrating from release F (or later), you can revalidate stored
sections with the VALIDATE statement in ISQL.
If you encounter errors during any step that you do not understand, write
down the error number and what you were doing, and contact your HP
Service Representative or Response Center.