ATG Datasource Debugging / Print SQL statements for repository queries

Here are the steps to debug Data-source problems.

1.    Datasource Debugging on WebLogic or WebSphere.

To add datasource debugging, first rename the datasource used by your applications (usually JTDataSource,properties). Then create a new JTDataSource.properties file with the following contents:

$class=atg.service.jdbc.WatcherDataSource
dataSource=/atg/dynamo/service/jdbc/DirectJTDataSource
showOpenConnectionsInAdmin=false
logDebugStacktrace=false
loggingDebug=false
monitored=false
loggingSQLError=true
loggingSQLWarning=false
loggingSQLInfo=false
loggingSQLDebug=false 

Second, create a DirectJTDataSource.properties file with the following contents:

$class=atg.nucleus.JNDIReference
JNDIName=java:/ATGSolidDS 

Where ATGSolidDS is replaced by the JNDI name of your application server data source.

Place both properties files in your localconfig directory. To enable data source debugging, set the monitored property and the loggingSQLInfo property in the JTDataSource.properties file to true.

Note: Due to the potential performance impact, this feature should be used only in a development environment. Do not enable SQL debugging in a production site.

2.    Datasource Debugging on JBoss.

The default JTDataSource for JBoss allows you to monitor and log data source information for debugging purposes. It does this using the WatcherDataSource class. A WatcherDataSource “wraps” another data source, allowing debugging of the wrapped data source. For example:

/atg/dynamo/service/jdbc/JTDataSource.properties
$class=atg.service.jdbc.WatcherDataSource
# The actual underlying DataSource.
dataSource=/atg/dynamo/service/jdbc/DirectJTDataSource

Note: Due to the potential performance impact, the features described here should be used only for debugging in a development environment. Do not use datasource logging in a production environment unless absolutely necessary.

WatcherDataSource Configuration

The default WatcherDataSource configuration is:

showOpenConnectionsInAdmin=false
logDebugStacktrace=false
loggingDebug=false
monitored=false
loggingSQLError=true
loggingSQLWarning=false
loggingSQLInfo=false
loggingSQLDebug=false 

This default configuration logs the following information:

• currentNumConnectionsOpen
• maxConnectionsOpen
• numGetCalls
• averageGetTime
• maxGetTime
• numCloseCalls
• averageCloseTime
• maxCloseTime
• averageOpenTime
• maxOpenTime.
   
  For additional debugging information, you can set the following properties to true:
  • showOpenConnectionsInAdmin—Lists currently open connections, along with the amount of time they have been held open and the thread that is holding them open. This information is useful for identifying Connection leaks. If logDebugStacktrace is also true, then stacktraces are displayed as well.
    Note: This momentarily prevents connections from being obtained or returned from the DataSource, so severely affects performance.
  • loggingDebug—Logs debug messages on every getConnection() and close() call. These messages include interesting information such as sub-call time, number of open connections, and the calling thread. If logDebugStacktrace is also true then a stacktrace is logged as well.
  • logDebugStacktrace—Creates stacktraces on each getConnection() call. This allows the calling code to be easily identified, which can be useful when trying to find Connection leaks, code that is holding Connections open for too long, or code that is grabbing too many Connections at a time.
    Note: This is done by generating an exception, which affects performance.
  • monitored—Gathers additional connection statistics and SQL logging.