DB2 Migration to Version 8 - Part 2

Wednesday Aug 25th 2004 by Marin Komadina
Share:

Part 2 of this series on the migration to DB2 v.8 examines issues that appeared during post migration testing.

As it was demonstrated in my last article, an error free DB2 V7 to V8 migration procedure is compromised of database software and catalog tables migration. The user data was not migrated, leaving the user tables and indexes intact. The series of post migration tests have been done prior to database handover to production. This article presents select issues that appeared during post migration testing.

This article covers:

  • The Migration Status
  • Database Routines
  • JVM and JDBC Driver
  • Explain Tables V8
  • Index Type 2
  • Conclusion

The Migration Status

The test-case database was a DB2 V8 database instance, running on a SUN Solaris 8 operating system, under db2inst1 instance owner. The DB2 V8 instance hosted a single node (partition) database, "ARTIST."

$ 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".
Listing 1: DB2 level information

For detailed information of pre-migration and migration steps, reference my last article, DB2 Migration to Version 8.

Following the migration procedure, additional logs and diagnostic files were created at the database log destination. A short explanation of these logs follows:

  • db2imdbd.dmp - system database directory migration dump file
  • db2imnod.dmp - node migration dump file
  • db2eventlog.000 - node diagnostic files, created as result of inspect check
  • db2inst1.nfy administration notification log
  • db2diag.log database diagnostic log
$ pwd 
/home/db2inst1/sqllib/db2dump$

$ ls -alrt
-rw-r-----   1 db2inst1  db2dba   2101976 Jun 28 15:17 db2imdbd.dmp
-rw-r-----   1 db2inst1  db2dba   2101976 Jun 28 15:17 db2imnod.dmp
-rw-r-----   1 db2inst1  db2dba   5242044 Jun 29 10:47 db2eventlog.000
-rw-rw-rw-   1 db2inst1  db2dba  13345933 Jun 29 10:47 db2diag.log
-rw-rw-rw-   1 db2inst1  db2dba  13345933 Jun 29 10:47 db2inst1.nfy
Listing 2: IBM log and diagnostic files

The listed files are part of the new IBM FFDC (First Failure Data Capture) system. Starting with DB2 V8, the database diagnostic concept changed. When errors occur, DB2 automatically starts collecting diagnostic
information, generating log files as defined by DBM DIAGPATH parameter.

Database Routines

Preceding the migration procedure, I made a backup of one stored routine, (procedure) SOFTVERSION, located under the user schema ARTIST. The stored procedure is extracted and saved using "get routine" commands.

DB2 V7 
######
$ cd /home/db2inst1/sqllib/function/routine/sqlproc/ARTIST
$ ls -lrt
drwxrwxr-x   5 db2inst1  db2dba       512 Jun 14 15:52 ARTIST
$ cd ARTIST
$ ls -lrt
-rw-r-----   1 db2inst1  db2dba     56125 May 28 10:22 P5227850.sqc
-rw-r-----   1 db2inst1  db2dba        96 May 28 10:22 P5227850.scm
-rw-r-----   1 db2inst1  db2dba      1534 May 28 10:22 P5227850.log
-rw-r-----   1 db2inst1  db2dba     75237 May 28 10:22 P5227850.c
-rwxr-xr-x   1 db2inst1  db2dba     15944 May 28 10:22 P5227850

db2 => select substr(IMPLEMENTATION,1,40) 
   AS PKGNAME,substr(PROCSCHEMA,1,20) 
   AS SCHEMA,substr(PROCNAME,1,30)
   as PROCNAME from syscat.procedures
PKGNAME                                  SCHEMA               PROCNAME
---------------------------------------- -------------------- ---------------------------- 
P5227850!pgsjmp                           ARTIST               SOFTVERSION

  1 record(s) selected.

db2 => select PROCEDURE_ID,PROCNAME from syscat.procedures

PROCEDURE_ID PROCNAME
------------ -----------------------------------------------------------------------------
1 SOFTVERSION

1 record(s) selected.

db2 => get routine into ARTIST_SOFTVERSION from procedure artist.SOFTVERSION
DB20000I  The GET ROUTINE command completed successfully.
Listing 3: Extracting stored procedure SOFTVERSION before migration

The stored routine, SOFTVERSION, has the database package P5227850 associated with it. A post migration check proved that the user stored routine did not migrate correctly. The situation with SOFTVERSION routine after migration:

DB2 V8 
######
$ cd /home/db2inst1/sqllib/function/routine/sqlproc
$ ls
--> procedure did not migrated, no compiled files of routines, empty directory

db2 => select substr(IMPLEMENTATION,1,40) 
   AS PKGNAME,substr(PROCSCHEMA,1,20) 
   AS SCHEMA,substr(PROCNAME,1,30)
   as PROCNAME from syscat.procedures
PKGNAME                                  SCHEMA               PROCNAME
---------------------------------------- -------------------- ---------------------------- 
P5227850!pgsjmp                          ARTIST               SOFTVERSION

  1 record(s) selected.

-> database definition exist

db2 => select PROCEDURE_ID,PROCNAME from syscat.procedures

PROCEDURE_ID PROCNAME
------------ -----------------------------------------------------------------------------
1 SOFTVERSION

1 record(s) selected.
Listing 4: Checking for stored procedure SOFTVERSION after migration

The migration procedure migrated the package and routine definition; however, the compiled files were not copied into new V8 subdirectories. IBM has published document explaining this problem (HIPER APAR IY43787):

"On migration of instances from DB2 UDB V7.2 to DB2 UDB V8.1, Java stored procedures and user defined functions (UDFs) are not migrated as expected.The MIGRATE DATABASE command does not move subdirectories under sqllib/function/jar due to missing logic in the copy_UDFs() migration script. This is a known limitation in DB2 Version 7.2 and will be resolved in a future FixPak for DB2 Version 8.

Workaround:

Move the subdirectories and jar files manually from the old sqllib directory (renamed sqllib_v6 or sqllib_v7 by migration) or applay 8.1 Fixpak 3 before migrating instance or databases."

Workaround instructions demanded a file copy of all V7 compiled files from the old V7 to the new V8 code database directory (/home/db2inst1/sqllib/function/routine/sqlproc/ARTIST). Finally, all database packages needed to be manually rebind.

$ cp /home/db2inst1/sqllibV7/function/routine/sqlproc/ARTIST/*.*  
   /home/db2inst1/sqllib/function/routine/sqlproc/ARTIST

$ db2rbind artist -l logfile.out all
 Rebind done successfully for database 'ARTIST'.

$ db2 "select PKGNAME, EXPLICIT_BIND_TIME, LAST_BIND_TIME from SYSCAT.PACKAGES"
PKGNAME  EXPLICIT_BIND_TIME         LAST_BIND_TIME
-------- -------------------------- --------------------------
P5227850 2002-08-05-10.52.28.023400 2003-01-08-18.21.27.822195
Listing 5: Manually rebinding all database packages

JVM and JDBC Driver

JDBC driver functionality depends on locally installed JVM software. The installed Java software on the test SUN Solaris machine was version 1.2.2:

# >>  pkginfo -l SUNWj2rt
   PKGINST:  SUNWj2rt
      NAME:  JDK 1.2 run time environment
  CATEGORY:  system
      ARCH:  sparc
   VERSION:  1.2.2,REV=2001.09.10.12.37
   BASEDIR:  /usr
    VENDOR:  Sun Microsystems, Inc.
      DESC:  Java virtual machine and 
	       core class libraries
    PSTAMP:  sola010910123743
  INSTDATE:  Jul 23 2002 09:30
   HOTLINE:  Please contact your local 
      service provider
    STATUS:  completely installed
     FILES:      192 installed pathnames
                   4 shared pathnames
                  16 directories
                  27 executables
               54464 blocks used (approx)

# java -version
java version "1.2.2"
Solaris VM (build Solaris_JDK_1.2.2_10, 
   native threads, sunwjit)

drwxr-xr-x   7 root     bin          512 
  Aug 12  2002 j2se -> version 1.3.1  
lrwxrwxrwx   1 root     other          9 
  Aug 12  2002 java -> ./java1.2  
  -> version 1.2.2 
drwxrwxr-x   6 root     bin          512 
  Aug 12  2002 java1.1  
drwxr-xr-x   7 root     bin          512 
  Aug 12  2002 java1.2  
Listing 6: Installed Java software information

The DB2 instance owner had java version 1.2.2 configured for the usage. Other installed java versions are 1.1 and 1.3.1. IBM java comp ability matrix gives us following details:

Version 7

Solaris

Java Version

2.6, 7, 8

1.1.8

2.6, 7, 8

1.2.2

2.6, 7, 8

1.3

Version 8

7, 8, 9

1.3

7, 8, 9

1.4

Table 1: IBM Java Compatibility Matrix

The DB2 V8 database requests a Java version of 1.3 or 1.4. The recommendation from IBM says:

"With the advent of DB2 8.1 came the new Universal JDBC driver, which can be used either in a Type 2 architecture or a Type 4 architecture. In a Type 2 mode, the Universal JDBC driver provides local application performance gains (because it avoids using TCP/IP protocol to communicate to the DB2 server). In Type 2 mode, there is also full support for distributed transactions. Therefore, if the application and the DB2 server are on the same machine, we recommend that you use the Universal JDBC driver in Type 2 mode. The driver is located in the file db2jcc.jar."

Standard JDBC driver  type 2 and type 3
>> ls -lrt /opt/IBM/db2/V8.1/java/db2java.zip
-r--r--r--   1 bin      bin      1522386 Feb 17 17:42 /opt/IBM/db2/V8.1/java/db2java.zip

Universal JDBC driver type 4 and type 2 
>> ls -lrt /opt/IBM/db2/V8.1/java/db2jcc.jar
-r--r--r--   1 bin      bin      1017444 Feb 17 17:42 /opt/IBM/db2/V8.1/java/db2jcc.jar
Listing 7: Installed V8 Java drivers

I changed the default Java installation on the machine to version 1.3.1. The DB2 instance owner adopted the profile, including a pointer to the latest JDBC driver.

# pkginfo -l SUNWj3rt
   PKGINST:  SUNWj3rt
      NAME:  JDK 1.3 run time environment
  CATEGORY:  system
      ARCH:  sparc
   VERSION:  1.3.1,REV=2001.08.09.00.00
   BASEDIR:  /usr
    VENDOR:  Sun Microsystems, Inc.
      DESC:  Java virtual machine and core class libraries
    PSTAMP:  re450m08140310
  INSTDATE:  Aug 12 2002 11:55
   HOTLINE:  Please contact your local service provider
    STATUS:  completely installed
     FILES:      212 installed pathnames
                   5 shared pathnames
                  52 directories
                  51 executables
               78337 blocks used (approx)

>> java -version
java version "1.3.1_01"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1_01)
Java HotSpot(TM) Client VM (build 1.3.1_01, mixed mode)

>> set | grep CALSSPATH 
CLASSPATH=/home/db2inst1/sqllib/java/db2java.zip:/home/db2inst1/sqllib/java/
   db2jcc.jar:/home/db2inst1/sqllib/function:/home/db2inst1/sqllib/java/
   db2jcc_license_cisuz.jar:/home/db2inst1/sqllib/java/db2jcc_license_cu.jar:.
Listing 8: Actual Java software information

Explain Tables V8

After migration, during a post-migration functionality test, a new error message appeared:

>> db2advis -d artist -i test_2.sql
execution started at timestamp 2004-06-23-11.23.03.324770
  found [1] SQL statements from the input file

SQL0220N  The Explain table "DB2INST1.EXPLAIN_INSTANCE", column "EXPLAIN_OPTION" 
does not have the proper definition or is missing.  SQLSTATE=55002

DB2 Workload Performance Advisor tool is finished.
Listing 9: DB2 advisor post migration test

All necessary explain tables were there, belonging to the DB2INST1 user. Searching for the solution, I found that the problem lies in the explain table format. Explain plan tables V7 are incompatible with explain plan tables V8. IBM documentation emphasizes a new format for the explain tables, but this was not mentioned in the migration documentation. The explain table structure and the data conversion to V8 format was made using the db2exmig utility.

>> db2exmig -d artist -e db2inst1 
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1999, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Migration Tool
 
Connecting to the Database.
Connect to Database Successful.
Binding package - Bind was Successful.
Examining the columns for table DB2UDB1.EXPLAIN_INSTANCE
Examining the columns for table DB2UDB1.EXPLAIN_STATEMENT
Examining the columns for table DB2UDB1.EXPLAIN_ARGUMENT
Examining the columns for table DB2UDB1.EXPLAIN_OBJECT
Examining the columns for table DB2UDB1.EXPLAIN_OPERATOR
Examining the columns for table DB2UDB1.EXPLAIN_PREDICATE
Examining the columns for table DB2UDB1.EXPLAIN_STREAM
Examining the columns for table DB2UDB1.ADVISE_INDEX
Examining the columns for table DB2UDB1.ADVISE_WORKLOAD
Found all tables OK. Start migration.
Old tables renamed...begin creating new explain tables.
Begin insert into new explain tables.
Copying to Explain table EXPLAIN_INSTANCE from EMINS_001087983210
Copying to Explain table EXPLAIN_STATEMENT from EMSTA_001087983210
Copying to Explain table EXPLAIN_ARGUMENT from EMARG_001087983210
Copying to Explain table EXPLAIN_OBJECT from EMOBJ_001087983210
Copying to Explain table EXPLAIN_OPERATOR from EMOPR_001087983210
Copying to Explain table EXPLAIN_PREDICATE from EMPRD_001087983210
Copying to Explain table EXPLAIN_STREAM from EMSTR_001087983210
Copying to Explain table ADVISE_INDEX from ADIND_001087983210
Copying to Explain table ADVISE_WORKLOAD from ADWKL_001087983210
Exec Imm was Successful.
Check all tables where copied properly.
All tables confirmed to have been copied properly.
Dropping old Explain table EMINS_001087983210 (EXPLAIN_INSTANCE)
Dropping old Explain table EMSTA_001087983210 (EXPLAIN_STATEMENT)
Dropping old Explain table EMARG_001087983210 (EXPLAIN_ARGUMENT)
Dropping old Explain table EMOBJ_001087983210 (EXPLAIN_OBJECT)
Dropping old Explain table EMOPR_001087983210 (EXPLAIN_OPERATOR)
Dropping old Explain table EMPRD_001087983210 (EXPLAIN_PREDICATE)
Dropping old Explain table EMSTR_001087983210 (EXPLAIN_STREAM)
Dropping old Explain table ADIND_001087983210 (ADVISE_INDEX)
Dropping old Explain table ADWKL_001087983210 (ADVISE_WORKLOAD)
Migration of the explain tables to Version 8.1 format has been sucessful.
 
******** WARNING **********
Because the 'NOT LOGGED INITITALLY' option was used in migrating the explain tables
it is recommended that a backup of the explain tables be taken as soon as possible,
if your backup procedures include the backuping up of the explain tables.
Listing 10: Explain table's conversion to V8 format

Having the explain tables converted to the V8 format, db2advis worked correctly.



Type 2 Indexs



Version 8 introduced Type 2 indexes as a new standard index type,
replacing the V7 Type 1 indexes. Both indexes currently coexist, however,
Type 1 indexes will be de-supported in the future. The type 2 index is replacement for the old Type 1 index, which has suffered from:



  • concurrency issues during inserts and next key locks
  • performance issues due to physically removing a key from a leaf page during update or delete


Unlike Type 1 indexes, Type 2 indexes are more Oracle like. Some benefits of Type 2 indexes:



  • no more physical action after delete or update action until final commit
  • next-key locking is reduced to a minimum, dramatically improving concurrency
  • online index reorganization supported
  • can be created on columns whose length is greater than 255 bytes


Example of an existing Type 1 index, table DEMO01, schema ARTIST:

$ pwd
/home/db2inst1/sqllib/db2dump

>> db2 inspect check table name DEMO1 schema ARTIST results keep index_check1.log
DB20000I  The INSPECT command completed successfully.

$ ls -lrt
total 1856
-rw-rw-rw-   1 db2inst1  db2dba    157260 Aug 17 14:01 db2diag.log
-rw-rw-rw-   1 db2inst1  db2dba      5329 Aug 17 14:02 db2inst1.nfy
-rw-r-----   1 db2inst1  db2dba    159822 Aug 17 16:47 index_check1.log.000

$ cat index_check1.log.000

  Tablespace phase start. Tablespace ID: 7
   Tablespace name: ARTISTTS
   Tablespace Type: SMS - System Managed Space; Extent size: 24; Page size: 8192; Number of containers: 1
   Container name: /home/db2inst1/node0/artist/ARTISTTS
     Table phase start (ID Signed: 2, Unsigned: 2; Tablespace ID: 7) :
 
      Data phase start. Object: 2  Tablespace: 7
      The index type is 1 for this table.
       DAT Object Summary: Total Pages 532 - Used Pages 127 - Free Space 74 %      
      Data phase end.
 
      Index phase start. Object: 2  Tablespace: 7
       INX Object Summary: Total Pages 33 - Used Pages 33
      Index phase end.
    Table phase end.
Listing 11: Index Type 1 example

DB2 inspect check proved that our table, DEMO1, has one Type 1 index on it. This is normal behavior after the database has been migrated to V8. The index was manually converted to a Type 2 index, using REORG INDEXES command.

db2 => reorg indexes all for table ARTIST.DEMO1 convert
DB20000I  The REORG command completed successfully.

>> db2 inspect check database results keep index_check2.log 
DB20000I  The INSPECT command completed successfully.

>> ls -lrt
total 1856
-rw-rw-rw-   1 db2inst1  db2dba    157260 Aug 17 14:01 db2diag.log
-rw-rw-rw-   1 db2inst1  db2dba      5329 Aug 17 14:02 db2inst1.nfy
-rw-r-----   1 db2inst1  db2dba    159822 Aug 17 16:47 index_check2.log.000

$ cat index_check2.log.000

  Tablespace phase start. Tablespace ID: 7
   Tablespace name: ARTISTTS
   Tablespace Type: SMS - System Managed Space; Extent size: 24; Page size: 8192; Number of containers: 1
   Container name: /home/db2inst1/node0/artist/ARTISTTS  
    Table phase start (ID Signed: 2, Unsigned: 2; Tablespace ID: 7) :
 
      Data phase start. Object: 2  Tablespace: 7
      The index type is 2 for this table.
       DAT Object Summary: Total Pages 532 - Used Pages 127 - Free Space 74 % 
      Data phase end.
 
      Index phase start. Object: 2  Tablespace: 7
       INX Object Summary: Total Pages 36 - Used Pages 36
      Index phase end.
    Table phase end.
  Tablespace phase end.
Listing 19: IBM withdraw support announcement

The table index was successfully converted to a Type 2 index. The new Type 2 index uses more database space (33 pages before and 36 pages after). Therefore, attention should be given to tablespace space prior to converting all database indexes to Type 2 indexes. Some DBAs recommend recreation and relocation of Type 2 indexes in a single, large, index tablespace. All existing, non-converted Type 1 indexes will continue to function properly and will be converted during index recreation. It is quite possible that future DB2 versions will omit support for Type 1 indexes.

Conclusion

This article was a mixture of advice and techniques to boost performance and prevent system failure after migration. The next logical step should be performing a stress test with a critical application. One thing is for sure--DB2 V8 brings more DB2 code stability and faster response times.

» See All Articles by Columnist Marin Komadina

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