db2备份单个表

1397535668

作为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


以上只讨论数据层面，没有讨论复制、备份授权的东西