473,372 Members | 1,022 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

why the table create in different tablespace

There is one thing I don't understand, I have default tablespace
userspace1 is database managed type, with total freepages 506048.
another one called tmpxx is DMS type too, with 525120 free pages. When
I create a new table (did not specify on which tablespace), why the
table not created on the userspace1 but instead on tmpxx? what is the
rule to create a table on tablespace without specify in which
tablespace

if I have both tablespace set as SMS, I don't have this problem.

Is this related to different tablespace?

Thank you
Nov 12 '05 #1
1 4997
xixi <da****@yahoo.com> wrote:
There is one thing I don't understand, I have default tablespace
userspace1 is database managed type, with total freepages 506048.
another one called tmpxx is DMS type too, with 525120 free pages. When
I create a new table (did not specify on which tablespace), why the
table not created on the userspace1 but instead on tmpxx? what is the
rule to create a table on tablespace without specify in which
tablespace

if I have both tablespace set as SMS, I don't have this problem.

Is this related to different tablespace?


Have a look at the documentation for the CREATE TABLE statement. There it
says which tablespace is used if you did not explicitily specify one:

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)

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.

So you should look at the definition of your tablespaces. If they are
defined in the same way, then you have most probably the case that the
"final choice is made by the database manager". I found sometimes that the
database manager chooses the tablespace that was created as the last one
(but that is by no means a rule used by DB2).

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: maricel | last post by:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel
2
by: Stanley Sinclair | last post by:
About to create a table which will "include" a BLOB. Am not sure how large to make the container and the tablespace. What I see says that BLOB is stored "separately." However, I don't know...
11
by: Hemant Shah | last post by:
Folks, I have a perl script that creates and uses global termporary table. This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the database to UDB 8 on Linux. The select statement...
3
by: randi_clausen | last post by:
I am trying to resize a DMS tablespace container in a database. There is only one table in this tablespace and it is at 89% full currently. The command executes successfully but still the size...
3
by: sandip | last post by:
Hi, i need to change the tablespace of a table, ie. use another tablespace for the table and stop the use of the existing tablespace. Is there any way i can accomplish this? One solution might...
5
by: Christian Traber | last post by:
Hi! just playing with tablespaces... - moved a existing table to a new tablespace - tried to move it back to default tablespace (ALTER TABLE accounts SET TABLESPACE pg_default;) Got the...
1
by: Raja Shekar | last post by:
HI Every body , I would like to know whether is it mandatory to give Tablespace page size and Bufferpool page size equal..? i also heard like while creating tablespace if pages size of tablespace...
2
by: rAinDeEr | last post by:
Hi, I have Db2 9 installed in Windows. Am trying to create a table with the following DDL CREATE TABLE DB2N.T_CO ( a1 VARCHAR(100) NOT NULL, b1 VARCHAR(255), c1 VARCHAR(255), d1 ...
5
by: Troels Arvin | last post by:
Hello, Every so often, I'm asked to help people recover data from tables that were either dropped or where to much data was DELETEed. The complications related to restoring data are a problem....
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.