DB2数据库常用指令以及重要知识点学习

DB2数据库基础知识

1、db2数据库特点
(1)具有很好的并行性（DB2把数据库管理扩充到了并行的，多节点的环境；数据库分区是数据库的一部分，包含自己的数据，索引，配置文件和事务日志；数据库分区有时被称为节点）。
(2)获得最高认证级别的ISO标准认证。
(3)性能较高，适用于数据仓库和在线事务处理。
(4)跨平台，多层结构，支持ODBC，JDBC等客户。
(5)操作简单，同时提供GUI和命令行，在windowsNT和unix下的操作相同。
(6)在巨型企业得到广泛的运用，向下兼容性好，风险小。
(7)能够在所有主流平台上运行，最适于海量数据处理。
2、db2数据库的特性
(1)完全Web使能的：可以利用HTTP来发送询问给服务器。
(2)高度可缩放和可靠：高负荷时可利用多处理器和大内存，可以跨服务器地分布数据库和数据负荷；能够以最小的数据丢失快速地恢复，提供多种备份策略。
3、实现储存过程
存储过程是存储在数据库中的一个预编译对象。这意味着过程是预编译的，可提供给各种应用执行。发送查询到服务器、分析和编译过程再不需要花费时间。
(1)特点：存储过程是SQL语句和控制流语句的一个集合或批量，它在一个名称下存储，按独立单元方式执行。它能帮助提高查询的性能。
(2)好处：
提高性能（应用不必重复地编译此过程）
减轻网络拥塞（为进行处理，应用不需要向服务器提交多个SQL语句）
一致性较好（由于过程作为单一控制点，在过程中定义的编码逻辑和SQL语句在所有应用中被一致地实现）
改善安全机制（用户可以被授予许可权来执行存储过程，尽管他们并不拥有这个过程）
(3)执行存储过程语句：call proc_name(param,…)
(4)RETURN关键字：允许存储过程把整型值返回给调用者。如果没有指定值，那么存储过程返回缺省值0或1，这依赖于存储过程的成功执行与否。RETURN value
(5)嵌套过程：可以执行或调用来自另一个过程的过程。
4、触发器
触发器：一个触发器是由SQL语句集组成的代码块，在响应某些动作时激活该语句集。一个触发器也可被解释为特定类型的存储过程，每当动作发生时执行该过程。每当基础表中数据受到数据操纵语言（DML）语句——INSERT、UPDATE或DELETE的影响时，触发器就被激发。触发器帮助维持表中数据的一致、可靠和正确。
(1)触发器特性：
当任何数据修改语句被发出时，它就被自动激发。
在存储过程的情况下，它不被显式地调用和执行。
它防止了对数据的不正确、未授权的和不一致的改变。
它不能返回数据给用户。
触发器可最多嵌套。当一个触发器执行对另一个触发器初始化动作时触发器的嵌套就产生了。
(2)触发器和数据完整性：
触发器可用来确保和加强业务规则和数据完整性。业务规则是指确保业务平稳运转的组织政策。数据完整性是指数据的精确性和可靠性。
如果某种改变违反了引用完整性，那么所有这样的改变都被拒绝，因此所有试图改变数据库中的数据都被取消。
允许实施非常复杂的限制。
它可执行依赖于对表所作修改的结果的特定的动作。
5、实现处理事务和游标
(1)理解DB2中的上锁
共享锁：允许并发事务来读取资源。如果一个资源上有任何共享锁的话，其它的事务都不能修改该资源上的数据。一个共享锁在数据已被事务读取后释放资源。
更新锁：更新（U）锁，在功能上避免了常见形式的死锁发生。
互斥型锁：排它型锁（X），在功能上唯一地限制并发事务访问一个资源，用排它锁时没有任何其它的事务可以读取或者修改已上锁的数据。
意向锁：指示SQL Server要在层次结构较低的某个资源上获得一个共享或排它锁。
带有各种特性的意向锁包括意向共享（IS），意向排它（IX）和带有意向排它的共享锁（SIX）。
模式锁：当任何数据定义（DDL）操作在表上执行时，SQL Server考察模式修改（Sch-M）锁。当编译查询时，SQL Server考察模式稳定（Sch-S）锁。
死锁：DEADLOCK_PRIORITY命令可定制死锁。把某个会话的DEADLOCK_PRIORITY设置为LOW，使得要选的某个会话变成死锁受害者。
(2)游标
游标是一个在给定结果集中帮助访问和操纵数据的数据库对象。游标的主要好处是你可以逐行的处理数据。结果集被定义为从SELECT语句中获得的行的集合。
声明游标：DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR {select_statement} [FOR {READ ONLY | UPDATE [OF column_list]}]
INSENSITIVE：使结果集存储在tempdb数据库中，且不允许修改游标的结果集。
SCROLL：指出游标可以向前或向后定位以读入数据。只要游标不是用INSENSITIVE选项声明，所有随后的取数显示基础表所作的修改。
READ ONLY：防止结果集中的修改。
UPDATE [OF column_list]：定义游标内可更新的列。如果使用OF column_list选项，只有列出可被更新的列；否则，除非游标被定义为READ ONLY，所有列都可更新。
打开游标：OPEN cursor_name
取数据：FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]] FROM cursor_name [INTO @variable_name [,…n]]
当一个FETCH语句执行时，一般认为游标定位在已被取到行的位置。被取到的行被称为当前行。0——成功执行了取数语句，1——执行取数语句不成功， 2——被读取的行丢失
关闭游标：CLOSE cursor_name 除了游标再打开的情况外，不可以进行读取和修改。
解除分配游标：DEALLOCATE cursor_name 所有游标持有的资源都被释放。
6、缓冲池
(1)定义：缓冲池是从磁盘读取表和索引数据时，数据库管理器分配的用于高速缓存这些表或索引数据的主存储器区域。
(2)创建：

CREATE BUFFERPOOL “BP04K_I1”

IMMEDIATE

ALL DBPARTITIONNUMS

SIZE 250

NUMBLOCKPAGES 0

PAGESIZE 32 K;

(3)修改:ALTER BUFFERPOOL SIZE

alter bufferpool IBMDEFAULTBP immediate size 100

(4)删除:删除缓冲池时，应确保没有任何表空间已指定给这些缓冲池。不能删除IBMDEFAULTBP缓冲池。

DROP BUFFERPOOL

(5)查看：select * from SYSCAT.BUFFERPOOLS;

7、表空间
(1)定义：表空间是一种存储结构，它包含表，索引，大对象和长型数据，表空间位于数据库分区组中。空间是数据库及存储在该数据库中的表之间的逻辑层。表空间在数据库中创建，表在表空间中创建。
(2)DB2 的表空间按管理方式分为两种：系统管理空间（System Management Space，SMS）和数据库管理空间（Database Management Space，DMS）。 按类型分为：规则表空间、大对象表空间、系统临时表空间、用户临时表空间。
(3)好处：能够把数据合理的分布存储在不同的磁盘上或者存储在磁盘的不同位置上，有助于提高数据存取的效率。
8、db2数据库调优
(1)在where 语句后面字段不要用函数，这样导致字段不走索引
(2)一个sql语句不要写的太长，尽量分临时表，一步一步写。
(3)表不要建太多的索引
(4)修改生产字段 一定要记得 REORG ->RUNSTATS -> 否则容易产生一些莫名其妙的错误。
(5)游标使用 注意commit 和rollback
(6)case when的用法 coalesce(username,’’) value(username,’’)
(7)插入或者删除太慢，随时记得表维护
(8)动态sql能自动选择最佳的执行计划。
(9)数据库优化命令：reorg、runstats 清除已delete的数据，优化数据结构
扩展：
数据仓库（Data Warehouse）： 是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、
反映历史变化（Time Variant）的数据集合，用于支持管理决策(Decisionaking Support)。
ODS：是一个面向主题的、集成的、可变的、当前的细节数据集合，用于支持企业对于即时性的、操作性的、集成的全体信息的需求。 区别：ods是短期的实时的数据，供产品或者运营人员日常使用，而数据仓库是供战略决策使用的数据；ods是可以更新的数据，数据仓库是基本不更新的反应历史变化的数据。

DB2数据库常用指令

因为工作的原因，接触到之前没有了解太多的DB2数据库，借此学习机会可以对DB2数据库常用指令进行一个简单的知识点总结。
常用指令：
1.改变列的长度

   db2 "alter table [tablename] alter column [columnname] set data type varchar(length)"

2.在表中加列

db2 "alter table [tablename] add [columnname]  varchar(256)"

3.修改列名
a.添加新列

db2 "alter table [tablename] add [columnname]  varchar(256)"

b.把旧列的数据更新到新列

update [tablename] set 新列=旧列

c.删除新列

 db2 "alter   table   [tablename]   drop   column   [columnname]

4.打开命令行窗口
　 #db2cmd
5.打开控制中心
　 # db2cmd db2cc
6.打开命令编辑器
　db2cmd db2ce
7.启动数据库实例
　 #db2start
8.停止数据库实例
　 #db2stop
如果你不能停止数据库由于激活的连接，在运行db2stop前执行db2 force application all就可以了 /db2stop force
9.创建数据库
　 #db2 create db [dbname]
10.连接到数据库
　 #db2 connect to [dbname] user [username] using [password]
11.断开数据库连接
　 #db2 connect reset
12.列出所有数据库
　#db2 list db directory
13.列出所有激活的数据库
　 #db2 list active databases
14.列出所有数据库配置
　 #db2 get db cfg
15.删除数据库
　 #db2 drop database [dbname]
执行此操作要小心，如果不能删除，断开所有数据库连接或者重启db2
16. 列出所有用户表
　 #db2 list tables
17.列出所有系统表
　#db2 list tables for system
18.列出所有表
　 #db2 list tables for all
19.列出用户表
　 #db2 list tables for user
20.列出特定用户表
　 #db2 list tables for schema [user]
21.创建一个与数据库中某个表(t2)结构相同的新表(t1)
　 #db2 create table t1 like t2
22.将一个表t1的数据导入到另一个表t2
#db2 “insert into t1 select * from t2”
23.查询表
　 #db2 "select * from table name where …"
24.显示表结构
　 #db2 describe table tablename
25.修改列
　 #db2 alter table [tablename] alter column [columname] set data type varchar(24)
26.执行脚本文件
　 #db2 -tvf scripts.sql
27.帮助命令

• 查看命令帮助
  　#db2 ? db2start
• 查看错误码信息
  #db2 ? 22001
• memo: 详细命令请使用"db2 ? <command>"进行查看。

28.备份数据库
#db2 backup db <db name>
备注：执行以上命令之前需要断开数据库连接
29.在线备份数据库
#db2 -v “BACKUP DATABASE <database name> ONLINE TO <path> WITH 2 BUFFERS BUFFER 1024 INCLUDE LOGS WITHOUT PROMPTING”
30.恢复数据库
#db2 restore db <source db name>
31.在线恢复数据库
#db2 “RESTORE DB <database name> TO <db path> LOGTARGET <logpath> WITHOUT PROMPTING”
#db2 “ROLLFORWARD DB <database name> TO END OF LOGS AND STOP” …
32.导出数据文件

  #db2move <db name> export 

33.导入数据文件
#db2move <db name> import
34.获取db2数据库管理配置环境信息
#db2 get dbm cfg
35.获取db2某个数据库数据库管理配置环境信息
#db2 get db cfg for <db name>
或者：连接至某个数据库以后执行db2 get db cfg
36.更改db2日志空间的大小
以下命令为了防止db2数据库过份使用硬盘空间而设，仅用于开发者自己机器上的db2，如果是服务器，则参数需要修改。
#db2 UPDATE DB CFG FOR <db name> USING logretain OFF logprimary 3 logsecond 2 logfilsiz 25600;
如果页大小是4KB，则以上命令创建3个100M的日志文件，占用300MB硬盘空间。25600*4KB=102400KB。
37.创建临时表空间

   #DB2 CREATE USER TEMPORARY TABLESPACE STMASPACE PAGESIZE 32 K MANAGED BY DATABASE USING (FILE 'D:\DB2_TAB\STMASPACE.F1' 10000) 
   EXTENTSIZE 256 

38.获取数据库管理器的快照数据
#db2 –v get snapshot for dbm
39.显示进行程号
#db2 list applications show detail
40.加载数据：

• 以默认分隔符加载,默认为“,”号

db2 "import from btpoper.txt of del insert into btpoper" 

• 以指定分隔符“|”加载

 db2 "import from btpoper.txt of del modified by coldel| insert into btpoper" 

41.卸载数据：

• 卸载一个表中全部数据

db2 "export to btpoper.txt of del select * from btpoper" 
db2 "export to btpoper.txt of del modified by coldel| select * from btpoper" 

• 带条件卸载一个表中数据

 db2 "export to btpoper.txt of del select * from btpoper where brhid='907020000'" 
 db2 "export to cmmcode.txt of del select * from cmmcode where codtp='01'" 
 db2 "export to cmmcode.txt of del modified by coldel| select * from cmmcode where codtp='01'" 

42.查询数据结构及数据：

 db2 "select * from btpoper" 
 db2 "select * from btpoper where brhid='907020000' and oprid='0001'" 
 db2 "select oprid,oprnm,brhid,passwd from btpoper" 

43.删除表中数据：

 db2 "delete from btpoper" 
 db2 "delete from btpoper where brhid='907020000' or brhid='907010000'" 

44.修改表中数据：

db2 "update svmmst set prtlines=0 where brhid='907010000' and jobtp='02'" 
db2 "update svmmst set prtlines=0 where jobtp='02' or jobtp='03'" 

45.联接数据库

 db2 connect to btpdbs

46.清除数据库联接

 db2 connect reset 断开数据库连接 
 db2 terminate 断开数据库连接 
 db2 force applications all 断开所有数据库连接 

47.备份数据库

db2 backup db btpdbs 

48.恢复数据库

db2 restore db btpdbs without rolling forward 

49.DB2帮助命令：

   db2 ? 
   db2 ? restroe 
   db2 ? sqlcode (例：db2 ? sql0803) 注：code必须为4位数，不够4位，前面补0 

50.bind命令：将应用程序与数据库作一捆绑,每次恢复数据库后，建议都要做一次bind

(1) db2 bind br8200.bnd 
(2) /btp/bin/bndall /btp/bnd 
    /btp/bin/bndall /btp/tran/bnd 

51.查看数据库参数：

   db2 get dbm cfg 
   db2 get db cfg for btpdbs 

52.修改数据库参数：

   db2 update db cfg for btpdbs using LOGBUFSZ 20 
   db2 update db cfg for btpdbs using LOGFILSIZ 5120 
改完后，应执行以下命令使其生效： 
   db2 stop 
   db2 start 

补充指令：
db2 set schema btp 修改当前模式为"btp"
db2 list tablespaces show detail 查看当前数据库表空间分配状况
db2 list tablespace containers for 2 show detail 查看tablespace id=2使用容器所在目录
db2 list application
db2 list db directory 列出所有数据库
db2 list active databases 列出所有活动的数据库
db2 list tables for all 列出当前数据库下所有的表
db2 list tables for schema btp 列出当前数据库中schema为btp的表
db2 list tablespaces show detail 显示数据库空间使用情况
删除一个实例：
cd /usr/lpp/db2_07_01/instance
./db2idrop InstName
列出所有DB2实例：
cd /usr/lpp/db2_07_01/bin
./db2ilist
为数据库建立编目
db2 catalog db btpdbs on /db2catalog
取消已编目的数据库btpdbs
db2 uncatalog db btpdbs
查看版本
db2level
显示当前数据库管理实例
db2 get instance
设置实例系统启动时是否自动启动。
db2iauto -on 自动启动
db2iauto -off 不自动启动
数据库优化命令：
reorg、runstats
当数据库经过一段时间使用，数据空间会变得越来越庞大。一些delete掉的数据仍存放在数据库中，占用数据空间，影响系统性能。因此需要定期运行reorg、runstats命令，清除已delete的数据，优化数据结构。
db2 reorg table 表名
db2 runstats on table 表名 with distribution and indexes all
因为要优化的表比较多，所以在/btp/bin目录下提供了一个sh程序runsall，可在当天业务结束后，运行runsall，对数据库进行优化。

DB2数据库事务隔离级别问题

数据库隔离级别

关于隔离级别的说明，使用到表t_bs_user，该表有如下字段：id，name，zt。
未提交读：
事务A读取到t_bs_user表中的id为1的数据zt为0，此时事务B修改了id为1的数据，将zt置为1，但是没有提交，事务A再次读取t_bs_user表的数据，发现zt为1，如果事务B做了回滚操作，那么事务A读取的将是脏数据。脏读可以通过增加事务隔离级别来避免。
提交读：
事务A读取到t_bs_user表中的id为1的数据zt为0，此时事务B修改了id为1的数据，将zt置为1，并提交，事务A再次读取t_bs_user表的数据，发现zt为1。这样就发生了不可重复读，事务A两次读取的数据不一样。不可重复读可以通过增加事务隔离级别来避免。
重复读：
事务A读取到t_bs_user表中的id为1的数据zt为0，并锁定此行数据，其他事务将无法对此行数据进行修改，但可以insert新的数据，事务A再次读取t_bs_user表中的数据，将会发现有新的数据，这个现象就是“幻读”。
序列化：
各个事务依次执行，不仅可以避免脏读、不可重复读，还避免了幻读。事务隔离级别最高，代价高，性能低，一般很少使用。

db2锁
在关系型数据库（BD2,Oracle,Sybase,Informix和Sql Server）最小的恢复和交易单位为一个事务，事务具有ACID（原子性，一致性，隔离性，永久性）特征。关系数据库为了确保并发用户在存取同一数据库对象的正确性（即无脏读，无丢失更新，可重复读，无幻读），数据库中引入了锁机制。基本的锁类型：共享锁S和排它锁X。
排它锁：如果事务A对数据D加X锁，则其他任何事务都不能再对数据D加任何类型的锁，直至事务A释放数据D上的X锁；一般要求在修改数据前要向该数据加排它锁，所以排它锁又称为写锁。如果事务隔离级别是ur（未提交读），更新数据时是没有加排它锁的。
共享锁：如果事务A对数据D加S锁，则其他事务只能对数据D加S锁，而不能加X锁，直至事务A释放D上的S锁；一般要求在读取数据前要向该数据加共享锁，所以共享锁又称读锁。
下表是总结的DB2中各SQL语句产生表锁的情况（假设缺省的隔离级别为CS）：

DB2数据库与Mysql数据库区别

1、账号管理方式
MySQL账号管理的方式与Oracle类似，由MySQL自己管理，账号存储在MySQL数据库的user表中，其账号的组成是有ip地址+用户名组成，而DB2没有自己的用户体系，其认证完全依赖于操作系统的账号。

2、权限管理方式
mysql可以对最小单元的对象可以批量进行授权；DB2则不能，相对比较繁琐。

3、日志管理
MySQL数据库使用日志双写的方式来保证数据的完整性与可恢复性，MySQL的事务日志与归档日志两个独立的对象，没有任何因果关系。DB2数据库的归档日志是有事务日志产生的。

4、锁的管理方式
MySQL使用MVCC模型实现锁的并发控制，DB2使用内存模型实现了锁的并发控制，在并发处理、处理资源冲突上讲，MySQL的并发处理能力、锁冲突的方式上要优于DB2。

5、schema的管理方式
MySQL数据库严格意义上讲没有schema的概念，其每个schema相当于独立的一个数据库。db2数据库可以在一个数据库能创建多个schema。

6、表空间的管理方式
MySQL数据库在表空间上支持相对较弱，存在很多的局限性，在条带化及空间的管理上存在软肋。DB2在这些方面做得都十分完善，管理方便、功能强大，而且易于维护。

7、事务行为
MySQL会回滚到上一个保存点，不会回滚整个事务；DB2会让整个事务进行回滚。

8、数据恢复
MySQL支持在恢复时打开数据库，灵活；DB2不支持在恢复或前滚时打开数据库。

9、SQL支持
MySQL对复杂SQL支持较弱，在运行时可能会存在性能问题；DB2支持各种复杂SQL，多种jion方式。

10、语法差异
MySQL遵守SQL92标准，但细节上有差异、默认大小写敏感；DB2遵守SQL92标准，大小写不敏感。

11、高可用与容灾设计
MySQL在高可用及容灾方面有非常多的方案，MySQL原生支持多种架构、复制方案来完成据库的高可用及容灾需求如MHA、PXC及MMM等，而且非常灵活、可根据业务需求进行个性化定制、二次开发，几乎零成本。DB2在这方面只有HADR方案且成本高昂。

12、可扩展性
MySQL在集群方面也有非常多的解决方案，如Cobar、Atlas、Fabric、Amoeba、TDDL、Mycat而且可以进行定制或者进行二次开发，可根据业务需求进行灵活扩展。DB2在这方面目前只有PureScale一种解决方案，灵活性较差。
扩展阅读：
从商用到开源：15个维度，全面剖析DB2与MySQL数据库的差异，链接: link.
MySQL与DB2的数据类型对比，链接: link.