Question re: ReOrg

bunzo · #1 (**permalink**) 11-16-05, 15:26

Using DB2 UDB v7.2 under Windows Server 2000.

I have a database that contains 9 tables. I'm having performance problems when running queries on the primary data table. I shut down all applications that connect to this database and ran RunStats on all tables. I then ran a ReOrg on 8 of 9 tables. When selecting the ReOrg from within the Control Panel, I am prompted to select a table space and an index. Most tables would reorg successfully when I selected TempSpace1 and the index identified as the pk_index. Some failed with an SQL2216N and a -1218 message code. (I cannot find the explanation for this error code). If I rerun the reorg and do not identify an index, they run successfully.

Can anyone explain this?

I am running the RunStats on the primary data table now. It's been running for over 2hrs now and not completed yet. There are approximately 100+ million records in this table. Record length is listed as 51 bytes. 8 columns. I've run a ReOrg on this table before and it ran for 6.5hrs before coming back and telling me that the table data and the indexes are inconsistent.

n_i · #2 (**permalink**) 11-16-05, 15:46

Quote:

Originally Posted by bunzo

failed with an SQL2216N and a -1218 message code. (I cannot find the explanation for this error code).

Code:

$ db2 "? SQL1218N "


SQL1218N There are no pages currently available in bufferpool
          "<buffpool-num>".

Explanation:

All of the pages in the bufferpool are currently being used.  A
request to use another page failed.

 The statement cannot be processed.

User Response:

The bufferpool is not large enough to provide pages to all
database processes or threads at this time.  The bufferpool is
too small or there are too many active processes or threads.

 The statement may be successful if executed again.  If this
error occurs frequently, some or all of the following actions may
prevent further failures:

1.    increase the bufferpool size

2.    decrease the maximum number of database agents and/or
    connections

3.    decrease the maximum degree of parallelism

4.    decrease the prefetch size for table spaces that are in this
    bufferpool

5.    move some table spaces into other bufferpools.

 sqlcode :  -1218

 sqlstate :  57011

Internet Services

Web Development

Digital Marketing

Consumer Tech