Creating a table using LIKE

You can create a table that looks like another table. That is, you can create a table that includes all of the column definitions from an existing table.

The following definitions are copied:

  • Column names (and system column names)
  • Data type, length, precision, and scale
  • CCSID
  • FIELDPROC

If the LIKE clause immediately follows the table name and is not enclosed in parentheses, the following attributes are also included:

  • Column text (LABEL ON)
  • Column heading (LABEL ON)
  • Default value
  • Hidden attribute
  • Identity attribute
  • Nullability

If the specified table or view contains an identity column, you must specify the option INCLUDING IDENTITY on the CREATE TABLE statement if you want the identity column to exist in the new table. The default behavior for CREATE TABLE is EXCLUDING IDENTITY. There are similar options to include the default value, the hidden attribute, and the row change timestamp attribute. If the specified table or view is a non-SQL-created physical file or logical file, any non-SQL attributes are removed.

Create a table EMPLOYEE2 that includes all of the columns in EMPLOYEE:

CREATE TABLE EMPLOYEE2 LIKE EMPLOYEE