DB2 federal database exercises

DB2 federal database exercises

We cannot directly query the two databases on the same instance. This is a good practice in sqlserver and can be referenced directly using the database name. The federated database must be used in DB2)

Two instances on the same server: db2inst1 db2inst2 Database: mydb1 mydb2 (same for local federation and remote Federation, so only remote Federation is demonstrated here)

The following operations perform Federation on db2inst2 on db2inst1, that is, you can access db2inst2 through nickname on db2inst1. All operations are performed under db2inst1:

DB2 update dBm CFG using federated yesdb2 connect to mydb1 -- note that this statement cannot be executed using DB2 "", but needs to enter the DB2 command line processor interaction mode. This is a problem discovered only after many attempts, note that the string following the password must be double quotation marks -- drda is the default adapter db2create server f_mydb2 type DB2/UDB version 9.7 wrapper drda authorization db2inst2 password "******" Options (dbname 'mydbdb ') DB2 "create user mapping for" db2inst1 "server f_mydb2 options (add remote_authid 'db2inst2', add remote_password '******') "-- A Table tb2db2" create nickname mydb2_tb2 for f_mydb2.db2inst2.tb2 "DB2" select * From mydb2_tb2 "DB2" select * From tb1 a inner join mydb2_tb2 B on B exists in db2inst2. id =. ID"

 

In addition, relevant tables and views are attached:

Sysibm. syswrappers -- each row represents the registered converter (wrapper) sysibm. sysservers -- each row represents the data source information, such as the data source version number and type. sysfuncmappings-including the basic information of function ing, such as the function ing name and server-based name. It can be used to identify errors such as the failure to find the corresponding function sysibm. sysserveroptions -- includes settings for server options, such as collating_sequence and proxy_authid. These options can affect the query performance. systypemappings -- includes the basic information of Data Type ing, such as the correspondence between the data types on the federated server side and the short Data Types of remote data sources. It is generally used to determine the errors related to data types. sysuseroptions -- This table contains user options and their setting values, such as the username and password used to connect to the remote data source (remote_authid and remote_password, it can be used to identify connection errors or access permission errors (in combination with the sysdbauth system table of DB2 itself) sysibm. syswrapoptions -- contains optional information about the created converter, such as whether to use the "trusted" or "fenced" option sysibm when using the converter. syspassthruauthsysibm. sysfuncmapparmoptionssysibm. sysfuncmapoptions

View:

SYSCAT.FUNCMAPPINGS SYSCAT.FUNCMAPPARMOPTIONS SYSCAT.FUNCMAPOPTIONS SYSCAT.NICKNAMES SYSCAT.PASSTHRUAUTH SYSCAT.SERVEROPTIONS SYSCAT.SERVERS SYSCAT.TYPEMAPPINGS SYSCAT.USEROPTIONS SYSCAT.ROUTINESFEDERATED