How to check for DB2 Direct IO and FILE SYSTEM CACHING

Question : I’m running DB2 9.5 . How do I check the FILESYSTEM CACHING status on a DB2 tablespace? Answer: DB2 bufferpools and Linux filecache perform approximately the same function. Thefunction is : copy data from disk. DirectIO directs reads from disk into DB2 memory.DirectIO is set up on a tablespace by tablespace basis. The tablespace option is NO FILE SYSTEM CACHING Can be applied to: system tablespaces, temporary tablespaces Note: LOB and LONG VARCHAR doesn’t benefit – and continues to use file cache Check FILESYSTEM caching by db2 -v "get snapshot for tablespaces on myDB" | more Check for... Read more →

nmon - tuning and benchmark tool

Nmon is a tuner and benchmark tool on Linux. Capture disk, cpu, memory , filesystem, resource usage, virtual memory As a DB2 DBA it's a very effective method of capturing different types of in data in one interface. nmon is useful in operation with DB2 and Linux IO testing tools Notes 1) Download nmon from http://nmon.sourceforge.net/pmwiki.php 2) tar –xvf filename.tar.gz on the target server 3) Locate the correct binary , in my case – nmon_linux_x86_64 4) Use to check if nmon is running : ps -ef | grep nmon 5) Use the file output option to Save the data file... Read more →

ADM4000W and catalogcache_sz

The following message appeared in the db2diag.log ADM4000W A catalog cache overflow condition has occurred. There is no error but this indicates that the catalog cache has exceeded the configured maximum size. If this condition persists, you may want to adjust the CATALOGCACHE_SZ DB configuration parameter Notes on CATALOGCACHE_SZ 1) CATALOGCACHE_SZ is the Catalog cache size configuration parameter 2) Specifies in pages the max space usable from database heap 3) CATALOGCACHE_SZ allocation occurs from database shared memory 4) If increasing CATALOGCACHE_SZ allocation consider the consequences versus allocating the memory for other resources such as bufferpool 5) Large amounts of dynamic... Read more →

Querying Bufferpools DB2

When a DB2 database starts the total memory required for the bufferpools must be available. If this memory is not available when a database starts, the database manager will only start with system buffer pools (one for each page size) with a minimal size of 16 pages, and an SQL1478W (SQLSTATE01626) warning is returned List buffer pool information by querying the SYSCAT.BUFFERPOOLS system view. Querying SYSCAT.BUFFERPOOLS SELECT * FROM SYSCAT.BUFFERPOOLS BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE ESTORE NUMBLOCKPAGES BLOCKSIZE ------------ ------------ -------- ------ -------- ------ ------------- --------- IBMDEFAULTBP 1 - 1000 4096 N 0 0 1 record(s) selected. Related Posts DMS... Read more →

Analyzing a DB2 trap file

Usually when the DB2 instance ends unexpectedly, trap files are created .There is usually one trap file capturing the root cause . A number of other files are created, supporting data about DB2 processes. The dbdiag.log file indicates which files to analyze. By default , the files are created in “/sqllib/db2dump/FODC*”. A typical list of files are : 19880.000.core 19880.20.000.db2pd.alldbs.txt 19880.20.000.trap.txt db2eventlog.000.crash db2pd.dbptnmem.txt OSSNAPS sysctl.txt 19880.20.000.cos.txt 19880.20.000.rawstack.txt chkconfig.txt db2pd.dbmcfg.txt db2pd.memory.txt procmaps.txt top.txt In the DB2 instance db2diag.log , various messages may appear: 2011-11-10-10.42.24.462022+000 E38459E1556 LEVEL: Critical PID : 22233 TID : 183312050528 PROC : db2sysc INSTANCE: INSTANCE_HOME NODE : 000... Read more →