批处理数据--db2备份数据

如果要插入数据,前提先根据主键删除记录,然后在插入。

批处理包含两个必要文件

init.bat和start.bat

文档内容如下

init.bat内容如下

@echo on
db2 connect to cashman user db2inst1 using '1qaz!QAZ'
db2 set schema db2inst1
db2 -td@ -vf update.sql>log.txt   //将执行结果输出到log.txt
db2 -td@ -vf delete.sql>>log.txt      //注意,如果是>会覆盖上一个执行结果日志,如果是>>会在上一个日志的基础上追加
db2 -td@ -vf insert.sql>>log.txt   
db2 import from t_user.sql of del modified by codepage=1386 insert into t_user(corpid,userid)>>log.txt   //这个定义参照db2set -all设置,可以保证插入数据全部显示

db2 commit 

db2 terminate
pause
exit

 

start.bat内容如下

@echo off
db2cmd init.bat
exit

编辑的每一个语句以@结束,insert以最后一条记录为准后@结束

db2 import from t_user.sql of del modified by codepage=1386 insert into t_user(corpid,userid)>>log.txt 

用到这句话时,t_user.sql用记事本打开

"武金晶","010184","901010200",5,"120108198502280023","13662003683","965EB72C92A549DD","01"
"强雅薇","012111","901010200",5,"120106199107267023","13802098032","965EB72C92A549DD","01"

无需加@,执行结果在log日志中显示

 

备份

http://www.infoq.com/cn/presentations/zee-system-performance-analysis


-bash-3.2# db2 backup db cashman
 SQL1092N  "ROOT" does not have the authority to perform the requested command
or operation.
-bash-3.2# db2 backup db cashman to /opt
SQL1092N  "ROOT" does not have the authority to perform the requested command
or operation.
注:在aix系统中root用户没有权限备份db2数据库
-bash-3.2# su -db2inst1
-db2inst1: 0403-010 A specified flag is not valid for this command.
-bash-3.2# su - db2inst1  
注:切换到db2数据库所在的用户
-bash-3.2$ db2 backup db cashman to /opt
SQL1035N  The database is currently in use.  SQLSTATE=57019
注:备份前先关闭数据库
-bash-3.2$ db2stop
02/17/2015 12:32:16     0   0   SQL1025N  The database manager was not stopped because databases are still active.
SQL1025N  The database manager was not stopped because databases are still active.
-bash-3.2$ db2stop force
02/17/2015 12:41:18     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
-bash-3.2$ db2start
注:然后在启动
02/17/2015 12:42:11     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
-bash-3.2$ db2 connect to cashman user db2inst1 using 1qaz!QAZ
-bash: !QAZ: event not found
-bash-3.2$ db2 connect to cashman user db2inst1 using '1qaz!QAZ'
SQL0104N  An unexpected token "!" was found following "<identifier>". 
Expected tokens may include:  "NEW".  SQLSTATE=42601
-bash-3.2$ db2 connect to cashman user db2inst1 using "1qaz!QAZ"
-bash: !QAZ": event not found
-bash-3.2$ db2 connect to cashman

   Database Connection Information

 Database server        = DB2/AIX64 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = CASHMAN

-bash-3.2$ db2 backup db cashman to /opt
SQL1035N  The database is currently in use.  SQLSTATE=57019
-bash-3.2$ db2 backup db cashman online use tsm
SQL2413N  Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
-bash-3.2$ db2 list applications for db cashman
注:查看当前正在使用的db2连接的情况

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 toad.exe       25         192.168.1.42.60927.150216044739                                CASHMAN  1   
DB2INST1 toad.exe       21         192.168.1.40.62487.150216044337                                CASHMAN  1   
DB2INST1 toad.exe       7          192.168.1.40.62486.150216044332                                CASHMAN  1   

-bash-3.2$ db2stop force db2start
SQL2032N  The "db2start" parameter is not valid.  SQLSTATE=22531
-bash-3.2$ db2stop force        
02/17/2015 12:48:43     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
-bash-3.2$ db2 backup db cashman use tsm
SQL1032N  No start database manager command was issued.
-bash-3.2$ db2start
02/17/2015 12:49:07     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
-bash-3.2$ db2 backup db cashman use tsm
SQL2071N  An error occurred while accessing the shared library
"/home/db2inst1/sqllib/adsm/libtsm.a". Reason code: "1".
-bash-3.2$ db2 backup db cashman to /opt
SQL2061N  An attempt to access media "/opt" is denied.
-bash-3.2$ su - root
root's Password:
-bash-3.2# db2 backup db cashman to /opt
SQL1092N  "ROOT" does not have the authority to perform the requested command
or operation.
-bash-3.2# su - db2inst1
-bash-3.2$ cd /home
-bash-3.2$ ls
ASKeyLinux  chong       db2inst1    hegl        poka01      yang
TT_DB       dasusr1     esaadmin    lost+found  test
backup      db2fenc1    guest       poka        wqs
-bash-3.2$ db2 backup db cashman to /home
SQL2061N  An attempt to access media "/home" is denied.
-bash-3.2$ cd db2inst1
-bash-3.2$ ls
DB2         createsql   db2inst1    hegl        t_inst
bak         db.sql      export_sql  sqllib
-bash-3.2$ cd ..
-bash-3.2$ ls
ASKeyLinux  chong       db2inst1    hegl        poka01      yang
TT_DB       dasusr1     esaadmin    lost+found  test
backup      db2fenc1    guest       poka        wqs
-bash-3.2$ cd backup
-bash-3.2$ ls
mydata
-bash-3.2$ cd ..
-bash-3.2$ ll
-bash: ll: command not found
-bash-3.2$ ls -l
total 56
drwxr-xr-x    2 root     system         4096 Oct 25 2012  ASKeyLinux
drwxr-xr-x    2 root     system         4096 Jul 19 2012  TT_DB
drwxr-xr-x    3 root     system          256 Nov 21 09:19 backup
drwxr-xr-x    2 chong    system          256 Sep 04 2012  chong
drwxr-xr-x    3 dasusr1  dasadm1         256 Sep 15 09:50 dasusr1
drwxr-xr-x    2 214      102             256 May 14 2014  db2fenc1
drwxr-xr-x   10 db2inst1 db2iadm1       4096 Nov 21 10:36 db2inst1
drwxr-xr-x    2 esaadmin system          256 Feb 26 1970  esaadmin
drwxr-xr-x    3 guest    usr             256 Jun 16 2012  guest
drwxr-xr-x    9 hegl     system         4096 Sep 05 2013  hegl
drwx------    2 root     system          256 May 24 2007  lost+found
drwxr-xr-x   20 poka     staff          4096 Dec 02 2013  poka
drwxr-xr-x   11 root     system         4096 Apr 02 2013  poka01
drwxr-xr-x    3 test     staff           256 Sep 15 09:59 test
drwxr-xr-x    3 root     system         4096 Jun 25 2012  wqs
drwxr-xr-x    2 yang     system          256 Sep 04 2012  yang
-bash-3.2$ chmod +w backup
注:db2inst1用户不能赋权限
chmod: backup: Operation not permitted.
-bash-3.2$ su - root     
root's Password:
-bash-3.2# chmod 775 /home/backup
注:root用户需要赋权限
-bash-3.2# su - db2inst1
-bash-3.2$ db2 backup db cashman to /home/backup
SQL2061N  An attempt to access media "/home/backup" is denied.
-bash-3.2$ db2 list history backup all for cashman
注:db2历史上备份的情况

                    List History File for cashman

Number of matching file entries = 8


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20141015151732001   F    D  S0000000.LOG S0000000.LOG 
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE                                                          
  00002 USERSPACE1                                                           
  00003 MYTBS                                                                
  00004 SYSTOOLSPACE                                                         
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP CASHMAN OFFLINE                                      
 Start Time: 20141015151732
   End Time: 20141015151736
     Status: A
 ----------------------------------------------------------------------------
  EID: 5 Location: /home/db2inst1/bak


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  R  D  20141016112050001   F       S0000000.LOG S0000000.LOG 20141015151732
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE                                                          
  00002 USERSPACE1                                                           
  00003 MYTBS                                                                
  00004 SYSTOOLSPACE                                                         
 ----------------------------------------------------------------------------
    Comment: RESTORE CASHMAN NO RF                                           
 Start Time: 20141016112050
   End Time: 20141016112126
     Status: A
 ----------------------------------------------------------------------------
  EID: 6 Location:


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20141114121100001   F    D  S0000000.LOG S0000000.LOG 
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE                                                          
  00002 USERSPACE1                                                           
  00003 MYTBS                                                                
  00004 SYSTOOLSPACE                                                         
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP CASHMAN OFFLINE                                      
 Start Time: 20141114121100
   End Time: 20141114121107
     Status: A
 ----------------------------------------------------------------------------
  EID: 13 Location: /home/db2inst1/bak


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20150217124659000   N       S0000000.LOG              
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP CASHMAN ONLINE                                       
 Start Time: 20150217124659
   End Time: 20150217124700
     Status: A
 ----------------------------------------------------------------------------
  EID: 20 Location:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2413   sqlerrml: 0

 sqlerrmc:
 sqlerrp : sqlubIni
 sqlerrd : (1) 0                (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20150217124927000   F       S0000000.LOG              
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE                                                          
  00002 USERSPACE1                                                           
  00003 MYTBS                                                                
  00004 SYSTOOLSPACE                                                         
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP CASHMAN OFFLINE                                      
 Start Time: 20150217124927
   End Time: 20150217124928
     Status: A
 ----------------------------------------------------------------------------
  EID: 21 Location:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2071   sqlerrml: 37

 sqlerrmc: /home/db2inst1/sqllib/adsm/libtsm.a?1
 sqlerrp : sqlubMWR
 sqlerrd : (1) 0                (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20150217125001000   F       S0000000.LOG              
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE                                                          
  00002 USERSPACE1                                                           
  00003 MYTBS                                                                
  00004 SYSTOOLSPACE                                                         
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP CASHMAN OFFLINE                                      
 Start Time: 20150217125001
   End Time: 20150217125002
     Status: A
 ----------------------------------------------------------------------------
  EID: 22 Location:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2061   sqlerrml: 4

 sqlerrmc: /opt
 sqlerrp : sqlubMWR
 sqlerrd : (1) 0                (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20150217125134000   F       S0000000.LOG              
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE                                                          
  00002 USERSPACE1                                                           
  00003 MYTBS                                                                
  00004 SYSTOOLSPACE                                                         
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP CASHMAN OFFLINE                                      
 Start Time: 20150217125134
   End Time: 20150217125135
     Status: A
 ----------------------------------------------------------------------------
  EID: 23 Location:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2061   sqlerrml: 5

 sqlerrmc: /home
 sqlerrp : sqlubMWR
 sqlerrd : (1) 0                (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20150217125411000   F       S0000000.LOG              
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE                                                          
  00002 USERSPACE1                                                           
  00003 MYTBS                                                                
  00004 SYSTOOLSPACE                                                         
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP CASHMAN OFFLINE                                      
 Start Time: 20150217125411
   End Time: 20150217125412
     Status: A
 ----------------------------------------------------------------------------
  EID: 24 Location:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2061   sqlerrml: 12

 sqlerrmc: /home/backup
 sqlerrp : sqlubMWR
 sqlerrd : (1) 0                (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:
-bash-3.2$ db2 backup db cashman use tsm
SQL2071N  An error occurred while accessing the shared library
"/home/db2inst1/sqllib/adsm/libtsm.a". Reason code: "1".
-bash-3.2$

-bash-3.2$ db2 backup db cashman use tsm
SQL2071N  An error occurred while accessing the shared library
"/home/db2inst1/sqllib/adsm/libtsm.a". Reason code: "1".
-bash-3.2$ db2 backup db cashman to /home/db2inst1
注:在db2用户有权限的目录下备份

Backup successful. The timestamp for this backup image is : 20150217130757

-bash-3.2$

备份数据库步骤如下
-bash-3.2$ db2 backup db cashman to /home/db2inst1
SQL1035N  The database is currently in use.  SQLSTATE=57019
-bash-3.2$ db2stop force
02/17/2015 13:39:14     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
-bash-3.2$ db2start
02/17/2015 13:39:20     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
-bash-3.2$ db2 connect to cashman

   Database Connection Information

 Database server        = DB2/AIX64 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = CASHMAN

-bash-3.2$ db2 backup db cashman to /home/db2inst1


bash-3.2$  db2 restore db cashman incremental automatic from /home/db2inst1 taken at 20150217134041 logtarget /db2_log/cashman
SQL1035N  The database is currently in use.  SQLSTATE=57019
-bash-3.2$ db2stop force
02/17/2015 13:56:53     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
-bash-3.2$ db2start
02/17/2015 13:56:59     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
-bash-3.2$ db2 connect to cashman

   Database Connection Information

 Database server        = DB2/AIX64 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = CASHMAN

-bash-3.2$ db2 restore db cashman incremental automatic from /home/db2inst1 taken at 20150217134041 logtarget /db2_log/cashman
SQL2581N  Restore is unable to extract log files or restore a log directory
from the backup image to the specified path. Reason code "2".
-bash-3.2$ db2 restore db cashman incremental automatic from /home/db2inst1 taken at 20150217134041  
注:停止db2,启动连接后,可覆盖原数据                       
SQL2539W  Warning!  Restoring to an existing database that is the same as the
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n)
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

创建数据库
C:\Users\Administrator>db2 create database cashman using codeset utf-8 territory
 cn
 注:用db2inst1的用户创建数据库,然后创建模式,模式隶属于用户的关系,在赋予权限
DB20000I  CREATE DATABASE命令成功完成。

C:\Users\Administrator>
C:\Users\Administrator>
C:\Users\Administrator>db2 connect to cashman

   数据库连接信息

 数据库服务器         = DB2/NT64 9.5.0
 SQL 授权标识         = ADMINIST...
 本地数据库别名       = CASHMAN


C:\Users\Administrator>db2 restore db cashman incremental automatic from D:\ tak
en at 20150203144733
SQL2523W  警告!正在复原至与备份映像上的数据库不同,但具有匹配名称的现有数据库。

目标数据库将被备份版本覆盖。将删除与目标数据库相关联的前滚恢复日志。
想要继续吗?(y/n)
DB20000I  RESTORE DATABASE命令成功完成。

 db2注册码失效


C:\Users\Administrator>db2start
DB2-0 : 服务已返回特定的服务错误代码。

2015-03-06 13:49:54     0   0   SQL8000N  DB2START 处理失败;找不到有效的产品许
可证。如果许可您使用此产品,那么应确保正确注册了许可证密钥。可以通过“许可证中
”或 db2licm 命令行实用程序来注册许可证。许可证密钥可从您的许可产品 CD 中获得。
SQL1032N  未发出启动数据库管理器的命令。  SQLSTATE=57019

注:当出现上述问题,可能是注册码失效了

C:\Users\Administrator>db2 connect to cashman
SQL1032N  未发出启动数据库管理器的命令。  SQLSTATE=57019

C:\Users\Administrator>db2licm -l      注:可用该命令进行查证
Product name:                     "DB2 Enterprise Server Edition"
Expiry date:                      "已到期"
Product identifier:               "db2ese"
Version information:              "9.5"

在网上下载db2ese_cV9.5CPU.lic,用下边的命令加载

C:\Users\Administrator>db2licm -a D:\db2ese_cV9.5CPU.lic

LIC1402I  成功地添加了许可证。

 


LIC1426I   现在,您已获取本产品的使用许可,详细信息请参阅许可协议。使用此产品必
须接受 IBM 许可协议的条款,该许可协议位于以下目录: "D:\PROGRA~1\IBM\SQLLIB\lice
nse\en"

C:\Users\Administrator>
C:\Users\Administrator>db2licm -l     注:加载完和验证结果
Product name:                     "DB2 Enterprise Server Edition"
License type:                     "CPU 选项"
Expiry date:                      "永久"
Product identifier:               "db2ese"
Version information:              "9.5"
Features:
DB2 Database Partitioning:        "未许可"
DB2 Performance Optimization ESE: "未许可"
DB2 Storage Optimization:         "未许可"
DB2 Advanced Access Control:      "未许可"
DB2 Geodetic Data Management:     "未许可"
DB2 pureXML ESE:                  "未许可"
IBM Homogeneous Federation ESE:   "未许可"
IBM Homogeneous Replication ESE:  "未许可"

C:\Users\Administrator>db2start
2015-03-06 14:27:54     0   0   SQL1063N  DB2START 处理成功。
SQL1063N  DB2START 处理成功。

C:\Users\Administrator>db2 drop db cashman
DB20000I  DROP DATABASE命令成功完成。

C:\Users\Administrator>db2 create database cashman using codeset utf-8 territory
 cn
DB20000I  CREATE DATABASE命令成功完成。
注:用db2的用户创建数据库
C:\Users\Administrator>

转载于:https://www.cnblogs.com/shareyezi/p/4323032.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值