Moving tables between tablespaces

[Troels Arvin]

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
)

[=?ISO-8859-1?Q?Gregor_Kovac=28?=]

Hi!

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

Best regards,
Kovi

Troels Arvin pravi:

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
)

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

[Serge Rielau]

Troels Arvin wrote:

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

[Troels Arvin]

Serge Rielau wrote:

>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.

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

[Serge Rielau]

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

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:6g************@mid.individual.net...

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?

[Serge Rielau]

Mark A wrote:

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:6g************@mid.individual.net...

>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?

Yes.... in the same sense as the value is preserved by a
db2stop/db2start. The tool works the same on DB2 V8 and DB2 9.5. It's
just that the column in question wasn't exposed in DB2 V8.

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

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:6g************@mid.individual.net...

Yes.... in the same sense as the value is preserved by a db2stop/db2start.
The tool works the same on DB2 V8 and DB2 9.5. It's just that the column
in question wasn't exposed in DB2 V8.

Cheers
Serge

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

I tried using the stored procedure SYSPROC.ADMIN_COPY_SCHEMA to copy a
schema from one name to another in the same database.

1. The proc I executed above is a different stored procedure than mentioned
in the link from this thread. The one I used above comes with DB2 V9.5.

2. When I call the stored procedure, it copies the schema and data (if I use
COPY or COPYNO), but it does not set the identity column last value to the
same values as the source schema, so that upon the first insert into the new
schema there is a duplicate unique constraint error (-803).

[Frank Swarbrick]

>>On 8/18/2008 at 4:34 PM, in message
<wJ******************@bignews9.bellsouth.net>, Mark A<xx****@xxxxx.com>
wrote:

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:6g************@mid.individual.net...

>Yes.... in the same sense as the value is preserved by a

db2stop/db2start.

>The tool works the same on DB2 V8 and DB2 9.5. It's just that the column

>

>in question wasn't exposed in DB2 V8.

Cheers
Serge

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

I tried using the stored procedure SYSPROC.ADMIN_COPY_SCHEMA to copy a
schema from one name to another in the same database.

1. The proc I executed above is a different stored procedure than
mentioned
in the link from this thread. The one I used above comes with DB2 V9.5.

2. When I call the stored procedure, it copies the schema and data (if I
use
COPY or COPYNO), but it does not set the identity column last value to
the
same values as the source schema, so that upon the first insert into the
new
schema there is a duplicate unique constraint error (-803).

As far as I can tell this is "working as designed". However, it could use
some "design improvement" in my opinion. Here is a thread I participated in
in the DB2 Express forum:

http://www.ibm.com/developerworks/fo...214937&tstart=
30

Especially look at my post dated Jul 25, 2008 07:36:26 PM and the response
from Raul Chong of IBM on Jul 28, 2008 10:04:17 AM.

What do you think?

Frank

[Mark A]

"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:48******************@efirstbank.com...

As far as I can tell this is "working as designed". However, it could use
some "design improvement" in my opinion. Here is a thread I participated
in
in the DB2 Express forum:

http://www.ibm.com/developerworks/fo...214937&tstart=
30

Especially look at my post dated Jul 25, 2008 07:36:26 PM and the response
from Raul Chong of IBM on Jul 28, 2008 10:04:17 AM.

What do you think?

Frank

I agree that it is working as designed. When I first used that SP
(SYSPROC.ADMIN_COPY_SCHEMA) I just assumed it would start sequences and
identity columns at 1 (assuming they were initially defined that way) and I
wrote my own script to reset them to the correct values. But I am not sure
if my script below would work correctly on a database that is actively being
used (in my case it was not being used when the script ran, and I stopped
and started the instance before running it).

Note that after I copy the schema with the above stored proc, the original
schema is DB2INST1 and the new one is DB2INST2. After I reset the sequences,
I can then drop the objects in the old schema.

db2 connect to <dbanme>

db2 -x "select 'alter sequence DB2INST2.'||rtrim(SEQNAME)||' restart with ',
cast(NEXTCACHEFIRSTVALUE as int), ';' from syscat.sequences where seqschema
= 'DB2INST1'" alter_seq.txt

db2 -tvf alter_seq.txt alter_seq.out