h1. Step 6: CRC Loader properties: Configure data source lookup

\\
During the hivedata installation one of the tables created was the CRC_DB_LOOKUP table. The CRC Loader needs to communicate with this table and in order to do this we need to tell it where to find the Hivedata tables. During this step we will define the properties for JDBC connection and the data source lookup in the following CRC Loader configuration files.

* CRCLoaderApplicationContext.xml
* edu.harvard.i2b2.crc.loader.properties (should still be open from previous section)

\\

h5. Prerequisite

You need to know the location of your Hivedata tables. These are owned by the PM cell.

\\

h3. Things to keep in mind when configuring the data sources in CRCLoaderApplicationContext.xml:

The following properties need to be updated or verified that they are set correctly for your environment:

\\
*"driverClassName"*
- make sure the jdbc driver is correct for the type of database you are connecting to

\\
*"url"*
- verify the jdbc is correct for your type of database 
- The correct location the database that has your hivedata tables

\\
*"username"* and *"password"*
- needs to match the database user you setup for your hivedata. In the i2b2 demo database this is _i2b2hive_ and _demouser_.


\\ \\

h3. Steps to update data sources in CRCLoaderApplicationContext.xml

These steps include example configurations for Oracle, PostgreSQL and SQL Server.

\\
{indent1} 1. The <b>CRCLoaderApplicationContext.xml</b> file is located in the following directory: {indent1}

{indent2} <b><i>YOUR_I2B2_SRC_DIR</i></b>\edu.harvard.i2b2.crc\etc\spring {indent2}

\\
{indent2} <b><i>Example:</i></b>&nbsp; C:\opt\i2b2\edu.harvard.i2b2.crc\etc\spring {indent2}

\\ \\
{indent1} 2. Open the CRCLoaderApplicationContext.xml file to configure the data source for the CRC Loader {indent1}

\\

h5. Oracle Database

\\
{code:xml}


<bean id="CRCBootstrapDS" class="org.apache.commons.dbcp.BasicDataSource"
	destroy-method="close">
	<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
	<property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" />
	<property name="username" value="i2b2hive" />
	<property name="password" value="demouser" />
</bean>


{code}

\\ \\

h5. PostgreSQL Database

\\
{code:xml}


<bean id="CRCBootstrapDS" class="org.apache.commons.dbcp.BasicDataSource"
	destroy-method="close">
	<property name="driverClassName" value=" org.postgresql.Driver " />
	<property name="url" value="jdbc:postgresql://localhost:5432/i2b2" />
	<property name="username" value="i2b2hive" />
	<property name="password" value="demouser" />
</bean>


{code}

\\ \\

h5. SQL Server Database

\\
{code:xml}


<bean id="CRCBootstrapDS" class="org.apache.commons.dbcp.BasicDataSource"
	destroy-method="close">
	<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
	<property name="url" value="jdbc:sqlserver://localhost:1433" />
	<property name="username" value="i2b2hive" />
	<property name="password" value="demouser" />
</bean>


{code}

\\ \\
{indent1} 3. Save the changes and close the file. {indent1}

\\ \\

h3. Steps to update data sources in edu.harvard.i2b2.crc.loader.properties

These steps include example configurations for Oracle, PostgreSQL and SQL Server.

\\
{indent1} 1. Go to the <b>edu.harvard.i2b2.crc.loader.properties</b> file you still have opened from earlier. {indent1}

{indent1} 2. Scroll to the section called <b>Datasource Lookup info</b>. {indent1}

{indent1} 3. Set the Datasource Lookup information to reflect the information you entered previously in the CRCLoaderApplicationContext.xml file. {indent1}

\\
{panel:title=Datasource Lookup info| borderStyle=solid| borderColor=#ccc| titleBGColor=#E5DFEC| bgColor=#ffffff} 

*Oracle Configuration*

&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.datasource={color:#2a00ff}CRCBootsrapDS{color}
&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.servertype={color:#2a00ff}ORACLE{color}
&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.schemaname={color:#2a00ff}i2b2hive{color}

\\
*PostgreSQL Configuration*

&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.datasource={color:#2a00ff}CRCBootsrapDS{color}
&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.servertype={color:#2a00ff}PostgreSQL{color}
&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.schemaname={color:#2a00ff}i2b2demodata{color}


\\
*SQL Server Configuration*

&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.datasource={color:#2a00ff}CRCBootsrapDS{color}
&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.servertype={color:#2a00ff}SQLServer{color}
&nbsp; &nbsp; edu.harvard.i2b2.crc.loader.ds.lookup.schemaname={color:#2a00ff}i2b2demodata.dbo{color}

{panel}

\\
{indent1} 4. Save the changes and close the file. {indent1}

\\ \\ \\