DB2 on Campus

DB2 on Campus Tour - Phillipines

After 5 university visits in Manila, and Cebu, the DB2 on Campus tour in the Phillipines ended today. We reached almost 800 students and teachers in 1 week with very good feedback. At UC (University of Cebu - Main campus) the attendance was close to 300 people (this is the second largest attendance in one single event I've ever had, the first one was in China with 330 people approx). According to Jun Contreras (IBM Phillipines), there would've been even more visits and attendance (maybe 2000 to 3000) should the H1N1 (swine flu) scare had not disrupted classes in some universities. I may be returning in September to deliver a TTT (teach the teachers) 2-day workshop. Next stop: Thailand.

Cheers, Raul.

My prediction

While celebrating the DB2 on Campus 3rd birthday, I remember predicting the future of MySQL in one of my presentations. It all happened during my first DB2 on Campus tour in Brazil; I recall being asked why students should choose DB2 Express-C instead of MySQL. At that time, I replied saying that DB2's performance, scalability and support for stored procedures made DB2 Express-C a superior product. I also mentioned that with DB2 Express-C your career would be 'safe' because IBM invests and will keep investing in DB2 Express-C given that DB2 Express-C is the core of the other DB2 editions, and the fact that DB2 is used as the database for many other IBM products. On the other hand, I said

"...with MySQL being open source, its future is not that certain. So investing your time an effort to become a MySQL expert may not pay off in the long run because maybe a company like Oracle would buy it, and then kill it".

Three years after this "prediction", we find that Sun bought MySQL, and then Oracle bought Sun, which made MySQL an Oracle product. I wish I could predict the lottery numbers like this =).

Moreover, this is an interesting article about how Oracle killed a company it bought (Virtual Iron) just 5 weeks after completing its acquisition.

It's time to try DB2 Express-C! Download the newest version, DB2 Express-C 9.7!

Raul.

DB2 on Campus Birthday: 3 years old!

The DB2 on Campus program started 3 years ago in June/July of 2006. I don't recall the exact date, but since today is a Friday I decided to pick this day to celebrate!

Thanks to the many volunteers and DB2 Student Ambassadors who have kept the program going!. So let's join the 28,000+ students and teachers who have attended the DB2 on Campus presentations in more than 250 universities around the world, and let's join the more than 45,000 people who have downloaded the DB2 Express-C book, and the DB2 on Campus videos to say:

Happy 3rd birthday DB2 on Campus program!!!

Raul.

Enabling applications from Oracle to DB2 the easy way

Register to the DB2 Chat with the Lab to learn how to enable an Oracle application to DB2 9.7 easily. This event will be held on Thursday 16th July 2009 at:
11:30 am Eastern / 10:30 am Central / 8:30 am Pacific / 4:30 pm London / 17:30 Munich time. Register here.

Cheers, Raul.

DB2 9.7 is now available!

DB2 9.7 is now available! To download the latest DB2 Express-C 9.7, click here.

DB2 Express-C 9.7 includes a number of improvements and enhancements, too long to provide a complete list here. Other editions of DB2 include even more improvements. You can review the "What's new overview" section of the DB2 Information Center for details. However, these are the ones I like:

1) Locking enhancements
With the new Currently Committed (CC) semantics of the CS isolation level, writers (i.e: UPDATE) will not block readers (i.e SELECT). Readers will read the currently committed value prior to the update operation. This will improve concurrency and reduce deadlocks.

For example, say you have a table T1 with the following contents:

FIRSTNAME LASTNAME
Raul Chong
Jin Xie

Now say an application AppA issues this statement, but does not commit:
update T1 set lastname = 'Smith' where firstname = 'Raul'

Next, application AppB issues this statement:
select lastname from T1 where firstname = 'Raul' with CS

Prior to DB2 9.7, this last statement would hang because it would wait for the exclusive lock held by the update statement of AppA (the writer) to be released. With DB2 9.7 and currently committed enabled (the default for new databases), the statement would return the currently committed value which was Chong.

2) Performance enhancements
For dynamic SQL, DB2 9.7 introduces the 'statement concentrator' feature, which should improve the performance of programs developed in languages such as Ruby and PHP. As its name suggests, the statement concentrator will look for SQL statements that look exactly the same but where the parameter value is different, and will 'concentrate' those statements into one. For example:

SELECT name FROM employee WHERE empid = 10
SELECT name FROM employee WHERE empid = 20
SELECT name FROM employee WHERE empid = 30

will be concentrated into:
SELECT name FROM employee WHERE empid = ?

The question mark (?) represents a parameter marker. At runtime, DB2 will supply the value of 10, 20 and 30 for this particular example. This will provide a huge performance boost. In prior releases, DB2 would treat each statement as unique, and would compile and execute each statement separately. However, the access plan for all these statement was likely the same, so it was a waste of resources to calculate it over and over again. With statement concentrator DB2 will compile the statement only once to obtain the access plan for the statement, and then it will reuse it.

Other performance improvements is the ability to store small LOBs inlined with the data rows. This improves performance for these LOBs as they can be access through the bufferpool.

3) Application development enhancements
A large number of improvements is now available to application developers:

a)
Triggers and UDFs now have enhanced SQL PL support. Before DB2 9.7 they only supported inline SQL PL which is a small subset of SQL PL.

b)
For SQL PL stored procedures, default parameter values and assignment of parameter values by name have been included. In addition, new Boolean, cursor, row and array data types are now supported.

c)
ROWNUM and ROWID are supported

d)
DB2 9.7 now supports the concept of a module (similar to the concept of 'Package' in Oracle). A module can bundle related database object definitions

e)
Implicit casting between data types is now allowed. This will be useful for dynamic languages like PHP and Ruby. For example, this is now allowed:

create table t1 (col1 int)
select * from t1 where col1 = '42'

In the example, the string '42' can now be compared to the integer column col1.

f)
There are many new functions for string, date manipulation and so on.

g)
DB2 now supports Create Global Temporary Tables (CGTTs). These are temporary tables where the data is only available during the session. The difference between CGTTs and Declared Global Temporary Tables (DGTTs) is that in the case of CGTTs, the table definition is persisted in the DB2 catalog tables. So once a CGTT is created, it can be used by any session without having to create it again. The rows will be independent per session though.

g)
Public aliases (a.k.a Public synonyms) for global object referencing have been added. Prior to DB2 9.7 all DB2 objects had a unique two-part name. The first part was the schema name, and the second part was the object name. By creating public synonyms, you can reference to objects without the schema name regardless of the user ID connected to the database. For example:

connect to sample user arfchong using mypsw
create public synonym raul for table arfchong.staff
select * from raul ## works OK
select * from arfchong.raul ## Error
connect to sample user db2admin using psw
select * from raul ## works OK

h)
DB2 9.7 has relaxed its object dependency rules to allow for more flexible schema evolution. For example say you had this dependency:

View2 --> View1 --> Table1

If you wanted to change View1, you first had to drop View2 because it was dependant on View1. Now this is not required. DB2 will perform the revalidation of View2 automatically for you either immediately or when the view is used again. This can certainly help developers when testing their applications, and when they need to add columns, or make other changes to the database schema.

Other improvements in the schema evolution area are: Columns can be renamed, the REPLACE option has been added to several CREATE statements so there is no need to issue a drop statement therefore all privileges on the object remain. ALTER COLUMN can now use SET DATA TYPE for all types.

i)
The Visual Studio add-ins have been enhanced

j)
There have been JDBC and SQLJ enhancements to support some of the things mentioned earlier

k)
The TRUNCATE SQL statement is now supported for fast deletes

l)
New data types such as NUMBER, VARCHAR2, TIMESTAMP with a precision are now supported

m)
Better support for Python-DB2 applications

n)
pureXML enhancements such a support for Declared Global Temporary Tables is now available

4) Storage enhancements
Every time I teach DB2, I often get the question about "how much can DB2 store?". And almost on every release, I'm surprised about how our developers at the lab keep pushing the limits. For DB2 9.7, the size of large and temporary table spaces limits have been increased fourfold. For example, a 32k page large table space can now store 64TB. Previously it could 'only' store 16TB.

5) Security enhancements
In the past I used to compare SYSADM with 'God' because he could pretty much have access to everything in DB2. With DB2 9.7 the security model has been enhanced to allow for separation of duties. This minimizes the risk of data exposure and helps with government compliance requirements. There are several new authorities including DATAACCESS and ACCESSCTRL. These authorities can be given to users to access data, and grant access to data respectively. What this means is that a security administrator (SECADM) could revoke SYSADM or a DBADM these authorities, and therefore they would not be allowed to access any data!

6) Manageability enhancements
Now you have the ability to move tables online to a different table space. This means that while users are accessing a table, you can change where the table storage resides.

7) Installation enhancements
Now you can use the db2val command to verify if your DB2 installation is OK.

Many of these enhancements are described in the free online book "Getting Started with DB2 Express-C - 3rd Edition" which has been updated to DB2 9.7. The book will be available this June 26th!.

Enjoy the new DB2 9.7!!!

Cheers, Raul.