How do you upgrade the DB2 database from v9.1 to v9.5 when upgrading Tivoli Workload Scheduler (TWS) v8.3 or v8.4 to TWS v8.5?
Migrating a DB2 server (Linux and UNIX for use with vTWS 8.5)
Migrating a DB2® Version 9.1 or DB2 UDB Version 8 server to DB2 Version 9.5 on Linux® and UNIX® requires that you install a new DB2 Version 9.5 copy and then manually migrate your existing instances and databases to this new copy.
Before you begin:
This migration task describes the steps for direct migration from DB2 Version 9.1 or DB2 UDB Version 8 to DB2 Version 9.5 regardless of the instance bit size. Review migrating environments with specific characteristics and determine which task applies better to your environment.
Before migrating the DB2 server:
- Ensure that you have root access.
- Ensure that you meet the installation requirements for DB2 database products. See Installation requirements for DB2 database products. The requirements for Linux and UNIX operating systems have changed.
- Review migration recommendations and disk space requirements.
- Perform pre-migration tasks.
- On Linux and UNIX operating systems except for Linux on x86, your existing 32-bit or 64-bit instances are migrated to DB2 Version 9.5 64-bit instances. The operating system and DB2 Version 9.5 database product that you installed determines the instance bit size, see Support changes for 32-bit and 64-bit DB2 servers for details.
- Additional migration restrictions apply. Review the complete list.
About this task:
To migrate a DB2 server from DB2 UDB Version 8 or DB2 Version 9.1 to DB2 Version 9.5:
1. Log on to the DB2 server as root.
2. Install DB2 Version 9.5. Run the db2setup command and select the Install New option on the Install a Product panel to install a new copy of DB2 Version 9.5.
3. Migrate instances from the same installation path that you indicated during DB2 Version 9.5 installation. Any 32-bit instances are migrated to DB2 Version 9.5 64-bit instances except for Linux on x86.
4. Optional: Migrate your DAS if you want to keep your existing DAS configuration and use new functionality available in DB2 Version 9.5. If your DAS is running on DB2 UDB Version 8, you need to migrate it to use the Control Center to administer your DB2 Version 9.5 and Version 9.1 instances.
5. Migrating databases - (below)
After you have migrated your instances to DB2® Version 9.5, you need to migrate each database under each instance.
Before you begin:
- Ensure that you have SYSADM authority.
- Ensure that all the local databases that you want to migrate are cataloged.
- Ensure that you backed up your databases as indicated in the pre-migration tasks.
- You must have DB2 Version 9.5 installed and migrate the instance to DB2 Version 9.5.
- Review the migration restrictions for database migration.
About this task:
To migrate a DB2 database:
1. Log on to the DB2 server as the instance owner or a user with SYSADM authority.
2. Optional: Rename or delete the db2diag.log file so that a new file is created. Also, remove or move to another directory any existing dump files, trap files, and an alert log files in the directory indicated by the diagpath parameter. By doing this, the files only contain information about the migration process that helps you to isolate and understand any problem that might occur during database migration.
3. Migrate the database using the MIGRATE DATABASE command:
db2 MIGRATE DATABASE database-alias USER username USING password
where database-alias is the name or the alias of the database you want to migrate and the username and password to authenticate a user with SYSADM authority.
4. If the database migration fails and returns the error message SQL1704N with a reason code that describes the cause of the failure, find this SQL error code and determine the list of the possible solutions for each reason code. One of the most common causes of migration failure is that the log file space is not large enough, in which case the following error is returned:
SQL1704N Database migration failed. Reason code "3".
You must increase log file size and execute the MIGRATE DATABASE command again. Once the database migration is complete reset the value of logfilsiz, logprimary, and logsecond database configuration parameters.
There are additional error codes that are returned by the MIGRATE DATABASE command for specific cases not supported by database migration. These cases are described in the migration restrictions.
5. If the database migration returns the warning message SQL1243W, you need to drop or rename the SYSTOOLS.DB2LOOK_INFO table. Otherwise, the ALTER TABLE and COPY SCHEMA statements will fail to run. Check if the SYSTOOLS.DB2LOOK_INFO table exists by running the following command:
db2 "SELECT tabname, tabschema, definer FROM syscat.tables
WHERE tabschema = 'SYSTOOLS' AND tabname = 'DB2LOOK_INFO'"
If you created this table, simply rename it by running the RENAME statement:
db2 RENAME SYSTOOLS.DB2LOOK_INFO TO new-table-name
If you did not create this table, simply remove it by running the DROP command:
db2 DROP TABLE SYSTOOLS.DB2LOOK_INFO
6. If you have external unfenced routines on Linux® or UNIX® that have no dependency on the DB2 engine libraries, the MIGRATE DATABASE command redefines your external routines as FENCED and NOT THREADSAFE and returns the warning message SQL1349W.
This command also generates a script called alter_unfenced_database-name.db2 with all the SQL statements to redefine external unfenced routines, altered during the database migration, as NOT FENCED and THREADSAFE. This script is created in the directory specified by the diagpath database manager configuration parameter. If the diagpath parameter is not set, the script is created in the INSTHOME/sqllib/db2dump directory where INSTHOME is the instance home directory. See Migrating C, C++, and COBOL routines for details on how to safely run your routines in the new multithreaded database manager.
7. Compare your database configuration settings after migration with the configuration settings you had before you migrated your database. Verify the following settings and database information are the same:
- database configuration parameter settings
- table spaces information
- packages information for your applications only
8. Verify your database migration is successful. Connect to the migrated databases and issue a small query:
db2 connect to sample
Database Connection Information
Database server = DB2/AIX64 9.5.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:
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.5 installation, and sample is the database name.
What to do next:
After migrating a DB2 database, performing the recommended post-migration tasks ensures a successful database migration.