db2笔记

  db2
添加主键
alter table tablename add primary key(col)
删除主键
alter table tablename drop primary key(col)
重命名
rename oldname to newname
快速清空一个大表
alter table tablename activate not logged initially with empty table
关闭表日志
alter table tabname activate not logged initially
设置某列为null
ALTER TABLE  tablename   ALTER column colname  drop not  NULL;
查看一个sql语句执行需要的时间
db2batch -d sample -f test.sql
value函数
select value(ID,'') from t1 如果t1.ID 为空， 则返回空串，如果t1.ID 不为空，则返回t1.ID
获取一个表的行数
db2 select row_number() over() from test
db2 select count(*)from test

union
union 运算符通过组合其它两个结果表 并消去表中任何重复行而派生一个结果表。当all随union一起使用时，不消除重复行，两种情况下，派生表的每一行不是来自table1就是来自table2.
except
except运算符通过包括所有在table1中但不在table2中的行并消除所有重复行而派生出一个结果表。当all随except一起使用时（except all），不消除重复行。

intersect
intersect运算符通过值包括table1和table2中都有的行并消除所有重复行而派生出一个结果表，当all随intersect使用时，不消除重复行。
注意（这几个运算符的结果行中包含的列要一致）

超级分组和移动函数
grouping sets 用来在单个sql中形成多级分组
select company_id,node_id,count(customed_id) from customer group by grouping sets(company_id,node_id)

rollup 可以在单个数据库操作中形成多个分组
select company_id,node_id,count(customed_id) from customer group by rollup(company_id,node_id)
注：rollup操作不是可交换的操作，指定用户组的顺序很重要。
cube 生成分组表中分组的所有组合。
select company_id,node_id,count(customed_id) from customer group by cube(company_id,node_id)
over 移动函数可以帮助实现移动的数据分析
select date,avg(qty) over(order by date rows between 1 preceding and 1 following)as values from sale

函数
DOUBLE() FLOAT() INT() BIGINT() SMALLINT() REAL() DEC() HEX()字符串的值的16进制表示 FLOOR() CHAR() VARCHAR() DIGITS()
SELECT DOUBLE('5678') FROM TEST

DATE() TIME()
TIMESTAMP()返回一个或两个参数中的时间戳 如果只有一个参数 则这个参数必须是一个时间戳或一个时间戳字符串；如果有两个参数第一个参数必须是一个日期或日期字符串，第二个参数必须是一个时间或时间戳字符串。
YEAR() MONTH() QUARTER() WEEK() DAY() HOUR() MINUTE() SECOND() DAYS() DAYOFYEAR() DAYOFWEEK() DAYOFWEEK_ISO() DAYNAME() MONTHNAME() MIDNIGHT_SECONDS() LENGTH() 
SELECT QUARTER(CURRENT DATE) FROM TEST
SELECT DAYOFWEEK_ISO(CURRENT DATE)FROM TEST

LCASE() LOWER() 返回定长 变长字符串的小写形式
UCASE() UPPER() 返回定长 变长字符串的大写形式
RTRIM() LTRIM()
COALESCE(arg1,arg2...)返回参数中第一个非null参数
CONCAT(arg1,arg2) 返回2个字符串的连接
INSERT(arg1,pos,size,arg2) 返回一个字符串，将arg1从pos处删除size个字符，将arg2插入该位置。
LEFT(ARG,LENGTH) 返回arg最左边的length个字符串，arg可以是char或binary string
RIGHT(ARG,LENGTH)

LOCATE(ARG1,ARG2,<POS>在arg2中查找arg1第一次出现的位置，指定pos，则从arg2的pos处开始查找arg1第一次出现的位置。
POSSTR(EXP1,EXP2)返回EXP2在EXP1中的位置。
REPEAT(ARG1,NUM_TIMES)返回arg1被重复num_times次的字符串。
REPLACE(EXP1,EXP2,EXP3)用exp3 替代exp1中所有的exp2
SPACE(SIZE)返回一个包含size个空格的字符串。
SUBSTR(ARG1,POS,<LENGTH>) 返回arg1中pos位置开始的length个字符，没指定length则返回剩余字符。

ROUND(EXP1,EXP2)返回exp1小数点右边的第exp2位置处开始的四舍五入值。

TRUNCATE(ARG1,ARG2)在小数点后的指定位数截断数字。arg1是要截断的数字，arg2如果是正整数，指定小数点右边舍入到的有效位数，如果是负数，表达式指定小数点左边舍入到的有效位数即保留arg1小数点前的arg2位。

NULLIF(ARG1,ARG2) 返回是否相等，如果2个参数相等，则返回null 否则返回参数1.

缓冲池
缓冲池用来进行表和索引的缓存，一个数据库至少要有一个缓冲池 ，缓冲池页的大小可以是4k，8k，16k，32k，表空间的页大小要和缓冲池保持一致，默认的缓冲池为IBMDEFAULTBP(4K).
CREATE BUFFERPOOL "BP1" IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 4K;
ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE 250 AUTOMATIC;

DB2 V9中缓冲池可以使用自动自调整内存，在默认情况下，应用程序使用缓冲池IBMDEFAULTBP；
仅当SYSCAT.BUFFERPOOLS中NPAGES值为-1时，DB CFG 的BUFFPAGE控制着缓冲池的大小，否则会忽略BUFFPAGE参数，并且用NPAGES参数所指定的页数创建缓冲池；
当SELF_TUNING_MEM参数打开时，使用automatic参数的缓冲池将自动调节大小，如下语句所示。CREATE BUFFERPOOL "BP1" IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 4K;
ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE 250 AUTOMATIC;

表空间 用来存储表，是抽象物理存储，由一个或多个容器组成，也就是说表空间可以分布在一个或多个物理设备上，有 4k，8k，16k，32k 四种页大小的表空间。为提高性能，可以将表的数据存储在一个表空间，表的索引存储到另一个表空间，将大对象存储到第三个表空间，任何表的数据循环都存储在表空间的所有容器中；默认表空间有 SUSCATSPACE (4K,存储系统目录和表)，
TEMPSPACE1(4K,临时表空间)，USERSPACE1(4K,用户表空间)
表空间的类型有REGULAR(如USERSPACE1),SYSTEMTEMPORARY(如TEMPSPACE1)，LARGE(用来存储LOB数据)，USERTEMPORARY(用来存储用户临时表)，按管理方式分为 系统管理表空间（System Manage Space,SMS）和数据库管理表空间（DataBase Manage Space，DMS）。对于系统管理表空间，每个容器都是操作系统的文件空间中的一个目录，由操作系统的文件管理器控制存储空间。对于数据库管理表空间，每个容器或者是固定大小的预分配文件，或者是物理设备（如磁盘） ，由数据库管理器控制存储空间。
系统管理表空间，其容器是文件系统的目录，是非预分配的。大小的限制为文件目录的大小，容器的个数在创建的时候决定，不能修改。如果使用系统管理表空间，用户数据不能分开存储，也就是不能把用户数据分别存储在数据，索引，大数据表空间中。
数据库管理表空间，其容器可以是文件 或裸设备（raw devices） ，其容器可以增加，修改或改变大小；如果使用数据库管理表空间，用户数据可以分开存储，也就是可以把用户数据分别存储在INDEX,TABLE和LOB表空间中。
容器是物理存储设备，可以用目录名。设备名或文件名来标识。可以为表空间分配容器，单个表空间可以横跨多个容器，但每个容器只能属于一个表空间。

 

 

并发性
并发产生的问题：
丢失更新：比如2个应用程序，当A和B同时读取同一行数据，如果A更改了该数据，B随后也更改了该数据，则A所执行的更新将丢失。

通常DB2 UDB不允许发生这类现象。
访问未落实的数据，胀读
当应用程序A更改了数据库中的某一行的值，当时没有提交，此时应用程序B访问到了应用程序A更改过后的数据，稍后，A更改的值进

行了回滚，也就是未被落实，这样应用程序B实际上读取了不曾存在的数据，也就是胀读。
不可重复读
当一个事务两次读取同一行数据，但每次获得不同的数据值时，就会发生这种情况。比如，应用程序A读取了一行数据，而应用程序B

在更改或删除该行后提交了更改，此时应用程序A再次读取该行时，接收到的是已修改的行或发现该原始行已被删除。

幻象读现象
当应用程序A执行一个查询，该查询根据某些搜索条件读取一组行。此时应用程序B插入新数据或更新现有的数据以满足用户的应用程

序查询。此时，应用程序A重复查询（在同一个工作单元，也就是事务内），发现某些附加的（“幻象”）行作为结果集的一部分返

回，但在初始执行该查询时不会返回这些行。

隔离级别：
可重复读 Repeatable Read
读稳定性 Read Stability
游标稳定性 Cursor Stability
未提交的读 Uncommitted Read

get snapshot for locks on sample
可重复的读 可防止  丢失更新。胀读。不可重复读。幻象读。
最严格的隔离级别，锁定该事务所引用的每一行，而不是仅仅锁定被实际检索或修改的那些行，也就是说会锁定该事务所有可能会用

到的行，只要该行在在事务的引用范围内，不管这些行时候满足检索或被修改的条件；可以多次检索同一结果集，并对他们执行任意

操作，直到由提交或回滚操作终止事务，如果LOCKLIST MAXLOCKS偏小，当发生锁升级由行级锁升级到了表级锁，则对整个表进行锁

定。

读稳定性   可防止  丢失更新。胀读。不可重复读。 可能出现幻象读
在这个事务存在期间，其他事务不能执行那些会影响这个事务检索到的行集的更新或删除的操作，但是其他事务可以执行插入操作。

如果插入的行与第一个事务的查询的选中条件匹配，那么这些行可能作为幻象出现在后续产生的结果数据集中。其他事务对其他行所

做的更改，在提交之前是不可见的。

游标稳定性 可防止  丢失更新。胀读。
默认的隔离级别。
1一般只锁定事务声明并打开的游标当前引用的行，也就是说该事务只锁定当前行，对当前行以外的记录不做锁定。
2所获取的锁一直有效，直到游标重定位或事务终止为止，3如果游标重定位，原来行上的锁就被释放，并获得游标现在引用的行上的

锁。4如果事务修改了它检索到的任何行，那么在事务终止之前，其他事务不能更新或删除该行，即使游标不再位于被更新或删除的

行，5需要注意：如果只检索，一般只锁定当前行；如果对检索的行还进行了更新或删除，则对修改的行也进行了锁定，即便指针移

向了其他行，对修改行的锁定还是存在。

未提交的读 可防止  丢失更新
最低的隔离级别，通常不上锁，仅当其他事务试图更新或删除被检索的行所在的表时，才会锁定一个事务检索的行。通常用于那些访

问只读表和视图的事务，以及某些执行select语句的事务（只要其他事务的未提交数据对这些语句没有负面效果）

如果事务A对特定的数据资源进行了锁定，另一个事务B尝试访问该数据资源，事务B尝试访问该数据资源的方式与事务A所持有的锁不

兼容，则事务B必须等等，直到事务A结束为止，这叫锁等待。

意向无 （Intent None，IN ）适用对象为 表空间和表
意向共享（Intent Share，IS）适用对象为 表空间和表
下一键共享（Next Key Share，NS）适用对象为 行
共享（Share，S）适用对象为 表和行
意向互斥（Intent Exclusive，IX）适用对象为 表空间和表
带意向互斥的共享（Share With Intent Exclusive，SIX）适用对象为 表
更新（Update，U）适用对象为 表和行
下一键弱互斥（Next Key Weak Exclusive，NW）适用对象为 行
互斥（Exclusive，X）适用对象为 表和行
弱互斥（Weak Exclusive，WX）适用对象为 行
超级互斥（Super Exclusive，Z）适用对象为 表空间和表

 

共享锁
锁拥有者和任何其它并发的事务都可以读（但不能更改）被锁定的表或行中的数据。只要表不是使用共享锁锁定的，那么该表中的单

个行可以使用共享锁锁定。但是，如果表是用共享锁锁定的，则锁拥有者不能在该表中获取行级的共享锁。如果表或行是用共享锁锁

定的，则其他并发事务可以读取数据，但不能对它进行修改。
更新锁
锁的拥有者可以更新被锁定表中的数据，而且锁的拥有者在它所更新的任何行上自动获得互斥锁。其它并发的应用程序可以读取但不

能更新被锁定表中的数据。
互斥锁
锁的拥有者可以读取和更改被锁定的表或行中的数据，如果获取了互斥锁，则只允许使用未提交的读隔离级别的应用程序访问被锁定

的表或行。对于用insert update delete语句操作的数据资源，将获取互斥锁。

多数情况下DB2数据库管理程序会在需要锁定资源时隐式地进行锁定。
发生锁转换是因为一个事务用一时间内只能在一个数据资源上持有一个锁。当事务尝试访问它已经持有的锁的数据资源，但是所需的

访问模式需要比已持有的锁更严格的锁时，则锁持有的锁的状态更改成严格的状态。例如 ，如果持有的共享S或更新U行级锁，但是

需要互斥X锁，则所持有的锁将被转换成互斥X锁，但意向互斥IX锁和共享S锁是特例，因为无法确定那个更严格。因此，如果持有其

中一种行级锁但又需要另一种锁，则所持有的锁将转换成带意向互斥的共享SIX锁。
特定数据库代理所能使用的空间由LOCKLIST MAXLICKS决定。当特定数据库代理使用的空间超过了已建立的锁空间限制，就会发生锁

升级。锁升级是一种转换，它将同一表内几个单独的行级锁转换成一个单一的表级锁。因为锁升级是在内部处理的，所以唯一可从外

部检测到的结果可能只是对一个和多个表的并发访问减少了。
锁超时由LOCKTIMEOUT数据库配置参数控制，默认是－1，表示无限制等待。建议修改设成一个合理的值。
死锁检测时间由DLCHKTIME数据库配置参数控制，当发现死锁后，会任意选择一个锁定将其释放掉，从而使事务继续进行下去。