Watson, IBM InfoSphere Streams and Oracle Streams

Initially, my plan was to write only on Oracle Streams but after Watson’s victory on Jeopardy, I got an interesting question from an ex-colleague of mine: Is this supercomputer HA? What if the database crashes and there is no source for answers? Funny, isn’t it 🙂 After all Watson is an example of an analytics workload optimized system, not an online sales platform.

But first, have a look at this, how Ken Jennings and Brad Rutter, the show’s two biggest winners, took on their database rival on Feb. 14-16 2011. Note that according to the president of WABC – New York City’s ABC television affiliate Monday’s broadcast of the IBM Watson/Jeopardy Challenge was the most watched show on any channel in the NY Metro area.

Watson is a mixture of 10 racks of IBM POWER 750 servers, Linux OS, 15 TB of RAM, 2880 processor cores and operates at 80 teraflops. The data is stored in a DB2 database. Another one of the 4 major technologies Watson is based on is IBM InfoSphere Streams. While IBM InfoSphere Streams enables continuous and super fast analysis of massive volumes of information, Oracle Streams enables continuous and very fast replication of massive volumes of information. Well, at least when it works and there are no errors. Say one like this:

Looking at the details of the error, what do we understand?

As much as we understand from the statistics:

My personal opinion is that Streams is as good as Advanced Replication was. After all, Oracle will not develop it any longer. Oracle bought Golden Gate and will try to use it for all data integration/replication solutions.

P.S. If only Sherlock Holmes had this type of Watson 🙂

Isolation levels in database transactions in Oracle and DB2

The SQL92 standard defines four levels of transaction isolation with differing degrees of impact on transaction processing. These isolation levels are defined in terms of the following three read types:

Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
Fuzzy reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
Phantom reads: A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

SQL92 defines four levels of isolation based on the read permissions above (nonrepeatable read is same as a fuzzy read):

Now, Oracle offers only the read committed and serializable isolation levels plus a read-only mode that is not part of SQL92. Read committed is the default. Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements. Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

Here is how to set in Oracle the isolation level of a transaction, remember to issue the statement at the very beginning:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

DB2 supports all four isolation levels. The isolation levels are called:

RR: Repeatable read
RS: Read stability
CS: Cursor stability
UR: Uncommitted read

Have a look at this table to see the DB2 UDB equivalent to the ANSI SQL Isolation Level:

In DB2, the isolation level can be specified in several different ways. It depends on if we use static or dynamic SQL. At precompile or bind time we can also use the PREP or BIND APIs to specify the isolation level. On database servers that support REXX: REXX and the command line processor connect to a database using a default isolation level of cursor stability. At the statement level use the WITH clause. From CLI or ODBC at runtime use the CHANGE ISOLATION LEVEL command. For dynamic SQL within the current session use the SET CURRENT ISOLATION statement to set the isolation level for dynamic SQL issued within a session.

DB2/LUW introduces a new isolation level semantic called “CURRENTLY COMMITTED” that allows DB2 locking to generally work as Oracle does and this means that your applications can work with both databases without different logic. There is also a new interface called “CLP Plus” that is useful for running scripts written for another vendor’s interface. It mimics SQL*PLus in Oracle.DB2/LUW introduces a new isolation level semantic called “CURRENTLY COMMITTED” that allows DB2 locking to generally work as Oracle does and this means that your applications can work with both databases without different logic. There is also a new interface called “CLP Plus” that is useful for running scripts written for another vendor’s interface. It mimics SQL*PLus in Oracle.

Finally, here is comparison of the most important database brands:

P.S. To determine the isolation level of a package in DB2, execute the following query: SELECT ISOLATION FROM SYSCAT.PACKAGES WHERE PKGNAME = ‘X’ AND PKGSCHEMA = ‘Y’ .

P.P.S. OLE DB, Teradata, and nonrelational data sources do not have a concept similar to isolation levels.