DB2 学习笔记(四)
$ db2 \? listdb2 get authorizations 用来获取当前用户的权限
Direct SYSADM authority = NO Direct SYSCTRL authority = NO Direct SYSMAINT authority = NO Direct DBADM authority = YES Direct CREATETAB authority = YES Direct BINDADD authority = YES Direct CONNECT authority = YES Direct CREATE_NOT_FENC authority = YES Direct IMPLICIT_SCHEMA authority = YES Direct LOAD authority = YES Direct QUIESCE_CONNECT authority = YES Direct CREATE_EXTERNAL_ROUTINE authority = YES Direct SYSMON authority = NO Indirect SYSADM authority = NO Indirect SYSCTRL authority = NO Indirect SYSMAINT authority = NO Indirect DBADM authority = NO Indirect CREATETAB authority = NO Indirect BINDADD authority = NO Indirect CONNECT authority = YES Indirect CREATE_NOT_FENC authority = NO Indirect IMPLICIT_SCHEMA authority = NO Indirect LOAD authority = NO Indirect QUIESCE_CONNECT authority = NO Indirect CREATE_EXTERNAL_ROUTINE authority = NO Indirect SYSMON authority = NO
DB2在编目节点(catalog node)时，可以在使用admin 关键字，指定admin 代表此节点对应了一个DB2系统的安装，称为系统(system)，如果不指定admin，代表此节点对应了一个DB2的实例（instance），并需要至少指定服务名与端口号其中之一来映射此实例。
$ db2 catalog [admin] node
Use "WITH HOLD" cause for cursor declaration would have more control on sources used by this cursor.
Use "LEAVE myloop" to stop execution in loop and go to the end of the loop.
Use "ITERATE myloop" to stop execution in loop and return to the begin of the loop to continue next loop.
Below db2 commands above need to attach to target node first before issuing them.
$ db2 list active databases $ db2 list applicationsUNIQUNE cause in savepoint
the cause intend to that the same savepoint name can't be reused in the same savepoint level.
Units of work
A transaction is commonly referred to in DB2(R) Universal Database (DB2 UDB) as a unit of work. A unit of work is a recoverable sequence of operations within an application process. It is used by the database manager to ensure that a database is in a consistent state. Any reading from or writing to the database is done within a unit of work.
For example, a bank transaction might involve the transfer of funds from a savings account to a checking account. After the application subtracts an amount from the savings account, the two accounts are inconsistent, and remain so until the amount is added to the checking account. When both steps are completed, a point of consistency is reached. The changes can be committed and made available to other applications.
A unit of work starts when the first SQL statement is issued against the database. The application must end the unit of work by issuing either a COMMIT or a ROLLBACK statement. The COMMIT statement makes permanent all changes made within a unit of work. The ROLLBACK statement removes these changes from the database. If the application ends normally without either of these statements being explicitly issued, the unit of work is automatically committed. If it ends abnormally in the middle of a unit of work, the unit of work is automatically rolled back. Once issued, a COMMIT or a ROLLBACK cannot be stopped. With some multi-threaded applications, or some operating systems (such as Windows(R)), if the application ends normally without either of these statements being explicitly issued, the unit of work is automatically rolled back. It is recommended that your applications always explicitly commit or roll back complete units of work. If part of a unit of work does not complete successfully, the updates are rolled back, leaving the participating tables as they were before the transaction began. This ensures that requests are neither lost nor duplicated.
sql0010N state 42603
编译过程时出现此错误信息，可能是由于多余的单引号引起，行号指定处为错误点。
sql0104N state 42601
过程如果超过65535字节长度限制，编译时会抛出此错误信息，行号指定处为限制点。
对于两个整形变量作除法运算，结果可能并不是你希望得到的小数（如果分子小于分母）
select 2/4 from sysibm.sysdummy1 1 ---- 0
若希望得到小数，可在运算之前转换分子分母任意一个到DECIMAL 或者DOUBLE
select DECIMAL(2)/4 from sysibm.sysdummy1 1 ---- 0.5
"GET DIAGNOSTICS" must be the first next line after DML, otherwise the ROW_COUNT will not be the value you want.
DELETE FROM PRMS1.opprtnty_line_item； GET DIAGNOSTICS v_affected_rows = ROW_COUNT;
It's impossible to change a column from not null to nullable by alter table statement.
But there's a way that meets this requirement by execute sys procedure.
It's not allowed to drop columns from a table without re-creation of the table.
db2 doesn't support following sql
select NULL as c1 from sysibm.sysdummy1
在DB2 中重命名表或者索引
RENAME TABLE EMP TO EMPLOYEE RENAME TABLE ABC.EMP TO EMPLOYEE RENAME INDEX NEW-IND TO IND RENAME INDEX ABC.NEW-IND TO IND
拥有检查约束和非主键自增列的表不允许重命名，在视图、触发器、函数或者物化查询表中被引用的表也不允许重命名，只能Drop 掉，重新创建。
使用DMS 表空间，DB2绕过操作系统，直接管理用于表空间的存储，可以保证数据页物理连续的存放。不过有个例外，如果是files类型的DMS表空间容器，则不能总是保证数据页是物理连续存放的，而对于裸设备(raw)容器，则可以完全保证。
