For example: I get an error creating a table or accessing a table space.
create table tpcds.vikram
(
c1 int not null primary key,
tx_date date not null,
c2 char(10),
c3 timestamp(9) default current timestamp implicitly hidden,
c4 smallint default current member implicitly hidden
)
distribute by hash (c1)
partition by range (tx_date)
(starting ('1/1/1998') ENDING ('12/31/2030') every 1 month)
;
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039
And, I then do db2 list tablespaces or db2pd -d BLUDB -tablespaces command to find out the table space whose state is not good. Please note that db2 list tablespace will only show the table spaces on that database partition only.
So, that is why I like to use this SQL (by using -2), I get all partitions and find out the table space whose state is not normal.
$ db2 "select tbsp_id,
> varchar(tbsp_name, 30) as tbsp_name, DBPARTITIONNUM,
> varchar(tbsp_state, 40) as tbsp_state
> from table(mon_get_tablespace('',-2)) as t"
TBSP_ID TBSP_NAME DBPARTITIONNUM TBSP_STATE
-------------------- ------------------------------ -------------- ----------------
1 TEMPSPACE1 2 NORMAL
2 USERSPACE1 2 QUIESCED_SHARE
5 MONSPACE 2 NORMAL
7 OTHTS 2 NORMAL
8 TS_EDW_001D16 2 NORMAL
So, we now know that USERSPACE1 is in QUIESCED STATE. But, which object in this table space is a cause of this state.
$ db2 "select substr(t.tabschema,1,15) schema, substr(t.tabname,1,30) table, substr(tbsp_name, 1, 12) tbsp_name, substr(quiescer_auth_id,1,10) authid, quiescer_application_handle application_handle, quiescer_state state, dbpartitionnum, member from table(mon_get_tablespace_quiescer(-2)) q, syscat.tables t where t.tbspaceid = q.quiescer_ts_id and t.tableid = q.quiescer_obj_id order by dbpartitionnum" SCHEMA TABLE TBSP_NAME AUTHID APPLICATION_HANDLE STATE DBPARTITIONNUM MEMBER -------- -------------- ------------ ---------- ------------------ ----- -------------- ------ TPCDS CATALOG_SALES USERSPACE1 DB2INST1 0 SHARE 2 2
So, we now know that TPCDS.CATALOG_SALES table state is causing table space USERSPACE1 to be in QUIESCED SHARE state and this is in database partition number 2.
$ db2 terminate DB20000I The TERMINATE command completed successfully. $ db2 "SET CLIENT CONNECT_DBPARTITIONNUM 2" DB20000I The SET CLIENT command completed successfully. $ db2 connect to bludb Database Connection Information Database server = DB2/LINUXPPC64LE 11.1.9.0 SQL authorization ID = DBPEMON Local database alias = BLUDB $ db2 quiesce tablespaces for table tpcds.catalog_sales reset DB20000I The QUIESCE TABLESPACES command completed successfully.
Now, we have brought out this table out of quiesced mode, confirm with the SQL on MON_GET_TABLESPACE_QUIESCER again.
But, we also need to make sure that we revert back our connection to the coordinator node.
Please make sure that you login as same user who is holding the quiesce state which in this case was db2inst1.