Appboard/2.6/builder/data sources/database - Revision history

imported>Mike.berman: 1 revision: Upload production docs for 2.6.2 releases

2016-09-06T21:19:14Z

1 revision: Upload production docs for 2.6.2 releases

← Older revision	Revision as of 21:19, 6 September 2016
(No difference)

imported>Doug yeager: /* Advanced Database Configuration */

2016-06-13T18:13:22Z

Advanced Database Configuration

← Older revision		Revision as of 18:13, 13 June 2016
Line 190:		Line 190:
	== Advanced Database Configuration ==		== Advanced Database Configuration ==

	There are a number of low-level database configuration parameters defined in <tt>[INSTALL_HOME]/server/webapps/enportal/WEB-INF/config/appboard.properties</tt>. Note that as with all property changes, the recommended approach is to make the change to the property in <tt>appboard-custom.properties</tt> so that the changes will persist across upgrades.		There are a number of low-level database configuration parameters defined in <tt>[INSTALL_HOME]/server/webapps/enportal/WEB-INF/config/appboard-jdbc.properties</tt>. Note that as with all property changes, the recommended approach is to make the change to the property in <tt>appboard-custom.properties</tt> so that the changes will persist across upgrades.

	Note that each parameter is documented in appboard.properties, and ideally the system defaults should be suitable for most users. However, under some high-load circumstances these are the parameters that may be of interest:		Note that each parameter is documented in appboard.properties, and ideally the system defaults should be suitable for most users. However, under some high-load circumstances these are the parameters that may be of interest:
Line 198:		Line 198:
	!Description		!Description
	\|-		\|-
	\|'''appboard.jdbc.~~acquireIncrement~~'''		\|'''appboard.jdbc.connectionTestQuery'''
	\| ~~When~~ the ~~available connections are about~~ to ~~run out~~, ~~BoneCP~~ will ~~dynamically create new ones in batches~~. ~~This property controls how many new connections to create in one go (up to a maximum of maxConnectionsPerPartition)~~. Note: ~~This is a per partition setting~~.		\| If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" databases that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none. Note: you will see some connectionTestQuery's configured for specific drivers that are not JDBC4 compliant.
	\|-		\|-
	\|'''appboard.jdbc.queryTimeout'''		\|'''appboard.jdbc.queryTimeout'''
Line 207:		Line 207:
	\| In addition to the Table and Query ''timeout'' setting, AppBoard also performs an availability/accessibility test (open TCP socket to database host:port). The default value is 5 seconds and in most cases this should not be changed.		\| In addition to the Table and Query ''timeout'' setting, AppBoard also performs an availability/accessibility test (open TCP socket to database host:port). The default value is 5 seconds and in most cases this should not be changed.
	\|-		\|-
	\|'''appboard.jdbc.~~connectionTestStatement~~'''		\|'''appboard.jdbc.idleTimeout'''
	\|Sets the connection ~~test statement. The query to send~~ to the ~~DB to maintain keep-alives and test for dead connections~~. ~~This#~~ is ~~database specific and should be set~~ to a ~~query that consumes the minimal amount~~ of ~~load on the server. Examples: MySQL: "/* ping / SELECT 1"~~, ~~PostgreSQL: "SELECT NOW()"~~. ~~If you do not set~~ this~~, then BoneCP will issue a metadata request instead~~ that ~~should work on all databases but is probably slower~~. (~~Note~~: ~~In MySQL, prefixing the statement by / ping */ makes the driver issue 1 fast packet instead~~. ~~See http://blogs.sun.com/SDNChannel/entry/mysql_tips_for_java_developers )~~		\| Sets Idle max age (in milliseconds). This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. Do not use aggressive values here (values less than 5 minutes will be ignored)! Default: 600000 (10 minutes).
	\|-		\|-
	\|'''appboard.jdbc.~~idleConnectionTestPeriod~~'''		\|'''appboard.jdbc.maxLifetime'''
	\| Sets ~~the idleConnectionTestPeriod~~. This ~~sets~~ the ~~time (~~in ~~minutes), for~~ a connection to ~~remain idle before sending~~ a ~~test query to the DB~~. ~~This~~ is ~~useful to prevent a DB from timing out connections on its end~~. ~~Do not use aggressive values here! Set to~~ 0 to ~~disable~~		\| Sets max age (in milliseconds). This property controls the maximum lifetime of a connection in the pool. When a connection reaches this timeout it will be retired from the pool, subject to a maximum variation of +30 seconds. An in-use connection will never be retired, only when it is closed will it then be removed. We strongly recommend setting this value, and it should be at least 30 seconds less than any database-level connection timeout. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. Default: 1800000 (30 minutes)
	\|-		\|-
	\|'''appboard.jdbc.~~idleMaxAge~~'''		\|'''appboard.jdbc.idlePoolTimeout'''
	\| Sets ~~Idle~~ max ~~age~~ (in ~~min~~). ~~The time (in minutes)~~, for ~~a connection to remain unused before it is closed off. Do not use aggressive values here! Set to 0 to disable~~.		\| Sets max time a pool can be idle before it is shutdown (in seconds). This will delete pools after a period of inactivity. Since pools get created automatically, this is useful for cleaning up pools associated with data sources that have been deleted, etc.
	\|-		\|-
	\|'''appboard.jdbc.~~maxConnectionsPerPartition~~'''		\|'''appboard.jdbc.maximumPoolSize'''
	\| Sets the maximum number of connections that will be ~~contained~~ in ~~every partition~~. ~~Setting~~ this ~~to 10 with our default~~ number of ~~partitions~~ (2) ~~means you may have~~ 20 ~~unique~~ connections to the ~~database~~. ~~Note~~ that the ~~connection~~ pool will not ~~create all these connections in one go but rather start off with minConnectionsPerPartition~~ and ~~gradually increase connections~~ as ~~required~~.		\| Sets the maximum number of connections that will be managed by the pool. This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend. A reasonable value for this is best determined by your execution environment. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to connectionTimeout milliseconds before timing out. Default: 20
			\|-
			\|'''appboard.jdbc.minPoolSize'''
			\| Sets the minimum number of connections that will be managed by the pool. This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool. Default: same as maximumPoolSize
	\|}		\|}

	The parameters above are global to all data sources. It is possible, however, to scope the parameters to the following, as well:		The parameters above are global to all data sources. It is possible, however, to scope the parameters to the following, as well:

	* A particular adapter (data source) instance. Although users should understand that connection pools may be shared amongst data sources, so great care must be given to associating specific parameters to a particular data source, with the understanding that other data sources that share the same URL/username/password will be using the same pool. One scenario where this could be useful is if a system is configured to leverage data out of two different database backends (i.e., MySQL and Oracle) and the user would like to configure ~~connectionTestStatement~~ to have a different value for one pool than another. By making this parameter apply to a particular adapter, different setting values could be used when creating the pool associated with one database than another.		* A particular adapter (data source) instance. Although users should understand that connection pools may be shared amongst data sources, so great care must be given to associating specific parameters to a particular data source, with the understanding that other data sources that share the same URL/username/password will be using the same pool. One scenario where this could be useful is if a system is configured to leverage data out of two different database backends (i.e., MySQL and Oracle) and the user would like to configure connectionTestQuery to have a different value for one pool than another. By making this parameter apply to a particular adapter, different setting values could be used when creating the pool associated with one database than another.
	* A particular SQL driver namespace. Some parameters are inherintly specific to one driver or another (such as '~~connectionTestStatement~~') and are best configured on a per-driver basis.		* A particular SQL driver namespace. Some parameters are inherintly specific to one driver or another (such as 'connectionTestQuery') and are best configured on a per-driver basis.

	In order to configure an connection setting on a particular adapter or SQL driver, specify the setting as '<namespace>'.'<setting>' where '<namespace>' corresponds to the fully qualified name of your adapter or SQL driver followed by the configuration parameter name above (omitting "appboard.jdbc").		In order to configure an connection setting on a particular adapter or SQL driver, specify the setting as '<namespace>'.'<setting>' where '<namespace>' corresponds to the fully qualified name of your adapter or SQL driver followed by the configuration parameter name above (omitting "appboard.jdbc").

imported>Jason.nicholls: /* Overview */

2015-07-21T16:27:31Z

Overview

← Older revision		Revision as of 16:27, 21 July 2015
Line 5:		Line 5:
	This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see [[appboard/2.6/builder/data_sources\|AppBoard Data Sources]]		This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see [[appboard/2.6/builder/data_sources\|AppBoard Data Sources]]

	The ~~two~~ types of database adapters to select from are <b>Database Query</b> or <b>Database Table</b>.		The three types of database adapters to select from are <b>Database Query</b>, <b>Database Table</b>, and <b>MDX Query</b>. This page covers to the Database Query and Database Table adapters. For more information about MDX queries refer to the [[appboard/2.6/builder/data_sources/database/mdx_query\|MDX Query]] documentation.

	===Database Query===		===Database Query===

imported>Jason.nicholls: /* Database Query */

2015-07-21T14:42:09Z

Database Query

← Older revision		Revision as of 14:42, 21 July 2015
Line 11:		Line 11:
	The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.		The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.

	This adapter ~~provdies~~ the ability to:		This adapter provides the ability to:

	* Use expressibility of SQL to be very specific about the data returned.		* Use expressibility of SQL to be very specific about the data returned.

imported>Jason.nicholls: 1 revision

2015-04-30T10:59:29Z

1 revision

← Older revision	Revision as of 10:59, 30 April 2015
(No difference)

imported>Jason.nicholls: /* Database Query */

2015-03-26T09:58:31Z

Database Query

New page

{{DISPLAYTITLE:Database Data Adapters}}
[[Category:AppBoard 2.6]]
== Overview ==

This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see [[appboard/2.6/builder/data_sources|AppBoard Data Sources]]

The two types of database adapters to select from are Database Query or Database Table.

===Database Query===

The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.

This adapter provdies the ability to:

* Use expressibility of SQL to be very specific about the data returned.
* Use expressibility of SQL to perform complex joins, transformations, and grouping
* Access to database functions in queries to perform all variety of tasks such as data conversion / manipulation.
* Executing stored procedures.
* Dynamic queries to fetch specific data versus complete blanket data and then filtering within AppBoard - for various reasons including performance.

==== Dynamic Queries ====

It is possible to create queries with dynamic components that can depend on session information, evaluating some expression, or through user interaction via Server Side Filter actions. For more information refer to the [[appboard/2.6/builder/dynamic_queries|Dynamic Queries]] documentation.

===Database Table===

This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. It is equivalent to executing <tt>select * from <table_name></tt> for the selected tables.

{{Warning|Although this adapter can be convenient for exploring a database schema it is recommended to use the Database Query adapter for the reasons outlined in the section above. In addition some databases have extremely large schema, such as Oracle, and it is very important to limit the set of tables discovered using the ''Table Name Regex'' field.}}

== Database Data Source Configuration ==

=== [Optional] Enable Failover ===

The "Enable Failover" option is provided on the "Connect" panel for certain adapters. For detailed instructions on enabling failover, see [[appboard/2.6/builder/data_source_failover|Data Source Failover]].

=== Select Template ===

Before entering the various settings for the database adapter, you must select a template for the type of database that you are going to connect to. This will enable AppBoard to pre-fill the URL field with an appropriate connection string for the selected database platform.

For either the Database Table or Database Query adapter, select from the following supported databases:
* Cloudera Impala - note: only available for ''Database Query''
* DB2
* H2
* Hypersonic (HSQLDB)
* MySQL / MariaDB
* Oracle
* PostgreSQL
* SQLAnywhere
* SQL Server

=== Configure Database Connection Settings ===

The following tables detail the settings available in the database adapters.

==== Database Query ====

You can configure one or more queries in each Database Query adapter configuration. Each configured query will have a different name, and result in a different Data Collection with that name. When naming a query, only alphanumeric characters, underscores, and hyphens are permitted.

By default only new or edited queries are actually run against the database to ensure the query is valid. This is done to prevent delays when editing the data source for cases where there are many existing queries. To force an existing query to be re-validated/discovered without changing it or losing any configuration then update the ''SQL Query'' with a trivial change such as adding a space character.

The table below details the settings you can configure for a Database Query Data Source:

Connect Screen:

{|class="wikitable"
!Name
!Description
|-
|'''Driver'''
| The Java class name for the JDBC driver. Example: <tt>oracle.jdbc.driver.OracleDriver</tt>
|-
|'''Timeout (seconds)'''
|The maximum duration (in seconds) to establish a successful database connection. Setting this value too low, in the case where the database cannot service the connections fast enough, will result in degraded performance.
|-
|'''URL'''
| The JDBC connection string/URL for defining the connection. This typically contains information about the database server and name, the user id, a password for connecting to the database, and an Oracle SID at the end which uniquely identifies the name of a particular database to connect to. Example: <tt>jdbc:mysql://myservername:3306/mydbname</tt>. The default connection string provided when you create the JDBC/Oracle adapter Data Source in AppBoard is the following: <tt>jdbc:oracle:thin:@localhost:1521:portal</tt>. Note that the sample URL string contains an Oracle SID at the end ("portal"), which uniquely identifies the name of a particular database to connect to. It is recommended that you make sure the connection string includes this SID so the connection can be made between AppBoard and the database. You may need to ask the database or application administrator to provide the Oracle SID for the database. Example Oracle URL: <tt>jdbc:oracle:thin:@servername:1521:OracleSID</tt>
|-
|'''Username'''
|User name credential used to connect to database.
|-
|'''Password'''
|User password credential used to connect to database.
|-
|'''Enabled'''
|Check this box to activate the connection defined in this Data Source. This is primary used in conjunction with setting failover configurations. Occasionally you may want to disable a Data Source. For example, you may set up a Data Source for testing purposes, but do not want it to continue running for performance reasons. Also, you may set up multiple connections to Data Sources and only want one to be active at a time, disabling the others until they may be needed.
|-
|'''Connection Retry Interval'''
|This setting works as part of the failover configuration. After a primary data source fails and failover occurs to a secondary or tertiary source, AppBoard will try to recover/revert to the highest priority configuration after this many seconds. Setting that determines how soon after a failed connection AppBoard will attempt to re-connect to that database. Setting this value too low can impact the performance of the system. Setting this higher will throttle the frequency of re-connect attempts when a database is not available. Setting this to 0 will implement a round-robin / no priority failover.
|}

Queries - Entity Settings Screen:

{|class="wikitable"
!Name
!Description
|-
|'''Name'''
|Name of this SQL query entity. This field is limited to 256 characters. Note: if editing the name all references internal to AppBoard are updated automatically, however in some cases Data Processing Scripts may have hardcoded references that will break as a result of the rename. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts. '''Note: This field is required.'''
|-
|'''Description'''
|A summary of the purpose of the query and any details or notes to provide for the system administrator. The Description field has a character limit of 512 characters.
|-
|'''Query Timeout (seconds)'''
|Maximum time (in seconds) to wait before timing out an executing SQL query. By default this is not set and the global default applies (see <tt>appboard.jdbc.queryTimeout</tt> in appboard.config). To disable the timeout set the value to zero, or to override the global default set to a positive value. Note: this option uses a feature of the JDBC driver and whether it is supported and the actual behaviour will depend on the driver.
|-
|'''[[appboard/2.6/builder/caching_and_polling|Cache Timeout (seconds)]]'''
|Maximum time (in seconds) a response from the database is kept before discarding and re-querying for new data. If set to zero then no caching is performed at all.
|-
|'''SQL Query'''
|SQL statement defining the records to be returned by the query. The SQL Query can contain a maximum of 8000 characters. '''Note: This field is required.'''
|-
|'''Update Statement'''
|Used in conjunction with the [[appboard/2.6/builder/widgets/actions/write_static_value|Write Static Value]] Action to write back to the original Data Source. Example: <tt>update asset set status=:status where assetName=:assetName</tt>. The Update Statement can contain a maximum of 1024 characters.
|-
|'''[[appboard/2.6/builder/data_processing_scripts|Data Processing Script(s)]]'''
|List of one or more Data Processing Scripts to be run on the query.
|}

==== Database Table ====

The table below details the settings you can configure for a Database Table Data Source:

{|class="wikitable"
!Name
!Description
|-
|'''Driver'''
| The Java class name for the JDBC driver. Example: <tt>oracle.jdbc.driver.OracleDriver</tt>
|-
|'''Timeout (seconds)'''
|The maximum duration (in seconds) to establish a successful database connection. Setting this value too low, in the case where the database cannot service the connections fast enough, will result in degraded performance.
|-
|'''[[appboard/2.6/builder/caching_and_polling|Cache Timeout (seconds)]]'''
|Maximum time (in seconds) a response from the database is kept before discarding and re-querying for new data. If set to zero then no caching is performed at all.
|-
|'''Catalog Name'''
|Database catalog name. It is not required, used to limit the set of tables found on the search.
|-
|'''Schema Name'''
|The database schema name. It is not required, used to limit the set of tables found on the search.
|-
|'''Table Name Regex'''
|Regular expression that defines the database tables that will be returned by the adapter. It is not required, but may be used to limit the set of tables found on the search. For example, if you had a couple of tables called Services and Locations, a useful regex to enter in that box would be “<nowiki>(Services|Locations)</nowiki>” - without the quotes. This is case-sensitive, and would match tables with names such as ServicesA, ServicesUS, LocationsB, or any table that contains either "Services" or "Locations" somewhere in the name. If you made the regex "^(<nowiki>Services|Locations</nowiki>)$" then it would only match exactly those names since the ^ and $ anchor the beginning and the end of the string.
|-
|'''Ansi Mode'''
|Check this box if your database is running in ANSI mode. Some databases (including MySQL and SQL Server) provide this variation on the character used to quote elements such as columns when the SQL statement is being constructed.
|-
|'''URL'''
| The JDBC connection string/URL for defining the connection. This typically contains information about the database server and name, the user id, a password for connecting to the database, and an Oracle SID at the end which uniquely identifies the name of a particular database to connect to. Example: <tt>jdbc:mysql://myservername:3306/mydbname</tt>. The default connection string provided when you create the JDBC/Oracle adapter Data Source in AppBoard is the following: <tt>jdbc:oracle:thin:@localhost:1521:portal</tt>. Note that the sample URL string contains an Oracle SID at the end ("portal"), which uniquely identifies the name of a particular database to connect to. It is recommended that you make sure the connection string includes this SID so the connection can be made between AppBoard and the database. You may need to ask the database or application administrator to provide the Oracle SID for the database. Example Oracle URL: <tt>jdbc:oracle:thin:@servername:1521:OracleSID</tt>
|-
|'''Username'''
|User name credential used to connect to database.
|-
|'''Password'''
|User password credential used to connect to database.
|-
|'''Enabled'''
|Check this box to activate the connection defined in this Data Source. This is primary used in conjunction with setting failover configurations. Occasionally you may want to disable a Data Source. For example, you may set up a Data Source for testing purposes, but do not want it to continue running for performance reasons. Also, you may set up multiple connections to Data Sources and only want one to be active at a time, disabling the others until they may be needed.
|-
|'''Connection Retry Interval (seconds)'''
|This setting works as part of the failover configuration. After a primary data source fails and failover occurs to a secondary or tertiary source, AppBoard will try to recover/revert to the highest priority configuration after this many seconds. Setting that determines how soon after a failed connection AppBoard will attempt to re-connect to that database. Setting this value too low can impact the performance of the system. Setting this higher will throttle the frequency of re-connect attempts when a database is not available. Setting this to 0 will implement a round-robin / no priority failover.
|}

Advanced - Entity Settings Screen:

{|class="wikitable"
!Name
!Description
|-
|'''Query Timeout (seconds)'''
|Maximum time (in seconds) to wait before timing out an executing SQL query. By default this is not set and the global default applies (see <tt>appboard.jdbc.queryTimeout</tt> in appboard.config). To disable the timeout set the value to zero, or to override the global default set to a positive value. Note: this option uses a feature of the JDBC driver and whether it is supported and the actual behaviour will depend on the driver.
|-
|'''[[appboard/2.6/builder/caching_and_polling|Cache Timeout (seconds)]]'''
|Maximum time (in seconds) a response from the database is kept before discarding and re-querying for new data. If set to zero then no caching is performed at all.
|-
|'''Filter'''
|Injects the 'where' clause used to filter data pulled from database.
|-
|'''Order'''
|Injects the 'Order By' clause used to perform server side sorting of the data pulled from database.
|-
|'''[[appboard/2.6/builder/data_processing_scripts|Data Processing Script(s)]]'''
|List of one or more Data Processing Scripts to be run on the query.
|}

== Advanced Database Configuration ==

There are a number of low-level database configuration parameters defined in <tt>[INSTALL_HOME]/server/webapps/enportal/WEB-INF/config/appboard.properties</tt>. Note that as with all property changes, the recommended approach is to make the change to the property in <tt>appboard-custom.properties</tt> so that the changes will persist across upgrades.

Note that each parameter is documented in appboard.properties, and ideally the system defaults should be suitable for most users. However, under some high-load circumstances these are the parameters that may be of interest:

{|class="wikitable"
!Name
!Description
|-
|'''appboard.jdbc.acquireIncrement'''
| When the available connections are about to run out, BoneCP will dynamically create new ones in batches. This property controls how many new connections to create in one go (up to a maximum of maxConnectionsPerPartition). Note: This is a per partition setting.
|-
|'''appboard.jdbc.queryTimeout'''
| Sets the global timeout in seconds that will be set limiting the maximum time a JDBC driver will execute a SQL Query. This value will only be used if the Database adapter's entity does not have Query Timeout configured. Default is set to 5 minutes (300 seconds).
|-
|'''appboard.jdbc.connectTimeout'''
| In addition to the Table and Query ''timeout'' setting, AppBoard also performs an availability/accessibility test (open TCP socket to database host:port). The default value is 5 seconds and in most cases this should not be changed.
|-
|'''appboard.jdbc.connectionTestStatement'''
|Sets the connection test statement. The query to send to the DB to maintain keep-alives and test for dead connections. This# is database specific and should be set to a query that consumes the minimal amount of load on the server. Examples: MySQL: "/* ping */ SELECT 1", PostgreSQL: "SELECT NOW()". If you do not set this, then BoneCP will issue a metadata request instead that should work on all databases but is probably slower. (Note: In MySQL, prefixing the statement by /* ping */ makes the driver issue 1 fast packet instead. See http://blogs.sun.com/SDNChannel/entry/mysql_tips_for_java_developers )
|-
|'''appboard.jdbc.idleConnectionTestPeriod'''
| Sets the idleConnectionTestPeriod. This sets the time (in minutes), for a connection to remain idle before sending a test query to the DB. This is useful to prevent a DB from timing out connections on its end. Do not use aggressive values here! Set to 0 to disable
|-
|'''appboard.jdbc.idleMaxAge'''
| Sets Idle max age (in min). The time (in minutes), for a connection to remain unused before it is closed off. Do not use aggressive values here! Set to 0 to disable.
|-
|'''appboard.jdbc.maxConnectionsPerPartition'''
| Sets the maximum number of connections that will be contained in every partition. Setting this to 10 with our default number of partitions (2) means you may have 20 unique connections to the database. Note that the connection pool will not create all these connections in one go but rather start off with minConnectionsPerPartition and gradually increase connections as required.
|}

The parameters above are global to all data sources. It is possible, however, to scope the parameters to the following, as well:

* A particular adapter (data source) instance. Although users should understand that connection pools may be shared amongst data sources, so great care must be given to associating specific parameters to a particular data source, with the understanding that other data sources that share the same URL/username/password will be using the same pool. One scenario where this could be useful is if a system is configured to leverage data out of two different database backends (i.e., MySQL and Oracle) and the user would like to configure connectionTestStatement to have a different value for one pool than another. By making this parameter apply to a particular adapter, different setting values could be used when creating the pool associated with one database than another.
* A particular SQL driver namespace. Some parameters are inherintly specific to one driver or another (such as 'connectionTestStatement') and are best configured on a per-driver basis.

In order to configure an connection setting on a particular adapter or SQL driver, specify the setting as '<namespace>'.'<setting>' where '<namespace>' corresponds to the fully qualified name of your adapter or SQL driver followed by the configuration parameter name above (omitting "appboard.jdbc").

== Troubleshooting ==

=== Missing JDBC driver ===

==== Case 1: Adapter JAR File Missing from Classpath ====

The most common issue when connecting to a database is a missing JDBC driver. Within the builder this may appear as an error message showing "Data was not found." when progressing from the "Connect" step in the Data Source Wizard.

In the <tt>appboard.log</tt> there may be a number of java exceptions including messages which specifically identify the driver that is missing, for example:

<pre>ERROR ... Please install missing JDBC Driver: com.mysql.jdbc.Driver ...</pre>

See the [[appboard/2.6/admin/db_drivers|Loading Database Drivers]] page for more information.

==== Case 2: Security Issue With /servlet Path ====

The use of custom JDBC addapters is also supported. You can add your own JDBC driver to the J2EE classpath, and save the driver JAR file under [AppBoard_Home]/server/webapps/enportal/WEB-INF/lib/. In this case, if the "missing JDBC driver" message is displayed, it could be a security conflict caused by the JAR adapter having a /servlet/ directory. This is redundant with the AppBoard Tomcat configuration. Delete the /servlet/ directory from the JAR.

=== Incorrect or Unexpected Date/Time Values and Formatting ===

Occasionally, date and time values from a database will not appear in AppBoard with the correct value or format when using the default settings in configuring the data source. One of the difficulties in handling date and time values from various types of databases is that not all database types handle date and time data in the same manner. It is possible, however, to adjust certain settings within AppBoard and the database itself to achieve better results. For more information on this topic, please see [[appboard/2.6/builder/data_sources/database/date_time_settings|Advanced Settings for Date and Time Data]].

===Empty Values in MySQL Date/Time Fields ===
*Issue:: Java cannot manage '00-00-00 00:00:00' in MySQL datetime fields. Even if this kind of datetime value is not supported within MySQL operations, values are allowed in general and used by some implementations as default for empty datetime values. The issue can result in many messages in Appboard error.log.
*Resolution: Convert the zero values to NULL values within the data-source. Example url: <tt>jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull</tt>