One of the themes of the seminar I am presenting next Tuesday – during the Red Database Symposium in The Hague – will be ‘the pro active database’. Of course the database can just sit back and relax (and enjoy the flight), responding to queries and stored procedure calls. Passively processing all incoming requests. Ands for that, one of the other themes is: how to provide the optimal interface – or Service Layer or API – for clients, ranging from Java Web Applications, SOA components (BPEL, ESB), external HTTP clients as well as PL/SQL programs. I will discuss Views and Instead Of triggers, Collections, Table Functions and Cursor based APIs – as means for implementing such a service layer.
Pro active goes beyond this servile attitude. 
It discusses ways for the database to not just wait until asked, but act and take initiative. One example is shown here: when events take place in the database that are potentially meaningful for external parties, either applications or humans, event triggers can enlist the help of a Servlet (using UTL_HTTP) to send IM (chat) messages to any interested party.
Other topics include Publishing an RSS feed from the database, Importing and Exporting Excel data using XML DB (save directly from your client into the database) and leveraging SOA Services from SQL and PL/SQL. For example: our BPEL engine runs a useful service that returns – through a complicated negations and internal process logic – the current Oil Price that applies for our business affairs. We will see how easy it is to get to the point where selecting the oil price in a SQL query will leverage the BPEL based service that even calls out to external web services.
With the world around us quickly changing, we need to establish a new balance between the database – both data, data oriented business logic and data based service – and clients – including Java and .NET middle tiers, SOA based services and external clients. What should be implemented in which tier? What interfaces should be defined to make all parties work optimally together? How can we best leverage services, reusable functionality available outside the database from inside it.
If you are interested in discussing these questions, and seeing a lot of demonstrations based on recent business cases, you are very welcome to come and join us. Register for this event through the URL given at the beginning of this article.
Oracle is much more, nowadays, then “a database company” (I still can’t wait to get my hands on BEA WebLogic, one of oracle newly acquired products). Because I am really Oracle XMLDB minded, I am even more into it then Lucas, although I would implement it slightly differently on some points like using the new Native Database Web Services (NDWS). Maybe you are a little bit more into it if you read: http://www.oracle.com/technology/tech/xml/xmldb/oow/2007/database%20native%20web%20services%20with%20oracle%20xml%20db.pdf (slide 22 to be precise). Security should be handled by the Oracle Web Service Manager, OWSM (fkna “Obelix”), on the middle tier. An advantage of using the database as a part in the services architecture, would also be that you could uniform all data transport via XML. Most business environments acquire nowadays a uniform standard network data container, for instance XML (with soap) or Edifact, to be used across all tiers. Using the database as a service end tier would favor this principle.
Marco – about “One of the most important factors, to make the database aware of business constraints / rules, is that with this information is vital for optimal performance.” Absolutely – we are in full agreement about that!!! The post mentions amongst others functionality to call from the database out to services, for instance to sent chat notifications. In my view this is not about business constraints and/or rules – which is solely about data. Obviously one could choose to implement calling out to the chat service in the database – as Oracle cleverly introduces all kinds techniques to allow you to do so (does anybody remember Oracle’s slogan “300% Java”?). For the moment I’ll just neglect the fact that this results in a massive footprint to support the database and about 600MB of software for a database alone. However with a tiered design that allows me to tap into a wealth of API’s and libraries, I could have provided this functionality by allowing each ‘tier’ to do what it does best, with an order of a magnitude less code in a highly flexible manner. Not only less code, but also more adaptable to change, easier to maintain and debug. Letting the middle tier perform the service call or whatever it needs to do to provide the desired functionality also will result in very much improved performance and scalability – as it can use superior memory, process management and clustering techniques. Security would be another argument why to split things like this up – especially when you’re delivering functionality by calling out to services. What happens when the security engineer tells us it is no longer allowed for traffic from DMZ-II (where the database is) to flow to DMZ-I (where the services is)? As you can see, there are a number of arguments to split up things like this and seeing data retrieval and manipulation only as a part of a higher goal. Obviously you don’t have to, and in some cases you won’t – especially when you’re predominantly a (Oracle) database company and/or only use (Oracle) database technology. Hey – in that case you might even consider using APEX as your prime vehicle to support your web front-ends. However in a environment that has (many) more different technologies and products to support and manipulate, things are really different. Which brings me back to my first reaction on “What should be implemented in which tier?†– I got the impression that there is fairly strong bias here to implement as much as possible in the database. This made me react in the way I did, because I sense a great deal of FUD in general from the traditional Oracle community with everything that isn’t solved and/or created from within the database. I think the discussion here has proven that this isn’t how you guys feel about it, which is great! Basically that is what I meant when I wrote ‘open minded’. Oracle’s database is a great product – especially in environments that have high volumes of data and a lot of concurrent users. However I won’t goes as far as saying it is the only thing I need to create solutions that actually provide value to my customer in a well designed, fast and cost effective way – and (luckily) neither do you.
One of the most important factors, to make the database aware of business constraints / rules, is that with this information is vital for optimal performance. This business information should be available to all tiers that need it. If you don’t you will have an sub-optimal environment regarding performance. The problem that will be introduced by having this information on all tiers that need it (so database included), is that you will have also to maintain all constraints consistently over all tiers. If you deprive the database from the needed implied business constraints you will cause to many round trips no matter what and the overall performance will be effected.
Jan, as stated before: I agree that there can be good (or even better) reasons to implement the functionality outside the database. You are right that there is no need for a ‘conditional reflex’ to try and solve every problem in the database. But the same goes for a conditional reflex to only use the database for data storage. I think it’s not about us agreeing or disagreeing. (My opinion on implementing things like this in- or outside the database or wherever depends on the situation and isn’t predefined at all.) The ‘problem’ I have with your posts is that you mainly drop some statements (which may be completely correct and to which I partly subscribe), but with so little argumentation to clearify your opinions. That makes it sound a little biased. I wrote down some arguments why centralizing the mentioned functionality close to the ‘update on emp’, inside the database, may be valuable. The only ‘argument’ you bring in is that using the database is a conditional reflex? I think one can find far more convincing arguments to keep this kind of functionality out of the database. 😉
Eric, there are also good reasons to let the application logic that sits on top of the database (so not in the database) or whatever storage mechanism you want to use, pass on the chat message. And I feel this is the most important thing I’m trying to say here. You have a choice and there is no real need for a ‘conditional reflex’ http://en.wikipedia.org/wiki/Ivan_Pavlov to try and solve every problem there is from within and/or via a thing which prime directive is to store data.
I’m implying nothing at all. Im my previous comment I just argued that there can be very good reasons to centralize the functionality as close to the ‘update on emp’ as possible. Do you disagree on that? So, if that ‘update on emp’ occures in a (Oracle) RDBMS (which of course isn’t always the case, but it is in the context of this blog entry) and the database is capable: Why not use is? I’m sure there can be good reasons for not using this capabilities of the database. But I don’t really get them from your posts. Rather than learning that you are “just a bit more open minded”, I would like to hear some convincing argumentation that supports your statements. (As stated before: At first sight I feel some agreement. However, I cannot rationalise that feeling, due to a lack of argumentation.)
“I’m in no way ruling out the database” … hey, I’m using it for storing data (almost) all the time 😉
Well with this “…to centralize the implementation in the one and only part of the chain that is always involved…” are you implying that everybody out there is using an (Oracle) RDBMS and this is the center of the universe, capable of stuff like UTL_HTTP? How’s that for dogmatism …. I’m in no way ruling out the database, I guess I’m just a bit more *open* minded about it.
Jan, regarding your last remark: It’s very likely that an ‘update of the emp’ can originate from various applications/processes/operations higher up in the ‘food chain’. Reusability, maintainability, univocity, agility, etc. can be good considerations to centralize the implementation in the one and only part of the chain that is always involved (no mather where the update originates from). Without advocating that everything should always be centralized in the database, I can only see dogmatic reasons for ruling out the database in advance in such a situation.
Reading your statement that “HTML should not flow out of the database, so Oracle stuff like APEX is the wrong way to go” or “I have some real reservations as to allowing databases to call out to anything” I basically FEEL some agreement at first. However, asking myself “why?”, I cannot find myself any rational argumentation for my ‘agreement’. Can you?
Aha – so that is what you meant by that image; HTML should *not* flow out of the database. So do you agree with me that Oracle stuff like APEX is the “wrong” way to go. In my view the database shouldn’t do anything other than store data and do so in a quick and robust way. If we can leverage functionality that allows us safeguard this data and make sure it is subject to the *data* constraints than we should make use of those facilities. Also, when there is good reason – say for instance we have to speed up retrieval and storage of data by combining multiple round trips into one – we should also leverage functionality the database has to offer us. Apart from that, I have some real reservations as to allowing databases to call out to anything. The above chat application is a good example – I mean the ‘update of the emp’ is instigated from somewhere higher up the ‘food chain’, why not have that operation/application perform the entry in some messaging solution which in turn will result in the chat message?
Jan,
It would be interesting to have you in the room next Tuesday, for you as well as for me. It seems that you are reading a message between the lines that does not exist. I am very much not advocating putting everything in the database. I make it very clear in the session – as I recently did on this blog (see
) that Application and UI logic should NOT be in the database.
I do believe that data contraints should be implemented close to the data – if for no other reason than that is the only way you can ensure that every data manipulation is subject to those constraints. Other functionality that is data oriented and not specific to any one application or even any one domain can be a candidate for implementation in the database. However, functionality available from the SOA infrastructure and Java middle-tier (including conducting chat communication, sending emails, publishing reports) should not be replicated in the database but instead be leveraged from the database. The utl_http package is an ideal way to find the way to such services. When events transpire at database level – for example the manipulation of a specific record (which can originate from anywhere including a batch job, SOA Service, Java Application, .NET UI etc.) – that is the place where ensuing notification of interested parties external to the database has to start.
Thanks for helping me make clear what exactly I do want to tell the world and what is not supposed to be the message, not even reading between the line. If you have suggestions with regard to specific sentences of turns of phrases that gave you the impression you describe, please feel free to let me know.
Lucas
“What should be implemented in which tier?”
Well from what I’ve been seeing the last couple of months on this blog – this must be rhetorical right? Apparently everything starts with/from an (Oracle) database. IMO this doesn’t help to “change quickly” (enough) – on the contrary. Don’t get me wrong, I’m not favoring an approach where the power of a database is underused, or not used at all. OTOH, I think it is a mistake to put as much of application/processing/ui logic and integration functionality in the database – just because you can do so.