Question

DB2中如何复制一张表结构

包括表空间、索引、注释，仅仅改一下表名

Answer 1

比如你想复制SAMPLE中的EMPLOYEE，可运行：
db2look -d SAMPLE -t "EMPLOYEE" -a -e -l -x -c ;

得到结果：
-- 此 CLP 文件是使用 DB2LOOK 版本 "9.7" 创建的
-- 时间戳记: 2012-3-12 18:30:46
-- 数据库名称: SAMPLE
-- 数据库管理器版本: DB2/NT Version 9.7.0
-- 数据库代码页: 1208
-- 数据库整理顺序为: IDENTITY

CONNECT TO SAMPLE;

-- 模拟表空间

ALTER TABLESPACE SYSCATSPACE
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
NO FILE SYSTEM CACHING
AUTORESIZE YES
TRANSFERRATE 0.060000;

ALTER TABLESPACE TEMPSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;

ALTER TABLESPACE USERSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
NO FILE SYSTEM CACHING
AUTORESIZE YES
TRANSFERRATE 0.060000;

------------------------------------------------
-- 表的 DDL 语句 "ADMINISTRATOR"."EMPLOYEE"
------------------------------------------------

CREATE TABLE "ADMINISTRATOR"."EMPLOYEE" (
"EMPNO" CHAR(6) NOT NULL ,
"FIRSTNME" VARCHAR(12) NOT NULL ,
"MIDINIT" CHAR(1) ,
"LASTNAME" VARCHAR(15) NOT NULL ,
"WORKDEPT" CHAR(3) ,
"PHONENO" CHAR(4) ,
"HIREDATE" DATE ,
"JOB" CHAR(8) ,
"EDLEVEL" SMALLINT NOT NULL ,
"SEX" CHAR(1) ,
"BIRTHDATE" DATE ,
"SALARY" DECIMAL(9,2) ,
"BONUS" DECIMAL(9,2) ,
"COMM" DECIMAL(9,2) )
IN "USERSPACE1" ;

-- 表上主键的 DDL 语句 "ADMINISTRATOR"."EMPLOYEE"

ALTER TABLE "ADMINISTRATOR"."EMPLOYEE"
ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY
("EMPNO");

-- 表上的索引的 DDL 语句 "ADMINISTRATOR"."EMPLOYEE"

CREATE INDEX "ADMINISTRATOR"."XEMP2" ON "ADMINISTRATOR"."EMPLOYEE"
("WORKDEPT" ASC)

COMPRESS NO ALLOW REVERSE SCANS;
-- 基于表的别名的 DDL 语句 "ADMINISTRATOR"."EMPLOYEE"

CREATE ALIAS "ADMINISTRATOR"."EMP" FOR TABLE "ADMINISTRATOR"."EMPLOYEE";

-- 表上的外键的 DDL 语句 "ADMINISTRATOR"."EMPLOYEE"

ALTER TABLE "ADMINISTRATOR"."EMPLOYEE"
ADD CONSTRAINT "RED" FOREIGN KEY
("WORKDEPT")
REFERENCES "ADMINISTRATOR"."DEPARTMENT"
("DEPTNO")
ON DELETE SET NULL
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

-- 表上的检查约束的 DDL 语句 "ADMINISTRATOR"."EMPLOYEE"

ALTER TABLE "ADMINISTRATOR"."EMPLOYEE"
ADD CONSTRAINT "NUMBER" CHECK
(PHONENO >= '0000' AND PHONENO <= '9999')
ENFORCED
ENABLE QUERY OPTIMIZATION;

---------------------------------
-- 用户定义的函数的 DDL 语句
---------------------------------

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";

CREATE FUNCTION resign_employee (number CHAR(6))
RETURNS TABLE (empno CHAR(6),
salary DOUBLE,
dept CHAR(3))
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN ATOMIC
-- -------------------------------------------------------------------------------------
-- Routine type: SQL table function
-- Routine name: resign_employee
--
-- Purpose: This procedure takes in an employee number, then removes that
-- employee from the EMPLOYEE table.
-- A useful extension to this function would be to archive the
-- original record into an archive table.
--
-- --------------------------------------------------------------------------------------
DECLARE l_salary DOUBLE;--
DECLARE l_job CHAR(3);--

SET (l_salary, l_job) = (SELECT salary, job
FROM OLD TABLE (DELETE FROM employee
WHERE employee.empno = number));--

RETURN VALUES (number,l_salary, l_job);--
END
;

----------------------------

-- 视图的 DDL 语句

----------------------------
SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VEMP AS SELECT ALL EMPNO , FIRSTNME, MIDINIT , LASTNAME,
WORKDEPT FROM EMP;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VDEPMG1 (DEPTNO, DEPTNAME, MGRNO, FIRSTNME, MIDINIT, LASTNAME,
ADMRDEPT) AS SELECT ALL DEPTNO, DEPTNAME, EMPNO, FIRSTNME, MIDINIT, LASTNAME,
ADMRDEPT FROM DEPT LEFT OUTER JOIN EMP ON MGRNO = EMPNO;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VEMPDPT1 (DEPTNO, DEPTNAME, EMPNO, FRSTINIT, MIDINIT, LASTNAME,
WORKDEPT) AS SELECT ALL DEPTNO, DEPTNAME, EMPNO, SUBSTR(FIRSTNME, 1, 1),
MIDINIT, LASTNAME, WORKDEPT FROM DEPT RIGHT OUTER JOIN EMP ON WORKDEPT
= DEPTNO;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VASTRDE2 (DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2,
DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN) AS SELECT ALL D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT,
D1.LASTNAME,'2', D1.DEPTNO,D1.DEPTNAME,E2.EMPNO,E2.FIRSTNME,E2.MIDINIT,
E2.LASTNAME FROM VDEPMG1 D1, EMP E2 WHERE D1.DEPTNO = E2.WORKDEPT;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VPROJRE1 (PROJNO,PROJNAME,PROJDEP,RESPEMP,FIRSTNME,MIDINIT,
LASTNAME,MAJPROJ) AS SELECT ALL PROJNO,PROJNAME,DEPTNO,EMPNO,FIRSTNME,MIDINIT,
LASTNAME,MAJPROJ FROM PROJ, EMP WHERE RESPEMP = EMPNO;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VSTAFAC2 (PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT,
LASTNAME, EMPTIME,STDATE, ENDATE, TYPE) AS SELECT ALL EP.PROJNO, EP.ACTNO,
AC.ACTDESC, EP.EMPNO,EM.FIRSTNME, EM.MIDINIT, EM.LASTNAME, EP.EMPTIME,
EP.EMSTDATE, EP.EMENDATE,'2' FROM EMPPROJACT EP, ACT AC, EMP EM WHERE
EP.ACTNO = AC.ACTNO AND EP.EMPNO = EM.EMPNO;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VPHONE (LASTNAME, FIRSTNAME, MIDDLEINITIAL, PHONENUMBER,
EMPLOYEENUMBER, DEPTNUMBER, DEPTNAME) AS SELECT ALL LASTNAME, FIRSTNME,
MIDINIT , VALUE(PHONENO,' '), EMPNO, DEPTNO, DEPTNAME FROM EMP,
DEPT WHERE WORKDEPT = DEPTNO;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE VIEW VEMPLP (EMPLOYEENUMBER, PHONENUMBER) AS SELECT ALL EMPNO
, PHONENO FROM EMP;

COMMIT WORK;

CONNECT RESET;

TERMINATE;

-- 为所有创建程序生成统计信息
-- db2look 实用程序将只考虑指定的表
-- 正在创建表的 DDL
-- 正在自动绑定程序包 ...
-- 绑定成功
-- 正在自动绑定程序包 ...
-- 绑定成功
;

将所有EMPLOYEE表名改掉，然后运行这段脚本。

希望能帮到您。

Answer 2

DB2复制表结构：

create table table_name_new as (select * from table_name_old) definition only;

插入数据

insert into table_name_new （select * from table_name_old);

也可以用export 、import来插入数据

--复制表结构，但是不复制主键、约束、索引...

create table table_name_new like table_name_old;

Answer 3

create table [tablename] like [tablename]