I recently met with a consultant friend of mine. He told me that he would like to have a new feature in the database server: a blocking select.
This feature would block on a SELECT until a new row is available. The SELECT statement could also have a condition (WHERE clause). If no rows are returned, the SELECT blocks until it could return at least one row. This way, an application could be event-driven without having to constantly check if there was anything new in the database. You can imagine the overhead of checking if there is something new every few seconds. A blocking SELECT could possibly reduce the pooling overhead and be more responsive depending on what the pooling interval is.
IDS does not have a blocking SELECT and as far as I know, nobody else has it either. I can think of two ways to achieve this:
- Use triggers on the table.
This could be an INSERT and an update trigger. The trigger would test the condition and then execute a user-defined routine (UDR) to send a signal to a process that would tell the process to read the table.
This approach could work when the table does not receive too many INSERTs and UPDATEs. The UDR could potentially find information from a file or from a configuration parameter stored in a table. It could also keep that data in memory to avoid getting the parameter for each trigger execution.
- Use the Change Data Capture API (CDC)
CDC provides an SQL interface to the database logs. You can configure your "log snooping" for a specific table. Each time a new log record is created, the function would return. The application then needs to look at the type of record and decide what to do. This approach should be very efficient and add virtually no overhead to the database server.
Another approach would be to use the InfoSphere Change Data Capture product to replicated specific records on a message queue and have the application subscribe to these messages.
Maybe a blocking SELECT would still be a neat feature to have but with all the different ways we can provide event-driven processing, maybe it is not needed after all.