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

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?

Mark A wrote:
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 wrote:
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

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

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

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

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

wrote:
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

wrote:
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

"Frank Swarbrick" <Frank.Swarbrick (AT) efirstbank (DOT) com> wrote


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

"Frank Swarbrick" <Frank.Swarbrick (AT) efirstbank (DOT) com> wrote


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

Now in DB2 9.7, you can move your tables between tablespaces, and the entire process is online.....



Troels Arvin wrote:

Moving tables between tablespaces
18-Aug-08

Hello,

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

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

admin_move_table(
schemaname,
tablename,
destination_tablespace
)

Previous Posts In This Thread:

On Monday, August 18, 2008 3:26 AM
Troels Arvin wrote:

Moving tables between tablespaces
Hello,

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

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

admin_move_table(
schemaname,
tablename,
destination_tablespace
)

On Monday, August 18, 2008 7:17 AM
Gregor_Kovac( wrote:

Hi!
Hi!

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

Best regards,
Kovi

Troels Arvin pravi:

--
____________________________

On Monday, August 18, 2008 7:29 AM
Serge Rielau wrote:

Troels Arvin wrote:There is no such thing in DB2 for LUW.
Troels Arvin wrote:
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

On Monday, August 18, 2008 7:42 AM
Troels Arvin wrote:

Serge Rielau wrote:Yes, that is also what I told the user to do.
Serge Rielau wrote:





Yes, that is 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

On Monday, August 18, 2008 8:50 AM
Serge Rielau wrote:

Troels Arvin wrote:You can check out the article Kovi pointed to.
Troels Arvin wrote:
You can check out the article Kovi pointed to.
It deals with these guys.

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

On Monday, August 18, 2008 10:28 AM
Mark A wrote:

I am a bit confused by the following statement in the article referenced:"The
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?

On Monday, August 18, 2008 10:53 AM
Serge Rielau wrote:

Mark A wrote:Yes....
Mark A wrote:
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 is
just that the column in question was not exposed in DB2 V8.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Monday, August 18, 2008 6:34 PM
Mark A wrote:

I tried using the stored procedure SYSPROC.
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).

On Tuesday, August 19, 2008 2:37 PM
Frank Swarbrick wrote:

wrote:As far as I can tell this is "working as designed".
wrote:


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

On Tuesday, August 19, 2008 3:05 PM
Mark A wrote:

I agree that it is working as designed. When I first used that SP(SYSPROC.
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


Submitted via EggHeadCafe - Software Developer Portal of Choice
Server Side Processing in ADO.NET/WCF Data Services
http://www.eggheadcafe.com/tutorials...essing-in.aspx