Basic Database Preparations for a Siebel Upgrade

This chapter describes the DB2 for z/OS database tasks you must complete before upgrading to the current Siebel CRM release. Also review the chapter in Siebel Database Upgrade Guide that describes the basic database preparations for a Siebel upgrade and perform any applicable tasks before starting your upgrade. This chapter includes the following topics:

Verifying Database Configuration

Upgrades: All upgrades.

Environments: All environments.

Verify that your development Siebel database configuration meets or exceeds Siebel requirements as described in Implementing Siebel Business Applications on DB2 for z/OS. Be aware of the following:

The upgrade sets the TRACKMOD parameter to YES, which is the IBM default value for table space objects. Oracle recommends that you set the TRACKMOD parameter to NO, to reduce data sharing overhead.
Set the DSNZPARM CMTSTAT to INACTIVE to prevent timeout errors from occurring. If you do not set this DSNZPARM parameter to INACTIVE, then set IDTHTOIN to 0 (Inactive).
Ensure the z/OS Unicode Conversion Services are correctly installed and configured. For further information on z/OS Unicode Conversion Services, see Implementing Siebel Business Applications on DB2 for z/OS.

Before you begin your upgrade, verify your database configuration. The consequence of exceeding available resources is a halted upgrade that requires you to allocate time to adjust the environment and then resume the upgrade.

Before you begin an upgrade or Incremental Repository Merge, you must have a default WLM environment defined for the DB2 subsystem.

When you upgrade from a previous version of Siebel Business Applications to the current release, the size of your database is likely to increase. The amount by which your database grows might vary widely, depending on your database configuration, row size of tables, and data content. For information on estimating the growth, see About Estimating Database Size.

The growth percentage will also increase depending on how you size your database and configure default storage for database table spaces. For example, if you set the default storage for your initial or next extent in a given DB2 table space to 10 KB, that table space will grow by a smaller percentage than if you set it to 100 KB.

Creating Storage Groups

Upgrades: All upgrades.

Environments: Development environment only.

Before upgrading the Siebel database, your DBA must create storage groups on the staging database. The names used to define the storage groups for the staging database can be the same or different to those defined on the target database.

Updating Table Space Group Names

Upgrades: All upgrades.

Environments: Development environment only.

Each Siebel table is assigned a group code number, which is stored in the GROUP_CD column of the S_TABLE table in the Siebel Repository. The group code identifies the name of the DB2 table space that is used when the table is created or re-created.

During the upgrade process, if the table space associated with a table does not match the group code defined for the table, errors occur. Therefore, it is recommended that you update S_TABLE with any changes that have been made to table space names, as reflected in the DB2 catalog. By updating S_TABLE, you ensure that the group names in the Siebel Repository and in your physical database environment are synchronized.

The following procedure describes how to update the S_TABLE table.

To update table space group names

To update the table space group names, execute the following query:

UPDATE SIEBEL.S_TABLE A 

SET A.GROUP_CD = (SELECT B.TSNAME FROM SYSIBM.SYSTABLES B WHERE A.NAME = B.NAME 
AND B.CREATOR = SIEBEL)
WHERE A.INACTIVE_FLG = 'N' and A.REPOSITORY_ID = (SELECT B.ROW_ID FROM 
SIEBEL.S_REPOSITORY B
WHERE B.NAME = 'Siebel Repository')

where SIEBEL is your Siebel schema qualifier name.

Process of Preparing the Storage Layout of the Schema

Upgrades: All upgrades.

Environments: All environments.

Before starting the upgrade, you have to prepare the storage control file you will use during the upgrade.

The storage control file contains information about physical schema attributes, including bufferpools, table space name and database name, that is used as the basis for the storage layout of your new 8.x Siebel database. Even if you are using a preconfigured storage layout, you must make sure that the layout is valid for your schema.

To preparing the storage control file, perform the following tasks:

Note: You must validate the storage control file after you extract it and after you modify it.

There are different starting points from which you can customize your storage layout:

Scenario 1. Begin with a Siebel-provided storage layout template, import the template into the Siebel Database Storage Configurator (dbconf.xls), customize it, then export it as your customized layout.
Scenario 2. Use your current configuration from an existing database layout and merge it with one of the Siebel-provided templates. This can then be imported to the Siebel Database Storage Configurator for further manipulation. For more information on the Siebel Database Storage Configurator, see Implementing Siebel Business Applications on DB2 for z/OS.

To prepare the storage control file, use the Database Configuration Wizard to extract the storage layout of your database from the DB2 catalog. As part of the extraction process, you can merge the storage layout information from your existing database with information you already input into a storage template file or information in a template provided with Siebel Business Applications.

As an alternative, instead of extracting the storage layout of your existing database, you can use a template provided with Siebel Business Applications as the storage control file. For more information about templates for the storage control file, see Implementing Siebel Business Applications on DB2 for z/OS. Siebel Business Applications provide the templates listed in the following table for the storage control file.

Table Storage control File Templates

Template	Description
storage_np.ctl	Database storage layout for a nonUnicode Siebel schema with no partitioning scheme
storage_np_u.ctl	Database storage layout for a Unicode Siebel schema with no partitioning scheme
storage_p.ctl	Database storage layout for the Siebel schema with partitioning for a set of tables on an ASCII database
storage_p_e.ctl	Database storage layout for the Siebel schema with partitioning for a set of tables on an EBCDIC database
storage_p_u.ctl	Database storage layout for the Siebel schema with partitioning for a set of tables on a Unicode database

Methods of Modifying the Storage Control File

This topic describes the different ways in which you can configure the storage layout of your schema.

This task is a step in Process of Preparing the Storage Layout of the Schema.

There are three methods by which you can configure storage space:

Method 1. This method consists of performing a standard Siebel database installation by running the Database Configuration Wizard, choosing the Generate DDL into a File installation option, and specifying as input one of the Siebel storage control file templates. This process generates the following:
- A storage control file, based on the Siebel template file you selected, that incorporates the configuration information you entered when you ran the Database Configuration Wizard. This file is generated in the dbsrvr\DB2390 (Windows) or dbsrvr/DB2390 (UNIX) directory
- A schema.sql file that is applied on the z/OS host to create the Siebel schema. The schema.sql file is based on the customized storage control file generated by the database install
Using these files, you can then configure storage space in any of the following ways:
- Amend the storage control file generated during the database install in the dbsrvr\DB2390 (Windows) or dbsrvr/DB2390 (UNIX) directory (see also Method 2).
- Apply the schema.sql file generated by the database install on the DB2 host to create the Siebel schema, then amend the schema using native DB2 tools. Extract the storage control file from the DB2 catalog; the file will include the changes you have made.
  
  Note: You access the extract utility through the Database Configuration Wizard. This utility allows you to extract information from the DB2 catalog. You can use this extract utility any time you want to create a new storage control file, based on the DB2 catalog.
- Amend the schema.sql file generated by the database install directly, apply it on the DB2 host to create the schema, and then extract the storage control file, which will include the changes you made.
For information on installing the Siebel database and extracting storage control files, see Implementing Siebel Business Applications on DB2 for z/OS.
Method 2. This method consists of manipulating the storage control file (storage.ctl) directly by opening it with a text editor program. This method can be used if you understand the file structure.
You can use this method to amend one of the Siebel-supplied storage control files or to amend a storage control file that you have extracted from another Siebel schema.
Method 3. This method consists of using the Siebel Database Storage Configurator tool. The Siebel Database Storage Configurator tool is a Microsoft Excel macro (dbconf.xls) that is installed in the dbsrvr\db2390 (Windows) or dbsrvr/db2390 (UNIX) subdirectory of your installation directory. This tool allows you to import a storage control file, amend it, validate the syntax of your changes, and then save it.
UNIX customers must transfer dbconf.xls and the .ctl files to their Microsoft Windows environment. Use BINARY FTP transfer for the dbconf.xls file. For information on using the Siebel Database Storage Configurator, see Implementing Siebel Business Applications on DB2 for z/OS.

Note: Validate the storage control file after you modify it. See Validating the Extracted Storage Control File for further information.

Options for Extracting the Storage Control File

This topic describes the options available when you run the Database Configuration Wizard to extract the storage control file.

This task is a step in Process of Preparing the Storage Layout of the Schema.

The information in your storage control file comes from the target database, that is, the database to be upgraded.

When you extract the storage control file, you can choose one of two methods:

Extract from Catalog
Extract from Catalog and Merge with Template

Extract from Catalog

This method extracts the storage layout of your existing database from the DB2 catalog; the output is a representation of the existing target database objects.

Use this option when creating the storage control file that is used to generate the DDL to build the staging database schema. For information on this task, see Required Tasks before Creating the Staging Database.

The Extract from Catalog method can also be used to perfect your target database layout by performing a dummy installation, manipulating the schema layout through native utilities, then extracting the customized layout. For information on the Extract from Catalog option, see Implementing Siebel Business Applications on DB2 for z/OS.

Extract from Catalog and Merge with Template

This method preserves your existing layout. This method merges storage layout information from your existing database with a storage template file provided with Siebel Business Applications. This preserves your prior layout, and your output is the prior physical layout merged with a storage layout template for the current release.

After you have created the staging database, use the Extract from Catalog and Merge with Template option to extract the storage layout of the Siebel staging database from the DB2 catalog and merge it with a storage control file for the current release so as to preserve any customizations you have made to the database layout in the upgraded database. For information on this task, see Required Tasks for Generating the Upgrade Files.

Objects Extracted to the Storage Control File

When you select the Extract from Catalog and Merge with Template option, the extracted storage control file does not list all database objects in the Siebel schema. The following list details scenarios that might occur during the extract and merge process and the behavior you can expect in these cases:

A database object exists in the existing database but not in the template. The definition of the database object is output to the new storage control file.
A database object is specified only in the template file. The definition of the database object is output to the new storage control file.
A database object is specified in both the existing database and the template. The layout of the existing database is extracted as the default. However, you can manually override this behavior by creating a file called override.inp in the BIN directory under SIEBEL_ROOT. Place any tables that you want to override into this file.

Note: Index objects are not output to the new storage control file if they are specified in both the existing database and the template and if there are no specific attribute differences between them. In this case, if you import the extracted storage control file into the Siebel Database Storage Configurator (dbconf.xls), the index objects are not displayed and their attributes cannot be edited.

Extracting the Storage Control File

After you have created the staging database but before you begin to generate the upgrade files, you must extract the storage control file from the staging database and merge it with a Siebel CRM template. You do this using the Database Configuration Wizard Extract from Catalog and Merge with Template option. Depending on the type of upgrade you are performing, assign one of the following names to the extracted storage control file:

Development environment upgrade: storage_upg_dev.ctl
Production environment upgrade: storage_upg_prod.ctl.

This task is a step in Process of Preparing the Storage Layout of the Schema.

To extract the storage control file

Run the Database Configuration Wizard.
For information on running the Database Configuration Wizard, see About Running the Database Configuration Wizard on Windows or About Running the Database Configuration Wizard Under UNIX.
Enter the information shown in Information Required for the Database Configuration Wizard Extract Option when prompted by the Database Configuration Wizard. Collect this information and verify it before running the utility.
Save the information you have entered and launch the Upgrade Wizard as described in Running the Database Configuration Wizard to Perform Upgrade Tasks
The database catalog is read and your prior custom database layout is merged with one of the Siebel database layout templates for the current release (located in the DBSRVR_ROOT\db2390 directory). New objects take a layout from one of the layout templates. By default, new tables are created as one-table-per-database to prevent concurrency and locking errors.

Note: If you choose a Siebel storage control file template that includes partitioning, and the existing database schema does not include partitioning, by default, the existing database objects are not partitioned in the storage control file generated (that is, the database catalog overrides the templates) unless you specify the table names in the override input file (override.inp).
Before using the merged storage control file, you must verify it against the staging database. For information on this task, see Validating the Extracted Storage Control File.

Information Required for the Database Configuration Wizard Extract Option

The following table lists the information you must enter to run the Database Configuration Wizard Extract and Merge option.

Table Information Required for the Database Configuration Wizard Extract and Merge Option

Field Name or Menu	Required Information
Siebel Server Directory	The absolute path of the directory where the Siebel Server is installed, for example, C`:\sba81\siebsrvr` (Windows) or `siebel/siebsrvr` (UNIX)`.` For UNIX, do not enter the string `$SIEBEL_ROOT`.
Siebel Database Server Directory	The absolute path of the directory where the Siebel Database Configuration Utilities are installed, for example `C:\sba81\dbsrvr` (Windows) or `siebel/dbsrvr (UNIX).`
RDBMS Platform	Choose IBM DB2 UDB for z/OS.
Siebel Database Operation	Choose Run Database Utilities.
Database Utilities Selection	Choose Configure Database
Database Configuration Options	Choose Extract Storage File to extract a storage control file.
Extract Options	Choose the Extract from Catalog and Merge with Template option. This option preserves your existing layout. This option merges storage layout information from the database you specify with information that you already entered into a storage control file, only taking objects from the template that do not already exist in the catalog. Note: The first time that you run an upgrade, you must use the Extract from Catalog and Merge with Template option, thereby preserving your existing layout.
Base Language	On the Base Language screen, specify which language serves as the primary language for the Siebel database. If you installed a single Siebel language pack, the Base Language screen is not displayed.
ODBC Data Source Name	Verify the ODBC name for connecting to the staging Siebel database. Windows: To find the name of your ODBC data source, navigate to the Start menu and select Settings, Control Panel, Administrative Tools, and then Data Sources (ODBC). Click the System DNS tab to find the name of your ODBC data source. UNIX: To find the name of your ODBC data source, type: `vi $ODBCINI.`
Database User Name Database Password	Enter the user name and password for the Siebel administrator of the staging database. Note: The staging database user name (user ID) must have authorization to set the CURRENT SQLID.
Siebel Schema Qualifier	Enter the eight-character identifier that designates the Siebel schema for your staging database. This is also an authorization ID. The schema qualifier must start with a letter, cannot contain special characters, and must be entered in uppercase.
Database Encoding	Specify whether your database is UNICODE or Non-UNICODE. If you select Non-UNICODE, click Next, then indicate whether your DB2 subsystem is ASCII or EBCDIC.
Environment Type	Indicate whether your database environment is production or development.
Select Siebel Schema Layout	Choose Siebel Schema without Partitioning if you want all tables only in segmented table spaces. Choose Siebel Schema with Partitioning if you want a layout that includes a set of tables that is recommended for partitioning. The remaining nonpartitioned tables are in segmented table spaces.
Default Table Space	Enter the name of the default table space.
Storage Group for Table Spaces Storage Group for Indexes	Indicate the values for the following parameters: Storage Group for Tablespaces. Enter the name of the table storage group. Storage Group for Indexes. Enter the name of the index storage group.
4KB Buffer Pool Name 8KB Buffer Pool Name 16KB Buffer Pool Name 32KB Buffer Pool Name Index Buffer Pool Name	Indicate the values for the following parameters: 4KB Buffer Pool Name. Enter the 4-KB buffer pool name for your table spaces or accept the default name, BP1. The DBA must have activated this buffer pool and granted access to it. 8 KB Buffer Pool Name. Enter the 8-KB buffer pool name for your table spaces or accept the default name, BP8K1. The DBA must have activated this buffer pool and granted access to it. 16KB Buffer Pool Name. Enter the 16-KB buffer pool name for your table spaces or accept the default name, BP16K1. The DBA must have activated this buffer pool and granted access to it. 32KB Buffer Pool Name. Enter the 32-KB buffer pool name for your table spaces or accept the default name, BP32K1. The DBA must have activated this buffer pool and granted access to it. Index Buffer Pool Name. Enter the buffer pool name for indexes or accept the default name, BP2. The DBA must have activated this buffer pool and granted access to it.
Database Name Prefix	Enter the prefix to assign to the names of logical Siebel databases on the target database. The default prefix is SIDB. Note: The prefix can consist of a maximum of four characters in length, it must start with a letter, and it cannot contain any special characters. The database name prefix must be the same for all database objects in the Siebel schema because the prefix identifies an object as belonging to the Siebel schema. Siebel utilities can recognize and use Siebel objects only if they follow Siebel naming conventions.
Storage Control File	Enter the directory path and name for the storage control file created by this process. You must accept the default value displayed in the Storage Control File field; this varies depending on the type of upgrade you are performing: Development environment upgrade: storage_upg_dev.ctl Production environment upgrade: storage_upg_prod.ctl.
Log Output Directory	Accept the default directory (dbconfig_extract_merge_mf) or enter a different directory name. If the directory does not exist, it will be created. Do not use special characters such as spaces, slashes, or symbols in the name of the log output.

Validating the Extracted Storage Control File

When you have extracted your existing database storage control file and merged it with a Siebel CRM template in preparation for the upgrade, you must validate the storage control file. (You must also validate the storage control file any time you modify it.) The validation process checks that the tables are the correct length for the target schema. Do not proceed with the upgrade until the validation process runs without error.

This task is a step in Process of Preparing the Storage Layout of the Schema.

The following procedure describes how to validate the storage control file you extracted and merged as described in Extracting the Storage Control File.

To validate the storage control file

Run the Database Configuration Wizard.
The procedure to validate a storage control file using the Database Configuration Wizard is also described in Implementing Siebel Business Applications on DB2 for z/OS.
Specify the following values:
1. On the Database Configuration Options screen, select the Validate Storage File option.
2. On the Data Transport Method screen, select the Batch - Generate Unload/Load option.
3. On the following screens, make sure you specify values for the staging database:
  - ODBC Data Source Name
  - Database User Name
  - Siebel Schema Qualifier
4. On the Schema File screen, specify the following values:
  - Schema File: Specify the directory path and filename of the file against which the extracted file is to be validated. For development environment upgrades, specify the ddl.ctl file. For production environment upgrades, specify the schema.ddl file.
  - Storage Control File: Specify the name of the storage control file you extracted and merged in Extracting the Storage Control File. For development environment upgrades, specify storage_upg_dev.ctl. For production environment upgrades, specify storage_upg_prod.ctl.
When you have entered all the required values and reviewed them, launch the Siebel Upgrade Wizard to start the validation process. See Running the Database Configuration Wizard to Perform Upgrade Tasks.
When the validation is completed, review the log files, dbvalidate_report.log and dbvalidate_schema.log, that are generated in the SIEBEL_ROOT\LOG\dbconfig_validate_mf\output directory (Windows) or the SIEBEL_ROOT/ LOG/dbconfig_validate_mf/output directory (UNIX).
If any validation errors occurred, correct them, then run the validation process again.

About Validation Errors

Run the validation process until no errors are reported. The most common reason that the validation process fails is because table spaces for the Siebel schema are not large enough to hold the new table definitions. If this error occurs, examine the validation log file and identify the names of the buffer pools associated with the table spaces generating errors. Increase the bufferpool sizes as necessary in the storage.ctl file.

You can amend the storage control file using any of the methods described in Methods of Modifying the Storage Control File. To amend buffer pool sizes associated with table spaces generating errors using the Database Storage Configurator (dbconf.xls), perform the following procedure. For further information on using the Database Storage Configurator, see Implementing Siebel Business Applications on DB2 for z/OS.

To amend bufferpool sizes using dbconf.xls

Open dbconf.xls and select Enable Macros when prompted.
Import the storage control file that generated the validation errors:
1. With the Home tab active, click Import.
2. Go to the directory where your storage control file is located and double-click the appropriate file.
3. When the import process is completed, click OK.
The following message appears:
```
Please enter default values for your system 
```
Either amend the values for the displayed parameters, or accept the default values. (This screen does not appear if you have already set the default values.)
Click Set.
You are prompted to indicate whether or not you want to import row lengths. Select No.
Select the Functions tab, then click the Tools tab.
Click the Repair BP Validation button.
Select the log file generated by the validation process by double-clicking on the file, or selecting the file and clicking Open. This file lists the names of the buffer pools associated with the table spaces generating errors.
The Database Storage Configurator updates the buffer pool sizes for the table spaces generating errors in the storage control file. When the process is completed, the following message is displayed.
```
Bufferpools have been updated successfully!
```
Click OK.
Click the Home tab, and then click Export to save the amended storage control file. Save the file with the same filename. A message is displayed stating that the file will be validated (the syntax is validated).
Click OK. When the validation process is completed, a message is displayed if the file contains any values that require review.
Click OK. The values in the file that require a review are displayed, where errors are highlighted in the color Red and warnings are highlighted in the color Yellow.
Make a note of the object that is generating the error or warning; the relevant object type tab and the object are highlighted. Click OK.
Navigate to the object that generated the validation warning or error by selecting Structures, and then object type. Amend the highlighted values if required, then export the file again.
When the validation process is completed successfully and you have exported the file, exit from the Database Storage Configurator.
Validate the storage control file against the target schema again using the Database Configuration Wizard.

Reviewing the Extracted Storage Control File

After you have extracted the storage control file, you must carefully review and edit it to meet your requirements.

Note: If you extracted the storage control file on a Siebel Server that is on a UNIX operating system platform, and you would like to amend the file using the Siebel Database Storage Configurator utility (dbconf.xls), transfer the storage control file to a Microsoft Windows computer which has Microsoft Excel installed using a BINARY FTP file transfer mode. Using BINARY FTP transfer ensures that carriage returns in the storage control file are maintained and transferred correctly.

This task is a step in Process of Preparing the Storage Layout of the Schema.

To review the storage control file

Navigate to the storage control file which is located in the directory that you specified when you ran the Database Configuration Wizard to extract the file.

Check the following parameters in the control file and modify them as appropriate for your database.

Note: Do not change the defaults for the following parameters in [Object 1] in the storage control file: IndexStogroup, IndexBp, PriQty, SecQty.

[Object 1]

Type = Defaults
Name = Defaults
Database = $DbnamePrefix0000
Tablespace = SIEBTS00
Stogroup = $StogroupTables
IndexStogroup = $StogroupIndexes
IndexBp = $IndexBufferPool
Bufferpool = $4KBufferPool
Locksize = Page
SegSize = 32
LockMax = 0
PriQty = 48
SecQty = 1440
PctFree = 17
FreePage = 0
Compress = Yes
Define = No
Erase = No
CCSID = $DbType

[Object 2]

Type = Database
Name = $DbnamePrefix0000
LockSize = Page

[Object 3]

Type = Tablespace
Name = SIEBTS00
Database = $DbnamePrefix0000
Bufferpool = $4KBufferPool
Stogroup = $StogroupTables
LockSize = PAGE
LockMax = 0
SegSize = 32
PriQty = 48
SecQty = 1440
PctFree = 17
FreePage = 0
Compress = No
Partitions = 0
Define = 0
Erase = 0

Once you have a storage layout that you are satisfied with, you are ready to continue with your upgrade.
Every time you modify the storage control file, you must validate it again. For additional information, see Validating the Extracted Storage Control File.

Reviewing EIM Table Partitioning

Upgrades: All upgrades.

Environments: All environments.

Partitioning Siebel Enterprise Integration Manager (EIM) tables can improve EIM processing performance. In general, it is recommended that you partition EIM tables based on the IF_ROW_BATCH_NUM column. This method of partitioning allows an EIM batch input to be assigned to one partition, thereby allowing multiple EIM batches to be run in parallel.

Before you begin the database upgrade, review the current method of partitioning EIM tables in your implementation to make sure that it is still appropriate, and modify your partitioning keys if required. If you need help with reviewing the partitioning design of your EIM tables, contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance.

For additional information about EIM table portioning, see Implementing Siebel Business Applications on DB2 for z/OS.

Converting LONG VARCHAR Columns to CLOB Columns

Upgrades: Releases 7.5.3, 7.7.

Environments: All environments.

This topic is part of an upgrade process. See How to Perform a Siebel Database Upgrade.

In Siebel CRM Release 8.x and 7.8.x, the LONG VARCHAR columns of the following Siebel tables are converted to CLOB columns on z/OS to make sure data truncation problems do not occur:

S_BITMAP_DATA
S_DMND_CRTN_PRG
S_EVT_MAIL
S_NOTE
S_NOTE_ACCNT
S_NOTE_CON
S_NOTE_OPTY
S_SCHMST_DBSCPT
S_SCHMSTEP_SCPT
S_SERVICE_SCRPT

Before upgrading to Siebel CRM from a pre-7.8.x release of Siebel Business Applications, if any of these tables are already in 32 KB buffer pools, convert the LONG VARCHAR columns in these tables to CLOB data types to make sure the columns are not truncated if, for example, a column is added to the table during the upgrade. For additional information, see 556105.1 (Article ID) on My Oracle Support.

Caution: For pre-7.8 x upgrades, you must convert the SCRIPT column of the S_SERVICE_SCRPT table from a LONG VARCHAR data type to a CLOB data type before you begin the upgrade. If you do not, the repository merge process fails because the row length of the table exceeds the DB2 limit. Changing the data type of the SCRIPT column in the S_SERVICE_SCRPT table involves dropping and re-creating the table. Ask your DBA to perform this task or create a service request (SR). You can log service requests by accessing My Oracle Support (Service Request tab), or by using your existing phone support numbers to contact Oracle Global Customer Support.

Rebuilding Target Tables Containing LONG VARCHAR Columns

This topic describes how to rebuild tables in the target database that contain LONG VARCHAR columns.

In previous Siebel CRM releases, LONG columns in the Siebel repository were mapped to LONG VARCHAR columns on z/OS databases. The DDLIMP utility has now been modified so that LONG columns are created on z/OS as VARCHAR columns with a maximum size of 16,350 characters. This change to DDLIMP can result in inconsistencies between the staging database, on which LONG columns are mapped to columns with a maximum size of 16,350, and the target database where a LONG VARCHAR column can be much larger.

The Siebel upgrade process requires that the staging database represents the target schema to be upgraded so differences in column definitions can cause issues during the upgrade. For example, a Siebel target table in a 32-KB table space can have a LONG VARCHAR column whose length exceeds 16,350 characters. However, when the same column is created in the staging database, it has a maximum length of 16,350 characters. In these circumstances, if the upgrade process attempts to add columns to the staging table as an additive change it will succeed, but will fail when the changes are applied to the target database.

To avoid potential issues during the target database upgrade, if a table in the target database resides in a table space within a 32-KB buffer pool, and if the number or size of the table columns will be increased during the upgrade process, then the table must be re-created so that it has the same column definitions as the corresponding staging database table.

Note: You can rebuild target tables at any time before you start the upgrade but you must have completed this task before you apply Additive schema changes to the production staging database.

To rebuild target tables that contain LONG VARCHAR columns

Determine which target tables containing LONG VARCHAR columns need to be re-created.
To do this, edit and then run the sample code listed in Sample Code for Generating a List of Tables to Rebuild.
Unload data from each non-empty table included in the list.
Drop each of the tables included in the list, including empty tables.
Synchronize the target database logical and physical schemas by launching the Database Configuration Wizard and selecting the Synchronize Schema Definition option. Specify values as follow:
- When prompted to enter the database user name and password, specify values for the target database.
- When prompted for the name of the repository with which the existing Siebel database is to be synchronized, specify the following values:
  - Production upgrades: Siebel Repository
  - Development upgrades: Prior Customer Repository
The Wizard generates the DDL required to synchronize the Siebel database and the Siebel Repository. If you did not select the Run DDL Automatically installation option, then the Wizard generates files that you must apply on the z/OS database to re-create the tables you dropped in the previous step.
Reload the tables with the data that you unloaded in step 2.

For detailed information on running the Synchronize Schema Definition process, see Implementing Siebel Business Applications on DB2 for z/OS.

Sample Code for Generating a List of Tables to Rebuild

The following sample SQL code can be used to generate a list of the target tables with LONG VARCHAR columns that need to be re-created before applying ADDITIVE schema changes.

Run this code against the staging schema after the additive changes are applied to the Siebel staging database. If no tables match the selection criteria in the code, then you do not have to rebuild any target tables.

--
-- CREATE A DROP LIST FOR LONGVARCHAR TABLES
--
SELECT
SUBSTR(
CONCAT(
CONCAT(' DROP TABLESPACE ' ,
CONCAT(STRIP(T.DBNAME) ,
CONCAT('.',
STRIP(T.TSNAME)
)
)
)
, ' /'
)
, 1, 36) AS STATEMENT
,CONCAT(' -- ', T.NAME) AS COMMENT
FROM SYSIBM.SYSTABLES AS T
WHERE
T.CREATOR = STAGING_TABLE_OWNER --<<< STAGING TABLE OWNER AND
T.NAME NOT LIKE 'EIM_%'
AND
EXISTS (SELECT 1 -- longvarchar table
FROM SYSIBM.SYSCOLUMNS C
WHERE C.TBCREATOR = T.CREATOR
AND C.TBNAME = T.NAME
AND C.COLTYPE = 'VARCHAR'
AND C.LENGTH=16350)
AND
EXISTS (SELECT 1 -- has been altered
FROM SYSIBM.SYSCOLUMNS K
WHERE K.TBCREATOR = T.CREATOR
AND K.TBNAME = T.NAME
AND K.ALTEREDTS != T.CREATEDTS)
ORDER BY 1
;

whereSTAGING_TABLE_OWNER is the staging database table owner in your environment.

The following is an example of a list of tables generated by running the query in the sample code:

DROP TABLESPACE D0000005.H1000000 / -- S_ORG_EXT 
DROP TABLESPACE D0000006.H2000000 / -- S_NOTE_FUL_REQ
DROP TABLESPACE D0000007.H3000000 / -- S_NOTE_MDF

Backing Up the Database

Upgrades: All upgrades.

Environments: All environments.

Perform a full backup of the database. This backup protects your repositories and environment.

It is a recommended practice that you back up your database at key stages of the upgrade:

Before any upgrade activity is started
After upgrading the Siebel Database Schema or Custom Database Schema (upgrep + upgphys)
After the repository merge

Perform any necessary maintenance on your Siebel database, for example running REORG or RUNSTATS, before backing it up. This ensures that your database is ready for use if you have to perform a database recovery.

Granting a Siebel User Upgrade Authorization

Upgrades: All upgrades.

Environments: All environments.

The Siebel user who executes the Database Configuration Wizard and performs the upgrade must be set up as an employee on Siebel. This is the Siebel user whose user ID is entered when the Database Configuration Utility prompts for Database User Name.

The user name (user ID) of the target database must have authorization to set CURRENT SQLID and must have Siebel administrator responsibility. SADMIN is the default administrator user name. If this user does not already exist in your database, or does not have Siebel administrator privileges, then you must add this user to your database before starting the upgrade. For further information on adding Siebel users, see Siebel Security Guide.

6Basic Database Preparations for a Siebel Upgrade