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
In the second article, I will provide practical solutions
for important migration issues.
This article covers:
Configuration Backup and Data Backup
DB2 V7 to DB2 V8
The Reasons for Migration
There are several
reasons why we should migrate to DB2 version 8. These are:
a.) IBM's latest dessupport
Listing 1: IBM withdraw support 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
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 to system with different code page
null and default values compression
flush of database package cache
index splitting with Index Type 2
data evaluation via lock deferral
table support (RCT)
recovery supports a local time
database check utility (INSPECT)
sequences support in partitioned database environment
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
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.
migration, some minimum technical prerequisites must be satisfied.
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.
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.
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.
- approx. 635 MB for DB2 V8 binary installation under
- 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
Performing DB2 Configuration Data and User Data Backup
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.
configuration data backup should include the following information:
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)
settings (database configuration and all binary files representing database
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
Listing 2: Performing catalog tablespace online
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
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
$ db2 backup database ARTIST to /backup/artist/node0"
Backup successful. The timestamp for this backup image is : 20040110103337
ARTIST001: db2 backup database ... completed ok
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
DB21085I Instance "artist" uses DB2 code release "SQL07029" with level
identifier "030A0105" and informational tokens "DB2 v22.214.171.124", "s031208" and
The DB2 V8 software
installation was straightforward. A software installation can be performed in
using the graphical installer in typical, custom or compact installation mode
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
DB21085I Instance "db2udb1" uses "32" bits and DB2 code release "SQL08010"
with level identifier "01010106".
Informational tokens are "DB2 v126.96.36.199", "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)
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
$ db2ckmig -e ARTIST > mig.log
$ cat mig.log
Version of DB2CKMIG being run: VERSION 8.
** WARNING **
Object name: 'MARIN.REORGCHK'
This view references a system entity that has changed; this may affect migration.
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
Listing 7: Pre-migration check using db2dart
>> db2dart artist /db
The requested DB2DART processing has completed successfully!
Complete DB2DART report found in:
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
# ./db2imigr db2inst1
Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001
You have new mail.
DBI1070I Program db2imigr completed successfully.
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.
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
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
$ 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 = ""
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
>> 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.
Before handover to production, a database
inspection was executed.
Listing 11: DB2 database inspection
$ db2 INSPECT CHECK DATABASE RESULTS KEEP check_out
DB20000I The INSPECT command completed successfully.
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,
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