Videos on DB2 BLU Acceleration

I haven't written about DB2 with BLU Acceleration before (but will start shortly).
Susan Visser has compiled a long list of related reading material to get you started how the integration of columnar storage technology deep into DB2 can benefit your enterprise. Let me share some DB2 BLU-related videos that were recently published.

Video 1 on DB2 BLU Acceleration
<iframe width="560" height="315" src="http://www.youtube.com/embed/SH1twB1TChg" frameborder="0" allowfullscreen></iframe>

Video 2 on DB2 BLU Acceleration
<iframe width="560" height="315" src="http://www.youtube.com/embed/n6D5Ns5J16U" frameborder="0" allowfullscreen></iframe>

Video 3: Client Praise on DB2 BLU
<iframe width="560" height="315" src="http://www.youtube.com/embed/5T6f74gYu1Y" frameborder="0" allowfullscreen></iframe>

DB2 Quiz: Which command did I run?

I have DB2 installed on my machine and am connected to a single database. In another window I called a DB2 tool and this is the output:

 Database Member 1001 -- Active -- Up 4 days 21:04:36 -- Date 2013-04-30-11.24.30.393709

Database Member Memory Controller Statistics

Controller Automatic: Y
Memory Limit:         3202592 KB
Current usage:        482624 KB
HWM usage:            482624 KB
Cached memory:        138816 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-HLTEST              40000         40000       38912
DBMS-hloeser            153216        153216       57600
FMP_RESOURCES            22528         22528           0
PRIVATE                   9472          9472           0
DB-HLTEST               257280        257280       42304
LCL-p27623                 128           128           0

What tool did I use and which parameter? Any guesses?

New Best Practices Paper: DB2 V10 Silent Installation and Uninstallation

A new paper has been published on the developerWorks website with DB2 Best Practices: DB2 V10 Silent installation and uninstallation. The paper describes how to use response files to silently install or uninstall DB2 and its fixpacks. The examples are based on the free DB2 Express-C and Windows operating systems, however, the information can be applied to all operating systems and DB2 editions.

Silent installation is important when DB2 is used as embedded database system in solution offered by IBM Business Partners. The background information on reponse files can also be used to enhance automated rollout of DB2 across many systems at customers.

Surrogates, my other self, SET SESSION AUTHORIZATION, and where it is recorded (DB2 Security)

DB2 allows users to switch to different identities. One way is through the means of so-called trusted contexts (which I won't discuss today), the other option is to call SET SESSION AUTHORIZATION (old: SET SESSION_USER). If properly authorized as individual user or member of a group, then a user can switch from the current userid to an authorized other userid and execute SQL statements within that authorization context.

As an example, this feature can be used to work with both personal userids (e.g., for password management purposes) and technical accounts (no login or connection possible, but able to execute SQL and own database objects). In early versions of DB2 this user switching capability was a privilege of DBADM and SYSADM users. However, with database security tightening from release to release already in DB2 9.1 the SETSESSIONUSER privilege was introduced (link to old DB2 9.1 Information Center).

I recently got asked where this privilege is recorded in the DB2 system catalog. Search engines don't seem to easily return an answer for that question. So here we go:

• DB2 has a page "Using the system catalog for security information" in its Information Center.
• On that page the SYSCAT.SURROGATEAUTHIDS catalog view is listed, but not linked to.
• The system catalog SYSCAT.SURROGATEAUTHIDS records information about users and groups who hold the SETSESSIONUSER privilege for userids and also information about trusted contexts.

Let's give it a try:

hloeser@rotach:~$ db2 connect to hltest

   Database Connection Information

 Database server        = DB2/LINUX 10.1.2
 SQL authorization ID   = HLOESER
 Local database alias   = HLTEST

hloeser@rotach:~$ db2 "select * from syscat.surrogateauthids"
GRANTOR                                                                                                                          TRUSTEDID                                                                                                                        TRUSTEDIDTYPE SURROGATEAUTHID                                                                                                                  SURROGATEAUTHIDTYPE AUTHENTICATE CONTEXTROLE                                                                                                                      GRANT_TIME               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------
HLOESER                                                                                                                          SYSATSCONTEXT                                                                                                                    C             PUBLIC                                                                                                                           G                   N            -                                                                                                                                2013-02-01-10.39.34.817754

  1 record(s) selected.


hloeser@rotach:~$ db2 "grant setsessionuser on user db2userl to user db2tech1"
DB20000I  The SQL command completed successfully.
hloeser@rotach:~$ db2 "grant setsessionuser on user db2user2 to group mqm"
DB20000I  The SQL command completed successfully.

hloeser@rotach:~$ db2 "select * from syscat.surrogateauthids"
GRANTOR                                                                                                                          TRUSTEDID                                                                                                                        TRUSTEDIDTYPE SURROGATEAUTHID                                                                                                                  SURROGATEAUTHIDTYPE AUTHENTICATE CONTEXTROLE                                                                                                                      GRANT_TIME               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------
HLOESER                                                                                                                          SYSATSCONTEXT                                                                                                                    C             PUBLIC                                                                                                                           G                   N            -                                                                                                                                2013-02-01-10.39.34.817754
HLOESER                                                                                                                          DB2TECH1                                                                                                                         U             DB2USER1                                                                                                                         U                                -                                                                                                                                2013-04-15-11.25.39.514764
HLOESER                                                                                                                          MQM                                                                                                                              G             DB2USER2                                                                                                                        U                                -                                                                                                                                2013-04-15-11.26.08.813650

  3 record(s) selected.

All information seems to be in.

(Updated) Special hybrid, object-capable, all purpose database

Recently I had to deal again with a special database. Let me share my observations.

This special database seems to have excellent compression capabilities as a ot of stuff can be cramped into it. From the outside the database looks small, but it seems to be able to store 10 times its advertised capacity.

This database can handle relational data, large objects, unstructured data, etc. and has several storage areas and storage types. For the experienced user data access is really fast and it seems index-only. For the infrequent user the only way of data access is through repeated table scans.

The database is equipped to deal with "situations" and act in "emergencies". Its capabilities include having candy up for distribution exactly when needed, a first aid kid with the right number and size of bandaids, chapstick, hand lotion, and some hand sanitizer.When fully loaded, the database can be used as a self-defense device, can be thrown as projectile even on longer distances.

I could go on with more features (you can in the comments), but do you know what database I described above...?

Update: My wife's handbag is one of the described special databases.