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