DB2中如何复制一张表结构

答案：比如你想复制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表名改掉，然后运行这段脚本。

希望能帮到您。

其他：create table [tablename] like [tablename]

上一个：db2数据库下载地址
下一个：DB2数据库,我要查询包括'abcd'的所有表名,如何查询呢?

更多图片编程知识: