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?

Read More

How to get database size in db2

Many ways are there to find db size in db2. The easiest way to find the data base size is to run the following commands

I) db2 connect to dbname

   db2 call get_dbsize_info(?,?,?,-1),  where get_dbsize_info is the built-in stored procedure. 

In the above procedure, first 3 parameters are output parameters and the 4th parameter is the only input parameter.The value of output parameters of my data base (SAMPLE)  are returned as given below.

Parameter Name  : SNAPSHOTTIMESTAMP
Parameter Value : 2014-06-15-00.32.43.272000

Parameter Name  : DATABASESIZE
Parameter Value : 165322752

Parameter Name  : DATABASECAPACITY
Parameter Value : 404513460224

 Return Status = 0

      When you run the above procedure first time for any database, the table STMG_DBSIZE_INFO with one row  having the fields of  SNAPSHOT_TIMESTAMP, DB_SIZE, DB_CAPACITY is created and the value of output paramaeters are cached in the table.  Every time when you run the same procedure, the db size is not recalculated. When you run the same again within 30 minutes, the db size is returned from the cached value. When you run the procedure after 30 mins, the db size is recalculated and cached in the table.  The input parameter, -1 indicates the default refresh time of 30 mins where as 0 tells an immediate refresh of the cached value. Any positive value (n) indicates that the db size is returned from the cached value for the next n minutes from the old SNAPSHOT_TIMESTAMP. 

Now, running the query "select * from SYSTOOLS.STMG_DBSIZE_INFO" will output the same value.

db2 "select * from SYSTOOLS.STMG_DBSIZE_INFO"

SNAPSHOT_TIMESTAMP         DB_SIZE              DB_CAPACITY
-------------------------- -------------------- --------------------
2014-06-15-00.32.43.272000            165322752         404513460224

II) You can also calculate the db size from the total size of the all data files related to the db. 

    Change to the directory where data files of your data base are stored and use the command du in linux.  In windows, right click on the folder where data files are captured and click on properties which shows the size. 

III) You can also find the approx  db size using the current db backup size. 

     Take the backup of the database and you can find the size of the backup file using ls command in linux. 

Note: Database capacity is different from database size. Database size is calculated from used pages for all table spaces both in DMS and SMS  where as Database capacity is calculated from the usable pages  for all table spaces in DMS and SMS. 

DMS - Database Managed Space

SMS - System Managed Space

More on db size, you can refer the  ibm technical tutorials. 

Read More