InterSystems Reports: How to store Server system databases in IRIS

InterSystems Reports Server stores system tables in an IRIS instance of your choice. How is this configured? 

Read this article if:

• You installed Logi Server directly using the Logi installer, not the InterSystems install script, OR
• You want to change the system database configuration

Background

The system databases are initially configured upon install. The InterSystems install script asks you for a superserver port, namespace, and user. It creates the namespace and does the Logi setup for you. If you run the Logi installer directly, it puts the system databases in a local Derby database by default. 

InterSystems Reports Server queries the system databases upon startup. If startup fails with the message "An error occurs when connecting to the database", it may be related to this setup. 

 

Clarification

InterSystems Reports has two types of database connections. These databases are completely unrelated. They can be on the same IRIS instance, but they don't have to be. 

 

 

How to configure system databases post-install

1) If InterSystems Reports Server is running, shut it down cleanly.  

2) Put a copy of the IRIS JDBC driver somewhere on the machine. Make note of the path.    

     Example: C:\MDV\intersystems-jdbc-3.1.0.jar     

3) In setenv.bat (Windows) or setenv.sh (Linux), in the installation's bin folder, add the JDBC driver path to the ADDCLASSPATH string. 

     For Windows, use a semicolon (;) as a delimiter.  For Linux, use colon (:). 

     Example (Windows):      set ADDCLASSPATH=%REPORTHOME%\lib;%JAVAHOME%\lib\tools.jar;C:\MDV\intersystems-jdbc-3.1.0.jar

4) Create a database and namespace in your IRIS instance. To avoid conflicts, only use these for InterSystems Reports; do not share with another application. No special mappings are required. 

5) Create a user with access to your database. This is used to retrieve data via JDBC query. 

6) Update the connection info in dbconfig.xml, in the installation's bin folder. Refer to this documentation. 

       The url has the format: jdbc:IRIS://<server name>:<superserver port>/<namespace>

7) Start InterSystems Reports Server. 

 

Frequently Asked Questions

Q. How can I tell if this worked? 

A. Start InterSystems Reports Server, and make sure it starts succesfully.  Then, see if any Logi tables were created in your IRIS namespace. You can use SQL Explorer and check the SQL.User schema. 

 

Q. Do these steps copy the content of the system tables to the new database? 

A. No. This creates fresh tables, like a new install. This may be fine right after install, or in a test system. If you need to preserve the content of the tables, use the DBMaintain tool to backup and restore:   Backing up and Restoring Server Data (v18)

 

Q. InterSystems Reports Server won't start. There are errors in the log like:

     [SQLCODE: <-201>:<Table or view name not unique>]

     [%msg: <Table 'SQLUser.UPDATEDETAILS_1' already exists>]

A. This can happen when you change the system username after the system databases have been set up in IRIS. The Logi tables already exist in IRIS, with the old user as owner. The new user does not have access. To fix this, give the new user full privileges on all SQLUser tables in the namespace.

 

Additional Documentation

Configuring the Server Database (v18)

InterSystems Reports Server Installation