Selecting data from a remote database

You can run a query on the local server that accesses data from a table, view, or table function on a remote server.

This is done by using a three-part qualified name. The relational database (RDB) name is specified as an additional qualifier for the object. Only one RDB name can be used in a single statement.

To return the rows for users with a disabled password from the USER_INFO_BASIC view on REMOTESYS, use the following statement:

SELECT AUTHORIZATION_NAME 
FROM REMOTESYS.QSYS2.USER_INFO_BASIC 
WHERE STATUS = 'DISABLED';

Db2® for i will connect to REMOTESYS to run the query and return the selected rows to the local system.

A table function can be used as well to direct the query to a remote system.

To return a list of jobs that have been running for more than 20 minutes on REMOTESYS, use the following statement:

SELECT JOB_NAME, AUTHORIZATION_NAME 
FROM REMOTE TABLE (REMOTESYS.QSYS2.ACTIVE_JOB_INFO(
            DETAILED_INFO => 'WORK')) 
WHERE JOB_ACTIVE_TIME < CURRENT TIMESTAMP - 20 MINUTES;

To access a table function on a remote RDB, the REMOTE TABLE keywords are required in the FROM clause in addition to the RDB qualifier. The remote system must recognize the REMOTE TABLE syntax or a syntax error will be returned.

Since a three-part object name or an alias that is defined to reference a three-part name of a table or view creates an implicit connection to the application server, a server authentication entry must exist. Use the Add Server Authentication Entry (ADDSVRAUTE) CL command on the application requestor specifying the server name, user ID, and password. The server name and user ID must be entered in upper case.

ADDSVRAUTE USRPRF(yourprf) SERVER(DRDASERVERNAME) USRID(YOURUID) PASSWORD(yourpwd)

See Distributed Database Programming for additional details on server authentication usage for DRDA.