5Planning a Siebel Database Upgrade
Planning a Siebel Database Upgrade
This chapter describes some of the important database-related issues to consider when planning an upgrade to the current Siebel CRM release. Also review the applicable topics in the chapter in Siebel Database Upgrade Guide that describes the Siebel database and UI upgrade planning tasks before starting your upgrade. This chapter includes the following topics:
Planning Resources for Upgrading to Siebel CRM on z/OS
The chapter in Siebel Database Upgrade Guide that describes Siebel database and UI upgrade planning tasks also describes the planning resources available to you before you start to upgrade to the current release of Siebel CRM. This topic describes additional resources available if you use Siebel Business Applications with DB2 for z/OS.
Documentation
Read Implementing Siebel Business Applications on DB2 for z/OS, available on the Siebel Bookshelf, for information on configuring your Siebel application after you have upgraded to the current Siebel CRM release.
About Oracle’s Advanced Customer Services
Oracle’s Advanced Customer Services offers detailed implementation planning and technical consulting services. Oracle recommends that you engage Oracle’s Advanced Customer Services for help in planning your upgrade on DB2 for z/OS. Oracle’s Advanced Customer Services can help you to:
Take advantage of the new features provided by the latest version of Siebel CRM
Customize the upgrade scripts and the upgrade process as appropriate for your installation
Carry out performance tuning on the upgrade scripts to minimize production downtime
For further information, see About Using Oracle’s Advanced Customer Services.
Planning Changes to the Physical Layout of the Schema
In planning your upgrade, you must understand the existing physical layout of your schema and determine whether or not you have to change the layout for the upgrade to the current release of Siebel CRM. Also consider database space requirements and whether or not you have to move table spaces. These issues are discussed in this topic.
New Database Schema Structure Since Siebel CRM Release 7.7.x
Upgrades from: Release 7.5.3 only.
In Siebel CRM Release 7.7.x, the current database schema structure, referred to as the 1:1:1 model, was introduced. It has the following characteristics:
One table in each table space
One table space in each database
Prior to Siebel CRM Release 7.7.x, the database schema was built using approximately 20 databases, each of which contained multiple table spaces. Each of these table spaces (if nonpartitioned) contained multiple tables. The current version of Siebel CRM contains thousands of databases. For example, an SIA installation has approximately 2700 databases. Each database has one table space and each table space has one table.
This model meets IBM recommendations and prevents database descriptor (DBD) locking and logging. These issues arise due to the increasing intensity of DB2 DML and DDL operations and the interaction of these operations with the DBD. The DBD is locked when information about the DB2 objects contained by the DBD is requested and accessed. In general, the more objects a DBD contains, the more probable that a DBD lock will be requested when information about a child object of the DBD is accessed.
Locks are acquired on the DBD table space (DBD01) if a DBD is not in memory (EDM pool). If the DBD is in the EDM pool, no lock is acquired on it if the SQL being run is static. However, most SQL executed by the Siebel application is dynamic; this means locks are acquired on the DBD. For more information on DBD locking, refer to the relevant IBM documentation.
The adoption of the 1:1:1 model since Siebel CRM Release 7.7.x means that if you are upgrading from Siebel CRM version 7.5.3, you must decide how much of this model to deploy. You have the following options:
Create all tables in the 1:1:1 model.
New 8.1 or 8.2 tables are created in the 1:1:1 model and the storage control file supplied with Siebel Business Applications is used for the upgrade.
Create new tables in the 1:1:1 model and maintain existing tables in their current table space if possible.
The following scenarios arise if you select this latter option:
Some existing tables have to be moved to incorporate the addition of new columns.
Some existing tables have to be moved because they have been extended and the addition of new columns causes the table’s LRECL to exceed that of the table space. This necessitates the use of the extract and merge methodology to create the storage control file. For more information on this methodology, see Extracting the Storage Control File.
Tables that are to retain the existing format are merged into the template control file which employs the 1:1:1 model.
For both options, enter existing tables that are to be migrated to the 1:1:1 model in the file override.inp. See About the Override File for further information. For more information on using storage control files, see Implementing Siebel Business Applications on DB2 for z/OS.
Preparing a Storage Control File
A key task for a successful upgrade is the building of a suitable storage control file for both the development and production upgrade. You must consider space requirements. This is particularly important for the development upgrade, because three new repositories are imported into the database (one extra repository is imported during the production upgrade). Some repository tables will increase significantly in size, so you must provide sufficient space for expected database growth. For information on preparing a storage control file, see Process of Preparing the Storage Layout of the Schema.
About Moving Table Spaces
If you want to move tables from one table space to another, you must recreate the tables in the new table space and then drop the existing table space, if it is empty. You cannot change the bufferpool designation in the storage control file to move tables because the page size is associated with the table space.
For example, if you are making changes to an existing table space that is using BP1 or a 4 KB bufferpool and these changes cause you to receive a warning from ddlimp that the table must now be in a 16 KB bufferpool, do not just change the bufferpool designation in the storage control file from BP1 to BP16K1. Doing so can cause any LONG VARCHAR column in the table to be bigger than is necessary, resulting in performance problems.
Testing Before a Production Upgrade
Careful testing is critical for a successful upgrade. In particular, the production upgrade must be thoroughly tested to avoid data-specific issues and gain the best possible performance during your upgrade.
Considering Code Page Support
Siebel CRM 8.x supports ASCII-, EBCDIC-, and Unicode-based coded character set IDs (CCSIDs) on DB2 for z/OS. Development databases can use EBCDIC code pages. Databases with EBCDIC code pages support the following procedures in a development environment upgrade:
Merging prior configuration changes into a new custom configuration repository
Generating the Siebel Runtime Repository data by executing the Full Publish command in Siebel Tools
Some limitations on databases with EBCDIC code pages include the following:
Siebel Web Client migration is not supported
Siebel Dun & Bradstreet server components are not supported
Before you conduct an upgrade, carefully read Siebel Release Notes on My Oracle Support for information about known restrictions. For guidelines about choosing the code page for your subsystem, see Implementing Siebel Business Applications on DB2 for z/OS. In addition, make sure you follow the rules specified by IBM for character conversion as described in the IBM DB2 installation documentation.
About Code Page Conversion
Siebel CRM supports the ASCII 5348 and EBCDIC 1140 code pages on DB2 for z/OS. If your current Siebel CRM release runs on a different code page, you must migrate to one of the supported code pages before upgrading to the current Siebel release.
Since Siebel CRM 7.5, Siebel Business Applications has supported the Unicode character set. On the z/OS platform, Siebel Business Applications only support the Unicode character set on DB2 for z/OS version 8 running in New Function Mode or later releases. Character conversions from Unicode code pages to ASCII and EBCDIC code pages on the z/OS host are performed by the z/OS Unicode Services; these conversions are required if Siebel Business Applications are to function correctly. For information on setting up the z/OS Unicode Services, see the IBM document about using Unicode Services on the IBM z/OS information center Web site at http://publib.boulder.ibm.com/infocenter/zos/v1r9/index.jsp
Check the Siebel schema code page before starting the Siebel upgrade to ensure characters on the target schema will display correctly. Schedule sufficient time to perform and test the code page conversion before beginning the upgrade. For advice and assistance on converting DB2 code pages, contact IBM.
Executing Jobs Using Siebel-Scheduled Mode or Vendor-Scheduled Mode
Before you perform a production upgrade, determine whether you will execute the jobs using Siebel-provided job scheduling or a third-party vendor scheduler. Choose your scheduling mode carefully, because once you begin an upgrade process under a selected mode, you cannot change your scheduling mode or reverse this decision.
Siebel-provided job scheduling is implemented by default. If you want to use a vendor scheduler, you must edit the data set DSNHLQ.SIEBEL.EXEC(@PRETPTH) and set the Scheduling option to a value of 2 (Vendor Scheduled).
In Siebel Scheduled Mode, the Siebel job scheduler uses job submission EXECs to run the following upgrade jobs:
Pret
Pret_FINS
Household
Household_FINS
Preschm
Preschm-FINS
Upglss
The Siebel job scheduler automatically submits dependent jobs by their predecessors.
Using Siebel Scheduled Mode, if a job ends abnormally or returns an invalid return code, the upgrade process is halted. You can check the job status by querying the Siebel job log. For further information, see Reviewing the Siebel Upgrade Log Files
If you use a third-party job scheduler, jobs are not submitted automatically. In this case, you will find it useful to first generate the upgrade scripts using Siebel scheduling to gain an understanding of job dependencies.
Logging is provided for both Siebel-scheduled and vendor-scheduled jobs using a DB2 table. Each job contains a step that checks whether or not all the other steps in the job completed successfully. An SQL UPDATE statement is then executed against the log table specifying the job status.
Staging and Target Database Planning
When scheduling your upgrade, be aware that the target database schema must not be changed after the staging DDL is produced. If target database schema changes are applied before the upgrade is completed, you must recreate the staging database and generate the upgrade files again.
When planning your upgrade, keep in mind the following:
The staging database must be in a separate DB2 subsystem to the target database
The staging database table space names must be same as those in the target database
The staging database tableowner and storage group names can be the same or different to the target database names
DB2 DSNZPARM Settings For the Target Database
For Siebel Business Applications to run correctly and efficiently, the DSNZPARM parameters for your target database must be set correctly. For a list of the required and recommended DSNZPARM parameter settings for DB2 for z/OS when using Siebel Business Applications, see Implementing Siebel Business Applications on DB2 for z/OS.
Obtaining Required Software and Hardware
Because each enterprise has specific requirements for hardware and software resources, it is recommended that you discuss your particular situation with your Siebel technical resource. Make sure your hardware supports the requirements of your Siebel CRM upgrade.
To verify that your computer, operating system platforms, and third-party programs are supported for the current release of Siebel CRM, see the Certifications tab on My Oracle Support (https://support.oracle.com).
Obtaining Required IBM Utilities
Verify that the following IBM utilities are available for your upgrade to the current release of Siebel CRM:
DSNTEP2. The upgrade uses DSNTEP2 to execute SQL.
Prepare and bind DSNTEP2 by following the procedures in your IBM installation documentation. If you made local modifications, you might have to prepare and bind a separate version. Also, if you are using a separate version of DSNTEP2, you have to change the SIEBSQL* members to reflect the new plan and program names.
DFSORT. The utility DFSORT is used to manipulate data for data migration during upgrade.
LOAD. The IBM DB2 Load utility is used to load data during data migration.
REBUILD INDEX. The IBM DB2 REBUILD INDEX utility (DSNUTILB) is used to build indexes after they are created using
DEFER YES.IEBCOPY. The utility IEBCOPY is used to create members in installation data sets. Sequential data sets contains control information used by IEBCOPY.
IEBGENER. The utility IEBGENER is used to copy sequential data sets.
DSNTIAUL. The upgrade uses DSNTIAUL to unload Siebel application data.
Compile, link-edit, and bind DSNTIAUL by following the procedures in your IBM DB2 installation documentation.
For information on these IBM utilities, see your IBM documentation.
About Using the DSNTIAUL Utility
In Siebel CRM releases since 8.0.x, the DSNTIAUL utility is used to unload data from tables. However, the SELECT statements that Siebel CRM 8.x uses to specify the selected columns to be unloaded cause DSNTIAUL to issue a warning message and return a nonzero return code for every unload job. This makes the process of determining whether an unload job completed successfully or not difficult.
To avoid this problem, Siebel CRM 8.x contains a patch for DSNTIAUL which causes a successful unload job to generate a zero return code by changing the selective SELECT warning message to an informational message. Jobs that generate a zero return code require no further consideration. After you apply the patch to DSNTIAUL, all jobs resulting in nonzero return codes must be reviewed. For further information, see About Applying the DSNTIAUL Patch.
About Applying the DSNTIAUL Patch
The DSNTIAUL patch, @@TIAUL USERMOD, is packaged as an SMP/E format USERMOD. The patch is a MACUPD (macro update) because DSNTIAUL is delivered as a macro and not as a source object. The @@TIAUL USERMOD updates the DSNTIAUL assembler source code so that nonzero return codes generated solely by the selective SELECT statements are suppressed. The USERMOD is generic in that it applies to known fix levels of the DSNTIAUL utility.
To apply the patch, copy the @@TIAUL USERMOD code, listed in The @@TIAUL USERMOD Patch, and apply it to the SMP/E DB2 target zone on z/OS. You can apply the patch using various methods, for example:
Use the SMP/E APPLY command to apply @@TIAUL USERMOD directly to the z/OS system, then run assemble, link, and bind jobs to specify it as the default program
Copy the source member containing the original DSNTIAUL code and add the code from @@TIAUL USERMOD to the copy. Then create an executable with a new name for the new source member.
Ask your z/OS system administrator for the most appropriate method to use to apply the @@TIAUL USERMOD at your site.
The @@TIAUL USERMOD Patch
This topic lists the @@TIAUL USERMOD for IBM DB2 UDB for z/OS. Before copying the following code and applying the USERMOD, change the modification control statements to reflect the applicable maintenance level of the existing DSNTIAUL utility; comments in the @@TIAUL USERMOD provide specific instructions.
++ USERMOD (@@TIAUL) REWORK(2010007)
/*
USERMOD @@TIAUL:
ALLOW RETCODE 0 EVEN WHEN SELECTING LIMITED COLUMNS.
THIS USERMOD WILL CAUSE ONE SECTION OF CODE TO BE BYPASSED.
THIS SECTION SETS RETURN CODE 4 IN THE CASE OF A SELECT NOT BEING A FULL SELECT WITH "*". THE CHANGE WILL NO LONGER FORCE A RETURN CODE 4.
THE CHANGE ALSO INCLUDES AN "ORACLE81" EYECATCHER.
NOTE:
THE PRE-REQUISITE (PRE) OPERAND <<MUST>> BE CHANGED ON THE
++VER CONTROL STATEMENT TO REFLECT CURRENT MAINTENANCE LEVEL.
MACRO DETAILS:
MACRO FMID RMID SYSLIB DISTLIB DATE
DSNTIAUL HDB8810 UK50731 SDSNSAMP ADSNMACS 2010.01.07
*/ .
++ VER (P115)
FMID(HDB8810)
PRE (UK50731)
/*
***************************************************************
* DO NOT ADD LINE NUMBERS TO THIS USERMOD! *
***************************************************************
* REP LINE WITH SEQ NUMBERS 04600000 *
* SKIP LINES WITH SEQ NUMBERS 11900000 TO 11930000 *
***************************************************************
*/ .
++MACUPD(DSNTIAUL) DISTLIB(ADSNMACS) .
./ CHANGE NAME=DSNTIAUL
SAVE (14,12),,'DSNTIAULORACLE81&SYSDATE.&SYSTIME' @ORACLE 04600000
*** DO NOT SET WARNING RETURN CODE *** @ORACLE 11895000
AGO .NOSLCT_BYPASS @ORACLE 11895001
.NOSLCT_BYPASS ANOP , @ORACLE 11935000
./ ENDUP
About DSNTIAUL CCSID Conversion Errors
DSNTIAUL unloads all Siebel application data in an EBCDIC CCSID. If the data to be unloaded is in ASCII format, conversion errors can occur for characters that are supported in ASCII but that are not supported in EBCDIC, for example, the euro and copyright symbols. CCSID conversion errors generate a return code of 4 and produce an SQLSTATE of 01517.
If the ASCII data contains a character that cannot be converted to EBCDIC, DSNTIAUL stops the unload process at that point. To correct this problem, you can either:
Update the source data and rerun the unload jobs
Use a program, such as the IBM DB2 UNLOAD utility, to complete the load and unload processing
Specify the DSNTIAUL TOLWARN (YES) parameter
The TOLWARN (YES) parameter forces DSNTIAUL to continue the unload process by inserting substitution characters for characters that cannot be converted; this ensures all records are unloaded even if they contain data that cannot be directly converted from ASCII to EBCDIC.
However, using the TOLWARN (YES) parameter can also cause corrupted data in your upgraded Siebel application. DSNTIAUL provides the same substitution character for all ASCII characters that cannot be converted to EBCDIC. When the data is converted back to ASCII, the substituted characters are all converted back to the same ASCII character, for example, the euro and copyright symbols will both be represented by the same character. After the upgrade, your Siebel application will contain corrupted data and you will have to review the source data to determine which symbol a corrupted character corresponds to.
Obtaining Required Security Privileges
For detailed information about security for DB2 for z/OS installations and upgrades, see Implementing Siebel Business Applications on DB2 for z/OS.
In Siebel CRM releases since 8.0.x, access privileges to database resources such as tables, views, and triggers are granted to a user group. A user group is a definition within the security package (for example, RACF) that has a common set of users attached to it. Access to the DB2 tables is granted to the user group, and user authentication is performed at the group level. All users belonging to the group are allowed access. All users who are not part of the group are denied access.
The user who executes the upgrade must be a member of a qualified group. To grant this user tableowner privileges, the tableowner must be set up as a qualified group, and the DBA who executes DDLs must be a member of this qualified group. The group ID is the qualifier (for example, RACF group ID).
The Siebel installation process allows the installer to specify the group user name for client access (the default is SSEROLE), and the resulting installation scripts generate the appropriate GRANT statements. GRANT statements for additional security groups that might be required must be created manually.
The following privileges are necessary for the user who performs the upgrade:
Read the DB2 catalog
Execute stored procedures
Bind stored procedures
Because each enterprise has specific requirements, it is recommended that you discuss your particular situation with your Siebel technical resource.
Planning Backup and Recovery Stages
In addition to the backup and recovery procedures that are standard for your environment, take a set of DB2 backups at key stages during the upgrade, using your preferred utility. A snapshot of your repositories and environment at these stages protects the progress of your upgrade in the event of a failed subsequent process.
It is recommended that you back up your Siebel schema at these key stages of the upgrade:
Before any upgrade activity is started
Before performing unloads
Note: Unloads must be performed when there is no system activity, so that the database is at a point of consistency.After upgrading the Siebel database schema
After the repository merge
After upgrading the custom database schema
Review the results of all JCL jobs that you execute during the install or upgrade process. You can use a spool viewer such as IBM’s SDSF to inspect the output from these jobs. You can review this information in addition to reviewing the upgrade log files.
About Creating a Schedule for the Upgrade
Develop a plan for your upgrade based on the objectives and constraints for your deployment.
If you are migrating multiple languages from a prior version, plan extra time for the repository merge process. The expected merge time can increase with the number of languages in the repository. You also might have to plan for additional installation-related tasks.
The following procedures can reduce the time required for your upgrade:
Run selected processes in advance of the upgrade.
Certain preupgrade tasks can be run at any time prior to the upgrade. These procedures can be performed in advance either for testing purposes or to accommodate downtime constraints.
Examples of procedures that can be performed by a database administrator in advance of your upgrade include Process of Preparing the Storage Layout of the Schema.
Apply additive schema changes to the production database ahead of the target database in-place upgrade. See About Siebel Additive Schema Upgrade Changes.
Prepare select processes to run in parallel.
If a large table such as
S_EVT_ACTis partitioned, it can run in parallel by transferring shipped statements into the numbered SQL statement.Do not start a new development effort until after the new version has been rolled out.
About Estimating Database Size
Database upgrade is resource intensive. If the upgrade exceeds available resources, the upgrade halts. You must then resolve resource issues before resuming the upgrade.
To help you estimate the database size required when upgrading to the current Siebel CRM release, the following table shows the number of tables in 4-KB, 8-KB, 16-KB, and 32-KB table spaces in a sample Siebel Industry Applications database in a 7.8, 8.0 and 8.1 release. The following table also shows the space required by the tables.
Because Siebel CRM adopted a 1:1:1 database schema structure since Siebel CRM 7.7 (one table in each table space, one table space in each database), these releases require many more 16-KB and 32-KB table spaces than pre 7.7 releases. However, some tables might not require 16-KB and 32-KB table spaces if you convert LONG VARCHAR columns to CLOB columns.
Actual expected growth might also vary widely from these estimates, depending on which Siebel application you are using (Siebel Business Applications or Siebel Industry Applications), database configuration, row size of tables, data content, and code page. The number of tables and space estimates shown in the following table for Siebel CRM are for an EBCDIC database.
Table Number of Tables and Space Required in a Sample Siebel DB2 Database by Release
| Release | 4-KB Table Space | 8-KB Table Space | 16-KB Table Space | 32-KB Table Space |
|---|---|---|---|---|
8.1 |
4136 (5,194,752 KB) |
375 (828,416 KB) |
182 (71,680 KB) |
115 (134,144 KB) |
8.0 |
4229 (2,759,008 KB) |
331 (312,112 KB) |
171 (62,272 KB) |
105 (52,160 KB) |
7.8 |
3857 (569,442 KB) |
Not applicable |
459 (237,735 KB) |
86 (224,62 KB) |
Upgrading Your DB2 Software
Upgrades: All upgrades.
Environments: Development environment only.
Before you upgrade, use the Certifications tab on My Oracle Support to verify that you are using currently supported versions of the following DB2 software:
DB2 for z/OS. If you are using an earlier version of DB2 for z/OS than the versions currently supported, you must migrate to a supported version before you upgrade to the current release of Siebel CRM.
DB2 Connect. Siebel Developer Web Clients (Siebel Mobile Web Client in connected mode) and Siebel Servers communicate with DB2 for z/OS through DB2 Connect middleware. Verify that you are using the version of DB2 Connect supported for the current release of Siebel CRM.