DB2 Migration to Version 8

Wednesday Jul 28th 2004 by Marin Komadina

Marin Komadina presents the first in a series demonstrating the migration procedure to DB2 v.8, on a SUN Solaris operating system with a single node DB2 V7 database.

A long line of applying database FixPaks has ended with number 11, the last FixPak V7, and a brand new line has begun with DB2 V8. Currently, a total of six FixPaks have been released for DB2 V8. Consequently, support for DB2 V7 has come to an end. This event triggered many DBAs to begin the process of database migration to DB2 V8.

IBM promotional brochures stressed V8 superiority, with its brand new self-managing and resource tuning (SMART) database technology. Much was promised, including easy configuration, tuning and management with database-enhanced automation. However, before we can check it out, we have to upgrade our existing V7 databases.

This article is the first in a series demonstrating the migration procedure on the SUN Solaris operating system and a single node DB2 V7 database.

In the second article, I will provide practical solutions for important migration issues.

This article covers:

  • The Reasons for Migration
  • Preparation for Migration
  • Performing DB2 Configuration Backup and Data Backup
  • Migration from DB2 V7 to DB2 V8
  • Conclusion

The Reasons for Migration

There are several reasons why we should migrate to DB2 version 8. These are:

a.)   IBM's latest dessupport announcement

IBM withdraws support effective September 30, 2004, for the following products: 
DB2(R) Content Management Product                      number
DB2 Universal Database(R) (UDB) V7.2 products
DB2 UDB Personal Edition                               5648-D45
DB2 UDB Workgroup Edition                              5648-D46
DB2 UDB Workgroup Unlimited Edition                    5648-D46
DB2 UDB Enterprise Edition                             5648-D48
DB2 UDB Enterprise -- Extended Edition                 5648-D50
Listing 1: IBM withdraw support announcement

b.)   The concern that arises from previously announced APARs (Authorized Program Analysis Report, a named issue with an IBM program, opened after the customer or IBM support personnel discover a problem with the database software code) or HIPER APARs ( HIgh Impact PERvasive APARs, critical DB2 bugs of which all customers should be aware.) such as:

c.) Some of the brand new database enhancements:

  • backup compression technique
  • restoring backup to system with different code page
  • table null and default values compression
  • online flush of database package cache
  • asymmetric index splitting with Index Type 2
  • uncommitted data evaluation via lock deferral
  • range-clustered table support (RCT)
  • online import mode
  • index renaming
  • point-in-time recovery supports a local time
  • online database check utility (INSPECT)
  • identity and sequences support in partitioned database environment

d.)   The existence of DB2 V8 Clients

IBM has provided client backward-compatibility with numerous restrictions. An official documentation describes this situation in the following way: "Due to significant communication protocol changes, you should be aware of some restrictions and limitations when accessing DB2 V7 servers from DB2 V8 clients."

These restrictions will not prevent the version 8 client from accessing the version 8 DB2 database, but will prevent usage of LOB, UDT and DATALINK data types, usage of authentification type SERVER_ENCRYPT, disable password changing, disable ATTACH command, two-phase commit, the SQL statements greater than 32 KB in size, .

Preparing for the Migration

My demo DB2 V7 database instance, used in this article, was running on the SUN Solaris 8 operating system, under instance owner db2inst1, hosting only one DB2 database "ARTIST" and having only one database partition.

Before migration, some minimum technical prerequisites must be satisfied.

a.)   System tablespace should have enough free space for double extension. This is very important, because running out of free space could interrupt the migration procedure and leave the database in an unusable state.

b.)   System temporary tablespace should have enough space for future expansion. A temporary tablespace was used during migration as intermediate storage for catalog tables, and a sort space for the index transformation.

c.)   The number of the log files and the log space should cover full change on the largest database object. According IBM, migration is fully recoverable, causing full transaction logging through the log buffer mechanism and the database log files. That is the reason why the database log configuration parameters should be extended. IBM recommends doubling the size for regular production parameters. After migration, database parameters have to be reset to those values used before migration.

d.)   Additional disk space requirements:

  • approx. 635 MB for DB2 V8 binary installation under /opt/IBM/db2/V8 directory
  • approx. 2GB for a temporary logging area under under /tmp/install directory. A DB2 installation is delivered in compressed format on the product CD-ROM. To install DB2, you need to copy the installation image to the temporary directory, uncompress it and perform the installation.
  • approx. 130MB for holding temporary trace files under /tmp directory

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)

Related Articles:
The DB2 Configuration Files Revealed, June 29, 2004

The second part of the DB2 configuration data backup, included separate SYSCATSPACE tablespace backup (database catalog):

Online backup SYSCATSPACE V7:
$ db2 backup database artist tablespace syscatspace online
Backup successful. The timestamp for this backup image is: 20040110122840

-rw-r-----   1 db2inst1  db2dba   109084672 Jul 12 12:30 
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.

$ db2 backup database ARTIST to /backup/artist/node0"
Backup successful. The timestamp for this backup image is : 20040110103337
ARTIST001: db2 backup database ... completed ok
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:

$ db2level
DB21085I  Instance "artist" uses DB2 code release "SQL07029" with level 
identifier "030A0105" and informational tokens "DB2 v7.1.0.93", "s031208" and 
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.

$ db2level
DB21085I  Instance "db2udb1" uses "32" bits and DB2 code release "SQL08010" 
with level identifier "01010106".
Informational tokens are "DB2 v8.1.0.0", "s021023", "", and FixPak "0".
Product is installed at "/opt/IBM/db2/V8.1".

DB2 V7 and V8 packages installed on the system: 

$ pkginfo | grep db2

application db2adt71       Application Development Tools (ADT) (PTF 1721100-000)
application db2adt81       Base Application Development Tools (PTF 1810600-002)
application db2ca81        Configuration Assistant (PTF 1810600-004)
application db2cc81        Control Center (PTF 1810600-123)
application db2cdb71       Control Database (PTF 1721100-002)
application db2chen81      Control Center Help (HTML) - en_US.iso88591 (PTF 1810600-128)
application db2cj71        Java Common files (PTF 1721100-003)
application db2cj81        Java Common files (PTF 1810600-007)
application db2cliv71      Client Application Enabler (PTF 1721100-005)
application db2cliv81      Base Client Support (PTF 1810600-008)
application db2conn71      Connect (PTF 1721100-007)
application db2conn81      Connect Support (PTF 1810600-009)
application db2crte71      Communication Support - TCP/IP (PTF 1721100-011)
application db2crte81      Communication Support - TCP/IP (PTF 1810600-011)
application db2cucs81      Code Page Conversion Tables - Unicode Support
application db2das71       Administration Server (PTF 1721100-014)
application db2das81       Administration Server (PTF 1810600-013)
application db2dc81        Development Center (PTF 1810600-017)
application db2dj71        Distributed Join for DB2 Data Source (PTF 1721100-017)
application db2dj81        DB2 Data Source Support (PTF 1810600-018)
application db2djx81       Relational wrappers common (PTF 1810600-024)
application db2engn71      Engine (PTF 1721100-015)
application db2engn81      Base DB2 UDB Support (PTF 1810600-014)
application db2icuc81      ICU Collation (PTF 1810600-038)
application db2icut81      ICU Utilities (PTF 1810600-039)
application db2inst81      DB2 Instance Setup Wizard (PTF 1810600-041)
application db2jdbc71      Java Support (PTF 1721100-039)
application db2jdbc81      Java Support (PTF 1810600-042)
application db2jhen81      Java Help (HTML) - en_US.iso88591 (PTF 1810600-045)
application db2msen81      Product Messages - en_US.iso88591 (PTF 1810600-071)
application db2pext71      Parallel Extension (PTF 1721100-057)
application db2pext81      Parallel Extension (PTF 1810600-111)
application db2rte71       Run-time Environment (PTF 1721100-016)
application db2rte81       Run-time Environment (PTF 1810600-015)
application db2smpl71      Sample Database Source
application db2smpl81      Sample Database Source (PTF 1810600-016)
application db2sp81        SQL Procedures (PTF 1810600-120)
application db2spb71       Stored Procedure Builder (PTF 1721100-060)
application db2tspf71      Transformer Stored Procedure Files (PTF 1721100-062)
application db2wbdb81      DB2 Web Tools (PTF 1810600-125)

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.

$ db2ckmig -e ARTIST > mig.log

$ cat mig.log
Version of DB2CKMIG being run: VERSION 8.
 Database: 'ARTIST'
   ** WARNING **
   Object name: 'MARIN.REORGCHK'
   This view references a system entity that has changed; this may affect migration.
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.

>> db2dart artist /db
         The requested DB2DART processing has completed successfully!
                        Complete DB2DART report found in:
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:

# pwd

# ./db2imigr db2inst1
Sun Microsystems Inc.   SunOS 5.8       Generic Patch   October 2001
You have new mail.
DBI1070I Program db2imigr completed successfully.  

$ db2start
05-25-2004 13:46:29     0   0   SQL1063N  DB2START processing was successful.
SQL8011W  One or more database partitions does not have a valid DB2 license key installed 
	for the "DB2 Enterprise Server Edition" product.
SQL1063N  DB2START processing was successful.

>> db2 migrate database artist
DB20000I  The MIGRATE DATABASE command completed successfully.

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.

$ db2licm -l
Product Name                  = "DB2 Enterprise Server Edition"
Product Password              = "DB2ESE"
Version Information           = "8.1"
Expiry Date                   = "08/23/04 (Try & Buy)"
Registered Connect User Policy = "Disabled"
Number Of Entitled Users      = "5"
Enforcement Policy            = "Soft Stop"
Number of processors          = "2"
Number of licensed processors = "1"
Annotation                    = ""
Other information             = "" 

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.

>>  db2licm -n DB2ESE 2
DBI1418I The number of licensed processors on this system has 
          been updated successfully.  

>>  db2licm -a db2ese.lic 
DBI1402I License added successfully.  
Listing 10: License registration

Before handover to production, a database inspection was executed.

DB20000I  The INSPECT command completed successfully.
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.

» See All Articles by Columnist Marin Komadina

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved