IBM Content Manager, Version 8.5

Planning custom table spaces for item type components and indexes to improve performance

You can customize your library server database by setting custom table, index, and large object (LOB) table spaces for item type root and child components, indexes, and LOB attributes. Using custom table spaces can increase the efficiency of database management and can increase database performance.

In versions of Content Manager EE earlier than Version 8.4.2, when item types are created, the library server creates all component tables, indexes, and LOB attributes in a single, predefined table space:

For DB2 Universal Database™, the default table space for all components, indexes, and LOB attributes is ICMLFQ32.
For Oracle, the default table space for all components, indexes, and LOB attributes correspond to the Oracle database user's default table space (then the Content Manager EE configuration manager creates objects for the table spaces that you specified). If you used the database configuration assistant (DBCA) templates to create the library server database and schema, then this value is set to the value that was specified in the ICM_LS_DEFAULT_TBLSPC field of the File Location Variables page in the DCBA wizard.

For these earlier versions of Content Manager EE, you must use the deferred DDL execution feature and the import and export function to change component and index table spaces and move the data to different tables.

In Content Manager EE, you can use the system administration client to set custom table spaces for new item type root and child components and indexes, including the table, index, and LOB attributes. You can also set custom table spaces for new child components of item types that existed in previous versions of Content Manager EE.

For a Content Manager EE system that uses the Oracle database, you can modify the table spaces, with some limitations on the modifications that you can perform.

The following tables show how you can use custom table spaces for item type root and child components, indexes, and LOB attributes:

For a DB2 Universal Database database, you can set table, index, and LOB custom table spaces for new root and child components, but after the table spaces are set, you cannot modify them. You cannot set table spaces for root and child components that existed in versions of Content Manager EE earlier than Version 8.4.2 through the system administration client.

Table 1. Setting custom table spaces for item type root and child components, indexes and LOB attributes with Content Manager EE Enterprise Edition and DB2 Universal Database
For this object	Can you set default table spaces?	Can you modify the default table space?
Existing root component (Version 8.4.1 and earlier)	No	No
Existing child component in an existing root component	No	No
New child component in an existing root component	Yes, for the child component table, index, and LOB	No
New root component	Yes, for the root component table, index, and LOB	No
New child component in a new root component	Yes, for the child component table, index, and LOB	No

For an Oracle database, you can set table, index, and LOB custom table spaces for new root and child components and you can set the index and LOB table spaces for existing root and child components (root and child components that existed in versions of Content Manager EE earlier than Version 8.4.2). You can also modify the default index and LOB table space for a root or child component for any new indexes or LOB attributes that are added after the default is originally set. After modifying the default value of index or LOB table space, note that the new table spaces will only affect new LOB attributes and user defined indexes rather than the ones already created.

Table 2. Setting custom table spaces for item type root and child components, indexes and LOB attributes with Content Manager EE Enterprise Edition and Oracle
For this object	Can you set default table spaces?	Can you modify the default table space?
Existing root component (Version 8.4.1 and earlier)	Yes, for the root component index and LOB	Yes, index and LOB only
Existing child component in an existing root component	Yes, for the child component index and LOB	Yes, index and LOB only
New child component in an existing root component	Yes, for the child component table, index, and LOB	Yes, index and LOB only
New root component	Yes, for the root component table, index, and LOB	Yes, index and LOB only
New child component in a new root component	Yes, for the child component table, index, and LOB	Yes, index and LOB only

Considerations for creating your table spaces

In order to fully utilize this feature, map out your table spaces before creating item types in Content Manager EE. Consider how your data is structured, how many item types and components you might create, and what types of activities are going to occur on the item types and components. The use of custom table spaces for item types can improve database performance and management in some environments. Examples:

Create one set of table spaces (table, index, LOB) for all components in a single item type: This approach is the easiest to maintain because you can easily back up, restore, and monitor the whole item type at the same time. For best maintenance, give all of the table spaces the same characteristics.
Create one set of table spaces (table, index, LOB) per component: This approach organizes your component types so that you can monitor and manage them separately, based on their size and characteristics (such as whether they contain LOB columns).

Using a single table space for all item type components, indexes, and LOB attributes can cause performance problems for large-scale Content Manager EE systems, and some individual item types can themselves be large. Therefore, putting each component in its own table space can result in improved database performance. In addition, if multiple custom table spaces are used, then the database administrator can monitor, back up, and restore table spaces based on the individual requirements of the item types and components. Finally, some item types are more active than others (for example, an item type for all scanned documents), and customizing a table space for the item type would enable the database administrator to span the table space across physical devices to improve performance of the Content Manager EE system.

Recommendation for a DB2 database: Choose the bufferpool size carefully so that the rows of the longest component can contain one page of the bufferpool size.

Oracle users:

Create the BASIC_STORAGE preference for any custom table space that you intend to store the index table for an Oracle text index in (see download.oracle.com/docs/ for more information).
By default, Content Manager EE stores LOB attributes less than or equal to 8k inline. LOB attributes larger than 8k are stored with a CHUNK size of 32k in either the Oracle default or a table space of your choice.
Recommendation: If you need more control over these storage characteristics, see the deferred DDL execution feature for Oracle.
Ignore the two SYS_Cnnnnnnn constraint indexes in the Index properties window, as Oracle automatically creates them internally using table table space (not the index default table space you specified previously).

See your database manual for best practices on creating table spaces.

Default library server table spaces

The following system and components table are predefined by Content Manager EE during configuration. Only an experienced database administrator should change their physical properties.

Table 3. Library Server default table spaces (created during configuration)
Bufferpool	Table space	Description
icmlsmainbp32	icmlfq32	Holds all the large, persistent, "frequently used" tables (notably, all the item type tables). When a new item type is defined, the system administration client defines the new tables in this table space. If you customize the DDL, then define this table space across multiple containers for performance and scalability.
icmlsmainbp32	icmlnf32	Holds the large but less-frequently used tables, such as event logs, versions, and replicas. If you don't use any of these features, then this table space does not need much space.
icmlsvolatilebp4	icmvfq04	Holds the "volatile" tables, those whose size is usually small but which can vary widely, and for which both updates and deletes are very common. For example, the tables associated with document routing "in progress" items and checked out items. Putting this on a separate physical disk will help performance.
icmlsfreqbp4	icmsfq04	Holds all the small "frequently used" but seldom updated tables (such as system information, user definitions, and ACLs), that do not take up much space but that are always needed in memory for good performance.
cmbmain4	cmbinv04	Holds the federated inventory tables.

Automatic storage tablespaces for DB2 Universal Database

Starting in Version 8.5, the Content Manager EE configuration now creates automatic storage tablespaces for the library server database and resource manager database instead of DMS (Database Managed Space) or SMS (System Managed Space).

Feedback