Database Resource

From PresenceWiki
Jump to: navigation, search

Connection Details

In the Connection Details tab we specify the name of the resource and the driver class, which may need to be imported via the jar manager.

After this the we need the connection url which may or may not include the database name.

Note, this tab also displays the internal Resource ID for this resource.

Sqlres connectiondetails.png

You may include a properties file to pass extra properties to the connection and to override existing properties.

Existing Properties that may be overwritten

driver=
url=
user=
password= 

A test of the connection should reveal:-

Sqlres connected.png

Connection Pooling

Here you can tell Presence to "Re-use connection between queries" for this resource.

To create a new connection can be time consuming for the server, so this tells Presence to cache the connections and reuse them.

Pooling.png

Sometimes connections can be lost or dropped between calls, so it is advisable to specify a custom query.

Presence will first run this query on the retrieved connection to check that the connection is still valid.

Because of this the custom query should be just a small query on a small table.

This will be sufficient to let Presence know whether the connection for that resource still exists, before it uses it.

If the connection is broken then Presence will remove it from the cache and create a new one.

Testing the custom query

Click "Test" to validate the query and to ensure that it runs relatively quickly. The query specified will be executed ten times and the average speed measured. You should see a result such as this:

Custom query result.png


Tuning Options

How long should connections remain idle before being destroyed?

This determines how long the connection pool should retain connections after they are used. The default is seven minutes, meaning that if a connection remains unused after seven minutes it will be closed. This value of this depends on how busy you expect the database to be and how you wish to balance speed of access (new database connections may take a while to be created, especially for the AS400) or server resources (database connections can sometimes take up large amounts of memory).

What is the minimum number of connections that should be kept alive?

This overrides the maximum idle time, so that even if a connection remains idle for longer than the value specified above, it will never be closed if it is one of the last n connections retained where n is the minimum connections to keep alive.

Load test using these settings

This launches the load test dialog:

Connection pool load test.png

This tool allows you to simulate server load by controlling the number of connections requested per second and the average time that connections should be in use for. The first value simulates how many queries or updates per second you expect to run, and the second simulates the amount of time you expect those queries or updates to take before the connection is returned to the pool.

Global Password Update

As it is common to store the username and password in the SQL Node, rather than the Resource, the Global Password Update tab

provides a way to update the username and password for all SQL Nodes that use this SQL Resource.

Sqlres passwordupdate.png

Overriding values via variables

Note it is also possible to override the username and password and even the SQL Resource for an SQL Node via presence variables, namely:-

Sqlres vars.png

We set the variables before an SQL Node

${control.database.override}

If set to a Resource ID then the SQL Node will this Resource rather than its specified Resource. You can find the resource ID by running the following SQL against the Presence database:

SELECT
    DATABASERESOURCEID,
    DESCRIPTIVENAME,
     CATEGORYNAME
FROM
    PUBLIC.APP.DATABASERESOURCES join PUBLIC.APP.CATEGORIES on PUBLIC.APP.CATEGORIES.CATEGORYID=PUBLIC.APP.DATABASERESOURCES.CATEGORYID
WHERE (
    DESCRIPTIVENAME = 'Presence Internal DB'
)
${control.database.user}
SQL node will use this value for the username in the connection
${control.database.password}
SQL node will use this value for the password in the connection

Further Reading

For an overview of the SQL Node that we use to run SQL statements see SQL Statement Nodes

For more info on how to connect to different types of database and how to aid connection pooling see Database Connectivity


See Also

Resources > Database Resource

FTP Connections | Database Resource | SMTP Servers | POP / IMAP Mail Accounts

Text Template Resources | Message Transporters | LDAP Resource


Task Elements | Resources