Upgrade to DB2 V 9.7
You can directly upgrade existing DB2 Version 9.5, DB2 Version 9.1, or DB2 UDB Version 8 instances and databases to DB2 Version 9.7.
NOTE: The files "SQLSPCS.1, SQLSPCS.2 , SQLSGF.1, and SQLSGF.2" are not tablespace containers they are files which contain meta data about the table spaces. These files are located under the database path directory, use the database snapshot to find these files.
Table space information files
System catalog and system temporary table spaces
System catalog table space (SYSCATSPACE)
Increasing the total size to twice the total of used space is recommended. In other words the amount of free space should be at least the same as the current amount of used space.
Temporary table space (TEMPSPACE1 is the default name)
Increasing the total size to twice the total size of the system catalog table space is recommended.
Software and Hardware:
AIX Version 7.14
On Windows operating systems, you have an option to automatically upgrade an existing pre-Version 9.7 DB2 copy. If you choose to upgrade your existing DB2 copy during installation, you only need to upgrade your databases after installation.
If your DB2 servers are running on a release prior to DB2 UDB Version 8, migrate them first to DB2 UDB Version 8, and then upgrade to DB2 Version 9.7.
What happens during Instance and database upgradation:
Instance scope:
- Upgrades an existing instance to a new instance under a DB2 Version 9.7 copy.
- Upgrades instance profile registry variables. The global profile registry variables set by the user are not upgraded.
- Upgrades the database manager configuration (dbm cfg) file.
- Sets the jdk_path dbm cfg parameter appropriately.
- Upgrades the db2audit.cfg audit configuration file when the audit facility is enabled.
- Uses the SSLconfig.ini SSL configuration file to set the new database manager configuration parameters to the corresponding SSL parameter value in this file and upgrades the instance profile registry setting DB2COMM=SSL.
- In a Microsoft Cluster Server (MSCS) environment, defines a new resource type, updates all DB2 MSCS resources to use the new resource type, removes the old resource type, and brings all resources online.
But I don't know what is the resource type in Microsoft Cluster Server.
Database directory scope:
When you access the database directory the first time, it is implicitly upgraded if necessary. The database directory is accessed when you issue commands such as LIST DATABASE DIRECTORY or UPGRADE DATABASE command.
Database upgrade:
When the database upgrade is called explicitly using the UPGRADE DATABASE command, or implicitly using the RESTORE DATABASE command from a pre-Version 9.7 database backup, the following database entities might be converted during database upgrade:
- Database configuration file
- Log file header
- Table root page for all tables
- Index root page for all tables
- Catalog tables
- Buffer pool files
- History file
For recoverable databases, the UPGRADE DATABASE command renames all log files in the active log path with the extension .MIG. After you upgrade your databases successfully, you can delete all the S*.MIG files.
The UPGRADE DATABASE command upgrades the files SQLSPCS.1, SQLSPCS.2 , SQLSGF.1, and SQLSGF.2 to support new functionality on automatic storage table spaces such as removing storage paths from a database and rebalancing automatic storage table spaces after you add or drop storage paths from a database.
NOTE: The files "SQLSPCS.1, SQLSPCS.2 , SQLSGF.1, and SQLSGF.2" are not tablespace containers they are files which contain meta data about the table spaces. These files are located under the database path directory, use the database snapshot to find these files.
The UPGRADE DATABASE command automatically collects statistics for all system catalog tables during database upgrade.
Restrictions:
Disk space requirements for DB2 Upgrades:
The files SQLSPCS.1 and SQLSPCS.2 contain table space information. During upgrade from DB2 UDB Version 8 to DB2 Version 9.7, these files grow to four times their previous size but the total data size on disk does not exceed the new size of theSQLSPCS.1 and SQLSPCS.2 files. For example, if these two files have a total size of 512 KB before upgrade, you will need at least 2 MB of free disk space.
Ensure that you have sufficient free space on the system catalog and the system temporary table spaces for the databases that you are upgrading. System catalog table space is required for both old and new database catalogs during upgrade. The amount of free space required varies, depending on the complexity of the database, as well as on the number and size of database objects.
For the system catalog table space, free pages should be equal to or greater than used pages. Total pages for the system temporary table space should be twice the amount of total pages for the system catalog table space.
Software and Hardware:
AIX Version 7.14
- 64-bit AIX kernel is required
- AIX 7.1.0 General Availability (GA)
- Minimum C++ runtime level requires the xlC.rte 11.1.0.0 and xlC.aix61.rte 11.1.0.0 (or later) filesets. These filesets are included in the April 2010 IBM C++ Runtime Environment Components for AIXV11.1 package.
- The minimum requirement for running DB2 Version 9.7 on AIX Version 7.1 is DB2 Version 9.7 Fix Pack 3 (V9.7.0.3).3
- Additional considerations for POWER7 systems:
- If you have IBM Tivoli® System Automation for Multiplatforms (SA MP) 2.2..x or SA MP 3.1.x Reliable Scalable Cluster Technology (RSCT) 2.5.4.0 installed, you require the SA MP3.1.0.6 special package with RSCT 2.5.4.2. Version 9.7 Fix Pack 2 and later fix packs include an updated version of SA MP which includes an RSCT level that supports POWER7 systems.
- DB2 products running on POWER7 systems support Active Memory™ Expansion.
- IBM Tivoli Monitoring for Databases: DB2 Agent (ITMA) is not supported on POWER7 systems.
- For other considerations or to manually enable POWER7 support, see www.ibm.com/support/docview.wss?&uid=swg21420730.
- 4To use the DB2 High Availability Feature, you need to apply Version 9.7 Fix Pack 3 or a later fix pack. These fix packs contain an updated version ofIBM Tivoli System Automation for Multiplatforms (SA MP).
Only 64 bit kernel is supported for AIX.
Disk requirements for UNIX and Linux:
/opt - Default installation path (/opt/IBM/db2/V9.7) needs minimum of 2 - 10 GB
/tmp - 2 GB of free space in the /tmp directory is recommended.
Note: On Linux and UNIX operating systems, you must install your DB2 product in an empty directory. If the directory that you have specified as the install path contains subdirectories or files, your DB2 installation might fail.
Memory requirements:
1 GB is recommended.
Paging space requirements:
DB2 requires paging, also called swap to be enabled.
This configuration is required to support various functions in DB2 which monitor or depend on knowledge of swap/paging space utilization.
A reasonable minimum swap/paging space configuration for most systems is 25-50% of RAM.
These higher requirements are due to virtual memory pre-allocated per database / instance, and retained virtual memory in the case of STMM tuning multiple databases.
Additional swap/paging space might be wanted to provision for unanticipated memory overcommitment on a system.
JAVA software requirements:
SDK 6
Differences between ROOT and NON ROOT installations:
Unlike root users, non-root users cannot choose where DB2 database products are installed. Non-root installations are always placed in the $HOME/sqllib directory, where $HOME represents the non-root user's home directory. The layout of the subdirectories within the sqllib directory of a non-root is similar to that of a root installation.
Pre Upgrade tasks:
1. Ensure that you have at least one free page of index space per object index to eliminate the overhead of a potential index rebuild.
If an index root page does not have enough free space during upgrade, then the index will need to grow by one page. If a free page cannot be found in the index object, then a page will be requested from the tablespace.
If the tablespace is full, then the entire index object will be marked invalid and will be rebuilt when the underlying table is accessed for the first time after upgrade.
2. If you use distributed transactions involving DB2 databases, ensure that the databases to be upgraded do not contain any indoubt transactions by using the LIST INDOUBT TRANSACTIONS command to get a list of indoubt transactions and to interactively resolve any indoubt transactions.
3. Convert type-1 indexes to type-2 indexes because type-1 indexes are discontinued in DB2 Version 9.7. Converting them before upgrade eliminates the overhead of index rebuild when you access tables using these indexes for the first time after upgrading to DB2 Version 9.7.
On UNIX or Linux operating systems, change to the $DB2DIR/bin directory where DB2DIR is the location that you specified during the DB2 Version 9.7 installation.
Run the below command:
db2IdentifyType1 -d database-name -o convert-t1-indexes-dbname.db2
If you have type-1 indexes, you will receive the following message: Type-1 indexes were found in the inspected tables.
The convert-t1-indexes-dbname.db2 command file contains REORG INDEXES ALL commands with the CONVERT parameter for each identified type-1 index.
Add a REORG INDEXES ALL command to the convert-t1-indexes-dbname.db2 command file for each root table with type-1 indexes, that you identified in the previous step, as shown in the following example:
If you have type-1 indexes, convert them to type-2 indexes by running the convert-t1-indexes-dbname.db2 command file:
4. Verify that databases are ready for DB2 upgrade to identify any problems before the actual upgrade. You must resolve them before you proceed with the upgrade.
Before you upgrade your databases, it is important to use the db2ckupgrade command to verify that your databases are ready for upgrade.
The db2ckupgrade command verifies that a list of conditions are true in order to succeed at the database upgrade.
Also, this command writes to the log file, specified with the -l parameter, a warning message for a list of conditions that impact database upgrades.
Restrictions
In a partitioned database environment, to verify that your databases are ready for upgrade, you must run the db2ckupgrade command on each database partition.
If you do not run the db2ckupgrade command on each database partition, the db2iupgrade command could succeed even when one or more database partitions are not ready for upgrade. However, the database upgrade will fail.
Backup database:
Disconnect all applications and users from the database.
Backing up server configuration:
Collect information from your DB2 servers by running the db2support command for all your databases that you are going to upgrade in all your instances.
1. Ensure that you have at least one free page of index space per object index to eliminate the overhead of a potential index rebuild.
If an index root page does not have enough free space during upgrade, then the index will need to grow by one page. If a free page cannot be found in the index object, then a page will be requested from the tablespace.
If the tablespace is full, then the entire index object will be marked invalid and will be rebuilt when the underlying table is accessed for the first time after upgrade.
2. If you use distributed transactions involving DB2 databases, ensure that the databases to be upgraded do not contain any indoubt transactions by using the LIST INDOUBT TRANSACTIONS command to get a list of indoubt transactions and to interactively resolve any indoubt transactions.
3. Convert type-1 indexes to type-2 indexes because type-1 indexes are discontinued in DB2 Version 9.7. Converting them before upgrade eliminates the overhead of index rebuild when you access tables using these indexes for the first time after upgrading to DB2 Version 9.7.
On UNIX or Linux operating systems, change to the $DB2DIR/bin directory where DB2DIR is the location that you specified during the DB2 Version 9.7 installation.
Run the below command:
db2IdentifyType1 -d database-name -o convert-t1-indexes-dbname.db2
If you have type-1 indexes, you will receive the following message: Type-1 indexes were found in the inspected tables.
The convert-t1-indexes-dbname.db2 command file contains REORG INDEXES ALL commands with the CONVERT parameter for each identified type-1 index.
Add a REORG INDEXES ALL command to the convert-t1-indexes-dbname.db2 command file for each root table with type-1 indexes, that you identified in the previous step, as shown in the following example:
REORG INDEXES ALL FOR TABLE table-name
ALLOW WRITE ACCESS CONVERTIf you have type-1 indexes, convert them to type-2 indexes by running the convert-t1-indexes-dbname.db2 command file:
db2 -tvf convert-t1-indexes-dbname.db24. Verify that databases are ready for DB2 upgrade to identify any problems before the actual upgrade. You must resolve them before you proceed with the upgrade.
Before you upgrade your databases, it is important to use the db2ckupgrade command to verify that your databases are ready for upgrade.
The db2ckupgrade command verifies that a list of conditions are true in order to succeed at the database upgrade.
Also, this command writes to the log file, specified with the -l parameter, a warning message for a list of conditions that impact database upgrades.
Restrictions
In a partitioned database environment, to verify that your databases are ready for upgrade, you must run the db2ckupgrade command on each database partition.
If you do not run the db2ckupgrade command on each database partition, the db2iupgrade command could succeed even when one or more database partitions are not ready for upgrade. However, the database upgrade will fail.
1. Login as the DB2 instance user:
2. On UNIX or Linux operating systems, change to the DIRIMG/db2/OS/utilities/db2ckupgrade/bin directory where DIRIMG is the location where you uncompressed the DB2 Version 9.7 installation image or the directory where you mounted the DB2 product DVD, and OS is the operating system name of the DB2 server.
3. Run
db2ckupgrade sample -l db2ckupgrade.log -u adminuser -p password
db2ckupgrade was successful. Database(s) can be upgraded.
If you performed the Converting type-1 indexes to type-2 indexes pre-upgrade task, you can use the -not1 parameter to skip the check for type-1 indexes.
If the db2chkupgrade completes successfully then the databases can be migrated.
5. If you created user-defined data types using a name that is a system built-in data type name, drop these user-defined data types and re-create them using a different name that is not restricted.
6. If you created database objects using restricted schema names, drop all the database objects that use reserved schema names and re-create them using a schema name that is not restricted.
7.Ensure that the log file for db2ckupgrade command contains the following text: Version of DB2CKUPGRADE being run: VERSION 9.7. This text confirms that you are running the correct level of the db2ckupgrade command.
8.Check and fix any invalid flavor fields on SQLSPCS files using the fixtbspflvr tool. Details about this tool can be obtained from http://www.ibm.com/support.
Backup database:
Disconnect all applications and users from the database.
db2 list applications
db2 force application all
Backup your database using the BACKUP DATABASE command.
db2 BACKUP DATABASE sample
In partitioned database environments, back up all database partitions.
Optional: Test the integrity of a backup image to ensure that the image can be restored using the db2ckbkp Check Backup command.
The following is an example on UNIX operating systems:
cd backup-dir
db2ckbkp SAMPLE.0.arada.NODE0000.CATN0000.20051014114322.001
[1] Buffers processed: #######
Image Verification Complete - successful.Backing up server configuration:
Collect information from your DB2 servers by running the db2support command for all your databases that you are going to upgrade in all your instances.
db2support output-directory -d database-name -cl 0
The -cl 0 parameter collects the database system catalog, database and database manager configuration parameters settings, DB2 registry variables settings.
The information collected is stored in the db2support.zip compressed zip file under the output directory.
A summary report in HTML format is included. In the db2supp_opt.zip file that is also included, you should check the optimizer.log file to verify that the collection of information was performed successfully.
Back up the information about all the packages for your applications associated with each database.
db2 LIST PACKAGES FOR SCHEMA schema-name
SHOW DETAIL > /upgrade/sample_pckg.txt
or
db2 LIST PACKAGES FOR ALL
SHOW DETAIL > /upgrade/sample_pckg.txt
If you enabled the audit facility, back up the audit configuration of your instances by issuing the following command:
db2audit describe > audit_instance-name.cfg
To backup all external routines:
cp -R $INSTHOME/sqllib/function $INSTHOME/routine_backup
Use the GET DATABASE MANAGER CONFIGURATION command to back up your settings for database manager configuration parameters:
db2 GET DBM CFG > dbm_instname.cfg
The db2support command generates a file with the output of the db2look command for the specified database.
db2look -d sample -e -o sample_tbs.db2 -l -x
Use the db2setcommand to back up your DB2 profile registry variables settings and redirect the command output to a file to save these settings: db2set -all > reg_instname.txt
If you set DB2 environment variables, use the appropriate system command to list environment variables and their values. For example, on AIX® you can issue the following command:
set |grep DB2 > env_instname.txt
Increasing the table space and log file sizes before upgrade:
Additional considerations are required in partitioned database environments to increase table space sizes because table spaces span across database partitions.
Use the below query to find the table space size:
db2 "SELECT SUBSTR(TBSP_NAME,1,15) NAME, TBSP_TYPE TYPE,
TBSP_AUTO_RESIZE_ENABLED AUTO_RESIZE, TBSP_NUM_CONTAINERS CONTAINERS,
TBSP_TOTAL_PAGES TOTAL_PGS, TBSP_USED_PAGES USED_PGS, TBSP_FREE_PAGES FREE_PGS,
TBSP_MAX_SIZE MAX_SZ, TBSP_PAGE_SIZE PG_SZ
FROM SYSIBMADM.TBSP_UTILIZATION
WHERE TBSP_CONTENT_TYPE IN ('ANY','SYSTEMP')"
If you have an SMS table space, ensure that you have at least the same amount of used pages available as free disk space; in this example, about 60 MB.
If you have a DMS table space and the number of used pages is greater than the number of free pages, use the following formula to calculate the number of pages to increase per container:
number_of_pages = ( used_pages - free_pages ) /
number_of_containers_in_SYSCATSPACE
Then use the following command to increase the size of all containers in the system catalog table space:
db2 "ALTER TABLESPACE SYSCATSPACE EXTEND (ALL number_of_pages)"
If you have a DMS table space with AUTORESIZE enabled and MAXSIZE is set to NONE, ensure that you have at least twice the amount of used pages available in free disk space.
If MAXSIZE is set to an integer value that is less than twice the amount of used pages, then you need to increase MAXSIZE using the ALTER TABLESPACE statement:
db2 "ALTER TABLESPACE SYSCATSPACE MAXSIZE (2*used_pages_in_SYSCATSPACE*page_size/1024) K"
Increase the size of the temporary table spaces.
If you have an SMS table space you only need to ensure that you have at least twice the amount of total pages for the system catalog table space in free disk space;
If you have a DMS table space, use the following formula to calculate the number of pages to increase per container:
number_of_pages = ( number_of_total_pages_in_SYSCATSPACE ) /
number_of_containers_in_TEMPSPACE1
Use the following command to increase the size of all containers in the temporary table space:
db2 "ALTER TABLESPACE TEMPSPACE1 EXTEND (ALL number_of_pages)"
Log space size requirements:
db2 UPDATE DB CFG FOR sample using LOGSECOND
(current_value of LOGPRIMARY + current_value of LOGSECOND) * 2
Gathering pre upgrade diagnostic data:
The gathering of this diagnostic information is done through the db2fodc -preupgade and db2support -preupgradecommands.
Taking DB2 server offline:
Stop the DB2 license service:
db2licd -end
Force all the applications:
db2 force application all
db2 terminate
db2stop
Upgrading DB2 Instance and Databases:
Before you begin:
Ensure that you have root access.
If a IBM® PowerHA® SystemMirror for AIX® cluster is running, you can not perform a IBM Tivoli® System Automation for Multiplatforms (SA MP) installation, upgrade or update because SA MPbundles Reliable Scalable Cluster Technology (RSCT) filesets that are dependent on PowerHA SystemMirror.
To skip the SA MP installation use the db2setup command or the installFixPackcommand.
Upgrade instances:
db2 GET DBM CFG | grep 'Node type'
Node type = Partitioned database server with local and remote clients
The node type should be like "Partitioned database server with local and remote clients"
db2stop force (Disconnects all users and stops the instance)
db2 terminate (Terminates back-end process)
Log on to the DB2 database server with root user authority on Linux and UNIX operating systems or Local Administrator authority on Windows operating systems and run:
$DB2DIR/instance/db2iupgrade [ -u fencedID ] InstName
The db2iupgrade command calls the db2ckupgrade command with the -not1 parameter to verify that the local databases are ready for grade.
The update.log is specified as the log file for db2ckupgrade, and the default log file created for db2iupgrade is /tmp/db2ckupgrade.log.processID.
On the HADR STANDBY server:
Ensure that the standby node is updated with the current instance information by performing the following actions:
Remove the old instance information from the standby database by issuing the following command on the standby node:
new db2 upgrade path/bin/db2greg -delinstrec instancename=old_instance_name.
Add the new instance information to the standby database by issuing the following command on the standby node: new db2 upgrade path/instance/db2iset -a new_instance_name
Restart your instance by running the db2start command:
db2start
Verify that your instance is running on to DB2 Version 9.7 by running the db2level command
db2level
Upgrade databases:
Log on to the DB2 server as the instance owner or a user with SYSADM authority.
Optional: Rename or delete the db2diag log files so that new files are created. Also, remove or move to another directory any existing dump files, trap files, and alert log files in the directory indicated by the diagpath parameter.
Upgrade the database using the UPGRADE DATABASE command:
db2 UPGRADE DATABASE database-alias
One of the most common causes of upgrade failure is that the log file space is not large enough, in which case the following error is returned:
SQL1704N Database upgrade failed. Reason code "3".
If the UPGRADE DATABASE command returns the SQL1243W warning message, you need to drop or rename the SYSTOOLS.DB2LOOK_INFO table.
Verify your database upgrade is successful.Connect to the upgraded databases and issue a small query:
Post Upgrade tasks:
1. Adjust the log size to the previous size.
2. During database upgrade, all packages for user applications and routines are marked as invalid. You must rebind invalidated packages to take advantage of changes in the DB2® server and new statistics.
This procedure only applies to Embedded SQL database applications programmed in C, C++, COBOL, FORTRAN, and REXX.
db2rbind database-name -l logfile all
3.Migrating explain tables.
db2exmig -d dbname -e explain_schema
Adopting new functionality:
Enable Read only On Standby (ROS).
Reversing DB2 migration:
There is no utility to fall back to a previous release of DB2 database after upgrading your DB2 server.
This procedure applies only to DB2 server upgrade. It does not include DB2 clients.
In partitioned database environments you must perform this procedure on all participating database partition servers. If you have several database partitions on a partition server, execute tasks at the database level, such as backup and restore, on each database partition.
Procedure:
Log on to the DB2 server as a user with SYSADM authority.
Drop all databases in DB2 Version 9.7 by running the DROP DATABASE command.
Log on to the DB2 server as root on Linux and UNIX operating systems or a user with Local Administrator authority on Windows operating systems.
Drop your DB2 Version 9.7 instances by running the db2idrop command. This command does not remove the database files; you need to drop your databases before dropping your instances.
If you upgraded your pre-Version 9.7 instances to DB2 Version 9.7, re-create your instances in the pre-Version 9.7 by running the db2icrt. Then restore the database manager configuration parameter values for each instance using the UPDATE DATABASE MANAGER CONFIGURATION command.
For each pre-Version 9.7 instance, log on to the DB2 server as the instance owner and restore your upgraded databases from a pre-Version 9.7 offline full backup by running the RESTORE DATABASE command. You cannot upgrade your databases from DB2 Version 9.7 to pre-Version 9.7 release.
If you have recoverable databases and you want to rollforward through the log files you had before the upgrade, rename all the S*.MIG files in the active log path using the .LOG extension and issue the ROLLFORWARD DATABASE command.
Upgrading DB2 Clients:
Backing up client configuration:
Backup client database manager configuration:
db2 GET DBM CFG > D:\upgrade\dbm_client.cfg
Back up the information of cataloged databases by running the db2cfexp command to create a configuration profile:
db2cfexp cfg_profile BACKUP
Ensure that you have root user authority.
Log on to the system with root user authority.
Upgrade your existing client instances by running the db2iupgrade command:
$DB2DIR/instance/db2iupgrade InstName
Post upgrade tasks:
1. Recatalog nodes and databases.
2. Manage changes in DB2 server behavior by modifying your settings where required. There are new registry variables, new configuration parameters, and new default values for registry variables and configuration parameters introduced in DB2 Version 9.7 that can impact the behavior of your application.
3.Verify that upgrading your clients was successful.
By connecting to the database.
No comments:
Post a Comment