-- 第一步：連接業務庫后執行該語句

connect to sjsda user db2admin using pass1009;

--update dbm cfg using federated yes;

-- 然后重新啟動 db2 服務

--db2stop force;

--db2start;

-- 第二步：執行以下語句，如以別的賬號，把下面的模式“db2admin.”都換掉

-- 刪除原有各對象

drop wrapper drda;

-- 創建WRAPPER 的 DDL 語句

CREATE WRAPPER "DRDA"

LIBRARY 'db2drda.dll'

OPTIONS (DB2_FENCED 'N'

);

-- 創建SERVER 的 DDL 語句

-- 需替換AUTHORIZATION,PASSWORD,DBNAME，指平臺的數據庫

CREATE SERVER tempserver

TYPE DB2/UDB

VERSION 8.1

WRAPPER DRDA

AUTHORIZATION "用戶名"

PASSWORD "密碼"

OPTIONS

(DBNAME 'TESTPP'

);

-- 創建USER MAPPING 的 DDL 語句

-- 需替換REMOTE_AUTHID,REMOTE_PASSWORD，指平臺的數據庫

-- "FOR db2admin "中的db2admin 在后面都會用到，最好是業務數據庫管理賬號，如以別的賬號，最好把下面的模式“db2admin.”也都換掉

CREATE USER MAPPING FOR db2admin

SERVER tempserver

OPTIONS

(REMOTE_AUTHID '用戶名'

,REMOTE_PASSWORD '密碼'

);

-- 創建跨庫視圖的 DDL 語句

-- 用戶基本信息的視圖

CREATE NICKNAME db2admin.TEMPXX_PERSON2 FOR tempserver.db2admin.TEMPXX_PERSON;

CREATE NICKNAME db2admin.TEMPXX_PERSONACCOUNT2 FOR tempserver.db2admin.TEMPXX_PERSONACCOUNT;

create view SYS_PERSON as

select db2admin.TEMPXX_PERSONACCOUNT2.PERSONUUID, db2admin.TEMPXX_PERSONACCOUNT2.FLAG, db2admin.TEMPXX_PERSONACCOUNT2.USERID, db2admin.TEMPXX_PERSONACCOUNT2.ACCOUNTSTAT, db2admin.TEMPXX_PERSONACCOUNT2.LOGINFAILNUM, db2admin.TEMPXX_PERSONACCOUNT2.LASTLOGINIP, db2admin.TEMPXX_PERSONACCOUNT2.LASTLOGINDATE, db2admin.TEMPXX_PERSONACCOUNT2.PASSQUESTION, db2admin.TEMPXX_PERSONACCOUNT2.PASSANSWER, db2admin.TEMPXX_PERSONACCOUNT2.TTLFLAG, db2admin.TEMPXX_PERSONACCOUNT2.ACCOUNTTTL, db2admin.TEMPXX_PERSONACCOUNT2.CREATETIME,db2admin.TEMPXX_PERSONACCOUNT2.DELTAG,

db2admin.TEMPXX_PERSON2.PERSONCODE, db2admin.TEMPXX_PERSON2.CNNAME, db2admin.TEMPXX_PERSON2.ENNAME, db2admin.TEMPXX_PERSON2.FIRSTNAME,db2admin.TEMPXX_PERSON2.LASTNAME,db2admin.TEMPXX_PERSON2.IDNUM, db2admin.TEMPXX_PERSON2.CARDCODE, db2admin.TEMPXX_PERSON2.SEX, db2admin.TEMPXX_PERSON2.MARRYCODE, db2admin.TEMPXX_PERSON2.PCODE, db2admin.TEMPXX_PERSON2.HOMETEL, db2admin.TEMPXX_PERSON2.OFFICETEL, db2admin.TEMPXX_PERSON2.HOMEFAX, db2admin.TEMPXX_PERSON2.OFFICEFAX, db2admin.TEMPXX_PERSON2.MOBILE, db2admin.TEMPXX_PERSON2.PAGER, db2admin.TEMPXX_PERSON2.EMAIL1, db2admin.TEMPXX_PERSON2.EMAIL2, db2admin.TEMPXX_PERSON2.COUNTRY, db2admin.TEMPXX_PERSON2.PROVINCEID, db2admin.TEMPXX_PERSON2.CITYID, db2admin.TEMPXX_PERSON2.CONNECTADDR, db2admin.TEMPXX_PERSON2.ZIP, db2admin.TEMPXX_PERSON2.EDUCODE, db2admin.TEMPXX_PERSON2.DEGREECODE, db2admin.TEMPXX_PERSON2.OTHERINFO,db2admin.TEMPXX_PERSON2.SEQUENCENO,db2admin.TEMPXX_PERSON2.JOB ,db2admin.TEMPXX_PERSON2.SIGNATURE

from db2admin.TEMPXX_PERSON2, db2admin.TEMPXX_PERSONACCOUNT2

where db2admin.TEMPXX_PERSON2.PERSONUUID = db2admin.TEMPXX_PERSONACCOUNT2.PERSONUUID;

-- 組織基本信息的視圖

CREATE NICKNAME db2admin.TEMPXX_ORG2 FOR tempserver.db2admin.TEMPXX_ORG;

create view SYS_ORG as

select db2admin.TEMPXX_ORG2.ORGUUID, db2admin.TEMPXX_ORG2.CNNAME, db2admin.TEMPXX_ORG2.ENNAME,db2admin.TEMPXX_ORG2.ORGCODE, db2admin.TEMPXX_ORG2.CONTACT,db2admin.TEMPXX_ORG2.ORGGRADE,db2admin.TEMPXX_ORG2.ORGPROP,db2admin.TEMPXX_ORG2.ORGLEVEL, db2admin.TEMPXX_ORG2.SERIALINDEX, db2admin.TEMPXX_ORG2.MEMO, db2admin.TEMPXX_ORG2.PARENTORGUUID, db2admin.TEMPXX_ORG2.STATUS,ORGLEVELCODE,db2admin.TEMPXX_ORG2.DELTAG,db2admin.TEMPXX_ORG2.SEQUENCENO

from db2admin.TEMPXX_ORG2;

-- 子系統和組織的映射關系視圖

CREATE NICKNAME db2admin.TEMPXX_SYSORGSHORTCUT2 FOR tempserver.db2admin.TEMPXX_SYSORGSHORTCUT;

CREATE NICKNAME db2admin.TEMPXX_SYSTEM2 FOR tempserver.db2admin.TEMPXX_SYSTEM;

create view SYS_ORGSUBSYSREL as

select db2admin.TEMPXX_SYSORGSHORTCUT2.SYSID, db2admin.TEMPXX_SYSORGSHORTCUT2.ORGUUID ,db2admin.TEMPXX_ORG2.CNNAME ,db2admin.TEMPXX_SYSTEM2.SYSNAME,db2admin.TEMPXX_SYSTEM2.SYSPROP

from db2admin.TEMPXX_SYSORGSHORTCUT2,db2admin.TEMPXX_SYSTEM2,db2admin.TEMPXX_ORG2

where db2admin.TEMPXX_SYSORGSHORTCUT2.SYSID = db2admin.TEMPXX_SYSTEM2.SYSID and db2admin.TEMPXX_SYSORGSHORTCUT2.ORGUUID = db2admin.TEMPXX_ORG2.ORGUUID;

-- 用戶和組織的映射關系視圖

CREATE NICKNAME db2admin.TEMPXX_ORGPERSON2 FOR tempserver.db2admin.TEMPXX_ORGPERSON;

create view SYS_ORGPERSON as

select db2admin.TEMPXX_ORGPERSON2.PERSONUUID,db2admin.TEMPXX_ORGPERSON2.ORGUUID,db2admin.TEMPXX_ORGPERSON2.ISBELONG from db2admin.TEMPXX_ORGPERSON2;

-- 用戶和系統角色的映射關系視圖

CREATE NICKNAME db2admin.TEMPXX_RIGHTSHORTCUT2 FOR tempserver.db2admin.TEMPXX_RIGHTSHORTCUT;

CREATE NICKNAME db2admin.TEMPXX_ROLE2 FOR tempserver.db2admin.TEMPXX_ROLE;

CREATE NICKNAME db2admin.TEMPXX_ROLEDISABLE2 FOR tempserver.db2admin.TEMPXX_ROLEDISABLE;

create view SYS_RIGHTSHORTCUT as

select db2admin.TEMPXX_RIGHTSHORTCUT2.PERSONUUID, db2admin.TEMPXX_RIGHTSHORTCUT2.ROLEID, db2admin.TEMPXX_ROLE2.SYSID ,db2admin.TEMPXX_ROLE2.CNNAME from db2admin.TEMPXX_RIGHTSHORTCUT2,db2admin.TEMPXX_ROLE2 where db2admin.TEMPXX_RIGHTSHORTCUT2.ROLEID = db2admin.TEMPXX_ROLE2.ROLEID

except

select db2admin.TEMPXX_ROLEDISABLE2.PERSONUUID,db2admin.TEMPXX_ROLEDISABLE2.ROLEID, db2admin.TEMPXX_ROLEDISABLE2.SYSID ,db2admin.TEMPXX_ROLE2.CNNAME from db2admin.TEMPXX_ROLEDISABLE2,db2admin.TEMPXX_ROLE2 where db2admin.TEMPXX_ROLEDISABLE2.ROLEID = db2admin.TEMPXX_ROLE2.ROLEID;

-- 用戶和應用角色的映射關系視圖

create view SYS_APPROLE_PERSON as

select distinct db2admin.TEMPXX_RIGHTSHORTCUT2.PERSONUUID, db2admin.TEMPXX_PERSON2.CNNAME as PERSONNAME,db2admin.TEMPXX_ROLE2.CNNAME as ROLENAME,db2admin.TEMPXX_ROLE2.ROLECODE from db2admin.TEMPXX_RIGHTSHORTCUT2,db2admin.TEMPXX_ROLE2,db2admin.TEMPXX_PERSON2 where db2admin.TEMPXX_RIGHTSHORTCUT2.ROLEID = db2admin.TEMPXX_ROLE2.ROLEID and db2admin.TEMPXX_RIGHTSHORTCUT2.PERSONUUID = db2admin.TEMPXX_PERSON2.PERSONUUID and TEMPXX_ROLE2.CREATEBY = '1'

except

select distinct db2admin.TEMPXX_ROLEDISABLE2.PERSONUUID, db2admin.TEMPXX_PERSON2.CNNAME as PERSONNAME,db2admin.TEMPXX_ROLE2.CNNAME as ROLENAME,db2admin.TEMPXX_ROLE2.ROLECODE from db2admin.TEMPXX_ROLEDISABLE2,db2admin.TEMPXX_ROLE2,db2admin.TEMPXX_PERSON2 where db2admin.TEMPXX_ROLEDISABLE2.ROLEID = db2admin.TEMPXX_ROLE2.ROLEID and db2admin.TEMPXX_ROLEDISABLE2.PERSONUUID = db2admin.TEMPXX_PERSON2.PERSONUUID and TEMPXX_ROLE2.CREATEBY = '1';