Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: change schema name

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How about "CREATE ALIAS NEWSCHEMA.TABLENAME FOR OLDSCHEMA.TABLENAME"?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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,
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA

    Any ideas on how I can use db2move in my case?
    You will just need to edit db2move.lst to replace the schema name in between "db2move ... export" and "db2move ... load"
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why do you think it is a problem?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •