In this section, you will find a brief description of the DBMS features, along with installation instructions and a few samples.
While the DBMS and mail-merge functions were designed to work together, they can also be used independently from each other. That is, you may find the DBMS support useful even if you have no need for mail-merge functionality, and likewise you can use the mail-merge functions without a DBMS back-end. Both functions require LISTSERV Classic or LISTSERV HPO, and are unavailable in LISTSERV Lite.
Note: Embedded mail merge is the default for LISTSERV 14.5 and following.
|
•
|
Direct LISTSERV to store subscriber information in a DBMS, on a list by list basis. That is, you may have a mix of traditional LISTSERV lists and DBMS lists. Furthermore, you can adjust the layout of your DBMS lists to match existing or current applications. You can map each list to a private table if this is what makes sense for you, or you can put all the lists in the same table, place related lists in one table, etc. You can add as many columns as you want to store additional information about subscribers.
|
DBMS support is available through Microsoft's ODBC interface on Windows 2000 and greater, and Oracle's OCI interface on OpenVMS Alpha, Digital Unix, AIX and Solaris (SPARC only). Additionally, DB2 is supported natively (i.e. via CLI) under the unixes which are supported by both DB2 and LISTSERV.
L-Soft does not support Microsoft Access (any version) as a datastore for mailing lists.
Oracle introduced major changes to the OCI API in version 8. While many of the concepts remain similar, all the function calls have been renamed and most have a different calling sequence. Supporting both OCI 7 and OCI 8 would require more than a handful of #ifdef statements – we would need to develop and support a separate interface for OCI 7. In addition, we would need to license Oracle 7 on all supported systems, which would significantly increase our development costs. While we recognize that there is a large Oracle 7 installed base, a year or so from now most of them will have upgraded to Oracle 8, thus the cost for developing an OCI 7 interface and purchasing six Oracle 7 licenses would have to be amortized over a period of about a year, leading to much higher licensing costs for the OCI 7 version. Furthermore, it is possible to use LISTSERV's OCI interface with an Oracle 7 server: the only component which must be at version 8 is the client, i.e. SQL*Net. As Oracle-based LISTSERV installations typically run on a separate, dedicated system, this simply means that you need to purchase a version 8 or higher client license for the system in question. It is not necessary to upgrade the server to Oracle 8 or higher. Oracle 7 is also supported through the ODBC interface.
Documented Restriction: In order to use the mail-merge features, you must set the site configuration variable EMBEDDED_MAIL_MERGE to a value of 1 (that is, enabled). This is the default.
LISTSERV's mail-merge support allows you to send individually customized messages to large numbers of recipients with very high throughput. The mail-merge functions support:
|
•
|
A simple bounce processing and collection system – LISTSERV processes and decodes all bounces, and writes the failing addresses to a plain-text file. You can group related mailings in the same bounce file or use a separate file for each mailing, whichever makes the most sense in your context. As each message is sent in "probe" format, even non-standard bounces will be processed accurately, as long as the remote MTA sends bounces to the correct (RFC821 MAIL FROM:) address.
|
|
•
|
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 OCI, you need to install and configure the Oracle8 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, but can be accessed via ODBC.)
|
|
•
|
If you are using Windows, you must be running at least Windows 2000 with Service Pack 4 applied. Windows NT 4.0 is no longer supported. The current Windows installation kits 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. L-Soft no longer supports Windows NT. Windows 2000 (Server and Workstation), Windows 2003 Server, and Windows XP (Professional and Home) are currently supported.
|
This section applies only to ODBC users. OCI users 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.
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.
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.
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.
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.
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 only planning to use the DBMS interface 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.
Installation instructions have been broken down by operating system. Make sure that your current LISTSERV license is installed before you begin, and that you have made a backup of your LISTSERV directory tree.
This is a standard VMSINSTAL kit. If you want to install the OCI interface, you must run the appropriate ORAUSER.COM procedure before installing the kit. Otherwise, you will not be asked whether you want to install OCI support.
LISTSERV 14.5 for unix kits support Oracle, DB2, and MySQL (via unixODBC) in a single, universal kit. This kit contains both a precompiled 'lsv' executable (which does not support any database), and a set of object files allowing you to link a new 'lsv' that supports any combination of databases for which you have a run-time environment on the machine running LISTSERV. The following object files are included:
If a particular database is not available for your operating system, the corresponding noxxx.o file will have been pre-linked into lsv.o and will not be included in the kit. A table showing LISTSERV DBMS support under unix follows:
LISTSERV cannot be relinked with support for both CLI and unixODBC at the same time. This is due to the fact that the two implementations are quite similar and share function names inside LISTSERV.
The current LISTSERV for unix kits contain a Makefile which is set up to relink 'lsv' without any DBMS support by default. A new "OS-specific flags" section has been added where you can add or remove DBMS support simply by adding or removing the reference to the appropriate no*.o file. For instance, if you are running LISTSERV 14.5 under Solaris, the default flags line is
For unixODBC, you must leave CLI support enabled because CLI and unixODBC share internal function names in LISTSERV, as noted above. In addition, you must also link explicitly to the unixODBC library (the -lodbc flag).
Note: You should not relink with DBMS support unless you have the appropriate DBMS support (SQL*Net, DB2, unixODBC) installed on your machine. Without this support, the link option will fail.
When done, start LISTSERV normally and send a few test commands before proceeding with the post-installation tasks. This will ensure that the installation was successful and that, where applicable, the appropriate environment is configured for the OCI or CLI library. Note that the DBMS interface will not be enabled until you add the necessary authentication/login information to your configuration files.
Post-installation instructions have been broken down by feature. References to the "LISTSERV configuration" correspond to SITE.CFG under Windows, SITE_CONFIG.DAT under OpenVMS and go.user for unix systems. Do not forget to export configuration variables under unix.
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.
To activate the ODBC interface, you must first create a system-wide ODBC data source. Open the Control Panel, double-click on the
ODBC 32 icon, select the System DSN tab, click
[Add...], and then select the appropriate driver and fill out the driver-specific form. L-Soft does not recommend supplying passwords in a system-wide DSN (if offered at all by the driver) because any Windows NT user has access to this information.
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.
15 Oct 2008 18:18:27 Connecting to ODBC data source MYDBMS...
>>> IM002/0: [Microsoft][ODBC Driver Manager] Data source name not found and
no default driver specified
>>> 08003/0: [Microsoft][ODBC Driver Manager] Connection not open
15 Oct 2008 18:18:27 >>> Error X'0100003B' opening DBMS list <<<
15 Oct 2008 18:18:27 -> Severity: Error
15 Oct 2008 18:18:27 -> Facility: DBMS interface
15 Oct 2008 18:18:27 -> Abstract: SQL error
On an x64 system there are two versions of the "ODBC Datasource Administrator" tool: One version for 64-bit and one version for 32-bit. When creating a datasource you have to take care to use the correct tool in respect to which client programs will be using the datasource. That is, if a 32-bit program (for example, LISTSERV) needs to access the datasource, you have to create this datasource using the 32-bit version of the tool.
Unfortunately, the shortcut to the tool which you can find at the usual location in "Control Panel" -> "Administrative Tools" points to the 64-bit version of the tool without even a hint that there also is a different version. The 32-bit version is in a special sub-folder of the Windows folder.
Note: Both files have the same name "odbcad32.exe", even the 64-bit version - the difference in the two files is their location, where again it is confusing that the tool for the 32-bit datasources is in a folder that is called "SysWOW64"...
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 NT, external authentication is based on the NT userid under which the application (LISTSERV) is running. When you run LISTSERV interactively, you are usually not running LISTSERV under the same NT 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.
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:
It is possible to connect to all kinds of databases (as opposed to MySQL only) using unixODBC, but L-Soft has introduced this interface primarily to work with MySQL, and does not formally support other DBMS products accessed 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 below.
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 an 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).
Similar to ODBC, the default data source for OCI is OCI_CONNECT=, not OCI_CONNECT_DEFAULT=, in order to be backwards compatible with 1.8d syntax.
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.
Note: Not all database types are supported across all platforms. For instance, CLI is not currently supported natively under the Windows NT/2000 version of LISTSERV 1.8e, but note that CLI databases can be accessed via ODBC, if the need arises.
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.
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.
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:
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:
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:
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:
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.
The use of the DBMS as a data store for list membership information is controlled by the "DBMS=" list header keyword. To create a DBMS list, specify a "DBMS=" keyword as follows:
Brackets indicate optional parameters. That is, "DBMS= Yes" defines a DBMS list with the default values for TABLE, EMAIL, NAME, UEMAIL and OPTIONS, as described in Generic DBMS post-installation tasks above. The EMAIL, NAME, UEMAIL and OPTIONS column should have a data type compatible with VARCHAR (not CHAR or other fixed-length data types). The UEMAIL column, if used, should be the primary key (at a minimum, it must be indexed), and should have the same width as EMAIL; the EMAIL column should not be indexed. You can set the column widths as you want, however LISTSERV will assume that EMAIL is at least 80 characters wide, and OPTIONS should allow at least 40 characters. If you let LISTSERV create the table, it will use a width of 128 for all columns.
Note: OCI support for Windows NT/2000 has been discontinued in version 1.8e (13.0) and following as LISTSERV now supports multiple simultaneous ODBC database connections.
When migrating an existing list to use a DBMS, you are responsible for migrating the subscriber data to the DBMS, if necessary (in many cases, the subscriber data will already be in the DBMS, possibly in a slightly different format). Once you add the "DBMS= Yes" keyword, LISTSERV stops accessing subscriber data from the xxx.LIST file and uses the DBMS instead.
Subscribers can be imported into a DBMS list using the ADD IMPORT command, also known as "bulk add." However, despite the name this is not an "import" operation in the database sense; it does not disable logging or rollback and is based on normal transactional operations. You may be able to obtain better performance using specialized import tools provided by your DBMS vendor.
While ADD IMPORT is very efficient with a traditional LISTSERV list, it still eliminates duplicates and, when appropriate, updates existing rows with the data in the ADD IMPORT job. That is, it does not assume that all the new data can simply be inserted into the table. In SQL terms, an ADD IMPORT job is a series of SELECT ... UPDATE and SELECT ... INSERT sequences. Even with an index, this kind of workload tends to have transaction rates in the dozens or at best hundreds per second, rather than thousands.
With hostname aliasing disabled, a UNIQUE constraint on the e-mail address, and ignoring case-sensitivity issues, it would be possible to just issue INSERT statements, let the DBMS reject those for which a row with the unique key already exists, and re-issue them as UPDATE statements. However, most people will use hostname aliasing, there can be no guarantee that a UNIQUE constraint is present, and case-sensitivity is not easily ignored. Instead, a new option was added to ADD IMPORT, directing LISTSERV to preload the existing e-mail keys in memory before starting the transaction. This allows LISTSERV to skip most of the SELECT statements and just issue a large number of INSERTs.
You should use this option whenever importing a new list into the DBMS, or whenever adding a very large number of new users. If you need to add 100 entries to a table with a million rows, it will actually be slower to preload the rows, at least if you have an index.
The PRELOAD option is not necessary for traditional LISTSERV lists and does not normally lead to a significant performance improvement. However, when importing a new list (no existing subscribers), it does reduce CPU usage somewhat, and was used in the benchmark suite in order to provide an apple to apple comparison.
The purpose of this benchmark is to give you a rough idea of the maximal performance that can be expected from an ADD IMPORT job. The benchmark adds 100,000 users to an empty list, without duplicates. That is, while LISTSERV still needs to determine that there are no duplicates, the job ends up being a pure INSERT workload. On a 1-CPU 300MHz Pentium II system with one IDE drive and Windows NT 4.0, the results were as follows:
Note: The UEMAIL column was not used in this benchmark, and the table was created with no index. In a typical DBMS import scenario, the index is not created until all the data has been imported, as this would significantly impact import performance. Hostname aliasing was enabled, which is the recommended setting.
Even with the slower DBMS products, bulk add times will usually be deemed satisfactory. However, traditional lists are always faster, because their data organization and access methods are optimized for LISTSERV's purposes. The difference is even more noticeable with a real-world workload containing duplicates and a higher proportion of SELECT statements, as illustrated by the following benchmark, in which 1% of the addresses in the ADD IMPORT job were duplicates (LISTSERV searched for the entry and updated the NAME field). Here an index was used on the UEMAIL column, as 1,000 full table scans would have led to unacceptable performance.
Note: An index was created as the recipients were added, whereas the first benchmark populated a table that would need to be indexed after the fact.
Whereas the first benchmark was a best-case scenario, 1% of duplicates could be considered the worst likely possibility. As you can see, duplicates have virtually no impact on LISTSERV's built-in data management functions, which were designed specifically for this purpose. The DBMS, on the other hand, are required to create an index on the fly, which is contrary to normal practice and significantly decreases performance. On the other hand, 1,000 full table scans would have taken even longer.
DBMS lists can be updated from an external application (by directly making changes to the DBMS tables) with essentially no restrictions and no particular precautions. If you are using an UEMAIL column, however, you must make sure to keep it synchronized with EMAIL, as LISTSERV will assume that, for every row, UEMAIL = UPPER(EMAIL). L-Soft recommends using a trigger procedure or adding a CHECK clause if using an UEMAIL column with an external application directly updating the table.
To delete a subscriber from a DBMS list, you can either delete the row or set the OPTIONS column (which is typically named after the list) to NULL. By definition, if the OPTIONS column is NULL, the user is not subscribed to the corresponding list. When LISTSERV deletes a subscriber from a DBMS list, it deletes the row if the table is named after the list (i.e. if you have the list in a separate, dedicated table). If the table is shared, it sets the OPTIONS column to NULL.
To add a subscriber to the list, simply set the OPTIONS column to the empty string (or insert a new row with OPTIONS set to the empty string). This subscribes the users with all the default options for the list in question.
Please note carefully that most if not all database applications make a distinction between a NULL value and the empty string. If you are having trouble making users added from an external application show up when you review the list, ensure that you are setting the OPTIONS column to "" (the empty string) rather than NULL.
Conversely, if you are having trouble deleting users via an external application, ensure that you are really setting the OPTIONS column to NULL rather than to the empty string.
You can change NAME at any time and without any special precautions. Likewise, you can change EMAIL at any time, as long as you update UEMAIL simultaneously (if you are using an UEMAIL column).
The simplest way to change subscription options is to use the TCP/IP command interface (the "TCPGUI" interface, see chapter 6) to submit a SET command. However, you can also do this by changing the value of the OPTIONS column, which is a series of numbers (in decimal form) separated with semicolons, as follows:
The first number is a version identifier, and must always have the value 4. The second number, flags_1, is a bitmask defining a first set of subscription options, as follows:
Note: Options not found in the table (e.g. MAIL or POST) have a bit value of 0 and do not need to be set to be enforced. To negate them, set the complimentary command (e.g. NOMAIL or NOPOST) with the appropriate bitmask value from the table.
Notes on UTF-8: The "reserved" bit value 4194304 indicates that the subscriber's name in the list record is in UTF-8. Normally, LISTSERV sets this value automatically at subscribe time based on the character set found in the "real name" field. In this case, the flag should always be left in the state in which it is found.
When adding DBMS records using extra-LISTSERV methods, this flag should be set if you are using UTF-8 in the name field. If the name field is plain ASCII, then you do not need to set this flag. While it is unlikely that setting this flag to “on” for a plain ASCII name field will cause problems, it may cause up conversion to UTF-8, which may not be desired. The best practice is to leave this flag “off” for plain ASCII.
Bit positions marked as "reserved" must be left unchanged if updating an existing entry, and set to zero when creating a new entry. They do not correspond to subscription options and are used for internal book-keeping purposes. Please note that certain options are incompatible with each other, or require that other options be enabled. If these restrictions are not observed, results will be unpredictable.
The third number, flags_2, is a bitmask reserved for future use. While no bits are currently defined, make sure to leave its value unchanged when updating an existing entry, and to set it to zero when creating a new entry. The fourth number is used internally by LISTSERV and should be handled similarly.
The fifth number is the date at which the user subscribed to the list. You should leave it unchanged when updating an existing entry. For new entries, you can either set it to zero (in which case, LISTSERV will report the subscription date as unknown), or set it to the number of complete days (i.e. not counting today) elapsed since and including January 1, 0001. For verification, this number is congruent to zero modulo seven on Mondays. It is equal to the C language expression time(0)/86400 + 719162 or, for OpenVMS users, to the Smithsonian base date plus 678575. (If you are familiar with the REXX language, this value is strictly equal to the value returned by Date('B').) As an example, the value for 23 June 1999 is 729927.
DOCUMENTED RESTRICTION: If you do not set the subscription date field for a new subscriber, LISTSERV will probe the address at least once, immediately, regardless of the Renewal= setting for the list and/or regardless of whether NORENEW is set for the address. If you do not want new subscribers to receive an initial subscription probe, be sure to set the subscription date!
The sixth number is a bitmask indicating which topics the user is subscribed to. The lowest order bit (value 1) corresponds to the pre-defined OTHER topic. The second lowest order bit (value 2) corresponds to the first topic in the "Topics=" keyword, and so forth. If you set this number to zero, the user will not be subscribed to any topics. To subscribe a user to all topics, use the value 16777215.
Sometimes using the list's pre-set defaults by setting the column value to the empty string is not acceptable or useful. In that case you can set the OPTIONS column values per the table above. This section provides a typical example of how the OPTIONS column values are used. (See Section 4.5.7
Preserving Options When Migrating from Non-DBMS to DBMS Lists if you are migrating from a "standard" LISTSERV list to a DBMS-type list.)
Note: If you are changing existing values, you should not change the values in the third and fourth positions, as they are reserved for LISTSERV's internal use. Only when adding new users should these positions be set to zero.
If you want the simplest defaults (which is not recommended, because the default when set this way is to use SHORTHDR and at minimum you should set new users to FULLHDR), you set
in the OPTIONS column and the users are added to the list. (Remember that the OPTIONS column is normally named after the list; we are not talking about a table column that is explicitly named "OPTIONS".)
This sets the user to MAIL FULLHDR NOREPRO NOACK, which are the standard defaults. You might want to change NOACK to ACK (or possibly NOREPRO to REPRO instead), in which case the value in the second position would be 1025 (for ACK) or 1132 (for REPRO).
Note: Simply add the numbers from the table together to combine option settings; if you want the user to be set to the FULLHDR and REPRO options, you set the value to 1024 + 128 = 1152. However, you cannot set it to a value such as 1536 (1024 + 512), which would be FULLHDR DUALHDR -- an impermissible combination as the two values are mutually exclusive.
Finally, if you want the user's subscription date to be included, you find the value per the specification found above and add it in the fifth position. Assuming that today's date is 23 June 1999, the value on that date is 729927, so the setting would now be:
Documented Restriction: If you do not set the subscription date field for a new subscriber, then LISTSERV will probe the address at least once, immediately, regardless of the Renewal= setting for the list and/or regardless of whether NORENEW is set for the address. If you do not want new subscribers to receive an initial subscription probe, be sure to set the subscription date!
When migrating an existing (standard) LISTSERV mailing list to the DBMS type, note that it is possible to preserve existing user options without having to figure out the bit values for every user's existing options by hand.
A standard LISTSERV subscriber entry is a 100-column string, of which the first eighty characters are the subscriber's e-mail address and "real name" field. Columns 81-100 contain an encoded string that holds the various user option settings, for instance:
If you take columns 81-100 from an existing list file, insert a semicolon after the first character, and store this in the option field, all the settings will be successfully migrated. So for instance if you have '3AEAQABMcBMe////' in columns 81-100, you store '3;AEAQABMcBMe////' in the option field. Note that the column value is not automatically converted to '4;' format until the value is updated, i.e., until a change is made to the user options or LISTSERV otherwise updates the value. LISTSERV will then use '4;' format when updating the value, but until then it will remain as it was and LISTSERV will be able to use it without any trouble.
Do not change the value of the first character from 3 to 4 when migrating as this is guaranteed to cause breakage.
The web interface can be used for mail-merge operations where the data source is either a DBMS or a traditional LISTSERV list. That is, if the addresses and names of the subscribers are in, say, a proprietary application that you have purchased or developed, you will need to write a script to extract the information from the application in question and create a DISTRIBUTE job.
You will be prompted to enter an arbitrary SELECT statement to select the recipients who should receive a copy of the message. Every column in the SELECT statement which can be mapped to a character string is then available as a substitution in the message, HTML-style. That is, if you have a column called ACCTNO with the customer's account number, you can substitute it in the text of your message using &ACCTNO; (a NULL value is mapped to the empty string).
Only the LISTSERV administrator and other users allowed to use DISTRIBUTE (as defined by the DIST_ALLOWED_USERS configuration variable) can use the above URL and send arbitrary DBMS-based mail-merge messages. This is because this method allows you to issue arbitrary SELECT statements, which are not limited to the membership of a particular list. List owners can use another URL to send mail-merge jobs to their respective lists:
This interface does not prompt you for a SELECT statement – it implicitly selects all the subscribers matching the MAIL/NOMAIL/DIGEST/INDEX subscription criteria. In addition, you can provide a boolean expression to further restrict the recipient list, such as:
If the target list is a DBMS list, all the columns in the table which can be mapped to a character string are available as substitutions. If the target list is a traditional LISTSERV list, only &EMAIL and &NAME are available, in addition of course to all the special substitutions, such as &*DATE (see below).
The purpose of this section is to describe the mail-merge enhancements to the DISTRIBUTE function, rather than DISTRIBUTE itself. While it will usually not be necessary to learn all the details of the DISTRIBUTE function to prepare mail-merge jobs, please refer to Section 9
Relayed File Distribution & theDISTRIBUTE Command if you do need further information about DISTRIBUTE.
//XYZNEWS-215 JOB ECHO=NODISTRIBUTE MAIL FROM=owner-nolist-xyznews@xyznews.com PW=xxxxx
//TO DD *
joe@xyz.com Joe Doe
hdoe@abc.def.com Helen Doe
...
/*
//DATA DD *
Date: Sat, 4 Jul 1998 22:47:24 +0200
From: XYZnews editor <xyzed@xyznews.com>
Subject: XYZnews issue #215
To: XYZnews recipients
The job must be mailed to LISTSERV, either from the LISTSERV administrator's address (not recommended) or from an address defined in the DIST_ALLOWED_USERS configuration variable. The personal LISTSERV password corresponding to the sending address must be provided, or the job will be rejected. The "ECHO=NO" option suppresses the message that is normally returned to indicate when the job starts and ends, on the assumption that this information is not wanted (if an error occurs, a message is sent anyway). XYZNEWS-215 is an arbitrary job name for problem tracking purposes.
The only differences are the options provided on the DISTRIBUTE command line, and the format of the //TO section. The format of the message section is the same with all three types of mail-merge jobs.
Note: Some DISTRIBUTE command lines can get quite long and may wrap in your mail client, causing an error when the job is processed by LISTSERV. To avoid this you can use one or more "continuation cards" (see chapter 2.3 of this manual) to split the command over multiple physical lines. See for instance the second example below.
//XYZNEWS-215 JOB ECHO=NODISTRIBUTE MAIL-MERGE DBMS=YES FROM=owner-nolist-xyznews@xyznews.com PW=xxxxx
//TO DD *
SELECT EMAILADDR,ACCTNO,NAME FROM ...
WHERE ...
AND ...
...
/*
//DATA DD *
Date: &*DATE;
From: XYZnews editor <xyzed@xyznews.com>
Subject: XYZnews issue #215
To: &*TO;
The DISTRIBUTE command line now reads DISTRIBUTE MAIL-MERGE DBMS=YES, and the recipient section contains one or more SELECT statements. If multiple SELECT statements are included, you must end them with a semicolon, and you may not have more than one statement per line. The &*DATE; and &*TO; substitutions are not required, and simply serve to illustrate the fact that substitutions can now be placed in the message text (including the mail headers).
With the syntax shown above, the first column in the SELECT statement must be the one containing the e-mail address. The other columns are made available as substitutions (&NAME, etc.) This is the recommended syntax when writing a script to prepare the jobs, as only the columns actually used for substitutions are transferred from the DBMS server. Sometimes, however, the script may not know in advance which columns will or will not be used. In this case, a SELECT * may be used, as follows:
//XYZNEWS-215 JOB ECHO=NO// DISTRIBUTE MAIL-MERGE DBMS=YES(EMAIL=EMAILADDR) ,
FROM=owner-nolist-xyznews@xyznews.com PW=xxxxx
//TO DD *
SELECT * FROM ...
WHERE ...
AND ...
...
/*
//DATA DD *
Date: &*DATE;
From: XYZnews editor <xyzed@xyznews.com>
Subject: XYZnews issue #215
To: &*TO;
Note: The use of a "continuation card" in the DISTRIBUTE command line above, used because the line was so long it wrapped. See Section 2.2
Control Cards – General Syntax Rules for more information on JOB card syntax.)
The EMAIL=EMAILADDR option tells LISTSERV the name of the column containing the e-mail addresses. All the other columns are made available for substitutions, provided of course that they can be mapped to a character string. Note, however, that even large columns (LONG et al.) will be transferred from the DBMS server. As a rule, this syntax should be avoided whenever you would normally avoid doing a SELECT * against the table.
//XYZNEWS-215 JOB ECHO=NODISTRIBUTE MAIL-MERGE DBMS=LIST(XYZLIST,MAIL,DIGEST,INDEX) PW=xxxxx
//TO DD "(&age < 15) and (&country = Canada)"
//DATA DD *
Date: &*DATE;
From: XYZnews editor <xyzed@xyznews.com>
Subject: XYZnews issue #215
To: &*TO;
This job selects all the recipients from the XYZLIST list whose subscription options are either MAIL, DIGEST or INDEX and for which the expression (&age < 15) and (&country = Canada) is true. By default, if you specify only DBMS=LIST(XYZLIST), LISTSERV will only select recipients with the MAIL option. The options you can include in this fashion are MAIL, NOMAIL, DIGEST and INDEX. Note that the FROM=owner-nolist-xyznews@xyznews.com option has been removed – bounces are automatically integrated with the XYZLIST bounce stream and do not need to be redirected to a change log. If desired, however, you can override this behavior by providing a FROM= keyword.
In addition, you can specify a boolean expression in the //TO section. This expression is in the same format as the conditional expressions used in LISTSERV's mail template files (described in the list owner's guide). If you do not want to do any further filtering, just set //TO to the empty string, or to the value "1" (true), for instance:
For a DBMS list, this syntax is functionally equivalent to a SELECT * job, that is, every column that can be mapped to a character string is available as a substitution. For a traditional LISTSERV list, only &EMAIL and &NAME are available. This can still have its uses, for instance, to send a mail-merge message to AOL recipients only, you could use:
//XYZNEWS-215 JOB ECHO=NODISTRIBUTE MAIL-MERGE FROM=owner-nolist-xyznews@xyznews.com PW=xxxxx
//TO DD *
*XDFN NAME="Joe Doe" AGE=23 country="Canada"
joe@xyz.com PROBE
*XDFN name="Helen Doe" Age=47 country=USA
hdoe@abc.def.com
...
/*
//DATA DD *,EOF
Date: &*DATE;
From: XYZnews editor <xyzed@xyznews.com>
Subject: XYZnews issue #215
To: &*TO;
With this syntax, all the substitutions are provided in the job, preceding the e-mail address they refer to. There is no DBMS access, no reference to an existing LISTSERV list and no filtering or selection of recipients – you are providing an exact recipient list. Note that when the DISTRIBUTE command's FROM= option is set to an owner-xxx address, LISTSERV generates the mail merge message as a passive probe of the recipient. The probe comes at no extra resource cost when sending a mail-merge message.
You can provide multiple *XDFN lines, which can have any number of keyword=value pairs. There must be no spaces either before or after the equal sign. Keywords are not case sensitive, so the case of the keyword name is not relevant. The value must be enclosed in double quotes if it contains spaces, double quotes or backslash characters. To escape a backslash or double quote in such a quoted string, precede it with a backslash. While LISTSERV will support *XDFN lines of up to 64k, bear in mind that you will probably send the job to LISTSERV via e-mail, in which case a lower limit may apply depending on your mail system. It is good practice to keep *XDFN lines to 998 characters or less, as this is the maximum length guaranteed to be successfully transmitted over the SMTP protocol.
In most cases, you will want LISTSERV to process all the bounces automatically for you. If you are using the DBMS=LIST syntax, bounces are, by default, integrated with the regular bounce stream for the list in question, and you have access to the full range of LISTSERV bounce processing features (see the description of the "Auto-Delete=" keyword in the list owner's guide for more information). Otherwise, L-Soft recommends using the "change log" feature in order to keep track of bounces. This is accomplished by including the following keyword in the DISTRIBUTE command line:
Where logname is an arbitrary name for the "change log" in which LISTSERV will be recording bounce activity, and hostname is the host name of the machine on which LISTSERV is running. You can use a different log file for every message, one file for each set of related messages, or just one for all the mail-merge messages you send; the decision is left up to you. The file will be located in LISTSERV's main directory (the one where, among others, permvars.file is located) and will be called nolist-logname.changelog. You do not need to create a list called NOLIST-logname and, in fact, you must not do that. The change log is a standard text file containing entries of the form:
LISTSERV will process all bounces silently, and store the bouncing addresses in the change log. Note that there may be other entries in the change log – be sure to ignore any lines which do not contain BOUNCE in the second column (in practice, "nolist" change logs only contain BOUNCE entries, but this could change in a future version). Because mail-merge messages automatically use passive probing, bounce processing is extremely accurate, even if the target mail server normally returns bounces in an unintelligible format.
In LISTSERV 1.8e-2002a (AKA LISTSERV 14.1), the BOUNCE record was changed slightly to provide more detail about the bounce. Previously only the bouncing probed address was collected:
It is possible to use the DBMS= keyword to extract and select recipients from either a DBMS or a LISTSERV list, without using the mail-merge functions. Simply use the formats shown above, substituting DISTRIBUTE MAIL for DISTRIBUTE MAIL-MERGE. Naturally, you are then unable to put substitutions in the message text, however the message may use significantly less bandwidth and will usually be delivered faster.
If the substitution is not defined (for instance, using the example &NAME; above), no substitution is performed and the resulting message will contain the literal text '&NAME;'. This can happen (still using &NAME; as an example) if there is no column called NAME in the DBMS, and can also happen in individual merge messages if the column called NAME is null for a given user. (Remember that a null value is not the same as a blank value in most DBMS products.)
|
•
|
&*DATE; is an RFC822 date field (without the "Date:" keyword) suitable for insertion in mail headers. This makes it easier to develop scripts that prepare DISTRIBUTE jobs, and there is no performance penalty as this is a "false substitution" – one that has the same value for all recipients and is pre-processed by LISTSERV. By the time the outbound mailer gets the message, it no longer contains a substitution.
|
|
•
|
&*WA_URL; is the URL of the LISTSERV web interface script, up to and including the script name. You would typically add a question mark and parameter list afterwards. As it is a false substitution, there is no performance penalty for using it. If the web interface has not been installed, it translates to the empty string.
|
|
•
|
&*TO; is the e-mail address of the current recipient, suitable for insertion in a "To:" field.
|
|
•
|
&*INDEX; is a unique, random number ranging from 1 to the total number of recipients. It can be used in conditional blocks (see below) to send a particular message to a random sample of recipients – an invitation to preview a new web site or an ad banner, for instance.
|
|
•
|
&*URLENCODE(); is a function introduced in LISTSERV 1.8e which can be used to assist you in passing URLs that contain spaces or special characters. The function encodes the passed value so that it is valid in a URL (replacing special characters with "%" followed by two hexadecimal digits forming the hexadecimal value of the character). For instance, this works for substitutions such as &*URLENCODE(&ID;); where &ID; represents a field extracted from a database. A specific example would be to encode a URL pointing to a file with spaces in its name, for instance, a file called Year 2000 figures.html. This file name could be converted for you and placed into a URL in your mail-merge message like this:
|
|
•
|
&*TICKET(listname); and &*TICKET_URL(listname,command); are special substitutions used to issue command tickets, which are described below.
|
Documented restriction: While it is possible to tell LISTSERV to issue a command ticket to a list owner address, for security reasons list owners cannot use command tickets for authentication of commands sent for the lists they own. If a list owner attempts to use a ticketed command on a list he owns, LISTSERV will respond
For security reasons, list owners may not issue ticketed commands. The ticket was otherwise valid - this is not an error on your part.
When testing ticketed commands it is thus necessary that the list owner do the testing from an account that is not listed in Owner= for the list in question.
Command tickets allow you to provide safe, authenticated, single-click subscribe, unsubscribe or SET commands in a mail-merge message. They are implemented through a two-step mechanism. In the first step, LISTSERV generates a cryptographically protected ticket through a mail-merge substitution. This ticket allows anyone to execute a limited set of commands on behalf of the user for whom it was issued, and only for the list for which the ticket was issued. Typically, you would use the substitutions to construct a URL, or perhaps a HTML form with a number of buttons triggering various changes in subscription options or status.
In the second step, the user activates the URL or form, which presents the ticket to LISTSERV for verification. If the ticket is valid and has not expired yet, the command is executed. There is no need to use passwords or OK confirmations. Tickets are safe because they are, by design, only ever mailed to the person for whom they are issued. They are also limited to a particular list, and may only be issued at the request of the owner of that list (who does not need to use tickets to affect the user's subscription to the list in question). Finally, tickets expire after a month, to limit the impact of incidents where printouts of e-mail messages were misplaced, etc.
A ticket allows the use of SUBSCRIBE, SIGNOFF and SET commands, with arbitrary parameters. However, the name of the list is extracted from the ticket, and may not be changed. In fact, you do not specify the name of the list in the command. The simplest way to format a ticket URL is as follows:
This substitution expands into a URL which will execute a SET XYZ-L NOMAIL command when activated. To use a ticket in a form with several buttons, you could do as follows:
The list name is optional: it is not required to use the ticket, but can be provided to direct the LISTSERV web interface to use the HTML templates for the list in question. You can customize the response on a per-list basis using the standard web template Customization features, which are described in the list owner's guide.
Unlike OK cookies, command tickets do not use up any disk space or require any kind of house-keeping. You can use them as often as you wish without ending up with gigabytes of pending cookie data on your LISTSERV server, as would be the case with OK cookies and a large daily newsletter offering multiple one-click commands. However, being cryptographically protected, they do require some amount of CPU time for generation, without which they would not provide much protection at all. So, do not worry if LISTSERV does not acknowledge execution of your 500,000 recipient job after 30 seconds as usual.
LISTSERV will generate the tickets only once, even if you refer to the same ticket multiple times in the message. Thus, you do not need to worry about using tickets in a single big form rather than multiple smaller forms, or once at the top and once at the bottom of the message. While it does cost a small amount of resources for the ticket to be merged with the message text in multiple locations, the CPU-intensive ticket generation only occurs once.
If you have a very large list which must be delivered very quickly at a specific time, as can often be the case in the news industry, command tickets may present a challenge. In this case, the best solution is to make use of the SMTP worker pool feature, rather than the "PRIME=" job option, to schedule the delivery of the message. Whereas "PRIME=" will hold the entire job and require you to estimate execution time in order to open the execution window long enough in advance, the SMTP worker pool feature can be used to create a worker pool which does not begin delivery until a specific time. You can then execute the job long in advance, but suspend delivery until the desired time.
In addition to the above, two new built-in mail-merge substitutions and a related (optional) DISTRIBUTE keyword are available. They were been added to solve concerns about "(no name available)" appearing in the To: field, and at the same time the flaw in using:
In the case of DBMS=LIST, the default value of NAMEFIELD=xxx is set automatically from the "DBMS=" keyword and/or the system defaults found in SITE.CFG. Note that the correct syntax is NAMEFIELD=NAME, not NAMEFIELD=&NAME; or similar. NAMEFIELD=xxx is not ignored for a list distribution. Any available column name can be specified for NAMEFIELD, at the risk of making a mistake. The design assumption was that in some cases there might be several name columns in the table, for instance with different character sets or one with and one without accents. It was thought best to allow this to override the internal default, even if the default is correct. However, normally one should omit NAMEFIELD=xxx for a list distribution and LISTSERV will provide the correct value.
|
•
|
&*NAME; is replaced with the variable specified in the (new) DISTRIBUTE option NAMEFIELD=xxx. If unknown, the empty string is substituted as a constant. This is just a convenient way to refer to the name field in examples or generic jobs, regardless of what it is really called.
|
|
•
|
&*TOFIELD; is a correct RFC822 to field (without the "To:") for the supplied name and e-mail address. If the name is unknown or missing, the result is the same as &*TO. A missing name is NULL, the empty string or '(no name available)'. To clarify, the correct use is:
|
Note: There is a performance cost for this option. The RFC822 rules are somewhat time-consuming; additionally, this also requires parsing XDFN lines to extract the name field (when not needed, LISTSERV adds its own XDFN). Finally, the NAME field is passed even if it is only used for &*TOFIELD.
In addition to simple substitutions, you can include "conditional blocks" in your mail-merge messages. A conditional block is a group of lines which is only included if certain conditions are met. Here is an example of a mail-merge message with conditional blocks:
Date: &*DATE;From: birthdays@xyz.com
Subject: Happy birthday, &FNAME;!
To: &*TO;
.* Special offer for people who turn 18.bb &age = 18
Now that you are 18, you can finally do what you have been waiting for all your life - sign up for your very own XYZweb online cash management account! We are waiving the first year's fee if you apply within the next 2 weeks! Apply now at:
.eb.* Special offer for younger children, but not around Christmas though!
.bb (&age <= 10) and (DEC not in &*DATE)
Congratulations! You have won an XYZweb teddy bear! Order it now by going to:
.eb.* Two randomly selected people every day get a free T-shirt
.* Note: &*index is randomized with every run. If we ran the job
.* twice, the prize would go to different people
.bb &*index <= 2
Congratulations! You have won a free XYZweb T-shirt!...
.eb.* Another 10 randomly selected people get a free baseball cap
.bb (&*index > 2) and (&*index <= 12)
.* Make that a free pair of sunglasses in Texas!
.bb (&country = USA) and (&state = TX)
Congratulations! You have won a free pair of XYZweb sunglasses!...
.elseCongratulations! You have won a free XYZweb baseball cap!...
.eb.* Special for AOL users
.bb &*to =* "*@aol.com"
Did you know that you can access XYZweb's store directly from AOL?
Simply do...
A conditional block starts with a .BB (begin block) statement, may include a .ELSE statement, and ends with .EB (end block). The .BB statement contains a conditional expression in the format used in LISTSERV mail templates (described in the list owner's guide). If it evaluates to TRUE, the text is included for the recipient in question, otherwise it is skipped.
Every recipient is assigned a unique, random number ranging from 1 to the total number of recipients. Or, in other words, every number from 1 to the total number of recipients is assigned to a particular, randomly selected recipient. This number is called &*INDEX, and can be used in conditional blocks to select random samples of recipients. Typically, you will use a sequence such as the following:
While traditional LISTSERV lists provide support for up to 23 topics, there are cases where LISTSERV's topic functions are difficult to use. For instance, imagine a web site where people can subscribe to news about movie stars. Presumably, while there would be hundreds of actors to choose from, most people would only be interested in a handful of them. However, it is likely that a small number of people will be interested in dozens of actors!
LISTSERV's built-in topic support cannot be used, because there will be a lot more than 23 topics. The straightforward approach is to create one mailing list for each actor, and this will work well for people who are only interested in a few actors. However, the avid fans who subscribed to dozens of actors may resent the number of individual messages they receive from the service, and sign off. This would be unfortunate, as they probably represent a non-negligible fraction of the purchases of fan-related material.
To determine whether a particular recipient is subscribed to news about this actress, LISTSERV checks whether the comma-separated PARTS field contains the topic Maxine_Bahns. For a mail-merge job using a DBMS back-end, the PARTS field is a DBMS column, whose name is specified either in the web interface form or in the "DBMS=" keyword on the DISTRIBUTE command, as follows:
In practice, you would probably maintain the mapping between subscriber and actors using a separate table and foreign keys, but LISTSERV cannot make use of this kind of data layout efficiently, as it would require issuing one SELECT statement per recipient. However, you could use a trigger procedure to update the comma-separated ACTORS column whenever a change is made to the mapping table.
In a mail-merge job with external data source (that is, a job which includes *XDFN declarations), you include the PARTS field in the job stream, with the following syntax:
In some cases, you may want to send some information to people who subscribe to any of a variety of topics. You can do so using the &*PARTS substitution and the IN operator of the LISTSERV expression evaluator. LISTSERV sets &*PARTS from the PARTS field, but replaces all commas with spaces in order to allow the use of IN/NOT IN. For instance, you could have the following:
You should use the simpler syntax whenever possible, because it allows LISTSERV to bypass the expression evaluator. While very fast, the expression evaluator does add some overhead considering that typical PARTS jobs will have scores of different topics, which must be evaluated for each of the hundreds of thousands of recipients. Thus, the likely order of magnitude of the number of evaluations is in the 1-10 million range.
LISTSERV is now able to interface to LDAP servers to authenticate user logins and to insert LDAP attributes in mail-merge distributions. For full information on LISTSERV/LDAP integration, please see Section 8
LISTSERV and LDAP.
In addition, LISTSERV is now able to execute on-demand Dynamic Queries against either LDAP or traditional DBMS servers, and use the results for access control or mail delivery. For instance, an LDAP query against an Active Directory server could be used to grant list owner privileges to all members of a particular Windows Security Group. A DBMS query could be used to combine employee rosters for two departments and send a single copy of an announcement to each unique employee e-mail address. For more information on Dynamic Query Lists (DQL), please see Section 9
Dynamic Queries.
If MySQL is not already installed on your unix machine, you will need to install it. If you are planning to access MySQL locally for LISTSERV's exclusive use, we recommend that you secure the installation for local access only, with a minimum of root access.
Current versions of MySQL and the accompanying documentation are available from http://www.mysql.com/, or you may have a version of it included in your unix distribution. If you have 'rpm' available, you can issue `rpm -q mysql' to check the install status. Otherwise, use your normal package installer to query its database of installed software.
An ODBC driver is required to connect unixODBC to a database. The appropriate driver for MySQL is called MyODBC. As with MySQL, there is a chance that MyODBC may already be installed on your machine (particularly in Linux distributions), so check for it before installing.
unixODBC can be installed either as a package or from source. Both are available from http://www.unixodbc.org/. The decision of which installation method to use is left to the user. As with the other components, there is a chance that unixODBC may already be installed on your machine (particularly in Linux distributions). If it is, you can skip directly to the next section.
Note: The main www.unixodbc.org page indicates that 2.2.12 was released in October 2006. This version does not appear to be available from SourceForge, but it is available from
www.unixodbc.org -- click "Download" in the sidebar of that page.
[listserv]Driver = /usr/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
Server = localhost
Database = listserv
Trace = off
[home]root:~# odbcinst -junixODBC 2.2.9
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini
If you have not already created a 'listserv' database (sometimes called a "schema" in the MySQL documentation) or a 'listserv' user in your MySQL installation, perform the following commands at the shell prompt (where "privileged_user" is whatever user you have set up as the overall MySQL administrator, and "password" is whatever password you want to assign to the 'listserv' user):
As the final step, make sure that you have linked LISTSERV's executable (lsv) with unixODBC support (see above for details). Then you can proceed to define connection information in LISTSERV's go.user file, for instance:
If the MySQL database you wish to connect to is running on a machine other than the one on which LISTSERV is installed, you will still need to install unixODBC and MyODBC on the LISTSERV machine in order to connect to the database, and then simply configure the system DSN to point to the machine in question. For instance,
[listserv]Driver = /usr/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
Server = mysqlbox.mydomain.com
Database = listserv
Trace = off
