We are looking for beta-test sites interested in the mail-merge and DBMS integration features of the next versions of LISTSERV and LSMTP. This is your chance to affect the outcome of the implementation, etc :-) You can test DBMS integration and mail-merge separately or together (they are designed to work together but this is not a requirement). The mail-merge features require LSMTP and currently the DBMS integration features require that LISTSERV run on NT (the DBMS server can be on a different system). Eventually, we will provide OCI support for selected operating systems. I will briefly describe how these new features work and what you can achieve with them. First, DBMS integration. This is essentially an integrated link between LISTSERV and a database, which can be used to keep mailing list data in the database, or then to create DISTRIBUTE jobs (typically mail-merge jobs) based on data pulled from the database. In the former case, you tell LISTSERV, on a list per list basis, that you want membership data to be kept in the database, either in a private table that contains just the membership information for this one list (LISTSERV will then delete rows when people sign off) or in a shared table where one additional column will be used to store LISTSERV specific information (in which case LISTSERV will not delete the row when people sign off). Several lists can share one table, and that table can in turn be shared with other applications, which can use any number of additional columns for their own purposes. These applications can also update the columns LISTSERV uses for the e-mail address and name, and the change will be reflected in LISTSERV. You can even update the subscription options column, although that requires a bit more work. You can put all or none of your lists in the database, depending on what makes the most sense for you. In addition, you can use the DBMS to run DISTRIBUTE jobs where the usual recipient list is replaced with one or more SELECT statements. You do not need to have a list coupled to the table you are selecting from. The interface was carefully optimized, I do not have performance figures for a realistic DBMS environment yet, but on a 300MHz Pentium II cut-a-corner desktop with a silly IDE drive, I am seeing bulk ADD rates in the 1,100/sec (this is with both LISTSERV and the DBMS running on the PC). My guess is that a more traditional setup (dedicated DBMS server with real I/O system) will deliver around 2,500/sec, I should have figures in a few days. Without using the DBMS, LISTSERV-HPO will do about 11,500/sec on the PC, so it is faster to let LISTSERV manage the lists natively. In fact, most of the sites already in the beta are planning to continue using trigger procedures to synchronize LISTSERV and the DBMS for their traditional broadcast or discussion lists. For lists with mail-merge capabilities, on the other hand, the added value is worth the overhead of the DBMS. The mail-merge features are a series of new DISTRIBUTE options allowing you to send customized messages efficiency to large numbers of users. This can range from simple substitutions to complex jobs with nested conditional blocks. You can either provide all the substitution values in the DISTRIBUTE job, or have LISTSERV pull the data from a DBMS. Here is an example: ------------------------------------------------------------------------- //BIRTHDAY-MERGE JOB Echo=No // DISTRIBUTE MAIL-MERGE [log in to unmask] , // DBMS=YES(E-MAIL=EMAIL,PARTS=TOPICS) //TO DD * SELECT * FROM CUSTOMERS WHERE EMAIL IS NOT NULL AND BIRTHDAY = TO_CHAR(SYSDATE,'MM/DD'); /* //Data DD *,EOF Date: &*DATE; From: [log in to unmask] Subject: Happy birthday, &FNAME;! To: &*TO; Happy birthday! (blah blah blah) To celebrate the occasion, XYZweb is pleased to credit your account with $10.00 in birthday credits. You can spend these credits anywhere in our online store, but there's a catch! They will expire in a week if you do not use them! So wait no further and go to your personalized web page at: &PERS_URL; .* 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: http://... .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: http://... .eb .* Two randomly selected people every day get a free T-shirt .* Note: &*index is randomized with every run. Even 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! ... .else Congratulations! You have won a free XYZweb basecall cap! ... .eb .eb .* Plug our travel partner if user checked TRAVEL category on web .* signup form .bb TRAVEL Are you by any chance making travel plans? If so, our partner, ZYX Travels, have a special offer for you! Simply follow this URL for more information: http://... .eb .* Special for AOL users .bb &*to =* *@aol.com Did you know that you can access XYZweb's store directly from AOL? Simply do... .eb .* That's it! ------------------------------------------------------------------------- When you submit this job (which can have a PRIME keyword to make it hold until a specific time), LISTSERV run the queries, retrieves a list of e-mail addresses and makes the value of the various columns available for use in substitutions or conditional blocks. It also generates a randomized index number, to allow advertisements to large lists to be sold in smaller chunks (or simply to allow new ideas to be tried out on a smaller subset of the user base). It then passes the mail off to LSMTP for fast delivery. This system is designed to scale to large numbers of conditional blocks, such as would be the case with, say, a web site allowing you to subscribe to news about hundreds of movie celebrities. You would then have hundreds of categories, with each subscriber signing up for only a small number of them. When the bounces arrives (they are all in passive probe format), LISTSERV processes them and saves them to a change log file called (in this example) NOLIST-BIRTHDAYS.CHANGELOG. You can then match the entries against your database to delete users whose mail is bouncing, based on your own criteria (you may just want to set the e-mail address to NULL and keep the row). Unlike conventional mail-merge, which is based on some kind of script generating one individual mail message for every recipient (which is obviously very resource intensive), mail-merge DISTRIBUTE jobs only generate one file in the LSMTP spool (or a small number if there are a lot of recipients - typically you want to batch a few thousand deliveries per message). This saves a lot of resources and also a lot of disk space - you no longer have one spool file sitting around forever for every bad recipient in the distribution. LISTSERV pre-processes the high-level tasks (DBMS access, evaluation of expressions, etc) and LSMTP then merges the substitutions on the fly, as the messages are being delivered. I can't give you apple to apple figures as mail-merge is still a low volume activity, so we'll have to do with oranges, knowing that they ARE oranges. We have been providing mail merge services to some of our outsourcing customers for I think about a year now, using an application that we developed for this purpose and which makes one separate mail message per recipient. With LSMTP 1.1a running on a 200MHz Pentium Pro and the application running on a separate machine, we can sustain about 150,000 hourly deliveries with real recipients (we have run jobs in excess of 300,000 so this is not a wild extrapolation :-) ). With local recipients on our LAN, we have peaked at around 175-200,000/hour, ie the overhead of creating all these individual files was the main bottleneck. Nevertheless, as our most demanding client wanted 50,000/hour, this was more than sufficient, and remains an option for jobs which require a very high degree of control over the formatting of the resulting message. With LSMTP 1.1b, the local recipient test runs at about 1,000,000/hour on another 200MHz Pentium Pro (a more recent one with faster memory, we can't run 1.1b on the original machine yet because of change control procedures, also note that there have been a number of general performance improvements in 1.1b that lead to better results across the board, mail-merge or not). Obviously you will never get that much throughput on a PP200 with real traffic, but clearly the mail merge overhead is no longer such a major bottleneck. Another test (oranges to oranges this time) showed that a mail-merge job to local recipients performs at about 80% of the speed of a regular broadcast job. Of course, mail-merge operations require a lot more bandwidth, especially for lengthy newsletters, and they cannot use the DISTRIBUTE backbone. However, if the target site also runs LSMTP 1.1b, the data can be transferred quickly and the merge can be done locally by the target LSMTP, in which case the 1M/hour figure would probably apply since the destinations would be local to the target LSMTP. Well, that is assuming the target MTA can run at that rate of course, which is another story, especially at large ISPs :-) There will be a web interface to create mail-merge jobs eventually, but for now you have to do this manually. If you do not have a DBMS, you can write a program to generate a DISTRIBUTE job with the appropriate keyword/value pairs. We do not sell database software and have no plans to make databases a pre-requisite, however they do make a LOT of sense when used with mail-merge, especially as the database tends to be there already, with the data ready to be used and existing DBMS expertise. There are also a number of special cases where storing list membership into a database makes a lot of sense, for instance to use automatic replication, either for disaster recovery or for the convenience of having quick access to the data at both customer and provider sites. Likewise, if you need to involve a LISTSERV ADD/DELETE operation in a distributed transaction using two-phase commit, a database makes a lot of sense (there are other ways to do it but none as easy or robust as using the database as a back-end). DBMS integration is like WWW integration, you have to use the integrated component when it makes sense, and leave it out when it doesn't. There are many cases where it makes a lot of sense for people to post to a mailing list from a WWW form, and LISTSERV's web interface provides this function, but most people still want to get their mail in their mailbox and hit the reply button so they can compose their message with a real editor, save it in a folder, etc. In both cases the point is the same, the functionality should be there in case it is needed, but it is not meant to replace the list manager and it is not generally better than the list manager. Eric