批处理数据--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
SQL1035NThe database is currently in use.SQLSTATE=57019
注：备份前先关闭数据库
-bash-3.2$ db2stop
02/17/2015 12:32:16 0 0 SQL1025NThe database manager was not stopped because databases are still active.
SQL1025NThe database manager was not stopped because databases are still active.
-bash-3.2$ db2stop force
02/17/2015 12:41:18 0 0 SQL1064NDB2STOP processing was successful.
SQL1064NDB2STOP processing was successful.
-bash-3.2$ db2start
注：然后在启动
02/17/2015 12:42:11 0 0 SQL1063NDB2START processing was successful.
SQL1063NDB2START 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'
SQL0104NAn 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
SQL1035NThe database is currently in use.SQLSTATE=57019
-bash-3.2$ db2 backup db cashman online use tsm
SQL2413NOnline 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 IdApplication Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 toad.exe 25 192.168.1.42.60927.150216044739 CASHMAN1
DB2INST1 toad.exe 21 192.168.1.40.62487.150216044337 CASHMAN1
DB2INST1 toad.exe 7 192.168.1.40.62486.150216044332 CASHMAN1
-bash-3.2$ db2stop force db2start
SQL2032NThe "db2start" parameter is not valid.SQLSTATE=22531
-bash-3.2$ db2stop force
02/17/2015 12:48:43 0 0 SQL1064NDB2STOP processing was successful.
SQL1064NDB2STOP processing was successful.
-bash-3.2$ db2 backup db cashman use tsm
SQL1032NNo start database manager command was issued.
-bash-3.2$ db2start
02/17/2015 12:49:07 0 0 SQL1063NDB2START processing was successful.
SQL1063NDB2START processing was successful.
-bash-3.2$ db2 backup db cashman use tsm
SQL2071NAn 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
SQL2061NAn 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
ASKeyLinuxchong db2inst1 hegl poka01 yang
TT_DB dasusr1 esaadmin lost+foundtest
backup db2fenc1 guest poka wqs
-bash-3.2$ db2 backup db cashman to /home
SQL2061NAn 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_sqlsqllib
-bash-3.2$ cd ..
-bash-3.2$ ls
ASKeyLinuxchong db2inst1 hegl poka01 yang
TT_DB dasusr1 esaadmin lost+foundtest
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 2012ASKeyLinux
drwxr-xr-x 2 root system 4096 Jul 19 2012TT_DB
drwxr-xr-x 3 root system 256 Nov 21 09:19 backup
drwxr-xr-x 2 chong system 256 Sep 04 2012chong
drwxr-xr-x 3 dasusr1dasadm1 256 Sep 15 09:50 dasusr1
drwxr-xr-x 2 214 102 256 May 14 2014db2fenc1
drwxr-xr-x 10 db2inst1 db2iadm1 4096 Nov 21 10:36 db2inst1
drwxr-xr-x 2 esaadmin system 256 Feb 26 1970esaadmin
drwxr-xr-x 3 guest usr 256 Jun 16 2012guest
drwxr-xr-x 9 hegl system 4096 Sep 05 2013hegl
drwx------ 2 root system 256 May 24 2007lost+found
drwxr-xr-x 20 poka staff 4096 Dec 02 2013poka
drwxr-xr-x 11 root system 4096 Apr 02 2013poka01
drwxr-xr-x 3 test staff 256 Sep 15 09:59 test
drwxr-xr-x 3 root system 4096 Jun 25 2012wqs
drwxr-xr-x 2 yang system 256 Sep 04 2012yang
-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
SQL2061NAn 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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
BD20141015151732001 F DS0000000.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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
RD20141016112050001 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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
BD20141114121100001 F DS0000000.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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
BD20150217124659000 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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
BD20150217124927000 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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
BD20150217125001000 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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
BD20150217125134000 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 LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
BD20150217125411000 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
SQL2071NAn 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
SQL2071NAn 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
SQL1035NThe database is currently in use.SQLSTATE=57019
-bash-3.2$ db2stop force
02/17/2015 13:39:14 0 0 SQL1064NDB2STOP processing was successful.
SQL1064NDB2STOP processing was successful.
-bash-3.2$ db2start
02/17/2015 13:39:20 0 0 SQL1063NDB2START processing was successful.
SQL1063NDB2START 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
SQL1035NThe database is currently in use.SQLSTATE=57019
-bash-3.2$ db2stop force
02/17/2015 13:56:53 0 0 SQL1064NDB2STOP processing was successful.
SQL1064NDB2STOP processing was successful.
-bash-3.2$ db2start
02/17/2015 13:56:59 0 0 SQL1063NDB2START processing was successful.
SQL1063NDB2START 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
SQL2581NRestore 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,启动连接后，可覆盖原数据
SQL2539WWarning!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
DB20000IThe RESTORE DATABASE command completed successfully.
创建数据库
C:\Users\Administrator>db2 create database cashman using codeset utf-8 territory
cn
注：用db2inst1的用户创建数据库，然后创建模式，模式隶属于用户的关系，在赋予权限
DB20000ICREATE 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）
DB20000IRESTORE DATABASE命令成功完成。
db2注册码失效
C:\Users\Administrator>db2start
DB2-0 : 服务已返回特定的服务错误代码。
2015-03-06 13:49:54 0 0 SQL8000NDB2START 处理失败；找不到有效的产品许
可证。如果许可您使用此产品，那么应确保正确注册了许可证密钥。可以通过“许可证中
”或 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 SQL1063NDB2START 处理成功。
SQL1063NDB2START 处理成功。
C:\Users\Administrator>db2 drop db cashman
DB20000IDROP DATABASE命令成功完成。
C:\Users\Administrator>db2 create database cashman using codeset utf-8 territory
cn
DB20000ICREATE DATABASE命令成功完成。
注：用db2的用户创建数据库
C:\Users\Administrator>
页:
[1]