Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 28, 2004

DB2 Migration to Version 8 - Page 2

By Marin Komadina

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

DB2 Archives