IBM 6.1.X manual For those tables which require reorganization, we use the command

optimizer to select an efficient access plan for complex SQL, particularly for queries of the JCR database.

We have determined a technique that has the same convenience of the reorgchk command and provides the detailed statistics preferred by the optimizer.

db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T'"

db2 -v -f "runstats.db2"

The first command is used to create a file, runstats.db2, which contains all of the runstats commands for all of the tables. The second command uses the db2 command processor to run these commands.

To determine which tables might benefit from reorganization, we use the command:

db2 reorgchk current statistics on table all > "reorgchk.txt"

For those tables which require reorganization, we use the command:

db2 reorg table tableschema.tablename

to reorganize the table based upon its primary key.

You should also ensure that your database servers have adequate numbers of disks. Multiple disks allow for better throughput by the database engine. Throughput is also improved by separating the database logs onto separate physical devices from the database.

You should ensure that the database parameter MaxAppls is greater than the total number of connections for both the datasource and the session manager for each WebSphere Portal application server instance. If MaxAppls is not large enough, you will see exceptions in your connection pools.

You should use System Managed Storage (SMS) for temporary table spaces to benefit complex SQL which require temporary tables to compute their result sets. This saves time in buffer writes and improves disk utilization.

Database performance is very important for obtaining good performance from WebSphere Portal. The maintenance tasks and practices mentioned here were found to be critical to the performance and correct operation of WebSphere Portal in our lab environment. Additional database maintenance and tuning may be needed in your production environments. For further information on DB2 administration and tuning, refer to the DB2 Information Center.

1 8

W E BS P HE R E P O R T AL V 6 . 1 T U N I N G G U I D E