we need to change schema name of some tables on one of our databases (DB2 UDB on UNIX).

The only way I could think of is:

- export data from tables which should be changed
- drop those tables
- create new schema
- create tables with new schema
- create indexes with new schema
- import data to tables.

Is there any other way (shorter, easier, faster) to do it?
Thanks in advance

That is the way to do it. Although if you fully qualify the table names on the create table statements, you do not have to create the schema explicitly.

Andy

How about "CREATE ALIAS NEWSCHEMA.TABLENAME FOR OLDSCHEMA.TABLENAME"?

There is an administrative stored procedure (included with DB2) that will change the schema name for entire database, but I don't recall if you can specify indvidual tables. I have used it in DB2 9.5, and it works well except that you have reset any identity columns and sequences yourself, since they get reset to 1. I forgot the name of the SP, but if you look around you should be able to find it,

Yes, found it - it is ADMIN_COPY_SCHEMA - moves data between schemas within same DB. It looks perfect for my task but it is not present in v8.2 (only dbmove - moves data between databases).

Any ideas on how I can use db2move in my case?

Thanks in advance

You will just need to edit db2move.lst to replace the schema name in between "db2move ... export" and "db2move ... load"

the problem in this case is that database name will change - i am moving data from one db to another. The original task was to re-name schema.

Why do you think it is a problem?