Configuring the Siebel Database Layout

This chapter describes the storage control file templates that are shipped with Siebel Business Applications for database schema configuration during the installation process. The primary audience for this chapter is the DB2 DBA.

This chapter contains the following topics:

Control Files Used in the Siebel Installation

The Siebel Database Schema installation process uses two types of Siebel control files:

DDL control file
The DDL control file contains the logical definitions for all Siebel tables and indexes for the current version of Siebel Business Applications. The DDL control file (ddl.ctl or schema.ddl) is read-only to protect the integrity of the Siebel data model.
Storage control file
The storage control file contains the physical database layout. The storage control file is specific to DB2 for z/OS.
Carefully review the storage control file (or the resulting schema.sql file that is based on the storage control file) and adjust the database layout to satisfy your database requirements. You can modify any option available for each database object, for example, by fine-tuning FREEPAGE and PCTFREE for a particular table space or a group of table spaces.

Note: It is especially important that you review SEGSIZE, PRIQTY, and SECQTY for the table spaces that are to contain the major tables for your implementation. The table spaces used for Siebel Repository tables are appropriately sized. However, because sizing requirements for base Siebel tables vary from deployment to deployment, it is your responsibility to set storage parameters capable of accommodating your installation.

Together, the DDL control file and the storage control file provide Siebel Business Applications with all the information necessary to create DDL for the Siebel Schema in an output file (by default named schema.sql).

You can choose either of two methods to execute the SQL files from a designated Siebel Server:

Automatic execution from the Siebel Server using an ODBC utility (such as odbcsql) or a version control tool for file execution. To use this method, review Configuration Options for DB2 Connect to determine the configuration appropriate to the Client your enterprise supports, and then follow the instructions in About the Required IBM Fix Packs.
Direct execution on the DB2 host using Siebel-provided scripts, your own FTP, or any other file transfer program to transfer files to the DB2 host, and then use a native IBM utility, such as SPUFI for file execution.

Note: If you want to execute SQL using mainframe native tools, refer to the IBM documentation.

About Storage Control File Templates

The storage control file is unique to the DB2 for z/OS platform for Siebel Business Applications. The storage control file contains storage information (for example, partitioning indexes, table spaces, and storage groups) that is used as the basis for the storage layout of your Siebel database. Even if you are using a preconfigured storage layout, make sure that the layout is valid for your Siebel Schema.

Starting with release 7.7, the Siebel database schema structure implements a 1:1:1 model (one table in each table space, and one table space in each database). This layout reduces logging, removes concurrency issues, and allows for more databases to be used to achieve high database parallelism. While this is the most appropriate schema structure to avoid database descriptor (DBD) locking and logging, you can choose to regroup tables in table spaces and table spaces in databases. However, if you do implement a structure other than the 1:1:1 model (for example, a 1:M:1 schema layout), ensure that you carefully monitor the impact of doing so in your environment. If you notice a decrease in the performance of Siebel Business Applications, this could be due to lock contention occurring as a result of SQL executed by Siebel Business Applications or Siebel utilities. In this case, you might have to make adjustments to the schema layout.

The Siebel application installation process installs five storage control file templates in the DBSRVR_ROOT\db2390 directory (Windows) or the /DBSRVR_ROOT/db2390 directory (UNIX). Select one of the following templates, based on your partitioning needs and your encoding scheme:

storage_np.ctl
This template contains a database storage layout for a non-Unicode Siebel Schema with one table for each table space, and one table space for each database. No partitioned table spaces are provided.
storage_np_u.ctl
This template contains a database storage layout for a Unicode Siebel Schema with one table for each table space, and one table space for each database. No partitioned table spaces are provided.
storage_p.ctl
This template is the database storage layout for the Siebel Schema with partitioning for ASCII encoding. Every nonpartitioned table resides in its own segmented table space. Each table space resides in its own database.
storage_p_u.ctl
This template is the database storage layout for the Siebel Schema with partitioning for Unicode encoding. Every nonpartitioned table resides in its own segmented table space. Each table space resides in its own database.
storage_p_e.ctl
This template is the database storage layout for the Siebel Schema with partitioning for EBCDIC encoding. Every nonpartitioned table resides in its own segmented table space. Each table space resides in its own database.

The layouts of the storage control file templates are generic; your DBA must customize them to suit the needs of your enterprise, for example, to change the default space allocation for Siebel objects, seed data, and views. Although Siebel-provided templates might be sufficient for your development environment without any changes, it is recommended that your DBA reviews and modifies the database storage layout to best fit the needs of your deployment in a production environment.

The storage control file templates are the starting point for your customization process. Before you select a Siebel storage control file template, you need to understand how Siebel objects are grouped and the object naming conventions used.

About Siebel Objects

An understanding of Siebel objects and object naming conventions is a foundation for understanding how your customizations affect the underlying data sets. The following are described in this topic:

Symbolic Variables in Storage Control Files

The storage control file contains definitions of objects (such as databases, table spaces, and tables) that define the physical storage layout of your database. Many of the options used in the object definitions are the equivalent of options in DB2 SQL statements.

While most options within the objects are defined by actual values (for example, SegSize is set to 32), some options include symbolic variables that are substituted with the actual values that comply with your organization’s standards. Review the following table for a list of the symbolic variables in the storage control file templates and the values substituted for them.

Table Symbolic Variables

Symbolic Variable	Actual Value Substituted for Symbolic Variable
$DbnamePrefix	Database Name Prefix
$StogroupTables	Table Storage Group for Tables
$StogroupIndexes	Index Storage Group for Indexes
$IndexBufferPool	Index Buffer Pool Name
$4KBufferPool	4-KB Buffer Pool Name
$8KBufferPool	8-KB Buffer Pool Name
$16KBufferPool	16-KB Buffer Pool Name
$32KBufferPool	32-KB Buffer Pool Name
$DbType	Encoding Scheme (acceptable values are either ASCII or EBCDIC)

Default Objects in Storage Control Files

The first object in the storage control file is a Defaults object. If no actual value exists for a given object, but a value for that object is required by a DB2 statement, then the default value is either derived from the Defaults object or inherited from a higher-level object.

The following example illustrates what the Defaults object looks like in the storage control file:

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

You can override default values for any given object by explicitly defining the value in the object. For example, if the Tablespace object does not contain PriQty or SecQty values, then these values are inherited from the PriQty and SecQty values defined in the Defaults object. However, if the values are defined in the Tablespace object, then the explicitly provided value overrides the default and is used in the output DDL.

Note: If you create an extension table in Siebel Tools and use the apply process without creating new underlying objects for this table in the storage control file, then the necessary storage layout is derived from the default definitions. For information about the apply process, see Roadmap for Creating Custom Extensions to the Siebel Schema.

Buffer Pools Used in Storage Control Files

Siebel Business Applications use four buffer pools in the storage control file templates:

One 4-KB buffer pool for 4-KB table spaces (the default name is BP1)
One 4-KB buffer pool for all indexes (the default name is BP2)
One 8-KB buffer pool for 8-KB table spaces (the default name is BP8K1)
One 16-KB buffer pool for 16-KB table spaces (the default name is BP16K1)
One 32-KB buffer pool for 32-KB table spaces (the default name is BP32K1)

Multiple buffer pools can be used for each page size. If you require multiple buffer pools, use the Siebel Database Storage Configurator (dbconf.xls) or any other method described in Preparing a Storage Control File to enter these values.

Your DBA must activate and grant use of buffer pools before you can perform the Siebel installation. For an example of buffer pool definitions, see Allocating Space for Buffer Pools and Storage Groups.

About Using Storage Groups

Siebel Business Applications require one storage group for indexes and one storage group for tables (data). Before performing your installation, ask your DBA to provide you with one storage group name for each. You can use the same name for data and indexes, or you can use different names. You can set up storage group usage in any way that makes sense for your deployment. For an example of storage group definitions, see Allocating Space for Buffer Pools and Storage Groups.

Database Objects in Storage Control Files

In the Siebel database storage layout, each table resides in its own table space, and each table space resides in its own database. The number of databases you receive depends on which modules your organization purchases. You can regroup table spaces in databases, as required for your enterprise.

Note: The maximum number of databases is 64,000 for each DB2 subsystem.

The following example illustrates what a Database object looks like in the storage control file:

[Object 4]
Type       = Database
Name       = SIDB2532
LockSize   = Page

The Database object illustrated in this topic translates into the following DDL output:

CREATE DATABASE SIDB2532 CCSID ASCII BUFFERPOOL BP1 /

In Siebel Business Applications, CCSID is defined on the database level only. CCSID is always taken from the Defaults object.

LockSize is not used in the CREATE DATABASE statement, because LockSize is a default value for all table spaces that belong to a given database.

The names of databases in the storage control file comprise two parts:

The first part of the name is a variable, $DbnamePrefix.
The default value of the database name prefix is SIDB. During the installation process, you can substitute the default database name prefix with a literal value up to four characters long that conforms to your organization’s naming convention.

Note: The database name prefix must be the same for all database objects in the Siebel schema because the prefix identifies an object as belonging to the Siebel schema. Siebel utilities can recognize and use Siebel objects only if they follow Siebel naming conventions.
The second part of the name is a value, generally a four-digit number, that is generated by Siebel CRM and that must not be changed. (A number of databases that contain temporary tables are not assigned database names ending in numbers.) Where a four-digit number is assigned, this number in the database name is the same four-digit number used in the table space name that resides in this database. For more information on the table space name structure, see Table Space Naming Conventions.

Table Objects in Storage Control Files

The following example illustrates what the Table object looks like in the storage control file:

[Object 8005]
Type       = Table
Name       = S_ORG_EXT
Database   = SIDB1465
Tablespace = H1465000
Clobs    = No

The Table object illustrated in this topic translates into the following DDL output:

CREATE TABLE SIEBTO.S_ORG_EXT (
ROW_ID VARCHAR(15) NOT NULL, 
CREATED TIMESTAMP DEFAULT  NOT NULL, 
CREATED_BY VARCHAR(15) NOT NULL, 
...

VAT_REGN_NUM VARCHAR(30), 
DIRECTIONS LONG VARCHAR) IN SIEBTO.H1465000 /

Siebel Business Applications include one template for each encoding scheme when partitioning is used and one template for each encoding scheme for nonpartitioning.

Most tables use page-level locking by default, but a few use row-level locking. The following tables use row-level locking:

S_ESCL_ACTN_REQ
S_ESCL_LOG
S_ESCL_REQ
S_DOCK_TXN_LOG
S_DOCK_TXN_LOGT
S_DOCK_TXN_SET
S_DOCK_TXN_SETT

The following tables use table-level locking:

S_DOCK_INIT_ITEM
S_DOCK_INITM_XX

Note: LockSize is not defined on the table level but for the corresponding table space.

Table Space Objects in Storage Control Files

Siebel tables are created in one of four table space sizes, 4 KB, 8KB, 16 KB, and 32 KB. Most tables are defined within a 4-KB table space.

Note: You can create new table spaces, and you can regroup tables as required for your enterprise.

The following example illustrates what the segmented Tablespace object for the Siebel Repository looks like in the storage control file.

[Object 4340]
Type       = Tablespace
Name       = H1004000
Database   = SIDB1004
LockSize   = Page
Bufferpool = BP1
Define     = No
Partitions = 0

This Tablespace object translates into the following DDL output:

CREATE TABLESPACE H1004000 IN SIDB1004 
USING STOGROUP SYSDEFLT 
PRIQTY   48 SECQTY 1440 FREEPAGE 0 PCTFREE 17 
DEFINE NO 
SEGSIZE 32 
BUFFERPOOL BP1 LOCKSIZE PAGE LOCKMAX 0 COMPRESS YES /

Note: If you compare the input and the output, you can see that there are more options in the DDL output. These additional options occur because some values, such as FREEPAGE and PCTFREE, are taken from the default values.

Object numbering can change when you perform a storage control file extract. Therefore, object numbering can be different from one storage control file to another (except for default storage objects, for which numbering remains consistent).

Object Naming Conventions

Understanding Siebel object naming conventions allows you to keep track of existing Siebel objects and to successfully create and maintain your own objects.

If you understand Siebel naming conventions, you can more easily identify underlying data sets (VSAM files); for example, the name of the underlying data sets for the H0401000 table space might look like Q10E.DSNDBC.SIEBTO.H0401000.I0001.A001 where the fourth node represents the table space name.

Note: Siebel utilities can recognize and use custom objects only if the objects follow Siebel naming conventions; therefore, it is strongly recommended that you use the Siebel naming conventions for objects that you create.

Table Space Naming Conventions

The table space naming convention applies to all Siebel table spaces, whether you create the table spaces or the table spaces are shipped as part of your Siebel Business Application.

Table spaces are named with eight characters (for example, H0401000), consisting of three elements in the following order:

A leading letter (H, V, or S) designates the product group that the table belongs to.
- H represents Horizontal (also known as Core).
- V represents Vertical (also known as Siebel Industry Applications or SIA).
- S represents your custom tables and auxiliary indexes (those that you create for the Siebel application).
A four-digit number that is assigned to the base table and is stored in the GROUP_CD column of the S_TABLE table in the Siebel Repository, for example, 0401. (This number cascades down to table spaces, auxiliary tables, and auxiliary indexes.)
Table names are stored in the repository table S_TABLE, and table numbers are stored in the Group Code column (GROUP_CD) in S_TABLE. You can view Table and Group names in Siebel Tools by navigating to the Table menu, and then selecting the Object option.

Caution: You can edit the Group property for tables you create but do not edit the Group property for tables provided in the Siebel application; doing so can cause operating anomalies in the application.
The ending three-digit number which is always 000 for table spaces on the database storage layout.

Note: When defining a new table space for new custom tables and auxiliary indexes, use the prefix S, follow it with a unique four-digit number greater than 6000, then end with the three-zero suffix (000), for example, S6001000. Using this prefix and suffix maintains object naming conventions.

Naming Conventions for Auxiliary Objects

In the current release of Siebel Business Applications, the value of the CURRENT RULES special register is set to STD, which causes DB2 to execute SQL statements in accordance with the rules of the SQL standard. As a result, DB2 implicitly creates auxiliary objects and assigns names to them. For information on the naming conventions used by DB2 when creating auxiliary objects, refer to the IBM DB2 for z/OS documentation.

Naming Conventions for Partitioning Triggers

Partitioning Trigger names comprise eight characters (for example, PTH0401) that consist of three elements in the following order:

The leading two letters must be PT, to designate that this is a partitioning trigger.
The third letter (H, V, or S) designates the product group or component that the trigger belongs to.
- H represents Horizontal (also known as Core)
- V represents Vertical (also known as Siebel Industry Applications or SIA).
- S represents partitioning triggers used for Assignment Manager or other components.
The ending four-digit number uniquely identifies the trigger based on the table number, for example, 0401.
The trigger name is normally related to a corresponding table space name; for example, the trigger name PTH0401 relates to the table space name H0401000.

About Modifying the Database Layout

There are two methods you can use to modify the database storage layout for your deployment:

Method 1: Adjust the Storage Control File to Reflect Your Database Layout. Adjust the object definitions and options in the storage control file to reflect your database storage layout. When you subsequently perform a customized database installation or when you use database utilities, select your new storage control file when you are prompted for the storage control file input parameter. The resulting output file, schema.sql, contains the DDL that reflects your modifications. For information on modifying the storage control file, see About Modifying Storage Control Files.
If you choose to modify the database layout by adjusting the storage control file, you must generate an updated version of the storage.ctl file before running any of the database configuration utilities that require this file as input, for example, the Synchronize Schema Definition (ddlsync) and Migrate Repository (dev2prod) utilities. You can generate an updated version of the storage.ctl file using the Database Configuration Wizard. For information on this task, see Extracting a Storage Control File from the DB2 Catalog.
Method 2: Modify Generated DDL Using DB2 Tools. To use this method, perform a standard installation as described in About Standard Installations, and select the Generate DDL into Files installation mechanism. After the Database Configuration Wizard generates the DDL to a file named schema.sql, your DBA can directly edit the output DDL using native DB2 tools. Some DBAs prefer this method, because it involves using customary DB2 tools and does not involve manipulation of the storage control file.
When your DBA has modified the DDL, extract the storage control file so that it reflects the schema layout on the mainframe. This extraction saves time in the future as it means less manual work is involved when you run database utilities, such as Migrate Repository (dev2prod).

About Modifying Storage Control Files

You can modify a storage control file using either of the following methods:

Open the storage control file with a text editor program and edit it directly to adjust object definitions or options.
This approach is useful for making minor adjustments to the database layout but only use it if you have a thorough understanding of the storage control file structure. Otherwise, you could introduce errors into the file and affect the operation of your Siebel application.
Use the Siebel Database Storage Configurator (dbconf.xls).
This is a Siebel Microsoft Excel macro tool that allows you to edit the attributes of objects in the Siebel-provided storage control file templates. For further information, see Using the Siebel Database Storage Configurator.

Using the Siebel Database Storage Configurator

When you install Siebel Business Applications, the Siebel Database Storage Configurator file, dbconf.xls, is also installed. This tool helps you to configure your Siebel Schema by allowing you to import and edit the attributes of the Siebel-provided storage control file templates. You can also use this tool to configure an existing storage control file.

Dbconf.xls is installed in the DBSRVR_ROOT\db2390(Windows) or DBSRVR_ROOT/db2390 (UNIX) directory. To open dbconf.xls, you must have Microsoft Excel installed on your workstation. If your installation is UNIX, you must transfer dbconf.xls and the storage control file that you want to configure to your Microsoft Windows environment. Use binary FTP to transfer the dbconf.xls file.

After you edit a storage control file template using the Database Storage Configurator, save the file with a new name, then direct the Database Configuration Wizard to your newly configured file when prompted to specify a storage control file during installation (see Installing the Siebel Database on the DB2 Host). The output DDL is generated from the information in the (configured) storage control file that you specify.

Tip: It is recommended that you preserve the original dbconf.xls spreadsheet and save your modified spreadsheet with a new name. Saving the spreadsheet allows you to review your current database layout while continuing to work on the storage control file. For example, because the spreadsheet allows you to sort data, you can easily identify all table spaces that belong to the same database.

Modifying a Storage Control File Using the Database Storage Configurator

To modify a storage control file using dbconf.xls, perform the following procedure.

To configure one of the Siebel storage control files

Open dbconf.xls and select Enable Macros when prompted.
If you are not prompted to Enable Macros, verify that your Microsoft Excel security setting is Medium, as follows:
1. Launch Excel, and choose Tools, Macro, and then Security.
2. Set the security level to medium, and then restart your computer.
  When the dbconf.xls spreadsheet opens, the Home tab is active. Click the Functions tab to select one of the functions available in dbconf.xls. Click the Structures to display information about database objects.
Import the storage control file you want to use as your template:
1. With the Home tab active, click Import.
2. Go to the directory where your files with a ctl suffix are located.
3. Double-click the appropriate file.
  When the import process is completed, a message appears, stating that you have imported the storage control file successfully.
4. Click OK.
When the message, Please enter default values for your system appears, either type the values for the following parameters into the corresponding fields, or accept the default values.

Note: Replace variables that are preceded with the $ symbol with actual values for your deployment.
- Table Storage Group for Table
- Index Storage for Indexes
- 4K Buffer Pool Name
- 8K Buffer Pool Name
- 16K Buffer Pool Name
- 32K Buffer Pool Name
- Index Buffer Pool Name
- Database Name Prefix
- Encoding Scheme (ASCII or EBCDIC)
Click Set.
You are prompted to indicate whether or not you want to import row lengths. Select No.
You need to import row lengths if you intend to do either of the following:
- Convert your database from a non-Unicode to a Unicode encoding scheme
- Perform space calculations using dbconf.xls.
Select the Functions tab, then display the template defaults by clicking the Defaults tab.
The default values are the values that apply to an individual object in the storage control file if no other value is specified. You can change the default value for an individual object by navigating to the relevant tab (select the Structures tab, then the relevant object type tab) and changing the information displayed.

For example, when you create a new extension table, the Database Storage Configurator takes the Siebel default database (SIEB0000) and table space (SIEBTS00) values of the template used unless you explicitly define new values.
When you review the values under the object type tabs, for example, Tablespace, an empty parameter cell indicates that the cell takes the default value for the object.
Review the default values, and update them as required.
The parameters in the spreadsheet correspond to native DB2 syntax. For example, the parameters PriQty (Primary Quantity) and SecQty (Secondary Quantity) are used during the Siebel Schema creation.
Review the databases by clicking Structures, and then Database.
Be aware that the values visible on any tab also reflect the defaults for any objects at a lower level to the object for which the tab exists. Thus, if no locksize is shown for a table space under the Tablespace tab, then that table space takes the default locksize from the database object it belongs to. For example, locksize at the database object level applies to all table spaces created in a given database unless the locksize is specified explicitly in the table space object.
Review the table spaces by clicking Structures, and then Tablespace.
To review any partitions, select a row that displays a value in the Partitions column greater than zero, and then click Show TSPARTs. This displays the rows of corresponding partitions.
Edit the values as desired, and then move off the cell to save the value.
Review the tables by clicking Structures, and then Table.
When you have updated the template, click the Home tab, and then click Export.
The Export worksheets to storage control file screen appears.
Rename the storage control file, for example, my_storage_p.ctl, and save it. A message is displayed saying that the file will be validated.
Click OK.
When the validation process is completed, a message is displayed if the file contains any invalid values that you need to review. The following is an example of the type of message that might be displayed:
```
Please review index partitions which are marked in red
```
Click OK.
The errors in the file are displayed, highlighted in red.
Make a note of the object that is generating the errors. The relevant object type tab and the object are highlighted. Click OK.
Review and correct the object values that generated the validation errors. For example, if the values specified for a partitioning index partition are incorrect, do the following:
1. Select Structures, and then Partitioning Index.
2. Select the relevant index, then click the Show Index Parts button.
  The index partitions are displayed and the cells that contain invalid values are highlighted.
3. Amend the invalid values, then export the file again.
  You cannot export the file until the validation process completes without error.
When you have exported the file, click Home, and then Clean, to make the Database Storage Configurator ready for use with a different template.
A text box appears letting you know that you have cleaned all data successfully.

Validating Your Work

You can use the Database Storage Configurator Validate button at any time to validate the syntax in the dbconf.xls spreadsheet. Wait to validate until after you have finished editing to ensure your worksheets are consistent.

Note: The spreadsheet validation option is not a substitute for using the Database Configuration Wizard to validate the storage control file after you amend it. The Validate Storage File option of the Database Configuration Wizard validates the new storage control file against the physical layout of the database.

To validate your dbconf.xls worksheet

After updating values for any of the template defaults, click the Validate button.
The Database Storage Configurator validates the new value; if the value is incorrect, it is highlighted in red. A text box also appears prompting you to review the invalid settings.
Update any incorrect value, move off the cell, and validate the value again.
The red highlighting disappears when the validation shows that the value is correct.

Working with CLOBs Using the Database Storage Configurator

Your Siebel application is delivered with the objects needed to create character large objects (CLOBs). You can use these objects to create CLOBs at the table level only, to avoid excessive processing overhead.

The following procedure describes how to activate a CLOB.

To activate a CLOB

Launch the Database Storage Configurator.
This task is described in Modifying a Storage Control File Using the Database Storage Configurator.
Click Structures, and then the Table tab.
Review the contents of the CLOB column.
To activate a specific CLOB, type Yes in the appropriate row in the CLOB column, and then save the value by stepping off the cell.

Note: The Auxiliary Tablespace tab displays a table name that is used as a pointer to a real auxiliary table space. The parameters displayed under this tab are the only parameters you can change for the auxiliary object.
Make any other changes needed to the template you are using, then verify and save the changes.
For information on this task, see Modifying a Storage Control File Using the Database Storage Configurator.