1.2 Pre-installation tasks
Before installing DBMS and mail-merge support, please review the following steps and make sure that your selected target system is ready to receive this update.
- DBMS support works with any modern version of LISTSERV Classic or Classic HPO, but the newer the version, the better, as older versions may not have enhancements and bug fixes included in newer versions. We strongly recommend upgrading to the latest release version of LISTSERV prior to enabling DBMS support.
- Mail-merge support requires LISTSERV Classic or Classic HPO.
- DBMS-enabled LISTSERV versions prior to 14.5 must set EMBEDDED_MAIL_MERGE set to 1 (enabled) in the site configuration file. This is the default setting in LISTSERV 14.5 and later.
- If you are planning to use the DBMS interface, you must install vendor-supplied DBMS support files on the target machine before installing the LISTSERV update.
- For ODBC (Windows), the appropriate drivers are already installed as part of the operating system under supported versions of Windows.
- For ODBC (Unix), you will need to install unixODBC. At minimum you will need the base 64-bit unixODBC package. Some systems may also need unixODBC-devel, but in most cases, LISTSERV relinks correctly with only the base package installed.
- For OCI, you need to install and configure the Oracle client files (SQL*Net et al.) The OCI material is typically licensed and not freely redistributable, and thus does not come with the LISTSERV kit. (Note that OCI is not supported natively by LISTSERV under Windows, butOracle databases can be accessed via ODBC.)
- For DB2, you need to install the appropriate IBM JDBC drivers for your operating system and DB2 version.
- If you are using Microsoft Windows, you must be running a supported version of Windows. L-Soft drops support for Windows versions when Microsoft ends what they call “extended support”. Our current LISTSERV installation kits for Windows query the operating system for the current version and service pack, and will abort the installation if you are not running the minimum required version. For a list of the Windows operating systems currently supported by L-Soft, please visit http://lsoft.com/products/listserv_os.asp .
- If using the DBMS interface, you may wish to create a DBMS username for LISTSERV in advance, and grant it the CREATE SESSION (mandatory) and CREATE TABLE (optional) privileges. If you are planning to create all tables yourself, you should not grant CREATE TABLE to LISTSERV’s DBMS username. If you want LISTSERV to be able to create tables on its own, you MUST grant CREATE TABLE to LISTSERV’s DBMS username.
- A compiler (such as gcc) is required to use the OCI interface on unix systems. L-Soft may not legally ship pre-linked executables containing the SQL*Net library.
- A compiler is also required to use the CLI interface on AIX, for similar reasons.
1.2.1 Selecting a Suitable DBMS Product
This section applies only to ODBC users (including Oracle users under Windows). OCI users (except under Windows) will always be using Oracle, and CLI users will always be using DB2, neither of which exhibit any of the problems mentioned in this section.
While L-Soft does not ordinarily recommend or endorse specific hardware or software brands, some database products (especially low-end PC offerings) may not be suitable for use together with LISTSERV. Without advocating one brand over another, L-Soft recommends the use of a DBMS that does not exhibit the problems mentioned below. All error messages are in reference to the diagnostics printed by the LISTSERV ODBC interface during startup.
- [FATAL] LIKE operator has no ESCAPE clause, errors will occur
This error indicates that the DBMS does not support any kind of “escape clause” for the LIKE operator. In practice, it means that whenever LISTSERV attempts a search containing a percent sign or underscore, the results will be incorrect (you may also get an ODBC error). This makes the DBMS unusable as a data store for LISTSERV lists. However, if you only plan to use the DBMS for mail-merge jobs, this restriction may be immaterial as LISTSERV will only be executing the SQL statements that you provide in the mail-merge job.
This should not be an issue for SQL Server users, as ESCAPE support was added many years ago in version 6.5. However, the Microsoft Access DBMS product appears to have this restriction, and as such, is not supported as a data store for LISTSERV lists.
- [SEVERE] Max active stmt: 1 - expect uncommitted read & unrequested commits
With a maximum of one active statement per transaction, the ODBC interface is unable to carry out typical SELECT ... UPDATE ... UPDATE ... COMMIT sequences using a single transaction, because the SELECT remains active until the COMMIT and prevents the execution of the UPDATE statements. To bypass this problem, the ODBC interface will use two transactions for these sequences. However, the two transactions will typically look like independent applications to the DBMS, and will suffer from “transaction isolation,” a vital DBMS feature that permits shared database access by multiple unrelated applications. As LISTSERV expects that an update will be reflected in a subsequent search, whether it has been committed or not, the ODBC interface will be forced to commit updates before beginning a new SELECT, even when LISTSERV had not requested a commit. In addition, if the DBMS does not support row-level locking, the ODBC interface will hang when attempting to execute the UPDATE statement, because the table is locked by the transaction containing the SELECT To avoid this, the ODBC interface may switch to the lowest level of transaction isolation, “uncommitted read.” If LISTSERV is the only application writing to the tables containing the data, this will be of no consequence, otherwise LISTSERV may see uncommitted changes made by other applications.
SQL Server artificially exhibits this condition. In reality, SQL Server does support multiple statements per transaction, but its ODBC driver reports otherwise, and will only allow one statement per transaction unless using dynamic cursors (or a related option). Dynamic cursors, however, will only work (with SQL Server) if the table contains a unique index; otherwise, the cursor is downgraded and leads to the one-statement behavior. It is possible that third-party ODBC drivers may allow and report unlimited statements per transaction.
- [SEVERE] FOR UPDATE clause not supported, no locking will occur
Some entry-level DBMS products may not support locking at all, or may only support it through a proprietary interface (rather than via SQL commands). In that case, LISTSERV will be unable to lock the rows it is in the process of updating, which may lead to incorrect behavior if you have other applications updating the tables.
- [SEVERE] ‘=’ operator is case-insensitive, results may be incorrect
With some DBMS products, the equality operator is case-insensitive. While LISTSERV is generally case-insensitive, it does of course have the ability to make case-sensitive searches, and will do so on occasion. LISTSERV’s built-in data management functions typically use the e-mail address as a unique, case-sensitive, primary index into the list, and LISTSERV assumes that it can use the e-mail address as a kind of “row ID” if the need arises. LISTSERV is not programmed to “doubt” the equality of a successful search for an e-mail address it had previously retrieved, as this is algorithmically impossible with its built-in functions. While this is a severe error in that it can lead to incorrect results, in practice it only has limited impact.
SQL Server usually has case-insensitive equality configured by default. While it is possible to change this setting, this can only be done by reinstalling the product from scratch, which is usually unacceptable. In addition, the setting is global and affects all tables, all users, etc. In practice, the impact does not warrant the re-installation of an existing system.
- Cursor behavior limits ability to commit before logical close
This warning indicates that the DBMS will “forget” the results of any active SELECT statement whenever a transaction is committed. As LISTSERV’s built-in data management functions do not have this restriction, LISTSERV will often commit changes while a search is in progress. For instance, if you issue the command SET XYZ-L DIGEST FOR *@AOL.COM, LISTSERV will search for users matching the pattern *@AOL.COM and, for each match, update the subscription options, send an e-mail message, and commit. This way, if the command is interrupted and re- executed, users who already received an e-mail notice will not receive a second copy. When this warning is printed, the ODBC interface will ignore any commit request from LISTSERV that would abort an active search. The transaction will always be committed when the LISTSERV command completes, so the final results will always be accurate. However, in extreme cases (such as a SET command updating every record in the database), the transaction might generate a very large amount of uncommitted data, and require a lot of rollback space.
SQL Server users should note that while the SQL Server ODBC driver will cause this message to be displayed unless a non-standard ODBC call has been issued in advance, in practice it has no operational impact as the ODBC interface will have to use a separate transaction for update activity anyway. Thus, the ODBC interface is free to commit whenever requested by LISTSERV. SQL Server itself is able to preserve active SELECT statements across a commit, it is the ODBC driver which requests the “close cursors on commit” behavior.
- Multi-row operations are unavailable
This is a performance warning indicating that bulk ADD operations may be slowed down. In practice, this warning only occurs with DBMS packages that offer limited performance anyway, and can be safely ignored.
Except as specifically indicated above, any error marked as FATAL or SEVERE can potentially lead to incorrect results. If you are planning to use the DBMS as a data store for LISTSERV lists, FATAL errors are unacceptable and SEVERE errors need to be investigated carefully. If you are planning to use the DBMS interface only for mail-merge operations, both SEVERE and FATAL errors may be acceptable as LISTSERV will only be executing the SQL statements provided in the mail-merge job. The script or person providing these statements is then responsible for making the necessary adjustments to work around the DBMS restrictions.