Appboard/2.4/builder/data sources/database

This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see AppBoard Data Sources

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

Database Table

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

Database Query

This adapter retrieves data from a RDBMS using SQL queries to create a data set. This is typically used when a JOIN is required to produce a more appropriate view of the data (without unnecessary associations), or for performance reasons.

Why use a Database Query Adapter?

When using the Database Table data adapter, you are presented with the visible tables and columns within those tables with little control over how they are formatted or filtered. This is quick and easy when the data exists in the form it is needed, but often there is a need for more control. Examples of situations where the Database Query adapter is required include:

• the required data comes from a SQL stored procedure
• some SQL calculations need to be done (such as date calculations)
• complex joins need to be done in the source
• the raw data set is too large to handle in memory and you need to select a dynamic subset of that data

Using SHIM Expressions in a Database Query Adapter

In many cases, you will want to pass a parameter value into a query rather than using a static query definition. AppBoard provides a system of variable expressions, called SHIM, that can be used for this purpose. For instructions on this option, see SHIM Expressions.

Using the Database Query adapter with a Netcool Objectserver

This section details how to create a Database Query Data Source that connects to a Netcool Objectserver:

First, save the following code into a text file called trimAllNulls.groovy:

import com.edgetech.services.ServiceRequestFacade
import com.edgetech.services.ServiceResponseFacade
import com.edgetech.services.GenericRecord
import com.edgetech.services.Association
import com.edgetech.services.impl.ServiceRequestHelper
import com.edgetech.services.model.AttributeType
import java.util.Collection;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
ServiceResponseFacade serviceResponse = responseFacade;
ServiceRequestFacade serviceRequest = requestFacade;
// this Groovy script intended for use with Netcool Objectserver JDBC sources to
// automatically strip the null termination from all STRING type fields.
Collection recordsToClean = serviceResponse.getOrderedGenericRecords();
if ( ( recordsToClean != null ) && !recordsToClean.isEmpty() ) {
	for ( GenericRecord recordToClean:recordsToClean ) {
		Collection<String> attributeNames = recordToClean.getAttributeNames();
		for ( String attributeName:attributeNames) {
			AttributeType type = recordToClean.getAttributeType(attributeName);
			if ( type.equals(AttributeType.STRING) ) {
				String value = recordToClean.getAttributeValueAsString(attributeName);
				if ( value != null ) {
					if ( value.endsWith("\0")) {
						recordToClean.setAttributeValue(attributeName,value.substring(0,value.length()-1));
					}
				}
			}
		}
	}
}

Next, perform the following steps to configure the Data Source:

1. Save the above Groovy script in a text file and place the file in the /server/webapps/enportal/WEB-INF/groovy-script/custom/ directory.
2. Log in to AppBoard builder as an administrator.
3. Create a Data Source and select the Database Query adapter.
4. select the "SQLAnywhere" template.
5. Set the URL to something like jdbc:sybase:Tds:localhost:4100/NCOMS?charset=iso_1 (note the charset change).
6. Change the driver setting from com.sybase.jdbc3.jdbc.SybDriver to com.sybase.jdbc2.jdbc.SybDriver.
7. Specify the query (using Objectserver SQL), the query name, and comment (description).
8. On the next tab, select the resulting table and click the Advanced button in the lower left.
9. Click "add script" in the Script Definitions box.
10. Enter custom/trimAllNulls.groovy in the path box and set "REFRESH" for the "Runs on" option.
11. Complete the Data Source wizard.

If you use the "Third Party" Data Source for IBM Tivoli Netcool/Omnibus, you do not need the above Groovy script. That functionality is built in to the adapter.

Database Data Source Configuration

[Optional] Enable Failover

In AppBoard versions 2.4 or higher, an "Enable Failover" option is provided on the "Connect" panel for certain adapters. For detailed instructions on enabling failover, see 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:

• DB2
• H2
• Hypersonic (HSQLDB)
• MySQL
• Oracle
• PostgreSQL
• SQLAnywhere
• SQL Server

Configure Database Connection Settings

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

Database Table

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

Name	Description
driver	The Java class name for the JDBC driver. Example: oracle.jdbc.driver.OracleDriver
connectTimeout	Time (in seconds) after a timeout occurs to wait before attempting to re-establish the connection. Setting this value too low can impact the performance of the system.
timeout	Time to wait (in seconds) before timing out on connecting to the datasource. If there is latency between AppBoard and the database, due to network configuration, this value may need to be increased.
cacheTimeout	Time limit before re-loading results from the database.
catalogName	Database catalog name. It is not required, used to limit the set of tables found on the search.
schemaName	The database schema name. It is not required, used to limit the set of tables found on the search.
tableNameRegex	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 “(Services|Locations)” - 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 "^(Services|Locations)$" then it would only match exactly those names since the ^ and $ anchor the beginning and the end of the string.
ansiMode	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: jdbc:mysql://myservername:3306/mydbname. The default connection string provided when you create the JDBC/Oracle adapter Data Source in AppBoard is the following: jdbc:oracle:thin:@localhost:1521:portal. 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: jdbc:oracle:thin:@servername:1521:OracleSID
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.
connectionRetryInterval	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.

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.

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

Connect Screen:

Name	Description
driver	The Java class name for the JDBC driver. Example: oracle.jdbc.driver.OracleDriver
connectTimeout	Time (in seconds) after a timeout occurs to wait before attempting to re-establish the connection. Setting this value too low can impact the performance of the system.
timeout	Time to wait (in seconds) before timing out on connecting to the datasource. If there is latency between AppBoard and the database, due to network configuration, this value may need to be increased.
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: jdbc:mysql://myservername:3306/mydbname. The default connection string provided when you create the JDBC/Oracle adapter Data Source in AppBoard is the following: jdbc:oracle:thin:@localhost:1521:portal. 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: jdbc:oracle:thin:@servername:1521:OracleSID
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.
connectionRetryInterval	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:

Name	Description
Name	Name of the query to be displayed in the name of the resulting Data Collection
Description	A summary of the purpose of the query and any details or notes to provide for the system administrator
SQL Query	SQL statement defining the records to be returned by the query
Update Statement	Used in conjunction with the Write Static Value Action to write back to the original Data Source. Example syntax: update asset set status='22' where assetName=${shim:query.assetName('"cdc-glb13"')}
Cache Timeout (seconds)	Time limit before re-loading results of the query from the database.
Data Processing Script(s)	List of one or more Groovy Scripts to be run on the query.

Detailed Database Configuration

There are a number of low-level database configuration parameters that can be manipulated in webapps/enportal/WEB-INF/config/appboard.properties. Note that as with all property changes, the recommended approach is to make the change to the property in appboard-custom.properties 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:

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.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.

It is possible to associated each configuration parameter above with 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.

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

Troubleshooting

The most common issue when connecting with a database data adapter is a missing driver. In AppBoard 2.3 and higher, certain database drivers that were bundled with previous versions of Appboard are no longer included. For most databases, you need to download the appropriate driver and save it into the following directory: [AppBoard_Home]/server/webapps/enportal/WEB-INF/lib/

The error message indicating a missing driver would be "Please install missing [drivertype] Driver" on the screen and in Appboard.log when you click next to go from the "Connect" to the "Explore" steps of the wizard.

For an example of how to install the MySQL driver, see Adding a MySQL Java Connector

If you rename a database driver file to pkg-<libraryName>.jar, it will be preserved and migrated in archives generated by AppBoard.