Systems Engineering and RDBMS

Re-naming a table in DB2 LUW

Posted by decipherinfosys on August 21, 2007

In our previous posts, we had covered re-naming of objects in SQL Server and re-naming of tables and columns in Oracle. In this post, we will cover how one can re-name a table in DB2 LUW. In DB2, there is no straight way to rename the column. One has to drop and re-create the table with new column name. We can rename the table though. Let us create a table for our testing purposes:

CREATE TABLE NEW
(
ID INT NOT NULL,
NAME VARCHAR(10),
CONSTRAINT PK_NEW PRIMARY KEY(ID)
);

Now rename it using following command.

RENAME TABLE new TO new_test;

Rename table command will give an error, if there are any dependent objects like view or trigger exists. This is different behavior than Oracle and MS SQL Server and is infact a better approach rather than allowing the re-name and marking the dependent objects asa invalid.

According to IBM documentation, one can change column name, its nullability property, its data type etc. using ALTOBJ() procedure provided in SYSPROC schema. For details on how to use ALTOBJ() stored procedure, please visit following link in IBM Infocenter.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0011934.htm

Sorry, the comment form is closed at this time.

 
%d bloggers like this: