DB2 Diary from Radhesh

Where a DB Request Spends Time in DB2 9.7?

2010-03-20T11:19:00.012-06:00

This title may sound somewhat familiar if you have been following my Blog for over a year. More than a year ago, I wrote about the shortcomings of time spent monitoring in DB2 (see my post “Where a DB Request Spends Time”). Last year when DB2 9.7 was released (around June 2009), I was thrilled to see “Time-spent monitor elements are more comprehensive” under monitoring enhancements in DB2 9.7. Since then I have been thinking about installing DB2 9.7 on my laptop and having a closer look at this enhancement. Finally, I got chance to do so and here I am going to share my findings.

Without spending too much time trying to come up with a more complex workload, I created a table t3 with similar structure as syscat.columns and populated it with rows from syscat.columns. Then I ran a query against this table as shown below. The real elapsed time of this SQL was more than 38 minutes (long enough for my analysis purpose).

Listing 1

Before I start analyzing the time spent by above SQL, let me list some of the queries that I used for this analysis.

Following query gives the client idle wait time, total request time, total wait time and various processing times for each connection in the database.

Listing 2

If you are interested to drill down the total wait time of each connection, following query can be used.

Listing 3

A connection can have more than one Unit of Work (UOW) and if you are interested to monitor the time spent on UOW level, following query can be used.

Listing 4

Again, if you want to drill down the total wait time of each UOW, following query can be used.

Listing 5

If you are interested to monitor time spent on statement level, you can use mon_get_pkg_cache_stmt table function. Following query gives info on time spent by top 10 dynamic SQL statements (sorted by statement execution time).

Listing 6

If you want to drill down the wait time of dynamic SQL statements returned by above query, following query can be used.

Listing 7

In all the above monitoring queries (Listing 2 through 7), I have intentionally left the WHERE clause as you can use the appropriate WHERE clause as per your specific need. Also, the result set of monitoring table functions used in above queries can be altered through the input parameters passed to those table functions. Check the documentation of these table functions for more detail.

In each of the above monitoring queries, SELECT list has indentation and the indented columns give the individual components of the time spent monitoring element selected just before the indented columns. “Hierarchy of time-spent monitor elements” on DB2 Information Center was very helpful while writing above queries. Also, you might have noticed the most of the wait time components are suffixed with “_wait_time” and similarly most of the processing time components are suffixed with “_proc_time”. Any remaining ambiguities get clarified by looking at the documented hierarchy of time-spent monitor elements.

Though, I found the time-spent monitoring elements available at each level (Connection, UOW, Statement) comprehensive, here I am going to present only the UOW level time spent monitoring for the SQL execution in Listing 1.

Here is the output of db2_mon_uow.sql at the end of SQL execution in Listing 1. To make it more readable, I have presented it vertically.

Listing 8

In the above output and in fact in all the columns selected in Listing 2 through 7, the unit of time is millisecond. As you can see total request time (~2242 seconds) is very close to sum of various time components (~2237 seconds) that constitutes the total request time. Even a minor difference (5 seconds or 0.2 %) can be clearly explained by “Other” category as documented under “Hierarchy of time-spent monitor elements” on DB2 Information Center. Though, the real elapsed time as shown in the output of “time” command (in Listing 1) is higher than the total request time reported by DB2, it can be due to the difference in time recording boundaries used by “time” command versus what’s used by DB2.

Similarly, if you want to drill down the total wait time, here is the output from db2_mon_uow_wait.sql

Listing 9

The difference between total wait time and sum of individual wait time components is just 65 milliseconds.

With this time spent monitoring enhancement, now we can easily find out where a DB request is spending maximum time and accordingly address any problem areas in database if present. Kudos to IBM DB2 team for putting this enhancement in DB2 9.7.

DBA's Strengths

2010-02-02T22:21:00.002-07:00

Did you ever get so engrossed doing something that hours passed by and you did not even realize? It might have happened while watching an interesting movie or playing your favorite video game or similar fun stuff. But did it ever happen while doing your DBA job, the job which pays you salary? I am sure it must have happened unless you are in a completely wrong profession. However, how often does it happen? Do you enjoy doing everything that you have been doing as a DBA? Most likely your answers would be “rarely” and “no”. Imagine how productive you would be if we flip this situation such that most of your job related activities fall under the category which you enjoy doing? Such activities are basically your strengths and you have a natural inclination towards such activities.

Marcus Buckingham, a speaker, trainer, researcher and author has been involved in finding solutions to exactly this type of workplace issues. I got chance to read some of his books (First Break All the Rules, Now Discover Your Strengths, Go Put Your Strengths to Work) and a core finding in his books can be summarized as:

We should try to identify our strengths and focus on our strengths to further grow it rather than spend all our time and effort on our weaknesses. Also, the best managers work hard to understand what their employee’s true talents/strengths are and then shape the job to allow the employee to perform to their maximum. It doesn't pay to focus on people's weaknesses; focus on their strengths.

In his book “Go Put Your Strengths to Work”, he lists following four signs that can help you identify your strengths:

When you do it, you feel effective
Before you do it, you actively look forward to it
While you are doing it, you feel inquisitive and focused
After you’ve done it, you feel fulfilled and authentic

If you are interested to learn more about his works, I would recommend reading his books. I would not be able to create the same impact in this blog even if I try to. However, here I would like to analyze his suggestions in context of DBA job and DBA team.

Traditionally, DBA roles have been classified as Application DBA, System DBA, Development DBA, Production DBA etc. These are very broad classifications and DBAs under each category still end up doing many of the activities which are not their strengths and they don’t enjoy doing it. The challenge is how to setup a DBA team where each DBA enjoys doing most of the activities that he is doing. First I will approach this problem from a DBA Manager’s perspective and then from an individual DBA’s perspective.

First of all, each DBA in the team should try to find out if they even like doing DBA job or they should pursue a different profession. After this high level elimination round, the team should be left of only those DBAs who really like being a DBA. Next we have to focus on how to distribute the responsibilities in such a way that most of the DBAs enjoy doing most of their assigned activities. For that, we have to first list down the activities on a more granular level that can really be associated with the likes (strengths) and dislikes (weaknesses) of a DBA. For example, here I have listed some of the DBA activities. They can be made more granular if required. Many more such activities can be added to the list.

Analyzing new application requirements and associated database changes
Converting a new database logical design to physical design
Creating new database
Storage capacity planning
SQL tuning
Configuration parameter tuning
Creating database standards and naming conventions
Performing database upgrades
Physical design of DDL changes
Implementing DDL changes
Performance diagnosis
Setting up performance monitoring
Troubleshooting database errors
Automating repetitive and mundane tasks
Exploring and implementing DBMS new features
Managing database scheduled jobs
Reacting to database user complains
Performing database security review

After listing the activities on such a granular level, each DBA should try to identify top 4 to 6 activities that really show the signs of being their strengths. The activities which show the signs opposite to that of strengths are the weaknesses and each DBA should also identify top 4 to 6 activities that are their weaknesses. In fact more activities will get added to the list while DBAs will be trying to identify their strengths and weaknesses. Once all the DBAs have identified their strengths and weaknesses, the responsibilities should be assigned in such a way that each DBA should be doing more of their strengths and less of their weaknesses.

A common concern about this approach is, if every DBA will be doing the interesting stuff, who will do the boring stuff. In my experience, interesting and boring words are very subjective. Something that is interesting to me does not have to be interesting for others also and vice versa. One can argue, who will find it interesting to resolve an alert in the middle of night. However, we should try to separate the actual activity with the circumstances under which we are doing it. We have to find out if the DBA hates resolving the alert or just because it’s in the middle of night. I agree there are few mundane things that most DBAs will hate doing. Wherever possible such mundane activities should be automated. This way, analyzing each activity carefully and assigning the responsibilities considering strengths and weaknesses of individual DBAs will result in a team where each DBA will do more of what they enjoy doing and will enhance the team productivity.

In reality, going through this whole exercise will not be as simple as it sounds here. For an individual DBA, just identifying his strengths and weaknesses will require lot of introspection over a period of several weeks. Some caution is required while identifying the strengths and weaknesses. Normally, we attach a hypothetical superiority or inferiority with each activity and we desire to do only the superior activities. We think that we will enjoy doing those activities and they are our strengths. However, our strengths are those which pass the litmus test of above mentioned 4 signs and not the activities which we think superior and desire to do it. Strengths should also not be confused with Skills. There are many activities that we are very skilled at because we have done it several times, however they might not necessarily reflect the 4 signs of strengths. This strength based approach of assigning roles and responsibilities within DBA team will require more introspection from each individual DBA as well as more managerial effort from the manager, however it’s worth doing.

If you are a DBA and your manager has no interest in strength based assignment of roles and responsibilities, you can still apply these techniques at least for you (or maybe along with some of your colleagues). You might be thinking that you do what your manager assigns to you. However, consciously or sub- consciously you also play a major role in deciding what activities you do more and what you do less. After identifying your strengths, you can try to volunteer more in the areas of your strengths and wherever possible reduce your involvement in the areas of your weaknesses.

Multi-terabyte

2010-01-13T23:19:00.002-07:00

After a long gap, I am back again to share some of my thoughts. Leaving my Blog dry for so long makes me feel drained as well. However, I try to console myself thinking now it’s the age of information overload. Also, the growth of community websites in past few years is pouring so much information that it’s difficult for one to cope up with it. Hence, no harm slowing down my contribution to this information overload :)

Even though I was not posting anything on my Blog, I was not completely detached from it. In the meanwhile, I was tackling with the junk comments on my posts :) Yes, you read it correctly... junk comments, not junk emails. Since the email applications already have ways to filter out most of the junk emails, the junkies are targeting blogs now.

One day while looking at my blog, my eyes got stuck at a keyword used in my profile and triggered a chain of thoughts... this word was “multi-terabyte”. I recalled when I had written that profile, I did not mean more than 10 terabytes by it. Just 5 years ago from now, a 10 terabyte data warehouse was considered to be huge and working with such databases used to make me feel privileged. However, now a days I doubt if anyone will consider a smaller than 100 terabyte data warehouse huge. In fact, we have already started talking about petabyte size of data warehouses.

That naturally makes me think, will DB2 be able to support muti-petabyte warehouses in future with its current architecture. If not, what’s there in IBM’s roadmap to support such warehouses? Well, I have not heard much except the empty promises that current scalability features (data partitions, balanced configuration units etc.) should be able to tackle it. Beyond DB2 world, I do see some promising work and an important one to note would be Column-oriented DBMS.

Column-oriented DBMS is not a new concept, though recently it has started getting more traction. If you are hearing about it first time, you can get a quick overview of it on the Wikipedia. Also, you will find tons of articles regarding it if you google it. IBM has been downplaying column-oriented DBMS mentioning that it’s not suitable for all type of workloads in data warehousing environment, the companies who provide Column-oriented DBMS still have a very small footprint in the data warehousing market and so on. These arguments are basically deviating the discussion from the main point.

The important question is, will the majority of workload in data warehousing environment greatly benefit by Column-oriented DBMS approach or not. And the researches on this topic have answered this question affirmatively. Also, I have not come across any clear statement from IBM or IBMers disproving this fact. Well, I am not proposing that IBM should change DB2 from row-oriented to column-oriented or come up with a brand new column-oriented DBMS. However, it can provide an option for column-oriented tables in DB2. May be it will make more sense to have this option on table space level and all the tables under that table space will be stored column-oriented. Please don’t take me wrong, I don’t think this feature is going to be so easy to implement. I can anticipate its far reaching impact on DB2 core engine and optimizer, but so what? When IBM came up with hybrid XML/relational DB2 engine for native support of XML data, it was not a simple feature. If I recall correctly, IBM publicized it to involve 5 years of development time by a large development team.

The next important question is, is it worth investing so much effort in column-oriented table space feature. I think it is... because it will prepare DB2 for the next generation data warehouses. DB2 has been stronger in the data warehousing market compared to OLTP and this feature will help it become even stronger in the data warehousing market. However, I am curious to hear your thoughts on this.

SQL Tips & Techniques- String Splitting

2009-06-20T17:10:00.005-06:00

In my previous post SQL Tips & Techniques- String Aggregation, I discussed how to perform string aggregation. Recently, I received a question to do the reverse using SQL i.e. String Splitting.

Listing 1

For example, we have a table myTab2 with column values as shown above. As you can see, the string value in LIST column is a list of sub-strings separated with “, “. Now, we would like to split the string in LIST column and present the sub-strings in separate rows. Hence, the query should return two rows for CATEGORY = 1, three rows for CATEGORY = 2 and two rows for CATEGORY = 3.

Here is the query that performs the required string splitting.

Listing 2

The above query uses recursive SQL and it will return the following.

Listing 3

I learned the above use of recursive SQL from Knut Stolze’s developerWorks article Parsing Strings in SQL. If you know a different way to do the string splitting using SQL, please share it.

DB2 Authentication & Password Encryption Method

2009-04-11T20:01:00.007-06:00

Few days ago, I received an e-mail from my colleague with following error message

I verified the password of db2inst1 on OS level and the password “db2test” was correct. I verified the authentication related configuration parameters and everything looked okay. The authentication type was set to SERVER. The connection was working fine if we only run “db2 connect to test” and do not specify user and using keywords. The version of DB2 was 8.1 and the OS was SUSE Linux Enterprise Server 10.

You might be thinking how come I am still dealing with DB2 version 8.1. Well, as I have mentioned in my earlier posts, the company I work for (Stratavia Corporation) has software product for data center automation called Data Palette. Data Palette still supports DB2 V8.1 and hence we have to test it on DB2 V8.1 also. Going back to the original problem, I approached our system administrator to figure out what is so unique about db2inst1 password that is causing the above authentication error. We found that the password encryption method used for db2inst1 was different than the other V8.1 instances where we don’t have the above mentioned authentication problem. On this problematic system, the password encryption method was “blowfish”. We changed it to “md5” as shown in the portion of “/etc/default/passwd” below. On SUSE Linux, “/etc/default/passwd” has the configuration parameter to control the password encryption method.

After changing the password encryption method, we set the password of db2inst1 and it was encrypted using md5 this time. On Linux systems, you can check the encrypted password in /etc/shadow file (you have to be root). The length of blowfish encrypted password is almost double than md5 encrypted password. Also, I think md5 encrypted password starts with $1 and blowfish encrypted password starts with $2.

After the above change, the authentication problem was gone. So it appears DB2 V8.1 (Fix pack 0) does not support authentication with password that is encrypted using blowfish encryption. Out of curiosity, I upgraded the same instance to V8.2 to test if blowfish password encryption will work or not and with V8.2 blowfish encryption worked fine. Hence, we should keep in mind that DB2 authentication (while using OS authentication) has dependency on password encryption method used by OS. The user password may work fine on the OS level, but DB2 authentication may still fail if DB2 does not support the password encryption method used by OS.

DB2 and EC2

2009-02-16T07:54:00.003-07:00

Though irrelevant, there is a similarity of alphabetical pattern between DB2 and EC2. E comes just after D and C comes just after B in the alphabet. May be this similarity aroused my interest in EC2... J Also, IBM and Amazon have partnered together to deliver DB2 in Amazon EC2 (Elastic Compute Cloud) environment.

Cloud computing, the biggest buzz word in current date, is no more perceived as mere hype. It has already started helping small startup companies. There are new businesses emerging on top of cloud computing infrastructure. Gradually more and more companies will start embracing the cloud computing.

Though I have been hearing about cloud computing for over a year, I never bothered to think about how similar or different it would be managing DB2 databases hosted in cloud computing environment. When I read the IBM’s announcement (actually followed the link from Anant Jhingran’s blog post), it crossed my mind that very soon we DBA’s may have to deal with the DB2 instances hosted in the cloud computing environment. Suddenly, several questions started arising in my mind. Will it be accessible through web browser only or we can still use our favorite SSH client or Remote Desktop (in case of Windows)? How will be the database physical design? Can we also access these virtual servers through VPN and so on...?

Out of curiosity, I thought of signing up for the Amazon’s service and get a feel of EC2. Looking at their pricing, it did not appear to cost much just to get a feel of it. On a second thought, I decided to go through their documentation first. The documentation is concise and to the point. It helped to clear the clouds over my understanding of Elastic Compute Cloud.

Here is a very high level summary of what I learned about EC2 from their documentation.

The virtual servers in EC2 environment are basically the running instances of AMI (Amazon Machine Image). AMI contains all the software, including OS and associated configuration settings, applications, libraries etc.
There are already many existing AMI (free as well as paid). Also, new AMI can be created from scratch or based on an existing AMI. However, new AMI has to be saved on Amazon S3 (Simple Storage Service) and there is a charge for this storage.
There are different types of instances based on CPU, Memory, Storage and I/O requirements and accordingly Amazon has different pricing for each type of instance.
AMI instances are not persistent. You lose all the changes when you shutdown your OS or terminate the AMI instance.
For all the online data that requires persistence (for example, a DB2 database), they have a different solution. It’s called Elastic Block Store (EBS). EBS is storage volume that can be attached to the host (running instances of AMI) and it can either be used as raw device or a file system can be created on it.
Snapshots of EBS can be created on the Amazon S3 (Simple Storage Service). EBS snapshot can be used to launch new EBS volumes using the previous snapshot as the starting point of those new volumes.
Static IP addresses can also be assigned to instances. They are called Elastic IP address.
We can access the servers hosted in EC2 environment using SSH client or Remote Desktop.

I like the ease of provisioning, server downsizing and upsizing available in EC2 environment. Also, it appears to me that set up and maintenance of disaster recovery and business continuity solutions will be relatively easier in EC2 environment. Testing the additional computing power (capacity planning) for increased workload will be much easier and cost effective. We don’t have to make permanent investment in computing power and then later realize that the investment did not solve the capacity problem.

Having said all the good things about EC2, I do agree that we will come across several unpleasant aspects of it when we start using it. I found some of the experiences discussed on Colin Percival’s blog.

In general, one of the big concerns about cloud computing is company policies and security issues related with storing company’s data outside company network. Though EC2 appears to have reasonable security provision, I am not an expert on network security and hence I will abstain from making any comment on security. However, if cloud computing provides or will provide robust security, I would anticipate revisions in company policies over a period of time to adapt to the new computing environment.

Where a DB Request Spends Time?

2009-01-13T20:35:00.009-07:00

While analyzing slow response or poor database performance, you might be curious to know where a database request spends time. This information will feed to database performance intelligence and guide the tuning effort in the right direction. However, I doubt if anyone ever found a satisfactory answer to this question for a DB2 database.

Basically, a database request may spend time either waiting for a service (wait time) or being serviced (service time). In fact this applies to any request to a computer system. The total time spent by a database request should be sum total of all the wait times and the service times for that request. Also, a database request may spend time inside the database system as well as outside the database system (e.g. Network). For a database request, the monitoring features of a database system should ideally provide all these wait times and service times spent inside database system.

In this post I will investigate the monitoring info available through db2batch and snapshot monitoring in attempt to identify the individual components that make up the total time spent by a SQL statement. First, I will investigate the monitoring info captured by db2batch. Listing 1 shows the SQL script I used for db2batch test

Listing 1

In case you are curious about the definition of table t1, it has the same structure as syscat.columns and I inserted all the rows from syscat.columns four times into t1. Also, t1 is created under a DMS tablespace with the associated buffer pool of just 1000 4K pages.

PERF_DETAIL 3 instructs db2batch to capture summary of monitoring information. Listing 2 shows the db2batch output after filtering out all the lines not related with time from the monitoring info

Listing 2

The SQL statement took total 130.567 seconds. Out of which Agent CPU Time was 124.36 seconds, but what about the remaining time (6.207 seconds). Even if we assume that buffer pool read time is not inclusive in Agent CPU Time, still it does not sum up to total elapsed time.

Next I will investigate the DB2 snapshot monitoring and identify any time related stats. Ideally, a database request should correspond to a statement. However, application level snapshot provides more time related stats, hence I will present a test example using application snapshot.

I created a test script as shown in Listing 3:

Listing 3

I ran the script with the time command as shown below

Listing 4

Here is the content of test.out

Listing 5

Listing 6 shows the time related lines from the application snapshot

Listing 6

Now let’s try to analyze the output of this test. The script test.sh took 2 minutes and 11.12 seconds (131.12 seconds). The query was run locally on the database server to avoid any network time. Also, the query returns just a single row to minimize the time associated with sending the result set to standard output. That’s the reason “Elapsed time of last completed uow” from application snapshot is pretty close to the time captured by the time command. Even the difference of fraction of a second is mostly because of other statements in test.sh (connect, get snapshot, terminate etc.).

Since the statement was run in auto-commit mode, the UOW comprises of single statement. Thus the SELECT statement took 130.9 seconds. Now let’s try to break up the 130.9 seconds and identify the individual components. We see that User CPU Time by agents is 124.78 seconds. Apart from that, the following time components sum up to less than a second

Listing 7

Adding them all to User CPU time will still be less than 126 seconds. So where was the remaining 5 seconds spent? In fact, many of the time components shown in Listing 7 (sort time, buffer pool read time etc.) might be inclusive in User CPU Time. In any case, we don’t have any accountability of at least 5 seconds. This gap of unaccountable time will be even longer for more complex queries. Also, note that all the time components shown in Listing 7 are not even available at the statement level where it makes more sense.

With these simple examples, I just wanted to show that DB2 monitoring does not provide the complete picture of all the time spent inside the database system. I have reached to the same conclusion even with the event monitoring. Ideally, the database monitoring should provide the total time spent by a statement and its individual components in a parent-child format. I would love to hear from you if you have any more insight or info regarding the time spent by a database request inside DB2.

SQL Tips & Techniques- Correlated Reference

2008-11-30T01:00:00.009-07:00

Sometimes even a simple SQL query may become challenging if we don’t approach it with a right technique. Few months ago I came across one such SQL query problem that I am going to share in this post. I was writing a database monitoring routine using snapshot table function. For simplicity, let’s consider the following query:

Listing 1

The above query will give the log used and available on partition 0, because the second argument is 0 in the snapshot_database table function. I had to collect the above info on each partition and using db2_all was not an option because the SQL was supposed to run through a JDBC connection. My first instinct was to find the list of partitions using following query and then run the query in Listing 1 in a loop for each partition

Listing 2

However, I wanted to have a single SQL query to get the complete result as shown below:

Listing 3

The problem appeared to be easy in the beginning, but it started becoming difficult as I thought over it and could not come up with the SQL query. Then I thought of checking the complete syntax of DB2 SQL query in DB2 SQL Reference to make sure I am not missing any syntax that can help with this problem. And I was indeed missing something. It was Correlated Reference, which can also be used in arguments of table function.

Most likely you might already be familiar with the Correlated Subquery and Correlation Name. Even if these terms do not sound familiar, I am sure you must have used them in your SQL. Let’s look at an example to make it clear

Listing 4

In the above query, notice the use of x.workdept inside the subquery. The employee table in the FROM clause of parent query has correlation name x. Though x is not present in the FROM clause of subquery, still x.workdept is used in the WHERE clause of subquery. This is called correlated subquery. In the above example, both the parent and subquery has employee table. However, subquery may have completely different tables and still it can use the columns from the parent table. Correlated subquery can also appear in SELECT and HAVING clause as shown below

Listing 5

Correlated reference can also be used in nested table expression as shown below

Listing 6

Above example has a nested table expression empinfo. Notice the use of d.deptno in the query of nested table expression empinfo.

As I mentioned earlier, correlated reference can also be used in the arguments of a table function and this helped me to get the solution of my SQL problem. The following SQL will give the result I wanted

Listing 7

In the above query, x.partition_number is used in the argument of snapshot_database table function. Changing the order of db_partitions and snapshot_database table functions in the FROM clause of above query will result in error because table references in a FROM clause are processed from left to right and x.partition_number must be resolved before it’s used in the argument of snapshot_database table function. The same rule applies with correlated subquery as well. The correlated reference must be from a table reference at a higher level in the hierarchy of subqueries.

Don’t Ignore Naming Convention

2008-09-30T23:31:00.003-06:00

Recently I worked with an organization having hundreds of DB2 databases and still a uniform and consistent naming convention across all these databases. Naming convention started from Server Name, Instance Name and Database Name itself. Just by looking at an Instance Name, you can tell whether it’s a production, development or QA instance and the associated application for that instance. Instance home directory, database path, log path, archive log path, database backup path ..., all followed a consistent naming convention.

In the above mentioned environment, if a file system is getting full on any of the DB2 database server, you immediately know if that file system is database related and which instance/database is using that file system. If a new DBA joins the team, he can quickly get familiar with the large environment just by learning the conventions. If a database has to be backed up, he knows the backup target and if a database has to be recovered, he knows where to find the backup image and archive logs, all because of consistent naming convention.

A good naming convention not only helps a new DBA quickly come up to speed, but it also simplifies automation of DBA tasks. Many of the decision points in automation can get benefitted by the established convention. Automation platform like Data Palette has the capability to capture all the variations and weirdness in your database environment and still enable the decision making. However, more variations often lead to complexity in implementing and managing the automation.

Just having any naming convention is not enough, you should have a good naming convention. I have described below some of the qualities of a good naming convention

1. Make your naming convention informative

You can name your instances like db2ins01, db2ins02 and so on. But such naming convention does not have much information except that it is a DB2 instance. Instead if your instance name can tell you whether it’s production, development or QA instance, then you get more information out of that naming convention. Also, if your instance name includes INST or your database name includes DB, it does not provide you any additional info if you already know that you are looking at an instance name or database name.

Similarly the directory structure of your instance, database and table space level paths should incorporate the corresponding instance name, database partition number, database name hierarchy. By looking at instance/database configuration and system catalog, you can figure out most of the associated paths. However, the reverse mapping i.e. you know the path and trying to figure out who is using that path is straightforward only if you have an appropriate naming convention.

Whenever a name is derived from some other base names, do not abbreviate or shorten the base names while using into derived names. For example, if your table space container path includes a directory with your database name, this directory name should have a complete database name and not abbreviated or short form. If you use the abbreviated or short name, your path name becomes less informative because now you need additional data (the mapping between full name and short name) to determine if that directory name corresponds to the database name.

2. Use fixed-width numbers in your naming convention

If your naming convention includes a sequence number, have a fixed width sequence number. For example, if you know that you will need max four digits for a sequence number in your naming convention, use 0001 instead of just 1. Also, it’s better to have your database partition numbers (in db2nodes.cfg) as fixed width. For example, if you have 16 partitions in your database from 0 to 15, have your partitions named as 00, 01, 02 and so on. Such partition numbers has a distinct advantage while using “##” in your db2_all command. The character sequence “##” (two hash characters) can be used to get replaced by partition number while running a command through db2_all. So if you use NODE00## in your db2_all command, it can get replaced as NODE0000, NODE0001...NODE0015 in the above example of a 16 partition database.

No matter how good a naming convention is, if it’s not followed consistently, it’s not useful. I started this post with an ideal example of consistency in naming convention. On the other extreme, I have also come across organizations where DBA’s come up with a new naming convention whenever they set up a new database. To make it even worse, within a database also the naming convention would start changing over a period of time...it all depends on the mood of DBA while coming up with the name.

In my opinion, naming conventions should be properly defined as a planned effort, it should be well documented and strictly enforced by change control board or a similar regulation authority. Though, I fully agree that there are several scenarios where we can’t expect to have a uniform naming convention across a larger organization. For example, let’s imagine a company being acquired by another company and as a result the IT department of these companies getting merged into a single organization. However, naming conventions should not get destroyed just because of ignorance or negligence of individuals in an organization.

XML Superstar Contest

2008-09-30T22:04:00.006-06:00

Did you hear about “Search for the XML Superstar” contest? If not yet, check out the details of this contest below (as provided by IBM). If you or your friend or colleague is a XML guru, here is a chance to win great prizes.

The International DB2 User Group (IDUG) is proud to announce the launch of the “Search for the XML Superstar” contest in the United States. This worldwide initiative is aimed at highlighting the benefits of DB2 and related technologies to database developers and university students.

The “Search for the XML Superstar” has five categories:
XML application programming
Ported applications
Database gadgets
Videos
Query challenge
Contestants will participate in a combination of education, exposure to practical applications and “tests” that will validate understanding and ability to program IBM database technologies. Entries will be judged by an independent panel of DB2 professionals.

Participants in the contest will be eligible to win one of a series of great prizes. Those who submit star-quality entries could receive an all-expense paid trip to an IDUG conference, a new laptop, an iPod, a Sony PlayStation 3 or a Nintendo Wii system. The contest will also allow students to win opportunities for interviews and lab visits, and will enhance the skills that catch employers’ eyes on a resume. Developers will be able to gain recognition from world-class consultants and be celebrated for standing out among their peers.

The contest launches in U.S. tomorrow. There will be a IBM press release coming out tomorrow, and the website (xmlchallenge.com), will launch then, as well. Also, check out the IBM DB2 Query Console (beta), an application hosted in the cloud that allows you to run SQL and XQuery statements against a DB2 database. Good luck for the contest!

SSV Backup

2008-08-20T01:11:00.002-06:00

As I mentioned in my previous post DB2 Partitioning, Database partitioning feature (DPF) of DB2 enables DB2 to provide a very scalable database solution. The applications connecting to database do not have to be aware of database partitioning. However, the DBAs managing the multi-partition database have to deal with some complexities. One of the administration complexities of multi-partition database is Backup and Restore. Each database partition has to be backed up separately. Also, the catalog partition has to be backed up before any other partition. The db2_all command is used in such scenarios to run a command against one or more than one database partitions as shown in the example below. Following example shows the offline backup of a multi-partition database “test” with catalog partition 0.

db2_all ‘<<+0< db2 BACKUP DATABASE test to /backup_path’

db2_all ‘|<<-0< db2 BACKUP DATABASE test to /backup_path’

The first line in the above example will backup the partition 0 and the second line will backup the remaining partitions in parallel. With the above approach, the backup timestamp of all the partitions would not be the same. Hence, we have to separately identify the backup images for each partition while restoring the database.

In DB2 9.5, you don’t have to deal with the above mentioned complexity because of Single system view (SSV) backup. SSV backup allows to backup some or all the database partitions using ON DBPARTITIONNUMS or ON ALL DBPARTITIONNUMS clause of BACKUP DATABASE command. The backup images created by SSV backup have the same timestamp for each database partition. The SSV backup has to be run from catalog partition. In the following example, all the database partitions are backed up using SSV backup. The backup image of all the database partitions has the same timestamp. Hence, all the database partitions could be restored with a single command using db2_all.

db2 BACKUP DB test ON ALL DBPARTITIONNUMS ONLINE INCLUDE LOGS

db2_all “db2 RESTORE DB test TAKEN AT 2008-08-17-23.44.56.125237“

db2 ROLLFORWARD DB test TO END OF BACKUP ON ALL DBPARTITIONNUMS

Some more good news... In the above example, notice that the backup image would include the logs. Prior to version 9.5, logs can be included with the online backup image for single partition databases only. With SSV backup, it’s possible to include the logs with online backup image for multi-partition databases also. Finally, notice the use of TO END OF BACKUP clause with the ROLLFORWARD command. It really simplifies to rollforward all the database partitions to the minimum recovery time and saves the effort to determine minimum recovery time over all the database partitions.

New Poll- Backup Target

2008-08-20T01:02:00.003-06:00

Thanks everyone for participating in my previous poll regarding DB2 autonomic features. Here is the final poll result

Self-tuning memory manager – 14 (51%)

Automatic storage – 11 (40%)

Automatic maintenance – 8 (29%)

Configuration advisor – 7 (25%)

Health monitor – 9 (33%)

Other – 1 (3%)

None – 6 (22%)

Interestingly, the use of the above features is almost in the same order as their positions in the listing. It’s evident that the top two autonomic features, self-tuning memory manager and automatic storage are the most popular autonomic features as per the poll result.

I have added a new poll ... No, this is not about Democratic vs. Republican J This is related to your database backup target. Please indicate the backup target as you specify in your backup command. For example, if your backup target is TSM, choose TSM only even though TSM will store the backup image on tape. If you select “Other”, it would be nice if you can explicitly mention the exact target through a comment to this post. I hope there will be more participation this time and thus the poll result will be more realistic. So, please don’t forget to provide your input in this poll !!!

A Peek on DB2 Log Record

2008-07-08T01:07:00.003-06:00

Every DB2 database has recovery log. Recovery log keeps track of all the changes made to a database and it is used for crash recovery or rollforward recovery. The recovery log is made up of a number of log extents, each contained in a separate file called a log file. Normally, a DBA will have to deal with only the sizing, archive method and archive destination of recovery logs. However, in some scenarios (e.g. SQL replication, HADR) you may come across a term called Log Sequence Number (LSN). In order to understand the LSN, we have to go one step further i.e. inside the log file. Here I will explain what is LSN and how can we map a LSN with a log file and vice versa.

A log file has number of log records inside it. A log sequence number (LSN) uniquely identifies a log record inside a log file. The LSN represents a relative byte address for the first byte of the log record within the recovery log. A transaction can generate more than one log records. Each log record also has a transaction identifier. Hence, the log records written by a single transaction have a common transaction identifier, a field in log record header.

If you have a LSN and you need to find out the log file that has the log record for that LSN, you may use db2flsn command.

db2flsn –q <input_LSN>

If you need to do the reverse i.e. find out the range of LSN and timestamp inside a log file, it becomes little tricky. There is a file DB2TSCHNG.HIS under database path, which contains the history of table space changes at a log file level. You can find out database path from database snapshot.

db2 get snapshot for db on <DB Name> | grep “Database path”

For each log file, DB2TSCHG.HIS contains information that helps to identify which table spaces are affected by the log file. Though the primary purpose of the file DB2TSCHNG.HIS is to track the table space changes, this file also has the information regarding range of LSN and timestamp within a log file. This information can be displayed using db2logsForRfwd command as shown below

$ db2logsForRfwd DB2TSCHG.HIS –all

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

Log file number : S0000000.LOG

First New Record LSN : 00000138800C

Log Extent Tail LSN : 000001770000

Backup End Time Stamp : 0

Flags : 107

pID : 1209781597 2008-05-03-02.26.37.000000 GMT

cID : 1215376321 2008-07-06-20.32.01.000000 GMT

Tablespace ID :

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

Log file number : S0000001.LOG

First New Record LSN : 00000177000C

Log Extent Tail LSN : 000001B58000

Backup End Time Stamp : 0

Flags : 107

pID : 1215376321 2008-07-06-20.32.01.000000 GMT

cID : 1215376322 2008-07-06-20.32.02.000000 GMT

Tablespace ID :

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

Log file number : S0000002.LOG

First New Record LSN : 000001B5800C

Log Extent Tail LSN : 000001F40000

Backup End Time Stamp : 0

Flags : 107

pID : 1215376322 2008-07-06-20.32.02.000000 GMT

cID : 1215376323 2008-07-06-20.32.03.000000 GMT

Tablespace ID : 0 2

If the DB2TSCHNG.HIS file is not in current path, you should provide the fully qualified path of this file to db2logsForRfwd command. However, note the following points regarding DB2TSCHNG.HIS file.

· This history file is available only for databases with archive logging enabled.

· This history file will have an entry for only the archived log files and not the active log files.

· The PRUNE HISTORY command prunes this history file also.

· When the registry variable DB2_COLLECT_TS_REC_INFO is off, history records will not be written to file DB2TSCHNG.HIS.

In the above output, “First New Record LSN” and “Log Extent Tail LSN” fields provide the range of LSN in a log file. The range of timestamp is available in pID and cID fields (second part). The pID and cID stands for previous and current LogID. Have you seen the reference of LogID anywhere else? The header record of a backup image also includes LogID. You can display the information in a backup image header using db2ckbkp command as shown below.

db2ckbkp –h <backup image filename>

You might be curious about the significance of LogID and so am I. But at this point, I don’t know any more about LogID.

If you want to dive deeper into log records, check the db2ReadLog API. You may also check the DB2 add-on tool Recovery Expert, if you are looking for a tool for log analysis.

Table Space Auto-resize in Real DB2 World

2008-06-29T08:45:00.001-06:00

Past few months my personal time with DB2 community was encroached by excessive customer engagements. Hence, no blog post for a long time. I am afraid that it may continue like this for a while. However, these customer engagements did bring me in direct contact with many DBAs in the DB2 community and provided opportunity to learn the problems/issues in their DB2 environment.

My customer engagements were primarily focused on standardizing and automating several DBA tasks using Stratavia’s Data Palette (a software platform for Data Center automation). It initially surprised me that in many of the customer environments, Table Space Size Management is in their top priority list of automation. But why not use the DB2’s in-built Table Space auto-resize feature? One reason could be the version of DB2 older than v8.2.2 (Table Space auto-resize was introduced in v8.2.2). However, this was not the reason. Here I will present a gap analysis between auto-resize feature and the actual requirements in the customer environments I was engaged with.

First of all, let’s consider the typical process of table space size management. It can be a planned effort (as a result of monthly or annual table space capacity planning) or it can be ad hoc (increase the size of table spaces that are about to get 100% full). Since the auto-resize feature deals with the second scenario, I will focus on that part only. Normally, DBA’s have some form of monitoring for table space size. When the size enters into alert zone (percent used above a threshold), we plan on increasing the size. This is a proactive approach. Compare it with the scenario where table space will be increased only when it does not have enough space to satisfy a DB request (almost 100% full). That’s how Auto-resize feature works. What if there is no space available on file system for extending the table spaces or auto-resize has already reached the max size? Whatever be the reason, if the table space could not be extended by auto-resize feature, it may affect the business processes dealing with that table space. Hence a proactive approach is essential for managing the table space size.

Secondly, there are environment specific custom requirements associated with increasing the size of table spaces. For example, send a notification whenever the table space is extended or extending the table space size should not cause the associated file system more than 95% full. Such requirements can not be fulfilled by the auto-resize feature.

The third point is little subtle but very important for big organizations having hundreds and thousands of databases. Imagine the admin effort required for managing auto-resize on table spaces under all these databases. Such organizations need a central system to apply and manage automation rather the local solutions like auto-resize feature.

Let me end this posting with a clarification that I have no intention to denounce the DB2’s table space auto-resize feature. In fact, it might be used in many environments and many of you might be finding it useful. However in my experience, organizations require more comprehensive automation solution for managing table space size than the table space auto-resize feature of DB2. I would like to hear your experience with table space auto-resize feature in case you are using it in your environment.

Mapping Application to Dynamic SQL

2008-03-22T15:48:00.002-06:00

If you need to find out dynamic SQL statements in the database package cache, you can do so using Dynamic SQL Snapshot. If you need to check the details of your application, you can use Application snapshot. If you are accessing application snapshot through table function, you have to use Statement snapshot to find the executing statement by that application. However, as soon as the statement execution is finished, you would not find that statement text in the statement snapshot. Also, even though dynamic SQL snapshot will show you this statement, it does not have any info regarding the application that executed it. Hence, if you are trying to map an application with a dynamic SQL statement, the snapshot table functions mentioned above may not be very useful.

I had to suffer with this limitation of DB2 for quite some time. Interestingly, other competitive vendor databases (Oracle & SQL Server) provided the link between SQL in Cache and their sessions. However, I recently found that in version 9, we have the mapping info between application and dynamic SQL statements available through db2pd (DB2 Problem Diagnostic) tool. In fact, this db2pd enhancement was made in some fixpack after v8.2. Under this enhancement, the “applications” option of db2pd provides you following new columns

· C-AnchID : Current anchor ID

· C-StmtUID : Current statement unique ID

· L-AnchID : Last anchor ID

· L-StmtUID : Last statement unique ID

The first pair of Anchor ID and Statement Unique ID can be used to find the current SQL statement being executed by the application and the second pair can be used to find the last SQL statement executed by that application. The “dynamic” option of db2pd includes following columns to map with the Anchor ID and Statement Unique ID pair.

· AnchID : Anchor ID of the statement

· StmtUID : Statement unique ID

You may check the “Scenario 2” under Monitoring and troubleshooting using db2pd at DB2 Information Center to look at an example of mapping application to a dynamic SQL statement. You may also run the following command and verify it yourself.

db2pd –appl –dyn –db

However, the problem with db2pd is that you can’t run it through SQL. Hence, if you want to collect the above info on a regular basis and store in a table as part of your database monitoring, you need to parse the db2pd output. I wish if the above AnchID and StmtUID info could be included in Application and Dynamic SQL snapshots, so that it can be accessed through snapshot table functions using SQL. At least now we have the mapping available through db2pd, which is much better than no mapping available earlier.

SQL Tips & Techniques- String Aggregation(Contd.)

2008-02-29T23:08:00.004-07:00

In my last post, I showed two ways to perform string aggregation. Both of them used recursive SQL. I also mentioned that a built-in function to perform string aggregation would be preferred. Though, I have not found a direct function for string aggregation, one of the comments on my previous post suggested the use of XMLAGG function. In fact, Serge Rielau used the XMLAGG function for string aggregation in his presentation “SQL on Fire! Part 1”. Let’s look at the query using XMLAGG function to perform the string aggregation.

Listing 5

SELECT category,

SUBSTR(names, 1, LENGTH(names) -2) as list

FROM

(SELECT

category,

REPLACE

(REPLACE

(XMLSERIALIZE

(CONTENT XMLAGG(XMLELEMENT(NAME a, name)

ORDER BY name)

AS VARCHAR(50)), '<A>', ''),

'</A> ', ', ') AS names

FROM mytab1

GROUP BY category) AS X

CATEGORY LIST

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

1 abc, bcd

2 cde, def, efg

3 fgh, ghi

3 record(s) selected.

In the above query, first we are converting each name into an XML expression using XMLELEMENT function. Here, XMLELEMENT function will add the tags <A > and </A> around each name. After the aggregation, we get rid of these tags using REPLACE function. Though, XMLAGG function is also a work around for string aggregation, I would prefer it over recursive SQL because it’s easier to understand and in my test it had lower estimated cost. Ideally, what we want is a function similar to myConcat as shown below.

Listing 6

SELECT category,

myConcat(name, ‘, ’) as list

FROM mytab1

GROUP BY category

CATEGORY LIST

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

1 abc, bcd

2 cde, def, efg

3 fgh, ghi

3 record(s) selected.

Is that something in your wish list also?

SQL Tips & Techniques- String Aggregation

2008-02-27T00:14:00.006-07:00

In my experience, application or database developers will have to deal with writing SQL queries more than a DBA (specially a System DBA). However, many a times DBAs are expected to help with writing complex queries. Keeping that in mind, I have decided to post SQL tips and techniques as I encounter them.

Recently, I had a requirement of performing string aggregation by concatenating them while grouping based on another column. For example, we have a table myTab1 with CATEGORY and NAME column values as shown below.

Listing 1

SELECT category, name

FROM myTab1

ORDER BY category

CATEGORY NAME

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

1 abc

1 bcd

2 cde

2 def

2 efg

3 fgh

3 ghi

7 record(s) selected.

The expected result is to have one CATEGORY per row with all the NAME values for a CATEGORY concatenated with a comma and a space in between. So the result should look as shown below.

Listing 2

CATEGORY LIST

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

1 abc, bcd

2 cde, def, efg

3 fgh, ghi

This could have been so simple if there was an aggregate function (similar to SUM, AVG, COUNT) to perform this task. However, there is no built-in function for it. So either you can write a function of your own or you have to use recursive SQL. You may refer the article “User-defined Aggregate Functions in DB2 Universal Database”, if you want to write a function for it. Here, I will show you a query that uses recursive SQL

Listing 3

WITH

t1(rowNum, category, name) AS

( SELECT rownumber() over(partition by category),

category, name

FROM myTab1 ),

t2(category, list, cnt) AS

( SELECT category, VARCHAR(name, 50), 1

FROM t1

WHERE rowNum = 1

UNION ALL

SELECT t2.category, t2.list || ', ' || t1.name,

t2.cnt + 1

FROM t2, t1

WHERE t2.category = t1.category AND

t2.cnt + 1 = t1.rowNum )

SELECT category, list

FROM t2

WHERE ( category, cnt ) IN (

SELECT category,

MAX(rowNum)

FROM t1

GROUP BY category )

ORDER BY 1

CATEGORY LIST

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

SQL0347W The recursive common table expression "RKUMAR.T2" may contain an infinite loop. SQLSTATE=01605

1 abc, bcd

2 cde, def, efg

3 fgh, ghi

3 record(s) selected with 1 warning messages printed.

Notice that in the above query, the definition of table expression t2 refers itself and hence we call such queries recursive. If you first run the query used in the definition of table expression t1, then run the entire query with t1 and t2 but without the last WHERE clause, you can easily follow logic involved here.

DB2 version 9.5 also supports the CONNECT BY syntax of Oracle for recursive queries. You may check the article “DB2 Viper 2 compatibility features” for details on CONNECT BY syntax. We may write the query using CONNECT BY as shown below.

Listing 4

SELECT category, list

FROM

(SELECT category, level,

MAX(level) OVER(PARTITION BY category) as maxlevel,

VARCHAR(SUBSTR(SYS_CONNECT_BY_PATH(name,', '), 3),50)

as list

FROM

(SELECT category,

name,

ROW_NUMBER() OVER (PARTITION BY category

ORDER BY name) AS curr,

ROW_NUMBER() OVER (PARTITION BY category

ORDER BY name) -1 AS prev

FROM myTab1)

CONNECT BY prev = PRIOR curr and category = PRIOR category)

WHERE level = maxlevel

ORDER BY 1

CATEGORY LIST

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

1 abc, bcd

2 cde, def, efg

3 fgh, ghi

3 record(s) selected.

In addition to CONNECT BY, the above query also uses the function MAX as OLAP aggregation function. You may check DB2 SQL Reference manual or the article “OLAP Functions in DB2” to learn about OLAP functions.

Locking issues

2008-02-06T08:08:00.000-07:00

Locking issues in a database often lead to finger-pointing between DBAs and Application developers. Application folks will complain that their application is performing poorly because of database locking issues. DBAs will respond that it’s the application design issue that’s causing the locks. In fact, the locking issues can be resolved by the combined effort of DBAs and Application folks.

DBAs have tools to diagnose the locking issue and explain to application folks the root cause. Then DBAs and Application folks can work together to analyze the transaction logic that’s causing the locking issue and make corrections if possible. Sometimes, the locking issues can be resolved or reduced by database level changes (e.g. changing maxlocks and locklist database parameters).

Now, let’s see what are the locking issues in DB2 and how can a DBA diagnose them. DB2 can have mainly three types of locking issues

· Deadlocks

· Lock escalation

· Lock wait and lock timeout

As a DBA, you should periodically check the occurrences of above locking issues through Database snapshot and make sure that there are not many occurrences of these locking issues. I will briefly describe here each of the three locking issues and also provide pointers for further details.

Deadlocks

A deadlock is created when two applications are each locking data needed by the other, resulting in a situation when neither application can continue execution. DB2 deadlock detector is responsible for handling deadlocks. When a deadlock is detected, the deadlock detector will choose a victim that will be automatically rolled back and issued an SQL0911 error message with a return code of 2. By rolling back the victim, the lock conflict is removed and the application can continue processing. Database parameter ‘dlchktime’ controls the time interval for checking deadlocks. DB2 also creates an event monitor called db2detaildeadlock by default to capture the relevant info about the deadlock which can be used to identify the root cause of the deadlock. Check the link “Deadlocks” at Information Center for more details.

Lock escalation

Lock escalation occurs when database manager will attempt to free up memory allocated to locking by obtaining table locks and releasing existing row locks. The desired effect is to make more lock memory available for additional applications. The following two database parameters have a direct effect on lock escalation:

· locklist- amount of storage (in units of 4k pages) that is allocated to hold the list of all locks held by all applications concurrently connected to the database.

· maxlocks- the allowable percentage of locklist that can be used by an application.

Both the above database parameters are automatic configurable by default in version 9. Though the above two database parameters influence the lock escalation, we should not always change them to resolve the lock escalation. Check the link “Correcting lock escalation problems” at DB2 Information Center for a step wise approach to resolve the lock escalation issue.

Lock wait and lock timeout

Finally, what’s lock wait and lock timeout? An application, that makes a request for a lock that is not compatible with the existing locks on the object, will have to wait to acquire the lock and this phenomenon is called lock wait. There is a database parameter called locktimeout that determines how long the application in lock wait state should continue waiting before it times out. If the timeout period is exceeded, the waiting application receives an SQL0911 error message with a return code 68 and the application’s unit of work (transaction) is rolled back by the database manager.

Note that the default value of locktimeout parameter is -1, which causes the lock timeout to be disabled. That is, an application that is waiting for a lock will continue to wait indefinitely or until the lock is released. To avoid such a scenario, it’s recommended to set a positive value for locktimeout (specially in transactional environment). In transactional environment, the recommended starting value for locktimeout is 30 seconds. However, tuning may be necessary in order to find the more appropriate value for this parameter.

You can find out the current lock waits in database using lock wait snapshot. Check the link SNAP_GET_LOCKWAIT table function at DB2 Information Center for more details about lock wait snapshot.

Diagnosing the lock timeout is little involved. Through the snapshot monitors, you would not have the details of applications involved in lock contention that resulted in the lock timeout. Prior to version 9, you could capture such details using event monitors. However, the event monitors to capture the transactions and statements may cause considerable performance impact and hence that might not be an option in your environment. DB2 v9.1 FP4 introduced lock timeout reporting feature to help the diagnosis of lock timeouts. There is a registry variable called DB2_CAPTURE_LOCKTIMEOUT, which controls the lock timeout reporting function. When this registry variable is set to ON, it captures basic information about each lock timeout that happens within the DB2 instance and a lock timeout report is created under the instance diagnostic path (diagpath). Check the following links at DB2 Information Center for more details.

· Lock timeout reporting

· Lock timeout report files

Hope the above info will help to reduce locking issues in your environment. As mentioned in the beginning, DBAs can help to diagnose the locking issues. However in most of the cases, the locking issue has to be resolved from application side (correcting the application logic, using the proper isolation level etc.).

Mimicking Production Database

2008-01-29T08:28:00.000-07:00

If you are a DB2 DBA, most likely you have already used db2look to extract the DDL from a DB2 database. However, db2look can do more than just extracting the DDL. Sometimes you may want to create a test database similar to production database in order to test your applications and query access plans. If the database is small, you may create a test database by restoring the production database backup image. What if the database is not small? Creating a test database of same size as production database may be cost and time prohibitive. In that case, you may want to have only a subset of production data loaded into the test database. Or, you may even want to have dummy data loaded into your test database instead of actual production data due to security reasons.

For the reasons explained above, you may have to create a test database which will not have exactly the same data as your production database. DB2 has a cost based optimizer and if the data is different in the test database, you may get different query access plans than the production database. This might be okay, if you are only testing the functionality of an application. But what if you have to test the performance or debug some SQL performance related issue? In that case, you would want to have the same query access plan in your test database as it will be in your production database. What’s the solution now? The solution is to give the production environment feeling to DB2 optimizer even in your test environment. How do we do that? DB2 optimizer generates access plan based on following

· Statistics

· Configuration parameters

· Registry and environment variables

You can capture the above from production database and apply to your test database. You can use db2look for this purpose. The –m option of db2look can be used to create UPDATE statements against the updatable catalog tables under the SYSSTAT schema as well as RUNSTATS command against all of the tables. The –f option of db2look can be used to generate necessary configuration updates and set statements for registry and environment variables. Check the following link at DB2 Information Center for more details

Mimicking databases using db2look

The above approach of mimicking database for performance testing has another advantage. Once you set up a test database, you will have to update only the statistics in the test database if the volume of data in production database changes over time. Updating the statistics only will take much less time than rebuilding the test database.

DB2 Autonomic Computing

2008-01-21T00:38:00.000-07:00

One of the highlights of DB2 Version 9.5 is “Manage your business, not your database”. IBM claims that the DB2 autonomic computing environment is self-configuring, self-healing, self-optimizing and self-protecting. Autonomic computing is not new in Version 9.5. Past several releases of DB2 have consistently added enhancements in this direction. Just look at the database and database manager configuration parameters in Version 8.1, 8.2, 9 and 9.5 and you can see a trend of how more and more configuration parameters have started supporting the AUTOMATIC setting. Some of the other important features under autonomic computing are Self-tuning memory manager, Automatic storage, Automatic database backup, Automatic statistics collection, Configuration advisor, Health monitor and Utility throttling.

However, somehow I feel that not all the autonomic features in DB2 are widely used. For example, most of the DB2 DBAs are taking their database backup using scheduled scripts rather than using the Automatic database backup. It would be interesting to know which autonomic features are really getting embraced and which ones are not so popular. So, I have added a poll (on the right side panel of my blog) to check which DB2 autonomic features you use in your environment. You can select more than one options in this poll. Also, it would be great if you can provide comments here about why you like or dislike a particular autonomic computing feature.

DB2 Installation Enhancements

2008-01-12T22:44:00.000-07:00

Prior to Version 9, the installation path of DB2 was fixed on each platform. As a result, you could not have multiple copies of DB2 on the same computer under a single OS. DB2 Version 8 introduced a patchy solution to this problem in the form of Alternate Fix Packs, which allowed coexistence of multiple Fix Pack levels of DB2 version 8 on the same computer. However, now you don’t have to deal with Alternate Fix Packs and you don’t have to get frustrated with the stiffness of DB2 Installation any more.

With DB2 Version 9, you can use multiple DB2 copies on the same computer. Each DB2 copy can be at the same or different code levels. We can also have DB2 Version 8 and Version 9 on the same computer. IBM has listed the following benefits of doing this (which sounds reasonable):

The ability to run applications that require different DB2 versions on the same machine at the same time.
The ability to run independent copies of DB2 products for different functions.
The ability to test on the same computer before moving the production database to the latter version of the DB2 product.
For independent software vendors, the ability to embed a DB2 server product into your product and hide the DB2 database from your users.

A DB2 copy can contain one or more different DB2 products. This refers to the group of DB2 products that are installed at the same location.

How is this possible now? You might have already guessed: now you can choose your own path while installing DB2. Each DB2 Copy has its own installation path. That’s cool, but now how will you find out all the installed DB2 products? DB2 Version 9 on Linux and UNIX comes with a command db2ls, which you can use to list:

Where DB2 products are installed on your system and list the DB2 product level.
All or specific DB2 products and features in a particular installation path.

Unfortunately, db2ls is not available on Windows. However, on Windows you can find all the installed DB2 copies and their installation path in Windows Registry under HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies.

Another notable enhancement for DB2 Installation is non-root installation on Linux and UNIX. Non-root installation is available with DB2 Version 9.5. You can check the details of it at the link: Non-root installation overview. However, there are several limitations of non-root installation. Apart from limitations, there are few differences between the directory structure of root and non-root installations.

More good news: Fix Pack post-installation tasks are automated on Linux and UNIX in DB2 Version 9.5. In previous releases, you had to update instances and bind the packages manually after installing a fix pack. Now you don’t have to run db2iupdt or dasupdt commands to update the instances after installing a fix pack, they will be performed automatically when you install the fix pack. In addition, binding occurs automatically at the first connection. As a result, once you start the database manager, the DB2 product is ready to use immediately after fix pack installation. You may check the details of applying fix packs at DB2 Information Center.

How to Recreate DB2 Instance

2008-01-06T22:48:00.000-07:00

Can we drop and recreate a DB2 instance without restoring all the databases underneath? The answer is YES. This is not a very common requirement, but once I ended up doing this. I had few DB2 version 8.2 single partition instances of type WSE (Workgroup Server Edition). I needed to use db2_all command with these instances to have my automation scripts work with these instances (You may read my previous posting “db2_all and RAHUSER” to understand the use of db2_all even for single partition instance). However, db2_all does not work with WSE instance and hence I needed to convert these instances from WSE to ESE (Enterprise Server Edition). I found that the installed product and license was good for allowing an ESE instance. Only problem was that the instances were created with WSE option.

Initially, I thought of using db2iupdt command for this as I remembered converting 32-bit instances to 64-bit using db2iupdt. Unfortunately, db2iupdt did not have any option to convert a WSE instance to ESE. I could not find any other one step command to perform this task. The databases under these instances were small non-production databases. Hence, I thought even if I will have to recreate all the instances as ESE and restore all the databases underneath, I would go for this instance conversion from WSE to ESE. The next question was whether I really need to restore all the databases after recreating the instance. When we drop an instance using db2idrop command, it does not drop the databases underneath. So if we recreate the instance, we should be able to re-catalog the databases under the newly created instance and it should be good to go. Logically this sounded good, but I had never done it before. So, first I tested it using the steps listed below and it worked fine. I used the same steps to convert all my WSE instances to ESE and did not have to restore the databases underneath.

Capture the details of databases cataloged under the instance (using “list db directory”).
Capture the Database Manager configuration (using “get dbm config”) and registry variables (using “db2set –all”).
Copy any files that you may need to refer or use with the newly created instance (e.g. files of external routines, any script, log files etc.).
Ensure a good backup of local databases under the instance (in case something goes wrong, backup will be your savior).
Stop the instance.
Drop the instance (using db2idrop command).
Recreate the instance as per your requirement (using db2icrt command).
Make the Database Manager configuration and registry variables same as original instance using the info captured in step-2.
Copy any required files captured in step-3 under the same path as it was with the original instance.
Start the instance.
Catalog the databases captured in step-1 under the newly created instance (using catalog database command).

If for some reason, you also need to drop and recreate your instance using the above steps, I would strongly recommend you to test it first in your specific environment. You may require few additional steps in your environment that you may discover during your testing.

Hope you enjoyed the holidays. Wish you very happy New Year.

Database Roles

2007-12-26T00:12:00.000-07:00

If you are familiar with Oracle Database, you will be happy to know that DB2 9.5 has introduced few new features that you may already be familiar with in Oracle. First of all, there are several SQL related compatibility features (e.g. support for dual, decode etc.). Check the developerWorks article DB2 Viper 2 compatibility features for more details. These compatibility features will greatly help the migration from Oracle to DB2 database.

Database Roles, another new feature in DB2 9.5, was also available in Oracle since long. A role has a certain set of privileges and a user who is granted membership in this role inherits those privileges. Roles are also available in Informix, SQL Server, Sybase and may be some other RDBMS’s.

DB2 provided an alternative to role in the form of group based authorization. You may grant one or more privileges to user groups and all the users in that group will automatically have those privileges. You may add a user to that group (equivalent to granting a role to a user) or remove a user from the group (equivalent to revoking a role from a user). However, there are two major limitations with group based authorization

Privileges and authorities granted to groups are not considered when a user is creating views, materialized query tables (MQTs), SQL routines, triggers and packages containing static SQL. This is because groups and users are controlled externally from the DB2 database system, for example, by an operating system or an LDAP server. So if a user will be removed from a group, database may not be aware of it and would not update the validity of created objects mentioned above.
A role can be granted to a role and so a role hierarchy is possible, which is not possible with group based authorization.

To overcome the above limitations, DB2 9.5 introduced roles in addition to group based authorization. In DB2, a role is a database object that groups together one or more privileges and can be assigned to users, groups, PUBLIC, or other roles by using a GRANT statement. For more details, check the Roles at DB2 Information Center.

DB2 Partitioning

2007-12-09T23:07:00.000-07:00

Divide and conquer- that’s the mantra used to deal with large volume of data in data warehouses. What are the features available in DB2 to divide or partition the data? DB2 provides following three features to partition your data.

Data Partitioning Feature (DPF)
Multi-Dimensional Clustering (MDC)
Table Partitioning (TP)

DPF allows you to partition the whole database. In fact, database partitions are defined on the instance level. Hence, all the databases under an instance are partitioned in the same way. DPF uses a hashing algorithm to distribute the data of a table across multiple database partitions based on the values in columns of distribution key of the table. Distribution key has to be selected carefully while creating the table to ensure a balanced distribution of data among all the database partitions. Normally, the distribution key with columns of high distinct cardinality provides good distribution of data. DPF is based on shared nothing architecture, i.e. each partition has its own processes, memory and disk storage. DPF allows you to scale the size and performance of your database by adding more partitions and additional resources (CPU, Memory, Disk storage) with each additional partition. Because of shared nothing architecture DPF can achieve almost linear scalability. Multiple partitions can be created on a single machine or it can be spread across multiple machines such that each machine can have one or more partitions.

MDC allows you to cluster your data on multiple dimensions. A common example is clustering your data warehouse fact table based on say following three columns: month of transaction date, region or state, industry of your customer. Once you cluster your table using above three dimensions, there will be a storage block (of extent size) allocated for each unique combination of values of these three dimensions. Also, a block index will be created which will have an entry pointing to each block rather than each row of the table. As a result, block indexes are much smaller compared to regular Row ID indexes and index scan on block indexes are much faster. Another advantage is that the data in a MDC table is self organized; you don’t have to reorg these tables. The biggest advantage of MDC table is query performance. If your query includes one or more dimensional columns in where clause, the block index will help to quickly figure out the subset of blocks that may have your result dataset. However, you should be aware that block indexes do not support index-only access. This is because a block index can point to an empty block, i.e. a block with now rows for a unique combination of dimension values. The performance of an MDC table is greatly dependent upon the proper choice of dimensions and the block (extent) size of the tablespace for the given data and application workload.

Table partitioning (TP) is a new feature in DB2 UDB V9. This feature is also known as range partitioning because using this feature you can define data partitions (not to be confused with database partitions in DPF) based on range of values of one or more than one columns. Each data partition is a separate database object and they can be placed in different tablespaces, if desired. Also, TP supports attaching and detaching a data partition from the TP table. A detached data partition becomes a regular table. These attach and detach features make the data roll-in and roll-out very convenient in a TP table.

So far I have very briefly described three partitioning features available in DB2 UDB. Another important notion is that all the three partitioning features can be used on the same table and decision of using one type of partitioning feature does not interfere with the decision of using other type of partitioning. Listed below are few good references which describe the DB2 partitioning features and its usage in data warehousing environment in more detail.

The IBM Redbook Leveraging DB2 Data Warehouse Edition for Business Intelligence – Chapter 10 (10.4 Partitioning)

IBM developerWorks article DB2 partitioning features

The IBM Redbook Up and Running with DB2 UDB ESE: Partitioning for Performance in an e-Business Intelligence World

db2_all and RAHUSER

2007-07-29T13:19:00.001-06:00

If you need to run a command on all or a subset of database partitions, you can use db2_all.

For example, to change the database configuration LOGFILSZ to 100 for sample database on all database partitions that are specified in node configuration file, we can use the following command:

db2_all “UPDATE DB CFG FOR sample USING LOGFILSZ 100”

Similarly, if you have to change the database configuration only on partition 1, you can run the following command:

db2_all “<<+1<UPDATE DB CFG FOR sample USING LOGFILSZ 100”

If you have to change the database configuration on all the database partitions except partition 0, you can run the following command:

db2_all “<<-0<UPDATE DB CFG FOR sample USING LOGFILSZ 100”

On UNIX based platforms, db2_all uses the remote shell program specified by DB2RSHCMD registry variable. Starting with DB2 v8.1 FP 9, we can chose either rsh or ssh for DB2RSHCMD registry variable. If this registry variable is not set, rsh (or remsh for HP-UX) is used.

There is an environment variable RAHUSER, which can be used to specify the user ID under which the remote command is to be run on UNIX based platforms.

Though the primary purpose of db2_all is to run the command on all the database partitions that you specify, it can also be used for the purpose of running the command under a different user ID with the help of RAHUSER environment variable. Thus, db2_all becomes useful even in a single-partition database. However, db2_all is available with Enterprise Server Edition (ESE) only. So, if you have a Workgroup Server Edition (WSE) instance, you would not be able to use db2_all.

The above mentioned use of db2_all, which allows you to run a command under a different user ID is particularly helpful in situations where your task automation script always runs as a fixed user ID or a common user ID say ScriptUser and if you wish to run the commands inside those scripts under a different user ID say DB2CommandUser (e.g. a user with SYSDBA authorization). In such a scenario, you can use following two solutions:

1. Use the ssh or rsh command directly to run the commands inside script as DB2CommandUser.
2. Use db2_all instead to run the command inside script as DB2CommandUser. This allows you to have a generic script which can use either rsh or ssh remote shell program depending on the DB2RSHCMD registry variable.

If you are using ssh as remote shell program for db2_all, ssh authentication should be configured such that ScriptUser can run the commands under the login of DB2CommandUser without prompted for any password or passphrase. This can be verified by running the following command while logged in as ScriptUser.

ssh DB2CommandUser@<HostName>

pwd

If the above command runs successfully without prompting for any password or passphrase, it indicates that the ssh authentication has been setup correctly. In the above command <HostName> can be localhost or a remote host depending on where do you want to run the command.

Similarly, if you are using rsh remote shell program for db2_all, the .rhosts file under the DB2CommandUser home directory must include the following line in order to allow ScriptUser to run command under DB2CommandUser login.

<HostName> ScriptUser

Where, <HostName> is the host name of computer where ScriptUser exists. The permission of .rhosts file should not allow the Group or Other to write to this file. The recommended permission for .rhosts is 600, though 644 will also work.

On Windows platform, db2_all uses “DB2 Remote Command Service” to run the commands on all database partitions that you specify. Also, RAHUSER should be the logon account associated with the DB2 Remote Command Service.

Copy and Drop Schema

2007-07-27T00:39:00.000-06:00

Do you need to make the exact copy of all the objects in a given schema? Are you planning to write a script for this task? If you are using DB2 version 9, perhaps you can avoid writing this script and still perform the same task using a single statement.

DB2 version 9 provides a new stored procedure called ADMIN_COPY_SCHEMA that can be used to copy all the objects from one schema to another schema name. The syntax for this stored procedure can be found at

ADMIN_COPY_SCHEMA at DB2 Info Center

In addition to the copy schema stored procedure, DB2 9 also offers a drop schema stored procedure to drop all the objects in a schema. The stored procedure is called ADMIN_DROP_SCHEMA. The syntax of this stored procedure can be found at

ADMIN_DROP_SCHEMA at DB2 Info Center