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”.

    SCmain.png

  3. Switch to the Services tab.  You’ll see the database service “SQLAnywhereForBI”.  Double-click on that entry to see its properties.

    DBServiceCMS.png

  4. Switch to the Configuration tab to see the database startup parameters.

    ServiceProperties.png

  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.

    ServiceProperties-Log.png

  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}”

  1. 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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. ABDELHAK SADOUK

    Hi friends,

    My server Intelligence Agent (SIA) can not start because the database service “SQLAnywhereForBI” can’t start also. I got the following error :

    “I . 08/09 20:35:06. A read failed with error code: (1392), Le fichier ou le répertoire est endommagé et illisible.

    E. 08/09 20:35:06. Fatal error:  cannot write to transaction log “C:\Program Files (x86)\SAP BusinessObjects\sqlanywhere\database\BI4_Audit.log”

    E. 08/09 20:35:06. unable to start database “C:\Program Files (x86)\SAP BusinessObjects\sqlanywhere\database\BI4_CMS.db”

    E. 08/09 20:35:06. Error writing to transaction log file

    I. 08/09 20:35:06. Database server shutdown due to startup error “

    Please, can you help me

    (0) 
    1. ABDELHAK SADOUK

      I found the solution by following the advice given on the following forum:

      http://evtechnologies.com/transaction-logs-on-sybase-sql-anywhere-and-sap-​​businessobjects-bi-4-1

      In fact, I crushed the BI4_Audit.db and BI4_Audit.log files and I replaced with others that I got from another machine where I installed BO again and where the files are not corrupted . After I logged in to the CMS database by executing the command in the command line:
      dbisql -c “UID = DBA; PWD = mypassword; BI4 Server =; DBF = C: \ Program Files (x86) \ SAP BusinessObjects \ sqlanywhere \ database \ BI4_CMS.db.”

      Once connected, I start the command:

      alter database ‘C: \ Program Files (x86) \ SAP BusinessObjects \ sqlanywhere \ database \ BI4_Audit.db‘ alter log off;

      The query runs successfully.
      And that’s good, I can be connected to BO smoothly.

      Thank you again Eric

      (0) 

Leave a Reply