473,320 Members | 1,936 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,320 software developers and data experts.

Change a table's tablespace

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 be export the data from
the table, re-create the tables in the new tablespace, drop the
original tablespace and load the data. But i would prefer another
simple and less time-consuming way.

Any suggestions would be highly appreciated.

Thanks in advance,
Sandip.

Nov 12 '05 #1
3 21216
sandip wrote:
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 be export the data from
the table, re-create the tables in the new tablespace, drop the
original tablespace and load the data. But i would prefer another
simple and less time-consuming way.


You could create a new table in the other tablespace

CREATE TABLE <new-table> LIKE <old-table> IN <new-ts>

copy the data

INSERT INTO <new-nable> SELECT * FROM <old-table>

correct foreign keys to point to the new table and then remove the old
table.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena
Nov 12 '05 #2
sandip wrote:
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 be export the data from
the table, re-create the tables in the new tablespace, drop the
original tablespace and load the data. But i would prefer another
simple and less time-consuming way.

Any suggestions would be highly appreciated.

Thanks in advance,
Sandip.

You can do a "load from cursor". One way or another the data needs to move.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3
If the tablespace exists in the same instance, you can do it from
control center. Right click, copy table, enter new table/tablespace
name. then delete old and rename new table name to old table name.

Nov 12 '05 #4

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

Similar topics

1
by: Paul Hallam | last post by:
How does oracle distribute records in a table that is in a tablespace with multiple datafiles. Very simplisticly Ingres will distribute sequential records evenly across multiple datafiles for a...
1
by: xixi | last post by:
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....
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
6
by: robert | last post by:
just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan. hmmmm. next step up (or down, depending on your outlook)...
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...
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...
12
by: bikkaran | last post by:
Hi , I have a table that contains 15lakh records..... I want delete that table....and insert fresh set of record. when I run the command ...db2 "delete from schema.tabname" it hangs...
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....
6
by: shawno | last post by:
Hi, We're running DB2 v8.1 on a windows platform and have a database that is quite large. It basically contains one table with a BLOB field (each blob is a zip file, maybe 500K to 1MB), and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...

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.