Section 9
Database Connections

The Maestro User Interface component of LISTSERV^® Maestro comes ready to use with its own internal database, which is mainly used to store recipient profiles and tracking information. The database installed with the Maestro User Interface is not intended for high-end production use. It is only intended for low-end use and evaluation purposes. Most notably, the internal database (based on MySQL) comes with a storage size (table space) of only 300 MB, and it has not been optimized for large data volumes.

If LISTSERV Maestro is to be used in a mid- to high-end production environment (generating a significant number of e-mail jobs), seriously consider configuring the Maestro User Interface component to use a production grade database. The Maestro User Interface has been tested together with the following databases:

• Microsoft^® SQL Server 7.0 and 2000
• Oracle^® 8i Enterprise/Standard Edition Release 3 (8.1.7)
• DB2^® Universal Database V7.2
• MySQL™ 3.23.42

Figure 27 Database Connection

Install any of these databases (or a compatible version) on the server where the Maestro User Interface component is installed, or on any other server that is reachable over the network. Next, configure the Maestro User Interface to use this database instead of the internal database. Using an external database is highly recommended in a production environment. Please refer to the documentation of the selected database for details on how to install and configure it.

9.1 Preparing the Database

Before the Maestro User Interface can be used together with a freshly installed database, the database must be prepared in certain ways. Outlined below are explanations of the required preparation steps for each of the supported databases.

9.1.1 Preparing SQL Serve

In the SQL Server management console, create either a new database for use with the Maestro User Interface or configure one of the existing databases so that the Maestro User Interface can share it. L-Soft recommends that a new database for sole use by the Maestro User Interface be created. Please see the SQL Server documentation for details about how to create, configure, and optimize a database.

Once a new database has been created, or an existing one is selected, create a user account that the Maestro User Interface can use to connect to the database. Create a new user with any desired name and give it the db_public role for the created or selected database. Next, in the “Permissions” of the database’s “Properties”, grant the Create Table privilege to this user.

9.1.2 Preparing Oracle

A new Oracle database for sole use by the Maestro User Interface must be created so that it uses UTF-8 as its database character set. The database character set UTF-8 is required and the Maestro User Interface will not work with a database that has a different character set. (See the Oracle documentation for details).

The Maestro User Interface can only use an existing Oracle database if the database uses the UTF-8 character set. To configure an existing database for use with the Maestro User Interface, use an Oracle administration tool (such as SQL*Plus), to create a new user. This new user must have the CREATE SESSION and the CREATE TABLE privilege and a sufficiently large table space quota in the user's default table space.

The Maestro User Interface does not require unusually large rollback segments. If duplicate elimination is performed for large e-mail jobs, larger temporary segments are needed as duplicate elimination is performed with a database sorting operation. See the Oracle documentation for more details on how to configure and optimize databases.

9.1.3 Preparing DB2

To use a DB2 database for the Maestro User Interface, verify that the selected database supports the code set UTF-8. If this code set is not supported by the chosen database, use the DB2 Control Center application to create a new database and define UTF-8 as the database code set.

To configure an existing database that already has the code set UTF-8, verify that at least one user table space and one system temporary table space with a page size of 32K each exist. It may also be necessary to create a buffer pool with a page size of 32K before the table spaces with 32K page size can be created. Next, create a new database user for sole use by the Maestro User Interface. This user must be configured to use a table space with 32K page size; otherwise the Maestro User Interface will not work. The new user needs the Create Table privilege.

Having prepared the DB2 database, the next step is to create a database alias on the server that is running the Maestro User Interface component (this is most likely not the server where the database itself is installed). Do this by starting the IBM DB2 Client Configuration Assistant on the server where the Maestro User Interface component is running. This is a runtime client database tool that comes with the IBM DB2 installation and needs to be installed on the Maestro User Interface component server.

In the Client Configuration Assistant, click the Add button to create a new alias. Select the "Search the network" option and continue with the wizard. For more details on IBM DB2 database administration and the definition of database aliases, see the IBM DB2 documentation. Note that the name of this alias is the value for the "Database name" parameter of the IBM DB2 database plugin that comes with the Maestro User Interface.

9.1.4 Preparing MySQL

To use the Maestro User Interface with MySQL, set up MySQL to use the “InnoDB Tables” table type (see the MySQL manual for more details). This table type supports transactions, and the Maestro User Interface requires a table type that supports transactions. If access to a different table type in MySQL that supports transactions is available, it is possible to attempt to use that table type. However, the InnoDB tables are the only table type of MySQL officially supported by L-Soft for use with the Maestro User Interface.

Set up MySQL with InnoDB data files and log files of sufficient size to accommodate the planned usage of the Maestro User Interface. After the MySQL database server is set up to use the InnoDB table type, choose between creating a new database specifically for use with the Maestro User Interface (recommended), or an already existing database, into which the Maestro User Interface will create its own tables. In both cases, it is necessary to create a user with certain privileges. This user will then be selected from the Maestro User Interface to connect to the database.

To connect to the database, start the MySQL client program mysql.exe from the bin folder of the MySQL binary installation. To create a new database (optional), enter the following command in the MySQL client: create database NAME; where “NAME” is replaced with the name of the database. Grant privileges by entering the following grant command for the username: grant all on DBNAME.*to NAME@HOST identified by 'PASSWORD'; where the uppercase values are replaced as follows:

DBNAME: The name of the database to be used with the Maestro User Interface (usually the same name used in the “create database” command, see above).

NAME: The user name of the user to be created and granted privileges.

HOST: The host name of the server where the Maestro User Interface is running that will access this database.

PASSWORD: The password associated with the user name.

Using grant all as described above grants all privileges on the given database to the given user. This is usually acceptable if the particular database was created specifically for use with the Maestro User Interface. However, if there is concern about granting the full set of privileges to the user (especially if the Maestro User Interface is to share an already existing database), use the following privilege list instead of “all”:

select, insert, update, delete, index, create, drop

Back One Page | Forward One Page