db2top: Memory screen and db2mtrk (video)

Today’s video is a comparison between the output of the db2mtrk command and the db2top Memory screen. It covers current allocations, high watermarks, and maximum allocations for the instance, database, and application groups of memory pools as well as the repeat mode of db2mtrk.

Watch “db2top: Memory screen and db2mtrk” directly on YouTube.

db2top: Buffer pool hit ratio (video)

Today’s video is a demonstration of buffer pool hit ratio in the Bufferpools screen of db2top. To produce a hit ratio of 100%, we query a single row repeatedly to perform logical reads with no corresponding physical reads. To reduce the hit ratio, we query more data than can fit in the buffer pool, resulting in physical reads.

Watch “db2top: Buffer pool hit ratio” directly on YouTube.

db2top: Regular expressions (video)

Today’s video is a demonstration of the regular expressions (regex) feature of db2top. In the video, I apply regular expressions to the Dynamic SQL screen to filter what SQL statements are shown. I demonstrate setting and clearing a regex, simple regular expressions, and POSIX extended regular expression metacharacters:

Watch “db2top: Regular expressions” directly on YouTube.

db2top: The Locks screen (video)

Today’s video is a demonstration of the Locks screen of db2top. In the video, I create a lock chain and launch db2top to examine the Application Status, Object Name, Locked By and Lock Count columns for three applications involved in the chain. I then use the Lock Chain screen to visualize the chain:

Watch “db2top: The Locks screen” directly on YouTube.

How Inception is like metaprogramming

Inception movie poster

I saw the movie Inception over the weekend and after reading What a Programmer Sees When He Watches Inception, in which Marke Hallowell compared the dreams-within-dreams concept from the movie to a recursive function, I think I too see parallels between the movie and computer programming. I think a parallel exists between dreams-within-dreams and programs-within-programs. Continue reading ‘How Inception is like metaprogramming’

Metaprogramming in SQL (Part 4)

In the past three posts, we have written metaprograms to create functions for comparing objects of built-in types, user-defined distinct types, ROW types, and ordinary ARRAY types. In today’s post, we will write a single metaprogram that can create comparison functions for all four. Continue reading ‘Metaprogramming in SQL (Part 4)’

Metaprogramming in SQL (Part 3)

In the first post in this series, I introduced the concept of metaprogramming in SQL using dynamic SQL and applied the technique to write a procedure that could generate comparison functions that compare two objects of any basic type. In the second post, I expanded the procedure to produce comparison functions that compare two objects of any ROW type. In today’s post, I will introduce the ARRAY type from DB2 9.7 and show how we can go about comparing two arrays, including arrays of ROW type objects. Continue reading ‘Metaprogramming in SQL (Part 3)’

Metaprogramming in SQL (Part 2)

In the last post on this subject, we used dynamic SQL to generalize an integer comparison function into a stored procedure that builds comparison functions of any basic type. This served as a simple example, but did not really offer much benefit over using the ‘=’ operator. In today’s post, I will extend the same technique to make functions that compare ROW objects just as easily, even though the ‘=’ operator cannot be used on ROW objects. Continue reading ‘Metaprogramming in SQL (Part 2)’

Metaprogramming in SQL (Part 1)

Metaprogramming can feel like magic. You call a function that you neither wrote nor imported from any library and, magically, it comes back with a result. Even more magical is how metaprogramming lets you do otherwise impossible things with your programming language. In “The Art of Metaprogramming”, Jonathan Bartlett’s developerWorks series, he lists three examples that illustrate the benefits of metaprogramming:

  • You can use metaprogramming to pre-generate tables of data for use at run-time.
  • In applications with large amounts of boilerplate code and limited ability to abstract this code cleanly into functions, you can use metaprogramming to create a mini-language to write the boilerplate for you at run-time, simplifying your own code.
  • You can use metaprogramming to transform a programming language that promotes verbosity into one that celebrates terseness. In addition to making up for inadequate language design, this can also ease maintenance.

Examples abound. A famous one is Ruby On Rails’ ActiveRecord class inspired by Martin Fowler’s ActiveRecord design pattern. At least in early versions of Rails, you could simply create an empty class like the following:

class Employee < ActiveRecord::Base

and, assuming you had a division column and mgrlastnm column in the employees table in your database, you could write code like Employee.find_by_division_and_mgrlastnm "Sales", "Simpson" and it would just work. The Rails metaprogramming logic would detect that such a function did not exist and would generate the missing function at run-time.

So how does all this relate to SQL? In this post and others, I will answer that question and attempt to illustrate the benefits of metaprogramming in SQL PL. Continue reading ‘Metaprogramming in SQL (Part 1)’

db2top: The Watch feature

For the second video in this video series, we have a demonstration of the db2top Watch feature. It demonstrates capturing a single application’s SQL, capturing complete database structures to a set of files, and capturing all the dynamic SQL in the cache:

Watch “db2top: The Watch feature” directly on YouTube.

  • Search