IBM Support

Accessing a tablespace returns SQL0290N "Table space access is not allowed" even no container errors

Technical Blog Post


Abstract

Accessing a tablespace returns SQL0290N "Table space access is not allowed" even no container errors

Body

 

Error SQL0290N may return when accessing a tablespace. For example,

db2 reorg table DB2INST1.TAB1
SQL0290N  Table space access is not allowed.  SQLSTATE=55039

In db2diag.log, the following messages are logged:

...
EDUID  : 108                 EDUNAME: db2agent (MYDB) 2
FUNCTION: DB2 UDB, base sys utilities, sqeAgent::AgentBreathingPoint, probe:16
CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
 [0] 0x00007F2FE024E6D5 _ZN8sqeAgent19AgentBreathingPointEi + 0x4D5
 [1] 0x00007F2FE2324B49 _Z23sqlpRefillAgentLRBCacheP9sqeBsuEduP9SQLP_DBCBP8SQLP_LCBP9SQLP_LTRNPmbh + 0x1429
 [2] 0x00007F2FE23216DE _Z7sqlplrqP9sqeBsuEduP14SQLP_LOCK_INFO + 0x5CE
 [3] 0x00007F2FDCAD5DC7 _Z22sqlbgbGetPagePLock_IUOPP8SQLB_BPDP11SQLB_FIX_CBPmPbm + 0x2B7
 [4] 0x00007F2FDCB0C2A3 _Z19sqlbGetPageFromDiskP11SQLB_FIX_CBi + 0x1E03
 [5] 0x00007F2FDC95CC42 _Z7sqlbfixP11SQLB_FIX_CB + 0x902
 [6] 0x00007F2FDCBE2BB5 _Z16sqlbDMSStartPoolP12SQLB_GLOBALSP12SQLB_POOL_CBb + 0x10D5
 [7] 0x00007F2FDC96873A _Z14sqlbStartPoolsP12SQLB_GLOBALS + 0x7DA
 [8] 0x00007F2FDCAA1723 sqlbinit + 0x2235
 [9] 0x00007F2FE035CBB5 _Z8sqledintP8sqeAgentP16sqeLocalDatabaseP5sqlcacPciPbm + 0x1195
 [10] 0x00007F2FE035392D _ZN16sqeLocalDatabase12FirstConnectEP8SQLE_BWARcP8sqeAgentP8sqlo_gmtiiPb + 0x52AD
 [11] 0x00007F2FE03344DD _ZN8sqeDBMgr23StartUsingLocalDatabaseEP8SQLE_BWAP8sqeAgentRccP8sqlo_gmtPb + 0xF5D

...


EDUID  : 108                 EDUNAME: db2agent (MYDB) 2
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSStartPool, probe:4303
MESSAGE : ZRC=0x80100003=-2146435069=SQLP_LINT "Interrupt from application"
         DIA8003C The interrupt has been received.
DATA #1 : <preformatted>
Tablespace 100 (TBS1)

...
EDUID  : 108                 EDUNAME: db2agent (MYDB) 2
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPoolsErrorHandling, probe:4151

MESSAGE : ADM6081W The table space "TBS1" (ID "100") is in the OFFLINE
         state and is not accessible. The table space state is "0x00004000".
         Refer to the documentation for SQLCODE -293.

The message above suggests the first application that activate the db locally on member 2

disconnects immaturely before the database is activated. And as a result, sqlbDMSStartPool

received interruption and tablespace TBS1 went offline due to the interruption.

 

Note in pureScale or DPF environment, it may show the state of the tablespace normal in some members (partitions) but offline on others members.

You can use the following to verify the state of tablespaces :

SELECT TBSP_ID, varchar(tbsp_name, 30) as tbsp_name, member, tbsp_type, TBSP_STATE FROM TABLE(MON_GET_TABLESPACE(null,-2)) AS t where TBSP_STATE != 'NORMAL'

TBSP_ID       TBSP_NAME           MEMBER TBSP_TYPE TBSP_STATE                                                                                                      

-------------------- ------------------------------ ------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         100 TBS1              2 DMS    OFFLINE                                                                                                                                                                                                   

 1 record(s) selected.

To resolve the problem, in db2diag.log verify the tablespace containers have no issue and then  put

the tablespace back online by "db2 ALTER TABLESPACE <name> SWITCH ONLINE".

 

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13285807