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

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;

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.

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;selectMethod=cursor;

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.

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

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.