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.