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
ARTIST.3.db2inst1.NODE0000.CATN0000.20040117123031.001
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
"U495945".
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:
/export/home/db2inst1/sqllib/db2dump/DART0000/ARTIST.RPT
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
/opt/IBM/db2/V8.1/instance
# ./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.
$ db2 INSPECT CHECK DATABASE RESULTS KEEP check_out
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.
Conclusion
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
» 
IE 7
Firefox 2.0
Comment and Contribute
