Sitedef Database Configuration

Database Sitedef Configuration

In the sitedef you configure database access in different parts;

• Pinpointing Core database
• Database declaration
• External Server specification

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 service/db-connection. Each Service has a declared database driver ("driver"-property)  together with related address for the database ("url"). The rest of the properties are unique for each driver.

<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

A link to jtds faq: https://jtds.sourceforge.net/faq.html

Example: Setup for SQL Server Database:

 <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="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>	

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>