Where to find your db2dump directory for a DB2 Windows instance?

On UNIX there’s a one to one mapping between instance owner userid and the path to the diaglog, and unless you’ve redirected it, you’ll find it in ~/sqllib/db2dump/. Here’s a reminder for myself of the magic required to find your db2diag.log file in a Windows instance:

E:\> db2set db2instprof
C:\ProgramData\IBM\DB2\db2build

E:\> cd /d C:\ProgramData\IBM\DB2\db2build\

C:\ProgramData\IBM\DB2\db2build> db2ilist DB2

C:\ProgramData\IBM\DB2\db2build> cd db2

C:\ProgramData\IBM\DB2\db2build\DB2> dir *log
 Volume in drive C has no label.
 Volume Serial Number is 8E04-1BBB

 Directory of C:\ProgramData\IBM\DB2\db2build\DB2

02/22/2012  03:54 PM            10,555 db2diag.log
02/22/2012  03:54 PM             1,424 db2resync.log

Forcing DB2 client side connections to hang on one of the members.

For test purposes I wanted to force an inter-member LOCK dependency. This turns out to be pretty easy. First I created a table with a couple of dummy rows

db2 create table x '(x int)'
db2 insert into x 'values(1)'
db2 insert into x 'values(2)'
db2 insert into x 'values(3)'
db2 commit

Then setup an uncommitted update to these rows

# member 1
db2 connect to testdb2
db2 +c update x set x = x + 1

Doing the same thing on another member hangs as expected:

# member 2
db2 connect to testdb2
db2 +c update x set x = x + 1

commit on the first member resolves the hang. Interestingly one can do a ‘select * from x’ without any trouble, since we allow concurrent read and update, but one gets the pre-commit values of the rows in the table.

some rudimentary SQL

Ironically, despite 10 years of working on a database, my work has been so far in the guts that I rarely have to do any SQL myself.  At one point when I’d been drafted for some some project management related work I did some fancy stuff with it making forms with python to compare regression results, but I’ve already forgotten all that.  Here’s a couple notes to myself of the very simplest stuff.

– create a table.  Note the backwards “prototype” compared to a C function;)

create table b (x int)

– how many rows?

select count(*) from b

– double table size

insert into b select * from b

–touch the whole thing to force IO and general activity in the database engine

update b set x = (x+1)