Moving tables between tablespaces

**=?ISO-8859-1?Q?Gregor_Kovac=28?=** · Aug 18 '08, 11:25 AM

Re: Moving tables between tablespaces

Hi!

Maybe this article an help you.

http://www.ibm.com/developerworks/db2/library/techarticle/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_tabl e(
schemaname,
tablename,
destination_tab lespace
)

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

**Serge Rielau** · Aug 18 '08, 11:35 AM

Re: Moving tables between tablespaces

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_tabl e(
schemaname,
tablename,
destination_tab lespace
)

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** · Aug 18 '08, 11:45 AM

Re: Moving tables between tablespaces

Serge Rielau wrote:

>admin_move_tab le(
> schemaname,
> tablename,
> destination_tab lespace
>)

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** · Aug 18 '08, 12:55 PM

Re: Moving tables between tablespaces

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** · Aug 18 '08, 02:35 PM

Re: Moving tables between tablespaces

"Serge Rielau" <srielau@ca.ibm .comwrote in message
news:6gt9grFhge pbU1@mid.indivi dual.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.SYSSEQUE NCES.LASTASSIGN VAL, which
is not exposed in DB2 UDB V8."

Does COPYSCHEMA(<TAR GETSCHEMA>,<TAR GETTABLESPACEIN FO>,<SOURCESCHE MA>) 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** · Aug 18 '08, 02:55 PM

Re: Moving tables between tablespaces

Mark A wrote:

"Serge Rielau" <srielau@ca.ibm .comwrote in message
news:6gt9grFhge pbU1@mid.indivi dual.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.SYSSEQUE NCES.LASTASSIGN VAL, which
is not exposed in DB2 UDB V8."
>
Does COPYSCHEMA(<TAR GETSCHEMA>,<TAR GETTABLESPACEIN FO>,<SOURCESCHE MA>) 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** · Aug 18 '08, 10:35 PM

Re: Moving tables between tablespaces

"Serge Rielau" <srielau@ca.ibm .comwrote in message
news:6gtgmeFhhi q1U1@mid.indivi dual.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_C OPY_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** · Aug 19 '08, 06:45 PM

Re: Moving tables between tablespaces

>>On 8/18/2008 at 4:34 PM, in message
<wJmqk.12227$XB 4.3006@bignews9 .bellsouth.net> , Mark A<xxxxxx@xxxxx. com>
wrote:

"Serge Rielau" <srielau@ca.ibm .comwrote in message
news:6gtgmeFhhi q1U1@mid.indivi dual.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_C OPY_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/forums/thread.jspa?threadID=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** · Aug 19 '08, 07:15 PM

Re: Moving tables between tablespaces

"Frank Swarbrick" <Frank.Swarbric k@efirstbank.co mwrote in message
news:48AABE72.6 F0F.0085.0@efir stbank.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/forums/thread.jspa?threadID=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.'||rtr im(SEQNAME)||' restart with ',
cast(NEXTCACHEF IRSTVALUE as int), ';' from syscat.sequence s where seqschema
= 'DB2INST1'" alter_seq.txt

db2 -tvf alter_seq.txt alter_seq.out

Moving tables between tablespaces

Moving tables between tablespaces

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment