Problem
If you encounter issues with the Content Store or Audit database connection, it's sometimes recommended to trace the JDBC communication between Cognos8 and the database to figure out what happens. This document only covers Microsoft SQL Server as database system.
The JSQLConnect JDBC driver is located in the {COGNOS_ROOT}\webapps\p2pd\WEB-INF\lib directory. Do not change this file! This is not necessary for activating the tracing. Be aware that running the trace may cause some strange issues. Only do the trace, if you have serious issues with your database connection via JDBC.
Solution
The following steps describe how to turn on tracing for the SQL JDBC driver:
1. Stop Congos8
2. Open Cognos Configuration
3. Rename the name for the Content Store \ Audit database by appending "&logfile=JSQLtrace.txt" (without the double quotes)
4. Save the configuration
5. Start Cognos8
The trace file is written to the {COGNOS_ROOT}\bin directory
The trace file looks like this:
22010-05-03 14:23:16.562 Logfile: created by:class com.jnetdirect.jsql.JSQLDriver
2010-05-03 14:23:16.562 -------- RUNTIME --------
2010-05-03 14:23:16.562 java.vm.version:2.3
2010-05-03 14:23:16.562 file.encoding:Cp1252
2010-05-03 14:23:16.562 java.version:1.5.0
2010-05-03 14:23:16.562 os.name:Windows XP
2010-05-03 14:23:16.562 os.version:5.1 build 2600 Service Pack 3
2010-05-03 14:23:16.562 user.timezone:Europe/Berlin
2010-05-03 14:23:16.562 java.class.path:.;cclcfgapi_mcf.jar;cogconfig.jar;cogconfigi.jar;dom4j.jar;xercesImpl.jar;xml-apis.jar;cclcfgapi.jar;jcam_crypto.jar;i18nj.jar;icu4j.jar;commons-httpclient.jar;commons-logging.jar;CognosIPF.jar;log4j-1.2.8.jar;jcam_jni.jar;jdxslt.jar;ant.jar;jcam_config_test.jar;cclcoreutil.jar;CognosCCL4J.jar
2010-05-03 14:23:16.562 java.class.path:.;cclcfgapi_mcf.jar;cogconfig.jar;cogconfigi.jar;dom4j.jar;xercesImpl.jar;xml-apis.jar;cclcfgapi.jar;jcam_crypto.jar;i18nj.jar;icu4j.jar;commons-httpclient.jar;commons-logging.jar;CognosIPF.jar;log4j-1.2.8.jar;jcam_jni.jar;jdxslt.jar;ant.jar;jcam_config_test.jar;cclcoreutil.jar;CognosCCL4J.jar
2010-05-03 14:23:16.562 runtime.totalMemory:22282240
2010-05-03 14:23:16.562 runtime.freeMemory:3369016
2010-05-03 14:23:16.562 -------- START OF LOG --------
2010-05-03 14:23:16.593 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:null] Starting physcial connection attempt for connection id 1 for host:localhost database:ContentStore83 SQL6.5:false
2010-05-03 14:23:16.734 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:668d6] select SCHEMA_NAME()
2010-05-03 14:23:16.75 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:668d6] select system_user
2010-05-03 14:23:16.906 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:668d6] select @@OPTIONS
2010-05-03 14:23:16.921 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:668d6] exec sp_tables @table_qualifier = null, @table_owner = 'dbo', @table_name = 'CMOBJECTS'
2010-05-03 14:23:16.921 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:668d6] select count(*) from CMOBJECTS
2010-05-03 14:23:16.921 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:668d6] select PROPNAME, PROPVALUE from CMSYSPROPS
2010-05-03 14:23:16.921 [Thread[Thread-6,6,main], IO:c1c1c, Dbc:668d6] Closing connection ID:1
Keep in mind that the activated tracing is quite CPU intensive.
The system performance may be affected due to the trace. The file will get large quickly.
Run your tests in a short time slot to make the trace file readable. Remember to deactivate the trace after completing your tests.
www.cogknowhow.com