Create Table using LIKE in DB2 for i

Create Table using LIKE in DB2 for i

We can create a table that looks like any other table already exists in the system i.e. the new table would have all the columns from an existing table.

So we will use LIKE keyword in SQL to achieve this and that will copy the following definitions from an existing table.

Long/Short(system) column names

Data type, length, precision, and scale

CCSID

FieldProc

Column text

Column heading

Default value

Hidden attribute

Identity attribute

Nullability

Example1:

Create a table PF2 that includes all of the columns in PF1:

CREATE TABLE PF2 LIKE PF1

Example2:

Create a table TABLE2 that includes all of the columns in TABLE11 including identity columns:

CREATE TABLE TABLE2 LIKE TABLE1 INCLUDING IDENTITY

if the specified table contains an identity column in it, then we must specify the option INCLUDING IDENTITY on the CREATE TABLE statement if we want that the identity column to be there in the new table created using LIKE keyword. We can check DSPPFD on new table from command line on IBM i green screen and check for the identity column attribute.

The default behavior for CREATE TABLE is EXCLUDING IDENTITY.