dynamic DB2 database manager configuration parameters: must be connected!

I was testing some updates to the code that gets executed when we process an update db cfg. Specifically, something of the following form

db2 update db cfg for b using CF_SCA_SZ 4096
db2 get db cfg for b | grep CF_SCA_SZ

I wanted to override the automatic default to set it to something very small to force a testing condition for my code. With an earlier version of my changes for this cfg update, I was able to do these sorts of updates dynamically, but then I started hitting errors like

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, the database
must be shutdown and reactivated before the configuration parameter changes
become effective. 

I figured that I’d messed things up with my changes, preventing the dynamic config updates, but couldn’t figure out what. I proceeded to take db2trc’s, seeing how far through the internal function sqlfUpdateDbCfg() [which shows up in the db2diag.log], before there was a failure, and finally bugged the owner of the configuration code.

Turns out the resolution of this problem shouldn’t have been pouring through the source code, taking internal traces, and bugging developers that knew about the code. Instead I needed to RTFM (although the cfg owner nicely didn’t say that). Here’s what the help says for this error:

db2 \? SQL1363W


SQL1363W  One or more of the parameters submitted for immediate
      modification were not changed dynamically. For these configuration
      parameters, the database must be shutdown and reactivated before
      the configuration parameter changes become effective.

Explanation:

The database configuration command has been successfully processed.
However, not all changes were processed right away.

Changes to database configuration parameters can only take effect
dynamically if you are connected to the database. Not all configuration
parameters support dynamic update, meaning that they will not take
effect until the database is deactivated and reactivated.
...

I’d activated my database, but hadn’t connected. Earlier when I didn’t have this trouble I must have been connected. Simple explanation, leaving me feeling very dumb.

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)