datori

How do we quickly populate a database table with arbitrary LOB data? This question often comes up when we need to generate some data for performance testing or tuning. We can, of course, write a little utility in our favourite programming language, such as Java or Perl.

The Java approach can be found in DB2 documentation, for example, here. It shows a stored procedure, but we can see how this idea can be used in a standalone application as well. All we need to do now is write the code, compile it hoping there are no errors (as if!), and wait while a few hundred thousand of LOBs get inserted over a JDBC connection. Fun!

But is there a quicker way? How about three simple Bash commands? (more…)

Determine group of a directory (current directory in this case)

perl -e 'print getgrgid((stat($ARGV[0]))[5]) . "\n";' $PWD

I’m not sure why I needed that, but I did need it once.

Just in case I ever forget how I did it… I was trying to download some 40 page PDF brochure from a government web site – I wanted to print it out and read it off-line. However, it was cleverly split into 20 different PDFs – no doubt for convenience. Instead of spending 20 minutes clicking on those various links and printing 20 document fragments, I chose to spend twice that time trying to automate the process. And here it is, in all its glory:

curl -s "http://www.datori.org" \

| perl -n -e 'chomp;s/.*?(?:(?i)href)="([^"]+)".*?(?:$|(?=(?i)href))/$1\n/xg and print'

The “thing” downloads the specified page and extracts all linked URLs from it, as indicated by the “href” tags. You’ve got to appreciate the enormity of perl…

Here’s one way to obtain the Unix time value (the number of seconds since midnight on January 1, 1970) from a DB2 timestamp:

values(
  (days(current_timestamp-current_timezone) - days('1970-01-01') )*86400 + 
  midnight_seconds(current_timestamp - current_timezone)
)

This can be used in a query directly or wrapped into a simple SQL user-defined function.

Or may be top 10. Or 3. Whatever the number, we are often looking for the worst offenders kicking up the server’s CPU utilisation or I/O wait time to the skies. DB2 built-in snapshot functions are a great help. Run “select * from table (snapshot_dyn_sql('YOURDB', -1)) t order by rows_read desc fetch first 5 rows only” and you will get a list of the queries retrieving the most data. The only thing is, snapshot functions provide, well, snapshots of the DB2 monitor data and are not by themselves suitable for the collection of historical data. Monitor counters can be reset without your knowledge, for example when the database is deactivated or when the SQL statement expires from the package cache.

To ensure continuity of the data collected by snapshot functions we can quickly create a table where we will store historical snapshot information:

create table DYNSQLDATA as (select * from table (snapshot_dyn_sql('YOURDB', -1)) t) with no data


We will then insert output of the snapshot function into this table at regular intervals:

insert into DYNSQLDATA select t.* from table (snapshot_dyn_sql('YOURDB', -1)) t

Now we can easily determine which statements demand most resources and analyze their historical performance. (more…)