db2备份单个表

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

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

评论

2 楼 luogen33 2012-05-21  

      

1 楼 alvin198761 2012-02-28  

能根据schame备份数据库不？？