IBM Support

Beware of the legacy SQL Server system property mxe.db.rowcount

Technical Blog Post


Abstract

Beware of the legacy SQL Server system property mxe.db.rowcount

Body

The mxe.db.rowcount property is an optional system property in Maximo for SQL Server databases.  This property is a setting on the jdbc driver to limit the number of rows affected by a transact-sql statement. The property was added to help prevent row locking that some customers encountered in releases prior to 7.1.

Use the mxe.db.rowcount property with caution because users may not be aware of the effects of this setting.

The setting of rowcount causes most transact-sql statements to stop processing when the number of affected rows is equivalent to the rowcount setting. The property applies to select, delete, update and insert statements as well as triggers. This setting affects only transact-sql issued through the jdbc connection; it is not set at the database server level.

If the number of rows satisfied by an update or delete statement is greater than the value specified by the mxe.db.rowcount property, some rows will not be updated or deleted as needed.

For example if the value of the mxe.db.rowcount is 15,000 and a SELECT/DELETE/UPDATE/INSERT statement is executed, it is executed only on the first 15,000 records that satisfy the statement. Therefore, if 16,000 rows satisfy the statement, then the sql statement will not be executed against a thousand records.

Recommendation: If you are using Maximo 7.1 or later, set mxe.db.rowcount to 0. Setting mxe.db.rowcount to 0 turns off the rowcount limits. After you change this setting, please make sure that you stop and restart the Maximo server to ensure all jdbc connections are dropped and recreated. Performing a live refresh on the mxe.db.rowcount property is not sufficient. Maximo keeps a pool of jdbc connections; and if these connections were created with a previous rowcount value, they are not updated.

Code changes in Maximo 7.1 along with setting the system property to mxe.db.sqlserverPrefetchRows=200 resolves the row locking issue.

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

UID

ibm11133583