Maximo 7.5 on SQL Server out of memory and performance problems
Maximo/TPAE switched to using Microsoft native JDBC driver in Maximo 7.5 onwards. By default, the Microsoft native JDBC driver fetches large amount of data into its client side JDBC connection causing potential memory problems.
Out of memory, performance, slow queries
Change in JDBC driver needing new tuning parameters
Resolving the problem
Maximo/TPAE switched to using Microsoft native JDBC driver in Maximo 7.5 onwards. 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 tends to run out of memory for large result sets i.e. queries with large number of records. This can happen even when the end user really does not fetch all rows onto the client screen.
In order to avoid this out of memory problem, the following properties should be set in Maximo/TPAE.
Some of these settings are needed for the database connection and hence shutdown Maximo application server.
Connect to Maximo database through 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';
The default value set by installation is TYPE_FAST_FORWARD, this value should be changed to TYPE_FORWARD_ONLY.
update maxprop set maximodefault = '200' where propname = 'mxe.db.sqlserverPrefetchRows';
update maxpropvalue set propvalue = '200' where propname = 'mxe.db.sqlserverPrefetchRows';
The mxe.db.sqlserverPrefetchRows by default is set to 0. Update to a value such as 200. This is the maximum number of rows fetched onto a clients screen for list pages and dialogs. If larger number of rows are needed, this number can be increased. Note that, if a high value such as 5000 is used, then too many rows will be fetched into client side whether the end user really needs all the rows. Adjust the sql query and reduce the results instead of using a high value for this property.
Open the <maximo_root>\applications\maximo\properties\maximo.properties file.
Make sure the mxe.db.url property is in the below format:
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 maximo.properties file.
APAR IV12954 - is required to set and use the above additional jdbc properties.
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.