Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2015

    Unanswered: Reorg table issues.

    Hi All,

    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.

    "call sysproc.admin_cmd('reorg table table-name');"

    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
    SQLState: 01H52
    ErrorCode: -2217

    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.

    Thanks in Advance.

  2. #2
    Join Date
    Jul 2013
    Moscow, Russia
    Provided Answers: 55
    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.
    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

  3. #3
    Join Date
    Nov 2009
    Provided Answers: 1
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts