Can you suggest the best solution for the given scenario?

[aditee sharma]

The environment is as follows :
Weblogic 9.2, Oracle 10g
1 Admin server, 3 App servers round robin

Scenario :
ETL/Informatica extract customer data from source system and populate our database.
We need to analyze this data and accordingly send Emails to the customers.
This means checking the "status" column in some table and then invoke the Email module.

Solution1 : Use a scheduler like Quartz that keeps polling the status column in the said table and accordingly invokes the Email module.
Pros: More robust than the other 2 solutions (ok, I feel its more robust).
Cons: The Email sending is not real time.

Solution 2: Let ETL send messages to a JMS queue and a MDB pick up those messages and invoke the Email Module.
Pros: Real-time Email sending.
Cons: Issues like failed messages, then re-trying failed messages by publishing to a failure queue etc. In a distributed system this is even more painful.

Solution 3: Whenever the status column in the said table is populated with the desired value, invoke a DB trigger that in turn invokes a "Java Stored Procedure " that ultimately calls the E-mail module.
Pros: Real Time Email sending.
Cons: I don't know. I am just told by every other person that Java Stored procedures are a nightmare to work with.
Have tried once before, and except for the fact that Oracle was using a old JVM in that particular version of DataBase, there were not many problems.This is 10g , so things should be better...or so I think.

I am inclined for Solution1 but my manager is inclined on Solution2.

Can the experts throw some light please ?

[Jeanne Boyarsky]

Aditee,
With solution 1, how would you know the e-mail was sent? By updating the status column again?

[Kishore Dandu]

Originally posted by Jeanne Boyarsky:
Aditee,
With solution 1, how would you know the e-mail was sent? By updating the status column again?

Same with solutions 2&3 also, they are not any different(except that you can configure jms to do a little of that)

[Paras Jain]

To me solution 2 looks better. I agree with the cons of this solution but since it is ETL's job to update the column it would be better if ETL handles the sending of the message. Unnecessary maintaining a diffent app/solution outside ETL doesn't look good to me

[aditee sharma]

Aditee,
With solution 1, how would you know the e-mail was sent? By updating the status column again?

Yes. Once the email is sent, we'l mark the column again.O
fcourse, we can put in place a locking mechanism so that while one iteration of the scheduler is being executed for a record, another iteration does not pick up the same record.

[aditee sharma]

Still no replies...
Well, I used "scheduler" in my last post but it actually goes for any strategy that we may use.
Kindly try to throw some light on the given solutions or if you have your own, then all the better.

[Rajah Nagur]

One thing; in any of the above solutions how do you guarantee that the email is delivered before updating the column?

You might send out an email, update the column and assume that the transaction is complete. This just ensures that the email is sent to your mail-server outbox and not to the inbox of the customer.
How do you handle the scenarios when the customer mailbox is full / invalid address / expired email account etc?

I could suggest the solution 4:
1. Configure a trigger on the status column. Move your email module logic to a Stored Procedure. The trigger calls the SP.
2. SP does not send an email directly. Instead, it persists this in a Mail Table (which consists of To-ADD, From-ADD, Subject etc)
3. Have a quartz scheduler configured on this Mail table which periodically scans the table and send out an email.

These are some of my thoughts that came to my mind.

I'm not sure how appropriate are these for your problem at hand.

[aditee sharma]

2. SP does not send an email directly. Instead, it persists this in a Mail Table (which consists of To-ADD, From-ADD, Subject etc)

Thanks for the input.

Why should we add this extra step of putting the email addresses from one table to another?
As stated in the scenario, we already have a email table populated by ETL.

If you foresee a problem from both ETL and Quartz Scheduler accessing the same table, then how is the situation different if we add a 2nd table that is actually being populated by a trigger from the 1st table?

[David Sheth]

I think option two would be your best solution. I'd like to specifically address what you listed as the "con" of that approach, and show why that don't apply:

Issues like failed messages, then re-trying failed messages by publishing to a failure queue etc.

First, you need to differentiate between transient failures, when you want to try again, and permanent failures, when you don't. Sometimes you don't know the difference, in which case you want to simply retry all failures a certain number of times before giving up on a particular message. You need to do this sort of analysis if you choose the scheduler approach or the JMS approach.

Using the JMS approach, you don't have to manually record how many times you have tried a specific message. Simply roll back the transaction, which will cause the message to go back onto the JMS Queue, where it will be automatically tried again the next time it is removed from the queue. Although not part of the spec, all JMS servers that I know of allow you to configure for a certain number of retries, after which you can take some other action. Again, that is the same thing that you'd have to do manually if you were to use the scheduler approach, and so that is why I'd recommend JMS for your particular case.

[aditee sharma]

Thanks for spending your effort and time, David.

Using the JMS approach, you don't have to manually record how many times you have tried a specific message. Simply roll back the transaction, which will cause the message to go back onto the JMS Queue, where it will be automatically tried again the next time it is removed from the queue

Agreed that with the scheduler we have to record the no. of times of retry.
However, I was thinking that JMS has this overhead of publishing to the retry queue and handling thereafter.
More importantly, lets say a create message failed and update message comes thereafter, then we have to manually handle this situation(correct me if I am wrong) in JMS.
With a scheduler, we don't need to worry about such situations as there is nothing like a message failure (there's no message to speak of).

Another point : I've seen that JMS is generally used for communicating with external systems.This is our own system and our own database once ETL has done the extraction.So, that ways JMS looks like a odd-man here.
Comments welcome, and I am sure you have many.
Thanks again.

[Thomas Bennett]

Well with PowerCenter you can have a post session task that does this or chain another workflow with a mapping and an Active Java Transformation to handle this for you so you can update the table once you know the email was sent.

Another approach is to use Data Analyzer to handle this for you.

[aditee sharma]

I googled PowerCenter...it looks like a Informatica tool.
We want to stick to Java, because these emails require templating and the Java Email Module uses Velocity to dynamically fill these templates and send out the emails.
In short, lets stick to a Java solution only here.
Can you elaborate on "Active Java Transformation" ?
thanks.

[Thomas Bennett]

Well you mentioned Informatica in your original post and Power Center is the ETL tool of Informatica. An Active Java Transformation is something you can have inside of your mapping so it goes between the Source(s) and Target(s). You can code anything you want in it as a regular Java Application. You will need to make sure any jars are in the CLASSPATH. You can even code it all in a jar and then just have a little snippet call your jar.

Passive/Active Java Transformation?
Passive mean that you will only be producing a single output for your input.
Active means that you can have (N) outputs for 1 input.

So you can send your email as your processing your data and have the real-time update that it was sent successfully. That seemed to be a question or concern you had.

~Informatica Consultant

[aditee sharma]

An Active Java Transformation is something you can have inside of your mapping so it goes between the Source(s) and Target(s).

Which mapping ? Are you talking about the source data and our DB (Target)?ETL is the data provider for our DB but I have no knowledge about how it works (its handled by a seperate team) and to cut a long story short, all emailing is to be handled by Java only.
So, what we are looking for is a pure Java solution.
The question is : what is the best way to trigger this Java email module?

I am sure that being a Informatica consultant you suggested a wonderful solution, but we are not going that way.

[Thomas Bennett]

well what I am saying is that you build it in Java and get the ETL folks to kick it off. This way it is processed as the data is coming in. Your ETL folks can help do this.

Or do you not want to include the ETL folks. One of your concerns was the updating the table if something did not happen. At some point you need to know that the ETL process finished. They can call the ETL tool thru command line and then have that execute your java app.

So a bat script that first calls ETL then after that returns you know the ETL is done so call the java application.

[aditee sharma]

So a bat script that first calls ETL then after that returns you know the ETL is done so call the java application.

Alright, thats more like it. From your input, I've understood the following 2 points:
1) There might be a bat or shell script that is executed periodically for this Source to Target data transfer in ETL.
2) You are suggesting to modify this script so that whenever the ETL job is done, it(the script) calls the Java Email Module through a command line.

Please confirm this.
Actually, this is much simpler, and actually makes the other solutions look dumb.If only I had known earlier how ETL is run

[Thomas Bennett]

That is correct. When you create the script make sure that when you call Informatica that you use the '-wait' option. This will not allow the command to return to the script until the workflow is done running.

[aditee sharma]

Thanks a great deal, Thomas.