DB2 730 考试复习------问题总结

robin

　　1.authorization
SYSADM:所有权限。
SYSCTRL:最高的系统控制权限级别。不允许直接访问数据。
SYSMAINT:不允吸访问数据。
SYSMON:提供快照能力，用于监控等。
DBADM:可以访问数据库中所有数据
LOAD:执行LOAD，根据操作模式，用户可能还需要LOAD操作目标上的insert 和 delete特权。
其中：
SYSCTRL：
db2start/db2stop
db2 create/drop database
db2 create/drop tablespace
db2 backup/restore/rollforward database
db2 runstats
db2 update db cfg for database dbname
SYSMAINT:
db2start/db2stop
db2 backup/restore/rollforward database
db2 runstats
db2 update db cfg for database dbname
DBADM:
db2 create/drop table
db2 grant/revoke
db2 runstats
　　表和视图特权 7种：
control:授予用户在表和视图上的所有特权，以及将这些特权（除了control）授予别人
alter:允许用户在表中添加列，在表和它的列上添加或修改注释，添加主键或唯一约束，以及创建或删除表检查约束。
delete：删除行
index:允许用户在表上创建索引（注意，只有创建，没有删除，删除索引需要用户有唯一的一种索引特权control）
insert：插入数据
references:允许用户创建和删除外键，注意，这里的这个权限指的是给予parent key的权限
select:允许检索行和创建视图，以及运行export
update：允许用户跟新，但只可以在特定列上有这种特权
　　对于触发器，需要下面的权限之一：
对于定义before或after触发器的表有alter权限
对于定义instead of触发器的视图有control权限
对定义instead of触发器的视图有所有权
对定义触发器的表或视图的模式有alterin权限
sysadm或dbadm
　　2.routine/function/procedure
function/procedur are both routine
execute procedure need execute privillage, use call to execute procedure, if there is an out para in your procedure, use '?' instead.
3.sequence/identity
gererated always as identity(start with 1 increment by 1):you can not give a value, this just be generated by db2.
gererated by default as identity(start with 1 increment by 1):you can give a value.
you can use alter clause to change the current value of identity cloumn:
alter table XXX alter column YYY restart with 100;
　　sequence is start from 1 by default
　　4.view/foreign key's with grant option/delete issue
view:
with cascade check option(if you don't set cascade explictly, this the default opinion for 'with check option'):all statuments executed against the view must satisfy the conditions of the view and all underlying views--even if those views where not defined with the check option.
with local check option:statuments executed against the view need only satisfy conditions of views that have the check option specified.
　　5.inner join/outer join
内连接与交叉积类似。
左外连接包括内连接和左表中未包括在内连接中的那些行。
右外连接包括内连接和右表中未包括在内连接中的那些行。
全外连接包括内连接以及左表和右表中未包括在内连接中的行。
6.xml usage
sqlquery,xmlquery,xmlexist
7.cursor:for example:delete form tab1 where current of csr1 with rr
if you do not add 'with hold', cursor will be closed after commit or rollback.
　　
8.select statement when there is a column is null
?
9.data types
There are four categories of built-in data types:numeric,string,datetime,and XML
　　CLOB:Character large object(Single Byte)
DBCLOB:Double Byte Character large object
BLOB:Binary large object
　　User-defined distinct:create distinct type candol as decimal(10,2) with comparisons,this will automatically generate functions to perform casting between the base type and the distinct type, and comparsion operators for comparing instan ces of the distinct type.
User-defined structured:create a type that consists of serveral columns of built-in types.
User-defined reference
　　10.can unique or index be null?
DB2 provide three types of constraints:unique,referential integrity,and table check.
　　unique:Unique constraints are used to ensure that values in a column are unique. Unique constraints can be defined over one or more columns. Each column included in the unique constraint must be defined as NOT NULL.It can be defined either as primary key or unique.You can define only one primary key on a table, but you can define multiple unique constraints.
CREATE TABLE BOOKS (BOOKID INTEGER NOT NULL PRIMARY KEY,
BOOKNAME VARCHAR(100),
ISBN CHAR(10) NOT NULL CONSTRAINT BOOKSISBN UNIQUE )
　　referential integrity:primary key <=> foreign key
CREATE TABLE AUTHORS (AUTHORID INTEGER NOT NULL PRIMARY KEY,
LNAME VARCHAR(100),
FNAME VARCHAR(100))
CREATE TABLE BOOKS (BOOKID INTEGER NOT NULL PRIMARY KEY,
BOOKNAME VARCHAR(100),
ISBN CHAR(10),
AUTHORID INTEGER REFERENCES AUTHORS)
if restrict or no action is specified, DB2 does not allow the parent row to be deleted.
if cascade is specified, thed deleting a row from the parent table automatically also deletes dependent rows in all dependent tables.
if set null is specified, then the parent row is deleted from the parent table and the foreign key value in the dependent rows is set null(if nullable)
table check:ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1) CHECK (BOOKTYPE IN ('F','N') )
　　Index can be defined as unique or nonunique.Nonunique indexes allow duplicate
key values; unique indexes allow only one occurrence of a key value in the list.Unique indexes do allow a single null value to be present. However, a second null value would cause a duplicate and therefore is not allowed.
DB2 does not let you create multiple indexes with the same definition.For example, because primary key will create an index implictly, you cann't create another index on it.
　　clustering indexes:physical store way <==> index store way
　　11.partition
use it to improve speed(分区，refer to RAID)
12.lock/isolation level
幻想读：事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。（select行）
脏读：读取了未提交的数据。（t1跟新数据，但未提交，t2读了，t1回滚，这时t2就读了脏数据）
不可重复读：事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。（where行）
丢失更新
　　可重复读RR：最严格，脏读、不可重复读、幻想读都不会发生。（锁定select扫过的所有行）
读稳定性RS：防止脏读、不可重复读。（锁定满足select where条件的行）
游标稳定性CS：防止脏读。（锁定游标当前所指定的行）
未提交读UR：都可能发生。
　　13.others：
AIX=>最低要workgroup
HP-UX=>最低要enterprise
6CPUs=>最低要Enterprise
use package:connect & execute
Z/OS catalog：先catalognode，然后直接catalog DCS Database（两步）
XML column cann't be unique