Watch this! Move your DB2 monitoring to the in-memory interface (WLM monitoring)

Since its first days as a database management system, DB2 has been been changed. It has been extended by new features to serve customer requirements and has been adapted to the state of the art in hardware and software technology. One major new feature has been the introduction of the DB2 Workload Management in version 9.5 and related more comprehensive monitoring with finer granularity (in-memory metrics monitoring) in version 9.7. As with many product changes, it takes a while for customers to really put them to use and reap the benefits, especially when the existing functionality still works.

Thus I was happy when I saw a new article on IBM developerWorks describing how to move off the (old) snapshot monitoring interfaces in DB2 and to the in-memory metrics monitoring. What is included in the article is an overview of the advantages of the newer interface. This should get you motivated to read the rest of the article (and then to migrate if not done yet). It contains a side-by-side comparison of old and new interfaces and has many sample SQL queries. The queries demonstrate how to obtain DB2 runtime metrics using the old and new interface for some popular monitoring tasks. You can find the documentation of the SQL interface to the in-memory metrics in the DB2 Knowledge Center in this overview. Most of the pages in the manual have further SQL samples to get you started.

So take a look, it will also help you with one of the upcoming DB2 quizzes on this blog.

Tuning your DB2 CLP environment: Customize appearance and editor

Tuning World Bodensee via Wikipedia

Over the last weekend, the annual Tuning World Bodensee was guest at the Messe Friedrichshafen (exhibition center and fair grounds). More than 100,000 people interested in car tuning traveled to Friedrichshafen. "Tuning" can be trying to get more performance out of engine or to customize the car to the personal style. With DB2, you can customize the command line processor to your personal style and preferences. Let's have a look at the available tuning options.

All the recent versions of DB2 provide three environment variables to tune the editing experience in the interactive DB2 CLP: DB2_CLP_EDITOR, DB2_HIST_SIZE, and DB2_CLPPROMPT. The first variable, DB2_CLP_EDITOR, is used to specify an external editor to be used for editing SQL statements. On my Linux system, I did the following:

export DB2_CLP_EDITOR=gedit

Now you can edit previous statements using the EDIT command. "EDIT 1" would call the editor with the first statement in the command history, "E 1" would do the same. To know which statements are available, use the HISTORY command or its short version "H". The maximum number of available commands is determined by the variable DB2_HIST_SIZE. It accepts numbers from 1 to 500.

export DB2_HIST_SIZE=100

To reduce the number of statements listed with the HISTORY commands, you can limit it: "H 10" would return the last 10 statements in history, "H R 5" would return the last five in reverse order. Instead of the option "R" you could also use the full word "REVERSE", e.g., "H REVERSE" or "HISTORY REVERSE". Editing commands is fun, but actually executing them is probably why they were edited. To execute a specific statement from the history, you can utilize RUNCMD. The short version is just "R" and a valid parameter would be the number corresponding to a "historic" statement.

Both RUNCMD and EDIT, if not invoked with a number, will pick the newest statement in history. Both also accept negative numbers with "-1" being the most recent statement.

What is left is to "decorate" the command line processor in your personal style. DB2_CLPPROMPT is used to modify the command prompt. It accepts different tokens and most characters. Here is my version which prints the current database name followed by "=> ":

export DB2_CLPPROMPT="DB: %d => "

Here is a small sample session with the bew prompt:

 DB:  => connect to hltest

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = HLOESER
 Local database alias   = HLTEST

DB: HLTEST => values 'Good Morning'

1         
------------
Good Morning

  1 record(s) selected.

DB: HLTEST => e -1
DB: HLTEST => values 'That''s it, good bye!'
Do you want to execute the above command ? (y/n) y

1                 
--------------------
That's it, good bye!

  1 record(s) selected.

DB: HLTEST => h r
4    h r
3    values 'That''s it, good bye!'
2    values 'Good Morning'
1    connect to hltest
DB: HLTEST =>

Everything is my IP: Obfuscating a "Good Morning"

Want to impress or scare your co-workers? Or show your boss how to hide Intellectual Property (IP)? Try this with DB2:

db2 => create view hello  WRAPPED SQL10050 ablGWmdiWmtmTmdKTmJqTmdKUmteUmduUnZq1mZK2idaWmdaWmdaWndjHBcaGicaGQIwPJVuycUYnzvpYqeM0J9mNKgGspriVQtaGRWxKEiYWqvXK6WNaJSUbhrqa
DB20000I  The SQL command completed successfully.
db2 => select * from hello

GREET      
------------
Good Morning

  1 record(s) selected.

Even though the functionaliy for it was introduced in DB2 9.7 FP2, probably not many have looked at obfuscation of SQL code, i.e., the body of routines, views, triggers, and packages or modules. Basically, if some business or application logic is inside the database, obfuscation might be an option. This could be either because you want to protect your IP or for security reasons. At the time code obfuscation was released for DB2 in 2010 I wrote that it would be possible to abandon spaghetti code as a way of protection.

It is pretty simple to use obfuscation as everything needed is in the built-in module DBMS_DDL. It has a function WRAP and a procedure CREATE_WRAPPED. When WRAP is called with a valid statement as parameter, it returns a string with the DDL statement to create a wrapped, i.e. obfuscated, version of it. The procedure CREATE_WRAPPED is called with a valid DDL statement, transforms it to the wrapped version and directly executes it. WRAP could be called to produce encoded statements to be embedded in text files. The files would be given to customers. CREATE_WRAPPED could be called directly from an application which creates objects inside a database. Customers would have a hard(er) time to reverse engineer the application logic of functions, procedures, or entire packages created in the database.

So instead of creating the view "hello" as shown above, my application could have issued the following statement:
call dbms_ddl.create_wrapped('create view hello (greet) as select ''Good Morning'' from dual')

And now you know the logic that was hidden inside the strange string of my first SQL statement above ("ablGWmdi..."). Try to memorize that pattern and impress your co-workers or boss...

BTW: If you are a hardcore DB2 user and have never used the DUAL table, read here.

DB2 Information Center: English, deutsch, español, italiano, etc.

Today I discovered an interesting feature of the DB2 Information Center that I will now use often. As all of you know, the DB2 Information Center is available in many languages and the content is displayed according to the preferred language setting of your browser. The DB2 Information Center even has a page on how to set this up. However, when I work with customers or partners in a different country or I don't even use my machine, switching languages requires some clicks.

So I was very positively surprised and happy to find out that I can just specify a "lang=xx" (xx being the country code) parameter to the base URL of the DB2 Information Center. Thus with one of the following URLs you have the Information Center in

• your standard preferred language,
• in English,
• auf Deutsch,
• en Español,
• en français, etc.

Now using a specific language is simpler which allows for faster cross-checking of wording between your language and the English version of the DB2 Information Center.

DB2 9.7 Fixpack 5 is available

The latest fixpack for DB2 9.7, FP5, has just been released. I took a look at the new features described in the Information Center. For the Express-C crowd the most important news is that PL/SQL can now be compiled, something that before was only available in the non-free editions of DB2. For the PL/SQL lovers also some new functions have been added to simplify migrations from Oracle to DB2.

Those working with HADR and looking for increased performance (and there is always a downside to it) can take a look at the super asynchronous sync mode. Another feature that I find interesting is a new procedure DESIGN_ADVISOR that can be used to get design recommendations from the server, so that you can write your own wrapper for the design advisor.

That's it for now, happy Friday and have a great weekend.

Su casa es mi casa: Restore in DB2 and obtaining SECADM and other privileges

One of the changes from DB2 9.5 to DB2 9.7 was the enhanced security, including extended abilities for SECADM and less or changed for SYSADM and DBADM. The idea was to introduce more security and prevent data theft. However, as we have learned during life, all good comes with some drawbacks, and so it is for security as well. It reduces what an administrator can do and cuts down on flexibility (remember how taking a flight was more than a decade ago?).

A common problem is with taking backups of a system and try to use them, e.g., for testing, on a different system. Users like SECADM need to be recreated in order to make things work on the system using the restored database. And that's why a "shortcut" was introduced in DB2 9.7 FP2. If the DB2 registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES is set to ON, then SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user performing the RESTORE DATABASE, typically one of the system administrators. Different methods of restore are supported.

Things for the curious: db2greg

It's Friday and it is a slow day. How about I tell you about a DB2 tool that until 2 weeks ago I had never heard of and never had used before? It is a tool that has been in DB2 for an eternity (I found an entry in the Information Center for version 8). I am talking about db2greg which is used to view and change the DB2 global registry.

hloeser@BR857D67:~/Downloads$ db2greg -dump
V,DB2GPRF,DB2SYSTEM,HENRIK,/opt/ibm/db2/V9.7,
V,DB2GPRF,DB2ADMINSERVER,dasusr1,/opt/ibm/db2/V9.7,
I,DB2,9.7.0.4,hloeser,/home/hloeser/sqllib,,1,0,/opt/ibm/db2/V9.7,,
V,DB2GPRF,DB2INSTDEF,hloeser,/opt/ibm/db2/V9.7,
V,DB2GPRF,DB2FCMCOMM,TCPIP4,/opt/ibm/db2/V9.7,
S,DB2,9.7.0.4,/opt/ibm/db2/V9.7,,,4,0,,1305817517,0
S,DAS,9.7.0.4,/opt/ibm/db2/V9.7/das,lib/libdb2dasgcf.so,,4,, ,,
I,DAS,9.7.0.4,dasusr1,/home/dasusr1/das,,1,0,/opt/ibm/db2/V9.7/das,,

Using the "-dump" option as shown above lists the current entries, here for a DB2 9.7. But why would I have to use db2greg and how did I find out about it? The reason is DB2 9.8 which basically is the pureScale feature that brings application cluster transparency. By lack of coffee and sleep and too much enthusiasm I had pulled too many power cables at the same time on a demo machine (nanoCluster). That resulted in some "extra time" in bringing back the machine to "fully operational". In that process I had to clean up some system entries, e.g., like shown in this example in the Information Center.

You will notice in the example that some information DB2 needs to know about the GPFS and the RSCT clusters are stored in the global registry (PEER_DOMAIN, GPFS_CLUSTER, etc.). If parts of a system are manually (re-)build, the registry may become inconsistent and that's when db2greg options like "-delvarrec" and "-addvarrec" are needed to patch up the registry.

For me the mishap ended up with some extra work, but lots of new things learned. And remember, patch responsibly...

DB2 Merge Backup: When some deltas make a full

I sometimes teach Data Management at university and one topic is backup strategies. We then discuss what is needed for a point-in-time recovery and what can be done to minimize the time needed for the recovery process. Full backups, incremental backups, delta backups, etc. are things to consider. Well, in a production environment having adequate maintenance windows to periodically take full backups, even online backups, could be a problem.

Some days ago DB2 Merge Backup become available. It combines incremental and delta backup to compute a full backup, so that taking such a full backup can be avoided. I just checked the product web page and a trial version is available. System requirements are DB2 LUW 9.5 or DB2 9.7 and it runs on most platforms.

Overview: DB2 Base Tables, Created Temporary Tables and Declared Temporary Tables

One of the new features in DB2 9.7 are "Created Temporary Tables". Previously, users had to distinguish between the base tables (in all different forms and shapes) and declared temporary tables. So what are the commonalities and differences between the three different types of table that are now supported in DB2?

Fortunately, as in most cases, there is the DB2 Information Center. For exactly my question above there is an excellent overview (in table format!) that compares the three table types by feature category. Did you know that all support indexes on them or that you have to specify LOGGING for temporary tables in order to support UNDO operations?

For a reference of the syntax, see here:
CREATE TABLE
CREATE GLOBAL TEMPORARY TABLE
DECLARE GLOBAL TEMPORARY TABLE

How to stretch a pureScale cluster: Configuring geographically dispersed DB2 pureScale clusters

Recently, two very interesting new articles were published on developerWorks. One deals with building geographically dispersed DB2 pureScale clusters, the other has an in-depth look on how to upgrade from DB2 9.7 to the DB2 pureScale feature. In both papers, a detailed description of each step is included.

Geographically dispersed clusters, sometimes referred to as stretch clusters, span multiple locations to be able to continue with processing even when an entire site is down. When all involved sites are up, the processing power of the available sites is used (active/active).

DB2 9.7 FP4 is out: Trigger support has been enhanced and some other goodies

A new fixpack 4 is now available for DB2 9.7. An overview of new features and enhancements is on the Information Center. Many of the enhancements are designed to make migration from other database systems, namely Oracle, simpler, saving even more on migration costs.

What stands out from my perspective, are the enhancements to the trigger functionality. Now you can lump the definition of update, delete, and insert triggers together into a single DDL statement. Speaking of statement, support for statement triggers that fire only once per execution has been added to DB2's PL/SQL functionality, too.

Many of you will like (pun intended!) a new LIKE feature. It is now possible to use a column reference as pattern expression, i.e., to look up the actual pattern in a different table.

Note that the fixpack has already been upload and the Information Center been updated. However, it seems that the fix list overview page and the fixpack summary page still need to be updated.

Some more time, some XML functions

I had written about some details regarding current time and timezone for the regular DB2 and the Oracle mode. Now I had some time to play with built-in XQuery functions.

There are quite many that deal with time, date, timestamps, and timezones. For the XML and XQuery processing an implicit timezone of UTC (Coordinated Universal Time) is assumed. This is something to keep in mind when processing XML data - different semantics again (who said life is easy?). XQuery defines a good number of functions and operators on durations, dates, and times. DB2 supplements that with some more functions to make life simpler (not easy) in DB2. Most of them provide the local value, i.e., the one related to where your database server is located.

xquery current-dateTime()

--------------------------------------

2011-04-08T10:12:48.70082Z

Note the "Z" behind the timestamp, indicating Zulu (UTC) time, not necessarily the one of your location. DB2 adds its own function to deliver that:

xquery db2-fn:current-local-dateTime()

--------------------------------------

2011-04-08T12:13:44.026531

Now the "Z" is missing and we have a timestamp without a timezone. How about some experiments with timezones?

xquery db2-fn:local-timezone()

--------------------------------------

PT2H

xquery db2-fn:current-local-dateTime() - current-dateTime()

--------------------------------------

PT2H

With the first call we can obtain the timezone at our place. In my example it is the Central European Time with Daylight Savings being active. Not surprisingly, subtracting the (global) current time from the current local time, we receive the same difference in hours.

xquery adjust-dateTime-to-timezone(current-dateTime(),  db2-fn:local-timezone())

--------------------------------------

2011-04-08T12:18:09.078883+02:00

XQuery has a function to adjust a timestamp to a specific timezone. In the example above, we use the already known functions as input and the result gives another timestamp. The interesting part about is that now a timezone indicator is returned, "+02:00".

My time is up for today. If you have time, try out the other time-related functions in XQuery...

(Updated) Times are changing: DB2 vs. Oracle mode

If you expected a show off between the two database systems as part of this blog article, you will be disappointed. I only wanted to show you some - on first sight strange - behavior you can run into, based on whether you are using the regular DB2 date and timestamp semantics or the Oracle compatibility mode.

Let's start with a regular DB2 database:

db2 => values current timestamp

1                        
--------------------------
2011-04-07-16.07.55.194069

  1 record(s) selected.

db2 => values current timestamp - current timezone

1                        
--------------------------
2011-04-07-14.08.13.495288

  1 record(s) selected.

Now we switch to a database with the DB2_COMPATIBILITY_VECTOR set to ORA (and date_compat enabled):

db2 => values current timestamp

1                        
--------------------------
2011-04-07-16.09.47.311791

  1 record(s) selected.

db2 => values current timestamp - current timezone

1                        
--------------------------
1956-07-04-16.09.59.043262

  1 record(s) selected.

The year 1956? This looks strange. But when looking into documentation for date values in Oracle mode, we learn that we are operating on timestamp(0) semantics and that adding or subtracting values mean dealing with days. Be aware or you are turning the wheel of time faster than you imagined...

Update: I thought I should point out how I solved the puzzle. The following gives the same result, regardless of what mode you are working in.

db2 => values current timestamp - (current timezone / 10000) hours

1                        
--------------------------

2011-04-07-14.08.13.495288

  1 record(s) selected.

Get the hint: How to enable support for Oracle-style hints in DB2

One of the more frequently asked question of DBAs coming from Oracle to DB2 is about how optimizer hints work in DB2. The standard answer we give is that there are no hints of that kind in DB2 and that a cost-based optimizer is used. Keep your statistics up-to-date (and maybe use automatic runstats), try out different optimization levels, and maybe set some of the documented (and undocumented) registry variables.

Well, I just mentioned undocumented registry variables. When you run "db2set -lr",  you get all supported registry variables listed - about 190 in my installation of DB2 9.7. A list of many of them and some additional links is in the DB2 Information Center. When you go to the overview of performance variables, you will notice the variable DB2_EXTENDED_OPTIMIZATION. Now here comes the trick of enabling Oracle-style hints:

db2set DB2_EXTENDED_OPTIMIZATION=OHINTS

Make sure, it is really set. Calling just "db2set" should show the new value. Then try it out. For my test, I created two tables with about 100,000 rows. Then I executed my query the first without hints to measure the time and to look at the access plan.
Then I tried it with hints:

select /*+ Make this really fast, please */ a.id, a.desc, ...., b.colfoo, b.doc
from hlhints a, mytest b
where a.id=b.nr and b.colfoo LIKE '%!!argh!!%'

Just based on the measured time, I can say that it really works. Try it yourself.

Current DB2 Fixpacks

The year is still young, but I already had a business trip last week. When looking at/after your systems, planning new ones, or just teaching or discussing DB2, it is good to know where we are in current fixpack levels.

The page "DB2 Fix Packs by version for DB2 for Linux, UNIX, and Windows" lists all of them from version 8.2 to 9.8. As today, DB2 9.5 is at FP7 (released December 13th, 2010), DB2 9.7 at FP3a (released October 20th, 2010), and DB2 9.8 (this is the pureScale feature) is at FP3 (released December 17th, 2010). As you can see, two fixpacks for the recent DB2 versions came out just before the holidays.

DB2 9.7 and changes to DBADM authorities

I was traveling the last 2 weeks and met with business partners and customers. One of the topics that was discussed were the security-related changes that happened in DB2 9.7. Some new roles like DATAACCESS and ACCESSCTRL were introduced and authorities for, e.g., DBADM were reduced.

Everybody agreed that a separation of duties means better access control and higher security. However, less authorities for the DBADM can make the life harder or more complex when there are no changes to development and test processes.

What are the changes that you had to make? What are the best practices that were introduced in your company? And last: What don't you like about the security changes?

Performance: Statement concentrator, small knob with an impact

First off, let me start by pointing out that it is best practice to use parameter markers for repeating, similar dynamic SQL queries. Instead of issuing

select fname, lname from person where id=15

and

select fname, lname from person where id=266

one could write

select fname, lname from person where id=?

and provide the 15 or 266 as parameter to the query. The reason is that every SQL statement needs to be compiled before it can be executed. Query compilation takes some time and if you need to repeat it gazillion times, it will add up. If a parameter marker is used, the statement is compiled once and DB2 remembers how to execute the statement (the code produced is called package) in its package cache. Whenever the statement is executed again, the compilation phase can be skipped, the package is taken from the cache, the parameter is replaced with its actual value, and you have the results back.

Now to the statement concentrator, a new feature introduced in DB2 9.7. Sometimes, it is not possible to use parameter markers or the application is third-party code and it cannot be changed. That's when the statement concentrator comes in handy. Once it is enabled for literals, the new logic detects repeating similar statements that don't use parameter markers. It then tries to reuse an already existing package from the package cache to skip compilation and to save time.

The statement concentrator is OFF by default and it can be configured on the server (STMT_CONC in the database configuration) and/or for clients (preferred) using, e.g., CLI/ODBC or JDBC/SQLJ. Remember: If you want to benefit from the statement concentrator, you need to be active and enable it. And yes, parameter markers and the statement concentrator work for XML-related queries, too...

DB2 Advanced Enterprise Server Edition: All you need in one package

On October 5th IBM announced a new edition of DB2 for Linux, UNIX, and Windows: The DB2 Advanced Enterprise Server Edition (AESE). It is based on the Enterprise Server Edition (ESE) and bundles several of the most valuable (in terms of usefulness) features into a single package.

Some of the Optim tools are already included in the AESE and on the engine side data compression (tables, indexes, XML data, temporary data), HADR and Q-Replication for realizing your high availability and disaster recovery solution of choice, label-based access control (LBAC) for greater data security, and data federation for DB2 and Oracle databases are some of the features that are part of the bundle.

And if you wonder: pureXML is always included free of charge, even in the free Express-C edition of DB2.

New fixpack (FP3) for DB2 9.7

DB2 9.7.3 is now available at this link. An overview of the changes can be found in in the Information Center and a more detailed list should be available later on here.

Update on 9/29/2010: Apparently there are some issues with the fixpack image for the server and it has been removed. Some information is in this document.

Update on 10/04/2010: The images are now available.

Catalog view xmlstrings for simpler access to stringID information

The XML support in DB2 is tightly integrated into the database engine and provides fast and sophisticated processing of XML data. In the past I had explained why - for compactness and speed - element and attribute names, namespace information is replaced with so-called stringIDs. The string to stringID mappings are stored in a cached dictionary which is persisted in a system catalog.

That system catalog, SYSIBM.SYSXMLSTRINGS, an internal table, has undergone some changes over the past database versions. In DB2 9.1, pureXML support was restricted to databases using a Unicode codepage. Hence, the string information was stored in clear text in the database codepage. Users could easily access AND display the system information. In DB2 9.5, the pureXML feature could also be used in non-Unicode databases. The VARCHAR-based string column was then changed into a VARCHAR FOR BIT DATA column to store the UTF-8 codes properly. Via a new function XMLBIT2CHAR it was possible to turn the encoded information back into a readable string.

Now, in the current version DB2 9.7, life got much simpler because a catalog view SYSCAT.XMLSTRINGS was introduced. It shows the stringID, the string in the database codepage (by calling the mentioned XMLBIT2CHAR function), and the string as bit data (hex format).