ShowTable of Contents
IBM Lotus Quickr is team collaboration software that helps you access the people, information and project materials you need to get your job done.
You can control your team's content and always access the latest version, can easily collaborate using team spaces that work inside or outside your firewall, can access and manage content from the desktop applications you use every day, and collaborate across time zones.
Quickr is built to leverage a broad set of skills and technologies by offering two deployment options: services for Lotus Domino® and services for WebSphere Portal. IBM Lotus Quickr with services for WebSphere Portal is also known as Quickr-J2EE (Quickr-J).
DB2 Universal Database™ (UDB) is the first multimedia, Web-ready relational database management system that is strong enough to meet the demands of large corporations and flexible enough to serve small- and medium-sized businesses.
DB2 product family software together with Internet technology makes information easily accessible, available, and secure across diverse platforms. There are more than 60 million DB2 users from over 300,000 companies worldwide relying on IBM data management solutions.
We use DB2 v9.1 FP4 for Quickr-J 8.1.1.x, and v9.5 FP4 for Quickr-J 8.5.
Sequence of migration steps
As best practice, the order of the migration steps are:
- Plan. The planning steps can be performed in any order. For more details, refer to the Quickr Information Center topic, “Planning for custom and extended resources.”
- Prepare the target system. Perform the preparation of the target system steps in order. Install Lotus Quickr 8.5, customize all themes and skins for your environment, and ensure it is working.
- Prepare the source system. Perform the preparation of the source system steps in order. Upgrading to Lotus Quickr 22.214.171.124 can occur while the target system is being prepared.
- Export the source data. Perform the export of the source data steps in order. Preparing the source system must be completed before beginning the export of the source data.
- Import the source data. Perform the import of the source data steps in order. Preparing the target system and exporting the source data must both be completed before beginning the import of the source data.
- Complete the migration. Perform the completion of the migration steps in order. Importing the source data must be completed before starting the completion of the migration.
- Perform post-migration tasks. Perform the post-migration steps in order.
Tuning DB2 for migration
The size of the Quickr-J database can be much larger than the memory size. In that case, if DB2 is not tuned before the migration process is started, it might cause unpredictable issues---or even a failure of the whole migration.
The host environment we use is Microsoft® Windows® 2008 with 4GB of memory, but the Quickr-J database size is 36GB, and we always encountered out-of-memory issues if we did not tune DB2. So we recommend tuning to optimize the database's status.
The DB2 tuning is performed during the migration Step 5, Import the source data, in which the user must restore the Quickr-J 8.1.1.x database backup to be used by the migrated Quickr-J 8.5 environment.
Here are the specifics of what should be tuned.
1. Update DB2 instance profile registries:
2. Update the database manager configuration:
First, connect to the database, for example:
db2 connect to jcrdb user db2admin using 12345678
Use this command to get the current value of the parameter:
db2 get dbm config
This parameter is an instance-wide soft limit on the total amount of memory that can be consumed by private sorts at any given time. When the total private-sort memory consumption for an instance reaches this limit, the memory allocated for additional incoming private-sort requests is considerably reduced.
When the instance-level sheapthres
is set to zero, then the tracking of sort memory consumption is done at the database level only, and memory allocation for sorts is constrained by the value of the database-level sheapthres_shr
db2 "update dbm cfg using sheapthres 0"
This parameter lets you specify whether you want to monitor an instance, its associated databases, and database objects, according to various health indicators. We needn't monitor database health while migrating.
db2 "update dbm cfg using health_mon off"
This is used to restore the database. It doesn't have much relevance to the migration process, but it is useful for the future use. The default size is 1024.
db2 "update dbm cfg using restbufsz 4096"
This parameter determines the maximum size of the heap that is used by the Java™ interpreter started to service Java DB2® stored procedures and UDFs.
db2 "update dbm cfg using java_heap_sz 12288"
3. Update configuration parameters that affect query optimization
With DB2 version 9.5, applheapsz
refers to the total amount of application memory that can be consumed by the entire application. Large schemas require sufficient space in the application heap.
db2 "update db cfg for jcrdb using applheapsz 6144"
This parameter specifies the size of the statement heap, which is used as a work space for the SQL or XQuery compiler during compilation of an SQL or XQuery statement.
If the stmtheap
parameter is not set large enough, you might receive a warning indicating that there is not enough memory available to process the statement.
db2 "UPDATE DB CFG FOR jcrdb USING stmtheap 32768"
Average Number of Active Applications. The optimizer uses the avg_appls
parameter to help estimate how much of the buffer pool might be available at runtime for the access plan chosen.
db2 "update db cfg for jcrdb using avg_appls 5"
Sort Heap Size. When choosing an access plan, the optimizer estimates the cost of the sort operations, including evaluating whether a sort can be piped, by:
- Estimating the amount of data to be sorted
- Looking at the sortheap parameter to determine if there is enough space for the sort to be piped.
db2 "update db cfg for jcrdb using sortheap AUTOMATIC"
Maximum Percent of Lock List Before Escalation (maxlocks), and Maximum Storage for Lock List (locklist). The optimizer considers the values of the locklist
parameters to determine whether row-level locks might be escalated to a table-level lock.
If the optimizer estimates that lock escalation will occur for a table access, then it chooses a table-level lock for the access plan, instead of incurring the overhead of lock escalation during the query execution.
db2 "update db cfg for jcrdb using maxlocks 60"
4. Other db configuration parameters
db2 "update db cfg for jcrdb using locklist 1000"
This parameter allows DBAs and ISVs to control the maximum amount of application memory that is allocated by DB2 database agents to service application requests.
db2 "update db cfg for jcrdb using appl_memory 4096"
This parameter defines the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written to them before they become full and a new log file is required.
The value of logfilsiz
should be increased if the database has a large number of update, delete, or insert transactions running against it, which will cause the log file to become full very quickly.
db2 "update db cfg for jcrdb using logfilsiz 8192"
This parameter allows you to specify the number of primary log files to be pre-allocated. The primary log files establish a fixed amount of storage allocated to the recovery log files.
If you find that secondary log files are frequently being allocated, you might be able to improve system performance by increasing the log file size (logfilsiz
) or by increasing the number of primary log files.
db2 "update db cfg for jcrdb using logprimary 36"
This parameter specifies the number of secondary log files that are created and used for recovery log files (only as needed).
When the primary log files become full, the secondary log files (of size logfilsiz
) are allocated one at a time, as needed, up to a maximum number as controlled by this parameter.
db2 "update db cfg for jcrdb using logsecond 32"
This parameter specifies the maximum number of concurrent applications that can be connected (both local and remote) to a database. Since each application that attaches to a database causes some private memory to be allocated, allowing a larger number of concurrent applications will potentially use more memory.
to automatic allows any number of connected applications. The database manager will dynamically allocate the resources it needs to support new applications.
db2 "update db cfg for jcrdb using maxappls AUTOMATIC"
This parameter determines the maximum memory used by the database heap.
With version 9.5, this database configuration parameter has a default value of AUTOMATIC, meaning that the database heap can increase, as needed, until either the database_memory limit is reached or the instance_memory limit is reached.
db2 "update db cfg for jcrdb using dbheap automatic"
This parameter represents a soft limit on the total amount of database shared memory that can be used by sort-memory consumers at any one time.
is set to AUTOMATIC, it is enabled for self tuning. This allows the memory tuner to dynamically size the memory area controlled by this parameter as the workload requirements change.
Because the memory tuner trades memory resources between different memory consumers, there must be at least two memory consumers enabled for self-tuning in order for self-tuning to be active. Memory consumers include SHEAPTHRES_SHR, PCKCACHESZ, BUFFER POOL (each buffer pool counts as one), LOCKLIST, and DATABASE_MEMORY.
Automatic tuning of sheapthres_shr
is allowed only when the database manager configuration parameter sheapthres
is set to zero. Enabling self-tuning of the sheapthres_shr
parameter automatically enables self-tuning of the sortheap
db2 "update db cfg for jcrdb using sheapthres_shr AUTOMATIC"
This parameter specifies the number of seconds that an application will wait to obtain a lock, helping avoid global deadlocks for applications.
db2 "update db cfg for jcrdb using locktimeout 300"
This parameter allows you to specify the number of asynchronous page cleaners for a database.
If this parameter is set to AUTOMATIC, the number of page cleaners started will be based on the number of CPUs configured on the current machine, as well as the number of local logical database partitions in a partitioned database environment.
There will always be at least one page cleaner started when this parameter is set to AUTOMATIC.
db2 "update db cfg for jcrdb using num_iocleaners AUTOMATIC"
This parameter specifies the number of I/O servers for a database. No more than this number of I/Os can be in progress for pre-fetching and utilities for a database at any time.
db2 "update db cfg for jcrdb using num_ioservers AUTOMATIC"
Turn off AUTO_RUNSTATS, AUTO_TBL_MAINT, AUTO_MAINT
db2 "update db cfg for jcrdb using AUTO_RUNSTATS off AUTO_TBL_MAINT off AUTO_MAINT off"
5. Update bufferpool settings
Three bufferpools are created with the default values shown below, when a user creates a Quickr-J database:
ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K
ICMLSVOLATILEBP4 SIZE 34000 PAGESIZE 4 K
ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K
We need to update the values to automatic.
db2 alter bufferpool ICMLSFREQBP4 size AUTOMATIC
6. Tune DB2 after updating the configuration parameters
db2 alter bufferpool ICMLSMAINBP32 size AUTOMATIC
db2 alter bufferpool ICMLSVOLATILEBP4 size AUTOMATIC
This command calculates statistics on the database to determine if tables or indexes, or both, need to be reorganized or cleaned up.
db2 connect to jcrdb
db2 reorgchk update statistics on table all
Note that this process took a long time with a 36GB database.
You can perform the reorganize operation at either the index level or table level. Here we do it on the table level to reorganize all indexes on the current database table.
db2 connect to jcrdb
db2 -x -r "reorg.db2" "select rtrim(concat('reorg table ',concat(rtrim (tabSchema),concat('.',rtrim(tabname))))) from syscat.tables where type='T'"
db2 -v -f "reorg.db2"
This command collects current statistics on tables and indexes, especially if significant update activity has occurred or new indexes have been created since the last time runstats
This provides the optimizer with the most accurate information with which to determine the best access plan. Since we have run reorg
on all tables, we need to use this command to update the table status in the database system table.
db2 connect to jcrdb
db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and detailed indexes all allow write access'))))) from syscat.tables where type='T'"
db2 -v -f "runstats.db2"
Now, you could make a new backup database with turned data, in case your import task fails.
db2 BACKUP DATABASE jcrdb TO "C:\DBBackup\q830_jcrdb_with_turning" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
You should now be familiar with a number of tuning tips and major DB2 configuration parameters that can affect migration from Quickr-J 8.1.1.x to 8.5 with large database sizes. Hopefully the reference values of the database configuration parameters can help you achieve a successful migration.
IBM DB2 Database for Linux, UNIX, and Windows Information Center:
IBM Lotus Quickr Information Center:
developerWorks Lotus Quickr product page:
About the authors
Zhou Jian Bing is a Software Engineer based at IBM's China Software Development Lab in Beijing, working on the Lotus Connections System Verification Test (SVT) team since April 2009. You can reach Jian Bing at firstname.lastname@example.org
Du Li Dong is a Software Engineer based at IBM's China Software Development Lab in Beijing, where he currently works on the Lotus Connections SVT team. You can reach Li Dong at email@example.com
Xie Ling is a Staff Software Engineer based at IBM's China Software Development Lab in Shanghai. He has worked on the Lotus Connections Functional Verification Test (FVT) team since August 2007, before which he worked as a developer for IBM Workplace for Business Control and Reporting. He earned a Masters degree in Computer Application Technology from Zhejiang University. You can reach Ling at firstname.lastname@example.org