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

Migrating IBM DB2 from 32 to 64-bit platform 2

In my previous post I described how to restore IBM DB2 backup which was made on 32-bit architecture to a 64-bit server. It wasn’t wise to call it “migration”. In fact, problem is much more complicated than just restoring from backup. Here I’d like to describe all hidden rocks I ran into during migration.

As I’ve already described previously you will have to provide the path to 64-bit decompression library during restore. You can read about that in detail in the first part.

The second big issue is TOOLSDB database. In case you have scheduled jobs (and you certainly do) you’d like to restore them too. Well, if you have just few of them then it would probably be easier to recreate them manually. In my case it wasn’t. First of all you need to create an empty TOOLSDB. You can do that in Tools – Tools Settings – Scheduler Settings – Create New. You have to make schema name and database name equal to what you have on the original database. You can find these values either in Control Center or via db2 get admin cfg command. Look for TOOLSCAT_DB, TOOLSCAT_INST and TOOLSCAT_SCHEMA. If you screwed up, take a look at this article. As your second step you will need to restore TOOLSDB to your newly created empty database. And lastly you’ll need to copy C:\ ProgramData\ IBM\ DB2\ DB2COPY1\ DB2DAS00\ metadata\ db2contact file to the new server. Otherwise your tasks will work but you won’t be able to edit them. It’s a bug described here.

If you use the DB2 audit facility you’ll need to activate it on the freshly installed server. To read audit configuration use db2audit describe. This way you can compare old and new configurations. On the new server auditing will be switched off. To activate it run db2audit start.

Another issue is CPU affinity. The interesting thing is that by default our new DB2 installation worked on two cores (0 and 1). Since we have 4 core license (in other words 200 PVUs on Xeons) we wanted to use 4. Solution here is to use DB2PROCESSORS configuration variable. To spread the load on our two 4-core Xeons we used db2set DB2PROCESSORS=1,3,5,7.

If you have more memory on the new server you will probably want to increase buffer pools. It doesn’t require explanation.

There is also a bug in DB2 version 9.7.3 where stored procedures from modules SYSIBMADM.UTL_FILE, SYSIBMADM.UTL_DIR and maybe some others won’t work after 32 to 64 migration. Invocations will fall back with the error:

“SQL0471N  Invocation of routine “SYSIBMADM.UTL_DIR.CREATE_DIRECTORY” failed due to reason “1”.  SQLSTATE=55023″.

Explanation of the reason code is:

“The invocation failed because the SQL routine was created on a previous release of the product and the DBMS was not able to migrate it to the current release. The routine must be dropped and re-created to become operative again”.

To resolve this error you will have to update to 9.7.4 or higher. You can find detailed explanation is here.

One more issue I personally had is that DAS didn’t start upon reboot. I had to run service manually every time. Error I had in event log was:

rcmdTcpipListener Failed to configure listener’s socket address. Check for port (db2ccmsrv) in SERVICES file, rc = 118423736

Even though DB2 documentation says that db2ccmsrv is deprecated it somehow screwed up DAS. The solution I found was to add the following line in Windows services file: “db2ccmsrv 50100/tcp” and the problem went away.

If you have your own functions or stored procedures you will have to recreate them on 64-bit server. Otherwise they won’t work.

In regards to authentication DB2 has four custom super users groups: SYSADM, SYSCTRL, SYSMAINT and SYSMON. In our case we use them to grant super users rights to particular users using Active Directory groups. You can read values for these variables by db2 get dbm cfg. To configure these parameters run:

db2 update dbm cfg using sysadm_group DB2_ADM
db2 update dbm cfg using sysctrl_group DB2_CTRL
db2 update dbm cfg using sysmaint_group DB2_MNT
db2 update dbm cfg using sysmon_group DB2_ADM

This is it. I guess I didn’t forget anything important. As you can see it’s hellova nuances. I hope this post will help someone in their own migration.

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.