Upgrade Guide > Tuning the Siebel Upgrade Files >

Managing Zero-Row SQL Commands Using Siebel Upgrade Tuner

Upgrades: All Siebel upgrades.

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

Platforms: Windows and UNIX only.

Databases: All databases.

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

The upgrade scripts support all the tables in the Siebel data model. This support means the tables may 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, db2udb\upgrade\V7_7\.

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.

Related Topics

About Tuning Siebel Production Upgrade Files

Prerequisites

• 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 Logparse.
• 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 on the SQL File column head. This action sorts the file names alphabetically. To reverse the sort order, click on 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

1. Click the Deactivate 0-Row SQLs tab in Upgrade Tuner.

   The Deactivate 0-Row SQLs screen appears.

2. 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.

3. Click in a row to display a command that returned zero rows.
4. Carefully evaluate whether you need this command for your upgrade.
5. Write down the net cost of the command.

   You can use a spreadsheet to keep track of net cost changes, if you prefer.

6. 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 N/A.
   • 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

1. 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 N/A and always appear at the end of the list.

2. Click in a row to display a command that returned zero rows.
3. Carefully evaluate whether you need this command for your upgrade.
4. 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 N/A.
   • When you save and exit, Upgrade Tuner activates the command in the SQL file.
   • The next time you start Upgrade Tuner, N/A is replaced by the running time for the command, and the word Changed does not appear.
5. Write down the SQL file name and SQL tag number for the command.
6. 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.