Advanced Topics Manual for LISTSERV 17.0

1.5.1 Configuring a List to Use the DBMS

This section assumes that the reader is familiar with the process of creating LISTSERV lists and updating their list header. Refer to the Site Manager’s Operations Manual for more information.

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:

DBMS= Yes[,TABLE(xxx)][,EMAIL(xxx)][,NAME(xxx)][,UEMAIL(xxx)][,OPTIONS(xxx)]

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.

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.

1.5.2 Importing Subscribers into a DBMS List

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.

To activate this option, simply add PRELOAD after the word IMPORT:

ADD XYZ-L DD=NEWSUB IMPORT PRELOAD

//NEWSUB DD *

joe@xyz.com Joe Doe

Helen Doe <hdoe@abc.def.com>

...

/*

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.

1.5.3 Updating DBMS Lists from an External Application

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).

1.5.4 Format of the OPTIONS 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:

4;flags_1;flags_2;reserved;subscription_date;topics;...

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:

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; the value for 14 August 2018 is 736919.

The sixth number is a bitmask indicating to which topics the user is subscribed. 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.

When updating an existing entry, make sure to preserve any information following the topics number (when creating a new entry, do not provide any such information).

1.5.5 Sample OPTIONS Column Settings

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 1.5.6 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

4;0;0;0;0;0

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”.)

If you want the standard default options that LISTSERV normally assumes if you don’t make any special settings in the list header, you would use

4;1024;0;0;0;0

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).

If you want the user to be set to ALL topics (if topics are defined for the list) by default, you put the value 16777215 in the last position, i.e.,

4;1024;0;0;0;16777215

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 14 August 2018, the value on that date is 736919, so the setting would now be:

4;1024;0;0;736919;16777215

Then a QUERY command sent for this user (for example, JOE@XYZ.COM in a list called ODBCTEST) would result in the following output:

>QUERY ODBCTEST FOR JOE@XYZ.COM

Subscription options for Joe Doe <joe@XYZ.COM>, list ODBCTEST:

MAIL                  You are sent individual postings as they are received
FULLHDR        Full (normal) mail headers
NOREPRO             You do not receive a copy of your own postings
NOACK          No acknowledgement of successfully processed postings

Subscription date: 14 Aug 2018

The topics you subscribe to are: All

1.5.6 Preserving Options When Migrating from Non-DBMS to DBMS Lists

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:

3AEAQABMcBMe////

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.