批处理数据--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>
