DB2中如何复制一张表结构

比如你想复制SAMPLE中的EMPLOYEE，可运行：
db2look -d SAMPLE -t "EMPLOYEE" -a -e -l -x -c ;
得到结果：
-- 此 CLP 文件是使用 DB2LOOK 版本 "97" 创建的
-- 时间戳记: 2012-3-12 18:30:46
-- 数据库名称: SAMPLE
-- 数据库管理器版本: DB2/NT Version 970
-- 数据库代码页: 1208
-- 数据库整理顺序为: IDENTITY
CONNECT TO SAMPLE;
-- 模拟表空间
ALTER TABLESPACE SYSCATSPACE
PREFETCHSIZE AUTOMATIC
OVERHEAD 7500000
NO FILE SYSTEM CACHING
AUTORESIZE YES
TRANSFERRATE 0060000;
ALTER TABLESPACE TEMPSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7500000
FILE SYSTEM CACHING
TRANSFERRATE 0060000;
ALTER TABLESPACE USERSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7500000
NO FILE SYSTEM CACHING
AUTORESIZE YES
TRANSFERRATE 0060000;
------------------------------------------------
-- 表的 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 employeeempno = 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 D1DEPTNO,D1DEPTNAME,D1MGRNO,D1FIRSTNME,D1MIDINIT,
D1LASTNAME,'2', D1DEPTNO,D1DEPTNAME,E2EMPNO,E2FIRSTNME,E2MIDINIT,
E2LASTNAME FROM VDEPMG1 D1, EMP E2 WHERE D1DEPTNO = E2WORKDEPT;
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 EPPROJNO, EPACTNO,
ACACTDESC, EPEMPNO,EMFIRSTNME, EMMIDINIT, EMLASTNAME, EPEMPTIME,
EPEMSTDATE, EPEMENDATE,'2' FROM EMPPROJACT EP, ACT AC, EMP EM WHERE
EPACTNO = ACACTNO AND EPEMPNO = EMEMPNO;
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表名改掉，然后运行这段脚本。
希望能帮到您。

以上就是关于DB2中如何复制一张表结构全部的内容，包括:DB2中如何复制一张表结构、、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！