Troubleshooting
Problem
Resolving a phantom quiesce and SQL0290N in DB2 in a partitioned database using table functions:
Symptom
A DB2 query or utility might fail with this error:
"SQL0290N Table space access is not allowed. SQLSTATE=55039"
db2pd -alldbpart -d sample -tablespaces| egrep -ip "Tablespace Statistics:"
Which shows:
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x07000000349AA940 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x07000000349B9B20 2 4096 4064 1824 0 2240 1824 1824 0x00000004 0 1 No n/a
0x07000000349C6CC0 3 4096 4092 80 0 4012 80 80 0x00000000 0 0 No n/a
0x07000000DEFB0080 4 4096 4092 328 0 3764 328 328 0x00000000 0 0 No n/a
$ db2tbst 0x00000004
State = Quiesced Exclusive
"SQL0290N Table space access is not allowed. SQLSTATE=55039"
And the underlying tablepsace is in a quiesced state as evidenced by this db2pd command:
db2pd -alldbpart -d sample -tablespaces| egrep -ip "Tablespace Statistics:"
Which shows:
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x07000000349AA940 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x07000000349B9B20 2 4096 4064 1824 0 2240 1824 1824 0x00000004 0 1 No n/a
0x07000000349C6CC0 3 4096 4092 80 0 4012 80 80 0x00000000 0 0 No n/a
0x07000000DEFB0080 4 4096 4092 328 0 3764 328 328 0x00000000 0 0 No n/a
$ db2tbst 0x00000004
State = Quiesced Exclusive
Cause
In this specific case the application connection that issued the: 'quiesce tablespaces for table <name> exclusive' command was closed or terminated before resetting the quiesce.
Diagnosing The Problem
If we look more closely we can see this quiesce doesn't have an owner:
This SQL, using the MON_GET_TABLESPACE_QUIESCER table function, provides the information
we need:
select DBPARTITIONNUM ,varchar(TBSP_NAME,30) as TBSP_NAME ,QUIESCER_TS_ID ,QUIESCER_OBJ_ID ,varchar(QUIESCER_AUTH_ID,30) as QUIESCER_AUTH_ID ,QUIESCER_APPLICATION_HANDLE ,QUIESCER_STATE from TABLE(MON_GET_TABLESPACE_QUIESCER(-2)) ORDER BY DBPARTITIONNUM
;
It returns the following:
DBPARTITIONNUM TBSP_NAME QUIESCER_TS_ID QUIESCER_OBJ_ID
-------------- ---------- -------------------- --------------------
2 USERSPACE1 2 19
..continued on next line:
QUIESCER_AUTH_ID QUIESCER_APPLICATION_HANDLE QUIESCER_STATE
---------------- --------------------------- --------------
DB2INST1 0 EXCLUSIVE
The above output tells us that someone or something as AUTHID: DB2INST1 issued a: 'quiesce tablespaces for a table' but that connection has subsequently disconnected before tidying up.
We can also determine the database partition number too, in this case 2
What it doesn't tell us is the table name. For that we will need the following SQL, taking the: QUIESCER_TS_ID and QUIESCER_OBJ_ID from the previous query, i.e 2 and 19 respectively:
select varchar(tabschema,20) as schema ,varchar(tabname,50) as table from syscat.tables where TBSPACEID=2 and TABLEID=19
Which shows:
SCHEMA TABLE
-------------------- --------------
TESTSCHEMA TEST
This completes the picture; now we know the quiesce was issued on partition 2 by DB2INST1 against the: TESTSCHEMA.TEST table.
This SQL, using the MON_GET_TABLESPACE_QUIESCER table function, provides the information
we need:
select DBPARTITIONNUM ,varchar(TBSP_NAME,30) as TBSP_NAME ,QUIESCER_TS_ID ,QUIESCER_OBJ_ID ,varchar(QUIESCER_AUTH_ID,30) as QUIESCER_AUTH_ID ,QUIESCER_APPLICATION_HANDLE ,QUIESCER_STATE from TABLE(MON_GET_TABLESPACE_QUIESCER(-2)) ORDER BY DBPARTITIONNUM
;
It returns the following:
DBPARTITIONNUM TBSP_NAME QUIESCER_TS_ID QUIESCER_OBJ_ID
-------------- ---------- -------------------- --------------------
2 USERSPACE1 2 19
..continued on next line:
QUIESCER_AUTH_ID QUIESCER_APPLICATION_HANDLE QUIESCER_STATE
---------------- --------------------------- --------------
DB2INST1 0 EXCLUSIVE
The above output tells us that someone or something as AUTHID: DB2INST1 issued a: 'quiesce tablespaces for a table' but that connection has subsequently disconnected before tidying up.
We can also determine the database partition number too, in this case 2
What it doesn't tell us is the table name. For that we will need the following SQL, taking the: QUIESCER_TS_ID and QUIESCER_OBJ_ID from the previous query, i.e 2 and 19 respectively:
select varchar(tabschema,20) as schema ,varchar(tabname,50) as table from syscat.tables where TBSPACEID=2 and TABLEID=19
Which shows:
SCHEMA TABLE
-------------------- --------------
TESTSCHEMA TEST
This completes the picture; now we know the quiesce was issued on partition 2 by DB2INST1 against the: TESTSCHEMA.TEST table.
Resolving The Problem
If we simply connect and issue another quiesce it will be added to the list of quiescers.
Also, if we just issue a reset it won't necessarily remove this quiesce as it only works against the current session and partition.
Instead what we have to do is ensure we connect to the correct partition and reset from there, as follows:
1
-----------
2 <<==Denotes partition 2, which is correct in this case.
5) Lastly, issue the quiesce but this time with a reset:
db2 "quiesce tablespaces for table test reset"
6) Re-run the MON_GET_TABLESPACE_QUIESCER table function SQL. It should now return 0 rows.
7) You can also re-run:
db2pd -alldbpart -d sample -tablespaces| egrep -ip "Tablespace Statistics:"
And check the state field to ensure it is 0x00000000 across all the partitions.
Also, if we just issue a reset it won't necessarily remove this quiesce as it only works against the current session and partition.
Instead what we have to do is ensure we connect to the correct partition and reset from there, as follows:
1) Firstly, disconnect the current connection:
db2 connect reset and / or: db2 terminate
2) Export the DB2NODE variable to ensure we connect to partition 2:
export DB2NODE=2
3) Connect to the database:
db2 connect to sample
Note: Using the user who quiesced the tablespace(i.e. QUIESCER_AUTH_ID, in this case, it is db2inst1) connect the database, or else "quiesce ... reset" won't work. So, if the user is not instance user, you may have to connect to database like below:
db2 connect to sample user <quiescer_auth_ud> using <password>
4) Ensure you are on partition 2:
db2 "values (current dbpartitionnum)"
The expected output is:
1
-----------
2 <<==Denotes partition 2, which is correct in this case.
5) Lastly, issue the quiesce but this time with a reset:
db2 "quiesce tablespaces for table test reset"
6) Re-run the MON_GET_TABLESPACE_QUIESCER table function SQL. It should now return 0 rows.
7) You can also re-run:
db2pd -alldbpart -d sample -tablespaces| egrep -ip "Tablespace Statistics:"
And check the state field to ensure it is 0x00000000 across all the partitions.
Related Information
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - Lock-Latch","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5;11.1","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
28 May 2020
UID
swg21986441