Defining External Database Connections

The Maestro User Interface component of LISTSERV Maestro uses a “system database” to store its working data, outlined in Section 10 The System Database. This may be either the default “internal” MySQL database, or some external database.

The Maestro User Interface can also be configured to access an external “user database” to retrieve existing information to build recipient lists in the recipients wizard or target groups wizard, or to create drop-in content elements.

Multiple databases managed by the same or different DBMS software can be configured as user databases so that recipient data and drop-in content elements can be accessed from many sources. The user database(s) may be on the same database server as the system database, or on different ones. By configuring LISTSERV Maestro to be able to access different databases, institutional data can be retrieved from different sources, allowing for great flexibility.

The following DBMS products have been tested and are compatible with LISTSERV Maestro:

DBMS versions comparable to the versions listed above should also be compatible with LISTSERV Maestro (this is particularly true for versions later than those listed). Versions earlier than those listed above are not officially supported.

LISTSERV Maestro communicates with external databases with so called “Plugins” and drivers. If an external database is going to be used for the system database or the user database, the appropriate driver must be installed and the plugin must be configured first.

Before an external database can be invoked, either as the system database in the HUB System Database Connection screen, or as a user database in the LUI Data Warehouse, Recipient Definition, Target Group Definition, or Drop-in Definition screens, LISTSERV Maestro must know how to access the particular DBMS software managing the database in question.

The following steps need to be taken once for each DBMS package, which will make any databases running under that software available:

Important: Connection details for user databases are defined in the recipients target groups wizard, or the recipients wizard in the Maestro User Interface during the recipient definition of a job. Do not enter connection details in the HUB for user databases. Connection details are entered in the HUB only for the external system database. After a restart, any database connection details entered in the Global Components Settings will change the system database.

The Maestro User Interface is a Java server application that uses JDBC to connect to the configured database. Therefore, it is usually necessary to install a compatible JDBC driver for the database. Each database plugin (see Section 9.2 Registering a Database Plugin for more information) has been developed to use exactly one JDBC driver. There may be several plugins for the same DBMS, each of which uses a different driver to access that DBMS. The specific plugin to be used depends on the DBMS and the JDBC driver available for that DBMS.

Important: After installing a new JDBC driver into LISTSERV Maestro (see descriptions below), it is necessary to restart LISTSERV Maestro to make it aware of the new driver. On Windows, you must also re-install the LISTSERV Maestro Windows service. To do so, execute the following command after installing the driver:

Note: The plugins available at the time this document was written support nine different drivers for four different databases as well as the ODBC-driver (as a read-only plugin only) that in turn allows access to any database or other data source that has an ODBC driver available.

This plugin is used for connecting to the DB2 V8.2 database and uses the IBM DB2 V8.2 thin driver.

•	How to install the driver: The driver comes in form of four files which are found in the installation folder of the DB2 V8.2 database (license conditions from IBM may apply):

[db2_install_folder]/SQLLIB/java/db2jcc.jar
[db2_install_folder]/SQLLIB/java/db2jcc_javax.jar
[db2_install_folder]/SQLLIB/java/db2jcc_license_cu.jar
[db2_install_folder]/SQLLIB/java/db2policy.jar

This plugin is used for connecting to the DB2 V7.2 or V8.2 database and uses the IBM DB2 V7.2 or V8.2 native driver. (For accessing DB2 V8.2, it is recommended to use the thin driver instead, see above.)

•	How to install the driver: The driver comes as part of the DB2 V7.2 or V8.2 database installation (license conditions from IBM may apply):

To install the driver, first install the DB2 run-time clients (from the runtime folder of the installation package) on the server where you want to run the Maestro User Interface. Use the client to connect to a database on the DB2 server (see Section 10.2.4 Preparing DB2 as the System Database). It is important that you install the client on the Maestro User Interface server, not on the server where the database is installed (except of course, if both components happen to be on the same server).

Then run the batch command file “usejdbc2.bat” (Windows) or “usejdbc2.sh” (Linux) from the “java12” subfolder in the DB2 run-time client installation, i.e. execute the file:

Note: The batch command file creates a different version of the JDBC driver file with the name “db2java.zip”. Make sure to use the newly created file when proceeding to the next step.

This plugin is used for connecting to the MySQL database of version 4.1 and later (tested until 5.0, at the time this was written). This plugin uses the ConnectorJ MySQL driver, which is installed together with LISTSERV Maestro.

Note: This plugin can not be used to connect to MySQL instances with a version earlier than 4.1. If you want to connect to such older MySQL versions, you need to use the MySQL L-Soft Driver Database Plugin (see below).

This plugin is used for connecting to the MySQL database of version 3.23.42 (or later 3.23.x builds) or version 4.x. This plugin uses the L-Soft MySQL driver, which is installed together with LISTSERV Maestro.

Note: This plugin can not be used to connect to a MySQL instance with a version of 4.1 or later. If you want to connect to such newer MySQL versions, you need to use the MySQL ConnectorJ Driver Database Plugin (see above).

These plugins are used for connecting to the Oracle database of version 8i, 9i, or 10g. These plugins use the Oracle Thin driver.

Note: The newer driver for 10g may also work for 9i and 8i and is usually more efficient. It is therefore recommended that you use the newer 10g driver even when connecting to Oracle 9i or 8i.

The driver comes in form of a file called “classes12.zip”. Simply copy this file into the “lib” folder in the LISTSERV Maestro installation:

The driver can be downloaded from the Oracle Technology Network. Look for the releases for Oracle 8i and the classes12.zip file. License conditions from Oracle may apply.

The driver comes in form of a file called “ojdbc14.jar”. Simply copy this file into the “lib” folder in the LISTSERV Maestro installation:

The driver can be downloaded from the Oracle Technology Network. Look for the releases for Oracle 9i (or 10g) and the ojdbc14.jar file. License conditions from Oracle may apply.

This plugin is used for connecting to the SQL Server database of version 6.5, 7.0, 2000 or 2005. This plugin uses the free open-source jTDS driver.

•	How to install the driver: From the binary distribution download, copy the file “jtds-1.2.jar” into the “lib” folder in the LISTSERV Maestro installation:

(At the time this document was written, version “1.2” was the most current version. When a new version is released, the name of the jar file that needs to be copied into the lib folder will probably change accordingly.)

This plugin is used for connecting to the SQL Server database of version 2000 or 2005. This plugin uses the SQL Server driver from Microsoft, which comes in two versions – the older driver for SQL Server 2000 only and the newer driver for SQL Server 2005 (which also works with SQL Server 2000). From the two drivers, the plugin will automatically choose the driver which is currently installed on your system. If both drivers are installed, the plugin will choose the newer driver, which works both with SQL Server 2005 and 2000.

•

How to install the driver for SQL Server 2000: This is the older JDBC driver from Microsoft which works with SQL Server 2000 only. The driver comes in form of a Windows install file. Execute the install file on any computer you like. What is important for the Maestro User Interface is not that the driver is installed on the same computer, but that you copy the following files from the installation folder of the driver to the installation folder of LISTSERV Maestro (shown for a default installation of the driver):

Copy the files “msbase.jar”, “mssqlserver.jar” and “msutil.jar” from the “lib” folder in the SQL Server JDBC-driver installation:

•	How to install the driver for SQL Server 2005: This is the newer JDBC driver from Microsoft which works with SQL Server 2005 and also SQL Server 2000.

The driver comes in the form of a self-extracting *.exe file. Extract the file to a suitable temporary location. Among the extracted files, you will find one file called “sqljdbc.jar”. Copy this file into the “lib” folder in the LISTSERV Maestro installation:

This plugin is used for connecting to the SQL Server database of version 6.5, 7.0, 2000, or 2005. This plugin uses the SPRINTA SQL Server driver from i-net software.

•	How to install the driver: From the SPRINTA download/installation, copy the file “Sprinta2000.jar” (older driver versions) or “Sprinta.jar” (newer driver versions) into the “lib” folder in the LISTSERV Maestro installation:

The driver can be purchased and downloaded from i-net software: http://www.inetsoftware.de. License conditions from i-net software may apply.

Note: The evaluation version of this driver which is (or was at the time this was written) available for download, contains a limitation of the number of concurrent database connections that will make the Maestro User Interface fail during operation. The evaluation version is therefore not supported for use with the Maestro User Interface.

This plugin is used for connecting to any ODBC compliant database or data source, and uses the ODBC driver which is part of Java, which is installed together with LISTSERV Maestro.

The ODBC-driver plugin is a read-only plugin. As such, it can only be used to read recipient data or drop-in content data. It cannot be used for the system database connection, or to create Hosted Recipient Lists. After registering this plugin it will not appear in the list of available drivers on the system connection page in the Administration Hub. However, it will appear in the corresponding lists of the recipient wizard, target group wizard, and database drop-in page.

On Windows installations, this driver is automatically installed together with LISTSERV Maestro, so the only step required to make this plugin available for usage is to register it as described in Section 9.2 Registering a Database Plugin.

The ODBC driver plugin operates differently when compared to the other database plugins. The other plugins bind a specific JDBC driver to LISTSERV Maestro, allowing access to the specific database for which the JDBC driver has been written. Database access then goes through three layers, from the plugin into the JDBC driver and from there into the database as shown below.

In contrast, the ODBC driver plugin has one more layer shown in 7. This plugin binds the JDBC-ODBC bridge driver to LISTSERV Maestro, allowing access to any ODBC driver. The plugin by itself does not define which database is accessible through it. It does however, define that anything that has an ODBC driver available to it is accessible. The ODBC driver for the database in question must also be supplied in addition to the plugin. Installation of the ODBC driver depends on the system and the OS in use. Please see the appropriate documentation for the ODBC driver and the operating system.

Database access goes through four layers starting with the ODBC plugin to the JDBC-ODBC bridge, to the ODBC driver, and ending with the database.

The performance of LISTSERV Maestro when using this driver is directly dependant on the ODBC driver used for the database in question. Accessing a database through an ODBC driver that is programmed inefficiently will impact the performance of LISTSERV Maestro. For example, if the ODBC driver uses up a lot of memory when doing large selects, LISTSERV Maestro may be subjected to a memory shortage caused by the ODBC driver. In that case, the driver is not usable unless it can be used to make smaller selects, or the server's memory is upgraded accordingly.

Important: Extensive testing with the ODBC driver(s) before employing in a production setting is recommended to determine the impact on memory and CPU usage.

Tip: The term “database”, when used with ODBC, is interpreted quite broadly. ODBC drivers for data sources, such as plain text files or if Microsoft Excel files exist, turning them into “databases” in the sense that they can be used to create recipient lists and drop-in content. See the L-Soft White Paper entitled Importing Data from Microsoft Excel into LISTSERV Maestro for an example.

LISTSERV Maestro uses “database plugins” to give access to different JDBC drivers (and through them to different databases) available to the Maestro User Interface. Before a plugin can be used, it must first be registered in the list of known plugins. Some plugins are already pre-registered when LISTSERV Maestro is installed, while others need to be registered after the corresponding JDBC driver has been installed.

To register a new plugin, log into the Administration Hub and click Global Component Settings, then Maestro User Interface, and then finally Database Plugins. Click on the [Register New Database Plugin] button. In the text box, enter the full class name of the plugin to be registered (see Section 9.1 Available Database Plugins).

Click [OK] to submit the class name. If the plugin was registered correctly, it will now appear in the list of plugins. If there was a problem during the registration, an error message describing the problem will appear. The most probable causes for problems are misspellings of the class name (which is case sensitive).