Performance Tuning for Content Manager

184 Performance Tuning for Content Manager

'.' concat tabname concat 'with distribution and detailed indexes all'

from syscat.tables where tabschema='schema' and type='T'">> fname.bat

echo db2 connect reset >> fname.bat

db2 connect reset

echo db2rbind db –l bind.log all /u userid /p password >> fname.bat

Change db to the name of your database and change userid and password for

your system values. Change the schema name based on your system and be

sure to use capital letters.

Our recommendation

Run the generated file fname.bat script daily for the first few weeks and during

initial database loading. After that, run it weekly or at least monthly as routine

performance maintenance, and whenever your database has changed

significantly in size. This should be part of your database administration.

8.3.4 Reorganizing tables through reorg

Over a period of time, after many insertion, deletion, and updating to database

table data, logically sequential data may be on non-sequential physical data

pages. This causes the database manager to perform additional read operations

to access data. Additional read operations are also required if a significant

number of rows have been deleted. In such a case, you need to reorganize the

table to re-cluster the data to match the primary index and to reclaim space. You

can reorganize the system catalog tables as well as database tables.

Reorg required?

The DB2 command reorgchk can be used to suggest if a reorganization of the

tables and indexes is warranted. You can run the command from a script and

schedule to run the script when the system usage is low. You can use reorgchk

to recalculate the table statistics using the “update statistics” option but it does

not give the level of control over recalculating the index statistics that the

runstats command does. Run runstats before running a reorgchk.

To see whether you need to reorganize a table, use the following command from

a DB2 command line window (after connecting to the database):

db2 reorgchk current statistics on table all > reorgchk.txt

Attention: Library Server relies heavily on DB2 stored procedures and

precompiled access modules to perform its functions. This is why runstats is

so important for maintaining the performance of a Content Manager. To learn

more about how Library Server works, see 5.5, “Library Server” on page 106

for more details.