DAS Query performance in Sentinel is very slow with Microsoft sql server

  • 7002473
  • 27-Jan-2009
  • 26-Apr-2012

Environment

Novell Sentinel 6.0 Support Pack 2
Novell Sentinel 6.1
Microsoft SQL 2005

Situation

When querying the database via DAS Query (Offline Queries, for example) the performance is very slow.  Intercepting the query and running it manually via a generic database access tool does not have the same slowness exhibited for the same query and time period.  Other connections via third-party JDBC drivers, including JTDS, do not exhibit this same slowness when running SQL queries against the database.

Resolution

There is a reported bug when using prepared SQL statements are used connecting to Microsoft's mssql server.  The workaround is to disable the use of prepared statements when using mssql.

To disable Sentinel's use of prepared statements perform the following steps:

Create a new file in $ESEC_HOME/config named jdbcoverride.properties ensuring that permissions are set properly for the system user to access the file.  In this file put the following two lines:

useCursors=true
prepareSQL=0

Make a backup of the configuration.xml file in $ESEC_HOME/config and then modify the original file adding the following Java Virtual Machine parameter to the line for the DAS Query service:

-Desecurity.jdbc.config.file=../config/jdbcoverride.properties

The resulting line may look like the following (new section in bold):

<process component="DAS" depends="UNIX Communication Server,Windows Communication Server" image="&quot;$(ESEC_JAVA_HOME)/java&quot; -server -Dsrv_name=DAS_Query -Xmx128m -Xms64m -XX:+UseParallelGC -Xss136k -Xrs -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=../log/DAS_Query.hprof -Duser.language=en  -Djava.net.preferIPv4Stack=true -Dfile.encoding=UTF8 -Desecurity.cache.directory=../data/das_query.cache -Desecurity.dataobjects.config.file=/xml/BaseMetaData.xml,/xml/WorkflowMetaData.xml -Djava.util.logging.config.file=../config/das_query_log.prop -Djava.security.auth.login.config=../config/auth.login -Djava.security.krb5.conf=../config/krb5.conf -Desecurity.execution.config.file=../config/execution.properties -Dcom.esecurity.configurationfile=../config/configuration.xml-Desecurity.jdbc.config.file=../config/jdbcoverride.properties-jar ../lib/ccsbase.jar ..//config//das_query.xml" min_instances="1" name="DAS_Query" post_startup_delay="20" type="container" working_directory="$(ESEC_HOME)/data"/>

Save the file and restart the entire Sentinel service on the server.  Queries executed via DAS Query will no longer use prepared statements and performance should be improved.