Solved

Is there a way to change table schema w/o drop/recreate?

Posted on 2012-03-29
9
892 Views
Last Modified: 2012-08-13
Hi all,

Is there a way, tool, routine or such to change a table schema to a new schema? In the mainframe, we have if we want to change OLDNAME to NEWNAME we can code the following CATMAINT job:

CATMAINT UPDATE
SCHEMA SWITCH(OLDNAME, NEWNAME)

Is there any such utility for DB2 LUW?
0
Comment
Question by:Enyinnaya
9 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37783762
you can duplicate a schema but you can't rename it
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37783869
...change a table schema to a new schema?

AFAIK, the CATMAINT UPDATE SCHEMA SWITCH() renames a schema. Are you wanting to rename the schema that contains a table? Or are you wanting to change which schema a table is part of?

Tom
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37786567
Hi!

There is a command in LUW called db2move see the manual
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dm.doc%2Fdoc%2Fr0024482.html

I think this is the only option in LUW that I know of that does this.

Regards,
    Tomas Helgi
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 37786576
Hi!

Sorry db2move is used across databases. The procedure you are looking for is ADMIN_COPY_SCHEMA which does this within the db.

Regards,
    Tomas Helgi
0
 

Author Comment

by:Enyinnaya
ID: 37788371
Tomas Helgi,

You got it right on the money! That ADMIN_COPY_SCHEMA is what I was looking for. And yes, does it with the same DB.

Thanks, Tom
0
 

Author Comment

by:Enyinnaya
ID: 37789325
I've requested that this question be closed as follows:

Accepted answer: 0 points for Enyinnaya's comment #37788371

for the following reason:

Good answer
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37789326
If my answer was good you  you should accept my comment  :)

Regards,
      Tomas Helgi
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
A while back when OPSMGR 2012 was released we were very excited about getting it into our environment and upgrading our 2007 implementation,  we started our planning and we then proceeded with our implementation. All went as planned & our system …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now