Sitedef Database Configuration

Database Sitedef Configuration

In the sitedef you configure database access in different parts;

• Supported database managers and drivers
• Pinpointing Core database
• Database declaration
• External Server specification

Supported Database managers and drivers

The following Database managers are supported with driver bundled in Comflow:

• SAP DB -  "com.sap.dbtech.jdbc.DriverSapDB"
• DB2 Universal Driver - "com.ibm.db2.jcc.DB2Driver"
• DB2 NET Driver - "COM.ibm.db2.jdbc.net.DB2Driver"
• DB2 APP Driver - "COM.ibm.db2.jdbc.app.DB2Driver"
• DB2 iSeries Access Driver - "com.ibm.as400.access.AS400JDBCDriver"
• Apache Derby Client Driver - "org.apache.derby.jdbc.ClientDriver"
• Apache Derby Client Driver 4 - "org.apache.derby.jdbc.ClientDriver40"
• Apache Derby Embedded Driver - "org.apache.derby.jdbc.EmbeddedDriver"
• MySql Driver-  "com.mysql.jdbc.Driver"
• MySql Driver - "com.mysql.cj.jdbc.Driver"
• Maria DB Driver - "org.mariadb.jdbc.Driver"
• Jtds Driver  (SQL Server) - "net.sourceforge.jtds.jdbc.Driver"
• Oracle Driver - "oracle.jdbc.driver.OracleDriver";
• Oracle Driver 2 - "oracle.jdbc.OracleDriver";
•  Jdbc Odbc driver - "sun.jdbc.odbc.JdbcOdbcDriver";
• H2 Driver - "org.h2.Driver";
• PostgreSQL Driver -  "org.postgresql.Driver";

The following Database managers are supported but driver is NOT delivered with Comflow (they have to be downloaded separately and put in a lib-folder) :

• SQL Server (Azure) - "com.microsoft.sqlserver.jdbc.SQLServerDriver" 
• Infor Compass "com.infor.idl.jdbc.Driver"
• SQL Anywhere = "sap.jdbc4.sqlanywhere.IDriver"

Pinpointing Core database

You pinpoint the Core database  for a Comflow installation under Site/ServerInfo in the Sitedef. The Core database thereby needs to be configured first before this settings is made.

The XML attributes are:

<ServerInfo name="localhost" instance="local" serverimpl="CA">
<CoreDatabase server="localhost" service="DEFAULT" />

Database declaration

You declare all databases for a Comflow installation under Site/Databases in the Sitedef.

Each database shall be specified with all it's parameters, especially the connection Metadataid and Default schema.

<Database name="CAAPPS" metadataid="CAAPPS" defaultschema="CAAPPS" enabled="yes" querydb="" server="localhost" service="CACORE">
<Qualifiers>
<MessageField name="Enterprise" value="001"/>
<MessageField name="Company" value="1"/>
</Qualifiers>
</Database>
<Database name="QUARTZ" metadataid="SCHED" defaultschema="QUARTZ" enabled="yes" querydb="" server="localhost" service="CACORE" preparedstatementinsql="false"/>
<Database name="CACORE" metadataid="CACORE" defaultschema="CACORE" enabled="yes" querydb="" server="localhost" service="CACORE">
<Qualifiers>
<MessageField name="Enterprise" value="001"/>
<MessageField name="Company" value="1"/>
</Qualifiers>
</Database>

External Servers

You declare the connection to the databases in the External servers part. where each connected server is declared and within the server each db-connection (example for SQL server db).

<ExternalServers>
        <Server name="customer-db">
            <Services>
                <Service class="" name="comflow" port="" type="jdbcpool">
                    <Parameters>
                        <parameter name="driver" value="net.sourceforge.jtds.jdbc.Driver"/>
                        <parameter name="url" value="jdbc:jtds:sqlserver://10.20.30.40:1433/comflow-prod"/>
                        <parameter name="user" value="SA"/>
                        <parameter name="password" value="XXX"/>
                        <parameter name="schema" value="CACORE"/>
                        <parameter name="useCaseingOnTable" value="true"/>
                        <parameter name="connectionTracking" value="true"/>
                        <parameter name="timePattern" value="yyyy-MM-dd HH:mm:ss.SSS"/>
                        <parameter name="connectionInitStmts" value="SET LOCK_TIMEOUT 30000"/>
                    </Parameters>
                </Service>
                <Service class="" name="quartz" port="" type="jdbcpool">
                    <Parameters>
                        <parameter name="driver" value="net.sourceforge.jtds.jdbc.Driver"/>
                        <parameter name="url" value="jdbc:jtds:sqlserver://10.20.30.40:1433/comflow-prod"/>
                        <parameter name="user" value="SA"/>
                        <parameter name="password" value="XXXX"/>
                        <parameter name="schema" value="QUARTZ"/>
                        <parameter name="useCaseingOnTable" value="true"/>
                       <parameter name="connectionTracking" value="true"/>
                        <parameter name="timePattern" value="yyyy-MM-dd HH:mm:ss.SSS"/>
                        <parameter name="connectionInitStmts" value="SET LOCK_TIMEOUT 30000"/>
                    </Parameters>
               </Service>
            </Services>
        </Server>
        <Server name="CUSTSQL02">
            <Services>
                <Service class="" name="M3PRD" port="" type="jdbcpool">
                    <Parameters>
                        <parameter name="driver" value="net.sourceforge.jtds.jdbc.Driver"/>
                        <parameter name="url" value="jdbc:jtds:sqlserver://100.200.300.400:1435/M3FDBPRD;useNTLMv2=TRUE"/>
                        <parameter name="user" value="corzia"/>
                        <parameter name="password" value="XXXX"/>
                        <parameter name="schema" value="MVXJDTA"/>
                        <parameter name="validationQuery" value="select 1"/>
                        <parameter name="charset" value="UTF-8"/>
                        <parameter name="useCaseingOnTable" value="false"/>
                    </Parameters>
               </Service>
            </Services>
        </Server>
    </ExternalServers>

A link to the possible configurations by Apache is below:

https://commons.apache.org/proper/commons-dbcp/configuration.html

Example: Setup for SQL Server Database:

Database:

The properties in the Properties section are all default false and not necessary if not wanted.

<Database name="CACORE" metadataid="CACORE" defaultschema="CACORE" enabled="yes" querydb="" server="localhost" service="DEFAULT" >
<Properties>
<Property name="append-rowlock" value=”true”  />
<Property name="append-nolock" value=”true”  />
<Property name="append-snapshot" value=”true”  />
 </Properties>
<Database/>

Service:

 <Service class="" name="comflow" port="" type="jdbcpool">
                    <Parameters>
                        <parameter name="driver" value="net.sourceforge.jtds.jdbc.Driver"/>
                        <parameter name="url" value="jdbc:jtds:sqlserver://10.20.30.40:1433/comflow-prod"/>
                        <parameter name="user" value="SA"/>
                        <parameter name="password" value="XXXX"/>
                        <parameter name="schema" value="CACORE"/>
                        <parameter name="useCaseingOnTable" value="true"/>
                        <parameter name="connectionTracking" value="true"/>
                        <parameter name="timePattern" value="yyyy-MM-dd HH:mm:ss.SSS"/>
                        <parameter name="connectionInitStmts" value="SET LOCK_TIMEOUT 30000"/>
                  </Parameters>
               </Service>

Example: Setup for DB2/400 Database:

<Service class="" name="M3PRD" port="" type="jdbcpool">
                    <Parameters>
                        <parameter name="driver" value="com.ibm.as400.access.AS400JDBCDriver" />
						<parameter name="url" value="jdbc:as400://sesecoXX.secotools.net; tcp no delay=true " />
                        <parameter name="user" value="comflow"/>
                        <parameter name="password" value="XXXX"/>
                        <parameter name="schema" value="MVXJDTA"/>
                        <parameter name="validationQuery" value="select 1"/>
                        <parameter name="charset" value="UTF-8"/>
                        <parameter name="useCaseingOnTable" value="false"/>
                    </Parameters>
               </Service>

Documentation of as400 jdbc parameters: https://jt400.sourceforge.net/doc/com/ibm/as400/access/doc-files/JDBCProperties.html#format

Example: Setup for Oracle Database:

<Service name="CACORE" type="jdbcpool" class="" port="1521">
  <Parameters>
   <parameter name="driver" value="oracle.jdbc.driver.OracleDriver"/>
   <parameter name="url" value="jdbc:oracle:thin:@10.10.10.10:1521:xe"/>
   <parameter name="user" value="CACORE"/>
   <parameter name="password" value="XXXX"/>
   <parameter name="schema" value="CACORE"/>
   <parameter name="timeStampPattern" value="dd.MM.yyyy HH:mm:ss.SSSSSS"/>
   <parameter name="datePattern" value="dd.MM.yyyy"/>
   <parameter name="timePattern" value="dd.MM.yyyy HH:mm:ss.SSSSSS"/>
  </Parameters>
</Service>

Example: Setup for Infor Compass/Data lake Database:

Note that in the Data lake, all table column names are shorten to 4 characters. In the repository they are though described as 6 characters, which also the applications are built for. By setting the shortenColumnName="true" the column names are shortened to 4 in runtime when querying the database.

Also note that the Data lake database is very slow, why you should not expect fantastic performance.

<Database name="M3DATALAKE" metadataid="MOVEX" defaultschema="default" enabled="yes" preparedstatementinsql="false" querydb="" server="InforDataLake" service="M3CE" shortenColumnName="true" />	

<ExternalServers>
		<Server name=”InforDataLake">
			<Services>
				<Service name="M3CE" type="jdbcpool" class="" port="">
					<Parameters>
						<parameter name="driver" value="com.infor.idl.jdbc.Driver" />
						<parameter name="url" value="jdbc:infordatalake://TENANT_ID?ionApiCredentials=%7B%22ti%22% …enormuslyLongStringHere… sg%22%7D" />
						<parameter name="schema" value="default" />
						<parameter name="useCaseingOnTable" value="true" />
						<parameter name="connectionTracking" value="true" />
						<parameter name="timePattern" value="yyyy-MM-dd HH:mm:ss.SSS" />
					</Parameters>
				</Service>
			</Services>
		</Server>
	</ExternalServers>