db2top Filters


On a busy system, sometimes the amount of data returned by our monitoring tools can be overwhelming. Fortunately, db2top was designed with this in mind and has a very flexible and powerful feature for extracting the information critical to your decision-making from reams of monitoring data. This feature, called regular expressions, is the subject of today’s post.

A regular expression, or regexp, is used to match a string of text against a pattern. The db2top tool uses a form of regexp called the IEEE POSIX Extended Regular Expression (ERE). This kind of regexp allows you to specify literals, metacharacters, and escaped metacharacters, which are a form of literals. Literals are characters that match only themselves. For example, the pattern a matches the string “a”. Metacharacters do not match themselves but instead can be used to match sequences of characters, match any character from a particular set, or be used to modify the matching behavior of literals or other metacharacters. The following are POSIX ERE metacharacters that db2top supports (source: Wikipedia article on regular expressions):

Metacharacter Description
. Matches any single character. Within POSIX bracket expressions, the dot character matches a literal dot. For example, a.c matches “abc“, etc., but [a.c] matches only “a“, “.“, or “c“.
[ ] A bracket expression. Matches a single character that is contained within the brackets. For example, [abc] matches “a“, “b“, or “c“. [a-z] specifies a range which matches any lowercase letter from “a” to “z“. These forms can be mixed: [abcx-z] matches “a“, “b“, “c“, “x“, “y“, or “z“, as does [a-cx-z].

The - character is treated as a literal character if it is the last or the first character within the brackets, or if it is escaped with a backslash: [abc-], [-abc], or [a\-bc].

[^ ] Matches a single character that is not contained within the brackets. For example, [^abc] matches any character other than “a“, “b“, or “c“. [^a-z] matches any single character that is not a lowercase letter from “a” to “z“. As above, literal characters and ranges can be mixed.
^ Matches the starting position within the string.
$ Matches the ending position of the string.
* Matches the preceding element zero or more times. For example, ab*c matches “ac“, “abc“, “abbbc“, etc. [xyz]* matches “”, “x“, “y“, “z“, “zx“, “zyx“, “xyzzy“, and so on. (ab)* matches “”, “ab“, “abab“, “ababab“, and so on.
{m,n} Matches the preceding element at least m and not more than n times. For example, a{3,5} matches only “aaa“, “aaaa“, and “aaaaa“.
? Matches the preceding element zero or one time. For example, ba? matches “b” or “ba“.
+ Matches the preceding element one or more times. For example, ba+ matches “ba“, “baa“, “baaa“, and so on.
| The choice (aka alternation or set union) operator matches either the expression before or the expression after the operator. For example, abc|def matches “abc” or “def“.

You escape metacharacters (turn them into literals) by preceding them with a ‘\’ (backslash) character. This makes the backslash itself a metacharacter, which implies that to make a backslash a literal, you need to type it twice in a row.

You tell db2top that you want to use a regular expression to filter its data by pressing the ‘/’ key. This causes a prompt to appear at the top left of the screen just under the header.

You can enter a regular expression from just about any data reporting screen in db2top. The home screen is an exception. Pressing the ‘/’ key there simply pops up a message saying, “Search not available”.

Let’s apply regular expressions to the Dynamic SQL screen. Launch the Dynamic SQL screen by holding Shift and pressing the ‘D’ key. Here is what it may look like without a regular expression applied:

db2top Dynamic SQL screen with no regexp
db2top Dynamic SQL screen with no regexp

Let’s use a regular expression to show only the GRANT statements. Type “/grant” without the quotes and press the Enter key. You should see something like this:

db2top Dynamic SQL screen with regexp of /grant
db2top Dynamic SQL screen with regexp of /grant

Notice that “grant” matched “GRANT” – db2top’s regular expressions are case-insensitive. Also, notice that on the right side of the header, to the left of the qp=off status, is our regular expression.

You can clear the regular expression by typing just the ‘/’ key and pressing the Enter key.

Let’s now use a more sophisticated regular expression to show only those CALL statements that specify a schema for the procedure they are calling. But first let’s see what would happen if we only searched for the pattern “/call”:

db2top Dynamic SQL screen with regexp /call
db2top Dynamic SQL screen with regexp /call

Here we see that even calls to procedures unqualified by their schema are also listed. Now we’ll filter out such calls with the pattern “/call [a-z]+\.[a-z]+”. Here we are using the bracket expression with the ‘+’ metacharacter to match any schema. Then we use a ‘.’ character escaped with a backslash followed by another bracket expression and ‘+’ to say that there must be a ‘.’ in the string between the schema name and the procedure name. This ensures we get only those call statements that qualify their procedure names with schemas:

db2top Dynamic SQL screen with regexp /call [a-z]+\.[a-z]+

db2top Dynamic SQL screen with regexp /call [a-z]+\.[a-z]+

For other articles on db2top at this site, see the main db2top page.

  • Peter

    Great Info ! Thanks for this excellent blog and information on “db2top”

  • Either way, I reached out with a key question for each of them, designed to give you not only a nugget of useful information but a better sense of the classes, too, which, by the way, have been based on RPG & DB2 Summit courses. Say “Goodbye! …

  • Brooksja

    I love DB2top.  This will make it that much more flexible.  Do the regexp filters cut the retrieval times, or just effect the output?