Solve DB2 “stuck” shared quiesce problems

Posted by David Yahalom under DB2 LUW

I knew this day was coming and I was just too afraid to admit it. After years of experiencing all kinds of strange Oracle problems (ORA-600 this, Ora-600 that) I thought moving on to a simpler architecture (DB2) would prove more weirdness-resistant. Actually, I knew all long this is not true but I liked the false sense of hope it gave me. -sigh- Anyway, I’m giving DB2 an official “pain in the ass” medal for the problems it caused me today. Oracle earned its fair share of these medals in the past too, so DB2 should feel just at home.

Today, one of our developers came to me saying that a DML operating she wrote is “hanging” and the table on which this operation was issued was non-responsive. Just as she walked into my room the “hang” released and we were able to query the problematic table again. We wrote some quick queries to verify all is well. Ten seconds after that I received a phone call from another developer saying that he is received a “SQL0290N Table space access is not allowed. SQLSTATE=55039″.

Okay, so something went bad with the Quiesce process in DB2?

Relaxed, I thought the problem is probably caused because the table is in load pending state or a quiesce state. So I quickly did a list tablespaces command and saw the tablespace #3 is indeed in quiesced shared mode.

The handbook procedure in such cases is to do a quiesce reset on any table from the problematic tablespace. Well, I tried that and guess what? Nada. While the command itself executed okay, the result was more of the same. Table space access is not allowed errors.

This seems like a phantom quiesce mode in DB2 (a wonderful architectural “feature” DB2 probably learned from Satan himself, nonetheless). In such a case, there are a few more trick to try like to try and reacquire the quiesce by the same user that caused it and then to reset it or to try a load terminate operation. Neither of these worked. Tablespace is still in quiesce mode. Maybe putting the tablespace in backup pending mode should do the trick? No such luck. You simply can’t because the table is already Quiesced shared.

The solution that worked for me, after doing a lot of guesswork (and I’ll put money that it will probably work for you in such situations as well) is:

1) Do a list tablespaces show detail command to find out the tablespace that is in QUIESCE SHARED mode.

Tablespace ID = 3
Name = PROD_TBS1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0001
Detailed explanation:
Quiesced: SHARE
Total pages = 2723840
Useable pages = 2723808
Used pages = 2181056
Free pages = 542752
High water mark (pages) = 2708288
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Number of quiescers = 1
Quiescer 1:
Tablespace ID = 3 <<--- This is the tablespace id that is in Quiece mode
Object ID = 12 <<--- This is the table inside the tablespace that is causing the QUIESCED SHARED

2) Run this query to find out which object is loking the tablespace in QUIECE SHARED mode:

SELECT SUBSTR(TAB1.DEFINER,1,15) AS DEFINER, SUBSTR(TAB2.TABSCHEMA,1,15) AS TABSCHEMA,SUBSTR(TAB1.TABNAME,1,15) AS TABNAME, SUBSTR(TAB2.TBSPACE,1,15) AS TBSPACE_NAME FROM SYSCAT.TABLES TAB1, SYSCAT.TABLESPACES TAB2 WHERE TABLEID=OBJECT_ID AND TAB2.TBSPACEID=TABLESPACE_ID AND TAB1.TBSPACEID=TAB2.TBSPACEID;

This is the result you will receive:

DEFINER TABSCHEMA TABNAME TBSPACE_NAME
--------------- --------------- --------------- ---------------
DB2INST9 TABSCM TABLE1 PROD_TBS1

There we have it. The problematic table name.

3) Now, lets try what we tried before, but this time, instead of just using a “quiesce table” command on a random table from the problematic tablespace, lets use it on the same table that is causing the problems.

db2 quiesce tablespaces for table TABSCM.TABLE1 share

Now we have re-quiesced the table back with the same locking level as it was originally quiesced with (shared).

3) And in order to get rid of the annoying quiesce do a:

db2 quiesce tablespaces for table TABSCM.TABLE1 reset

After this all is back to normal.

The reason I’m annoyed is because there is nothing online that suggests doing this. There are bits and bytes of info here and there but nothing concrete. Even IBMs solution for this is to simply to Re-Quiesce the tablespace (and not the table itself):

To remove a phantom quiesce:

1. 1 Connect to the database with the same user ID used when the quiesce mode was set.
2. Use the LIST TABLESPACES command to determine which table space is quiesced.
3. Re-quiesce the table space using the current quiesce

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002003.htm

Monitor locks in DB2

Posted by David Yahalom under DB2 LUW

Since locking in DB2 can be a real problem if not managed correctly, it is very important to keep a close eye on row and table locks.

I know of two easy ways to monitor locks.

The easiest one is simply to execute the following query from the DB2 CLI:

SELECT SUBSTR(appl_name, 1,15) as APPLICATION_WAITING,
SMALLINT(agent_id) as USER_WAITING_ID,
SUBSTR(authid,1,15) AS USER_WAITING,
SMALLINT(agent_id_holding_lk) AS USER_HOLDING_ID,
lock_mode AS HOLDING,
lock_object_type AS TYPE,
lock_mode_requested AS REQUESTED_LOCK_MODE
FROM SYSIBMADM.LOCKWAITS

Then use this to drill down to a specific lock for more details:

SELECT appl_status,
SUBSTR(PRIMARY_AUTH_ID,1,10) AS USER_HOLDING_LOCK,
SUBSTR(appl_name, 1,15) AS APP_HOLDING_LOCK,
SUBSTR(client_nname,1,20) AS CLIENT_HOLDING_LOCK
FROM SYSIBMADM.SNAPAPPL_INFO
WHERE AGENT_ID = <>>

Another way to monitor locks which will result in more detail displayed is:

1) Allow DB2 to monitor locks in the database:

db2 update monitor switches using LOCK on

2) Run the following command which will output the monitoring statistics to a text file of your choice:

db2 get snapshot for locks on database_name > /tmp/locks.txt

3) Keep in mind that there is a certain amount of overhead involved while using the monitor locks option, this is why I always turn the locking monitor off when it is not needed.

db2 reset monitor all

IBM migration toolkit

Posted by David Yahalom under DB2 LUW

I want to recommend a great free tool from IBM called the “IBM migration toolkit” (now avail. in V2 beta).

This GUI tool allows you to migrate data from Oracle, SQL and a varaity of other databases to IBM DB2 (both LUW and Z/OS). I found this very useful with my DB2 deployment and if you are working in a place where you need to transfer data from Oracle/SQL server to DB2, try this tool fist before going to higher-end (pricey) solutions as it might fit your needs.

“The IBM Migration Toolkit is an easy-to-use tool that allows you to migrate your data from a wide variety of source databases to either DB2 or Informix Dynamic Server, regardless of platform.”