前言:
最近有些项目在Linux下使用的是IBM的DB2数据库,感觉没MySQL那样容易了解深入,可能是DB2数据库更倾向于商业化,没MySQL那样开源,相关DB2的资源网上没MySQL那样普及,至于在学习DB2过程中没学MySQL那样顺畅.
下面简单记录下学习笔记,我所使用的Linux系统是RedHat企业版本(Red Hat Enterprise Linux 5),此为自己安装在虚拟机下的DB2数据库,主要用来学习的,真正的如果商业使用DB2数据库需要付费的。
Linux下DB2数据库安装步骤:
1.下载DB2数据库软件包db2exc_971_LNX_x86.tar.gz (注:官方免费使用版本)http://www.ibm.com/developerworks/cn/downloads/im/udbexp/Linux for System x86 V9.7.5 317MBDB2 Express-C 免费数据库服务器db2exc_971_LNX_x86.tar.gz (404 MB)Data Studio（独立）管理实用程序ibm_data_studio_standalone_linux.tar.gz (208 MB)DB2 9.7.1 的本地语言包db2exc_nlpack_971_LNX_x86.tar.gz (317 MB)注:第一次登陆需要注册用户后才可以下载2.解压 tar -zxvf db2软件包(放在opt目录下)注:安装切换到root用户下[root@localhost DB2]# pwd/mnt/hgfs/Sunrier/DB2 [root@localhost DB2]# lsdb2exc_971_LNX_x86.tar.gz [root@localhost DB2]# cd /opt[root@localhost opt]# ls[root@localhost opt]# tar -xf /mnt/hgfs/Sunrier/DB2/db2exc_971_LNX_x86.tar.gz.................................................................................................................................................................................expc/db2_deinstallexpc/readmefirst.htmexpc/db2setupexpc/db2lsexpc/db2ckupgradeexpc/db2_installexpc/readmefirst.txtexpc/installFixPack[root@localhost opt]# lsexpc[root@localhost opt]#3.开始安装软件包[root@localhost opt]# lsexpc[root@localhost opt]# cd expc[root@localhost expc]# lsdb2 db2_deinstall db2ls db2setup installFixPack readmefirst.htmdb2ckupgrade db2_install db2prereqcheck doc readmefirst readmefirst.txt[root@localhost expc]# ./db2_install用于安装产品的缺省目录 － /opt/ibm/db2/V9.7***********************************************************要选择另一个目录用于安装吗?[是/否]否正在初始化 DB2 安装。要执行的任务总数为:35要执行的所有任务的总估计时间为: 1149任务 #1 启动描述: 正在检查许可协议的接受情况估计时间 1 秒任务 #1 结束任务 #2 启动描述: 具备 root 用户特权的情况下进行安装时的基本客户机支持估计时间 3 秒任务 #2 结束********************************************************************************任务 #34 启动描述: 正在注册 DB2 更新服务估计时间 30 秒任务 #34 结束任务 #35 启动描述: 正在更新全局概要文件注册表估计时间 3 秒任务 #35 结束已成功完成执行。有关更多信息, 请参阅 "/tmp/db2_install.log.24239" 上的 DB2安装日志 。[root@localhost opt]# lsexpc ibm[root@localhost opt]#4.注册license[root@localhost opt]# cd ibm/db2/V9.7/adm[root@localhost adm]# pwd/opt/ibm/db2/V9.7/adm[root@localhost adm]# lscieadmsv cieprcnt db2audit db2chparm db2fmp db2genp db2govlg db2inidb db2path db2rfpen db2set db2stop db2sysc ciecsdem cieprisc db2cacpy db2dart db2fmpr db2gov db2havend db2licd db2pd db2rstar db2star2 db2stop2 db2syscr cielock cieprupx db2cap db2dasstml db2fmpterm db2govd db2iclean db2licm db2pdcfg db2rstop db2start db2stst db2trc[root@localhost adm]# /opt/ibm/db2/V9.7/adm/db2licm -a /opt/expc/db2/license/db2expc_uw.licLIC1402I License added successfully.LIC1426I This product is now licensed for use as outlined in your License Agreement. USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSEAGREEMENT, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V9.7/license/zh_CN.utf8"[root@localhost adm]#5.创建三个用户组[root@localhost adm]# groupadd -g 901 db2iadm1[root@localhost adm]# groupadd -g 902 db2fadm1[root@localhost adm]# groupadd -g 903 db2dadm16.创建三个用户(分别是拥有实例的用户,受防护的用户,DAS用户)且指定对应的用户组[root@localhost adm]# useradd -g db2iadm1 -u 101 -d /home/db2inst1 -m db2inst1[root@localhost adm]# useradd -g db2fadm1 -u 102 -d /home/db2fenc1 -m db2fenc1[root@localhost adm]# useradd -g db2dadm1 -u 103 -d /home/db2dasusr1 -m db2dasusr1参数说明:-u uid 使用者的ID值,必须为唯一的ID值,除非用-o选项可以不唯一,数字不可为负值,0~999传统上是保留给系统帐号使用。-d path 使用者目录-m 使用者目录如不存在则自动建立-s shell 使用者登入后使用shell名称作为提示符,如果不写系统会帮你指定预设的登入shell例子:[root@localhost ~]# groupadd -g 888 testgroup[root@localhost ~]# useradd -g testgroup -u 555 -d /home/testuser1 -m testuser1或[root@localhost ~]# useradd -g testgroup -u 555 -d /home/testuser1 -m -s /bin/sh testuser1[root@localhost ~]# userdel testuser1[root@localhost ~]# groupdel testgroup当加上-s /bin/sh时,在root用户下切换到testuser1时变成sh-3.1$ 查看echo $PS1变成了\s-\v\$所以想更改成以前的那种登陆提示符,修改PS1环境变量的值sh-3.1$ export PS1="[\u@\h \W]\$ "\h 显示主机名\u 显示当前用户的用户名\W 显示当前工作目录的名字\w 显示当前工作目录的路径\s 显示当前运行的shell的名字\v 显示shell的版本\t 显示当前时间\d 显示当前日期\\ 显示反斜杠\$ 显示$符作为提示符,如果用户是root的话,则显示#号\# 显示当前命令的命令编号\! 显示该命令的历史记录编号\n 打印新行\nnn 显示nnn的八进制值7.添加三个用户的密码添加实例的用户密码[root@localhost adm]# passwd db2inst1Changing password for user db2inst1.New UNIX password: 输入新密码(如Sunrier)BAD PASSWORD: it is based on a dictionary wordRetype new UNIX password: 再输入新密码(如Sunrier,跟第一次输入相同)passwd: all authentication tokens updated successfully.[root@localhost adm]#添加受防护的用户密码[root@localhost adm]# passwd db2fenc1Changing password for user db2fenc1.New UNIX password: 输入新密码BAD PASSWORD: it is based on a dictionary wordRetype new UNIX password: 再输入新密码(两次需相同,否则需要重新输入)passwd: all authentication tokens updated successfully.[root@localhost adm]#添加DAS用户密码[root@localhost adm]# passwd db2dasusr1Changing password for user db2dasusr1.New UNIX password: 输入新密码BAD PASSWORD: it is based on a dictionary wordRetype new UNIX password: 再输入新密码(两次需相同,否则需要重新输入)passwd: all authentication tokens updated successfully.[root@localhost adm]#8.检查用户组和用户是否创建成功用户组: more /etc/group | grep db2用户: more /etc/passwd | grep db2[root@localhost adm]# more /etc/group | grep db2db2iadm1:x:901:db2fadm1:x:902:db2dadm1:x:903:[root@localhost adm]# more /etc/passwd | grep db2db2inst1:x:101:901::/home/db2inst1:/bin/shdb2fenc1:x:102:902::/home/db2fenc1:/bin/shdb2dasusr1:x:103:903::/home/db2dasusr1:/bin/sh[root@localhost adm]#9.创建实例进入/opt/ibm/db2/V9.7/instance目录[root@localhost adm]# cd /opt/ibm/db2/V9.7/instance[root@localhost instance]# pwd/opt/ibm/db2/V9.7/instance[root@localhost instance]# ./dascrt -u db2dasusr1 (注:创建DB2管理服务器)[root@localhost instance]# ./db2icrt -u db2fenc1 db2inst1 (注:创建DB2实例)或者[root@localhost instance]# ./db2icrt -a server -u db2fenc1 db2inst1 (注:创建DB2实例)这里dascrt创建的是DB2 adminstration server,每台服务器只有一个这种server,为进行DB2管理(比如运行控制中心)所必须,同时指定其管理用户是db2dasusr1 ,db2icrt创建的是实例,其名字一般和管理用户名一样,这里db2fenc1指定db2inst1为它的防护用户,而db2inst1为一实例用户10.配置DB21)设置DB2自启动(注:依然在root用户下)[root@localhost instance]# pwd/opt/ibm/db2/V9.7/instance[root@localhost instance]# ./db2iauto -on db2inst1db2set MsgRC=1306, P1='', P2=''[root@localhost instance]# 设置对db2inst1在Linux启动时自动启动。2)修改网络服务端口(注:在db2inst1用户下)修改DB2的服务端口为50658,默认情况下端口是50000[root@localhost instance]# su - db2inst1[db2inst1@localhost ~]$ db2 update dbm cfg using SVCENAME 50658DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@localhost ~]$3)修改DB2连接方式为TCP/IP[db2inst1@localhost ~]$ db2set DB2COMM=TCPIP [db2inst1@localhost ~]$修改DB2连接方式为TCPIP,然后可通过JDBC、ODBC等访问本DB2服务器上的数据库,安装了DB2客户端的其它机器也可访问数据库。11.查看DB2许可证情况[root@localhost ~]$ su - db2inst1 [db2inst1@localhost ~]$ db2licm -lProduct name: "DB2 Express-C"License type: "Unwarranted"Expiry date: "Permanent"Product identifier: "db2expc"Version information: "9.7"Max number of CPUs: "2"Max amount of memory (GB): "2"[db2inst1@localhost DB2]$注:上面为官网下载的免费DB2版本[root@localhost ~]$ su - db2inst1 [db2inst1@localhost ~]$ db2licm -lProduct Name = "DB2 Universal Database Express Edition"Product Identifier = "DB2EXP"Version Information = "8.2"Expiry Date = "Permanent"Registered User Policy = "Disabled"Enforcement Policy = "Soft Stop"Number of processors = "1"Number of licensed processors = "2"Annotation = ""Other information = ""[db2inst1@localhost ~]$ db2licm -l注:上面为商业使用中的付费DB2版本12.查看DB2版本[root@localhost ~]$ su - db2inst1 [db2inst1@localhost ~]$ db2level DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09071" with level identifier "08020107".Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23033", and Fix Pack "1".Product is installed at "/opt/ibm/db2/V9.7".[db2inst1@localhost ~]$注:上面为官网下载的免费DB2版本[db2inst1@localhost ~]$ db2levelDB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08023" with level identifier "03040106".Informational tokens are "DB2 v8.1.2.97", "special_15462", "MI00142_15462", and FixPak "10".Product is installed at "/opt/IBM/db2/V8.1".[db2inst1@localhost ~]$注:上面为商业使用中的付费DB2版本
DB2相关数据库命令
1.数据库实例的启动首先要启动数据库的实例,即切换到db2inst1用户(注:db2inst1用户为当前数据库的实例),然后执行db2start启动数据库的实例[root@localhost instance]# su - db2inst1[db2inst1@localhost ~]$ db2startSQL1063N DB2START processing was successful.[db2inst1@localhost ~]$2.数据库实例的关闭首先在db2inst1用户下强制关闭实例上的所有应用程序,然后再关闭数据 库实例[db2inst1@localhost ~]$ db2 force application allDB20000I The FORCE APPLICATION command completed successfully.DB21024I This command is asynchronous and may not be effective immediately.[db2inst1@localhost ~]$ db2stopSQL1064N DB2STOP processing was successful.[db2inst1@localhost ~]$强制停止[db2inst1@localhost DB2]$ db2stop forceSQL1064N DB2STOP processing was successful.[db2inst1@localhost DB2]$3.显示所有的实例[db2inst1@localhost ~]$ db2ilistdb2inst1[db2inst1@localhost ~]$4.显示当前的实例[db2inst1@localhost ~]$ db2 get instance The current database manager instance is: db2inst1[db2inst1@localhost ~]$5.删除一个实例(注:需切换到root用户权限下)[root@localhost ~]# cd /opt/ibm/db2/V9.7/instance[root@localhost instance]# pwd/opt/ibm/db2/V9.7/instance[root@localhost instance]# ./db2idrop db2inst1DBI1070I Program db2idrop completed successfully.[root@localhost instance]#6.列出当前实例中有哪些数据库[root@localhost instance]# su - db2inst1[db2inst1@localhost ~]$ db2 list db directorySQL1031N The database directory cannot be found on the indicated file system.SQLSTATE=58031[db2inst1@localhost ~]$注:上面信息说明实例中没有数据库7.创建数据库[root@localhost instance]# su - db2inst1[db2inst1@localhost ~]$ db2 create database testSQL1032N No start database manager command was issued. SQLSTATE=57019[db2inst1@localhost ~]$[db2inst1@localhost ~]$ db2startSQL5043N Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.[db2inst1@localhost ~]$ db2 create database testDB20000I The CREATE DATABASE command completed successfully.[db2inst1@localhost ~]$ db2 list db directory System Database Directory Number of entries in the directory = 1Database 1 entry: Database alias = TEST Database name = TEST Local database directory = /home/db2inst1 Database release level = d.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =[db2inst1@localhost ~]$注:上面通过创建一个test的数据库,并说明了创建数据库时,要先启动数据库,然后创建数据库test成功后并列出了当前实例中的所有数据库,只存在数据库test使用UTF-8编码 db2 create database test on '/home/db2inst1' using codeset UTF-8 territory CNon '/home/db2inst1' 表示数据库路径一般情况下'/home/db2inst1'为默认数据库路径8.连接数据库[db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST[db2inst1@localhost ~]$注:用密码情况下格式[db2inst1@localhost ~]$ db2 connect to test user username using passworddb2 connect to <database> user <username> using <password>9.列出当前实例中所有激活的数据库[root@localhost ~]# su - db2inst1[db2inst1@localhost ~]$ db2 list active databasesSQL1032N No start database manager command was issued. SQLSTATE=57019[db2inst1@localhost ~]$ db2 list db directorySystem Database DirectoryNumber of entries in the directory = 1Database 1 entry:Database alias = TESTDatabase name = TESTLocal database directory = /home/db2inst1Database release level = d.00Comment =Directory entry type = IndirectCatalog database partition number = 0Alternate server hostname =Alternate server port number =[db2inst1@localhost ~]$ db2startSQL1026N The database manager is already active.[db2inst1@localhost ~]$ db2 list active databasesSQL1611W No data was returned by Database System Monitor.[db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST[db2inst1@localhost ~]$ db2 list active databases Active DatabasesDatabase name = TESTApplications connected currently = 1Database path = /home/db2inst1/db2inst1/NODE0000/SQL00001/[db2inst1@localhost ~]$ 注:可以看出查看当前激活的数据库为已经启动后连接的数据库10.查看表的空间[db2inst1@localhost ~]$ db2 list tablespaces [ show detail ] Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = Database managed space Contents = All permanent data. Regular table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 1 Name = TEMPSPACE1 Type = System managed space Contents = System Temporary data State = 0x0000 Detailed explanation: Normal Tablespace ID = 2 Name = USERSPACE1 Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal[db2inst1@localhost ~]$注: show detail为可选项,显示更详细信息或者 db2pd -tablespaces -db 数据库名[db2inst1@localhost ~]$ db2pd -tablespaces -db testDatabase Partition 0 -- Database TEST -- Active -- Up 0 days 00:03:11Tablespace Configuration:Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name0x9DC2A060 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 SYSCATSPACE0x9DC2B4B0 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 TEMPSPACE10x9DC30940 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1Tablespace Statistics:Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped0x9DC2A060 0 24576 24572 18712 0 5860 18712 18712 0x00000000 0 0 No 0x9DC2B4B0 1 1 1 1 0 0 0 0 0x00000000 0 0 No 0x9DC30940 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No Tablespace Autoresize Statistics:Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF0x9DC2A060 0 Yes Yes 33554432 -1 No None None No 0x9DC2B4B0 1 Yes No 0 0 No 0 None No 0x9DC30940 2 Yes Yes 33554432 -1 No None None No Containers:Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container0x9B218F00 0 0 File 24576 24572 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000000/C0000000.CAT0x9B219120 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000001/C0000000.TMP0x9B219390 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000002/C0000000.LRG[db2inst1@localhost ~]$11.列出数据库中所有用户表[db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ------------------------ ----- -------------------------- 0 record(s) selected.[db2inst1@localhost ~]$注:上面信息说明数据库test中还没有表12.在数据库test中创建表student [db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST [db2inst1@localhost ~]$ db2 "create table student (id int,fname varchar(30),age int)"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ------------------------------- --------------- -----STUDENT DB2INST1 T 2012-08-06-14.38.33.456768 1 record(s) selected. [db2inst1@localhost ~]$13.向表student中添加数据信息[db2inst1@localhost ~]$ db2 "insert into student values (1,'Tom',22)"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 "insert into student values (2,'Jack',21)" DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 "insert into student values (3,'Sunrier',25)"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$14.显示表student所有的信息[db2inst1@localhost ~]$ db2 "select * from student"ID FNAME AGE ----------- ------------------------------ -----------1 Tom 222 Jack 213 Sunrier 25 3 record(s) selected.[db2inst1@localhost ~]$15.更改表student中的数据(如将Sunrier的年龄改为22)[db2inst1@localhost ~]$ db2 "select * from student"ID FNAME AGE ----------- ------------------------------ -----------1 Tom 222 Jack 213 Sunrier 25 3 record(s) selected.[db2inst1@localhost ~]$ db2 "update student set age=22 where fname='Sunrier'"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 "select * from student"ID FNAME AGE ----------- ------------------------------ -----------1 Tom 222 Jack 213 Sunrier 22 3 record(s) selected.[db2inst1@localhost ~]$16.查看表student结构[db2inst1@localhost ~]$ db2 describe table student Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 30 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$或[db2inst1@localhost ~]$ db2 "describe select * from student" Column Information Number of columns: 3 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 497 INTEGER 4 ID 2 449 VARCHAR 30 FNAME 5 497 INTEGER 4 AGE 3[db2inst1@localhost ~]$或[db2inst1@localhost ~]$ db2 "describe select * from db2inst1.student" Column Information Number of columns: 3 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 497 INTEGER 4 ID 2 449 VARCHAR 30 FNAME 5 497 INTEGER 4 AGE 3[db2inst1@localhost ~]$17.创建一个新表(如people)与数据库中某个表(如student)结构相同[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ------------------------ --------------- ----- ----------------STUDENT DB2INST1 T 2012-08-06-15.26.17.1895381 record(s) selected.[db2inst1@localhost ~]$ db2 describe table student Data type ColumnColumn name schema Data type name Length Scale Nulls ------------------------------- --------- -------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 30 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from student"ID FNAME AGE ----------- ------------------------------ ----------- 1 Tom 22 2 Jack 21 3 Sunrier 253 record(s) selected.[db2inst1@localhost ~]$ db2 create table people like studentDB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ------------------------------- --------------- ---PEOPLE DB2INST1 T 2012-08-16-15.13.49.396370STUDENT DB2INST1 T 2012-08-06-15.26.17.1895382 record(s) selected.[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls ------------------------------- --------- ---------------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 30 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$18.两个结构相同的表,将原来数据库中某个表(如student)的数据导入与它相同结构的新表(如people)中[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ------------------------------- --------------- ----PEOPLE DB2INST1 T 2012-08-16-15.13.49.396370STUDENT DB2INST1 T 2012-08-06-15.26.17.1895382 record(s) selected.[db2inst1@localhost ~]$ db2 describe table student Data type Column Column name schema Data type name Length Scale Nulls ------------------------------- --------- -----------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 30 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- -----ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 30 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from student"ID FNAME AGE ----------- ------------------------------ -----------1 Tom 222 Jack 213 Sunrier 25 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE ----------- ------------------------------ -----------0 record(s) selected.[db2inst1@localhost ~]$ db2 "insert into people select * from student"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE ----------- ------------------------------ -----------1 Tom 222 Jack 213 Sunrier 25 3 record(s) selected.[db2inst1@localhost ~]$19.修改一个表的字段类型(如表people中的fname字段把varchar(30)改为varchar(28))[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 30 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE ----------- ------------------------------ -----------1 Tom 222 Jack 213 Sunrier 25 3 record(s) selected.[db2inst1@localhost ~]$ db2 "alter table people alter column fname set data type varchar(28)"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------- --------- ------------------- ------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 28 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE ----------- ---------------------------- -----------1 Tom 222 Jack 213 Sunrier 25 3 record(s) selected.[db2inst1@localhost ~]$格式:db2 "alter table <tablename> alter column <columnname> set data type <datatype>"tablename:表名columnname:字段名datatype:字段类型注: 一般更改字段类型是有操作限制的. 将字段改为比之前类型长度大的可以;如果要改小,必须先drop掉原来的column,然后再重新添加. 虽然我上面的执行成功了,可能是因为我用的官方免费版本的,如果遇到把长度大的改为小的无法执行,先用drop,再重新添加20.向一个表添加字段(如向表people中添加备注信息字段notes;向表people中添加分数字段score)格式:db2 "alter table <tablename> add <columnname> <datatype>"[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 30 0 Yes AGE SYSIBM INTEGER 4 0 Yes 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE ----------- ------------------------------ -----------1 Tom 222 Jack 213 Sunrier 25 3 record(s) selected.[db2inst1@localhost ~]$ db2 "alter table people add notes varchar(100)"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------ --------- ----------------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 36 0 Yes AGE SYSIBM INTEGER 4 0 Yes NOTES SYSIBM VARCHAR 100 0 Yes 4 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people" ID FNAME AGE NOTES ----------- ------------------------------------ 1 Tom 22 - 2 Jack 21 - 3 Sunrier 25 - 3 record(s) selected.[db2inst1@localhost ~]$ db2 "alter table people add score integer"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls ------------------------------- --------- -------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 36 0 Yes AGE SYSIBM INTEGER 4 0 Yes NOTES SYSIBM VARCHAR 100 0 Yes SCORE SYSIBM INTEGER 4 0 Yes 5 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE NOTES SCORE -------- ----------------- ----------- --------------1 Tom 22 - -2 Jack 21 - -3 Sunrier 25 - - 3 record(s) selected.[db2inst1@localhost ~]$21.删除表中的某个字段(如删除表people中的字段score)格式:db2 "alter table <tablename> drop column <columnname>"[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls ------------------------------- --------- ------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 36 0 Yes AGE SYSIBM INTEGER 4 0 Yes NOTES SYSIBM VARCHAR 100 0 Yes SCORE SYSIBM INTEGER 4 0 Yes 5 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE NOTES SCORE ----------- ----------------- ----------- --------------1 Tom 22 - -2 Jack 21 - -3 Sunrier 25 - - 3 record(s) selected.[db2inst1@localhost ~]$ db2 "alter table people drop column score"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------- --------- ------------------- ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 36 0 Yes AGE SYSIBM INTEGER 4 0 Yes NOTES SYSIBM VARCHAR 100 0 Yes 4 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE NOTES ------ -------------- ----------- --------------------1 Tom 22 - 2 Jack 21 - 3 Sunrier 25 - 3 record(s) selected.[db2inst1@localhost ~]$注:如果drop掉字段之后,可能会导致表查询/插入操作不能执行,则需要执行一下reorg命令,优化数据结构,格式如db2 reorg table <tablename>[db2inst1@localhost ~]$ db2 reorg table peopleDB20000I The REORG command completed successfully.[db2inst1@localhost ~]$ 22.给表中添加带默认值的字段(如向表people中添加分数字段score,默认设置为90)格式:db2 "alter table <tablename> add column <columnname> <datatype> not null with default <value> "[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 36 0 Yes AGE SYSIBM INTEGER 4 0 Yes NOTES SYSIBM VARCHAR 100 0 Yes 4 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE NOTES ------- -------------- ----------- --------------------1 Tom 22 - 2 Jack 21 - 3 Sunrier 25 - 3 record(s) selected.[db2inst1@localhost ~]$ db2 "alter table people add column score interger not null with default 90"DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 describe table people Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- ----------------ID SYSIBM INTEGER 4 0 Yes FNAME SYSIBM VARCHAR 36 0 Yes AGE SYSIBM INTEGER 4 0 Yes NOTES SYSIBM VARCHAR 100 0 Yes SCORE SYSIBM INTEGER 4 0 No 5 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from people"ID FNAME AGE NOTES SCORE ----------- ------------------------------------ ------1 Tom 22 - 902 Jack 21 - 903 Sunrier 25 - 90 3 record(s) selected.[db2inst1@localhost ~]$ 例1.向表people中添加地址字段address默认设置为shanghaidb2 "alter table people add column address varchar(30) not null with default 'ShangHai'"例2.将表people中地址字段address默认设置改为当前时间db2 "alter table people alter column address set default current date"格式:db2 "alter table <tablename> alter column <columnname> set default <value>"23.列出数据库中用户表[db2inst1@localhost ~]$ db2 list tables for userTable/View Schema Type Creation time ------------------------------- --------------- ---STUDENT DB2INST1 T 2012-08-06-15.26.17.189538 1 record(s) selected.[db2inst1@localhost ~]$24.列出数据库中所有系统表[db2inst1@localhost ~]$ db2 list tables for systemTable/View Schema Type Creation time ------------------------------- --------------- -----ATTRIBUTES SYSCAT V 2012-08-06-14.20.49.182036AUDITPOLICIES SYSCAT V 2012-08-06-14.20.49.237474AUDITUSE SYSCAT V 2012-08-06-14.20.49.243535BUFFERPOOLDBPARTITIONS SYSCAT V 2012-08-06-14.20.49.264336BUFFERPOOLNODES SYSCAT V 2012-08-06-14.20.49.277662BUFFERPOOLS SYSCAT V 2012-08-06-14.20.49.281563CASTFUNCTIONS SYSCAT V 2012-08-06-14.20.49.309007CHECKS SYSCAT V 2012-08-06-14.20.49.314078..............................................................................................................................................................................ROUTINES SYSSTAT V 2012-08-06-14.20.52.502569TABLES SYSSTAT V 2012-08-06-14.20.52.510027HMON_ATM_INFO SYSTOOLS T 2012-08-06-14.35.41.029633HMON_COLLECTION SYSTOOLS T 2012-08-06-14.35.41.208925POLICY SYSTOOLS T 2012-08-06-14.35.40.156347 396 record(s) selected.[db2inst1@localhost ~]$25.列出数据库中所有表[root@localhost ~]# su - db2inst1[db2inst1@localhost ~]$ db2startSQL1026N The database manager is already active.[db2inst1@localhost ~]$ db2 list active databasesSQL1611W No data was returned by Database System Monitor.[db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST[db2inst1@localhost ~]$ db2 list tables for allTable/View Schema Type Creation time ------------------------------- --------------- -----STUDENT DB2INST1 T 2012-08-06-15.26.17.189538ATTRIBUTES SYSCAT V 2012-08-06-15.21.20.819408AUDITPOLICIES SYSCAT V 2012-08-06-15.21.20.886143AUDITUSE SYSCAT V 2012-08-06-15.21.20.911042BUFFERPOOLDBPARTITIONS SYSCAT V 2012-08-06-15.21.20.936300BUFFERPOOLNODES SYSCAT V 2012-08-06-15.21.20.957929BUFFERPOOLS SYSCAT V 2012-08-06-15.21.20.978954CASTFUNCTIONS SYSCAT V 2012-08-06-15.21.21.011517CHECKS SYSCAT V 2012-08-06-15.21.21.036428COLAUTH SYSCAT V 2012-08-06-15.21.21.061589COLCHECKS SYSCAT V 2012-08-06-15.21.21.094771COLDIST SYSCAT V 2012-08-06-15.21.21.114126.........................................................................................................................................................FUNCTIONS SYSSTAT V 2012-08-06-15.21.25.272699INDEXES SYSSTAT V 2012-08-06-15.21.25.289851ROUTINES SYSSTAT V 2012-08-06-15.21.25.315173TABLES SYSSTAT V 2012-08-06-15.21.25.320565HMON_ATM_INFO SYSTOOLS T 2012-08-06-15.30.39.352789HMON_COLLECTION SYSTOOLS T 2012-08-06-15.30.39.498061POLICY SYSTOOLS T 2012-08-06-15.30.38.749121 397 record(s) selected.[db2inst1@localhost ~]$26.列出数据库中特定用户表[root@localhost ~]# su - db2inst1[db2inst1@localhost ~]$ db2startSQL1026N The database manager is already active.[db2inst1@localhost ~]$ db2 list active databasesSQL1611W No data was returned by Database System Monitor.[db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST[db2inst1@localhost ~]$ db2 list tables for schema db2inst1Table/View Schema Type Creation time ---------------------- --------------- ----- --------------------------STUDENT DB2INST1 T 2012-08-06-15.26.17.189538 1 record(s) selected.[db2inst1@localhost ~]$ db2 list tables for schema db2inst2Table/View Schema Type Creation time ------------------------------- --------------- ----- -------------------------- 0 record(s) selected.[db2inst1@localhost ~]$ 格式: db2 list tables for schema <username>注:符号<>表示必选项27.删除表中的数据 :[db2inst1@localhost ~]$ db2 delete from student where id=3 DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 "select * from student"ID FNAME AGE ----------- ------------------------------ ----------- 1 Tom 22 2 Jack 21 2 record(s) selected.[db2inst1@localhost ~]$28.删除一个数据库中的某个表 [db2inst1@localhost ~]$ db2 drop table studentDB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time 0 record(s) selected.[db2inst1@localhost ~]$29.删除一个数据库test[db2inst1@localhost ~]$ db2 list db directory System Database Directory Number of entries in the directory = 1Database 1 entry: Database alias = TEST Database name = TEST Local database directory = /home/db2inst1 Database release level = d.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =[db2inst1@localhost ~]$ db2 drop db test SQL1035N The database is currently in use. SQLSTATE=57019SQL1025N The database manager was not stopped because databases are still active.[db2inst1@localhost ~]$ db2 connect resetDB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 drop db test DB20000I The DROP DATABASE command completed successfully.[db2inst1@localhost ~]$ db2 list db directorySQL1057W The system database directory is empty. SQLSTATE=01606[db2inst1@localhost ~]$ 注:删除数据库首先要断开数据库的连接30.显示当前数据库连接有哪些应用程序[root@localhost ~]# su - db2inst1[db2inst1@localhost DB2]$ db2 list applicationSQL1611W No data was returned by Database System Monitor.[db2inst1@localhost DB2]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1Local database alias = TEST[db2inst1@localhost DB2]$ db2 list applicationAuth Id Application Appl. Application Id DB # of Name Handle Name Agents-------- -------------- ---------- ----------------------------DB2INST1 db2bp 35 *LOCAL.db2inst1.120807014245 TEST 1 [db2inst1@localhost DB2]$31.查看DB2全部受支持的注册表变量列表[db2inst1@localhost DB2]$ db2set -lrDB2_OVERRIDE_BPFDB2_PARALLEL_IODB2ACCOUNTDB2ADMINSERVERDB2BQTIMEDB2BQTRY...........................................................................................................................DB2TCP_CLIENT_KEEPALIVE_TIMEOUTDB2_PMODEL_SETTINGSDB2_PMAP_COMPATIBILITYDB2_HADR_ROSDB2_STANDBY_ISO[db2inst1@localhost DB2]$32.更改DB2 UDB注册表变量的值db2set registry_variable_name=new_value[db2inst1@localhost ~]$ db2set DB2COMM=TCPIP[db2inst1@localhost ~]$33.查看在服务器上已经设置的所有DB2概要文件注册表[db2inst1@localhost DB2]$ db2set -all[i] DB2PROCESSORS=0[i] DB2COMM=TCPIP[i] DB2AUTOSTART=YES[g] DB2SYSTEM=localhost.localdomain[g] DB2INSTDEF=db2inst1[g] DB2ADMINSERVER=db2dasusr1[db2inst1@localhost DB2]$34.导出表中的数据以DEL格式导出db2 "export to teacher.txt of del select * from teacher"db2 "export to teacher_bak.txt of del modified by coldel| select * from teacher"[db2inst1@localhost ~]$ lsdb2inst1 sqllib Sunrier teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001 [db2inst1@localhost ~]$ db2 "export to teacher.txt of del select * from teacher"SQL3104N The Export utility is beginning to export data to file "teacher.txt".SQL3105N The Export utility has finished exporting "2" rows.Number of rows exported: 2[db2inst1@localhost ~]$ lsdb2inst1 sqllib Sunrier teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001 [db2inst1@localhost ~]$ cat teacher.txt 1,"Lory","上海徐汇中学",197808062,"Sunrier","田林中学",19880627[db2inst1@localhost ~]$字段之间默认分隔符号为逗号,下面使用'|'分割[db2inst1@localhost ~]$ db2 "export to teacher_bak.txt of del modified by coldel| select * from teacher"SQL3104N The Export utility is beginning to export data to file "teacher_bak.txt".SQL3105N The Export utility has finished exporting "2" rows.Number of rows exported: 2[db2inst1@localhost ~]$ lsdb2inst1 sqllib Sunrier teacher_bak.txt teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001[db2inst1@localhost ~]$ cat teacher_bak.txt 1|"Lory"|"上海徐汇中学"|197808062|"Sunrier"|"田林中学"|19880627[db2inst1@localhost ~]$以IXF格式导出 [db2inst1@localhost ~]$ pwd/home/db2inst1[db2inst1@localhost ~]$ lsdb2inst1 sqllib Sunrier teacher_bak.txt teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001[db2inst1@localhost ~]$ ls /home/db2inst1/SunrierTEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001[db2inst1@localhost ~]$ db2 "export to /home/db2inst1/Sunrier/teacher.ixf of ixf select * from teacher"SQL3104N The Export utility is beginning to export data to file "/home/db2inst1/Sunrier/teacher.ixf".SQL3105N The Export utility has finished exporting "4" rows.Number of rows exported: 4[db2inst1@localhost ~]$ ls /home/db2inst1/Sunrierteacher.ixf TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001[db2inst1@localhost ~]$ 如需要导出记录过程的message[db2inst1@localhost ~]$ db2 "export to /home/db2inst1/Sunrier/teacher_bak.ixf of ixf messages /home/db2inst1/Sunrier/teacher.msg select * from teacher"Number of rows exported: 4[db2inst1@localhost ~]$ ls /home/db2inst1/Sunrierteacher_bak.ixf teacher.ixf teacher.msg TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001[db2inst1@localhost ~]$ cat /home/db2inst1/Sunrier/teacher.msg SQL3104N The Export utility is beginning to export data to file "/home/db2inst1/Sunrier/teacher_bak.ixf".SQL3105N The Export utility has finished exporting "4" rows.[db2inst1@localhost ~]$35.导入数据到一个表中db2 "import from teacher.txt of del insert into teacher"db2 "import from teacher_bak.txt of del modified by coldel| insert into teacher"[db2inst1@localhost ~]$ lsdb2inst1 sqllib Sunrier teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001 [db2inst1@localhost ~]$ cat teacher.txt3,"Jerry","上海徐汇中学",197103064,"Tim","田林中学",19820627[db2inst1@localhost ~]$ db2 "select * from teacher"ID FNAME ADDRESS BIRTH ----------- -------------------- -------------------------------1 Lory 上海徐汇中学 1978-08-062 Sunrier 田林中学 1988-06-27 2 record(s) selected.[db2inst1@localhost ~]$ db2 "import from teacher.txt of del insert into teacher"SQL3109N The utility is beginning to load data from file "teacher.txt".SQL3110N The utility has completed processing. "2" rows were read from theinput file.SQL3221W ...Begin COMMIT WORK. Input Record Count = "2".SQL3222W ...COMMIT of any database changes was successful.SQL3149N "2" rows were processed from the input file. "2" rows weresuccessfully inserted into the table. "0" rows were rejected.Number of rows read = 2Number of rows skipped = 0Number of rows inserted = 2Number of rows updated = 0Number of rows rejected = 0Number of rows committed = 2[db2inst1@localhost ~]$ db2 "select * from teacher" ID FNAME ADDRESS BIRTH ----------- -------------------- ----------------------------- 1 Lory 上海徐汇中学 1978-08-06 2 Sunrier 田林中学 1988-06-27 3 Jerry 上海徐汇中学 1971-03-06 4 Tim 田林中学 1982-06-27 4 record(s) selected.[db2inst1@localhost ~]$36.利用脚本创建表格式:db2 -tvf scriptName.sqlteacher.sql为以下内容----建立表teacher create table teacher ( id integer not null , fname varchar(20) not null, address varchar(80) default '上海',birth date, primary key(id)); --建表结束--以下为插入数据字段insert into teacher values(1,'Lory','上海徐汇中学','1978-08-06');insert into teacher values(2,'Sunrier','田林中学','1988-06-27');[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ------------------------------- --------------- PEOPLE DB2INST1 T 2012-08-16-15.13.49.396370STUDENT DB2INST1 T 2012-08-06-15.26.17.189538 2 record(s) selected.[db2inst1@localhost ~]$ db2 -tvf teacher.sql create table teacher ( id integer not null , fname varchar(20) not null, address varchar(80) default '上海', birth date, primary key(id) )DB20000I The SQL command completed successfully.insert into teacher values(1,'Lory','上海徐汇中学','1978-08-06')DB20000I The SQL command completed successfully.insert into teacher values(2,'Sunrier','田林中学','1988-06-27')DB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 describe table teacher Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- -----------ID SYSIBM INTEGER 4 0 No FNAME SYSIBM VARCHAR 20 0 No ADDRESS SYSIBM VARCHAR 80 0 Yes BIRTH SYSIBM DATE 4 0 Yes 4 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from teacher"ID FNAME ADDRESS BIRTH ----------- -------------------- ---------------------------- 1 Lory 上海徐汇中学 1978-08-06 2 Sunrier 田林中学 1988-06-27 2 record(s) selected.[db2inst1@localhost ~]$37.备份数据库(如防止表误操作)格式:db2 backup db <database name> [ to <dir name> ]database name:表示数据库to <dir name>:表示为备份到的目录路径,为可选项,默认在当前目录下[db2inst1@localhost ~]$ ls db2inst1 sqllib teacher.sql [db2inst1@localhost ~]$ db2 list db directory System Database Directory Number of entries in the directory = 1Database 1 entry: Database alias = TEST Database name = TEST Local database directory = /home/db2inst1Database release level = d.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =[db2inst1@localhost ~]$ db2 backup db test Backup successful. The timestamp for this backup image is : 20120817103306 [db2inst1@localhost ~]$ lsdb2inst1 sqllib teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001[db2inst1@localhost ~]$ db2 list active databasesSQL1611W No data was returned by Database System Monitor.[db2inst1@localhost ~]$注:TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001即为备份的数据库节点文件; 执行备份命令时,如果出现无法执行,则先断开数据库的连接再执行备份命令. 我使用的免费版本的DB2数据库测试时,从上面可以看出没有断开也可以执行,但执行完,发现数据库处于断开状态了如: db2 force application all 强制关闭实例上的所有应用程序[db2inst1@localhost ~]$ lsdb2inst1 sqllib Sunrier teacher_bak.txt teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001[db2inst1@localhost ~]$ db2 backup db test to /home/db2inst1/SunrierBackup successful. The timestamp for this backup image is : 20120817150317[db2inst1@localhost ~]$ lsdb2inst1 sqllib Sunrier teacher_bak.txt teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001[db2inst1@localhost ~]$ cd Sunrier/[db2inst1@localhost Sunrier]$ lsTEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001[db2inst1@localhost Sunrier]$ 说明:上面的方法为脱机备份(也称为离线备份或者冷备份),此方法必须断开所有与数据库连接的应用后才能进行,备份时数据库不能提供给用户使用. 38.恢复数据库(如将一个表删除后,通过删除前的备份文件恢复)格式:db2 restore db <database name> [ from <dir name> ]database name:表示恢复的数据库名from <dir name>:表示为从哪个目录路径下恢复,为可选项,默认在当前目录下[db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ---------------------- --------------- ----- -----PEOPLE DB2INST1 T 2012-08-16-15.13.49.396370STUDENT DB2INST1 T 2012-08-06-15.26.17.189538TEACHER DB2INST1 T 2012-08-17-10.18.18.245263 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from teacher"ID FNAME ADDRESS BIRTH ----------- -------------------- --------------------- 1 Lory 上海徐汇中学 1978-08-06 2 Sunrier 田林中学 1988-06-27 3 Jerry 上海徐汇中学 1971-03-06 4 Tim 田林中学 1982-06-27 4 record(s) selected.[db2inst1@localhost ~]$ db2 drop table teacherDB20000I The SQL command completed successfully.[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ------------------------------- --------------- -----PEOPLE DB2INST1 T 2012-08-16-15.13.49.396370STUDENT DB2INST1 T 2012-08-06-15.26.17.189538 2 record(s) selected.[db2inst1@localhost ~]$ db2 connect resetDB20000I The SQL command completed successfully. [db2inst1@localhost Sunrier]$ ls /home/db2inst1/SunrierTEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001[db2inst1@localhost ~]$ db2 restore db test from /home/db2inst1/SunrierSQL2539W 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) yDB20000I The RESTORE DATABASE command completed successfully.[db2inst1@localhost ~]$ db2 list tablesSQL1024N A database connection does not exist. SQLSTATE=08003[db2inst1@localhost ~]$ db2 connect to test Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TEST[db2inst1@localhost ~]$ db2 list tablesTable/View Schema Type Creation time ----------------- --------------- ----- --------------------------PEOPLE DB2INST1 T 2012-08-16-15.13.49.396370STUDENT DB2INST1 T 2012-08-06-15.26.17.189538TEACHER DB2INST1 T 2012-08-17-10.18.18.245263 3 record(s) selected.[db2inst1@localhost ~]$ db2 "select * from teacher"ID FNAME ADDRESS BIRTH ----------- -------------------- ---------------------- 1 Lory 上海徐汇中学 1978-08-06 2 Sunrier 田林中学 1988-06-27 3 Jerry 上海徐汇中学 1971-03-06 4 Tim 田林中学 1982-06-27 4 record(s) selected.[db2inst1@localhost ~]$注:如果想把恢复的数据库更改为新的数据库名,则格式如下 db2 restore db <database name> [ from <dir name> into <new database name> ]例:db2 restore db test from /home/db2inst1/Sunrier into testdb或者db2 restore db test from "/home/db2inst1/Sunrier" into testdb39.db2move命令的使用db2move是一个集成式的数据移动工具,它具有导入(import),导出(export),装入(load)三种操作方法.db2move导出的数据文件格式是IXF(Integration Exchange Format)集成交换格式.格式:db2move <database name> [ export -tc <username> ] -u <username> -p <password>参数: -tc 创建表的用户名 -tn 用户的表名 -sn 模式名,即导出该模式下的所有表 查看db2move命令帮助db2move -help导出test数据库中的全部数据[db2inst1@localhost ~]$ lsdb2inst1 Sunrier teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001sqllib teacher_bak.txt teacher.txt[db2inst1@localhost ~]$ db2move test export -u db2inst1 -p SunrierApplication code page not determined, using ANSI codepage 1208***** DB2MOVE *****Action: EXPORTStart time: Fri Aug 17 15:58:34 2012Connecting to database TEST ... successful! Server : DB2 Common Server V9.7.1Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!EXPORT: 136 rows from table "SYSTOOLS"."HMON_ATM_INFO"EXPORT: 0 rows from table "SYSTOOLS"."HMON_COLLECTION"EXPORT: 3 rows from table "DB2INST1"."PEOPLE"EXPORT: 5 rows from table "SYSTOOLS"."POLICY"EXPORT: 3 rows from table "DB2INST1"."STUDENT"EXPORT: 4 rows from table "DB2INST1"."TEACHER"Disconnecting from database ... successful!End time: Fri Aug 17 15:58:35 2012[db2inst1@localhost ~]$ lsdb2inst1 sqllib tab1.msg tab3.ixf tab4.ixf tab5.msg teacher_bak.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001db2move.lst Sunrier tab2.ixf tab3.msg tab4.msg tab6.ixf teacher.sqlEXPORT.out tab1.ixf tab2.msg tab4a.001.lob tab5.ixf tab6.msg teacher.txt[db2inst1@localhost ~]$注:执行上面命令后会把数据库test中全部数据提取到当前目录(/home/db2inst1),每个表的内容都存储在一个.ixf文件中, 每个.ixf文件都有一个与之相对应的.msg文件,.msg文件是描述从表中导出数据时的信息.例外还有两个文件,db2move.lst用来记录.ixf文件,.msg文件与表一一对应.EXPORT.out记录的是导出数据时的屏幕输出.导出test数据库中的teacher表中的信息 [db2inst1@localhost ~]$ lsdb2inst1 Sunrier teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001 sqllib teacher_bak.txt teacher.txt[db2inst1@localhost ~]$ db2move test export -tn teacher -u db2inst1 -p SunrierApplication code page not determined, using ANSI codepage 1208***** DB2MOVE *****Action: EXPORTStart time: Fri Aug 17 16:33:24 2012All table names matching: TEACHER;Connecting to database TEST ... successful! Server : DB2 Common Server V9.7.1EXPORT: 4 rows from table "DB2INST1"."TEACHER"Disconnecting from database ... successful!End time: Fri Aug 17 16:33:24 2012[db2inst1@localhost ~]$ lsdb2inst1 EXPORT.out Sunrier tab1.msg teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001db2move.lst sqllib tab1.ixf teacher_bak.txt teacher.txt[db2inst1@localhost ~]$ 40.查看test数据库备份的历史记录格式:db2 list history backup all for <database name>[db2inst1@localhost ~]$ db2 list history backup all for test List History File for testNumber of matching file entries = 4 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID-- --- ------------------ ---- --- ------------ ------- B D 20120817103306001 F D S0000000.LOG S0000000.LOG ----------------------------------------------------------- Contains 3 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE -----------------------------------------------------------Comment: DB2 BACKUP TEST OFFLINE Start Time: 20120817103306 End Time: 20120817103315 Status: A -----------------------------------------------------EID: 4 Location: /home/db2inst1Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -- B D 20120817150248000 F S0000000.LOG --------------------------------------------------------------- Contains 3 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE -----------------------------------------------------------------Comment: DB2 BACKUP TEST OFFLINE Start Time: 20120817150248 End Time: 20120817150249 Status: A -------------------------------------------------------------EID: 5 Location:Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ B D 20120817150317001 F D S0000000.LOG S0000000.LOG ---------------------------------------------------------------Contains 3 tablespace(s):00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE --------------------------------------------------------------Comment: DB2 BACKUP TEST OFFLINE Start Time: 20120817150317 End Time: 20120817150323 Status: A ------------------------------------------------------------EID: 6 Location: /home/db2inst1/SunrierOp Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID-- --- ------------------ ---- --- ------------ ----------B D 20120817150825001 F D S0000000.LOG S0000000.LOG ---------------------------------------------------------------- Contains 3 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE -------------------------------------------------------------Comment: DB2 BACKUP TEST OFFLINE Start Time: 20120817150825 End Time: 20120817150832 Status: I ------------------------------------------------------------ EID: 7 Location: /home/db2inst1/SunrierOp Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ---------- R D 20120817151620001 F S0000000.LOG S0000000.LOG 20120817150317 ----------------------------------------------------------- Contains 3 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE -----------------------------------------------------------Comment: RESTORE TEST NO RF Start Time: 20120817151620 End Time: 20120817151629 Status: A ----------------------------------------------------------- EID: 8 Location:[db2inst1@localhost ~]$41.读数据库管理程序配置db2 get dbm cfg42.写数据库管理程序配置db2 update dbm cfg using 参数名 参数值43.查看数据库的配置db2 connect to <database> user <username> using <password>db2 get db cfg [ for <database> ]database:数据库名username:用户名password:表示密码44.设置数据库的配置db2 connect to <database> user <username> using <password>db2 update db cfg for <database> using 参数名 参数值45.添加DB2服务端口50000切换到root用户下su - root[root@localhost etc]# vi /etc/services 在/etc/services文件中加入db2inst1 50000/tcp/etc/services内容格式:# service-name port/protocol [aliases ...] [# comment]46.断开与数据库的连接 db2 connect reset 或 db2 terminate db2 disconnect <database>47.查看命令帮助[db2inst1@localhost ~]$ db2 ? db2start {START DATABASE MANAGER | DB2START} [REMOTE [INSTANCE] instance-name {ADMINNODE node-name | HOSTNAME hostname} USER username USING password] [ADMIN MODE {USER username | GROUP groupname }] [PROFILE profile] [DBPARTITIONNUM db-partition-number] [ADD DBPARTITIONNUM HOSTNAME hostnamePORT logical-port [COMPUTER computer-name] [USER username] [PASSWORD password][NETNAME netname] [LIKE DBPARTITIONNUM db-partition-number |WITHOUT TABLESPACES]] | STANDALONE | RESTART [HOSTNAME hostname][PORT logical-port] [COMPUTER computer-name] [USER username] [PASSWORD password][NETNAME netname] ] ]NOTE: From the operating system prompt, prefix commands with 'db2'. Special characters MAY require an escape sequence (\), for example: db2 \? change database db2 ? change database xxx comment with \"text\"[db2inst1@localhost ~]$ 格式:db2 ? <command>48.查看错误码信息[db2inst1@localhost ~]$ db2 ? 22003SQLSTATE 22003: A numeric value is out of range.[db2inst1@localhost ~]$ 格式:db2 ? <ERRCODE>49.待定,工作过程中遇到相关问题再补充......实例程序//db2dbproc.sqc[cpp] view plain copy/************************************************************ FileName : db2dbproc.sqc FileFunc : C语言接口访问本地DB2数据库 Version : V0.1 Author : Sunrier Date : 2012-08-06 Descp : Linux下使用C语言访问DB2函数 *************************************************************/ //(嵌入SQL语句的关键字不区分大小写) #include <stdio.h> #include <string.h> #include <sqlca.h> #define PARAERR 0x04 #define DATABASEERR 0x08 EXEC SQL include sqlca; int check_error(char szMessage[]) { if( sqlca.sqlcode ) { printf("Check error report : \n"); printf("Error occured : %s ,sqlcode = [%d] \n",szMessage,sqlca.sqlcode); return 1; } return 0; } int db2_login(int iArgcFlag,char *pUserName,char *pPassword,char *pDataBase) { int iRetCode = 1; EXEC SQL BEGIN DECLARE SECTION; char szUserName[50]; char szPassword[50]; char szDataBase[50]; EXEC SQL END DECLARE SECTION; memset(szUserName,0,sizeof(szUserName)); memset(szPassword,0,sizeof(szPassword)); memset(szDataBase,0,sizeof(szDataBase)); strcpy(szDataBase,pDataBase); if( 1==iArgcFlag ) { EXEC SQL CONNECT TO:szDataBase ; } else { if( 3==iArgcFlag ) { strcpy(szUserName,pUserName); strcpy(szPassword,pPassword); EXEC SQL CONNECT TO:szDataBase USER:szUserName USING:szPassword; } else { printf("Usage : argc found error !\n"); return PARAERR; } } iRetCode = check_error("CONNECT TO DATABASE"); return iRetCode; } void db2_logout( void ) { EXEC SQL CONNECT RESET; check_error("CONNECT RESET"); } //demo.c[cpp] view plain copy/************************************************************* FileName : demo.c FileFunc : 测试登陆和关闭DB2数据库 Version : V0.1 Author : Sunrier Date : 2012-08-06 Descp : Linux下使用C语言访问DB2函数 *************************************************************/ #include <stdio.h> int main(int argc,char *argv[]) { int iRetCode = 0; int iArgcFlag; char szUserName[20] = "Sunrier"; char szPassword[20] = "redhat"; char szDatabase[20] = "test"; iArgcFlag = 1; iRetCode = db2_login(iArgcFlag,szUserName,szPassword,szDatabase); printf("iRetCode = %d \n",iRetCode); if( iRetCode ) { printf("DB2 Connection failure !\n"); return iRetCode; } else { printf("DB2 Connection success !\n"); } db2_logout(); return 0; } //makefile[cpp] view plain copyOBJS = demo all:$(OBJS) DB2INCPATH=/home/db2inst1/sqllib/include DB2LIBPATH=/home/db2inst1/sqllib/lib demo:demo.c db2dbproc.sqc @db2 connect to test @db2 prep db2dbproc.sqc @gcc -I$(DB2INCPATH) -o demo demo.c db2dbproc.c -L$(DB2LIBPATH) -ldb2 @rm -rf db2dbproc.c clean: @ls | grep -v ^makefile|grepv[.]c|grep -v [.]h|grepv[.]sqc|grep -v [.]sql$$ | xargs rm -rf [db2inst1@localhost DB2]$ lsdbproc.sqc demo.c makefile[db2inst1@localhost DB2]$ make Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authorization ID = DB2INST1 Local database alias = TESTLINE MESSAGES FOR dbproc.sqc---------------------------------------------------------- SQL0060W The "C" precompiler is in progress. SQL0091W Precompilation or binding was ended with "0" errors and "0" warnings.[db2inst1@localhost DB2]$ lsdbproc.sqc demo demo.c makefile[db2inst1@localhost DB2]$ ./demoiRetCode = 0 DB2 Connection success ![db2inst1@localhost DB2]$
