dSeries (WA DE) may not show agent status in Topology.

Products

• CA Workload Automation DE

Issue/Introduction

When ESP dSeries uses MS SQL 2019 or above, some DB errors may result in user permissions.

The tracelog may have errors like this:

202XXXXX 00:00:12.345 [relationaldatabase] [ERROR] Timer-0: [2025-XX-XX_00:00:12.345] SQL Exception for query: SELECT ID FROM ESP_SM_SERVER_STATUS WHERE GUID = ? FOR UPDATE; the exception is: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[sqljdbc42.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[sqljdbc42.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600) ~[sqljdbc42.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522) ~[sqljdbc42.jar:?]

 

 

Environment

ESP dSeries: 12.3 or above

Database: MS-SQl 2019 or above

OS: Any

Cause

The JDBC requires select cursor option.

Resolution

Add the “selectMethod=cursor” to the JDBC in db.properties. URL. Example:

jdbc.URL=jdbc:sqlserver://dbserver.example.com;port=1234;DatabaseName=deDB;selectMethod=cursor;integratedSecurity=true;domain=example.com;authenticationScheme=NTLM

 

Additional Information

In some cases the SQL DB may only allow Windows / AD authentication.  Add the following parameters to JDBC:

integratedSecurity=true;domain=example.com;authenticationScheme=NTLM

 

The above parameters may be needed if dSeries is on Linux OS connecting to SQL database.