What data should I collect for a MEMORY issue with DB2 for Linux, Unix and Windows?

Answer

Disclaimer - DB2 Universal Database (all versions) is a very complex product with many different components interacting with one another. This being the case, it is not possible to outline all the diagnostic data collections that may be needed to resolve any/all problems that may arise. Each issue with DB2 is unique, and in most cases, there is an initial round of data that will provide a good overall view of all aspects of DB2's behavior in a given circumstance. While this may be enough to identify root cause, it is not uncommon for this initial round of data to indicate a problem in a specific area, and that a second round of data is required to dig deeper into that.

In addition to the diagnostic data outlined below, DB2 support will need to have a detailed description of the problem. They will ask many questions to get an overall picture of the problem seen. During this time, additional diagnostic data may be requested. Also, after reviewing the initial set of data, additional diagnostics may be required.

Due to this fact, it would be best if a PMR could be opened before collecting data, so the support analyst can get some details about the issue, and then perhaps tailor the initial data collection so as to minimize the chances of additional collections being required. However there still is no guarantee that additional data collections will not be required.

Must-gather for a MEMORY issue data collection:

Memory issues are defined as any issue where there are memory errors present, or the system is maxed out on memory usage, and/or paging frequently. This is by no means an exhaustive list of possible memory issues.

If at all possible, a PMR should be opened prior to gathering data so that a support analyst can discuss the symptoms and come up with a plan as to what data to collect. However there are a few that will most likely be needed.

If a memory issue is observed, then you should (as the instance owner) log in to the DB2 server itself, and execute the following command:

db2fodc -memory full

This command may take some time to complete, but it will gather information crucial to the root cause analysis. If there is more than one PHYSICAL node for this instance, please execute the command on each PHYSICAL node.

It will produce a directory called "FODC_Memory_XXXXXXX" in the db2dump (DIAGPATH) of the DB2 server/node, where XXXXXXX is the timestamp that the command was executed.

Once the command completes, the FODC information can be gathered using the db2support command as follows:

If the issue is specific to one database:

db2support . -d <dbname> -c -s

If for the whole instance:

db2support . -s

Again, if there is more than one PHYSICAL node, then the command should be executed on all PHYSICAL nodes.

Additional DB2 data that can be collected:

db2pd -inst -dbptnmem -alldbs -memsets -mempools > db2pd.mem.out
db2 get snapshot for database manager > snap.dbm.out
db2 get snapshot for database on <db-name> > snap.db.out
db2 get snapshot for applications on <db-name> > snap.app.out

db2 "SELECT varchar(memory_set_type, 20) as set_type, varchar(db_name, 20) as dbname, memory_set_used, memory_set_used_hwm, memory_set_committed FROM TABLE(
MON_GET_MEMORY_SET(NULL, CURRENT_SERVER, -2))" > memset.out

db2 "SELECT varchar(memory_set_type, 20) AS set_type, varchar(memory_pool_type,20) AS pool_type,
varchar(db_name, 20) AS dbname, memory_pool_used, memory_pool_used_hwm FROM TABLE(
MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, -2)) " > mempool.out

The next set of data is OS specific:

AIX :

svmon -P, svmon -G, svmon -U
vmstat -v, vmstat -p all
ps -kelf, ps aux
ipcs -am, lsps -a

Linux :

pmap
ipcs -am, ipcs -mu
ps -elf, top -b -n 1
free, /proc/meminfo

Solaris :

pmap -x, pmap -S, pmap -xs
ipcs -am
ps -elf, /usr/ucb/ps avwx
mdb -k, ::memstat
swap -s, swap -l

Sending in the data:

The collected data should be uploaded to the associated PMR. If there is no PMR open for the issue yet, then please open one first. Then once you have the PMR number, there are a couple of ways to upload the data.

Before uploading, please ensure that the data is named appropriately. If DB2 support has asked for data to be named a certain way, then please do so. If they have not, then please come up with a good naming scheme. For example, if you have multiple db2support.zip files from multiple machines (IE DPF environment with multiple physical nodes), then you can name them like:

db2support.<hostname>.zip

1. You can use a browser to upload the data. Go to

http://www.ecurep.ibm.com/app/upload

Then follow the directions there.

2. Command line FTP. You can go here:

http://www.ibm.com/support/pages/node/739283

For directions to upload data. Please be sure to follow the directions under "Prepare your data" to ensure that the upload and attachment to the PMR is successful, or there may be delays in support gaining access to it.