Database performance
Perform a reorg check for databases
- Perform a reorg check for databases
- Reduce Database Deadlock
- Improve performance of DB2 database
- Increasing the connection pool size for Member Manager
Performing a reorg check for databases
Database performance is important to the overall performance of WebSphere Portal. Some database servers require administrative action to maintain peak performance after a large number of entries have been added to the database tables, such as after a database migration. Because entries are added to the database for every first-time user and whenever portal resources are created, database performance should be periodically monitored.
For DB2 Universal Database Enterprise Edition, the REORGCHK command is used.
Reduce Database Deadlock
To reduce deadlock with the WebSphere Portal database, for DB2 V8.1 FP4 or higher users only (skip this step if you use DB2 on OS/390 or OS/400), enter the following commands on the DB2 server with DB2 instance owner privileges (for Windows, use the DB2 Command Window)...
db2set DB2_EVALUNCOMMITTED=YESIn order to apply all of these settings, you need at least DB2 Fix Pack 4 applied.
Improve DB2 database performance
The performance of DB2 databases of WebSphere Portal systems can be improved by specifying the DB2_INLIST_TO_NLJN parameter in DB2. This parameter influences the DB2 statement optimizer to generate more appropriate execution plans for WebSphere Portal systems.
DB2 supports a special parameter in its DB2 registry for changing the optimizer's behavior. To apply this parameter, follow these steps:
- Log in as the instance owner (for example, db2inst1) or any other user with DB2 SYSADM privilege.
- Execute the command:
db2set DB2_INLIST_TO_NLJN=YES- You can check the parameters that are set by executing the command:
db2set- To make the setting effective, restart the DB2 instance.
Applying this parameter causes the DB2 optimizer to rewrite the query in a way that transforms the IN list to a VALUES clause, which is then joined with the base table using a standard nested loop join. This enables the index to be used on the base table, which potentially results in a significantly better query performance in WebSphere Portal systems using DB2 for Multiplatforms.
Additional information: WebSphere Portal systems using DB2 for Multiplatforms (Windows, Unix, and OS/400) frequently issue SQL queries with large IN lists, for example...
SELECT ... FROM ... WHERE OID IN (?, ?, ?, ... ?)Since parameter markers (?) are used, the DB2 optimizer does not know the values in the IN list. In most cases, it will decide to do a hash join. However, this can result in a table scan that becomes problematic in terms of performance and concurrency, especially with larger tables.
Increasing the connection pool size for Member Manager
If you use DB2 with WebSphere Portal, the performance of SQL statements can be impaired after many updates, deletions, and insertions to the database instance for Member Manager. Follow these steps to improve performance:
- Follow these steps to increase the connection pool size for the Member Manager database instance:
- Log in to the Administrative Console for WebSphere Application Server.
- In the left navigation area, navigate to Resources-JDBC Providers-wpsdbJDBC-Data Sources.
- In the list of data sources, click wmmDS.
- Click the Connection Pooling tab.
- Set the Maximum pool size field. For guidance, see the Tuning Guide in the WebSphere Application Server V5 Information Center.
- For the WebSphere Portal database instance (default name typically wpsdb) and for the Member Manager database instance (default name typically wmmdb), run the following command to obtain the current statistics for the data:
db2 reorgchk update statistics on table all
See also
WebSphere is a trademark of the IBM Corporation in the United States, other countries, or both.
IBM is a trademark of the IBM Corporation in the United States, other countries, or both.