The quick answer is that CREATE TABLE, if not given a tablespace, does not automatically place the table in USERSPACE1.
The slightly longer answer can be found here: http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7s0sqls0621.htm#HDRHCTABLA which boils down to 'if there are multiple tablespaces of the appropriate page size, all of which the user has authorization to use, then DB2 will arbitrarily choose one of them to use, starting with IBMDEFAULTGROUP if it exists' _____________________________________________ Dale M. McInnis IBM Toronto Lab Senior Technical Manager, DB2 UDB Backup, Data Protection and Recovery Email: [EMAIL PROTECTED] Phone: (905) 413-2397, Fax: (905) 413-4685, T/L: 969-2397 |--------+--------------------------------> | | Pierre Saint-Jacques | | | <[EMAIL PROTECTED]| | | > | | | Sent by: | | | [EMAIL PROTECTED]| | | a.best.com | | | | | | | | | 10/23/2001 12:58 PM | | | Please respond to | | | db2eug | | | | |--------+--------------------------------> >-----------------------------------------------------------------------------------------------------------| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: Re: DB2EUG: create table | | | | | >-----------------------------------------------------------------------------------------------------------| The reason for this is as follows: You have created the: NGTS_100_PCT_4K tablespace as the first tblsp. that has your id as a schema name. When you create a table with no tblsp. mentionned in the IN clause, it defaults to the first tblsp. that has your id as a shema name. If it is full then you get the error. If you issue: crete table...... in userspace1 and it is not full or there are enough pages to do so then the command is successful. With no IN clause, the table goes to userspace1 if a) you tell it to, b) You don't have any tbslps. in your schema name. HTH, Pierre. "Gandra, Narayan" wrote: > 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 #### sescons.vcf has been removed from this note on October 23 2001 by Dale McInnis ===== To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
