How to create a cross-database view in DB2

This article describes how to create a cross-database view in DB2. The following describes the specific procedures for creating a cross-database view in DB2, I hope this will help you in your future studies.

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, replace "db2admin." In the following mode with other accounts.

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

REMOTE_AUTHID and REMOTE_PASSWORD must be replaced, indicating 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 "db2admin." mode.

 
 

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

Create DDL statements for DB2 cross-database view

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 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 db2admindb2admin.TEMPXX_PERSON2.PERSONUUID = db2admin.TEMPXX_PERSONACCOUNT2.PERSONUUID; 
 
 

View of organization Basic Information

 
 

  
  
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;  
 
 

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 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 db2admindb2admin.TEMPXX_SYSORGSHORTCUT2.SYSID = db2admin.TEMPXX_SYSTEM2.SYSID and 
db2admindb2admin.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 as   
  
  
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 as   
  
  
select db2admin.TEMPXX_RIGHTSHORTCUT2.PERSONUUID, db2admin.TEMPXX_RIGHTSHORTCUT2.ROLEID,   
  
  
db2admin.TEMPXX_ROLE2.SYSID ,db2admin.TEMPXX_ROLE2.CNNAMEfrom db2admin.TEMPXX_RIGHTSHORTCUT2,db2admin.
  
  
TEMPXX_ROLE2 where db2admindb2admin.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 db2admindb2admin.TEMPXX_ROLEDISABLE2.ROLEID = db2admin.TEMPXX_ROLE2.ROLEID;   
 
 

User-application role ing View

 
 

  
  
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 db2admindb2admin.TEMPXX_RIGHTSHORTCUT2.ROLEID = db2admin.TEMPXX_ROLE2.ROLEID and 
  
  
db2admindb2admin.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 db2admindb2admin.TEMPXX_ROLEDISABLE2.ROLEID = db2admin.
  
  
TEMPXX_ROLE2.ROLEID and db2admindb2admin.TEMPXX_ROLEDISABLE2.PERSONUUID = db2admin.TEMPXX_PERSON2.
  
  
PERSONUUID and TEMPXX_ROLE2.CREATEBY = '1';   
 
 

The above content is an introduction to creating a cross-database view for DB2. I hope you will find some gains.