Friday, May 25, 2012

IBM Commercial from the past with a still up-to-date problem

Remember when IBM sold the first Personal Computers? When manuals got printed? And there were IBM printers? And there were real friends (not just on Facebook)...? :)

Enjoy the (long) weekend, maybe an upcoming vacation, or just Spring...

Wednesday, May 23, 2012

Partial Early Desk Cleaning - New in DB2

Remember those days when your parents told you to clean up your room or your desk? There are two strategies to deal with cleaning up a room or a desk. Do a little bit here and there or wait till it is really crowded (and filthy...?). The latter is more of a "last resort" action, e.g., before visitors arrive or someone gets really, really angry. The former (today often called "pro-active") means that some time is taken daily or weekly to clean up parts of the room or the desk. In the best scenario, the desk always shines, there is rarely the need for a bigger clean-up and it gives a good feeling (or it could mean you don't really have a real job...).

Now let's talk about DB2. Starting with version 10 it features PED and PEA. The first is Partial Early Distinct (PED) and means that the big clean-up task of removing duplicates from a result set is not done at the end, but as early as possible ("here and there"). The big advantage is that it will speed up the query because smaller intermediate result sets can be move through the system, less memory is used for the sort heap (needed for eliminating duplicates), and some more. Partial Early Aggregation (PEA) works similarly and applies to GROUP BYs.

Some more of "partial early" is explained in a DB2 V10.1 Query performance enhancements paper published at the DB2 LUW Best Practices website.

More later, I promised my boss to clean up my desk...

Tuesday, May 22, 2012

Travel and Security Concepts

When I am teaching a DB2 class, or data management in general, security is always on the agenda. Authentication and authorization are basic terms, but often they are mixed up. That is the moment when I bring the following analogy.

Authentication is about making sure that I am really the person I say I am. This can be done during travel through a "government-issued identification card" (passport, driver's license, etc.) or when working with a computer through a userid and password (or keycard, token generator, a PIN, or a fingerprint). Often a special token is then issued which can be used as simplified identicator for a limited time. During air travel this is the (in)famous bording pass. On the computer it could be setting a cookie, a session identifier or something else.

Authorization gives me (after I have been identified) access to specific resources, it is the access control part of security. I am allowed only to board a specific flight, I don't have access to the lounge, I am allowed to sit in economy class, but not in business class, the pilot is allowed to use electronic devices during take-off and landing, I don't.

Next on the agenda: Groups and roles in DB2 (groups are controlled outside the database, roles inside).

Tuesday, May 8, 2012

Business Travel and DB2 SQL Compatibilty

As part of my job I travel a lot within Europe. Most countries accept the Euro and participate in the Schengen Agreement (forming the Schengen Area) which means borderless travel and few, if any, controls (more that in a future blog post). What remains are the different languages and the different culture, including food. During one of the recent journeys I came up with a nice comparison of the DB2 SQL (Oracle) Compatibility and understanding the different European languages.

For the performance of PL/SQL and the Oracle SQL dialect in DB2 we often have to point out that statements are compiled to regular DB2 runtime code (so-called sections and packages). At runtime DB2 basically doesn't know whether DB2's SQL or the Oracle-like syntax was used. Both perform equally well as there is only one runtime infrastructure, no emulation. The same is true for SQL vs. XQuery, there is the same single runtime infrastructure.

In school I learned English, Latin, and French, later at university Spanish. I don't speak Latin, French, or Spanish fluently, but I understand enough to get around. The same for Dutch, Danish, etc. What that means is that I don't need a translator, but can directly understand local road signs, hotel employees, newspapers. This gives a "performance advantage", again, as I (my "runtime infrastructure") can process the languages directly, not all ("compatibility rate"), but enough for most common situations. Here DB2 and I differ as DB2 has a higher compatibility rate...