MDM SQL Storage
MDM stored procedures need to be written using SQL statements since the query is directly executed against the underlying database.
For suggestions on how to handle any issues you may encounter with MDM SQL Storage, see Troubleshooting for MDM SQL Storage.
Working principles of MDM SQL storage
There is no difference between system containers and user containers since both can use SQL storage.
All non-user defined data containers are considered as system containers except UpdateReport. Since this container uses a well-defined format, it is stored in SQL databases when SQL storage is enabled.
When MDM receives a request that may be a query, a "create record" request, or an "update record" request, it first identifies whether the request is on a system container or a user container, and then forwards this request to the right storage.
Each SQL storage is independent: it has its own connection pool and configuration. This is important as it directly impacts the number of connections MDM establishes with the SQL databases. If you have two data containers and a minimum of 20 connections, it means MDM will create 40 (20*2) connections during startup.
To support different types of RDBMS, SQL storage uses Hibernate internally to support multiple database types.
For more information about Hibernate, see http://www.hibernate.org/.
Common configurations for using MDM SQL storage
Procedure
Using variables in the datasource configuration file
For example, use the variable ${container} in the datasources configuration.
<datasources xmlns=http://www.talend.com/mdm/datasources">
<datasource name=MySQL-DS1?>
<type>RDBMS</type>
<rdbms-configuration>
<connection-url>jdbc:mysql://10.42.150.15:3306/${container}</connection-url>
<database-name>${container}</database-name>
<init>
<database-name>${container}</database-name>
<connection-url>jdbc:mysql://10.42.150.15:3306/</connection-url>
<connection-username>root</connection-username>
<connection-password>toor</connection-password>
</init>
</rdbms-configuration>
</datasource>
</datasources>The variable ${container} will be replaced by the data container name when it is being created.
For example, when a container "Product" is created, the <connection-url> element will be "jdbc:mysql:10.42.150.15:3306/Product". The <database-name> element will be replaced by the value "Product" too.
The <connection-username> element also accepts the ${container} variable.
Using multiple database vendors in the datasource configuration file
For example, MySQL may be used for the user data, H2 for staging and Oracle for system objects.
<datasources xmlns="http://www.talend.com/mdm/datasources">
<datasource name="RDBMS-1">
<master>
<type>RDBMS</type>
<rdbms-configuration>
<dialect>MySQL</dialect>
<connection-driver-class>com.mysql.jdbc.Driver</connection-driver-class>
<connection-url>jdbc:mysql://localhost:3306/${container}</connection-url>
<connection-username>root</connection-username>
<connection-password>toor</connection-password>
<fulltext-index-directory>/var/lucene/indexes/DS1</fulltext-index-directory>
<init>
<connection-url>jdbc:mysql://localhost:3306/</connection-url>
<connection-username>root</connection-username>
<connection-password>toor</connection-password>
<database-name>${container}</database-name>
</init>
</rdbms-configuration>
</master>
<staging>
<type>RDBMS</type>
<rdbms-configuration>
<dialect>H2</dialect>
<connection-driver-class>org.h2.Driver</connection-driver-class>
<connection-url>jdbc:h2:target/data/h2_staging_ds2;MVCC=true</connection-url>
<schema-generation>create</schema-generation>
</rdbms-configuration>
</staging>
<system>
<type>RDBMS</type>
<rdbms-configuration>
<dialect>Oracle11g</dialect>
<connection-driver-class>oracle.jdbc.driver.OracleDriver</connection-driver-class>
<connection-url>jdbc:oracle:thin:@fakehost:1521:XE</connection-url>
<connection-username>SYSTEM</connection-username>
<connection-password>password</connection-password>
</rdbms-configuration>
</system>
</datasource>
</datasources>Database Specific Configurations for Using MDM SQL Storage
SQL Server 2008
SQL Server 2008 can rapidly run into concurrent issues with deadlocks upon insert or updates. This kind of issue appears very rapidly, and it does not require a heavy load.
To fix this issue, you need to configure your SQL Server instance. Run the following SQL:
ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;