Create a cross-database view in DB2

-- Step 1: connect to the Business Database and execute this statement

Connect to sjsda user db2admin using pass1009;
-- Update dbm cfg using federated yes;

-- Then restart the db2 Service

-- Db2stop force;
-- Db2start;

-- Step 2: execute the following statement. For example, if you use another account, replace "db2admin." In the following mode.

-- Delete existing objects

Drop wrapper drda;

-- Create the wrapper ddl statement

Create wrapper "DRDA"
LIBRARY db2drda. dll
OPTIONS (DB2_FENCED N
);

-- Create server ddl statements

-- AUTHORIZATION, PASSWORD, and DBNAME must be replaced, which indicates the database of the platform.

Create server tempserver
TYPE DB2/UDB
VERSION 8.1:
WRAPPER DRDA
AUTHORIZATION "User Name"
PASSWORD "PASSWORD"
OPTIONS
(DBNAME TESTPP
);

-- Create user mapping ddl statements

-- Replace REMOTE_AUTHID and REMOTE_PASSWORD, which indicate the platform database.

-- Db2admin in "FOR db2admin" will be used later. It is best to use the business database management account. FOR example, if you use another account, you 'd better replace the following pattern "db2admin .".

Create user mapping for db2admin
SERVER tempserver
OPTIONS
(REMOTE_AUTHID Username
, REMOTE_PASSWORD Password
);

-- Create DDL statements across database views
-- View of basic user information

Create nickname db2admin. TEMPXX_PERSON2 FOR tempserver. db2admin. TEMPXX_PERSON;
Create nickname db2admin. TEMPXX_PERSONACCOUNT2 FOR tempserver. db2admin. TEMPXX_PERSONACCOUNT;
Create view SYS_PERSON
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;

-- View the basic information of an organizational unit

Create nickname db2admin. TEMPXX_ORG2 FOR tempserver. db2admin. TEMPXX_ORG;
Create view SYS_ORG
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;

-- View of ing between subsystems and organizations

Create nickname db2admin. TEMPXX_SYSORGSHORTCUT2 FOR tempserver. db2admin. TEMPXX_SYSORGSHORTCUT;
Create nickname db2admin. TEMPXX_SYSTEM2 FOR tempserver. db2admin. TEMPXX_SYSTEM;
Create view SYS_ORGSUBSYSREL
Select db2admin. TEMPXX_SYSORGSHORTCUT2.SYSID, db2admin. Sid, 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;

-- User-organization ing View

Create nickname db2admin. TEMPXX_ORGPERSON2 FOR tempserver. db2admin. TEMPXX_ORGPERSON;
Create view SYS_ORGPERSON
Select db2admin. TEMPXX_ORGPERSON2.PERSONUUID, db2admin. TEMPXX_ORGPERSON2.ORGUUID, db2admin. TEMPXX_ORGPERSON2.ISBELONG from db2admin. TEMPXX_ORGPERSON2;

-- User-system role ing View

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
Select db2admin. example, db2admin. example, db2admin. TEMPXX_ROLE2.SYSID, db2admin. TEMPXX_ROLE2.CNNAME from db2admin. example, db2admin. TEMPXX_ROLE2 where db2admin. Role = db2admin. TEMPXX_ROLE2.ROLEID
Except
Select db2admin. Allow, db2admin. Allow, db2admin. Allow, db2admin. TEMPXX_ROLE2.CNNAME from db2admin. TEMPXX_ROLEDISABLE2, db2admin. TEMPXX_ROLE2 where db2admin. Allow = db2admin. TEMPXX_ROLE2.ROLEID;

-- User-application role ing View

Create view SYS_APPROLE_PERSON
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;