Narayan,
I had a similar problem. My solution was to create a table space called
IBMDEFAULTGROUP. I found the following in the DB2 V7.1 SQL Reference taken
from the CREATE TABLE syntax diagram (see below). As I read it, USERSPACE1
may not be the default. If you create a table space called
IBMDEFAULTGROUP, then it will be used first if one is not specified on the
CREATE TABLE statement. This solved the problem for us.
SQL Reference
--------------------------------
...
IN tablespace-name1
Identifies the table space in which the table will be created. The table
space must exist, and be a REGULAR table space over which the authorization
ID of the statement has USE privilege. If no other table space is
specified, then all table parts will be stored in this table space. This
clause cannot be specified when creating a subtable (SQLSTATE 42613), since
the table space is inherited from the root table of the table hierarchy. If
this clause is not specified, a table space for the table is determined as
follows:
IF table space IBMDEFAULTGROUP over which the user has USE privilege
exists with sufficient page size
THEN choose it
ELSE IF a table space over which the user has USE privilege
exists with sufficient page size
(see below when multiple table spaces qualify)
THEN choose it
ELSE issue an error (SQLSTATE 42727).
If more than one table space is identified by the ELSE IF condition, then
choose the table space with the smallest sufficient page size over which
the authorization ID of the statement has USE privilege. When more than one
table space qualifies, preference is given according to who was granted the
USE privilege:
1. the authorization ID
2. a group to which the authorization ID belongs
3. PUBLIC
If more than one table space still qualifies, the final choice is made by
the database manager.
Determination of the table space may change when:
- table spaces are dropped or created
- USE privileges are granted or revoked.
The sufficient page size of a table is determined by either the byte count
of the row or the number of columns. See Row Size for more information.
--------------------------------
Hope this helped!
Patrick Hans - Database Administration
State of Nebraska Department of Roads
Phone: 402.479.3987
E-mail: [EMAIL PROTECTED]
"Gandra, Narayan"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
m> cc:
Sent by: Subject: DB2EUG: create table
[EMAIL PROTECTED]
a.best.com
10/23/2001 09:53 AM
Please respond to
db2eug
Hello All,
When I issue the following command gettig the following error message:
db2 "create table test (col1 int)"
DB21034E The command was processed as an SQL statement because it was not
a
valid Command Line Processor command. During SQL processing it returned:
SQL0289N Unable to allocate new pages in table space "NGTS_100_PCT_4K".
SQLSTATE=57011
I know one of my above tablespace is 100% full, but when I issue the above
create table statement the table should be created in userspace1 tablespace
which is default and userspace1 tablespace has lot of space avialable to
create objects.
If I issue the following create table statement then working fine.
db2 "create table test (col1 int) in userspace1"
DB20000I The SQL command completed successfully.
If none of the tablespaces was full then working fine.
All Suggestions are welcome.
Thank you in advance!
-GNR
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see
http://people.mn.mediaone.net/scottrmcleod
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
