DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 ALTER TABLE ALTER COLUMN

Db2 ALTER TABLE ALTER COLUMN

Summary: in this tutorial, you’ll learn how to use the Db2 ALTER TABLE ALTER COLUMN statement to modify column definitions.

Introduction to Db2 ALTER TABLE ALTER COLUMN statement

Sometimes, you may want to modify column definition to cope with the new business requirements such as extend the maximum length of a column or changing the default value of a column to a new one.

Db2 ALTER TABLE ALTER COLUMN allows you to modify the definitions of the existing columns in a table.

The following illustrates the syntax of the ALTER TABLE ALTER COLUMN statement:

ALTER TABLE table_name ALTER COLUMN column_name modification_type;

In this syntax:

  • First, specify the name of the table which you want to perform the change in the ALTER TABLE clause.
  • Second, specify the name of the column that you want to modify.
  • Third, add a clause as a modification type. Db2 allows you to change the data type of a column, adjust the column length, and change the default value to a new one.

Db2 ALTER TABLE ALTER COLUMN examples

We’ll use the orders table created in the ALTER TABLE ADD COLUMN tutorial for the demonstration.

1) Modifying the length & data type of column examples

The following example uses the ALTER TABLE ALTER COLUMN statement to change the data type of the created_date column from DATE to TIMESTAMP:

ALTER TABLE orders ALTER COLUMN created_date SET DATA TYPE TIMESTAMP;

To view the columns of the orders table, you use the DESCRIBE TABLE command:

DESCRIBE TABLE orders;

Here is the output:

db2 alter table alter column - modify data type

This statement adds a new column named note to the orders table:

ALTER TABLE orders ADD COLUMN note VARCHAR(40);

To increase the length of the note column to 255, you use the following statement:

ALTER TABLE orders ALTER COLUMN note SET DATA TYPE VARCHAR(255);

2) Changing the default value to a new one example

To change the default value of a column to a new value, you use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT new_default_value

The following example sets the default value of the created_date column to the current timestamp:

ALTER TABLE orders ALTER COLUMN created_date SET DEFAULT CURRENT_TIMESTAMP;

In this tutorial, you’ve learned how to use the Db2 ALTER TABLE ALTER COLUMN statement to modify the definition of a column such as data type, length, and default value.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 ALTER TABLE ADD COLUMN
Next Db2 ALTER TABLE DROP COLUMN

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.