Setting up a Database

Creating the database itself is beyond the scope of this manual as it depends largely on the type of database used and its vendor.

Composable Architecture Platform supports a very large collection of databases through its use of JDBC drivers for connectivity. However, configuring the JDBC driver correctly to work with a given database is normally the job of an experienced database administrator, since it requires the understanding of security, network and connectivity issues. If you are not experienced in these matters, we suggest you refer this section to someone who is.

Create new JDBC driver

To set up a new driver and thereby allow connection to a database, follow these steps:

Make sure that the driver class is available in the class path of the program or application server that is running Composable Architecture Platform. In the case of Jetty, the location for the driver JAR file is in server/lib/ext/jdbc/[database name].

Enter the driver class name (fully qualified) as the JDBC driver class. If the settings differ for systems, make sure that you set up a blank system name (this represents the default settings) as well as settings for each system that is different.

Enter the URL prefix and suffix for the driver. This maps to the parts of the URL string sent to the driver. The complete URL sent will be in the form of:

[URL prefix][Data base name][URL suffix]

Using JNDI data sources

A common method used to define a database connection within a J2EE application server is to use a JNDI defined data source. Composable Architecture Platform supports this through the use of JDBC driver class names that start with the letters "JNDI". By default, a single JNDI data source driver is created.

To set the actual JNDI name for the data source, supply the JNDI name in the database alias field in the rules configuration and select the JNDI Datasource as the driver.

There may be times when you wish to add another JNDI data source driver, for example, to use a database that requires specific overrides for BIGINT or VARCHAR(1024). In which case, you can create a database specific driver (for example: JNDI_ORACLE), and then make sure that specific driver is selected in the rules configuration for the relevant database.

Please note that if using JNDI data sources for items such as Flight Recorders and Case Managers, you will still need to define the correct JDBC drivers within the console for it to be able to access the data.

Common JDBC drivers

The following is a list of common JDBC drivers and their equivalent configuration.

DB2

• More information: http://www.ibm.com/

• Driver class: com.ibm.db2.jcc.DB2Driver

• URL prefix: jdbc:db2:

Derby

• More information: http://db.apache.org/derby/

• Driver class: org.apache.derby.jdbc.EmbeddedDriver

• URL prefix: jdbc:derby:

• URL suffix: ;create=true

MS SQL Server and Sybase

• More information: http://jtds.sourceforge.net/

  • (open source driver – others exist)

• Driver class: net.sourceforge.jtds.jdbc.Driver

• URL prefix: jdbc:jtds:<server_type>:<server url>

  • (where <server_type> is one of either 'sqlserver' or 'sybase')

• URL suffix: Valid suffixes can be found at http://jtds.sourceforge.net/faq.html#driverImplementation

MySQL

• More information: http://dev.mysql.com/downloads/connector/j/

• Driver class: com.mysql.jdbc.Driver

• URL prefix: jdbc:mysql:<server url>

  • If your database is configured to use UTF-16, you also need to force UTF-8:

• URL suffix: ?characterEncoding=UTF-8&useUnicode=true&characterSetResults=UTF-8

Postgresql

• More information: http://jdbc.postgresql.org/

• Driver class: org.postgresql.Driver

• URL prefix: jdbc: postgresql:<server url>

Oracle

• More information: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

• Driver class: oracle.jdbc.OracleDriver or oracle.jdbc.driver.OracleDriver

  • (depending of version)

• URL prefix: jdbc:oracle:thin:@<server url>

• BIGINT: NUMBER

For Oracle RAC Server or Amazon Oracle RDS connectivity, the settings will typically look something like:

• URL prefix: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS=(PROTOCOL=TCP)(HOST=<host name>)(PORT=1521))(CONNECT_DATA =(SERVICE_NAME= URL suffix: )))