Technical Blog Post
Abstract
75 ways to demystify DB2 #60: Techtip : Hang of db2 uncatalog/catalog command
Body
Hello DB2 mates,
Hope all is going well for you.
In a multiple databases environment, we sometimes configure remote catalogue information for target databases like followings.
$ db2 catalog tcpip node <nodename> remote <IP or hostname> server <Port number>
$ db2 catalog db <dbname> as <db alias> at node <nodename>
And in some cases, we need to reconfigure this information.
For example, if you regularly change the port number of target system DB2 instance, the client catalog information should be changed as well.
But if you encounter a command hang situation with 'db2 uncatalog/catalog' command, you will be in the urgent situation with the need of this change promptly.
In this blog, I would introduce one popular situation of the command hang and how to resolve the hang of 'db2 uncatalog db' or 'db2 catalog db' command in this case?
One of the popular situation of 'db2 uncatalog/catalog' command hang is the file lock on the sqldbdir file.
When we do uncatalog/catalog, DB2 updates the information on file called 'sqldbdir' under '<DB2 instance directory>/sqllib/sqldbdir' directory.
And 'db2flacc' process is responsible for the work.
When the following commands are hung, check if there is the file lock on sqldbdir file.
$ db2 uncatalog db dbname
or
$ db2 catalog db dbname as db_alias_name at node nodename
See the output of 'lsof |grep sqldbdir' output. ( In AIX, you may need to install 'lsof' package. It's not installed by default.)
From the following example, 'asncap' process consumes the read lock on the file and 'db2flacc' is waiting for the file lock.
db2flacc 13238 db2inst1 3u REG 253,23 22312 82106 /home/db2inst1/sqllib/sqldbdir/sqldbdir
asncap 26653 db2inst1 3rR REG 253,23 22312 82106 /home/db2inst1/sqllib/sqldbdir/sqldbdir
From '3rR', the capital 'R' means for a read lock on the entire file.
To resolve this, stop the process when you can and make sure that no process consumes the file lock on 'sqldbdir' file.
Then try the commands again.
Usually the file lock does not happen even when some applications is using the catalog information.
But when you see this file lock and command hang, stopping the process that holds the file lock is the quickest way to escape from this situation.
Thank you.
UID
ibm11140022