Performance Tuning for Content Manager

178 Performance Tuning for Content Manager

For example, for a simple (no child components) item type myitemtype, with

user-defined attributes myattr1 and myattr2, perform the following steps to create

indexes on the attributes:

1. Query to get numeric values AAAA for myattr1 and BBBB for myattr2:

db2 select keywordcode, keywordname from icmstnlskeywords where

keywordclass=1 and keywordname in ('myattr1','myattr2')

2. Query to get numeric value CCCC for myitemtype:

db2 select c.componenttypeid from

icmstnlskeywords k, icmstcompdefs c where

k.keywordname='myitemtype' and k.keywordclass=2 and

k.keywordcode=c.itemtypeid and c.comptypeclass=0

3. Create the index using the obtained numeric values:

db2 create unique index mynewindex1 on icmut0<CCCC> (

ATTR000000<AAAA> asc,

ATTR000000<BBBB> asc,

versioned asc,

componentid asc

) include (itemid, compkey)

(This index is unique, even if myattr1 and myattr2 are not.)

4. If you want the myattr1 and myattr2 combination to be unique within

myitemtype, create this index as well:

db2 create unique index mynewindex2 on icmut0<CCCC>001 (

ATTR000000<AAAA> asc,

ATTR000000<BBBB> asc)

After creating a new index, either manually or through the DB2 Control Center,

run the runstats and rebind commands to make sure that the DB2 UDB

optimizer can effectively use the new index.

See 8.3.3, “Keeping database statistics and execution plans up to date through

runstats/rebind” on page 183 for details about how to run the processes.

8.2.6 Create indexes for document routing

If you use document routing with “filter on owner,” adding this index to the Library

Server database might improve performance:

db2 create index icmux002040017x on icmut00204001(

itemid asc,

assigneduserid asc);