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-- 数据库整理顺序为: identityconnect 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 语句 "admin**trator"."employee"------------------------------------------------create table "admin**trator"."employee" ( "empno" char(6) not ** , "firstnme" varchar(12) not ** , "midinit" char(1) , "lastname" varchar(15) not ** , "workdept" char(3) , "phoneno" char(4) , "hiredate" date , "job" char(8) , "edlevel" smallint not ** , "sex" char(1) , "birthdate" date , "salary" decimal(9,2) , "bonus" decimal(9,2) , "comm" decimal(9,2) ) in "userspace1" ; -- 表上主键的 ddl 语句 "admin**trator"."employee"alter table "admin**trator"."employee" add constraint "pk_employee" primary key("empno");-- 表上的索引的 ddl 语句 "admin**trator"."employee"create index "admin**trator"."xemp2" on "admin**trator"."employee" ("workdept" asc)compress no allow reverse scans;-- 基于表的别名的 ddl 语句 "admin**trator"."employee"create alias "admin**trator"."emp" for table "admin**trator"."employee";-- 表上的外键的 ddl 语句 "admin**trator"."employee"alter table "admin**trator"."employee" add constraint "red" foreign key("workdept")references "admin**trator"."department"("deptno")on delete set **on update no actionenforcedenable query optimization;-- 表上的检查约束的 ddl 语句 "admin**trator"."employee"alter table "admin**trator"."employee" add constraint "number" check (phoneno >= '0000' and phoneno <= '9999')enforcedenable query optimization;----------------------------------- 用户定义的函数的 ddl 语句---------------------------------set current schema = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";create function resign_employee (number char(6)) returns table (empno char(6), salary double, dept char(3)) modifies sql data language sql begin **ic-- --------------------------------------------------------------------------------------- routine type: sql table function-- routine name: resign_employee---- purpose: th** procedure takes in an employee number, then removes that-- employee from the employee table.-- a useful extension to th** 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 = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";create view vemp as select all empno , firstnme, midinit , lastname, workdept from emp;set current schema = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";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 = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";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 = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";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 = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";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 = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";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 whereep.actno = ac.actno and ep.empno = em.empno;set current schema = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";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 = "admin**trator";set current path = "sysibm","sysfun","sysproc","sysibmadm","admin**trator";create view vemplp (employeenumber, phonenumber) as select all empno, phoneno from emp;commit work;connect reset;terminate;-- 为所有创建程序生成统计信息 -- db2look 实用程序将只考虑指定的表 -- 正在创建表的 ddl-- 正在自动绑定程序包 ... -- 绑定成功-- 正在自动绑定程序包 ... -- 绑定成功;将所有employee表名改掉，然后运行这段脚本。希望能帮到您。 20210311