SQL0286N A table space could not be found with a page size of at least 32768

24 September,2015 by Jack Vamvas

Question:  I got this error on  MYDB on MYSERVER: DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0286N A table space could not be found with a page size of at least "32768" that authorization ID "DB2USER" is authorized to use. SQLSTATE=42727

 

Answer:   You must be attempting to execute a CREATE TABLE statement where the requirement is for a pagesize greater that the tablespace pagesizes available.   You could verify this by connecting to the database and running the LIST TABLESPACES SHOW DETAIL command.

If you  need  to create a table with larger sized records  , such as the 32K tablespace recommended in your error message, you'll have to create some extra database objects.

1) Create a 32k bufferpool

2) Create a 32k pagesize  regular tablespace

3) Create a temp tablespace using the 32k pagesize

This is a sample script to complete these 3 steps. Customise according to your environment

 

 

CREATE BUFFERPOOL  MYDB_32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32K;
CREATE LARGE TABLESPACE MYDB_32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 PREFETCHSIZE 32  BUFFERPOOL  MYDB_32K;
CREATE SYSTEM TEMPORARY TABLESPACE  MYDBTEMP_32K PAGESIZE  32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL  MYDB_32K;



 Read More on DB2 tablespace management

How to check reclaimable space enabled on DB2 tablespace with ...

DB2 – How to check if autoresize is enabled - DBA DB2

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL0286N A table space could not be found with a page size of at least 32768

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright