I’m spoiled. While we build a fair number of environments each year, we also have basic starting standards. Because of this, I sometimes miss the basics when a problem shows up. Or at least it takes me longer to get there.
In this case, we had a couple of alerts over the high-volume weekend (Black Friday 2013). They were alerts from our connection monitor. We had done some tuning before the holiday, which included increasing MAXFILOP. This database is largely SMS tablespaces and an older version of DB2 (and WCS 6). The alerts were transient – as soon as someone logged in to look at them, connections were working just fine. Looking in the db2 diag log on Monday morning, I saw a number of entries like this:
2013-12-02-09.49.35.996713-300 I634382E367 LEVEL: Severe PID : 10811 TID : 47251525193888PROC : db2agent (ESB19Q02) 0 INSTANCE: db2inst1 NODE : 000 DB : ESB19Q02 APPHDL : 0-1206 APPID: *LOCAL.db2inst1.133012144937 FUNCTION: DB2 UDB, base sys utilities, sqleserl, probe:10 RETCODE : ZRC=0xFFFFEC73=-5005 2013-12-02-09.49.35.996180-300 I632343E481 LEVEL: Error PID : 10811 TID : 47251525193888PROC : db2agent (ESB19Q02) 0 INSTANCE: db2inst1 NODE : 000 DB : ESB19Q02 APPHDL : 0-1206 APPID: *LOCAL.db2inst1.133012144937 FUNCTION: DB2 UDB, config/install, sqlf_read_db_and_verify, probe:30 MESSAGE : SQL5005: sqlf_openfile rc = DATA #1 : Hexdump, 4 bytes 0x00007FFFFEC5864C : 0600 0F85
One time, I actually managed to catch the error at the command line – it looked like this:
$ db2 connect to esb19q02 SQL5005C System Error.
In researching this, I found this helpful technote: http://www-01.ibm.com/support/docview.wss?uid=swg21403936
And while I first thought that I needed to increase MAXFILOP, I figured out that it was really the ulimit that was my problem:
$ ulimit -a ... open files (-n) 1024 ...
This particular instance had three databases on it, all with SMS tablespaces, and one with over a thousand tables. The settings for MAXFILOP for the three databases added up to 4096.
In order to increase the limit, I added the following lines to /etc/security/limits.conf, as root:
db2inst1 soft nofile 16192 db2inst1 hard nofile 16192
… where db2inst1 is my instance owner.
Modifying the ulimit as the instance owner itself did not work:
$ ulimit -n 16192 -bash: ulimit: open files: cannot modify limit: Operation not permitted
Unfortunately, these settings do not take effect until the next time the database manager is started (db2stop/db2start), so I had to schedule that outage. I could have also done it with a failover to avoid the actual outage.
To prevent the issue, MAXFILOP could actually be lowered across the databases, with the side effect of possibly decreasing database performance, but preventing an actual inability to connect.
With the modifications to make automatic storage tablespaces so easy to use, and the default, I see fewer and fewer databases making extensive use of SMS tablespaces.
I have come across this issue with one of our Mysql database’s , but coming to db2 doing the root installation will set the semaphores at the kernel level by itself which is not supported during very early version of db2 , i use to think some times why not db2 set this ulimit also nevertheless root having privilege to do so, but recognized that it has a db parameter MAXFILOP which is not a one time decision that can be taken by db2 as ir does for semaphores
Your blogs are very straight to topic and simple to understand … thanks for so much good knowledge sharing blogs.
Could you please come out with a blog in which we can get the details about best practice for configuration db2 databases. Things which need to be taken care as a DBA so that we can avoid future performance issue. e.g how to decide logfile size, number of logs, how much space need to allocate in bufferpools ( if we are not using Auto). What system parameters we need to look into e.g swap memory, ulimit value.
The thing is that’s a complicated question. Probably about 1/2 of my blog entries could apply in answering. I could write a book on it, and that would just cover the e-commerce databases that I’m most familiar with. I don’t have an answer because it is such a complicated question.
There are some awesome developerWork’s which you can refer to , here are some of them
Although these were tested of db2 v8.1 , these are basics which do not change from version to version drastically . So you can take them as reference .
And don’t miss the scott’s blog http://www.dbisoftware.com/blog/db2_performance.php
These things cover some basics what you are looking for .
Have a nice day