I am new to DB2. Recently I had to alter the datatype of one of the columns of my table CERTIFICATE_TAB. Post this, I had to reorg the table.
I used the following query, which is working perfectly fine for other tables.
However while reorganising my table CERTIFICATE_TAB i get the following error:
Error: DB2 SQL Error: SQLCODE=-2217, SQLSTATE=01H52, SQLERRMC=1, DRIVER=3.50.152
which is the error which comes when the page size of table exceeds the system tablespace (as per ibm documnetation)
However I am unable to create a tablespace to be used for reorg. Could you please suggest some SQL query for creating table space and then reorg using it.
Alternatively, I can delete the entire data and then reorg, but till I reorg my table, i am unable to delete the data. and getting -668 error code.
The following query returns the statement which you can run to create a system temporary tablespace with an appropriate page size.
I assume that the full table name to reorg is TABLE-SCHEMA.TABLE-NAME here (use capital letters).
Issue COMMIT statement afterwards. Then try to run your reorg statement.
select 'CREATE SYSTEM TEMPORARY TABLESPACE TMPSYS_'||RTRIM(CHAR(t.PAGESIZE/1024))||'K PAGESIZE '||RTRIM(CHAR(t.PAGESIZE))
from syscat.datapartitions p
join syscat.tablespaces t on p.tbspaceid=t.tbspaceid
where p.tabschema='TABLE-SCHEMA' and p.tabname='TABLE-NAME'
fetch first 1 row only
Hi.. since the column was altered.. the table is in reorg pending state ... do the following steps below...
1. Find the tablespace page size for this table CERTIFICATE_TAB where its located. This can be found using db2look or syscat.tablespaces. Commands below
db2look -d <dbname> -t CERTIFICATE_TAB -l (L in lower case) -e
this output should give you the tablespace create statement which should have the page size as well.
db2 "select pagesize,tbspace from syscat.tablespaces" this output should also tell you whats the pagesize is (usually 4k,8k,16k or 32k)
2. Once you identify the page size and see if you have temporary tablespace for that page size. If you identify pagesize as 16k (for example) db2 doesn't create temporary 16k tablespace by default.
so you have to create system temporary SMS tablespace for the appropriate page size where the table resides.
3. Once the system temp tablespace is created.. you need to specify in the reorg command. below
db2 reorg table <schemaname>.<tablename> use <temporary tablespace>
substitute appropriate values for the above command. This should fix the reorg pending issue.