Starting from BusinessObjects XI SP5, SAP Sybase SQL Anywhere is used as default database for BusinessObjects system databases (CMS and Audit). This post describes steps how to add a new database and provides some information about SQLAnywhere tools.

Defaults

By default on 64bit Windows, SQLAnywhere will be installed in folder:

C:\Program Files (x86)\Business Objects\SQLAnyWhere12

The executables and database files are located in the folder:

C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin

CMS database is stored in two files BOE120.db and BOE120.log (log here is database log not a text file). Audit database is stored in BOE120_AUDIT.db and BOE120_AUDIT.log.

The databases are running on SQLAnywhere server which is represented by Windows service BOE120SQLAW. BO gets access to the databases through ODBC system data sources BOE120 and BOE120_AUDIT.

We want to add another database TEST. Add the database to server and create a new ODBC data source.

Before trying this out, you must be aware that you might need to roll back if some step fail. This post may contain mistakes; your configuration may be different from the one that used here; you can make a mistake. Make sure that you made backup, ideally snapshot of your VM etc.

Create database

First we need to create the database files. This can be done with DBINIT tool. You can get the list of all parameters exectuting the tool without parameters. The simplest form for creation database is

dbinit -dba <uid>,<pwd> <database>

dbinit -dba sa,xxxxxx test.db SQL Anywhere Initialization Utility Version 12.0.1.3436 CHAR collation sequence: 1252LATIN1(CaseSensitivity=Ignore) CHAR character set encoding: windows-1252 NCHAR collation sequence: UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary) NCHAR character set encoding: UTF-8 Creating system tables Creating system views Setting option values Database “test.db” created successfully

The files test.db and test.log are created in the current folder.

Adjust server settings

To adjust the SQL Anywhere server setting, you need to stop the corresponding service as well as  Server Intelligence Agent service. You can do this windows NET command:

net stop “Server Intelligence Agent (WINPC)” The Server Intelligence Agent (WINPC) service is stopping. The Server Intelligence Agent (WINPC) service was stopped successfully.

Now we can stop SQL Anywhere service using tool DBSVC. To find the name of the service, you can run dbsvc -l. It is most likely BOE120SQLAW. To stop the service, use dbsvc -x <name>. To start the service, use dbsvc -u <name>

dbsvc -x BOE120SQLAW SQL Anywhere Service Utility Version 12.0.1.3436 Service “BOE120SQLAW” stop pending. Service “BOE120SQLAW” was stopped successfully.

You can get the command that was used to create the service using dbsvc -l -cm

dbsvc -l -cm dbsvc -t Network -s Automatic -as -sn “BOE120SQLAW” -y -w “BOE120SQLAW” “C:\Program Files (x86)\Business Objects\SQLAnyWhere12\Bin\dbsrv12.exe” -x tcpip(PORT=2638) -n BOE120SQLAW_sa “C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin\BOE120.db” “C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin\BOE120_AUDIT.db”

Let’s add the path to the new database in the end

dbsvc -t Network -s Automatic -as -sn “BOE120SQLAW” -y -w “BOE120SQLAW” “C:\Program Files (x86)\Business Objects\SQLAnyWhere12\Bin\dbsrv12.exe” -x tcpip(PORT=2638) -n BOE120SQLAW_sa “C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin\BOE120.db” “C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin\BOE120_AUDIT.db” “C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin\TEST.db” SQL Anywhere Service Utility Version 12.0.1.3436 Service “BOE120SQLAW” was created successfully.

Starting the services again.

dbsvc -u BOE120SQLAW SQL Anywhere Service Utility Version 12.0.1.3436 Service “BOE120SQLAW” start pending. Service “BOE120SQLAW” was started successfully. net start “Server Intelligence Agent (WINPC)” The Server Intelligence Agent (WINPC) service is starting. The Server Intelligence Agent (WINPC) service was started successfully.

If the service BOE120SQLAW fails, check the paths to the databases.

Create ODBC data source

Now let’s create the ODBC data sources. You can use dbdsn -ls to list all system data sources, and command dbdsn -ls -cm to see the commands used to create those data sources.

dbdsn -ls SQL Anywhere Data Source Utility Version 12.0.1.3436 SQL Anywhere System Data Sources: BOE120 BOE120_AUDITC:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin>dbdsn -ls -cm dbdsn -y -ws “BOE120” -c “UID=sa;DBN=BOE120;ServerName=BOE120SQLAW_sa” dbdsn -y -ws “BOE120_AUDIT” -c “UID=sa;DBN=BOE120_AUDIT;ServerName=BOE120SQLAW_sa”

So we can use the following command to add our data soure dbdsn -y -ws “TEST” -c “UID=sa;DBN=TEST;ServerName=BOE120SQLAW_sa”

dbdsn -y -ws “TEST” -c “UID=sa;DBN=TEST;ServerName=BOE120SQLAW_sa” SQL Anywhere Data Source Utility Version 12.0.1.3436 System Data Source “TEST” written to registry.

You will also see the data source in the list of system data sources.

Ping

Now is the time to test connection. This can be done with DBPING command.

dbping -c UID=sa;DBN=TEST;PWD=xxxxxx;ServerName=BOE120SQLAW_sa -d SQL Anywhere Server Ping Utility Version 12.0.1.3436 Connected to SQL Anywhere 12.0.1.3436 server “BOE120SQLAW_sa” and database “TEST”. Ping database successful.