深入浅出DB2
( SELECT * FROM VIRGO.EMPLOYEE ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ; DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPLOYEE_PRE AS ( SELECT * FROM VIRGO.EMPLOYEE ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ; DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPLOYEE_INS AS ( SELECT * FROM VIRGO.EMPLOYEE ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ; DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPLOYEE_DEL AS ( SELECT * FROM VIRGO.EMPLOYEE ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ; P2:BEGIN SET V_START_TS = CURRENT TIMESTAMP - CURRENT TIMEZONE;
SET OUT_SQLMSG = 10; DELETE VIRGO.EMPLOYEE
WHERE DAT_STRT_DT = IN_TX_DATE; SET OUT_SQLMSG = 20; UPDATE VIRGO.EMPLOYEE
SET DAT_END_DT = V_MAX_DATE WHERE DAT_END_DT = IN_TX_DATE; --2、向当前数据临时表插数据 SET OUT_SQLMSG = 30;
INSERT INTO SESSION.EMPLOYEE_CUR SELECT T1.EMP_NO ,T1.EMP_NAME ,T1.DEPT
,T1.BIRTHDAY ,'EMPLOYEE'
,CURRENT TIMESTAMP ,USER ,'N'
,IN_TX_DATE ,V_MAX_DATE
FROM LEO.EMPLOYEE T1 WITH UR; SET OUT_SQLMSG = 40; --3、向昨日临时表插入数据
INSERT INTO SESSION.EMPLOYEE_PRE
第16页
深入浅出DB2
SELECT * FROM VIRGO.EMPLOYEE
WHERE DAT_END_DT = V_MAX_DATE WITH UR;
--4、比较数据，新增数据插入临时表 INS ， 删除数据插入临时表 DEL SET OUT_SQLMSG = 50;
INSERT INTO SESSION.EMPLOYEE_INS SELECT T1.*
FROM SESSION.EMPLOYEE_CUR T1
LEFT JOIN SESSION.EMPLOYEE_PRE T2 --比较所有字段 ON COALESCE(T1.EMP_NO,'') = COALESCE(T2.EMP_NO,'')
AND COALESCE(T1.EMP_NAME,'') = COALESCE(T2.EMP_NAME,'') AND COALESCE(T1.DEPT,'') = COALESCE(T2.DEPT,'')
AND COALESCE(T1.BIRTHDAY,TO_DATE('1900-01-01','YYYY-MM-DD')) = COALESCE(T2.BIRTHDAY,TO_DATE('1900-01-01','YYYY-MM-DD')) WHERE T2.EMP_NO IS NULL WITH UR;
--5、比较数据， 删除数据插入临时表 DEL SET OUT_SQLMSG = 60;
INSERT INTO SESSION.EMPLOYEE_DEL SELECT T1.*
FROM SESSION.EMPLOYEE_PRE T1
LEFT JOIN SESSION.EMPLOYEE_CUR T2 --比较所有字段 ON COALESCE(T1.EMP_NO,'') = COALESCE(T2.EMP_NO,'')
AND COALESCE(T1.EMP_NAME,'') = COALESCE(T2.EMP_NAME,'') AND COALESCE(T1.DEPT,'') = COALESCE(T2.DEPT,'')
AND COALESCE(T1.BIRTHDAY,TO_DATE('1900-01-01','YYYY-MM-DD')) = COALESCE(T2.BIRTHDAY,TO_DATE('1900-01-01','YYYY-MM-DD')) WHERE T2.EMP_NO IS NULL WITH UR;
----------------6、插入新增数据 INS ， 封口删除数据 DEL-----------------
SET OUT_SQLMSG = 70;
UPDATE VIRGO.EMPLOYEE T1 SET DAT_END_DT = IN_TX_DATE WHERE EXISTS (
SELECT 1
FROM SESSION.EMPLOYEE_DEL T2
WHERE COALESCE(T1.EMP_NO,'') = COALESCE(T2.EMP_NO,'') AND COALESCE(T1.EMP_NAME,'') = COALESCE(T2.EMP_NAME,'') AND COALESCE(T1.DEPT,'') = COALESCE(T2.DEPT,'')
AND COALESCE(T1.BIRTHDAY,TO_DATE('1900-01-01','YYYY-MM-DD')) = COALESCE(T2.BIRTHDAY,TO_DATE('1900-01-01','YYYY-MM-DD')) WITH UR ) ;
SET OUT_SQLMSG = 80;
INSERT INTO VIRGO.EMPLOYEE
SELECT * FROM SESSION.EMPLOYEE_INS WITH UR;
SET OUT_SQLMSG = 81;
DROP TABLE SESSION.EMPLOYEE_CUR; DROP TABLE SESSION.EMPLOYEE_PRE; DROP TABLE SESSION.EMPLOYEE_INS; DROP TABLE SESSION.EMPLOYEE_DEL;
--------------------------拉链算法结束------------------------
第17页
深入浅出DB2
SET OUT_SQLMSG = 90;
CALL CORE_ADMIN.CORE_INS_LOG_PROC(V_PROC_NM, V_START_TS,NULL,V_SCHEMA_NM,'D', NULL, NULL, OUT_SQLCODE , OUT_SQLSTATE); IF (
OUT_SQLCODE < 0 ) THEN SET OUT_SQLERRMC = 'Error in CORE_ADMIN.CORE_INS_LOG_PROC '; SIGNAL SQLSTATE OUT_SQLSTATE SET MESSAGE_TEXT = OUT_sqlerrmc; ELSE IF ( V_INS_ROW_IND = 'N' ) THEN SET OUT_sqlerrmc = 'Stored Procedure Finished Successfully, Nothing inserted as row_id already exists'; ELSE SET OUT_sqlerrmc = 'Stored Procedure Finished Successfully'; END IF; SET OUT_SQLMSG = 999; SELECT SQLCODE, SQLSTATE INTO OUT_SQLCODE, OUT_SQLSTATE FROM SYSIBM.SYSDUMMY1; END IF; END P2; END P4;
END P1
4.4 Oracle拉链算法实例
5 函数
5.1 一般函数
第18页
5.2 Db2正则表达式
6 存储
6.1 分区表
7 权限管理
7.1 用户列表
--dmdata --rawdata --cmnref --libra --librisk --core_admin --param --virgo --leo --oracle --img --sunflow --sjxt --db2fenc --db2inst --jcm
深入浅出DB2
第19页
深入浅出DB2
7.2 常用授权命令
如果具有 SYSADM 权限的用户创建数据库，那么将自动授予用户对该数据库的 DATAACCESS、ACCESSCTRL、SECADM 和 DBADM 权限
--授权数据库管理权限给用户
db2grant dbadm on database to user db2admin --给予创建用户和schema的权限
--授权数据库管理权限给用户
db2grant secadm on database to user db2admin --给予创建角色的权限
--授权数据库管理权限给用户组
db2 grant dbadm on database to group db2grp1 --授权查询：
db2grant select on table tableName to user db2admin --全部授权
DB2 GRANTALL ON TABLE OWK.\
7.3 DB2权限详解
一、DB2权限简介
DB2数据库权限分为实例权限级别（SYSADM、SYSCTRL、SYSMAINT、SYSMON）和DB2数据库权限级别（DBAMD、LOAD）。SYSCTRL、SYSMAINT、SYSMON权限级别的用户不能访问数据，DBAMD、LOAD权限级别的用户可以访问数据。 实例级别权限(只能在用户组级别上进行分配)：
SYSADM ----系统管理权限(UNIX系统中，SYSADM用户组默认地设置为实例拥有者所在的主用户组，因此，该组中的任何用户都拥有SYSADM权限） SYSCTL---- 系统控制权限 SYSMAINT---- 系统维护权限 数据库级别：
dbadm---- 数据库管理权限（不能执行诸如删除数据库、创建和删除表空间、备份或者恢复数据库等等维护性和管理任务） load---- 对表进行load操作的权限
1、 DB2数据库权限：
第20页