Tuning the Siebel Upgrade Files

This chapter provides guidelines for preparing your Siebel Business Applications data for the Siebel database upgrade. This chapter includes the following topics:

Starting and Stopping Siebel Upgrade Tuner

Environments: Production test environment only. Does not apply to production environment.

Platforms: Windows and UNIX only.

Databases: All databases.

This topic describes how to start the Upgrade Tuner (upgtuner) from the Siebel Server installation directory on a Windows host. It also describe how to save or discard your changes when exiting Upgrade Tuner.

Use the Upgrade Tuner to improve the performance of table creation, index creation, and SQL execution during the production upgrep step in the production test environment.

Requirements

In the production test environment, you must have completed the production upgrep step.
You must have run the Logparse utility on the production upgrep log files. See Summarizing Siebel Log Files Using the Logparse Utility.
For UNIX platforms, you must have transferred files to a Windows host on which a Siebel Server is installed. See Transferring UNIX Files for Use by Siebel Upgrade Tuner.

Starting Upgrade Tuner

Use this procedure to start Upgrade Tuner.

To start Upgrade Tuner

Enter the following command:
```
SIEBEL_ROOT\bin\upgtuner /c LOGPARSE_SUMMARY_LOCATION
```
LOGPARSE_SUMMARY_LOCATION is the location of the summary.xml file generated by the Logparse utility for the production upgrep. For example:
```
upgtuner /c SIEBEL_ROOT\log\upgrep_prod_77\summary.xml
```
The Upgrade Tuner Process Information page appears.
Alternative: Start Upgrade Tuner without the /c option. Click OK at the error pop-up. When Upgrade Tuner displays, click Browse and navigate to the summary.xml file.

Saving Your Changes and Exiting Upgrade Tuner

Use this procedure to save your changes and exit Upgrade Tuner.

To save your changes and exit Upgrade Tuner

Evaluate the changes you have made and revise them as needed.
Upgrade Tuner does not revise the upgrade files until you exit.
Click Save and then Exit.
Click Yes in the pop-up window that asks you to confirm if you want to save and exit.
Upgrade Tuner applies your changes to the upgrade files and exits.

Discarding Your Session Changes and Exiting Upgrade Tuner

Use this procedure to discard the changes you have made in the current session and exit Upgrade Tuner. No changes are made to upgrade files.

Parallelize Table or Index Creation Pages

Changes are discarded and do not display the next time you start Upgrade Tuner, if:

You moved tables or indexes between threads, these changes are discarded.
You created new threads, the threads are discarded.

Deactivate 0-Row SQLs

Changes are discarded and do not display the next time you start Upgrade Tuner, if:

You deactivated a command, this change is discarded. The next time you start Upgrade Tuner, no check mark displays in the Inactive column. The command remains active in the SQL file.
You activated a command, this change is discarded. The next time you start Upgrade Tuner, a check mark displays in the Inactive column, and the command remains inactive in the SQL file.

To discard your changes and exit Upgrade Tuner

Click Cancel.
Click Yes in the pop-up window that asks if you to confirm you want to discard your changes and exit.

Managing Parallel Threads Using Siebel Upgrade Tuner

Environments: Production test environment only. Does not apply to production environment.

Platforms: Windows and UNIX only.

Databases: Oracle only.

Upgrade Tuner allows you to create, edit, and delete parallel thread for table and index creation. This improves upgrade performance by reducing the amount of time required to complete table and index creation.

You create, edit, and delete threads in the Parallelize Table Creation page and the Parallelize Index Creation page. These two pages have the same layout.

Requirements

In the production test environment, you must have completed the production upgrep step.
You must have run the Logparse utility on the production upgrep log files. See Summarizing Siebel Log Files Using the Logparse Utility.
For UNIX platforms, you must have transferred files to a Windows host on which a Siebel Server is installed. See Transferring UNIX Files for Use by Siebel Upgrade Tuner.
Start Upgrade Tuner. See Starting and Stopping Siebel Upgrade Tuner.

Displaying Threads

You can view and sort the contents of threads or view items sorted across threads:

Default sort. The default sort is all the items in the Serial Thread sorted from highest to lowest cost. The default sort displays when you start Upgrade Tuner, add a thread, or remove a thread. To reverse the sort order, click the Serial Thread column head.
Contents of a thread. Click the column head for that thread. To reverse the sort-order, click the column head again.
All items in all threads sorted by cost. Click the Cost per Table column head. To reverse the sort-order, click the column head again.

Creating Parallel Threads

Use this procedure to create a parallel thread for table creation or index creation. Upgrade Tuner automatically names threads Parallel Thread 1, Parallel Thread 2, and so on. You cannot edit thread names.

You start creating parallel threads by creating Parallel Thread 1 and Parallel Thread 2 together. You must assign at least one table or index to each of these threads.

All threads you create must contain at least one table or index.

Creating Parallel Thread 1 and Parallel Thread 2

Use the following procedure to create Parallel Thread 1 and Parallel Thread 2.

To create Parallel Thread 1 and Parallel Thread 2

In the Serial Thread, select a table or index, and move it to the right using the forward arrow key on the keyboard.
Upgrade Tuner creates Parallel Thread 1 and Parallel Thread 2. It then assigns the table or index to Parallel Thread 1.
Move at least one table or index to Parallel Thread 2, to populate both threads.

Creating Additional Parallel Threads

Use the following procedure to create additional parallel threads.

To create additional parallel threads

Select a table or index and move it to the right using the forward arrow key on the keyboard.
When you move the table or index to the highest-numbered thread and click the arrow key again, Upgrade Tuner creates a new thread and places the table or index in the new thread.

Tip: Another way to create a new thread is to right-click in a row. In the drop-down menu, select the last thread listed.

Moving Items Between Threads

Use the arrow keys (left/back and right/forward) to move tables or indexes between threads, including the Serial Thread.

Note: You cannot save and exit if any thread is empty.

Deleting a Thread

Use this procedure to delete an existing thread. You cannot delete the Serial Thread.

You must delete Parallel Thread 1 and Parallel Thread 2 together. You must delete all other threads before deleting Parallel Thread 1 and Parallel Thread 2.

Deleting a Thread Other Than Parallel Thread 1 or Parallel Thread 2

Use the following procedure to delete a thread other than Parallel Thread 1 or Parallel Thread 2.

To delete a thread other than Parallel Thread 1 or Parallel Thread 2

Click the column head for the desired thread.
This action sorts the list so that all items in that thread appear at the start of the list.
Right-click in the column head, and choose Move all items to the serial thread from the pop-up menu.
Right-click in the column head, and choose Remove thread from the pop-up menu.
Upgrade Tuner deletes the thread and renames all higher-numbered threads.

Deleting Parallel Thread 1 and Parallel Thread 2

Use the following procedure to delete Parallel Thread 1 and Parallel Thread 2.

To delete Parallel Thread 1 and Parallel Thread 2

Right-click the column head for Parallel Thread 2, and choose Move all items to the serial thread from the pop-up menu.
Right-click the column head for Parallel Thread 1, and choose Move all items to the serial thread from the pop-up menu.
Right-click in the column head for Parallel Thread 2, and choose Remove thread from the pop-up menu.
Upgrade Tuner deletes Parallel Thread 1 and Parallel Thread 2.

Evaluating Upgrade Performance Improvement

To evaluate production upgrep performance improvement, use the two fields at the start of the page:

Total cost of sequential table (or index) creation. Displays the time to create tables or indexes when no parallel threads are used.
Total cost of parallelized table (or index) creation. Displays the time to complete the upgrade using the parallel threads you have created. The time is computed by adding the Serial Thread time and the longest-running parallel thread time.

The difference between the sequential creation time and the parallelized creation time is an estimate of the reduction in upgrade time from using parallel threads.

You can reduce upgrade time further by performing the following actions:

Move additional items from the Serial Thread to a parallel thread
Move items from the longest-running parallel thread to other threads or a new thread

The goal is to reduce both the Serial Thread time and longest-running parallel thread time to a minimum. Because each new parallel thread requires additional memory and CPU cycles, you might need to experiment with the number of parallel threads to optimize upgrade performance.

Managing Zero-Row SQL Commands Using Siebel Upgrade Tuner

Environments: Production test environment only. Does not apply to production environment.

Platforms: Windows and UNIX only.

Databases: All databases.

The upgrade scripts support all the tables in the Siebel data model. This support means the tables might contain SQL commands that run against tables that are not included in your Siebel database, that are empty, or do not contain data that applies to a specific SQL command. By inactivating such SQL commands, you can reduce the time required to perform the production upgrep.

The Deactivate 0-Row SQLs page displays a list of SQL files that contain commands that returned zero rows. This means the command does not affect any data and does not change the database schema. The screen displays only upgrade commands executed natively by the RDBMS. The screen does not display SQL commands executed using odbcsql.

The SQL files are located in DBSRVR_ROOT\DBPLATFORM\upgrade\VERSION, for example, db2\upgrade\V8_x.

When you select a file, the command that returned zero rows displays in the lower half of the screen. You can then either deactivate or activate the command. You cannot edit the displayed command.

When you deactivate a command and save your changes, Upgrade Tuner opens the SQL file containing the command and inserts (Execute=No) in the command. When you activate a command, Upgrade Tuner removes (Execute=No) from the command.

Requirements

In the production test environment, you must have completed the production upgrep step.
You must have run the Logparse utility on the production upgrep log files. See Summarizing Siebel Log Files Using the Logparse Utility
For UNIX platforms, you must have transferred files to a Windows host on which a Siebel Server is installed. See Transferring UNIX Files for Use by Siebel Upgrade Tuner
Analyze your customizations and the nature of application data. Verify that you understand the role of any new tables you have added.
Start Upgrade Tuner. See Starting and Stopping Siebel Upgrade Tuner

Displaying Zero-Row SQLs

You can view and sort zero-row SQLs in several ways:

Default sort. The default sort order is the order in which the zero-row commands appear in the driver files. Any inactivated SQL commands, including those inactivated in previous sessions appear at the end of the list. The default sort order displays when you start Upgrade Tuner.
Display items sorted by cost. To sort commands from longest-running time to shortest, click the Net Cost column head. To reverse the sort order, click Net Cost again. Commands inactivated prior to this session appear at the end of the list.
Display commands activated or deactivated in the current session. Click the Inactive column head. Items display at the beginning of the list. The word Changed displays in the Inactive column for these items. Items that have been deactivated display check marks. Items that have been activated do not.
Display commands inactivated in previous sessions. Click the Net Cost column head and scroll to the end of the list. Inactivated commands do not have a check mark in the Inactive column and do not display the word Changed.
Display commands activated in previous sessions. The display of SQL commands does not provide a way to identify commands activated in a previous Upgrade Tuner session. When you activate a command, write down its SQL file name and SQL tag number so you can locate the command in future sessions.
Display all the zero-row SQL commands in a file. Click the SQL File column head. This action sorts the file names alphabetically. To reverse the sort order, click the column head again.

Deactivating Zero-Row SQL Commands

Use this procedure to deactivate SQL commands that do not affect any data.

To deactivate zero-row SQL commands

Click the Deactivate 0-Row SQLs tab in Upgrade Tuner.
The Deactivate 0-Row SQLs screen appears.
Click the Net Cost column head.
This sorts the entries so that the longest running SQL commands appear first. If they do not, click the column head again. Commands deactivated in previous Upgrade Tuner sessions display at the end of the list.
Click in a row to display a command that returned zero rows.
Carefully evaluate whether you need this command for your upgrade.
Write down the net cost of the command.
You can use a spreadsheet to keep track of net cost changes, if you prefer.
To deactivate the command, click in the check box in the Inactive column.
The following occurs:
- A check mark displays indicating the command is inactive.
- The word Changed appears next to the check mark to indicate the change was made in this session.
- The time displayed in the Net Cost column changes to Not applicable.
- When you save and exit, Upgrade Tuner inactivates the command in the SQL file.
- The next time you start Upgrade Tuner, a check mark displays in the Inactive column for the command, but the word Changed does not.

Activating Zero-Row SQL Commands

Use this procedure to activate SQL statements that do not affect any data.

To activate zero-row SQL commands

Click the Net Cost column head, and then scroll to the end of the list.
This sorts commands by running time. Inactive commands have a running time of Not applicable and always appear at the end of the list.
Click in a row to display a command that returned zero rows.
Carefully evaluate whether you need this command for your upgrade.
To activate the command, click in the check box in the Inactive column.
The following occurs:
- The check mark disappears from the check box, indicating the command is active.
- The word Changed appears next to the check box to indicate the change was made in this session.
- The time displayed in the Net Cost column remains Not applicable.
- When you save and exit, Upgrade Tuner activates the command in the SQL file.
- The next time you start Upgrade Tuner, Not applicable is replaced by the running time for the command, and the word Changed does not appear.
Write down the SQL file name and SQL tag number for the command.
The next time you run Upgrade Tuner, locate the command and write down its net cost.
You can use a spreadsheet to keep track of net cost changes, if you prefer.

Evaluating Upgrade Performance Improvement

To evaluate production upgrep performance improvement, add together the net cost of all the zero-row SQLs you deactivated. Then subtract the net cost of the zero-row SQLs you activated.

The final sum is an estimate of how much you have reduced the time required for the next production upgrep.

Transferring UNIX Files for Use by Siebel Upgrade Tuner

Environments: Production test environment only. Does not apply to production environment.

Platforms: UNIX only.

Upgrade Tuner is part of the Siebel Server and runs only on Windows. To tune production upgrade files on a UNIX computer you must do the following:

Transfer upgrade files needed by Upgrade Tuner from the UNIX host to a Windows host on which a Siebel Server is installed.
Run Upgrade Tuner on the Windows host using the UNIX files as input.
Transfer the modified upgrade files from the Windows host back to the UNIX host.

Scripts generated by the Logparse utility during the production upgrep on the UNIX host simplify the file transfer process:

upgtuner_ftp_get.txt. This script moves upgrade files from a UNIX host to a target directory on the Windows host.
upgtuner_ftp_put.txt. This script moves the upgrade files from the Windows host to a target directory on the UNIX host.

Requirements for the UNIX Host

In the production test environment, you must have completed the production upgrep step.
You must have run the Logparse utility on the production upgrep log files. See Summarizing Siebel Log Files Using the Logparse Utility.

Requirements for the Windows Host

You must have installed a Siebel Server. You do not have to install the Siebel Database Server.
The Windows host can be a Siebel Server on which have performed upgrades. Tuning upgrade files from a UNIX host does not interfere with upgrade files already on the Windows-based Siebel Server.
To run the upgtuner_ftp_put.txt script, you must be able to FTP from the Windows host to the UNIX host.

The following procedures use FTP to transfer files. If FTP is not available, you can use other methods for transferring files.

Transferring Files from the UNIX Host to the Windows Host

To run Upgrade Tuner on UNIX upgrade files, you must first transfer the files to a Windows host.

To transfer files from the UNIX host to the Windows host

Windows host. Create a target directory for the UNIX upgrade files, and share the directory.
UNIX host. Copy the following scripts using FTP to the Windows computer target directory:
- upgtuner_ftp_get.txt
- upgtuner_ftp_put.txt
  The files are located in $SIEBEL_ROOT/bin.

Windows host. In both scripts, replace placeholder parameters with actual values, as described in the following table.

Placeholder	Value
&HostIP	This value is the IP address of UNIX computer.
&Username	This value is the user name used to open an FTP session with the UNIX computer (for example sadmin).
&WindowsTempDir	This value is the full path of the target directory on the Windows computer. The target directory does not have to be within the Siebel Server installation. Avoid using a target directory that already contains upgrade files.

Windows host. Use FTP and upgtuner_ftp_get.txt to move the files shown in the following table from the UNIX host to the target directory on the Windows host.

File	Location on the UNIX Host
summary.xml	$SIEBEL_ROOT/log/upgrep_prod_VERSION For example, `$SIEBEL_ROOT/log/upgrep_prod_8x/summary.xml`.
master_upgrep_prod_ VERSION.ucf	$SIEBEL_ROOT/bin For example,`$SIEBEL_ROOT/bin/master_upgrep_prod_8x.ucf`.
schema*.ddl	DBSRVR_ROOT/DBPLATFORM For example, `DBSRVR_ROOT/Oracle/schema.ddl, schema_t1.ddl, schema_t2.ddl`.
driver_upgrep_prod_ VERSION.ucf	DBSRVR_ROOT/DBPLATFORM/upgrade/VERSION For upgrades from Siebel CRM version 8.1.1.10 on Oracle Database, an example would be `DBSRVR_ROOT/Oracle/upgrade/v8_1_1_10/driver_upgrep_prod_v81110.ucf`.
*.sql	DBSRVR_ROOT/DBPLATFORM/upgrade/VERSION For example, for upgrades from Siebel CRM version 8.1.1.10 on Oracle Database, an example would be `DBSRVR_ROOT/Oracle/upgrade/v8_1_1_10/preschm.sql`.

Windows host. Navigate to the target directory containing the UNIX upgrade files, and open the summary.xml file in a text editor.
Windows host. Near the beginning of the file, locate the element <SIEBEL_ROOT>, and edit the value to be the absolute path to the target directory containing the UNIX files that you copied to the Windows host.
Windows host. Save the file, and exit.
Windows host. Start Upgrade Tuner, and tune the UNIX upgrade files.
Specify the target directory containing the UNIX upgrade files. The summary.xml file contains a flag that tells Upgrade Tuner to look for all the upgrade files in the target directory. You do not have to move the files.

Transferring Files from the Windows Host to the UNIX Host

After you have tuned the UNIX upgrade files, transfer them back to the UNIX host.

To transfer files from the Windows host to the UNIX host

UNIX host. Create a target directory for the UNIX upgrade files that will be transferred from the Windows host.
Alternative: Use the FTP upload directory for the UNIX host.
Windows host. Use FTP to copy the UNIX upgrade files from the target directory to the UNIX host.
UNIX host. Move the upgrade files to their proper locations.
The path for <SIEBEL_ROOT> in the summary.xml file is used for the Windows host and thus is incorrect for the UNIX host. The next time you run Logparse, it will overwrite summary.xml and include the path for SIEBEL_ROOT on the UNIX host computer.

Rolling Back Siebel Upgrade Tuner Changes

Environments: Production test environment only. Does not apply to production environment.

Platforms: Windows and UNIX only.

Databases: All databases.

Use these procedures to discard the changes you saved from the most recent Upgrade Tuner session. You do this by rolling back the upgrade files to a previous Upgrade Tuner session.

This roll-back process is particularly useful for deployments on UNIX. You can roll back upgrade files to a previous version on the UNIX host. You do not have to transfer the files to a Windows host and rerun Upgrade Tuner.

Upgrade File Versions

Before Upgrade Tuner saves changes to the upgrade files, it does the following:

If this your first Upgrade Tuner session, Upgrade Tuner saves the current driver and SQL files to .orig, for example driver_upgrep_prod_8x.ucf.orig.
If this is the second session or all later sessions, Upgrade Tuner saves the current driver and SQL files to .old, for example driver_upgrep_prod_8x.ucf.old.

To roll back, you replace the upgrade file with the .old or .orig version.

Guidelines for Rolling Back Upgrade Files

Use the following guidelines to roll back upgrade files:

To roll back to the previous Upgrade Tuner session, replace the driver or SQL file with the .old version.
To roll back to the original version of the file, before any Upgrade Tuner modifications, replace the driver or SQL file with the .orig version.
You can roll back the driver and SQL files separately. For example, you can roll-back to the original driver file while retaining the most recent changes to SQL files.
You do not have to roll back all SQL files at once. For example, you can roll back some SQL files while retaining the most current version of others.
You do not have to roll back all the commands in an SQL file. You can edit the file and activate or deactivate as many commands as desired.
Manually editing the driver_upgrep_prod_VERSION.ucf file is not recommended.
If you roll back to a session with fewer threads, then you do not have to delete any schema_t#.ddl or schema_i#.ddl thread-files. The deletion is unnecessary because Upgrade Tuner removes from the driver file the steps that execute the deleted threads.

The following procedures use driver_upgrep_prod_8x.ucf and pret.sql as examples.

Rolling Back to the Previous Session

Use this procedure to discard your most-recent session and roll back to the previous session.

To roll back to the previous session

Save a copy of driver_upgrep_prod_8x and pret.sql to new names.
Copy driver_upgrep_prod_8x.old to driver_upgrep_prod_8x.
Copy pret.sql.old to pret.sql.
Restart Upgrade Tuner.

Rolling Back to the Original Upgrade Files

Use this procedure to discard all Upgrade Tuner changes and roll back to the original upgrade files.

To roll back to the original upgrade files

Save a copy of driver_upgrep_prod_8x and pret.sql.
Copy driver_upgrep_prod_8x.orig to driver_upgrep_prod_8x.
Copy pret.sql.org to pret.sql.

Activating or Deactivating SQL Commands Manually

Use this procedure to activate or deactivate individual zero-row SQL commands by editing the SQL file.

To activate or deactivate a command by editing an SQL file

Save a copy of the SQL file to a new name.
Open the .sql file (not the copy) and locate the desired SQL command.
Commands begin Run_SQL_#, for example Run_SQL_100.
Edit the command as follows:
- To activate the command, delete the element (Execute=N)
- To deactivate a command, add the element (Execute=N)
  Insert the element on a line by itself after Run_SQL_# =.
Save the file.