Enabling Database Logging for the CMS Repository

[jose_at_sap]

In SAP Business Objects installations that use SAP SQL Anywhere as the default CMS and Audit repositories, database logging is not enabled.  If you ever run into the situation where the database cannot start, it's useful to enable logging to find out why that is happening.  Here's how you do that for both Windows and Unix deployments.

Windows Installations

Database logging is enabled by adding a startup parameter when the database starts. The database startup parameters are specified in the Windows service for the CMS and Audit databases.  You'll need to install the SAP SQL Anywhere Client on your BOBJ system to run the database administration tools.  Instructions on how to obtain the SQL Anywhere Client are here: Database Administration Tools for SQL Anywhere CMS and Audit DBs.

1. After installing the SQL Anywhere Client, launch Sybase Central.
2. In the right panel, double-click on "SQL Anywhere 12".
   
   
3. Switch to the Services tab.  You'll see the database service "SQLAnywhereForBI".  Double-click on that entry to see its properties.
   
   
4. Switch to the Configuration tab to see the database startup parameters.
   
   
5. That's where you'll add the additional parameter to enable logging.  Add the following at the beginning of the Parameters text box:
   
   -o "C:\BOBJ\dblogs\cmslog.txt"
   
   In this example, all database server messages will be logged in the file cmslog.txt located in the specified directory.  You can obviously choose a different location and/or file name for your deployment.
   
   
6. Click the Apply button.  Close the Service Properties dialog and shut down Sybase Central.

Important Notes:

• You'll need to restart the database service to create the log file.  You can do this from Sybase Central, from the Services applet in Control Panel, or by restarting the Business Objects server.
• After troubleshooting and resolving any issues, disable database logging in your production system.  For performance reasons, we recommend that production database servers run without any logging.

Here's an example of what appears inside the database log file:

I. 05/22 11:08:33. Starting database "BI4_CMS" (C:\Program Files (x86)\SAP BusinessObjects\sqlanywhere\database\BI4_CMS.db) at Thu May 22 2014 11:08

I. 05/22 11:08:33. Starting database "BI4_Audit" (C:\Program Files (x86)\SAP BusinessObjects\sqlanywhere\database\BI4_Audit.db) at Thu May 22 2014 11:08

I. 05/22 11:08:33. Transaction log: BI4_CMS.log

I. 05/22 11:08:33. Transaction log: BI4_Audit.log

I. 05/22 11:08:33. Starting checkpoint of "BI4_Audit" (BI4_Audit.db) at Thu May 22 2014 11:08

I. 05/22 11:08:33. Starting checkpoint of "BI4_CMS" (BI4_CMS.db) at Thu May 22 2014 11:08

I. 05/22 11:08:33. Finished checkpoint of "BI4_Audit" (BI4_Audit.db) at Thu May 22 2014 11:08

I. 05/22 11:08:33. Finished checkpoint of "BI4_CMS" (BI4_CMS.db) at Thu May 22 2014 11:08

I. 05/22 11:08:33. Database "BI4_Audit" (BI4_Audit.db) started at Thu May 22 2014 11:08

I. 05/22 11:08:33. Database "BI4_CMS" (BI4_CMS.db) started at Thu May 22 2014 11:08

I. 05/22 11:08:33. Database server started at Thu May 22 2014 11:08

I. 05/22 11:08:33. Trying to start SharedMemory link ...

I. 05/22 11:08:33.     SharedMemory link started successfully

I. 05/22 11:08:33. Trying to start TCPIP link ...

I. 05/22 11:08:33. Starting on port 2638

I. 05/22 11:08:34.     TCPIP link started successfully

I. 05/22 11:08:35. Now accepting requests

Unix Installations

The database startup parameters are specified in the file <bobj_install>/sap_bobj/sqlanywhere_startup.sh, where <bobj_install> is the location where you installed SAP Business Objects.

1. Edit that file (sqlanywhere_startup.sh).  The database startup parameters appear at the end:
   
   dbspawn -f dbsrv12 -gk all -n "$SQLANYWHERE_SERVER" -x "tcpip(port=${SQLANYWHERE_PORT};DoBroadcast=NO;BroadcastListener=NO)" "${SQLANYWHERE_CMS_DBFILE}" "${SQLANYWHERE_AUDIT_DBFILE}"

2. Add the following parameter to enable logging:
   
   dbspawn -f dbsrv12 -o /home/bo4user/cmslog.txt -gk all -n "$SQLANYWHERE_SERVER" -x "tcpip(port=${SQLANYWHERE_PORT};DoBroadcast=NO;BroadcastListener=NO)" "${SQLANYWHERE_CMS_DBFILE}" "${SQLANYWHERE_AUDIT_DBFILE}"
   
   In this example, all database server messages will be logged in the file cmslog.txt located in the specified directory.  You can obviously choose a different location and/or file name for your deployment.

Important Notes:

• You'll need to restart the database service to create the log file.  You can do this by executing the shutdown/startup scripts.
• After troubleshooting and resolving any issues, disable database logging in your production system.  For performance reasons, we recommend that production database servers run without any logging.

SQL Anywhere Database Server Log Files

The -o filename parameter prints all database server messages to the specified log file.  There are also a few other parameters that you can use:

Parameter	Description
-oe filename	Log startup errors, fatal errors, and assertions.
-on { size[ k | m | g ] }	Specify a maximum size for the database server message log, after which the file is renamed with the extension .old and a new file is started.
-os { size[ k | m | g ] }	Specify a maximum size for the database server message log file, at which point the file is renamed.
-ot logfile	Truncate the database server message log file and appends output messages to it.

For complete details on SQL Anywhere database server startup parameters, please refer to the documentation: http://dcx.sybase.com/1201/en/dbadmin/server-database-dbengine.html.