1. Database Upgrade Guide for DB2 for z/OS
  2. Example of a Siebel Development Environment Upgrade Flow

Example of a Siebel Development Environment Upgrade Flow

This topic presents the flow of steps in part of a typical development environment upgrade. The steps are extracted from an actual driver file. To perform an upgrade, the Upgrade Wizard reads the steps in a driver file and performs the commands the steps contain. The driver file type used in this example is as follows:

  • Upgrade: Siebel 15.0 to Siebel 17.0

  • Environment: Development

  • Upgrade mode: upgrep

  • Multilingual: No

Upgrading the Siebel Database Schema (upgrep) is a two-step process, where you select each of the following upgrade processes in turn:

  1. zSeries Staging of Files for Upgrade: master_upgrep_dev_150_mf_m.ucf

  2. zSeries Seed/Repository Upgrade: master_upgrep_dev_150_mf.ucf

Example Steps in a Development Environment Upgrep Staging of Files

The following table shows the steps in the driver file for zSeries Staging of Files for Upgrade.

Step

Script or Input File

Description

1. Generate the DDL file for the staging database.

strgupgd

Generates the DDL file for the staging database.

2. Copy files into the output directory.

strgupgd

Runs the extract and merge process to create the storage control file.

The database administrator must perform operations using the files in the $GenDDLDirectory directory.

Once the operations are completed, resume the Upgrade Wizard operations by submitting the following command line on the midtier computer: $GenDDLDirectory\upg_restart.

3. Restart the midtier file generation process.

strgupgd

Restarts the midtier file generation process for the upgrade.

If all necessary mainframe tasks completed successfully, click YES to continue, otherwise click NO to terminate this process.

If you choose NO, you can resume Upgrade Wizard operations by submitting the following command line on the midtier computer: $GenDDLDirectory\upg_restart.

4. Generate the DDL for the additive schema changes.

Ddlimp2

For the DB2390 staging database, run the ddlimp file to generate the schema.additive.sql file.

5. Copy files into the output directory.

strgupgd

Generates a set of files in the $GenDDLDirectory directory.

The database administrator must transfer these files from the $GenDDLDirectory directory to the mainframe using the ftp_pause1.txt and perform all required tasks on the DB2 host commands.

Once completed, you can resume Upgrade Wizard operations by submitting the following command line on the midtier computer: $GenDDLDirectory\upg_restart.

6. Restart the midtier file generation process.

strgupgd

Restarts the midtier file generation process for the upgrade.

If all necessary mainframe tasks completed successfully, click YES to continue, otherwise click NO to terminate this process.

If you choose NO, you can resume Upgrade Wizard operations by submitting the following command line on the midtier computer: $GenDDLDirectory\upg_restart.

7. Generate the DDL for temporary tables.

ddlimp

Creates the temporary tables for the z/OS upgrade.

8. Generate the DDL for Siebel tables.

ddlimp

Creates the schema for the z/OS upgrade.

9. Copy files into the output directory.

strgupgd

Generates a set of files in the $GenDDLDirectory directory.

The database administrator must transfer these files from the $GenDDLDirectory directory to the mainframe using the ftp_pause2.txt and perform all required tasks on the DB2 host commands.

Once completed, you can resume Upgrade Wizard operations by submitting the following command line on the midtier computer:

$GenDDLDirectory\upg_restart

10. Restart the midtier file generation process.

strgupgd

Restarts the midtier file generation process for the upgrade.

If all necessary mainframe tasks completed successfully, click YES to continue, otherwise click NO to terminate this process.

If you choose NO, you can resume Upgrade Wizard operations by submitting the following command line on the midtier computer: $GenDDLDirectory\upg_restart.

11. Generate the DDL for Siebel indexes.

ddlimp

Creates the indexes for DB390 development upgrade.

12. Copy files into the output directory.

strgupgd

Generates a set of files in the $GenDDLDirectory directory.

The database administrator must transfer these files from the $GenDDLDirectory directory to the mainframe using the ftp_pause3.txt and perform all required tasks on the DB2 host commands.

Example Steps in a Development Environment Upgrep Seed Repository

The following table shows the steps in the driver file. The Script or Input File column in the table lists the SQL file or input file that is executed in each step. The Comment column provides a brief explanation of what the SQL file or input file does.

Step

Script or Input File

Comment

Verify repository name

rename_existing_repositories.sql

Renames Siebel Repository to Prior Customer Repository.

Preparation of prior customer repository

SWTClob.jar

RepCrawler.jar

The SWT to OD Conversion Utility converts Siebel Web Templates to an Object Definition Layout. Converted Web templates are stored in the database.

The Repository Sanitization Utility searches the entire repository for unreferenced repository objects. Unreferenced repository objects are deactivated if they are not used by any application.

Import Siebel seed data

dataimp utility

seedupg0.inp as input

seedupg1.inp as input

seedupg_locale.inp as input

Prior to importing seed data, dataimp deletes existing seed data.

The seedupg* files contain filters that dataimp uses to prevent deleting seed data that you have modified or seed data meeting specified criteria.

Unmodified seed data has a last update date (LAST_UPD) of 1980-01-01. Dataimp does not delete records where LAST_UPD is later than this date.

Upgrade data after seed data import

upg_data_afterseed.sql

For customers who have not converted to UTC time, sets the UTC value in S_SYS_PREF to False. For customers who have converted to UTC time, the script takes no action.

Upgrade data after seed data import SIA

upg_data_afterseed_sia.sql

None.

Set system preference for codepage for DB

set_codepage.sql

Sets the database codepage in the S_SYS_PREF.

Update version component information

upd_upgcomp.sql

Updates the S_UPG_COMP table with the product release level. The S_UPG_COMP table stores version information for application executable programs.

Encryption Upgrade

EncryptionUpgrade.jar

Siebel Business Applications allow customers to encrypt sensitive information stored in the Siebel database, for example, credit card numbers, Social Security numbers, birth dates. This information cannot be viewed without access to Siebel Business Applications.

Sensitive data can be encrypted by using AES (Advanced Encryption Standard). This utility identifies the RC2 encrypted columns and upgrades their data to the AES.

This utility updates the logical layer of data for columns which are candidates for encryption.

Export prior customer repository

repimexp utility

Exports the existing prior customer repository to create the new customer repository.

Import Prior Siebel, New Siebel, and New Customer repositories

repimexp utility

Imports the prior Siebel repository, new Siebel repository, and the new customer repository in parallel to the repository tables.

Install SQL packages

seeduver.sql

Verifies that versions are set correctly in S_APP_VER.

Install SQL packages

ifstrg.sql

Sets storage parameters for Siebel Enterprise Integration Manager tables.

Install SQL packages

ifindxstrg.sql

Sets storage parameters for Siebel Enterprise Integration Manager table indexes.

Install SQL packages

pkgseq.sql

Adds a suffix to row IDs in the S_SEQUENCE table. Ensures that row IDs are unique.

Install SQL packages

pkgldel.sql

Defines s_txn_log_del_proc. Procedure periodically deletes transactions from S_DOCK_TXN_LOG. Also deletes rows from S_DOCK_TXN SET. Prevents need for large rollback segment.

Install SQL packages

trgreset.sql

Ensures that denormalized rows in S_TERR have correct values.

Install SQL packages

ddlseq.sql

Sets sequence numbers for specified tables.

Install SQL packages

pkgvis.sql

Creates function that modifies how Oracle optimizer does visibility check.

Fix column alignment for custom objects

AlignApplet.jar

Applet alignments are executed based on the data type of the field. Alignments, which can be one of the following, are executed across the entire Repository for a similar look and feel of fields:

  • Consistent Left

  • Right

  • Center

Create temporary indexes for merge performance

crt_temp_indexes_merge.sql

Creates temporary indexes to improve the merge performance.

Take backup of DB and update DB stats

None

Pauses the Siebel Upgrade Wizard to enable a database backup and the execution of database statistics before the merge.

Execute Incremental Repository Merge

siebdev

Starts the incremental repository merge automatically for Windows. If you are using a Windows computer and Siebel Tools is installed for Siebel Enterprise servers in UNIX environment, then you must perform this step manually.

Generate Merge Report

MergeReport.jar

Generates the hierarchical merge report in HTML format. Also creates the file IRM_Merge*_ERROR.txt in $SIEBEL_HOME\log, which contains only the errors that are present in the IRM_Merge*.txt file.

Export merge data to CSV

ExportMrgCSV.jar

Exports repository-merge log data to a CSV file for business object, business component, applet, view, and Web page object definitions. This data can be compared with the Usage Pattern Tracking (UPT) CSV files to obtain the intersection data, so that you can focus testing on these objects only.

The Siebel Upgrade Wizard exports the log data to CSV files in the following folder: upgrep_log_directory/output/export_csv/csv.

Record Upgrade History

store_history.sql

Stores the upgrade history in the S_INST_UPG_HIST table.

Steps That the upgphys Process Executes

The following table shows the different steps executed by the upgphys process. The Script or Input File column in the table lists the SQL file or input file that is executed in each step. The Description column provides a brief explanation of what the SQL file or input file does.

Step

Script or Input File

Description

1. Check whether the merge and conflict resolution steps are complete

verify_irm.sql

Verifies whether the merge and conflict resolution steps are complete.

2. Drop temporary indexes created for merge performance

drop_temp_indexes_merge.sql

Removes all the temporary indexes created to improve the merge performance.

3. Export schema definition

None

Exports the schema definition to the file DBSRVR_ROOT/platform/schema.ddl. This file is used as an input to the production test and production environments.

4. Generate files to synchronize the physical and logical schema

schema.ddl

Applies the schema.ddl and synchronizes the tables and indexes.

5. Migrate query search controls of list applets into the Siebel database

SrchCntrlMigration.jar

This step migrates query search controls of list applets from files into the Siebel database.

This step also exports the entire New Customer Repository to: $DbsrvrRoot/$DatabasePlatform/custrep.dat.

6. Enable Workspace

EnableWorkspace.exe

Enables the Workspace on the New Customer Repository for repository content.

7. Encryption Upgrade - Physical

EncryptionUpgrade.jar

This step updates the physical layer data for columns which are encrypted with the RC2 algorithm. This updates the data to AES algorithm.

8. Export repository to a file

None

Exports the New Customer Repository to:$DbsrvrRoot/$DatabasePlatform/custrep.dat.

This step exports the Design Time Repository (DR) to the custrep_dev.dat file and Runtime Repository (RR) to the custrep.dat file.

The custrep.dat file contains only the required repository data and is used as an input to the production test and production environments.

9. Import New Customer Repository as Siebel Repository

custrep_dev.dat

Truncates the repository tables and imports the previously exported New Customer Repository as Siebel Repository.

This step imports the Design Time Repository (DR) from the custrep_dev.dat file.

10. Enable Workspace for Seed data

EnableWorkspace.exe

Enables the Workspace on the New Customer Repository for Seed data (List of Values).

11. Copy files to the output directory.

None

Copies files to the output directory.

12. Verify repository after upgrade

dbchck

dictutl

mlovupgd

SeedConflict.jar

The repository is optionally verified after you complete the upgphys process. This step is executed if you selected the option Verify Repository After Upgrade in the Database Configuration Wizard.

13. Upgrade history

store_history.sql

Stores the upgrade history in the S_INST_UPG_HIST table.