Errors installing i2b2 version: 1.3.1 on Sql Server

Errors installing i2b2 version: 1.3.1 on Sql Server

This document describes one apparent documentation error and two apparent data scripting errors pertaining to installation of i2b2 version 1.3.1 on Sql Server version 2005 and later.

Documentation error

In document on page 3 the database name to be created and associated with user "i2b2hive" is listed as "i2behive", which we believe to be an error. I believe this error to be generic across Oracle and Sql Server.

Data scripting error

There is an apparent bug in using the documentation in "Data_Installation_Guide_1-3.pdf" and the scripts in "i2b2demodata-13.zip" to install the i2b2 demo data on Microsoft Sql Server.

In our analysis, the problem stems from differences between Microsoft Sql Server version 2005 and later when compared to Oracle RDBMS. The Oracle RDBMS creates a default schema of the same name as the username. With version 2005, Microsoft Sql Server no longer creates a schema of the same name as a user and usually defaults to schema of "dbo".

Therefore, the installation instructions in "Data_Installation_Guide_1-3.pdf" if applied to Oracle RDBMS will result in a user and schema of the same name. However, if the installation instructions in "Data_Installation_Guide_1-3.pdf" are applied to Microsoft Sql Server version 2005 or later, the default behavior for that product will result in default schema for each user being "dbo". Once the databases are created in this manner, we found that the application would throw an error like this

16:00:40,283 ERROR [MetadataDbDao] PreparedStatementCallback; bad SQL grammar [select * from i2b2hive.ont_db_lookup where LOWER(c_domain_id) = ? and LOWER(c_project_path) like ? and (LOWER(c_owner_id) =? or c_owner_id = '@') order by c_project_path]; nested exception is
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'i2b2hive.ont_db_lookup'


In the above error it is obvious that the application is expecting a schema of the name "i2b2hive" and not "dbo".

If the enterprising individual setting up the i2b2 instance on Sql Server attempts to work around this by manually creating schemas of the same name as the users and makes them the default schemas for those users, then an error occurs in the load script

\Release_1-3\NewInstall\Demodata\scripts\ crc_create_datamart_sqlserver.sql

This results from explicit references on lines 232 and 234 to schema "dbo", which does not exist.

We have configured and tested a fix to this problem which eliminates the error thrown by the application and preserves the same schema naming convention across Oracle and Sql Server databases. This involves creating users and schemas identically in Oracle and Sql Server, per the documentation. Then two lines in the data load script need the reference to "dbo." removed.

In summary, create users and their default schemas of the same name, per the documentation. Then replace line 232 in \Release_1-3\NewInstall\Demodata\scripts\ crc_create_datamart_sqlserver.sql with this line

CREATE INDEX VD_IDX_DATES ON Visit_Dimension(Encounter_Num, Start_Date, End_Date)

And then replace line 234 with this line

CREATE INDEX VD_IDX_AllVisitDim ON Visit_Dimension(Encounter_Num, Patient_Num, InOut_Cd, Location_Cd, Start_Date, End_Date)

I intend to post a Sql Server database/user/schema creation script to accompany this bug report which may be suitable for inclusion in future versions of the product.

Tony Black, University of Washington, tblack@u.washington.edu
June 16, 2009