datatype varchar(8192)

krReddy · #1 (**permalink**) 05-10-07, 02:49

Hi all,

I got an error while executing the below query

Quote:

CREATE TABLE raj
(
team varchar (8192) ,
pm varchar (8192)
)

this is the error i got .

Quote:

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

If i change the varchar argument to a value <=4000 , it will be executed properly.
Please tell me the reason y its happening like that .

I feel this error is due to the maximum row size limitation in DB2.

krReddy · #2 (**permalink**) 05-10-07, 03:44

Please tell me what is the maximum row size limit in DB2 UDB 9

stolze · #3 (**permalink**) 05-10-07, 04:34

Each row of a table must fit onto a single data page. Therefore, if your rows can be potentially longer than 4005 bytes, you need a tablespace with a bigger page size for that table. The explanation of the error message gives some information in this regard:

Code:

$ db2 "? sql286"


SQL0286N A default table space could not be found with a
          pagesize of at least "<pagesize>" that authorization ID
          "<user-name>" is authorized to use.

Explanation:

 The CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE statement did
not specify a table space, and a table space of the right type
(USER TEMPORARY for declared temporary table) with sufficient
page size (at least "<pagesize>"), over which authorization ID
"<user-name>" has USE privilege, could not be found.

 Sufficient page size for a table is determined by either the
byte count of the row or the number of columns.

User Response:

 Ensure the existence of a table space of the correct type
(REGULAR or USER TEMPORARY) with a page size of at least
"<pagesize>" and that authorization ID "<user-name>" has USE
privilege on this table space.

 sqlcode :  -286

 sqlstate :  42727

You can find the maximum length of a row here in table 32: http://publib.boulder.ibm.com/infoce...c/r0001029.htm

krReddy · #4 (**permalink**) 05-10-07, 05:42

I came to know that by setting the default Table space value to a higher number (like "32768")
it is possible to execute the above query. If so please tell me how to set the table space
value