IBM Support

Maximo Asset Management on Microsoft SQL Server: Avoiding Out of Memory and Performance Problems

Troubleshooting


Problem

From Maximo 7.5 forward, Maximo/TPAE uses Microsoft's JDBC driver. By default, the Microsoft native JDBC driver fetches large amount of data into its client side JDBC connection, causing potential memory problems.

Symptom

Out of memory, performance, slow queries

Cause

Change in JDBC driver needing new tuning parameters

Resolving The Problem

Maximo/TPAE switched to using Microsoft native JDBC driver in Maximo 7.5 forward. By default, the Microsoft native JDBC driver fetches large amount of data into its client side JDBC connection. As a result, Maximo client side connection in the application server may run out of memory for large result sets, for example, queries that fetch a large number of records. This can happen even when the end user does not fetch all rows onto the client browser screen.
Step 1:
To avoid out of memory problems, set the following properties in Maximo/TPAE. Some of these settings are needed for the database connection, so shut down all Maximo application servers that connect to that database:

Connect to the Maximo database with a database client tool. Execute the following SQL commands:

update maxprop set maximodefault = 'TYPE_FORWARD_ONLY' where propname = 'mxe.db.resultsettype';
update maxpropvalue set propvalue = 'TYPE_FORWARD_ONLY' where propname = 'mxe.db.resultsettype';

commit;
Step 2:

In the Maximo System Properties application, modify the following system properties:

mxe.db.sqlserverPrefetchRows - change from 0 to 200. Save the value, then select the row and click on Live Refresh.

This value sets the maximum number of rows fetched onto a clients screen for list pages and dialogs. If larger number of records are needed, this value can be increased. If a set to a high value such as 5000, then too many rows will be fetched into client side, whether or not the end user needs to see so many records.

You can also adjust SQL queries to reduce the total number of results rather than using a high value for this property.

Step 3:

Open the <maximo_root>\applications\maximo\properties\maximo.properties.orig file.

Make sure the mxe.db.url property is in the below format:

mxe.db.url=jdbc:sqlserver://;<host>:<port>;databaseName=<database>;integratedSecurity=false;sendStringParametersAsUnicode=false;

Replace <host> <port> <database> with the appropriate values of DB Server Hostname, IP Port and Name of the Maximo Database in your environment respectively.

If you already have <host> <port> <database> values, make sure the new additional parameters are added and set correctly.

Save the file over the existing maximo.properties file.

Start an administrative command prompt. Navigate to (default) C:\IBM\SMP\maximo\tools\maximo. Execute encryptproperties.bat.

Step 4:

Rebuild Maximo ear file and redeploy on the application server(s).

Step 5:

Restart Maximo server and the above changes will take effect.

NOTE: If SQL "order by" clause is used as part of any SQL statement, users might notice performance issues. For these order by sql queries, a non-clustered index with the order by column should be created to improve the performance. If the query contains large result set, without the non-clustered index on the order by column, SQL Server driver connection will run into out-of-memory.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"System Related","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.5;7.6;7.6.1","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
13 September 2023

UID

swg21592716

Page Feedback