Moving tables between tablespaces

#1

Hello,

How does a use efficiently move a table between tablespaces, given that
the user doesn't have shell access to the server hosting the database?

Is there some built-in routine that the user can execute? - Such as a
function with the following parameters:

admin_move_table(
schemaname,
tablename,
destination_tablespace
)

#2

Hi!

Maybe this article an help you.
http://www.ibm.com/developerworks/db...dm-0602rielau/

Best regards,
Kovi

Troels Arvin pravi:

Quote:

Hello,

How does a use efficiently move a table between tablespaces, given that
the user doesn't have shell access to the server hosting the database?

Is there some built-in routine that the user can execute? - Such as a
function with the following parameters:

admin_move_table(
schemaname,
tablename,
destination_tablespace
)

--
____________________________

Quote:

http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
In A World Without Fences Who Needs Gates? |
Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

#3

Hi!

Maybe this article an help you.
http://www.ibm.com/developerworks/db...dm-0602rielau/

Best regards,
Kovi

Troels Arvin pravi:

Quote:

Hello,

How does a use efficiently move a table between tablespaces, given that
the user doesn't have shell access to the server hosting the database?

Is there some built-in routine that the user can execute? - Such as a
function with the following parameters:

admin_move_table(
schemaname,
tablename,
destination_tablespace
)

--
____________________________

Quote:

http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
In A World Without Fences Who Needs Gates? |
Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

#4

Troels Arvin wrote:

Quote:

Hello,

How does a use efficiently move a table between tablespaces, given that
the user doesn't have shell access to the server hosting the database?

Is there some built-in routine that the user can execute? - Such as a
function with the following parameters:

admin_move_table(
schemaname,
tablename,
destination_tablespace
)
There is no such thing in DB2 for LUW.

The easiest way is to do a CREATE TABLE LIKE followed by a LOAD from
CURSOR, a DROP and finally a RENAME TABLE.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#5

Troels Arvin wrote:

Quote:

Hello,

How does a use efficiently move a table between tablespaces, given that
the user doesn't have shell access to the server hosting the database?

Is there some built-in routine that the user can execute? - Such as a
function with the following parameters:

admin_move_table(
schemaname,
tablename,
destination_tablespace
)
There is no such thing in DB2 for LUW.

The easiest way is to do a CREATE TABLE LIKE followed by a LOAD from
CURSOR, a DROP and finally a RENAME TABLE.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#6

Serge Rielau wrote:

Quote:

admin_move_table(
schemaname,
tablename,
destination_tablespace
)

There is no such thing in DB2 for LUW.

:-(

Quote:

The easiest way is to do a CREATE TABLE LIKE followed by a LOAD from
CURSOR, a DROP and finally a RENAME TABLE.

Yes, that's also what I told the user to do. However, this will mean
losing indexes, constraints, permissions, and triggers as far as I can see.

--
Troels

#7

Serge Rielau wrote:

Quote:

admin_move_table(
schemaname,
tablename,
destination_tablespace
)

There is no such thing in DB2 for LUW.

:-(

Quote:

The easiest way is to do a CREATE TABLE LIKE followed by a LOAD from
CURSOR, a DROP and finally a RENAME TABLE.

Yes, that's also what I told the user to do. However, this will mean
losing indexes, constraints, permissions, and triggers as far as I can see.

--
Troels

#8

Troels Arvin wrote:

Quote:

Yes, that's also what I told the user to do. However, this will mean
losing indexes, constraints, permissions, and triggers as far as I can see.
You can check out the article Kovi pointed to.

It deals with these guys.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#9

Troels Arvin wrote:

Quote:

Yes, that's also what I told the user to do. However, this will mean
losing indexes, constraints, permissions, and triggers as far as I can see.
You can check out the article Kovi pointed to.

It deals with these guys.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#10

"Serge Rielau" <srielau (AT) ca (DOT) ibm.com> wrote

Quote:

Troels Arvin wrote:
Yes, that's also what I told the user to do. However, this will mean
losing indexes, constraints, permissions, and triggers as far as I can
see.
You can check out the article Kovi pointed to.
It deals with these guys.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

I am a bit confused by the following statement in the article referenced:

"The composition of the DDL objects is entirely based on the documented
SYSCAT catalog views, with one exception. The high watermark for an IDENTITY
column needs to be retrieved from SYSIBM.SYSSEQUENCES.LASTASSIGNVAL, which
is not exposed in DB2 UDB V8."

Does COPYSCHEMA(<TARGETSCHEMA>,<TARGETTABLESPACEINFO>,< SOURCESCHEMA>) reset
the sequences and/or indentify columns to the same last assinged value as
the source schema? Does the COPYSCHEMA SP work differently in V9.5 versus
V8 in this regard?