9Customizing a Development Environment
Customizing a Development Environment
This chapter describes procedures for customizing development environments and migrating those customizations to user-acceptance or production environments on DB2 for z/OS. This chapter includes the following topics:
About Customizing Your Development Environment
Customization of Siebel Business Applications within your development environment can involve:
Converting nonpartitioned tables to partitioned tables
Changing views, business objects, applets, and tables
Adding new columns to existing tables and adding new tables
Modifying workflow policies and workflow processes
These tasks are documented in Using Siebel Tools and Siebel Business Process Framework: Workflow Guide. However, several customization procedures and issues are specific to DB2 for z/OS; these are described in this chapter.
It is assumed that you perform your development on either of the following:
DB2 on a Windows or UNIX computer
DB2 for z/OS on a partition of your z/OS computer reserved for development
In many cases, changes to user-acceptance and production (server) databases are made by a database administrator working within a change-management system. Therefore, this guide includes procedures for generating Data Definition Language (DDL) files that are later applied to databases.
About Using Siebel Tools in a DB2 for z/OS Environment
There are certain considerations and procedures to follow in Siebel Tools when developing applications that run against databases on DB2 for z/OS. These include:
Siebel Tools Configuration File Parameters
The following parameters in the [ServerDataSrc] section of the Siebel Tools application configuration file (tools.cfg) must be set for DB2 for z/OS, as shown in the following table.
Table Siebel Tools Configuration File Parameters
| Configuration File Parameter | Value |
|---|---|
TableOwner |
Schema qualifier |
MaxCursorSize |
-1 |
PrefetchSize |
-1 |
Setting Database Options
When developing applications on any platform (for example, DB2 for Windows) that will eventually be deployed on DB2 for z/OS, you must set the appropriate database options in Siebel Tools. Setting these options enables all Siebel Tools features for DB2 for z/OS and validates Siebel objects for the z/OS operating system:
CHAR columns with a length greater than 1 are allowed.
Users can eliminate unused indexes.
Users can reduce the size of VARCHAR fields.
To set database options for DB2 for z/OS
Start Siebel Tools.
From the Tools menu, choose View, and then the Options menu item.
The Development Tools Options dialog appears.
On the Database tab, select the Developing for deployment on DB2 for zSeries check box.
Storage Control File Names
When working in Siebel Tools, you must specify the complete paths for storage control file names in the Apply dialog box when applying schema changes to a database. (In previous releases, the storage control file was referred to as the table groupings file.)
About Inactivating Unused Indexes
The standard Siebel data model has a large number of indexes that can degrade performance on DB2 for z/OS. This performance degradation can occur because DB2, unlike Oracle and Microsoft SQL Server, stores NULL as one of the indexed values. Each row with a NULL value for an indexed column gets an entry of its record identifier (RID) in the DB2 index.
Because many Siebel tables are related to a large number of other tables, there are many indexes on foreign keys. Where these related tables are not used, DB2 assigns the indexes for the foreign keys one entry: NULL. This key entry has a long RID chain comprised of all the RIDS for every row in the table. There is maintenance overhead each time a row is inserted and even more overhead when it is deleted. Therefore, users of DB2 for z/OS might want to deactivate unused indexes. Do not deactivate unused indexes in Siebel Tools. To deactivate indexes, contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance from Oracle’s Application Expert Services.
About Reducing VARCHAR Field Lengths
DB2 for z/OS pads VARCHAR fields to their maximum lengths in indexes. To increase flexibility, Siebel Business Applications make extensive use of VARCHAR columns. This use of VARCHAR columns can cause performance degradation for z/OS customers who have a large number of rows in certain tables, such as S_CONTACT.
The total length of an index equals the combined lengths of all index columns. To reduce the index length, you reduce the length of participating columns. To make better use of index space, users of DB2 for z/OS can reduce VARCHAR lengths, in cases where there is no application impact, using Siebel Tools.
Analyze your business needs at installation or upgrade to determine whether it is necessary to reduce VARCHAR field lengths. Some examples are the following:
FST_NAME and LAST_NAME, VARCHAR(50) columns in S_CONTACT. These belong to many indexes.
ADDR, a VARCHAR(200) column in S_ADDR_PER.
NAME, a VARCHAR(100) column in S_ORG_EXT.
Be aware that you also must make the same length reductions to columns denormalized from those you have reduced. One example is S_PER_RESP.PER_FST_NAME, which is denormalized from S_CONTACT.FST_NAME.
A denormalized column duplicates the data in a column in another (base) table for performance reasons. The table and column names of the duplicated column are specified in the Denormalization Path property of the Column object definition of the denormalized column. For more information on columns, see Using Siebel Tools.
About Siebel LONG Columns on DB2 for z/OS
This topic describes how LONG columns in the Siebel Schema are implemented on the DB2 for z/OS platform. Siebel Business Applications use LONG columns on RDBMS tables to store arbitrarily long character data up to 16,350 characters. Examples include scripts, emails, notes, and descriptions. On all RDBMS platforms, except DB2 for z/OS, the size of LONG columns is fixed and is independent of the size of the table space in which the table is defined.
On the z/OS platform, a LONG column in the logical Siebel Schema is indicated as a LONG VARCHAR column in the Siebel Repository. As of DB2 Version 9.1 for z/OS, LONG VARCHAR columns cannot be created with the LONG attribute in the DB2 catalog. Instead DB2 for z/OS creates these columns as VARCHAR columns that are set to the maximum size possible. The size of these VARCHAR columns varies according to two factors:
The buffer pool and page size of the table space in which the table is defined.
The combined byte size of all other columns in the table.
You can estimate the size of the LONG column by calculating the buffer pool size of the table space minus the size of all other columns; the LONG column takes up the remainder of the buffer pool size defined for the table space. For example, a table containing one LONG column, defined in a 16-KB table space, with a combined byte size for all other columns of 5 KB, results in the creation of a VARCHAR column having a size of about 11 KB. For more information on calculating the size of columns, refer to the vendor documentation on the IBM Web site.
About Long Columns and Siebel Utilities
The Siebel dbchck utility compares the physical database schema with its logical definition in the Siebel Repository and generates errors for any inconsistencies between them. However, the dbchck utility does not generate errors for LONG columns, which are identified as LONG VARCHAR columns in the Siebel Repository and physically created by DB2 as VARCHAR columns, because inconsistencies between the logical and physical definitions of these columns is expected behavior on the DB2 for z/OS platform.
When you run other Siebel utilities, for example, Synchronize Schema Definition (ddlsync), the utilities generate LONG DDL syntax for these LONG columns if the tables containing the columns need to be re-created. However, when the table is re-created, the LONG syntax is converted by DB2 and the column is again physically created as a VARCHAR column set to the maximum size possible.
How Siebel Tables with LONG Columns Are Stored
On the z/OS platform, LONG columns can be up to 16 KB in length. If Siebel Business Applications created all LONG columns on DB2 for z/OS with a length of 16,350 characters, this would effectively force all tables to a 32-KB table space, thereby increasing buffer pool storage requirements (each table space is assigned a buffer pool of the same size).
To minimize the number of tables created in 32-KB table spaces, during the Siebel installation process, tables with LONG columns are created in an 8-KB table space by default. In Siebel Business Applications, LONG columns have an assumed minimum logical length of 4096 bytes, which forces the table to be created in an 8-KB table space. However, some of the larger tables with LONG columns are created in 16-KB table spaces. In addition, the S_SERVICE_SCRPT table is defined in a 32-KB table space because the LONG column in this table needs as much space as possible.
CREATE TABLE statements for the LONG column. So the resulting length of a LONG column is still set by DB2 for z/OS to a VARCHAR of the maximum length possible, that is, all the space not used by other columns.
About Moving Tables With LONG columns to Larger Table Spaces
By creating most tables with LONG columns in an 8-KB table space by default, almost all aspects of the Siebel application function without problems. However, depending on your business needs and the amount and type of data a LONG column is to contain, you might choose to define a Siebel table containing a LONG column in a larger or smaller table space.
For example, if your implementation involves a usage scenario in which very large notes, descriptions, emails, or other items up to 16,350 characters in length are stored, you can resolve this situation by moving the table to a larger table space, either 16 KB or 32 KB. Be aware, however, that if you move a table to a 32 KB table space, the following problems can occur:
The LONG column can become very large, which increases the storage requirements for buffer pools and can result in wasted space because the Siebel application usage of LONG columns is limited to 16 KB. If additional data is stored in a LONG column, using a tool such as SPUFI, only the first 16,350 bytes of data is retrieved by the Siebel application. Therefore, it is recommended that before executing the DDL that creates a table with a LONG column in a 32-KB buffer pool, you change the physical column definition to VARCHAR (16,350).
Large LONG columns in 32 KB table spaces can cause SQL SELECT statements to fail because the result set of a SELECT statement containing a LONG column can exceed the 32 KB in-memory sort limit of DB2 for z/OS. When DB2 for z/OS performs an in-memory sort, the full length of the LONG column is added to the total length of the result set. As a result, you might experience SQL0670N errors.
If you encounter DB2 sort limit problems of this type, redefine the LONG column as a CLOB column and move it to a smaller table space. For information about converting LONG columns to CLOB columns, see Converting LONG VARCHAR Columns to CLOB Columns.
Determining the Table Space Size for Tables with LONG Columns
To avoid LONG columns becoming unnecessarily large, define tables containing LONG columns in table spaces and buffer pools that are an appropriate size. This involves considering factors such as the type of data that the LONG column stores, and the maximum amount of data that the column is likely to contain. The following procedure describes how to estimate the appropriate table space size for a table with a LONG column.
To estimate the appropriate table space size for a table with a LONG column
Enter the following query to determine your current minimum length requirements for an existing LONG column:
SELECT MAX(LENGTH(column_name)) FROM tableowner.tablename
where:
column_name is the name of the LONG column
tableowner.tablename is the name of the table owner and the table containing the LONG column
Using the value returned by the query as a guideline, adjust the column length to take into account future needs.
On the basis of your calculations in Step 1 and Step 2, use the guidelines in the following table to determine the size of the table space appropriate for the table containing the LONG column.
Note: The values shown are general guidelines only; the actual table space size required for a specific table might vary depending on table-specific variables.Move the table to a smaller or larger table space and buffer pool as required.
For information on this task, see Moving Tables Between Table Spaces.
Moving Tables Between Table Spaces
You can move tables containing LONG columns to larger or smaller table spaces as appropriate for your environment. Circumstances in which you might consider moving tables between table spaces include the following:
If a table containing a LONG column is defined in a 32-KB table space and if SELECT queries are failing because the row length of the table exceeds the 32-KB limit, then the column must be converted to a CLOB and the table can be placed in a smaller table space.
DB2 for z/OS creates LONG columns as VARCHAR columns that are set to the maximum size possible. It is recommended that, where possible, you change the physical definition of the LONG column to a VARCHAR column of a fixed length, and move the table containing the column to a smaller table space.
In determining the size of the VARCHAR column, take into account that if the column does not exceed the buffer pool size limit, the column size can be easily increased at a later time using an ALTER statement, which is an additive schema change. However, if the column size must be decreased at a later time, you must drop the table and then re-create it, which is a non-additive schema change that involves shutting down the production database.
The following procedure outlines the steps in moving a table with a LONG column to a larger or smaller table space.
To move a table to a table space of a different size
Determine the appropriate table space size for the table using the guidelines in Determining the Table Space Size for Tables with LONG Columns.
Unload the data in the table.
Delete the table using the SQL DROP command.
Re-create the table in a table space of a different size.
If the table is the only table defined in the table space, when re-creating the table you can specify the same table space name, provided that you specify a different buffer pool size. If the table is defined in a table space with several other tables, when re-creating the table, create it in a new table space where the table is the only table in the table space.
Load the data back into the table.
Example of Moving a Table to a Table Space of a Different Size
This topic gives one example of moving a table to a table space of a different size. You might use this feature differently, depending on your business model. In this example, the LONG column in the table does not exceed 4000 bytes in length so the table can be defined at the physical schema level as a VARCHAR in an 8-KB table space.
To move a table with a LONG column to a different table space
Unload the data from the table, casting the LONG column as a VARCHAR column with a maximum length of 4000 bytes.
Defining a LONG column as a VARCHAR data type with a defined length means that even if you define the table containing the column in a larger table space, the length of the column is limited in size; this is beneficial when performing database upgrades.
SELECT CLASS_ID, COMMENTS, … NAME, PROCEDURE_NAME, REPOSITORY_ID, ROW_ID, SSE_FLG, SST_FLG, SSV_FLG, USER_AGENT_CD, CAST(column_name AS VarChar(4000)) AS column_name FROM tableowner.tablename WITH UR;
where:
column_name is the name of the LONG column
tableowner.tablename is the name of the table owner and table containing the LONG column
Use the SQL DROP command to remove the original table, then re-create the table in an appropriately sized table space, in this example, an 8-KB table space.
Load the data back into the table.
About Siebel Tables and CLOB Columns
This topic describes how character large object (CLOB) data types are implemented on the z/OS platform.
When you install Siebel Business Applications, a number of Siebel application objects are defined as CLOB data types in the Siebel Repository; such objects can have up to 128 KB of data for a single data element in a table row. Other Siebel tables have columns that are defined as LONG columns in the Siebel Schema, and as LONG VARCHARs in the Siebel Repository, but are converted to CLOB columns on z/OS. Siebel objects that are defined as LONG columns but stored as CLOBs can have up to 16,350 bytes of data for a single data element in a table row.
Siebel Tables with LONG Columns That Are Created as CLOBs
The following Siebel tables have columns defined as LONG that are converted to CLOB columns on z/OS (Clobs is set to Yes in the storage control file definition of the table). Siebel Business Applications use CLOB columns for these tables to avoid the in-memory sort limit of DB2 for z/OS.
These tables belong to the Siebel Repository and are not read or updated frequently so the performance issues associated with using CLOB columns, described in Issues in Using CLOB Columns Instead of LONG VARCHAR Columns, are not relevant:
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
If appropriate for your environment, you can enable CLOB columns for Siebel tables other than those listed. For information on this task, see Converting LONG VARCHAR Columns to CLOB Columns.
Siebel Tables Defined with CLOB Columns
The following Siebel tables have columns that are defined as CLOB columns in the Siebel Repository. Activity on these tables is quite high, but these tables cannot be stored in 32-KB table spaces because they have very large columns (greater than 128 KB) and so could encounter the in-memory sort limit of DB2 for z/OS:
EIM_AUDIT_ITEM
EIM_AUDIT_READ
S_AUDIT_ITEM
S_AUDIT_READ
S_BR_GBL_BINARY
S_BR_MODULE_BIN
S_DOCK_TXN_LOG
S_TU_LOG
S_TU_LOG_X_01
S_TU_LOG_X_02
S_TU_LOG_X_03
S_TU_LOG_X_04
S_TU_LOG_X_05
S_WEBCHNL_SES
S_WEBCHNL_SNSVC
If appropriate for your environment, you can also define new extension columns for tables as CLOB data types. For information on this task, see About Defining New Extension Columns as CLOB Data Types.
About Defining New Extension Columns as CLOB Data Types
If you create an extension column for a table, you can define the column as a CLOB data type by typing the value CLOB in the Physical Type field of the column in Siebel Tools (although CLOB is not one of the options in the Physical Type field drop-down list). For information on creating extension columns for tables, see Roadmap for Creating Custom Extensions to the Siebel Schema.
It is recommended (though not required) that you also enable CLOB columns for the table containing the extension column in the storage control file as described in Converting LONG VARCHAR Columns to CLOB Columns.
Converting LONG VARCHAR Columns to CLOB Columns
Siebel application objects defined as LONG columns in the Siebel Schema are defined as LONG VARCHAR data types in the Siebel Repository. If you choose, you can store these columns as character large objects (CLOBs) on DB2 for z/OS.
The default setting for these objects in the Siebel Schema is LONG because of the performance and storage characteristics of CLOBs in a DB2 for z/OS environment. These characteristics are described in Issues in Using CLOB Columns Instead of LONG VARCHAR Columns. However, it is recommended that you store LONG VARCHAR objects in the Siebel Repository as CLOBs in the following circumstances:
If you have Siebel Marketing, change the LONG column in the S_NOTE table to a CLOB.
If you write your own scripts, it is recommended that you convert LONG columns that contain scripts to CLOB columns to safeguard against space limitations that are inherent to the structure of z/OS.
The Siebel installation, upgrade, and migration processes create all auxiliary objects necessary to support CLOBs so that their use is transparent. However, CLOBs are not enabled by default. To store Siebel application objects defined in the Siebel Repository as LONG VARCHAR data types as CLOB data types, perform the following procedure.
To convert LONG VARCHAR columns in a table to CLOB columns
Edit the storage control file as described in About Modifying Storage Control Files.
Locate the table object for which you want to enable CLOB columns, and set the value of Clobs to YES.
All LONG VARCHAR columns defined for the table in the Siebel Repository are created as CLOB columns in the physical Siebel Schema.
Issues in Using CLOB Columns Instead of LONG VARCHAR Columns
If you use a CLOB column instead of a LONG VARCHAR column, you do not run into the in-memory sort limit of DB2 for z/OS. This memory limitation does not occur because DB2 for z/OS does not account for the actual length of the CLOB column in estimating the total row width to be sorted in memory. However, there are a number of issues in using CLOB columns as follows:
Using a CLOB column has an impact on performance.
Fetching data from CLOB columns requires extra network flows between the client and the z/OS host, which can impact response time. Use CLOB data in form applets but be cautious about using CLOB data in a Siebel list applet if you want to keep the volume of data in network flows to reasonable levels. Writing data to CLOB columns is also slower than writing to LONG VARCHAR columns so for tables that are frequently used, moving to CLOB columns is not an option.
Using a CLOB column has an impact on storage (DASD).
Using CLOB columns generally increases the amount of storage (DASD) needed, because space is allocated for the total width of the CLOB column even if the CLOB column contains only one byte of data.
Compression is not supported for LOB table spaces.
Perhaps the most important trade-off of using CLOBs relates to recovery considerations. Using CLOBs with LOG YES provides a point of forward recovery. However, there is a cost associated with logging the contents of a CLOB column. You need to consider this additional overhead before deciding to use CLOB columns.
Converting Nonpartitioned Tables to Partitioned Tables
Siebel Business Applications support two scenarios for converting nonpartitioned tables to partitioned tables.
Source table and target table are defined in different table spaces.
If the source table and target table are defined in different table spaces, and the target table space is a new table space that does not yet exist in your current database, no special action is required. Run the two utilities available in the Database Configuration Wizard (Migrate Repository and Synchronize Schema Definition) to automatically rebuild the table in the new partitioned table space and remove the original table from the database.
Source table and target table are defined in the same table space.
Database operations do not support rebuilding a table space from nonpartitioned to partitioned. Therefore, if the source table and target table are defined in the same table space (for example, if you used the dbconf.xls spreadsheet to convert an existing table space from nonpartitioned to partitioned), you must perform one of the following procedures (as appropriate for your configuration) to convert the nonpartitioned table to a partitioned table.
To convert to a nonpartitioned table for a partitioned table space configuration
Use your preferred database tool to manually move every nonpartitioned table (every table that you intend to convert) to a temporary table space, and then use the SQL DROP command to remove the original table space.
Use the Siebel Database Storage Configurator (dbconf.xls) to modify the storage control file, changing the mode of the table spaces to partitioned.
For further information, see About Modifying Storage Control Files.
Run the Database Configuration Wizard and select the Migrate Repository option. This process automatically rebuilds the source tables in the new partitioned table space and removes the original nonpartitioned table.
Manually remove the temporary table space that you created in Step 1 from the database.
To convert nonpartitioned tables to partitioned tables using alternate tools
You can use alternate tools (for example, BMC Change Manager or IBM Compare Utility) to convert nonpartitioned tables to partitioned table spaces. In this case, after applying all changes, change the schema version by executing the following SQL:
update S_APP_VER set CUSTOM_SCHEMA_VER = char(integer(CUSTOM_SCHEMA_VER) + 1)
This SQL is stored in ddlview.sql and can be executed outside of the Siebel application.
About Creating Custom Extensions to the Siebel Schema
There are several different possible scenarios for creating Siebel Schema custom extensions:
Creating a small extension column, so the table fits into its existing table space
Creating a large extension column, so the table has to be redefined to a larger page size and therefore a larger table space
Creating an extension table
If planning custom extensions to the Siebel Schema on the z/OS platform, consider the following:
If a LONG column is defined for a table, attempting to add an extension column to the table causes an error. This is because a LONG column uses all the space in a table space that is not used by the non-LONG columns, leaving no space available for an extension column. In this case, use an extension table instead. For further information, see About Siebel LONG Columns on DB2 for z/OS.
You can create a new extension column of type CLOB for a table by typing the value CLOB in the Physical Type field of the column in Siebel Tools, although this option is not available in the drop-down list.
Creating tables with Identity type columns or extension columns of Identity type is not supported.
The process of creating a small extension column is the same on z/OS as on other platforms and is documented in Using Siebel Tools. To create a large extension column or an extension table on the z/OS platform, you must specify a 16-KB or 32-KB table space:
If you are developing on DB2 for Windows or UNIX, you must enter a 16-KB or 32-KB table space in the Apply Schema screen (see Step 4.)
If you are developing on DB2 for z/OS, you must edit the storage control file to specify the database and table space in which the new extension column or table is to reside.
For further information on creating custom extensions to the Siebel Schema, see Roadmap for Creating Custom Extensions to the Siebel Schema. For information on estimating storage needs, see About Creating Custom Extensions to the Siebel Schema
Roadmap for Creating Custom Extensions to the Siebel Schema
To extend the Siebel Schema by creating extension columns or tables, you need to perform the following tasks:
Plan the new custom object, for example, determine the type of object you need to create, the name of the object, and its size.
Add a storage definition for the new object to the storage control file of the Siebel Schema being extended.
For information on this task, see Amending the Storage Control File for New Schema Objects.
Create the new object in Siebel Tools; this adds the object definition to the Siebel Repository thereby updating the logical schema definition in the development environment.
The process of adding a new object in Siebel Tools is the same on z/OS as on other platforms. However, you need to make sure that you select the check box on the Database tab of the Development Tools Options dialog under the View menu. This option validates the object’s compliance with DB2 for z/OS sizing conventions.
Apply the physical schema extensions to the development database, specifying the storage control file you updated in Step 2. You can apply the changes directly or generate the DDL into a file which can be applied on the z/OS host later.
If you are developing on DB2 for Windows or UNIX, follow the procedures in Applying Schema Extensions to the Local Development Database.
If you are developing on DB2 for z/OS, follow the procedures in Migrating Customizations from Development to the Target Database.
Some organizations do not allow direct database extension. Siebel Tools does not allow the Apply/ DDL button to be disabled but the Apply process fails if developers do not have appropriate database privileges (such as CREATEDBA and DBADM). If you do not want developers to extend tables directly, you can control this using database privileges. In this case, developers follow the procedures in Migrating Customizations from Development to the Target Database to apply schema extensions to their development databases.
If you chose to generate the DDL into an output file, review the file, then have your DBA execute the file on the z/OS host.
In your development environment, update and test the configuration changes that apply to the extensions you made.
Apply the schema extensions on the production database on the z/OS host.
For information on this task, see Process of Applying Schema Extensions to the Target Database.
Amending the Storage Control File for New Schema Objects
If you need to create new database objects as a result of changes made to the Siebel Schema in Siebel Tools, you must create a storage definition for the new objects in your storage control file. If you do not create storage control file definitions for new objects, the required objects are created using values derived from the Defaults object in the storage control file when you apply the schema extensions to the development database using Siebel Tools.
This task is a step in Roadmap for Creating Custom Extensions to the Siebel Schema.
Use the following procedure to add new object definitions to a storage control file.
To amend the storage control file for new schema objects
Using the Extract from catalog option on the Database Configuration Wizard, extract the storage control file from the Siebel Schema that is being extended.
For information on this task, see Extracting a Storage Control File from the DB2 Catalog.
Add the new object definitions to the extracted storage control file. For example, if you create a new extension table, you must create a table object definition, and object definitions for the database and table space in which the table is to be placed.
You can edit the storage control file using a text editor or you can use the Siebel Database Storage Configurator to edit the file. For additional information, see Using the Siebel Database Storage Configurator.
You can add new object definitions anywhere in the storage control file, for example, you can append them to the end of the file. The new object definitions are read from the file when you apply the physical schema extensions to the development database. Once the new objects are physically created in the Siebel Schema, the next time the Extract from catalog task is run to produce a new storage control file, these objects are correctly grouped by type in the file.
Note: The Extract from catalog process collects the definition for only Siebel Schema database and table space objects that are associated with a table.The following example shows object definitions for a new extension table, named X_EXTENT, which is created in a table space, named S0600100, which is in a database, named SIDB6001:
[Object 6001] Type = Database Name = SIDB6001 LockSize = Page [Object 6002] Type = Tablespace Name = S0600100 Database = SIDB6001 LockSize = Page Bufferpool = BP16K1 Define = No Partitions = 0 [Object 6003] Type = Table Name = X_EXTENT Database = SIDB6001 Tablespace= S0600100 CLOBS = NO
When specifying the object number, for example [Object 6001], enter the next number in sequence after the last object number listed in the existing storage control file.
Applying Schema Extensions to the Local Development Database
After your have customized the Siebel Schema by defining new tables or columns in Siebel Tools and in the storage control file, you must apply the changes to your test environment.
This task is a step in Roadmap for Creating Custom Extensions to the Siebel Schema.
The following procedure describes how to apply schema extensions to a local Siebel development database.
To apply schema extensions to your local development database
In Siebel Tools, select the table containing the changes you want to apply to the database.
Click Apply/DDL in the Object List Editor.
Select one of the following options and click OK.
Apply. Select this option to apply the changes you have made directly to the database.
If you are connected to a local database, a message box appears, alerting you that your changes will be applied to the local database only. Click OK to continue.
Generate DDL. Select this option to write the DDL to implement the repository changes to a file. You can apply the DDL against the database later.
The Apply Schema dialog box appears.
Fill in the fields as described in the following table, and then click either Apply or Generate DDL, depending on the option you selected in Step 3.
Field Description Tables
Select one of the following options from the drop-down menu:
All. Updates the database to reflect all changes made to the dictionary. This option forces each database object to be compared with the data dictionary, and updated if required.
Current Query. Updates the database to reflect modifications made to the tables in the current query only.
Current Row. Updates the database to reflect modifications made to the table in the current row only.
Table space
You do not have to set a value for this field on DB2 for z/OS.
16K table space
You do not have to set a value for this field on DB2 for z/OS.
32K table space
You do not have to set a value for this field on DB2 for z/OS.
Index space
You do not have to set a value for this field on DB2 for z/OS.
Storage control file
Specify the complete path to the storage control file you updated with the new object definitions as described in Amending the Storage Control File for New Schema Objects. For example:
C:\siebel\8.2.2.0.0\ses\dbsrvr\db2390\storage.ctl.The storage control file includes information about table spaces or index spaces associated with the new objects you are applying. If you do not specify table space or index space information for new objects in the storage control file, the values specified in the Defaults object are applied.
Database user
Enter the ID of a database user with CREATEDBA or DBADM privileges.
The schema qualifier is read from tools.cfg.
Database user password
Enter the password of the database user you specified above.
ODBC data source
Verify that the ODBC connection specified in the ODBC Data Source text box is correct for your environment.
You cannot apply schema changes to any database other than the one you are currently connected to (for example, by specifying the ODBC name of a different database).
DDL File
Specify the name and location of the file where you want the DDL to be stored (only applicable if you selected the Generate DDL option in Step 3).
Depending on the option you selected (Apply or Generate DDL), your changes are either written directly to the database or the DDL to implement the changes is written to the file you specified.
To activate extensions to EIM tables, select the appropriate tables, and then click Activate.
Spooling DDL Generated by the Apply Button
The Siebel environment variable, SIEBEL_GENERATE_DDL, allows you to record into a file the DDL that is generated when you click the Apply button in Siebel Tools.
To spool the DDL generated by the Apply button to a file
Before running Siebel Tools, set the environment variable from the command line or using Windows environment parameters:
If using the command line, set
SIEBEL_GENERATE_DDLtoY.If using the Windows environment parameters, do the following:
From the Start menu, choose Control Panel, and then the System option.
Click the Advanced tab, then click Environment Variables.
In the System Variables box, click New.
The New System Variable window appears.
Enter
SIEBEL_GENERATE_DDLin the Variable name field andYin the Variable value field, and click OK.Continue to click OK until you are out of the System Variable window.
Open Siebel Tools, and click the Apply button.
The DDL is generated to the file output.sql in the
binsubdirectory of the Siebel Tools installation directory. The default Siebel Tools installation directory isC:\Siebel\8.2\Tools_1.
Process of Applying Schema Extensions to the Target Database
After testing schema extensions that you created in the development environment, you can migrate the changes to the target user-acceptance or production database. To apply schema extensions to the target database, perform the following tasks:
This process is a step in Roadmap for Creating Custom Extensions to the Siebel Schema.
Preparing the Target Database
This topic describes how to prepare a Siebel production database before Siebel Schema extensions are applied.
This task is a step in Process of Applying Schema Extensions to the Target Database.
Complete the following actions before migrating the changes to the target database:
Prepare the storage control file (for example, my_storage_file.ctl).
If you have created any new extension tables, edit the storage control file to specify the database and table spaces where the new extension tables are to reside. For information on this task, see Amending the Storage Control File for New Schema Objects.
Ask all mobile users to synchronize.
Make sure all connected clients are disconnected from the Siebel database.
When all mobile user transactions have been merged and routed, stop the Siebel Server.
Perform a full backup of the database.
Note: If you are changing the data type or length of custom extension columns that already contain data in the target database, export that data before making the schema changes. After making the changes, import the data back into the target database.
Migrating Customizations from Development to the Target Database
This topic describes how to migrate customizations from a development to a target database.
When migrating customizations from your development source to your target database on the DB2 host, you can use one of two modes of execution:
Run DDL Automatically. Select this mode to apply the DDL and DML required to create the Siebel Schema directly against the database, using an ODBC connection.
Generate DDL Into Files. Select this mode to generate the DDL required to create the Siebel Schema into files for transfer to the DB2 host.
This topic describes the DDL-generation mode of execution that is generally used under a change-management system.
This task is a step in Process of Applying Schema Extensions to the Target Database.
If your development environment is on DB2 for z/OS, your DBA can clone the development database to the target database. For more information, see Cloning a DB2 for z/OS Database.
Siebel Business Applications do not support customized database triggers. If you have created customized triggers on your Siebel base tables, disable them before migrating the database schema. You can re-create the triggers after the migration is finished.
To migrate the schema
Launch the Database Configuration Wizard and follow the steps in Performing a Standard Installation until the Siebel Database Operation screen is displayed (Step 6).
Select the Migrate Repository option, and click Next.
In the Source Repository Selection Screen, choose one of the following options, and click Next:
Read source repository directly from the database. If you select this option, proceed to Step 5.
Read source repository from a previously exported file. If you select this option, the Repository File Selection screen appears. Proceed to Step 4.
If you selected the Read source repository from a previously exported file option, enter the name of the source repository file you want to use, and click Next.
Indicate whether the target database will be online or offline when the repository migration process runs against the target database, and click Next.
Select one of the following options, and click Next:
There are new schema changes to be applied. If you select this option, during the migration process, schema changes defined in the new repository are applied to the physical target database. The target enterprise must be offline.
There are no new schema changes to be applied. If you select this option, schema changes defined in the new repository are not applied to the physical target database during the migration process.
On the Language Selection screen, indicate the language in which the target database runs, and click Next. (This screen is displayed only if you have more than one language deployed.)
If you selected the Read source repository from a previously exported file option in Step 3, proceed to Step 12.
If you selected the Read source repository directly from the database option in Step 3, proceed to Step 8.
Enter the ODBC Data Source Name to use to connect to the Siebel database, and click Next.
Enter the Database User Name for the database in which the source repository resides, and click Next.
Enter the password associated with the Database User Name. Re-enter the password to confirm, and click Next.
Enter the Siebel Schema Qualifier of the database in which the source repository resides. This name is a character ID that identifies the Siebel Schema owner, for example, SIEBTO.
On the Source Database Repository Name screen, enter the name of the repository you are migrating, then click Next.
You are not prompted to enter this value if you chose the Read source repository from a previously exported file option in Step 3.
On the Target Database Repository Name screen, enter the name you want to assign to the repository after it is migrated, then click Next.
In the Target RDBMS Platform screen, select IBM DB2 UDB for z/OS, and then click Next.
Specify whether the target database you are migrating to is Unicode or non- Unicode, and click Next.
Enter the target database ODBC data source name, then click Next.
Enter the target database DB2 subsystem name, then click Next.
In the Target Database User Name screen, enter the Target Database Username, then click Next.
In the password screen, enter the password associated with the Target Database Username. Confirm the password by typing it again, then click Next.
In the Target Schema Qualifier screen, type the target schema qualifier in uppercase, and then click Next.
In the Target Security Group ID/Grantee screen, type the target security group authorization ID, then click Next.
If you indicated that there are no new schema changes to be applied (Step 6), proceed to Step 25.
In the Migrate Repository Mechanism screen, select the Generate DDL into Files option, and then click Next.
On the DDL Commit Frequency screen, choose the number of DDL statements that can be run before a COMMIT statement is issued, and click Next.
On the Output Directory screen, enter the DDL output directory. The default directory is DBSRVR_ROOT
\db2390\dboutput\dev2prod (Windows) or DBSRVR_ROOT/db2390/dboutput/dev2prod (UNIX).On the Storage Control File screen, enter the name and directory path of the storage control file to be used in the migration, for example, DBSRVR_ROOT
\db2390\my_storage_file.ctl (Windows) or DBSRVR_ROOT/db2390/my_storage_file.ctl (UNIX).Click Next.
On the Log Output Directory screen, specify where log files generated during the migration are to be created, for example, SIEBSRVR_ROOT
\log\dev2prod_mf(Windows) or SIEBSRVR_ROOT/log/dev2prod_mf(UNIX), and click Next to continue.Save the configuration information you have entered and launch the Siebel Upgrade wizard as described in the following relevant topic:
Click OK to continue.
The Siebel Upgrade Wizard creates a number of files in the directory you specified in Step 24. These files contain the SQL and unload and load control cards used to perform the repository migration from development to production on the DB2 host.
A file, ftpsync.txt, is also created; use this file to transfer the migration files to z/OS where they are applied. For information on this process, see Applying Schema Changes to the Target Database.
Click Exit to exit the Database Configuration Wizard.
Applying Schema Changes to the Target Database
After generating the DDL into files, you must use Oracle-provided scripts, your own FTP, or a similar file transfer program to transport the DDL files to the z/OS host, where you execute it using customary methods, for example, SPUFI.
This task is a step in Process of Applying Schema Extensions to the Target Database.
To apply schema changes to the target database using Oracle-provided scripts
Navigate to the output directory you specified in Step 24 and open the ftpsync.txt file.
Edit the ftpsync.txt file using the following information:
- Change &IP to the IP address of your DB2 host, for example
ZM01. Change &Username to your own user name, for example
SADMIN.Change all occurrences of
SIEBELQ1to your own high-level qualifier (HLQ), for example,SADMIN.Save the file.
The following is an example of the ftpsync.txt file:
open &IP user &Username quote site cylinders primary=1 secondary=1 quote site recfm=fb lrecl=80 blksize=0 quote site cylinders primary=1 secondary=1 send &directoryPath1/jobsync.txt 'HLQ.SIEBEL.V00' quote site cylinders primary=10 secondary=2 send &directoryPath1/unload.ldc 'HLQ.SIEBEL.V01' quote site cylinders primary=10 secondary=2 send &directoryPath1/load.ldc 'HLQ.SIEBEL.V02' quote site cylinders primary=5 secondary=2 send &directoryPath1/schema.sql 'HLQ.SIEBEL.V03' quote site cylinders primary=1 secondary=1 send &directoryPath1/bumpver.sql 'HLQ.SIEBEL.V04'
- Change &IP to the IP address of your DB2 host, for example
Execute ftpsync.txt to send the DDL files to the host, as follows:
If you are using Windows, double-click jobsync.bat.
If you are using UNIX, execute Unix_jobsync.bat.
The Unix_jobsync.bat file contains the following code:
mv ./schema.sql schema.sql.old cat ./schema.db.sql ./schema.tbsp.sql ./schema.tbl.sql ./schema.grt.sql ./ schema.uind.sql ./schema.oind.sql ./schema.nuind.sql > schema.sql echo User ftp -vn < ftpsync.txt > ftpsync.log
Enter the password associated with the user name you specified in the ftpsync.txt file, and press any key to continue.
The log file ftpsync.log is created in the DBSRVR_ROOT
\db2390\dboutput\dev2prod(Windows) or DBSRVR_ROOT/db2390/dboutput/dev2prod(UNIX) directory.The log contains information on the file transfer.
When the transfer is successfully completed, log on to the mainframe, and edit the DSNHLQ.SIEBEL.V00D2P data set by changing the job card and DSNHLQ.
Submit the job and, when it is completed, make sure that the return code is 0.
A new partitioned data set (PDS) is created, DSNHLQ.SIEBEL.D2P.EXEC.
Execute the following REXX exec to apply the DDL generated for the schema migration process.
Execute clist DSNHLQ.SIEBEL.D2P.EXEC(SBLD2P)
Verify the schema changes.
Synchronizing Siebel Repository Definitions and the Physical Siebel Schema
To synchronize the Siebel Repository database definitions and the existing physical Siebel database, use the Synchronize Schema Definition option of the Database Configuration Wizard.
When you synchronize repository definitions and the existing Siebel Schema, the following data sources are accessed:
Siebel Repository
Storage control file
DB2 catalog
When you run the Synchronize Schema Definition option, be aware of the following:
Custom columns in the Siebel Schema that are not in the Siebel Repository are not deleted
Custom indexes in the Siebel Schema that are not in the Siebel Repository are deleted
To synchronize Siebel schema definitions
Launch the Database Configuration Wizard and follow the steps in Performing a Standard Installation until the Siebel Database Operation screen (Step 6) is displayed.
Select the Run Database Utilities option, and click Next.
Select the Synchronize Schema Definition option, and click Next.
Identify the appropriate database encoding method, and click Next.
UNICODE Database
Non-UNICODE Database
If you selected the UNICODE Database option, proceed to Step 7.
Specify the code page encoding scheme for your Siebel database, either ASCII or EBCDIC, and click Next.
On the Siebel Tools Directory and Tools DSN screen, enter the directory where Siebel Tools is installed. Oracle reccomends that you do not select the BIN folder.
This step is required when schema changes are made so that the changes are published into the Runtime Repository.
On the Language Selection screen, select the language in which the database runs, and click Next.
Enter the ODBC data source name to use to connect to the database, and click Next
Enter the source database DB2 subsystem name, then click Next.
Enter the database user name. Click Next.
Enter the password associated with the database user name, then enter the password again to confirm it. Click Next.
Enter the Siebel schema qualifier in uppercase, and click Next.
Enter the name of the security group ID/Grantee, and click Next.
Specify the Repository Synchronization Mechanism, in this case, select the Generate DDL into Files option, and click Next.
On the Commit Frequency screen, choose the number of DDL statements that can be run before a COMMIT statement is issued, and click Next.
Enter the name of the output directory where the DDL is to be created, then click Next.
By default, the DDL output directory is DBSRVR_ROOT
\db2390\dboutput\ddlsync(Windows) or DBSRVR_ROOT/db2390/dboutput/ddlsync(UNIX). Click Next.Enter the path and name of the storage control file to use in the synchronization process. Alternatively, use the Browse button to locate this file, then click Next.
Enter the name of the Repository with which the existing physical Siebel database is to be synchronized. Click Next.
Enter the name of the directory where the log files are to be created, and click Next.
By default, the files are created in SIEBSRVR_ROOT
\log\ddlsync_mf(Windows) or SIEBSRVR_ROOT/log/ddlsync_mf(UNIX).Save the configuration information you have entered and launch the Siebel Upgrade wizard as described in the following topics:
The Wizard generates the DDL required to synchronize the Siebel database and the Siebel Repository.
If you selected the Run DDL Automatically installation option, the Siebel Upgrade Wizard applies the DDL commands to synchronize the Siebel database with the Repository directly against the database on the z/OS host. When you receive a message stating that the configuration was applied successfully, click OK. This step completes the Run DDL Automatically synchronization option.
If you selected the Generate DDL Into Files installation option, the Siebel Upgrade Wizard generates the following files into the output directory that you designated in Step 19 after the synchronize schema definition configuration is completed:
schema.sql
ddlview.sql
Ask your DBA to apply these files to synchronize the Siebel Repository database definitions with the existing physical Siebel database.
Cloning a DB2 for z/OS Database
You can clone your existing Siebel database to a target database. The DB2 system cloning process involves the following steps:
Extracting a Storage Control File from the DB2 Catalog
Extract a storage control file from the existing Siebel Schema.
Generating a DDL File from a Storage Control File
Use the extracted storage control file to generate a DDL file, then apply the DDL file to the database to create a clone of the Siebel Schema.
Generating a DDL File from a Storage Control File
Use the following procedure to generate DDL from a storage control file.
To generate a DDL file
Launch the Database Configuration Wizard and follow the steps in Performing a Standard Installation until the Select Installation Type screen is displayed (Step 17).
Select the Customized Install option, and then click Next.
Specify the storage control file you want to use to create the DDL, and click Next.
In the Select Installation Mechanism screen, select the Generate DDL Into Files option, and click Next.
On the DDL Commit Frequency screen, choose the number of DDL statements that can be run before a COMMIT statement is issued, and click Next.
In the Output Directory screen, specify the location of the directory in which the DDL is to be created, for example, DBSRVR_ROOT
\db2390\dboutput\install(Windows) or DBSRVR_ROOT/db2390/dboutput/install(UNIX). Click Next.In the Log Output screen, specify the directory where log files are to be created or accept the default. Click Next to continue.
By default, the files are created in SIEBSRVR_ROOT
\log\install_mf(Windows) or SIEBSRVR_ROOT/log/install_mf(UNIX).Save the configuration information you have entered and launch the Siebel Upgrade wizard as described in the following topics:
Press Ok and the Wizard generates DDL from the storage control file you specified in Step 3 into the output directory that you designated in Step 6.
When the DDL files are created, the following message appears.
Files schema.sql and ddlview.sql with the Siebel Schema modifications have been generated in the DDL Output Directory. Please select Yes to exit now and apply the files
Select Yes.
The schema.sql and ddlview.sql files are created.
Apply the DDL files using either an optional process provided by Siebel Business Applications or any other tools used in your company for applying DDL.
If you want to clone the Siebel Schema into more than one database, copy the generated DDL files, replace the schema qualifier in the DDL files, and then save the files under a new name.
About Data Migration
After you have successfully migrated your source schema to your target database, you must copy data from your development environment. Such data might include:
Modified files, such as the Web templates, image files, and cascading style sheets
Transactional data, such as accounts, contacts, and opportunities
Setup data, such as employees, positions, and responsibilities
Program data, specifically Assignment Manager rules and Workflow processes and policies, and personalization rules and expressions
For information on copying such data, see Migrating Data Using Siebel Enterprise Integration Manager and Developing and Deploying Siebel Business Applications.
Applying Schema Changes to Other Local Databases
For information on upgrading local databases, see Using Siebel Tools and Developing and Deploying Siebel Business Applications.
Customizing Applications Using Assignment and Workflow Rules
You can customize applications using Siebel Assignment Manager and Siebel Workflow, as well as by using Siebel Tools.
You can use Siebel Assignment Manager to create business rules that automatically assign entities, such as opportunities, service requests, or activities, to the most qualified individuals. For more information, see Siebel Assignment Manager Administration Guide.
Siebel Workflow provides a graphical interface for designing and implementing business processes and user interactions. Workflow processes define the steps to automate business processes, such as sending email. Workflow policies trigger processes when they detect certain conditions, for example, an opportunity being assigned. For more information, see Siebel Business Process Framework: Workflow Guide.
To activate assignment or workflow rules you must run the following Siebel Server components:
Generate Triggers (GenTrig).
Allows you to create database triggers. GenTrig writes database triggers to a SQL file, such as TRIGGER.SQL. On the DB2 host, the SQL file is executed manually by a DBA. To execute TRIGGER.SQL manually, run GenTrig with the EXEC parameter set to FALSE.
Note: For DB2, GenTrig does not log into the database as the table owner. Instead, it logs in as the privileged user. All triggers generated are qualified with the schema qualifier. When starting the GenTrig component, users are prompted for the schema qualifier.Workflow Monitor.
Uses the database triggers to identify the records that might match policy conditions.
For information on activating rules, see Developing and Deploying Siebel Business Applications.