If you want to upgrade to a new DB2® Version 9.7 server, recreate
your instances and then upgrade your databases by restoring a pre-Version
9.7 database backup. After restoring the database backup, the RESTORE
DATABASE command automatically runs the UPGRADE
DATABASE command.
About this task
Restrictions
Procedure
To upgrade to a new DB2 Version
9.7 server:
- Perform a full offline database
backup of your existing databases and any other pre-upgrade
tasks that apply. If you performed a full offline
or online database backups recently and you cannot perform another
one before upgrade, you can perform an incremental offline database
backup instead.
- Log on to the new DB2 server
as root on Linux and UNIX operating systems or user
with Local Administrator authority on Windows operating systems.
- Install DB2 Version
9.7 on the new DB2 server.
- Create your instances on the new DB2 server by running the db2icrt command
from the DB2 Version 9.7 copy
location that you installed in the previous step.
If the new DB2 server has similar
resources, then restore the database manager configuration parameter
values for each instance using the UPDATE DBM CFG command
and the values that you saved in the pre-upgrade tasks.
- Optional: Create a new DB2 Administration
Server (DAS) on DB2 Version
9.7. You need a DAS if you want to keep your existing DAS
configuration and use new functionality available in DB2 Version 9.7. If your DAS is running on DB2 UDB Version 8, upgrade it to
use the Control Center to administer your instances running under DB2 Version 9.1 or later.
- Transfer pre-Version 9.7 backup files for all the databases
that you want to upgrade to the new DB2 server.
- Log on to the DB2 server
as a user with SYSADM authority.
- Upgrade the database using the RESTORE DATABASE command.
The following example shows how to restore the sample database on UNIX operating systems:
db2 RESTORE DATABASE sample FROM /db2/backups
where sample is
the database name and /db2/backups is the directory
for the database backup file.
If you performed an incremental
offline database
backup before upgrade, you must have access to the most recent full
offline database
backup and the incremental
offline database backup and
use an automatic incremental restore to upgrade
the database. A
manual incremental restore will fail because each RESTORE DATABASE
command tries to upgrade the database before the database is completely
recovered. The following example shows how to perform an automatic
incremental restore:
db2 RESTORE DATABASE sample INCREMENTAL AUTOMATIC
TAKEN AT timestamp WITHOUT PROMPTING
In a partitioned database environment, you must
execute the RESTORE DATABASE command in all database
partitions starting with the catalog partition first.
- When the database was restored but the database was not
upgraded, the RESTORE DATABASE command returns
the following error and includes the upgrade error message with the
reason code:
SQL2519N The database was restored but the restored database was not upgraded
to the current release. Error "-1704" with tokens "3" is returned.
SQLSTATE=57011
The error message SQL1704N indicates
the database upgrade failed. Find this SQL error code in the Message Reference Volume 2 to
read the list of the possible solutions for each reason code. In
the previous example, tokens "3" means reason code 3 which indicates
that the upgrade failed because the database logs are full. If this
error occurs, complete the following steps to upgrade the database:
- Increase the size of the log files.
- Upgrade the database using the UPGRADE
DATABASE command.
- If the log file size is still not large enough, the following
error is returned:
SQL1704N Database upgrade failed. Reason code "3".
You
must increase the log file size and attempt to upgrade the database
again.
After the database upgrade is completed reset
the size of the log files to their pre-upgrade values.
- Optional: Configure your new DB2 server
to use the new resources available by running the AUTOCONFIGURE command to calculate
the buffer pool sizes, and the database manager and database configuration
parameters values. The following example shows how to run this command
to only display recommended values for the sample database:
db2 CONNECT TO sample
db2 AUTOCONFIGURE USING MEM_PERCENT 80
WORKLOAD_TYPE complex
NUM_STMTS 1 TPM 73
ADMIN_PRIORITY performance
IS_POPULATED YES
NUM_REMOTE_APPS 15
ISOLATION CS
APPLY NONE;
If you choose not to run this command or
not to apply the recommended values, manually configure your DB2 server to use the new resources.
Otherwise, your databases might not perform as expected.
- Restore any external routines that
you backed up in the pre-upgrade tasks.
- Verify your database upgrade is successful. Connect to
the upgraded databases and issue a small query:
db2 CONNECT TO sample
Database Connection Information
Database server = DB2/AIX64 9.7.0
SQL authorization ID = TESTDB2
Local database alias = SAMPLE
db2 "SELECT * FROM SYSCAT.DBAUTH"
Alternatively, if
you have sample files installed, run the testdata.db2 script:
cd samplefile-dir-clp
db2 connect to sample
db2 -tvf testdata.db2
where samplefile-dir-clp is DB2DIR/samples/clp on Linux and UNIX and DB2DIR\samples\clp on Windows, DB2DIR represents the
location specified during DB2 Version
9.7 installation, and sample is the database name.
What to do next
After upgrading the DB2 server,
perform the recommended post-upgrade tasks such
as resetting the diagnostic error level, adjusting log space size,
and rebinding packages. In addition, verify
that the upgrade of your DB2 server was
successful.