Performing DB2 Configuration Data and User Data Backup
Instance and database configuration data backup is an optional pre-migration step. The general opinion is that offline or online data backup should be enough for the migration start. According my experience, this was not always a true. In some cases, without instance and database configuration data backup, it can be almost impossible to carry out migration troubleshooting and problem discovery.
A DB2 configuration data backup should include the following information:
- DB2 general configuration files (all profile types and node configuration file), DB2 instance settings (instance configuration, node directory file, database directory file and an instance configuration file in the binary format)
- DB2 database settings (database configuration and all binary files representing database configuration)
- DB2 tablespace information
- DB2 packages (routines)
- DB2 logical structure, statistics and optimizer info (db2look and export of catalog tables with actual optimizer statistics)
The DB2 Configuration Files Revealed, June 29, 2004
The second part of the DB2 configuration data backup, included separate SYSCATSPACE tablespace backup (database catalog):Listing 2: Performing catalog tablespace online backup
An additional DB2 data backup was performed, using a full offline database backup method, on the local file system. When needed, this would be the fastest recovery method.Listing 3: Performing offline database backup
Migration from DB2 V7 to DB2 V8
As a first step, a database software installation was needed. DB2 version 7 with FixPak 11 was installed on my test SUN Solaris machine:Listing 4: DB2 V7 level information
The DB2 V8 software installation was straightforward. A software installation can be performed in several ways:
a.) using the graphical installer in typical, custom or compact installation mode
b.) using the SUN Solaris pkgadd command and response file or db2setup command in interactive mode (db2setup -r ver8.rsp).
Regardless of the method, the result should be an error free DB2 V8 software installation, as displayed in the Listing 5.Listing 5: DB2 V8 level and software package information
On SUN Solaris, the database software V8 is installed by default at /opt/IBM/db2/V8.1 directory. This change of installation rules is more than welcome since the old V7 definition, /opt/IBMdb2/V7.1, was anything but logical.
DB2 Database Migration
At this point, practical migration can start. Nevertheless, I prefer to do one more database check--the DB2 V7 database check with a DB2 V8 pre-migration tool, db2ckmig. This tool inspects the tablespace and database structure, and finishes with a particular status report for every object inspected. If the report indicates that there is a problem, the problem needs to be corrected before migration to ensure that the migration does not fail.Listing 6: Pre-migration check using db2ckmig utility
According to the db2ckmig log, the test database ARTIST has one problematic view. This view was created in the database schema MARIN and references the system catalog table. I left the view as is, remembering to check the view status once again after migration. In general, this was only warning and not a real problem as far as performing the migration. You should never ignore pre-migration warning messages. Together with db2ckmig, I made one more check for internal, logical, database correctness using the db2dart utility.Listing 7: Pre-migration check using db2dart utility
Even after this test, in the log file ARTIST.RPT, I did not find anything suspicious. Finally, I started the instance and database migration:Listing 8: DB2 instance and database migration
Depending on the database size and machine power, "migrate database" can take a while, during which time conversion of the database configuration file, log file header, buffer pool files, catalog tables, index root page, history file and table space files to new a format take place. Along with the migration of existing database objects, some new objects are created such as system catalog tables, system views and user-defined functions. In our test, the migration finished correctly and the database was running DB2 V8. User data was not changed in any way.
The DB2 V7 license was invalidated, causing a SQL1063N warning message. Nevertheless, the database was fully usable for the trial period of 90 days, giving enough time to obtain a valid license key from IBM Support.Listing 9: License information after migration
Upon obtaining a valid V8 license (db2ese.lic) and accompanying key, the installation was straightforward procedure. Registration was two-step processes. The first step was the processor registration and the second was the V8 license registration.Listing 10: License registration
Before handover to production, a database inspection was executed.Listing 11: DB2 database inspection
The resulting inspection log file, "check_out," was placed in the location defined in the database-manager configuration variable, DIAGPATH. By default, this is in the ~/sqllib/db2dump directory. An error free database was finally ready for hand-over to production.
The migrated DB2 database is running and the users are happy. I could not finish this article without saying a word about some migration problems I found. Just to mention, the user environment variables that were not transferred, wrong explain tables, indexes using old technology, silently changed configuration parameters or the mess with user stored procedures. The actual list is much longer. In the next article, I will present important steps needed to clear migration footprints.