Advanced Topics Manual for LISTSERV 17.0

Post-installation instructions have been broken down by feature. References to the “LISTSERV configuration” correspond to SITE.CFG under Windows, and go.user for unix systems.

1.4.1 Mail-Merge

For best performance, make sure that MAXBSMTP is set to at least 5000. If you want to allow individual list owners to send mail-merge messages for their respective lists, set the DIST_OWNER_MAIL_MERGE configuration variable to the value 1. By default, this option is disabled. Note that the LISTSERV administrator is always allowed to send mail-merge messages; it is assumed that the administrator knows whether mail-merge is supported, whereas individual list owners may not have this information.

1.4.2 ODBC Interface (Windows)

(For unixODBC, please see Section 1.4.5 unixODBC (UODBC) Interface.)

To activate the Windows ODBC interface, you must first create a system-wide ODBC data source.  This MUST be done with the 64-bit verson of the ODBC management application.  Thankfully, this is the default under 64-bit Windows systems, and the icon can be found in the Administrative Tools folder from the Start Menu.  Alternately, click the “Run” command, and type

%windir%\system32\odbcad32.exe

into the dialog box, then click OK.

Once the management app is open, select the System DSN tab, click [Add...], and then select the appropriate driver and fill out the driver-specific form.

(Under Windows Server 2016 and later, Microsoft have placed links for both the 32-bit and 64-bit versions of the ODBC interface into the Windows Administrative Tools start menu group, so it should no longer be necessary to use the workaround above.)

For the purposes of this example, we will assume that the DSN you just created is called GREEN. You would then add the following lines to SITE.CFG:

ODBC_DSN=GREEN ODBC_UID=...
ODBC_AUTH=...

Replace the ellipses with appropriate (DBMS-specific) authentication information. While officially called the “authentication string” in the ODBC specifications, ODBC_AUTH is often called “password” in vendor documentation.

See Section 1.4.7 Generic DBMS Post-Installation Tasks for instructions that are common to ODBC and OCI.

1.4.3 OCI Interface

To activate the OCI interface, you must add an OCI_CONNECT parameter to your LISTSERV configuration. Its value should be an OCI configuration string, typically a service name from TNSNAMES.ORA. In addition, you may need to add OCI_UID and OCI_PWD parameters. If you do not supply a userid and password, the OCI interface will login with external (i.e. operating system) authentication.

Note: Under Windows, external authentication is based on the Windows userid under which the application (LISTSERV) is running. When you run LISTSERV interactively, you are usually not running LISTSERV under the same Windows userid as when it is started as a service. Thus, if using external authentication, Oracle may fail to login when started as a service. To circumvent this problem, use standard authentication (IDENTIFIED BY password) or use the Control Panel to change the account under which the LISTSERV service is running.

See Section 1.4.7 Generic DBMS Post-Installation Tasks for instructions that are common to ODBC and OCI.

1.4.4 CLI Interface

This support is similar to the ODBC support documented in Section 1.4.2 ODBC Interface (Windows), but the configuration variables are called CLI_DSN, CLI_UID, and CLI_AUTH.

1.4.5 unixODBC (UODBC) Interface

The prerequisite for unixODBC support is that unixODBC must be installed on the unix machine that is running LISTSERV.

The LISTSERV implementation is similar to that for ODBC and CLI, except that the prefixes are UODBC_*. You define UODBC_DSN and so on in your configuration, and you use “DBMS= UODBC” or just “DBMS= Yes” in your lists and DISTRIBUTE jobs. For instance, the site configuration for a unixODBC datasource called GREEN might look like this:

UODBC_DSN=”green”
UODBC_UID=”listserv”
UODBC_AUTH=”fiatlux”
export UODBC_DSN UODBC_UID UODBC_AUTH

It is possible to connect to all kinds of databases (as opposed to MySQL only) using unixODBC, but this interface is designed primarily to work with MySQL, and does not formally support other DBMS products accessible via unixODBC. We would be interested in hearing the results of tests conducted in the field with other DBMS products and would be willing to consider adding support based on those results.

Because the documentation for unixODBC is sparse and sometimes contradictory, we provide instructions for a simple installation and configuration of unixODBC in Section 1.8 Installing and configuring unixODBC with LISTSERV and MySQL, below.

1.4.6 Connecting to multiple simultaneous database sources

Alternate data sources are specified as follows: In a List Header:

* DBMS= Yes,...,SERVER(server_alias)

In an ad-hoc DISTRIBUTE job:

DISTRIBUTE MAIL-MERGE DBMS=YES(EMAIL=EMAILADDR,...,SERVER=server_alias)

The SERVER specification is optional; it defaults to SERVER(DEFAULT), which for backward compatibility is identical with the server defined in ODBC_DSN= in the site configuration file.

When defining an alternate server, it is recommended that server_alias be defined as an alpha string as opposed to a numeric string, in order to avoid certain problems in some ODBC drivers. There is no character limit for the server_alias string, but for simplicity’s sake it should not be overly long.

To use ODBC as our first example, the default ODBC data source is defined in the site configuration file as before, using the site configuration variables ODBC_DSN= ODBC_UID=, ODBC_AUTH=, and so forth. (Obviously, if you are using OCI or CLI, you use the equivalent site configuration keywords for those interfaces instead).

Alternate ODBC data sources are then defined with additional parameters of the form

ODBC_DSN_server_alias=
ODBC_UID_server_alias=
ODBC_AUTH_server_alias=

For OCI, you first define your default OCI connection, then alternate OCI data sources

are defined with additional parameters of the form

OCI_CONNECT_server_alias=
OCI_UID_server_alias=
OCI_PWD_server_alias=

Similar to ODBC, the default data source for OCI is OCI_CONNECT=, not

OCI_CONNECT_DEFAULT=.

For CLI, you first define your default CLI connection, then alternate CLI data sources are defined with additional parameters of the form

CLI_DSN_server_alias=
CLI_UID_server_alias=
CLI_AUTH_server_alias=

A default data source MUST always be defined, otherwise the driver will disable itself.

Finally, for unixODBC, you first define your default UODBC connection, then alternate UODBC data sources are defined with additional parameters of the form

UODBC_DSN_server_alias=
UODBC_UID_server_alias=
UODBC_AUTH_server_alias=

A default data source MUST always be defined, otherwise the driver will disable itself.

You do not have to use the default data source for anything and it does not even need to be a valid login, but it needs to be there as an indicator that you have configured ODBC/OCI/CLI and want it to be activated. The driver itself does not know which data source(s) will be accessed until it is called for the first DISTRIBUTE job or list posting after startup.  The default data source can be as simple as the following (using ODBC as an example):

ODBC_DSN=NOLOGIN
ODBC_UID=NOLOGIN
ODBC_AUTH=NOLOGIN

1.4.7 Generic DBMS Post-Installation Tasks

A number of additional configuration variables are available to alter the default behavior of the DBMS interface. In the following discussion, a “DBMS list” refers to a list whose membership data is stored in a DBMS, as opposed to a traditional LISTSERV list where LISTSERV keeps the membership data in the xxx.LIST file.

• DBMS_DEFAULT_TABLE (default: LISTSERV) – default value for the name of the table in which DBMS lists are stored. Specify either a valid table name, or an asterisk to name the table after the list.
• DBMS_DEFAULT_EMAIL (default: EMAIL) – default value for the name of the column containing the subscriber’s e-mail address.
• DBMS_DEFAULT_UEMAIL (default: empty string) – default value for the name of the optional column containing an upper-case copy of the subscriber’s e-mail address. If set to the empty string, no such column is created or used. See below for more information on this performance option.
• DBMS_DEFAULT_NAME (default: NAME) – default value for the name of the column containing the subscriber’s name.
• DBMS_DEFAULT_OPTIONS (default: *) – default value for the name of the column containing the subscriber’s LISTSERV subscription options. An asterisk indicates that the column should be named after the list.
• DBMS_NO_HOSTNAME_ALIASING (default: 0) – when set to 1, disables hostname aliasing for increased performance with some DBMS products. L-Soft recommends using the default value of 0.

When using all the default options, DBMS lists will be kept in a table called LISTSERV, with the e-mail address in a column called EMAIL, the name in a column called NAME, and one additional column for each DBMS list. The name and e-mail address will be shared, that is, a change in the user’s name for list A is automatically reflected in list B. Of course, each of the layout parameters can be overridden on a per-list basis.

1.4.8 Performance Options

The DBMS interface offers two options to improve lookup performance and overcome a fundamental difficulty in obtaining good e-mail lookup performance from a traditional SQL DBMS. An Internet e-mail address, as stored by LISTSERV in the DBMS, has the following format:

userid@hostname

Both halves of the address present a performance challenge.

The userid is case-sensitive. Internet standards and current industry practice demand that the case of the userid be respected. Failure to do so will lead to undelivered mail. Thus, LISTSERV must store the userid in the DBMS exactly as it was provided to LISTSERV. People, on the other hand, are not used to making a difference between JOE and Joe. When asking LISTSERV to remove Joe from the list, they will expect JOE’s subscription to be cancelled, not a message claiming that there is nobody named Joe on the list. Thus, LISTSERV must make a case-insensitive search when looking up an address:

SELECT ... WHERE UPPER(EMAIL) = ‘JOE@XYZ.COM’;

Unfortunately, even on an indexed column, this search will typically require a full table scan. While it would be technically possible for the DBMS to use the index together with the UPPER function, in practice this optimization has not been implemented, because it is not frequently required in a typical DBMS environment. To alleviate this problem, LISTSERV supports the optional use of an additional column, containing an upper case copy of the e-mail address. The above search can then be rewritten as:

SELECT ... WHERE UEMAIL = ‘JOE@XYZ.COM’;

This search will make use of any available indexes. The drawback is that the column takes up additional space and must be set and changed together with the e-mail address, or lookup results will be incorrect. If the table is to be updated by applications external to LISTSERV, L-Soft recommends adding a CHECK clause to make sure that the UEMAIL column is always set correctly. If LISTSERV is the only writer, this is not necessary.

While the hostname is not case-sensitive, many sites use mail systems where users appear to have a different hostname based on a variety of technical factors. LISTSERV supports a feature called “hostname aliasing” which allows it to know that, for instance, joe@classic.msn.com and joe@msn.com are the same person. Thus, when your customer support department receives a complaint from joe@msn.com asking to be deleted from your mailing lists, LISTSERV will automatically delete joe@classic.msn.com rather than report that Joe is not subscribed to the mailing list. The drawback, however, is that LISTSERV must formulate the lookup as follows:

SELECT ... WHERE UEMAIL LIKE ‘JOE@%’;

LISTSERV then reviews the selected entries and determines whether they are a valid match for joe@msn.com. With a good DBMS, this will not introduce any performance problem. The DBMS will use the index for LIKE searches until the first wildcard is encountered, and in practice there will not be many entries left to scan. Some DBMS, however, may simply not use the index for LIKE searches. In this case, you may want to set the DBMS_NO_HOSTNAME_ALIASING configuration parameter to 1, to disable the hostname aliasing feature for DBMS lists. The drawback, of course, is that LISTSERV will not be able to match joe@msn.com to joe@classic.msn.com. L-Soft recommends upgrading to a more advanced DBMS product rather than disabling hostname aliasing.