How to copy table in db2 | db2look command with example

In DB2, you can create a duplicate table or copy an existing table to a new table  with data or without data in many ways.

One way is to use db2look command. Using db2look command, you can create a table structure from an existing table with check constraints,  default values and foreign keys, etc..

Let us start with the below example.

db2look -d sales -t ITEMMASTER  -e > item.ddl

 The above statement  generates   DDL Statements for the table ITEMMASTER in the file item.ddl

DDL statments includes create table,  primary key generation as given below

-- DDL Statements for Table "DB2INST1   "."ITEMMASTER"

CREATE TABLE "DB2INST1   "."ITEMMASTER"  (  "ITEMCODE" VARCHAR(6 OCTETS) NOT NULL ,   "DESCRIPTION" VARCHAR(25 OCTETS) )    IN "USERSPACE1"   ORGANIZE BY ROW; 

-- DDL Statements for Primary Key on Table "JEGAN   "."ITEMMASTER"

ALTER TABLE "DB2INST1   "."ITEMMASTER"  ADD PRIMARY KEY ("ITEMCODE");

Now you can create duplicate table in the same database or same table in another database.

To create a duplicate table, just open the item.ddl, and change the name of table name ITEMMASTER_DUP or give your own name.  Running  the below command creats a duplicate table.

db2 -tvf item.ddl

To copy data, run

  db2 "insert into itemmaster_dup  select * from itemmaster"

Using another way :

All the below statements are used to create a table structure from an existing tables in the same database  but with out constraints, column default values and foreign keys, etc..

db2 "create table itemmaster1 as (select * from itemmaster) with no data

db2 "create table itemmaster3 as (select * from itemmaster) definition only"

db2 "create table itemmaster4 like itemmaster"

To insert data, db2 "insert into itemmaster_dup  select * from itemmaster"

Using db2look command

     1. You can create a table structure from an existing table with check constraints,  default values and foreign keys, etc..
     2. You can copy structure from one database to another database eaven one machine to another machine in different platform.
     3. You can also transfer objects such as triggers, indexes, sequences, table spaces, buffer pools, among others to another database.
     4. No Database connection is required for invoking the db2look command.
     5. You can generate ddl for a single table or all tables in a database.

The basic syntax of the db2look command is as follows:

db2look -d <database-name> [option1] [option2] … [optionN][...]
where -d dbname - to mention data base name

Some of the options that can be used with db2look command are

-e Generates DDL statements for all database objects such as tables, views, indexes, triggers, primary key, referential, and check constraints, user-defined functions, and procedures.

-z schema-name  - to generate  for the specified schema name.

-t table-name - for only specified tables (maximum 30). Table names must be separated by a blank space.

-o file-name Writes the output to a file . If not specified, writes to standard output.

Now let us see how to move  all tables of a database from machine to another machine.

Eg : db2look -d employee -e
-generates DDL for all tables , views , etc for the employee database and displays on screen

db2look -d employee -e -o k.sql
-generates DDL for all tables , views , etc for the employee database and sends the output to k.sql file

 Copy k.sql file to the target matching and run the query with the following command
       
db2 -tvf k.sql

Now let us see how to move one table  from machine to another machine.

 You can also visit my  post,  How to move db2 tables with data across different platforms?