DB2 fails to start after promoting to DC

Our backup database server is now also an additional domain controller. After DC promotion DB2 failed to start with error:

No mapping between account names and security IDs was done.

It’s an expected behavior, since server removes all local users groups during promotion, including DB2ADMNS and DB2USERS. These groups are used for extended security and in case it’s enabled (which is default) you will experience these kinds of problems. If you don’t change these groups before promotion then you won’t be able to use db2extsec to change them gracefully after promotion because database just won’t start and all CLI commands won’t work.

To solve this problem you need to disable extended security by changing DB2_EXTSECURITY registry variable to NO in HKLM\ SOFTWARE\ IBM\ DB2\ GLOBAL_PROFILE and HKLM\ SOFTWARE\ IBM\ DB2\ InstalledCopies\ DB2COPY1\ GLOBAL_PROFILE. Then create DB2ADMNS and DB2USERS active directory groups and point to them using:

db2extsec -u mydom\db2users -a mydom\db2admns

Bear in mind that using domain groups for extended security is supported starting from DB2 version 9 Fix pack 2. If you’re using an older version then you will have to disable this feature.

Advertisement

shIT happens

After we moved our DB2 server from v9.7.3 x86_32 to v9.7.5 x86_64  (with server replacement) DB2 Storage Management has broken down. Tasks that make snapshots of tablespaces have stopped working with error:

CALL CAPTURE_STORAGEMGMT_INFO(2, ‘ ‘, ‘DATA_SPACE’)
SQL0443N Routine “SYSPROC.CAPTURE_STORAGEMGMT_INFO” (specific name “CAPT_STGMGMT_INF”) has returned an error SQLSTATE with diagnostic text “SQL0303”. SQLSTATE=38553

SQL0303N A value cannot be assigned to a host variable in the SELECT, VALUES, or FETCH statement because the data types are not compatible.

Looks like data type incompatibility. I tried dropping all SGMT_* tables, recreate them using CREATE_STORAGEMGMT_TABLES and make a snapshot. Same error.

I’ve googled everything related to this in the Internet. There are only two topics without any suggestions here and here.

It seems like this problem has no solution (IBM DB2 bug?) and we should just go with it.

Rollforward on a backup server to another db name

Sometimes it’s handy to restore a production database on a testing server to a particular point in time (PIT). This procedure involves several nuances.

First of all production databases usually have much more memory and larger buffer pools. If you have less memory (as in our case) on a testing server, then your database just won’t rollforward because it won’t be able to activate database which in its turn is due to inability to activate buffer pools. In this situation you also cannot alter buffer pools manually because you cannot connect to the database. Looks like a deadlock. Solution here is to use DB2_OVERRIDE_BPF registry variable with say 5000 pages. After a database restart all buffer pools will be of 5000 pages size. Then you will be able to alter your buffer pools. Don’t forget to unset DB2_OVERRIDE_BPF and restart after you finished all maintenance procedures. I do that inside a batch script:

db2set DB2_OVERRIDE_BPF=5000
db2stop force
db2start

db2cmd -w -c db2 -t -f db_rf_restore.db2 -z db_rf_restore.err

db2set DB2_OVERRIDE_BPF=
db2stop force
db2start

Actual restore is done inside db_rf_restore.db2 DB2 CLP script. Important point here is to use -w flag which will wait for script completion before moving on to the next command. And -c which will automatically close Command Window after script has been run. Otherwise you will need to do it by hand.

Another matter here is how to rollforward a database if you changed its name and moved it to another server. To accomplish that you will need to copy archival (don’t confuse them with active) logs from a production server to your testing server and use OVERFLOW LOG PATH (“D:\backup\tlogs”) clause in ROLLFORWARD DATABASE command to point to directory where you copied them to. Use also AND COMPLETE clause to finish rollforward process and turn off rollforward pending state.

Here is the complete restore script:

DROP DATABASE db_2;

CREATE DATABASE db_2 ON D:;

RESTORE DATABASE db
FROM “D:\backup”
TO D:
INTO db_2
WITH 2 BUFFERS
BUFFER 1024
PARALLELISM 1
COMPRLIB C:\SQLLIB\BIN\db2compr.dll
WITHOUT PROMPTING;

ROLLFORWARD DATABASE db_2
TO 2012-02-16-12.30.00.000000
AND COMPLETE
OVERFLOW LOG PATH (“D:\backup\tlogs”);

CONNECT TO db_2;
ALTER BUFFERPOOL data_pool SIZE 75000;
CONNECT RESET;

DB2 notifications

Notifications are used in two parts of DB2 – Health Center and Task Center. Configuration is pretty simple. Go to Control Center – Task Center – Tools – Contacts and click on SMTP Server button. Fill here the FQDN of your mail server.  Then add contacts. After that you can use them during scheduled task configuration on Notifications tab.

To configure Health Center notifications go to Control Center – Health Center – Health Center – Configure – Alert Notification. In Configure Health Alert Notification window select instance and add contacts to Health Notification Contact List.

It may look like this is it. However, there is one issue we ran into. Sometimes DB2 sends emails using just a host name, without the domain part, like db2admin@sqldb2. All modern servers won’t allow sender address not in FQDN format. So we had to add DB2 server IP into white list on our Postfix mail server using check_client_access parameter.

DB2 Monitor Heap Utilization alert

Several times a week we receive alerts from DB2 Health Center concerning utilization of heap memory consumed by Health Monitor. Here is the message from logs:

ADM10500E  Health indicator “Monitor Heap Utilization” (“db2.mon_heap_util”) breached the “upper” alarm threshold of “95 %” with value “200 %” on “instance” “DB2”.  Calculation: “((db2.mon_heap_cur_size/db2.mon_heap_max_size)*100);” = “((655360 / 327680) * 100)” = “200 %”.  History (Timestamp, Value, Formula): “()”

Even though MON_HEAP_SZ parameter of our DB2 instance is configured to be managed automatically alert still shows up. As it turned out it’s a minor DB2 bug. Which is described here. Short explanation of this bug follows:

Health Monitor is sending Alert to db2 user while Monitor Heap is set to Automatic.

So if in configuration parameters you see “AUTOMATIC” in Value column for MON_HEAP_SZ then you can safely ignore this alert. However, it’s not a good idea to have unresolved issues in Health Center. Besides, it’s rather annoying since it shows up quite regularly and bring our attention to it. The solution for this problem is to switch off monitoring of monitor these parameters by:

db2 update alert cfg for database manager using db2.mon_heap_util set THRESHOLDSCHECKED NO
db2 update alert cfg for databases using db.db_heap_util set THRESHOLDSCHECKED NO

What’s peculiar about this error is that according to IBM it was fixed in 9.5 and we work on 9.7 (regression bug?). On top of that, there is another bug JR31509 described here which is connected with the previous one. Short explanation:

The health indicator calculates the alarm threshold using db2.mon_heap_max_size, but this max size may not be increased even though the MON_HEAP_SZ is configured as AUTOMATIC.

That basically means that AUTOMATIC may not work properly and we might have memory issues in future. But I’ll keep my fingers crossed.

DB2 transaction logging

By default DB2 database uses circular logging. That means you have fixed number of log files which are used circularly. And each time previous file is full next file is erased and reused. It is normal for data warehousing or OLAP where you have fixed data set and only select data from it. In case of failure there are specific restore procedures which simply involves loading of this fixed data set back again. If you work on an OLTP database you have to use archival logging. Otherwise you won’t be able to restore your database to particular point in time if failure occurs. To configure archival logging you need to change several database configuration parameters:

• LOGRETAIN – change it to Recovery if you want to switch to archival logging
• LOGARCHMETH1 – point to directory where your log files will be kept

Some other useful parameters:

• LOGPATH – points to your so called “active logs”. Database use them for immediate recovery needs and they aren’t meant to be used for roll-forward recovery.
• LOGPRIMARY – number of log files that will be used for active logs
• LOGFILSIZ – size of each log file

You can find more information here in detail and here in short.

Increasing DB2 buffer pools

Just a small tip on DB2 memory allocation. It’s very well described in a number of articles, like this or in IBM DB2 official guide on Troubleshooting and Tuning Database Performance. What I want to describe here is how to increase buffer pools, probably one of the most important tuning parameters and very basic at the same time. The issue you can run into is when you increase buffer pool size you get an error SQL20189W:

The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory.

It is not just a warning which suggests you to reboot. In fact, after a reboot your buffer pools won’t activate due to insufficient memory and database will work using small system buffers which will drastically decrease performance.

The reason why it happens is global memory cap which is configured in instance Configuration Parameters and called INSTANCE_MEMORY. It’s a total amount of memory which this instance can use for its operations. In order to have bigger buffer pools you must also increase this parameter. After that, SQL20189W goes away and you can tweak buffer pool memory on-the-fly. To check that change has happened use:

db2mtrk -d -v

and look for the line like

Buffer Pool Heap (1) is of size 3343450112 bytes

Changing hostname of the DB2 server

Recently we’ve migrated our DB2 instance to a new server. This involved moving domain name of the old server to the new one. We didn’t change the name of the new server, but just made an alias. After that we changed domain name of the old server because we wanted to leave a backup ground in case something happens. However, after reboot old database refused to start. I found good article here on IBM site, but I couldn’t run any DB2 command from CLI, they stopped working all at once.

The tip here is to go to HKLM\ SOFTWARE\ IBM\ DB2\ GLOBAL_PROFILE and HKLM\ SOFTWARE\ IBM\ DB2\ InstalledCopies\ DB2COPY1\ GLOBAL_PROFILE branches of the registry and change DB2_ADMINGROUP, DB2_USERSGROUP and DB2SYSTEM accordingly. After restart I was able to run DB2 CLI commands.

Then I stick to the IBM article. Particularly I ran the following commands:

db2 uncatalog node SQL
db2 catalog admin tcpip node OLDSQL remote OLDSQL system OLDSQL
db2 update admin cfg using DB2SYSTEM OLDSQL
db2 update admin cfg using SMTP_SERVER OLDSQL

This, along with two reboots, solved the problem.

Migrating IBM DB2 from 32 to 64-bit platform

The best way to move your database from one server to another is a backup/restore procedure. You can also use db2move utility but it’s not much of help here because it moves only the tables.

If you use a built-in compression to reduce size of your backups which is a normal thing to do then if you’ll try to restore a backup made on a 32-bit architecture to a 64-bit platform using a command like this

RESTORE DATABASE mydb FROM “D:\dbbackup” TAKEN AT 20111215030019 TO “D:” WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT ROLLING FORWARD WITHOUT PROMPTING;

then you will get an error

SQL2570N  An attempt to restore on target OS “NT-64” from a backup created on source OS “NT-32” failed due to the incompatibility of operating systems or an incorrect specification of the restore command.  Reason-code: “2”.

The reason why this happens is a compression library. Each time you make a compressed backup DB2 puts a compression library into a backup itself. When restoring on a 64-bit platform DB2 refuses to use a 32-bit library. There are two solutions to this problem. First is to make a plane uncompressed backup. But if your backup file is quite large then it can be rather painful to move it between servers. Second solution is to add COMRLIB clause into the original query

RESTORE DATABASE mydb FROM “D:\dbbackup” TAKEN AT 20111215030019 TO “D:” WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 COMPRLIB C:\SQLLIB\BIN\db2compr.dll WITHOUT ROLLING FORWARD WITHOUT PROMPTING;

If you restore to an existing database you will get SQL2539 warning. Which just means that original database files will be deleted.

Use this workaround description at IBM site as a reference: IY71307: SQL2570N when restoring a compressed backup image to another platform or wordsize.

DB2 advanced concepts

Now lets look at some advanced level terms of DB2 product. First comes Buffer Pool which is an extremely useful object for tuning DB2 performance. In fact Buffer Pool is just a region of memory where database keeps indexes and data. Initially pools are small. So it’s a rule of thumb to always tune them during database installation.

Next one is Transaction Logs. Transaction Logs are of big importance to any database. They keep track of transactions happening in database and in case of failure allow to “roll-forward” to particular point in time. In other words if you have full database backup made at T0 and transaction logs from that point and something bad happens at T1 then you can always recover your database to any point in time between T0 and T1 by recovering from full backup and then roll-forwarding.

Large Objects or LOBs are objects like video, audio, scanned documents, etc. It’s a type which is available during table creation. One special thing about LOBs is that they are logically created as a column of table which is stored in particular tablespace but are physically located in separate so called large object tablespace.