Copy Data From A Remote DB2 Database Using DB2 For i 7.1
July 25, 2012 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:
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.
RELATED STORY AND REFERENCES
Inserting Data From A Remote Database, IBM i SQL programming manual
Distributed Database Programming, IBM i manual