作为DBA,往往会备份整个数据库,但是有的时候需要备份单个表,在db2有以下两种方法:
以上只讨论数据层面,没有讨论复制、备份授权的东西
--方法1 使用db2命令
--导出表结构ddl
bash-3.00$ db2look -d mtpdb -t SIBOWNER -e
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: A1INMTP
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- This CLP file was created using DB2LOOK Version "9.5"
-- Timestamp: Fri Feb 18 19:10:10 2011
-- Database Name: MTPDB
-- Database Manager Version: DB2/AIX64 Version 9.5.5
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful
CONNECT TO MTPDB;
------------------------------------------------
-- DDL Statements for table "IBMWSSIB"."SIBOWNER"
------------------------------------------------
CREATE TABLE "IBMWSSIB"."SIBOWNER" (
"ME_UUID" VARCHAR(16) ,
"INC_UUID" VARCHAR(16) ,
"VERSION" INTEGER ,
"MIGRATION_VERSION" INTEGER )
IN "USERSPACE1" ;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
--导出数据
db2 => export to "/tmp/IBMWSSIB.SIBOWNER.bak20110219" of del messages "/tmp/IBMWSSIB.SIBOWNER.msg" select * from IBMWSSIB.SIBOWNER
Number of rows exported: 1
--方法2 使用SQL (其实不属于SQL,只有db2使用)
--建立相同结构的表
db2 => create table TableName as (select * from TblName) definition only
--插入原有数据
db2 => insert into TableName select * from TblName
以上只讨论数据层面,没有讨论复制、备份授权的东西