Portal Express, Version 6.0
Operating systems: i5/OS, Linux, Windows

---

 

Database performance

This topic has techniques that can improve the database performance for IBM^® WebSphere^® Portal Express.

• Performing a reorg check for databases

• Reducing Database Deadlock

• Improving DB2 database performance

• Increasing the connection pool size for Member Manager

For additional tips to improve WebSphere Portal Express performance, see the WebSphere Portal Express Tuning Guide. Tuning guides for various versions of WebSphere Portal Express are available on the Product Documentation Web site: http://www.ibm.com/websphere/portal/library

 

Performing a reorg check for databases

Database performance is important to the overall performance of WebSphere Portal Express. 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 resources are created, database performance should be periodically monitored.

The administrative action required varies depending on the database server. For IBM DB2 Universal Database™ Enterprise Server Edition, the REORGCHK command is used. For other databases, see the product's documentation.

If using a JCR database or if the REORGCHK command is insufficient for your needs, the following approach provides the detailed statistics that the optimizer requires:

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.

 

Reducing Database Deadlock

Follow these steps to reduce deadlock with the WebSphere Portal Express database:

• DB2 Version 8.1 FP4 or higher users only: Enter the following commands on the DB2 server with DB2 instance owner privileges (for Windows, please use the DB2 Command Windows):

              db2set DB2_EVALUNCOMMITTED=YES
              db2set DB2_INLIST_TO_NLJN=YES

  In order to apply all of these settings, you need at least DB2 Fix Pack 4 applied.

 

Improving DB2 database performance

The performance of DB2 databases of WebSphere Portal Express 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 Express systems.

DB2 supports a special parameter in its DB2 registry for changing the optimizer's behavior. To apply this parameter, follow these steps:

1. Log in as the instance owner (for example, db2inst1) or any other user with DB2 SYSADM privilege.

2. Execute the command: db2set DB2_INLIST_TO_NLJN=YES

3. You can check the parameters that are set by executing the command: db2set

4. 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 Express systems using DB2 for Multiplatforms.

 

Additional information: WebSphere Portal Express systems using DB2 for Multiplatforms 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 Express, 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:

1. Follow these steps to increase the connection pool size for the Member Manager database instance:

   1. Log in to the Administrative Console for WebSphere Application Server.

   2. In the left navigation area, navigate to Resources-JDBC Providers-wpsdbJDBC-Data Sources.

   3. In the list of data sources, click wmmDS.

   4. Click the Connection Pooling tab.

   5. Set the Maximum pool size field. For guidance, see the Tuning Guide in the WebSphere Application Server Version 5 information center.

2. For the WebSphere Portal Express 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

 

Related information

• Caching

• Nested groups

• Performance improvements

 

Parent topic:

Tuning

 

Related concepts
Search

---