OMOP
Space shortcuts
Space Tools

Versions Compared

Old Version 2

changes.mady.by.user Janice Donahoe

Saved on

compared with

New Version Current

changes.mady.by.user Janice Donahoe

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Section

Configure

data source files
Section

Configure CRC data source

Earlier in during the data installation you created new crcdata tables and in order for the CRC to communicate with the database it needs to know where these tables reside. This information is configured in the crc-ds.xml file.

Things to keep in mind when configuring your datasources:

  • The crcdata tables were created during the Data Installation stage of this document.
  • The other database tables (hivedata, pmdata, etc.) were created when setting up your i2b2 database (demo environment - i2b2 Installation Guide).
    • The CRCBootStrapDS points to the data source for your CRC_DB_LOOKUP table which is a hivedata table.
  • The <user-name> and <password> need to match the database user you set up for your hivedata. In the i2b2 demo database this is i2b2hive and demouser.
    • Only 1 CRCBootStrapDS should
    Info

    As part of the i2b2 on OMOP project there are specific crcdata tables that were created earlier in this installation process. Other than the metadata table, all other tables were created when you initially setup your i2b2 database using the standard i2b2 Installation Guide.

    3
    Section

    Steps to configure data sources

    The following steps define how to add the data source for the new crcdata tables. The steps include example configurations for Oracle, PostgreSQL, and SQL Server.

    Warning

    Only 1 CRCBootStrapDS can reside in the crc-ds.xml file

    .

    • If your
    datasource file already has one do not add another.

    html-text
    <div class="i2b2-panel">
        <table class="i2b2-warning">
            <colgroup>
                <col width="24">
                <col>
            </colgroup>
            <tbody>
                <tr>
                    <td>
                        <img src="https://community.i2b2.org/wiki/download/attachments/14091764/icon_warn.png" width="16" height="16" align="absmiddle" alt="warning" border="0">
                    </td>
                    <td>
                        <b>Warning</b>
                        <br>The <b>crc-ds.xml</b> file should contain only 1 CRCBootStrapDS. If your datasource file already has one do not add another one.
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
     

    • The QueryToolDemoDS points to the data source for your demodata tables.
      • The <user-name> and <password> need to match the database user you set up for your demodata. In the i2b2 demo database this is i2b2demodata and demouser.
    • The <connection_url> needs to have the following:
      • The correct JDBC information for your type of database.
      • The correct location of the database containing your crcdata tables. (These may or may not reside in the same location as your hivedata)
    • Both the <driver-class> and <driver> need to accurately reflect the type of database you are connecting to.

     

    Steps to configure data sources

    The following steps define how to configure your data sources for the CRC Cell. These steps include example configurations for Oracle, PostreSQL and SQL Server.

     
    indent
    Level1

    1. The crc-ds.xml file is located in the following directory:

    indent
    Level
    • file does contain the CRCBootStrapDS you can proceed with this section of the installation guide.
    • If your file does not contain the CRCBootStrapDS, you can go to the CRC Data Source Configuration chapter of the i2b2 Installation guide.

     

    Step 1: Locate and open your crc-ds.xml file

    Panel
    borderColor#ccc
    bgColor#ffffff
    titleBGColor#DDD9C3
    borderStylesolid
    titleFile Location

    YOUR_I2B2_SRC_DIR\edu.harvard.i2b2.crc\etc\

    jboss

    indent
    Level1
    2. Open the crc-ds.xml file to configure your data sources.

     

    Step 2: Add a new datasource and update the following information: (see examples shown below)

      •  
    Oracle Database

     

    Code Blockxmlxml<datasource jta="false"
      • Set the jndi-name
    ="java:/CRCBootStrapDS" pool-name="CRCBootStrapDS" enabled="true" use-ccm="false"> <connection-url>jdbc:oracle:thin:@localhost:1521:xe</connection-url> <driver-class>oracle.jdbc.OracleDriver</driver-class> <driver>ojdbc6.jar</driver> <security> <user-name>i2b2hive</user-name> <password>demouser</password> </security> <validation> <validate-on-match>false</validate-on-match> <background-validation>false</background-validation> </validation> <statement> <share-prepared-statements>false</share-prepared-statements> </statement> </datasource>
      • and pool-name to OMOP_CDM_{ORACLE | POSTGRESQL | SQLSERVER}.
      •  Update the connection-url to point to the location of the database containing the crcdata tables.
      •  Update the user-name and password to accurately reflect the database user you setup during the Data Installation.
      •  Verify the jdbc driver in the connection-url, driver-class, and driver accurately reflect the type of database you are connecting to.

    Step 3: Save the changes and close the file.

    Oracle Database
    Code Block
    xml
    xml
    <datasource jta="false" jndi-name="java:/QueryToolDemoDSOMOP_CDM_ORACLE"
	pool-name="QueryToolDemoDSOMOP_CDM_ORACLE" enabled="true" use-ccm="false">
	<connection-url>jdbc:oracle:thin:@localhost:1521:XE</connection-url>
	<driver-class>oracle.jdbc.OracleDriver</driver-class>
	<driver>ojdbc6.jar</driver>
	<security>
		<user-name>i2b2demodata</user-name>
		<password>demouser</password>
	</security>
	<validation>
		<validate-on-match>false</validate-on-match>
		<background-validation>false</background-validation>
	</validation>
	<statement>
		<share-prepared-statements>false</share-prepared-statements>
	</statement>
</datasource>

     

    PostgreSQL Database

     

    Code Block
    xml
    xml
    <datasource jta="false" jndi-name="java:/CRCBootStrapDSOMOP_CDM_POSTGRESQL"
	pool-name="CRCBootStrapDSOMOP_CDM_POSTGRESQL" enabled="true" use-ccm="false">
	<connection-url>jdbc:postgresql://localhost:5432/i2b2</connection-url>
	<driver-class>org.postgresql.Driver</driver-class>
	<driver>postgresql-9.2-1002.jdbc4.jar</driver>
	<security>
		<user-name>i2b2hive</user-name>
		<password>demouser</password>
	</security>
	<validation>
		<validate-on-match>false</validate-on-match>
		<background-validation>false</background-validation>
	</validation>
	<statement>
		<share-prepared-statements>false</share-prepared-statements>
	</statement>
</datasource>
<datasource jta="false" jndi-name="java:/QueryToolDemoDS"
	pool-name="QueryToolDemoDS" enabled="true" use-ccm="false">
	<connection-url>jdbc:postgresql://localhost:5432/i2b2</connection-url>
	<driver-class>org.postgresql.Driver</driver-class>
	<driver>postgresql-9.2-1002.jdbc4.jar</driver>
	<security>
		<user-name>i2b2demodata</user-name>
		<password>demouser</password>
	</security>
	<validation>
		<validate-on-match>false</validate-on-match>
		<background-validation>false</background-validation>
	</validation>
	<statement>
		<share-prepared-statements>false</share-prepared-statements>
	</statement>
</datasource>

     

    SQL Server Database

     

    Code Block
    xml
    xml
    <datasource jta="false" jndi-name="java:/CRCBootStrapDSOMOP_CDM_SQLSERVER"
	pool-name="CRCBootStrapDSOMOP_CDM_SQLSERVER" enabled="true" use-ccm="false">
	<connection-url>jdbc:sqlserver://localhost:1433</connection-url>
	<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
	<driver>sqljdbc4.jar</driver>
	<security>
		<user-name>i2b2hive</user-name>
		<password>demouser</password>
	</security>
	<validation>
		<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>
		<validate-on-match>false</validate-on-match>
	</validation>
	<statement>
		<share-prepared-statements>false</share-prepared-statements>
	</statement>
</datasource>
<datasource jta="false" jndi-name="java:/QueryToolDemoDS"
	pool-name="QueryToolDemoDS" enabled="true" use-ccm="false">
	<connection-url>jdbc:sqlserver://localhost:1433</connection-url>
	<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
	<driver>sqljdbc4.jar</driver>
	<security>
		<user-name>i2b2demodata</user-name>
		<password>demouser</password>
	</security>
	<validation>
		<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>
		<validate-on-match>false</validate-on-match>
	</validation>
	<statement>
		<share-prepared-statements>false</share-prepared-statements>
	</statement>
</datasource>

    indent
    Level1

    3. If your environment has multiple projects pointing to different data sources then copy the <datasource> section for each project data source.

    indent
    Level1

    4. Update the jndi-name and pool-name to reflect the name of the second project .

    indent
    Level3

    Example:   A second project called Demo2 would have the following entry:

     

    Second Data Source Configuration

     

    Code Blockxmlxml
    <datasource jta="false" jndi-name="java:/QueryToolDemoDS"
	pool-name="QueryToolDemo2DS" enabled="true" use-ccm="false">
	<connection-url>jdbc:oracle:thin:@localhost:1521:XE</connection-url>
	<driver-class>oracle.jdbc.OracleDriver</driver-class>
	<driver>ojdbc6.jar</driver>
	<security>
		<user-name>i2b2demodata2</user-name>
		<password>demouser</password>
	</security>
	<validation>
		<validate-on-match>false</validate-on-match>
		<background-validation>false</background-validation>
	</validation>
	<statement>
		<share-prepared-statements>false</share-prepared-statements>
	</statement>
</datasource>
    Info
    titleNote

    The above example is for an Oracle database. If your database is SQL Server or PostgreSQL then you will need to copy the <datasource> section for your type database connection. Do not use this one as it will not work.

    indent
    Level1

    5. Save the changes and close the file.