DIA3208E Error encountered in TCP/IP protocol support

Question : I recently received a query from an application owner, who reported application outage issue. An inspection of the db2diag.log file found this message. How can I solve this problem? DIA3208E Error encountered in TCP/IP protocol support. TCP/IP function "close". Socket was "3". Errno was "9". Errno was "9". Answer: The DIA3208E Error encountered in TCP/IP protocol support message indicates multiple db2tcpcm processes. If the instance is stopping and there is an attempt to terminate the sockets simultaneously , only one can close without an error Option 1 - db2set DB2TCPCONNMGRS=1 Option 2 – Install DB2 version 9.5 FixPack... Read more →

Reduce the size of the DB2 LRG file

Question : A server is short on disk space . I created a list of the largest file, and the largest is C000000.LRG. What is a LRG file and how can I make it smaller? Answer: The LRG file is large tablespace container. To decrease the size of file : delete the data in the table , commit an offline REORG using the LONGLOBDATA option. An Inplace REORG will not work. db2 REORG TABLE APPL_LOG ALLOW NO ACCESS LONGLOBDATA Read More How to monitor a DB2 REORG Reorg table DB2 DB2 table reorg and table truncate DB2 Used Space and... Read more →

DB2 Total Sorts - overflows – improve performance

Sorts are part of processing queries , but excessive sorts , especially in an OLTP databases can kill performance. Sorts hit IO,CPU and lead to slow response time. A typical DB2 Sort occurs if a sql statement uses an ORDER BY but can’t find a relevant index. Sort Overflows represent the amount of sorts overflowing to disk . A high percentage could indicate performance issues. Increasing SORTHEAP is the quickest way of improving this situation , but is probably masking underlying problems. A more in depth approach will require an analysis of the sql code and supporting indexes. To obtain... Read more →

SQL20169W The buffer pool is not started

The DB2 message SQL20169W The buffer pool is not started appeared in response to a ALTER BUFFERPOOL statement db2 ALTER BUFFERPOOL ITMBUF8K IMMEDIATE size 250000 ---SQL20169W The buffer pool is not started. SQLSTATE=01654 To overcome this problem , disconnect all the applications connected to the database, and activate the database. There are a number of methods to disconnect all the applications , such as DB2 deactivate database Read More Database will come up with hidden buffer pools - DBA DB2 Querying Bufferpools DB2 - DBA DB2 DB2 SORTHEAP and pinned pages - DBA DB2 Read more →

DB2 block all connections to a database - svcename

Taking a OFFLINE DB2 backup requires shutdown of all database connections . Often you'll see the SQL1035N The database is currently in use mesage. There are a number of workarounds to achieve the outcome of starting the OFFLINE backup, such as : db2 force applications all --or QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS Sometimes the required outcome does not occur., particularly with very persistent connection attempts. One method of blocking connections to the DB2 database server instance is to change the SVCENAME configuration parameter. This forces the attempted connections to all databases on the instance to fail. If you don’t want... Read more →