Page tree
Skip to end of metadata
Go to start of metadata

On this page:

Target audience:

CAST AI Administrators

Summary: it is possible to modify the connection configuration for the CAST Application Engineering Dashboard and the RestAPI to pool multiple CAST Dashboard Service schemas/databases in one resource "domain".  In other words, you no longer have to configure one resource (in the context.xml) per CAST Dashboard Service "schema/database", instead, one resource (in the context.xml) can function for multiple CAST Dashboard Service schemas/databases.

Introduction

When configuring a connection for the CAST Application Engineering Dashboard or the RestAPI, each CAST Dashboard Service database/schema that you want to reference requires one UNIQUE <resource> entry in the context.xml file. For example, the following code block shows the configuration required for a Tomcat 7 application server to specify four Dashboard Service schemas (V82_CENTRAL1_CSS_A, V82_CENTRAL2_CSS_A, V81_CENTRAL1_CSS_B, V81_CENTRAL2_CSS_B), two on one CAST Storage Service server  and two on another (note that each <resource> has a different "name" attribute):

<Resource name="jdbc/domains/AED1" url="jdbc:postgresql://NEFYN:2280/postgres"
    initConnectionSqls="SET search_path TO V82_CENTRAL1_CSS_A;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED2" url="jdbc:postgresql://NEFYN:2280/postgres"
    initConnectionSqls="SET search_path TO V82_CENTRAL2_CSS_A;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED3" url="jdbc:postgresql://CEIRIAD:2280/postgres"
    initConnectionSqls="SET search_path TO V81_CENTRAL1_CSS_B;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED4" url="jdbc:postgresql://CEIRIAD:2280/postgres"
    initConnectionSqls="SET search_path TO V81_CENTRAL2_CSS_B;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

This configuration works well when you only need to reference a handful of CAST AIP schemas/RDBMS, however, if you need to reference tens or hundreds of CAST AIP schemas, then it can quickly become arduous to configure each resource manually. In addition, for each resource you configure in the context.xml, a database connection will be opened to the target RDBMS - if the CAST AIP schemas/databases are located on the SAME RDBMS, then multiple connections to that same RDBMS will be opened by the web application, which can impact performance.

In order to simplify the configuration of multiple database connections/resources in the context.xml file and as a direct response to the issues outlined above, CAST has introduced the ability to use a connection pooling feature. This feature is detailed below.

Note about pooling Measurement Service schemas

The connection pooling feature is aimed at pooling multiple CAST Dashboard Service schemas located on the same RDBMS. Whilst technically possible to pool multiple CAST Measurement Service schemas this is not recommended nor supported:

  • The CAST Application Analytics Dashboard does NOT support the display of data from multiple Measurement Service schemas, i.e. the dashboard expects one resource named "AAD". which must correspond to one single CAST Measurement Service.
  • The CAST Report Generator can technically support connections to multiple Measurement Service schemas, however, this configuration is not recommended nor supported.

Connection pooling

The connection pooling feature is handled through two specific files in the deployed WAR file:

%CATALINA_HOME%\webapps\<WAR>\WEB-INF\domains.properties
%CATALINA_HOME%\webapps\<WAR>\META-INF\context.xml
If you edit either of these files, ensure you restart the web application server so that the changes are taken into account.

domains.properties

The domains.properties file contains the configuration required for each domain. By default this is delivered as follows:

#New connection pool management
#DOMAIN1=MYRESOURCE,SCHEMA1
#DOMAIN2=MYRESOURCE,SCHEMA2
#DOMAIN3=MYRESOURCE,SCHEMA3
  • DOMAIN refers to the name of the domain you are creating. You can use any domain name notation you want, but each domain name must be unique.
  • MYRESOURCE refers to the name attribute used in the <resource> tag in the context.xml file. This identifies the connection parameters (as defined in the context.xml file) for the schema/database. This item can be repeated as many times as necessary - each schema that is located on the same RDBMS will use the same MYRESOURCE.
  • SCHEMA refers to the name of the CAST Dashboard Service schema you want to connect to.
  • The # is used to comment out a line. By default all configuration is commented out and therefore no connection pooling is in effect.
  • To avoid database connections errors, please ensure that:
    • any empty lines in the domains.properties file are removed
    • any white spaces in the domain configuration are removed - for example a space after the comma as in "DOMAIN1=MYRESOURCE, SCHEMA1" will cause an issue, whereas "DOMAIN1=MYRESOURCE,SCHEMA1" will not.

In simple terms, you need to create one line (i.e. DOMAIN=MYRESOURCE,SCHEMA) per CAST Dashboard Service schema you need to access in the CAST Application Engineering Dashboard or in the CAST RestAPI. So if you have 200 CAST Dashboard Service schemas:

  • you need to enter 200 lines
  • if all 200 schemas are on the same RDBMS, then the MYRESOURCE will be identical in each line
  • all DOMAIN entries must be unique

context.xml

In the context of the connection pooling feature, the context.xml is used only to define the connection parameters to an RDBMS and the MYRESOURCE item (name attribute used in the <resource> tag). The required format of the <resource> tag is very similar to the format used for a standard connection without connection pooling, except that the CAST Dashboard Service schema/database is not defined at all (i.e. the initConnectionSqls (Tomcat 7) or connectionInitSqls (Tomcat 8/8.5) attributes are not defined). The schema/database is instead defined in the domains.properties file:

Tomcat 7

<!-- CSS2 -->
<Resource name="jdbc/domains/{MYRESOURCE}"
	url="jdbc:postgresql://{host}:{port}/postgres"
	username="{user}" password="{password}"
	auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
	validationQuery="select 1" initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<!-- Oracle -->
<Resource name="jdbc/domains/{MYRESOURCE}"
	url="jdbc:oracle:thin:@{server}:{port}:{instance}"
	username="{user}" password="{password}"
	auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
	validationQuery="select 1 from DUAL" initialSize="5" maxActive="20" maxIdle="10" maxWait="-1" />

<!-- Microsoft SQL Server -->
<Resource name="jdbc/domains/{MYRESOURCE}"
	url="jdbc:jtds:sqlserver://{host};instance={instance}"
	username="{user}" password="{password}"
	auth="Container" type="javax.sql.DataSource" driverClassName="net.sourceforge.jtds.jdbc.Driver"
	validationQuery="select 1" initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

Tomcat 8 / 8.5

<!-- CSS2 -->
<Resource name="jdbc/domains/{MYRESOURCE}" 
	url="jdbc:postgresql://{host}:{port}/postgres"
	username="{user}" password="{password}" 
	auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
	validationQuery="select 1" initialSize="5" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/>

<!-- Oracle -->
<Resource name="jdbc/domains/{MYRESOURCE}"
	url="jdbc:oracle:thin:@{server}:{port}:{instance}"
	username="{user}" password="{password}"
	auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
	validationQuery="select 1 from DUAL" initialSize="5" maxTotal="20" maxIdle="10" maxWaitMillis="-1" />

<!-- Microsoft SQL Server -->
<Resource name="jdbc/domains/{MYRESOURCE}"
	url="jdbc:jtds:sqlserver://{host};instance={instance}"
	username="{user}" password="{password}"     
	auth="Container" type="javax.sql.DataSource" driverClassName="net.sourceforge.jtds.jdbc.Driver"
	validationQuery="select 1" initialSize="5" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/>

maxActive and maxTotal attributes

When configuring connection pooling, CAST highly recommends that you tune the maxActive (Tomcat 7) and maxTotal (Tomcat 8/8.5) attributes in each <resource> tag according to your consumption use case and the number of schemas you are pooling. These parameters govern the maximum number of active connections that can be allocated at the same time. The default value is 20. 

Remember that when connection pooling is in use, there is only one <resource> tag for each target RDBMS, so you can increase/decrease the number of active connections to fit your need.

When using the combined CAST-AAD-AED.war file

If you are using the combined CAST-AAD-AED.war file (where you have both Measurement Service and Dashboard Service schemas configured in the context.xml) and you want to use connection pooling for your Dashboard Services, you must ensure you configure the Measurement Service schema connection via the connection pooling mechanism as well otherwise it will be ignored.

For example, you have the following configuration in your context.xml for a Tomcat 7 application server prior to invoking connection pooling:

<!-- AAD configuration -->
<Resource name="jdbc/domains/AAD" url="jdbc:postgresql://BOB:2280/postgres"    
	initConnectionSqls="SET search_path TO v82_MEASURE_BOB;"
	username="operator" password="CastAIP" 
	auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
	validationQuery="select 1"
	initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<!-- AED configuration -->
<Resource name="jdbc/domains/AED1" url="jdbc:postgresql://FRED:2280/postgres"
    initConnectionSqls="SET search_path TO V82_CENTRAL1_FRED;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED2" url="jdbc:postgresql://FRED:2280/postgres"
    initConnectionSqls="SET search_path TO V82_CENTRAL2_FRED;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

When you invoke connection pooling for the two Dashboard Service schemas on the FRED CSS server, you need to do the following for the Measurement Service schema:

domain.properties

#New connection pool management
AAD=AAD,V82_MEASURE_BOB
DOMAIN2=AED,V82_CENTRAL1_FRED
DOMAIN3=AED,V82_CENTRAL2_FRED
Note that the DOMAIN for a Measurement Service schema MUST be set to "AAD" as shown above, otherwise the CAST Application Analytics Dashboard will not function correctly.

context.xml

<!-- AAD configuration -->
<Resource name="jdbc/domains/AAD" url="jdbc:postgresql://BOB:2280/postgres"    
	username="operator" password="CastAIP" 
	auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
	validationQuery="select 1"
	initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<!-- AED configuration -->
<Resource name="jdbc/domains/AED" url="jdbc:postgresql://FRED:2280/postgres"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

Example

This example will configure the following four Dashboard Service schemas via connection pooling:

Dasboard Service schema nameCSS name
V82_CENTRAL1_FREDFRED:2280
V82_CENTRAL2_FREDFRED:2280
V81_CENTRAL1_BILLBILL:2280
V81_CENTRAL2_BILLBILL:2280

Standard configuration without connection pooling

Without connection pooling (Tomcat 7/CSS2) you would need to configure the following in the context.xml - one unique <resource> per Dashboard Service schema:

<Resource name="jdbc/domains/AED1" url="jdbc:postgresql://FRED:2280/postgres"
    initConnectionSqls="SET search_path TO V82_CENTRAL1_FRED;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED2" url="jdbc:postgresql://FRED:2280/postgres"
    initConnectionSqls="SET search_path TO V82_CENTRAL2_FRED;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED3" url="jdbc:postgresql://BILL:2280/postgres"
    initConnectionSqls="SET search_path TO V81_CENTRAL1_BILL;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED4" url="jdbc:postgresql://BILL:2280/postgres"
    initConnectionSqls="SET search_path TO V81_CENTRAL2_BILL;"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

Connection pooling configuration

To convert the above standard configuration to connection pooling:

domain.properties with connection pooling

Ensure each line is uncommented (except for the first) and then configure four unique domains (one for each CAST Dashboard Service schema) and ensure that the MYRESOURCE item is correctly defined: in the example below, there are two MYRESOURCE items, AED1 and AED2 which refer to each RDBMS:

#New connection pool management
DOMAIN1=AED1,V82_CENTRAL1_FRED
DOMAIN2=AED1,V82_CENTRAL2_FRED
DOMAIN3=AED2,V81_CENTRAL1_BILL
DOMAIN4=AED2,V81_CENTRAL2_BILL

context.xml with connection pooling

You can see that there are now only two <resource> elements:

  • The first one defines MYRESOURCE "AED1" pointing to the CSS called "FRED" - two schemas V82_CENTRAL1_FRED and 82_CENTRAL2_FRED are hosted on this CSS
  • The second <resource> defines the MYRESOURCE "AED2" called "BILL" - two schemas V82_CENTRAL1_BILL and 82_CENTRAL2_BILL are hosted on this CSS
<Resource name="jdbc/domains/AED1" url="jdbc:postgresql://FRED:2280/postgres"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>

<Resource name="jdbc/domains/AED2" url="jdbc:postgresql://BILL:2280/postgres"
    username="operator" password="CastAIP"
    auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
    validationQuery="select 1"
    initialSize="5" maxActive="20" maxIdle="10" maxWait="-1"/>
  • No labels