5

I am trying to rename a table in db2 like so

rename table schema1.mytable to schema2.mytable

but getting the following error message:

the name "mytable" has the wrong number of qualifiers.. SQLCODE=-108,SQLSTATE=42601

what is the problem here.... I am using the exact syntax from IBM publib documentation.

Robert Lujo
  • 15,383
  • 5
  • 56
  • 73
brucezepplin
  • 9,202
  • 26
  • 76
  • 129

6 Answers6

9

You cannot change the schema of a given object. You have to recreate it.

There are severals ways to do that:

  • If you have only one table, you can export and import/load the table. If you use the IDX format, the DDL will be included in the generated file. If using another format, the table has be created.

  • You can recreate the table by using:

    Create table schema2.mytable like schema1.mytable

  • You can extract the DDL with the db2look tool

  • If you are changing the schema name for a schema given, you can use ADMIN_COPY_SCHEMA

These last two options only create the table structure, and you still need to import the data. After having create the table, you insert the data by different ways:

  • Inserting directly

    insert into schema2.mytable select * from schema1.mytable

  • Via load from cursor

  • Via a Load or import from file (The file exported in the previous step)

The problem is the foreign relations, because they have to be recreated.

Finally, you can create an alias. It is easier, and you do not have to deal with relations.

AngocA
  • 7,655
  • 6
  • 39
  • 55
  • hi thanks - thought there might be a command to simply move the table into the new schema, but your way of creating a new table in the other schema from the actual table, then inserting does the job for me. but more cumbersome but that's fine. – brucezepplin Nov 15 '13 at 15:42
  • 1
    There's also an `ADMIN_MOVE_TABLE` system stored procedure that does exactly what it says, including all dependencies. – mustaccio Nov 15 '13 at 15:46
  • @mustaccio I also thought ADMIN_MOVE_TABLE could do that, but the documentation does not have the option to provide a different schema: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0055069.html – AngocA Nov 15 '13 at 17:14
  • More information about how to move tables: http://www.ibm.com/developerworks/data/library/techarticle/dm-0907db2outages/ – AngocA Nov 15 '13 at 17:14
  • Stupid me.I'd take my comment back but it's too late. – mustaccio Nov 15 '13 at 18:23
7

You can easily rename a table with this statement:

RENAME TABLE SCHEMA.TABLENAME TO NEWTABLENAME;
Kampai
  • 22,848
  • 21
  • 95
  • 95
Pierre
  • 71
  • 1
  • 1
0

You're not renaming table in provided example, you're trying to move to different schema, it's not the same thing. Look into db2move tool for this.

Konstantin
  • 3,254
  • 15
  • 20
0

if you want to rename a table in the same schema, you can use like this.

RENAME TABLE schema.table_name TO "new_table_name";

Otherwise, you can use tools like DBeaver to rename or copy tables in a db2 db.

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Gayan Liyanage
  • 1
-1

What if you leave it as is and create an alias with the new name and schema.

Saurabh Agrawal
  • 1,355
  • 3
  • 17
  • 33
  • Unfortunately, this doesn't actually move the table, it just creates a pointer to it. This _can_ be useful in some situations, but will not help when the table actually needs to be moved. – Clockwork-Muse Apr 29 '14 at 13:56
-2

Renaming a table means to rename a table within same schema .To rename in other schema ,db2 call its ALIAS:

db2 create alias for

Tom123456
  • 77
  • 1
  • 9
  • 1
    Unfortunately, this doesn't actually move the table, it just creates a pointer to it. This can be useful in some situations, but will not help when the table actually needs to be moved. Also, this is identical to an existing answer. – Clockwork-Muse Apr 29 '14 at 13:56