Upgrade your complex DB2 UDB Environment: Tips and Tricks

1. Upgrade your complex DB2 UDB Environment: Tips and Tricks

2. Ben S. Prusinski, Database Administrator • 12 Years IT Experience • Network and Database Administration with DB2 UDB, Oracle, SQL Server and SAP R/3. • Financial Services and Government Project Experience

3. OverviewThis presentation will be in workshop hands-on format. It will cover the best method for upgrading a complex environment from DB2 UDB 7.1 to DB2 UDB 8.1. It will also cover “gotchas” for performing database migration from old hardware and operating systems to new hardware and operating system versions.

4. Upgrade your Complex DB2 UDB Database Environment • I will cover migration from DB2 UDB 7.2 with Fixpak6 on AIX 4.3.3 to DB2 UDB 8.1 on Fixpak3 with AIX 5.2L.

5. Overview • 1. Planning • 2. Hardware/Operating system migration • 3. Database Migration • 4. Problem Resolution • 5. Conclusion

6. Many companies today have a critical need for the updated versions of DB2 UDB 8.1 ESE and AIX 5.2L that will allow better performance and scalability for mission critical business applications. In addition with the new release of DB2 UDB for AIX, database self- managing SMART initiatives and self- healing features will provide a more robust and stable enterprise database environment for LPL’s mission-critical applications.

7. Planning your DB2 UDB Upgrade • Total: 4-6 weeks to install new hardware, configure disk/filesystems, install new version of DB2 database server software, upgrade database instances, migrate databases and upgrade DB2 UDB clients

8. Preparation Steps for the Migration/UpgradeHardware and Operating System1. Best to upgrade to new version of AIX first rather than attempt to migrate everything at once.2. Check with IBM Support for version compatibility for AIX and DB2 UDB: 32 bit versus 64 bit.3. Fixpak issues

9. AIX upgrade with DB2 upgrade:Check versions with upgrade for AIX and DB2 with IBM DB2/AIX support to clarify issues on version compatabilityNo issues with DB2 UDB 7.2 (with Fixpack 6) and AIX 5.2L 32 bit(There are only problems with AIX 5.1L and DB2 UDB 7.2)

10. Upgrade migration scenarios:- Migrate DB2 UDB from current database server to a new server- Migrate DB2 UDB from same server to same server- version upgrade- Migrate 32 bit DB2 UDB 7.2 to 64 bit DB2 version 8.1

11. Migrate DB2 UDB from current database server to a new server • Take offline full backup of current database • Db2 backup database acctview use tsm • Perform redirected restore of database to new server - db2 restore database acctview use tsm taken at 20031219143225 into acctview replace existing redirect without rolling forward - db2 set tablespace containers for 0 … - db2 restore database acctview continue

12. Moving to new hardware1.Install DB2 UDB 8.1.2 and fixpaks on new hardware and configure disk layouts and filesystems for DB2 and AIX2.Perform a complete offline backup for DB2 databases3.FTP over the backup image to the new database servers4.Perform a redirected restore from old hardware to new hardware after install version 8.1.25.take backup of database on new servers6.test database connection and environments7.test stored procedures

13. More tips on migration with new AIX serverTEST new version of AIX 5.2L with DB2 UDB database and application.ENABLE ALL AIX UNIX Filesystems to support large file sizes larger than 2Gb to avoid problems with DB2 UDB tablespaces and containers- especially with SMS based tablespaces.

14. Next step in migration:On new database server at LPL we migrated to an IBM p690:Allocate at least 2 Gb of disk space in /usr/opt filesystem.

15. Additional Preparation- collect additional DB2 UDB database configurations: db2 get db cfg for acctview. Db2 get dbm cfg for acctview - extract ddl for db2 dbusing db2look utility- get DB2 partition layout for nodes (DB2 EEEE now ES version only)- get db2set parameters very important- get DB2 and AIX filesystem layout- get DB2 and AIX logical disk volume layout- update db2 diag level to diaglevel 4- collect disk and filesystem layout AIX for DB2 prep

16. Install DB2 UDB 8.1 and FP3 for DB2 on sandbox (unixtest) Shutdown current version of DB2. Shutdown DB2 Database Server: db2inst1> db2stop Shutdown DB2 Administrative Instance: db2inst1> db2admin stop

17.  Shutdown database instances for DB2 UDB$ db2stop08-06-2003 14:33:29 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2admin stopSQL4407W The DB2 Administration Server was stopped successfully.

18. NOTE: During the sandbox installation for new version DB2 UDB, it failed because space ran out on/home and /home/db2inst2 Also need to re-install with new name for DB2 Admin instance- call it dasuser instead of db2as!!!!!2. Free up additional space under /home/db2inst2 the new DB2 UDB 8.1 instance directoryRe-run db2setup installation program to resolve failed installation.

19. Installing DB2 UDB 8.1 for 32 bit AIX 5.2Llogin as the AIX UNIX superuser- root# mount -oro -vcdrfs /dev/cd0 /mnt; cd /mnt# cp ese.sbcs.tar.Z /usr# cd /usr# zcat ese.sbcs.tar.Z| tar -xf -# cd ese.sbcs# ./db2setup

20. Filesystem and Disk space requirements – bare minimumDB2 UDB V8.1 Database: 169Mb/usr/opt/db2_08_01/usr need 2Gb/usr/opt need 2Gb/home need 2-4Gb:Admin Client- 145MbUsed by DB2 DBA to administer DB2 remotely and locally servers

21. Additional Filesystem and Disk space requirements for the database upgradeRuntime client- 60Mbused by users to connect who do not have an Admin or Application clientApplication Clients- 180Mb(Used for developing stored procedures, DB2 applications, etc)

22. More configuration details for the migrationConfiguration Setup DB2 V8.1- db2inst2 is new DB2 V8.1 database instance- dasusr1 is new DB2 V8.1 Admin database instance (DAS)- TCP/IP= db2c_db2inst2- TCP/IP Port: 50002 under /etc/servicesWarehouse DB V8.1:- DWCTRLDBSchema: IWHUser: db2inst2Configuration Setup DB2 7.2:-db2inst1 is V7.2 DB2 UDB instance-> /home/db2inst1-db2as is V7.2 DB2 UDB Admin database instance (DAS) server-> /home/db2as

23. PART II: Upgrade DB2 7.2 Instance to DB2 UDB 32 bit 8.1.2 instance

24. Login as the DB2 7.2 database instance owner, db2inst1 Run the DB2 8.1 DB2CKMKIG utility before starting migration db2inst1> cd /home/db2inst2/sqllib/binor wherever you installed the new version of DB2 8.1 instance also located under /usr/opt/db2_08_01/instance or on the DB2 UDB 8.1.2 CD-ROM you have mounted during the v8 installation.HOWEVER do not run the db2ckmig utility nor db2imig from here it will not work!

25. Database Migration Check for DB2 UDB Upgradedb2inst1> ./home/db2inst2/sqllib/bin/db2ckmig -eSAMPLE -l /home/db2inst1/upgrade -u db2inst1 -p db2NOTE: the log file for the upgrade check must be unix filesystem writable or you will get the error message"cannot open log file"Check to make sure it ran successful and current version. 

26. Migrate the current DB2 7.2 database instance to the 8.1 version of DB2 UDB

27. $ db2 list database directory System Database Directory Number of entries in the directory = 1Database 1 entry: Database alias = SAMPLE Database name = SAMPLE Local database directory = /home/db2inst1 Database release level = 9.00 Comment = Directory entry type = Indirect Catalog node number = 0

28. Syntax: /DB2DIR/instance/db2imigr -u db2fenc1 InstNamewhere DB2DIR is either /usr/opt/db2_08_01 on AIX NOTE: db2fenc1 is only required when migrate from client instance to a server.LOGIN AS DB2INST1 (v7.2 instance owner/user)cd /usr/opt/db2_08_01/instance CHANGE PERMISSIONS to allow db2inst1 to run the commands in this directory see Unix admin:./db2imigr -u db2fenc1 db2inst1

29. $ ./db2imigr -u db2fenc1 db2inst1DBI1052E You must be root to execute this program.Explanation:This program can only be run under the root user ID. Specialprivileges are required to execute this program.User Response:Login as root and issue the command again.

30. root> cd /usr/opt/db2_08_01/instanceroot> ./db2imigr -u db2fenc1 db2inst1db2ckmig was successful. Database(s) can be migrated.DBI1070I Program db2imigr completed successfully.

31. Check to make sure the instance has been upgraded to new DB2 version:$ db2levelDB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08010"with level identifier "01010106".Informational tokens are "DB2 v8.1.1.0", "s021023", "", and FixPak "0".Product is installed at "/usr/opt/db2_08_01".

32. Check database directory for db2inst1 to ensure it has been upgraded to new version of DB2:$ db2 list db directory System Database Directory Number of entries in the directory = 1Database 1 entry: Database alias = SAMPLE Database name = SAMPLE Local database directory = /home/db2inst1 Database release level = a.00 Comment = Directory entry type = Indirect Catalog database partition number = 0

33. Start the DB2 UDB 8.1 database instance $ db2start08-07-2003 15:23:35 0 0 SQL1063N DB2START processing was successful.SQL1063N DB2START processing was successful.NOTE: you still need to run the database migration to complete the rest of the database upgrade:

34. $ db2 connect to sample user db2inst1 using db2SQL5035N The database requires migration to the current release.SQLSTATE=55001The database migration has to still be completed

35. Migrate the database to new release of DB2 8.1:Syntax:db2 migrate database {database alias} user {username} using {password}db2 migrate database SAMPLE user db2inst1 using mypasswordoptions: update statistics, rebind packages, revoke execute privileges [ db2undgp -r ] migrate db2explain tables

36. Compare pre-migration DB2 configuration parameter settings, database manager configuration settings, tablespace records and packages records to newly upgrade database instance to ensure no errors occurred in the upgrade and migration!!!

37. $ db2 migrate database SAMPLEDB20000I The MIGRATE DATABASE command completed successfully.$ db2 connect to SAMPLE Database Connection Information Database server = DB2/6000 8.1.0 SQL authorization ID = DB2INST1 Local database alias = SAMPLE

38. Next take complete offline backup of the new database after the upgrade- very important!db2 backup database SAMPLE use tsm

39. Migrate the DB2 Administrative (DAS) database instance over to DB2 8.1$ ./dasmigr db2as mydas1DBI1052E You must be root to execute this program.Explanation:This program can only be run under the root user ID. Specialprivileges are required to execute this program.User Response:Login as root and issue the command again.

40. Try out some of the new features in DB2 UDB 8.1 DB2 Health Monitor: graphical and command line

41. $ db2 get health snapshot for dbm Database Manager Health SnapshotNode type = Enterprise Server Edition withlocal and remote clientsInstance name = db2inst1Snapshot timestamp = 08-08-2003 08:24:05.824699Number of database partitions in DB2 instance = 1Start Database Manager timestamp = 08-07-2003 15:23:29.033376Instance highest severity alert state = Not yet evaluatedHealth Indicators: Not yet evaluated

42. Conclusion:Performing a complex database migration for DB2 UDB is a challenging and daunting task for any DB2 UDB database professional Make sure to plan carefully and test in a sandbox and/or development environment before upgrading your production and QA servers.

43. Final caveats:You will still need to keep the DB2 7.2 clients until all the servers have been upgraded.

44. Comments/Questions?

45. Additional Resources:Quick Beginnings AIX and DB2 UDB 8.1 Release Notes for DB2 UDB 8.1 on AIX Installing DB2 UDB 8.1 Server Installing DB2 UDB 8.1 clients Release Notes- installing DB2 UDB 8.1 Fixpack 3IBM Support:http://www.ibm.com/software/data/db2/libraryIBM Redbooks:http://www.redbooks.ibm.comUp and Running with DB2 UDB ESE: Partitioning for Performance in an e-BusinessIntelligence World, SG24-6917-00

46. Upgrade Your Complex DB2 UDB Environment Tips and TricksSession Code:Speaker: Ben S. PrusinskiEmail: ben.prusinski@lpl.com