 | Level: Intermediate Noureddine Brahimi (noureddinebrahimi@yahoo.ca), Staff Software Developer, IBM
05 Apr 2007 This article guides you through a Siebel 7 database environment upgrade from DB2® UDB Version 8 to DB2 9. You'll walk through the necessary steps and learn the tools and commands that you will need to accomplish the migration.
The article starts by presenting some new features in DB2 9, and shows how these features can be utilized by the Siebel application. This document details the pre-migration and migration for both UNIX® and Windows® operating systems, and concludes with the post-migration steps.
What's new in DB2 9
-
pureXM™L: In DB2 9, XML data store support is native. This enables well-formed XML documents to be stored in their hierarchical form within columns of Siebel tables. An XML type is used to store XML data rather than storing it as text. Therefore, you have efficient search and retrieval of XML data using XQuery, SQL, or a combination of both.
-
Automated administration features: The automation of administration features eases administration tasks for Siebel/DB2 DBAs. This includes Self Tuning Memory Management (STMM). STMM helps reduce or eliminate the task of configuring your DB2 server by continuously updating configuration parameters (database configuration and database manager configuration), resizing buffer pools, and dynamically determining the total amount of memory to be used by the database.
-
More database security: A new security feature called Label-Based Access Control (LBAC) allows you to control data access at the row and column level. For this, a new security administrator (SECADM) authority level is added that collects several security-related privileges. LBAC can be used to enforce Siebel access control, based on records visibility for a user that is based on organization, position, and a membership to a list or group.
-
Resiliency enhancements: Siebel/DB2 DBAs can use several enhancements immediately with DB2 9 that add resiliency. Some of these features include:
-
Ability to restart interrupted recovery operations that can save precious time and effort in database recovery situations.
-
Support for performing redirected restore operations with scripts automatically generated from existing backup images.
-
The ability to rebuild databases from table space backup images.
-
More additional useful features: There are more features of interest for Siebel applications. Some are related to performance and scalability, such as data row compression, and some are related to manageability, such as the ability to copy database schema, and ALTER TABLE statement enhancements.
This article shows you how to enable some of DB2 9 features, such as self-tuning memory management and compression, as part of the post-migration steps.
 |
Pre-migration steps
The pre-migration steps are very important, and are key to a successful migration. In this section, you will do essentially three tasks:
-
Gather the most important information about your current database and your environment prior to the migration (some useful scripts and examples are given). This way, you can easily go back if you encounter any issue down the road. This information can also be used if you want to apply some if these parameters values after the migration.
-
Check if your machine is compliant and that DB2 9 can be safely installed and used.
-
Stop DB2 server to prepare the migration.
Details of these tasks are found below:
-
Run an offline backup of your database.
Check if there is any application or user connected to the database by issuing
db2 list application
Disconnect any user or application before issuing the offline backup by running
db2 force application all
Issue a backup either to TSM, to a directory or a device
db2 backup database dbname use TSM To TSM
db2 backup database dbname to directory/device To a directory or to a device
|
-
Save your database configuration information as db cfg, dbm cfg, and data row counts.
-
Take a row count for all tables in the database(s) and save the result. The script below can help for this purpose.
-
Run db2 get db cfg for dbname for the database(s) and save the result.
-
Run db2 get dbm cfg and save the result.
-
Run db2set -all and save the result.
-
Run db2 list tablespaces show detail and db2 list packages for all show detail for the database(s) and save the results.
-
Run db2look -d $dbname -e -a -m -o $outputfilename.
-
Run db2dart dbname /DB /V Y /RPT . /RPTF E and save the results. This command will take time.
Listing 1. Example on a UNIX platform to get table row count. Script name: get_table_count.sh
if [ $# -lt 1 ]; then
echo "This script need a database name as a parameter "
echo
echo "Example $0 dbname "
exit 1
fi
db2 "connect to $1 "
if [ $? -ne 0 ]; then
echo "ERROR connecting to the database $1"
echo;echo
exit 1
fi
db2 "select tabname from syscat.tables where tabschema = 'SIEBEL' order by 1" > x
export rownums=`cat x | wc -l`
export headrows=$(($rownums-3))
export tailrows=$(($headrows-3))
cat x | head -$headrows | tail -$tailrows > y
echo; echo
while read tabname
do
db2 "select count(*) from siebel.$tabname " > x1
rownums=`cat x1 | wc -l`
if [ $rownums -gt 3 ]; then
headrows=$(($rownums-3))
tailrows=$(($headrows-3))
cat x1 | head -$headrows | tail -$tailrows > y1
nb=$(cat y1)
else
nb=0
fi
echo "--------------- Number of rows in table ... $tabname $nb"
echo
done < y
rm x y x1 y1
db2 terminate
|
If the Siebel schema name is not SIEBEL, then the first query in the script and in the loop should be changed accordingly.
Listing 2. Script to get all db information before the migration. Script name: get_db_info.sh
if [ $# -lt 1 ]; then
echo "This script need a database name as a parameter "
echo
echo "Example $0 dbname "
echo;echo
exit 1
fi
export db_name=$1
date
echo;echo "-------------- Table counts"
nohup get_table_count.sh $db_name | tee table_count.log
db2 connect to $db_name
echo;echo "-------------- Tablespace lists with detail"
db2 list tablespaces show detail | tee tbspace_detail.log
echo;echo "-------------- Package lists with detail"
db2 list packages show detail | tee package_detail.log
echo;echo "-------------- DBM CFG"
db2 get dbm cfg | tee dbmcfg.log
echo;echo "-------------- DB CFG"
db2 get db cfg for $db_name | tee dbcfg.log
echo;echo "-------------- DB2 SET"
db2set -all | tee db2set.log
echo;echo "-------------- db2look for all db objects"
db2look -d $db_name -e -a -m -o db2look.log
db2 terminate
echo;echo "-------------- db2dart on all database. This will take time"
db2 force application all
db2dart $db_name /DB /V Y /RPT . /RPTF E
date
|
-
Review your disk space requirements. Database migration requires additional disk space as follow:
-
The files SQLSPCS.1 and SQLSPCS.2 contain table space information and grow during the migration to four times their previous size. If the total size of these two files is 512KB, then you need at least 2MB of free space.
-
Ensure that there is sufficient free space on the system catalog and the system temporary tablespaces. The amount of disk space varies, However, the following are some suggested guidelines. Increase the total size of SYSCATSPACE and TEMPSPACE1 (which is the default name and can be changed) to twice the current value. For SMS tablespaces, increase the container files sizes, and for DMS, add additional containers (this can trigger data rebalancing). The additional space is needed only for the migration operation. Therefore, it can be reduced, following a successful migration. The following example, based on on AIX and Siebel 77 DB2 database, shows you how to determine the amount of space occupied by SYSCATSPACE.
$ db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 229882
Useable pages = 229882
Used pages = 229882
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
You can see (bold elements are the important for this task) that the
SYSCATSPACE tablespace is using 229882 pages x 4096 bytes per
page which is about 898 MB. Next, you need to find out where the
containers for this tablespace are located.
$ db2 list tablespace containers for 0
Tablespace Containers for Tablespace 0
Container ID = 0
Name = /V9_FS/sia77u_v9/catalog
Type = Path
You now need to find out if the free space is at least twice of the used space.
$ df -k /V9_FS
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/V9_LV 70909952 67149124 6% 704 1% /V9_FS
You can see that you have 67GB free space in this file system, which is enough
|
-
Increase logfilsiz, logprimary, and logsecond, to twice their current values. The migration makes changes to system catalog objects in a single transaction, and these changes need adequate log space to contain this transaction. If there is insufficient space, the transaction is rolled back and the migration will not complete successfully. For additional details, refer to "Increasing table space and log file sizes before migration."
-
For Linux only: Change raw devices to block devices. For more details, refer to "Changing raw devices to block devices (Linux)."
-
Stop the DB2 server by issuing the following commands:
db2licd -end
db2 force application all
check if there is any application still connecting by issuing db2 list applications
db2stop (to be issued for each instance)
db2admin stop (to be issued as the DAS user)
db2_kill
ps -eaf | grep db2 (make sure there are no DB2 processes up)
|
-
Before installing DB2 9, ensure that your machine is compliant and all the patches or fixes are already installed. To check this, refer to "System requirements for DB2 9 for Linux UNIX and Windows."
|
Migration steps
This section gives more details about the migration task itself. The migration contains two major steps, the instance migration and the database migration. This section goes through these two parts of the migration, first on a Windows platform and then on a UNIX platform.
-
Migration on Windows: On Windows, with DB2 9, there are two approaches to migrate the database.
-
First approach: By choosing this step, the instance is migrated automatically while installing DB2 9. (This is the recommended method)
When installing DB2 9, the previous version is detected and the installation wizard displays a button to migrate the instance (See the bottom of Figure 1).
Figure 1. Installation wizard
Selecting the  button installs DB2 9 and migrates the instance automatically. You need only to migrate the database later, by invoking the CLP and issuing these commands:
db2start
db2 migrate db dbname
|
-
Second approach: In this case, DB2 is installed in a different directory than the previous version's directory. To do the instance migration and the database migration, follow these steps:
-
Install DB2 in a different folder by selecting the  button, in the Installation wizard.
-
After the installation is complete, navigate to the new DB2_9Directory/bin.
-
Open the DB2 CLP window (db2cmd.exe) and issue the following command to migrate the instance: db2imigr v8InstanceName /u:userid,password.
-
Close the CLP and open it again, for the operation to take effect.
-
Issue the db2ilist command. It shows both instances.
-
Issue the db2 list directory command. This brings the database that you migrate in the next step.
-
Issue the db2start command.
-
Issue the command db2 migrate db dbname
, to migrate the database.
-
Migration on UNIX: On a UNIX platform, you need to install DB2 9, migrate the instance, and then migrate the database by performing the following steps:
-
Install DB2 9 without uninstalling DB2 UDB Version 8, and without dropping or renaming the SQLLIB directory.
-
Add the license by running the DB2_9_Directory/adm/db2licm -a filename.lic command as root.
For example, if you start installing DB2 9 ESE from the directory /db2_9_installimage, and you choose the default location, then you should run
/opt/IBM/db2/V9.1/adm/db2licm -a /db2_9_installimage/ese/disk1/db2/license/db2ese.lic.
-
Check your root environment and make sure there is no DB2 path or instance's SQLLIB on the LIBPATH or PATH environment variables.
Also check the .profile and .kshrc files. This generates errors with db2imigr, and prevents the instance migration.
-
Migrate the instance. Go under DB2_Installation_Path/instance, and run the db2imigr command as root.
For example, db2imigr -u db2fenc1 db2inst1
.
On a successful migration, the old SQLLIB is renamed to SQLLIB_V81.
-
Log on as the instance owner of the migrated instance, and issue the db2start command.
-
Run the database migration as instance owner by running the db2 migrate db dbname
command.
At this point, you already did the migration task. The next section shows you how to adjust some parameters and enable some DB2 features to start using Siebel 7 and DB2 9.
|
Post-migration steps
This section takes you through some steps to prepare to use DB2 9 and Siebel 7. You also enable some of the new DB2 9 features.
-
Adjust the log space size (logfilsiz, logprimary, and logsecond database configuration parameters). If you changed these parameters, as recommended in the pre-migration steps, then you can use the values saved in step 3 of the pre-migration steps to adjust these parameters values back.
-
Install Siebel stored procedures. Copy the appropriate Siebel stored procedure code (siebproc on UNIX and siebproc.dll on Windows) to the FUNCTION directory under SQLLIB. Change the permission of this file. Permissions on UNIX, for siebproc, should be read-write for the owner, read for the group, and neither read nor write for all others.
-
Grant execute privilege on Siebel stored procedures and user-defined functions to PUBLIC.
db2 grant execute on procedure siebstat to public
db2 grant execute on procedure siebtrun to public
db2 grant execute on procedure siebdbx to public
db2 grant execute on procedure nextseq to public
db2 grant execute on procedure siebtrim to public
|
-
Activate the database after the migration by running:
-
Check the values for the registry variable, database configuration, and database manager configuration. Set the global registry variables using the db2set -g command. These types of variables are not migrated.
-
Rebind all the packages, since all packages are invalidated during the migration. It's recommended to rebind all the packages by running:
db2rbind dbname -l output_filename all
You can also bind CLI packages. Go under sqllib/bnd and issue the following
db2 bind @db2cli.lst blocking all grant public clipkg 6
|
-
Migrate db2 explain tables, if you need them, by running:
db2exmig -d dbname -e explain_schema [ -u userid password ]
|
-
Gather all the database information by running the same commands as in step 2 in the Pre-migration section.
-
If you want to enable the compression for all or some tables, you need to alter the table and turn the compression on for the table, and then issue a reorg command with the RESETDICTIONARY option, like the following:
db2 "alter table tablename compress yes"
db2 "reorg table tablename resetdictionary"
|
You can turn off the compression again by altering the table and disabling compression (compress no), and then issue the same reorg command.
-
Update the statistics on all Siebel database tables. To run runstats on all Siebel tables, you can modify the script get_table_count.sh, found in Listing 1. For this, replace all the instructions and commands in the loop between do and done by the following:
do
echo "-------- Runstats on table $tabname ..."
db2 "runstats on table $tabname with distribution
and detailed indexes all shrlevel change"
echo
done < y
|
-
Backup your database after migration completion, as shown in step 1 of the Pre-migration section.
-
Migrate the connected clients to the server. For more details, refer to "Migration overview for DB2 clients."
-
Set STMM to have better performance by:
-
On a database configuration level, set SELF_TUNING_MEM to ON and set the following to AUTOMATIC:
DATABASE_MEMORY, LOCKLIST, MAXLOCKS, PCKCACHESZ, SHEAPTHRES_SHR, SORTHEAP, SHEAPTHRES_SHR, NUM_IOCLEANERS, NUM_IOSERVERS, DFT_PREFETCH_SZ, MAXAPPLS
-
On a database manager configuration level, set the following parameters:
- Set SHEAPTHRES to 0 (zero)
- Set INSTANCE_MEMORY, FCM_NUM_BUFFERS and FCM_NUM_CHANNELS to AUTOMATIC
-
Set the size of all database buffer pools to AUTOMATIC.
You can run the following script on a UNIX platform to enable this new DB2 9 functionality.
Listing 3. Enabling STMM. Script name: enable_stmm.sh
if [ $# -lt 1 ]; then
echo "This script need database name as a parameter : "
echo
echo " Example : $0 database_name"
echo;echo
exit 1
fi
export db_name=$1
db2start
db2 -v connect to $db_name
echo "db cfg level"
db2 -v update db cfg for $db_name using self_tuning_mem on
db2 -v update db cfg for $db_name using database_memory automatic
db2 -v update db cfg for $db_name using locklist automatic maxlocks automatic
db2 -v update db cfg for $db_name using pckcachesz automatic
db2 -v update db cfg for $db_name using sortheap automatic sheapthres_shr automatic
db2 -v update db cfg for $db_name using num_iocleaners automatic
db2 -v update db cfg for $db_name using num_ioservers automatic
db2 -v update db cfg for $db_name using dft_prefetch_sz automatic
db2 -v update db cfg for $db_name using maxappls automatic
echo "dbm cfg level"
db2 -v update dbm cfg using sheapthres 0
db2 -v update dbm cfg using instance_memory automatic
echo "bufferpool level"
db2 "select bpname from syscat.bufferpools" > x
export rownums=$(cat x | wc -l)
headrows=$(($rownums-3))
tailrows=$(($headrows-3))
cat x | head -$headrows | tail -$tailrows > y
while read bp_name; do
db2 -v alter bufferpool $bp_name size automatic
done < y
rm x y
db2 connect reset
db2 terminate
db2 force application all
db2stop force
db2start
|
|
Disclaimer
All the information contained in this document is based upon publicly available information as of March 22, 2007, and is subject to change. IBM disclaims all warranties as to the accuracy, completeness, or adequacy of such information. IBM shall have no liability for errors, omissions, or inadequacies in the information contained herein or interpretation thereof
Acknowledgements
Many thanks to the following individuals who generously provided their time and expertise to help write this article:
-
Thomas Chu -- Manager, DB2 System Test
-
Kimberly Messer -- Information Development Support team
-
Dilip Kikla -- IBM Siebel/Oracle Technical Alliance Team
-
Amyris Rada -- Migration team
-
Andrew Miller -- IM Marketing Lead
-
Jeegar Ghodasara -- Migration team
Downloads | Description | Name | Size | Download method |
|---|
| Shell script to get Siebel table counts | get_table_count.sh | 2KB | HTTP |
|---|
| Shell script to get information on the database | get_db_info.sh | 2KB | HTTP |
|---|
| Shell script to enable STMM | enable_stmm.sh | 2KB | HTTP |
|---|
Resources Learn
-
DB2 migration portal technote: Navigate through the available information and resources related to migrating DB2 database products to a new release.
-
DB2 9 overview: Find general product and tools introductions, what's new for each release, changes in existing functionality from previous releases, tutorials, documentation information, product accessibility documentation, and notices.
-
DB2 9 Migrating information management systems: Understand how to migrate affected environment components, including applications.
-
Autonomic computing technical library: Find a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks involving autonomic computing.
-
DB2 9 for Linux UNIX and Windows: Learn more about DB2 9, the next-generation hybrid data server with optimized management of both XML and relational data.
-
IBM Redbooks "DB2 9: pureXML Overview and Fast Start": Read about the hybrid XML data services in DB2 9 for Linux, UNIX, and Windows.
-
IBM Redbooks "DB2 9: pureXML Guide": Learn about the pureXML data store, hybrid database design, and administration. It also describes XML schemas, industry standards, and how to manage schemas. Furthermore, learn about SQL/XML, XQuery, and XPath using easy-to-understand examples. Finally, see how to use XML technology efficiently in business applications.
-
IBM Redbooks "Automatic Configuration for IBM DB2 9": Find out about the DB2 Performance Configuration Wizard, an expert tool for the configuration of DB2 Universal Databases.
-
"DB2 Label-Based Access Control, a practical guide, Part 1: Understand the basics of LBAC in DB2" (developerWorks May, 2006): Read a tutorial that includes use case scenarios that demonstrate how users can apply LBAC to protect their data from illegal access, and yet have the flexibility of allowing users to access data restrictively. It also provides a step-by-step guide to creating LBAC solutions based on use-case scenarios.
-
"DB2 label-based access control: A practical guide, Part 2: A step-by-step guide to protect sensitive data using LBAC" (developerWorks May, 2006): Learn to use DB2's LBAC security feature. LBAC controls access to table objects by attaching security labels to them. Users attempting to access an object must have its security label granted to them. When there's a match, access is permitted; without a match, access is denied.
-
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
-
Stay current with developerWorks
technical events and webcasts.
Get products and technologies
-
Build your next development project with
IBM trial software, available for download directly from developerWorks.
Discuss
About the author | 
| | Noureddine Brahimi is a member of the DB2 System Test team at the IBM Toronto Software Laboratory. Since joining the lab in April 2004, he has worked on different projects related to Siebel/DB2 integration and testing. Prior to joining the Toronto Lab, he worked with IBM France for three years on various Siebel/DB2 migration projects. Any feedback related to this article can be directed to Noureddine at noureddinebrahimi@yahoo.ca |
Rate this page
| |
