Migrate DB2 V7 databases to DB2 UDB Version 8.1

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Introductory

Clara Liu (claraliu@ca.ibm.com), Database Consultant, IBM Toronto Laboratory

04 Mar 2004

Learn the benefits of the latest version of DB2 and how easy it is to migrate to. This article summarizes the new features and enhancements of the V8.1.4 update. It then discusses the complete migration process including the preparation, the actual migration, and the post-migration tasks. Hints and tips are highlighted to help you perform a smooth and problem-free migration.

Overview of DB2 UDB V8 enhancements

In today's competitive and ever-changing business environment, organizations are constantly challenged to provide their business with high availability, scalability, usability, manageability, and performance. With more than 438 new features and enhancements as of the availability of DB2 UDB V8.1.4 update in November 2003, DB2 helps you to meet these challenges. The What's New in V8 manual summarizes all the new features and improvements, detailed discussion on each feature can be found in the DB2 product manuals.

Manageability enhancements

• Database maintenance mode forces all users off the instance and put it into quiesced mode
• Online database checking tool
• Logging enhancements that allow larger log size, infinite log space, enhanced log bandwidth, and etc
• Multi-FixPak install for UNIX®
• Kerberos security for UNIX
• Efficient buffer pool memory allocation
• Referential integrity enhancements for delete-connected tables
• Row migration in UNION ALL views
• Online import
• Compressed backup

Usability enhancements

• Enhanced Control Center with new wizards such as Performance configuration wizard
• New command support
• Health Center to provide automated server-side health monitoring
• Memory Visualizer graphically monitors memory usage in the data engine such as sort heaps, buffer pools, and caches

Availability enhancements

• Online reorganization enhances database availability by allowing read and write table access during reorganization
• Online index maintenance provides full read and write access to tables during index creation
• Online load allows concurrent access to tables in the same table space as the table is being loaded
• Runstats enhancement
• NULL and default value compression to reduce data storage

Performance enhancements

• Full 64-bit support for AIX®, Solaris®, HP-UX®, Linux, and Windows
• Connection Concentrator to support large number of concurrently connected users
• Multi-dimensional clustering to provide range partitioning on multiple dimensions, reduce need for indexing, and roll-in / roll-out improvements
• Enhanced global temporary tables
• Threading architecture for Java stored procedures and user-defined functions
• Enhanced catalog caching on all nodes in MPP systems
• User-maintained summary tables
• Range-clustered tables that are implemented as sequential clusters of data to provide fast and direct access
• Page cleaning enhancements
• Asymmetric index splitting
• Lock deferral to improve concurrency
• Direct I/O support in AIX

Application development enhancements

• New DB2 Development Center provides tight integration between DB2 and WebSphere, DB2 and Visual Studio
• Development of user defined functions using the Development Center to build, deploy, test, and debug UDFs written in SQL, Java™, C, OLE, and OLEDB
• Enhanced Java support includes JDBC 3 certification, Type 4 JDBC driver, and Java common client
• OLE DB enhancements to provide additional functions and better performance

SQL enhancements

• New MERGE SQL statement allows single SQL statement to process separate operations more efficiently
• Insert through UNION ALL view
• Identity and sequences support in partitioned database environment
• Support of INSTEAD OF Triggers that allows views to be updateable
• Informational constraint can be used in query rewrite but are not enforced by the database manager
• Additional and/or enhanced user-defined functions such as TO_CHAR, TO_DATE, ROUND, FLOOR, CEILING, TRUNC
• Dynamic scrollable cursors support in DB2 CLI
• CLI LOAD fullselect support
• Result set retrieval from SQL data change operations
• Searched INSERT, UPDATE, DELETE, and MERGE statements against fullselects
• Support of SQL statements that modify SQL data within SQL table functions
• Microsoft Visual Studio .NET support
• DB2 .NET Data Provider support

Back to top

Product and packaging changes in DB2 UDB V8

A few product and packaging changes were introduced in ® DB2® Universal Database™ V8 GA and V8.1.2. For your convenience, the changes are summarized in the following table:

DB2 V8 product and packaging changes

Back to top

Preparing for the migration

Before we jump right into the migration steps, you should be aware of the prerequisites to be prepared for the migration.

Migration road map

Since many new features and enhancements were added, not every older version of DB2 UDB can be migrated to Version 8 directly. See the chart below for possible migration paths. To obtain the current version and release level of DB2 UDB, enter the command db2level in an OS command prompt or DB2 Command Window. You should receive a result similar to this:

DB2 V8 possible migration paths

* For the Linux platform, the source migration platform must be at DB2 Version 6 FixPak2.

For a complete listing of the migration restrictions, refer to the DB2 Server Edition Quick Beginnings Guide, Chapter 4 Migration Considerations.

Back-level server and client support

The DB2 client and server layer has been redesigned in DB2 V8 to use the open DRDA (Distributed Relational Database Architecture) communication protocol. The enhanced Common Client architecture reduces code path length for client-server communication and allows other enhancements such as 32/64 bit client/server interoperability.

DB2 V8 servers will continue to provide back-level client support. Due to significant communication protocol changes, you should be aware of some restrictions and limitations when accessing DB2 V7 servers from DB2 V8 clients. Therefore, you should migrate all the DB2 servers to Version 8 first before migrating the clients. A list of known restrictions and limitations for the 'DB2 V8 clients to DB2 V7 servers' environment can be found in the What's New in V8 manual, Chapter 1 Introduction to DB2 Universal Database Version 8.

Here are few client/server connectivity scenarios to consider when planning the migration.

There is no restriction to connect DB2 V8 32-bit or 64-bit clients to DB2 V8 32-bit or 64-bit servers.

If you were to connect DB2 V7 32-bit clients to DB2 v8 32-bit servers, there is no restriction in a single partition database environment but limited restriction in partitioned environment. There is no DB2 V7 client support in a DB2 V8 partitioned database environment for the SET CLIENT CONNECT_NODE or ATTACH_NODE options or for a utility flow that requires an ATTACH command. If these commands are needed, DB2 V8 client must be used instead.

There are many restrictions with this client/server connectivity. For a complete list of the restrictions, please refer to the What's New in V8 manual, Chapter 1 Introduction to DB2 Universal Database Version 8.

Since DB2 V7 32-bit clients cannot work with 64-bit servers, for such clients to connect to DB2 V8 64-bit servers, you can use one of the following implementations.

As shown in Figure 2, you set up a DB2 Connect Server V8 32-bit to accept database requests from the DB2 V7 32-bit clients. The requests will then route to the DB2 server V8 64-bit installed.

Alternatively, create a 32-bit DB2 Connect Gateway instance on the target DB2 64-bit server. The instance is a loopback node that points to the local database that the client is trying to connect. You need to catalog a loopback node at the server. Then, catalog the local database to use the loopback node at the client. See example below.

Catalog a loopback node at the server:

$ db2 catalog db sample as loopsam at node loopnd

Catalog a local SAMPLE database to use the loopback node at the client:

$ db2 catalog db loopsam as loopsam at node loopndc

Space requirements

System catalog tables are updated during the migration. To ensure there is enough space to hold both the old and new database catalogs, the following is recommended:

• The system catalog table space should have approximately twice the space currently occupied.
• The temporary table space should have approximately twice the space of the system catalog table space.
• The values of logfilsiz, logprimary, and logsecond should be doubled to prevent running out of log file space.
• There are occasions that migrations might fail if buffer pools are too small. Ensure size of the buffer pools are large enough for new database objects to be created.
• Ensure 2GB available free disk space for DB2 V8 temporary target directory.
• Ensure 900MB available free disk space for DB2 V8 target directory.
• On UNIX, before running db2imigr command make sure enough space is available in /tmp because migration trace file is written into this directory.

Backup databases and DB2 settings

Take an offline database backup for each local database on the server. They are required to back out of the migration.

>>-BACKUP--+-DATABASE-+--database-alias------------------------->
           '-DB-------'

>--+-------------------------------------+---------------------->
   '-USER--username--+-----------------+-'
                     '-USING--password-'			

You should also keep a record of the followings for verification purpose after the migration.

• DB2 database manager configuration parameters for each instance. In a partitioned database system, save a copy for each computer.
• db2 get dbm cfg
• DB2 database configuration parameters for each local database. In a partitioned database system, save a copy for each computer.
• db2 get db cfg for [database-name]
• DB2 registry variable settings.
• db2set -all
• Table spaces for each database to be migrated
• db2 list tablespaces show detail
• Packages for each database to be migrated.
• db2 list packages

Change diagnostic error level to 4

Update the database manager configuration to set the diagnostic error level to 4. This captures all errors, warnings, and informational messages during the migration. They can be used for problem determination should you encounter migration errors. Update the DBM configuration with this command:

Take the DB2 server offline

This step ensures that all DB2 processes are terminated before the migration. Procedures to take the DB2 server offline are:

1. Stop all command line processor sessions by entering the db2 terminate command in each session.
2. Disconnect all applications and users by issuing db2 force application all.
3. Stop the DB2 license service by entering the db2licd -end command.
4. On Windows 2000, the properties of a service can be set so that it restarts if the service fails. If the restart on failure option is set for any DB2 services, it must be disabled.
5. Stop each database manager instance by issuing the db2stop command.
6. On Windows platform check the Task Manager, there should be no db2 processes running.
7. On UNIX and Linux platforms, verify that there are no db2 processes by issuing the command ps -ef |grep db2. Note that option for the ps command may vary on different platform and shell.

Verify databases before migration

Before a database can be migrated, it has to be in a consistent state, not in backup pending state, and not in roll forward pending state. In additional, table spaces in the database, must be in normal state. A pre-migration tool is shipped with the DB2 V8 product CD to help you in verifying the required database and table space states.

>>-db2ckmig--+-database-+-- -l filename--+------------+--+--------------+-->
             '- -e -----'                '- -u userid-'  '- -p password-'

			
				Command parameters
				
				database
    specifies an alias name of a database to be scanned
			
				-e
    specifies that all local cataloged databases are to be scanned
			
				-l
    specifies a log file to keep a list of errors and warnings generated for the scanned database
			
				-u
    specifies the user ID of the system administrator
			
				-p
    specifies the password of the system administrator's user ID

To verify the state of a database:

1. Log on as the instance owner
2. Issue the db2ckmig command
• Example: db2ckmig sample -l c:\db2v8mig\chkmig.log

Check the log file. The log file displays the errors that occur when the db2ckmig command is run. Check that the log is empty before continuing with the migration process.

It is also recommended to run db2dart against the databases to check for database structure consistency before the migration. The db2dart command can be as simple as:

Review the output file for warnings and errors.

What gets migrated

Should you run into problems with the migration, it is helpful to know what gets migrated. For your information, the following will be migrated:

• Database configuration file
• Log file header
• Buffer pool files
• Catalog tables
• Index root page
• History file
• Table space files (DataJoiner V2.1.1 only, support in V8.1 FixPak 1)

In addition, new objects are also created:

• New system catalog tables
• New system views
• New user-defined functions
• New files in the database directory

Note that user data will NOT be migrated.

Back to top

Migrating to DB2 UDB V8

Ensure that you have reviewed and performed the pre-migration steps before continuing with the migration.

Install DB2 V8 code on the server

Since multiple versions of DB2 can be installed on a UNIX or Linux server, if you already have DB2 Version 7 on the server, simply follow the standard procedure to install DB2 Version 8 on the same server.

On the other hand, Windows allows only one version of DB2 to be installed on a server. When DB2 Version 8 is installed, Version 7 will be removed. All instances are migrated during the DB2 for Windows installation.

For a partitioned database environment, install DB2 Version 8 on each physical server node.

Procedures to migrate DB2 servers on UNIX and Linux systems

Migrating instances

• Log in as a user with root authority.
• Ensure that /tmp has up to 70% free space. The instance migration trace file is written to /tmp.
• Migrate instances using the db2imigr command.
• DB2DIR/instance/db2imigr [-u fencedID] Instance_name
• The db2imigr command runs the db2ckmig again and make sure the databases within the instance are ready for migration. If db2ckmig reports problem, migration will not continue.
• If you are migrating from a non-partitioned version of DB2 to a partitioned version, you must update the instance to a partitioned format using the db2iupdt command.

Migrating the database

• Migrate the database using the migrate database command.

  Listing 4. Syntax diagram of the MIGRATE DATABASE command

  >>-MIGRATE--+-DATABASE-+--database-alias------------------------> '-DB-------' >--+-------------------------------------+----------------------> '-USER--username--+-----------------+-' '-USING--password-'

  
  
• If you are migrating a partitioned database system that has multiple machines requires that database migration be performed on all participating machines.
• With an offline database backup, it is also possible to migrate the database via restore. The restore will automatically go through the database migration process.
• Note that only version database recovery is possible to migrate the database, roll forward recovery is not allowed. Any database log files generated after the backup but before the migration cannot be used to restore, roll forward, and migrate the database.
• Update the statistics using the runstats command.
• Rebind all packages stored in the database with the db2rbind command.
• During the migration, EXECUTE privileges are granted to PUBLIC for all existing functions, methods, and external stored procedures. Revoke the privileges by entering the db2undgp -r command.
• Verify a successful migration by comparing the current database manager and database configuration parameters, table spaces and packages details with those captured before the migration.

Migrating the DB2 Administration Server

• In DB2 Version 8, the new tools catalog database is used to store task information created by the Task Center and Control Center. These tasks are run by the DB2 administration server's scheduler.
• If you have created a DB2 tools catalog on the DB2 Version 8 system and want to use the existing pre-Version 8 scripts and schedule, you must migrate the DAS to Version 8.
• To migrate a pre-Version 8 DAS to the DB2 tools catalog: dasmigr [old-das-name] [new-das-name]

Procedures to migrate DB2 servers on Windows systems

Instances are automatically migrated during DB2 installation on Windows. No manual steps are required.

• Migrate the database using the migrate database command.

  Listing 5. Syntax diagram of the MIGRATE DATABASE command

  >>-MIGRATE--+-DATABASE-+--database-alias------------------------> '-DB-------' >--+-------------------------------------+----------------------> '-USER--username--+-----------------+-' '-USING--password-'

  
  
• If you are migrating a partitioned database system that has multiple machines requires that database migration be performed on all participating machines.
• With an offline database backup, it is also possible to migrate the database via restore. The restore will automatically go through the database migration process.
• Note that only version database recovery is possible to migrate the database, roll forward recovery is not allowed. Database transactions executed after the backup but before the migration are not recoverable.
• Update the statistics using the runstats command.
• Rebind all packages stored in the database with the db2rbind command.
• During the migration, EXECUTE privileges are granted to PUBLIC for all existing functions, methods, and external stored procedures. Revoke the privileges by entering the db2undgp -r command.

• In DB2 Version 8, the new tools catalog database is used to store task information created by the Task Center and Control Center. These tasks are run by the DB2 administration server's scheduler.
• If you have created a DB2 tools catalog on the DB2 Version 8 system and want to use the existing pre-Version 8 scripts and schedule, you must migrate the DAS to Version 8.
• DAS migration is done automatically if the DB2 tools catalog is created during DB2 Version 8 installation. However, migration must be done manually if the tools catalog is created after the installation.
• To migrate a pre-Version 8 DAS to the DB2 tools catalog: dasmigr [old_das_name] [new_das_name]

Verify a successful migration

Review the DB2DIAG.LOG and Administration notify log ([instance-name].nfy) stored in INSTHOME/sqllib/db2 and Event Viewer on Windows and INSTHOME/sqllib/db2dump on UNIX and Linux platforms for any errors.

Compare the current database manager and database configuration parameters, table spaces and packages details with those captured before the migration.

You should also run some diagnostics against the database such as db2dart and runstats.

To inspect the entire database, issue:

To get a listing of other available options, simply enter db2dart in the command prompt.

Upgrade the DB2 clients

Simply install DB2 Version 8 client on top of the older version of DB2 client install. The previous client info (such as database and node directories) will be automatically migrated.

How to back out of a migration

There is no utility to reverse a database migration. You have to uninstall DB2 V8 code, reinstall previous DB2 version, recreate the instances and restore databases from the offline backups.

Back to top

Migration considerations

No support for cross platform database migration

To migrate a database to DB2 V8 on a different platform, you need to create a new database and manually copy data over to the database.

You can use the db2look utility with -e option to obtain the DDL of the database including the table spaces, tables, buffer pools, views, triggers, and etc. Then, use db2move to export and import/load data.

Migrate DB2 V7 32-bit instance to DB2 V8 64-bit instance

As the cost of memory becomes more affordable, it is common to have tens of gigabytes of memory on a single server. To take advantage of the memory, one of the options is to use 64-bit DB2. There are several migration paths to move from a DB2 V7 32-bit instance to DB2 V8 64-bit instance.

The first option is to migrate the instance from DB2 V7 toV8 as described above. Then, update the instance to 64-bit with the command db2iupdt -w 64 [instance-name].

An alternative is to use the backup and restore utilities to migrate the databases. DB2's backup and restore utilities allows you to move 32-bit DB2 Version 7 databases of AIX, HP-UX, Solaris Operating Environment, and Linux to any of AIX, HP-UX, Solaris Operating Environment, and Linux 64-bit Version 8 database.

Once the server is migrated to 64-bit Version 8, you may have questions about its compatibility with V7 and v8 clients. The following matrix describes the supported configurations for communicating between DB2 clients and servers.

Supported DB2 client and server configurations

• (1) The DB2 V7 server must be configured as a DRDA AS (Application Server). Restrictions on the Version 8 client accessing the Version 7 server are provided in the section above.
• (2) Supported only for DB2 V8 64-bit server on Windows (no support for other platforms).
• (3) Only TCP/IP protocol is supported (no support for SNA)
• (4) Supported only for DB2 V8 64-bit servers on platforms other than Windows (no support for servers on Windows).
• (5) Supported only for SQL requests (no support for utility or API requests).
• (6) No support for use of DB2 utilities using AT NODE facility (primarily used for administering of a multi-partition database).

Migration problem with Java stored procedures and user-defined functions

One known problem with the migration is that Java stored procedures and user-defined functions (UDFs) are not migrated. The compiled files of these routines are stored in the subdirectories under sqllib/function/jar. However, the migration process fails to copy the files into the new V8 subdirectories. This is a known problem and will be fixed in a future FixPak. Current workaround is to manually move the subdirectories and jar files from the old to the new sqllib directory.

Back to top

Where to get help

Should you run into any problems when migrating DB2 to Version 8, there are a number of ways to get help.

• Online DB2 Technical Support where lots of downloadable manuals, technotes, product information can be found.
• IBM DeveloperWorks for DB2
• IBM Passport Advantage Software Maintenance program. Variety of technical support offerings are available includes Web based self serve access to the product knowledge base, ability to ask questions and report problems to IBM support analysts, and access to consulting services.

Back to top

Summary

This article gives you a high-level overview of the new features and enhancements in DB2 Version 8. It then takes you through the prerequisites of the migration, and the steps to perform the migration. The migration is straightforward and should be very smooth if you follow the steps as discussed in the article. If you encounter any problems, different channels are available to help you in diagnosing and resolving the issue.

The information contained in this publication does not include any product warranties, and any statements provided in this document should not be interpreted as such.

About the author

		Clara Liu works as a DB2 UDB consultant at the IBM Toronto Laboratory. As a member of the Data Management Channel Development Team, she works closely with IBM business partners. Clara specializes in database application development and integration of new technologies with DB2. She teaches DB2 UDB certification courses to IBM business partners and at conferences. She co-authored the book DB2 SQL Procedural Language for Linux, UNIX, and Windows. You can reach Clara at claraliu@ca.ibm.com.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top