Copy Data From A Remote DB2 Database Using DB2 For i 7.1

Published: July 25, 2012

by Michael Sansoterra

IBM DB2 for i 7.1 introduced the ability to query a remote DB2 for i database directly without having to invoke an explicit CONNECT statement. Querying a remote table with one SQL statement is great, but lacks one important feature: requests for remote data are usually combined with requests for local data.

However, DB2 for i does not allow references to tables from multiple relational databases in a single statement, until now that is. The i7.1 technology refresh 4 update allows an INSERT/SELECT statement to reference tables from different DB2 relational databases. (Even if you're not at i7.1 you can still perform this task.)

Say, for example, your company has two IBM i machines (a.k.a., iSeries, System i, AS/400) with relational databases named QERP (ERP) and QINV (Inventory), and that these machines need to share item master data. In the past, you could accomplish data sharing using FTP, DDM, or embedded SQL using DRDA (to name a few techniques).

This new feature allows a developer to write a single INSERT/SELECT statement (executing on QERP) that can query remote relational database QINV and place the results in a local table on QERP. The bonus is a CONNECT statement isn't required either.

Assuming a developer is coding a data retrieval routine on system QERP, a statement to copy QINV's item master data to a local QTEMP table is as simple as this:

INSERT INTO QTEMP/ITEM_MASTER 
SELECT * 
  FROM QINV/DEV/ITEM_MASTER IM

This statement uses the three-part naming convention that consists of the relational database name/schema (library) name/table-or-view name. When using the SQL naming convention, use a dot separator instead of the slash.

There are a few things to keep in mind when the SQL naming convention:

• This technique uses DRDA to communicate with the remote relational database and does an implicit CONNECT behind the scenes. Therefore, DRDA should be configured appropriately. If you're not sure how to configure your local and remote machines to communicate using DRDA, review the references section below.
• The local IBM i must have a registered relational database entry for the remote machine. In this case, machine QIBM must have a relational database directory entry for QINV. Use the Add Relational Database Directory Entry (ADDRDBDIRE) command to add a relational database reference to the remote machine or partition.
• Currently, this technique can only be used with an INSERT/SELECT statement where the target of the INSERT is on the local machine and all tables in the SELECT refer to the same remote relational database. Other statements that mix references to multiple relational databases will return the error: SQL0512 - Statement references objects in multiple databases. Hopefully one day soon, developers will be able to reference multiple relational databases in other SQL DML statements.
• If you don't mind writing embedded SQL or a SQL stored procedure, other SQL statements such as UPDATE, SELECT, CREATE TABLE AS, etc., can benefit from this feature by simply coding an INSERT/SELECT from the remote database into a temporary table. Thereafter, once the data is in a QTEMP table, a secondary SQL statement such as an UPDATE can make use of the temporary data copied from the remote system.
• Only the "client" (i.e., local) IBM i machine needs to be at i7.1 with Technology Refresh 4 for this feature to work. The "server" (i.e., remote) IBM i can be on an earlier version of IBM i/OS. (I verified it works against i6.1.)
• When connecting to other systems, remember the SELECT statement will run on the remote box. Therefore you can only reference SQL features available on the remote box's i/OS release. For example, when connecting to an i6.1 machine, you cannot use the new i7.1 built-in functions.
• When referencing remote routines like user-defined functions in the SELECT, include the schema name when using the SQL naming convention, for example: INSERT INTO LOCAL_TABLE SELECT MYLIB.MYFUNCTION() ….
• The SYSTEM_USER and CURRENT_SERVER special registers can be used to query the remote user and remote server names.

One more thing, if you're not on i7.1, there has always been a relatively easy way to do this type of INSERT local/SELECT remote on older systems. To do so, follow these steps:

1. Make sure there is a remote database entry configured on the local box (see references section below).

2. Enter the SQL SELECT statement for a remote database query in a source member. Do not use the three part naming syntax.

3. Create a query management query (QMQRY) from the source member using the CRTQMQRY command:

CRTQMQRY QMQRY(REMOTE_QRY)  SRCFILE(SRCLIB/QQMQRYSRC)

This command will create a query management query object containing the SQL definition from the source member.

4. Execute the QMQRY against the remote server and dump the results locally to an outfile:

STRQMQRY QMQRY(REMOTE_QRY) OUTPUT(*OUTFILE) 
OUTFILE(QTEMP/REMOTE_DTA) RDB(MYREMOTE) USER(MIKE)   PASSWORD(T0@ST)

Overall, IBM just brought developers one step closer to the idealized dream of being able to access and manipulate data from heterogeneous systems with a single SQL statement. In a future tip, I'll explore a mechanism that allows local and multiple remote relational database tables (DB2 only) to be referenced in a single SQL SELECT statement.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORY AND REFERENCES

Using DRDA to Execute SQL Statements on Another iSeries

Inserting Data From A Remote Database, IBM i SQL programming manual

Distributed Database Programming, IBM i manual

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot