Environment
Novell Sentinel 6.0 Support Pack 2
Novell Sentinel 6.1
Microsoft SQL 2005
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=""$(ESEC_JAVA_HOME)/java" -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.
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=""$(ESEC_JAVA_HOME)/java" -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.