Copy Data from Remote to Local #IBMi

Just a quick "blog-like" entry on how to copy data from a remote system to the local system using Db2 SQL.

The key is to use the 3-level naming support available in Db2 for i for about 10 years. Basically you can code something like the following to get to a remote location's database files:

SELECT * FROM CHICAGO.QIWS.QCUSTCDT;        

Run this from SQL iQuery, the Command Entry screen, or from IBM ACS and it will pull in the records from the QCUSTCDT file on the remote server named CHICAGO.

To make this all work you need to have the remote location identified on your system. The WRKRDBDIRE command is used to setup these remote entries. Basically you give it a name, the IP address, and a couple of settings. For example, to setup the CHICAGO remote location, you might code the following:

ADDRDBDIRE RDB(CHICAGO) RMTLOCNAME('8.8.8.8' *IP) RMTAUTMTH(*USRID)        

I used the *USRID option for remote authentication, but the default value may be best for your shop--basically this controls what is sent to the remote for the Authentication step (e.g., the equivalent of Sign On). When mixing IBM i release levels, as I always have to accommodate, then *USRID works best in my situation, but your situation would be different.

To pull in data from a remote file we combined the 3-level names with an INSERT statement. Consider the following INSERT statement that copies data from the remote CHICAGO location to the local system.

INSERT INTO MYDATA.CUSTOMERS 
   SELECT * from CHICAGO.MYDATA.CUSTOMERS WHERE BALDUE > 10000;        

In this example, the customers located on the Chicago server are copied to the CUSTOMERS file on the local system when the remote record's BALDUE is over $10,000.

What about Functions? For example, one of our SQL iQuery customers has Power systems installed in nearly a dozen locations--all of which are on different land masses (i.e., islands). So the ability to drive there and check out the system isn't a frequent occurrence. I put together a little script that uses the #SQLTools DISK_LIST UDTF (you can use IBM's SYSDISKSTAT on V7R3 and later) to pull in the current status of the Disk drivers on those remote systems. The so called Protection Status column containing anything except 'ACTIVE' (e.g., DEGREDATED) is what we are looking for. IBM added PROTECTION_STATUS in a late TR on V7R3/R4 but not to R2 so if your disks on V7R2 are degraded, QSYS2.SYSDISKSTAT won't help, use the #SQLTools DISK_LIST UDTF instead.

When using any UDTF, you can not qualify it to 3-level name; that is chicago.sqltools.disk_list() returns a syntax error. But there is a work around!

You move the 3-level name to a dummy WHERE clause. When you do that, the SQL processor realizes what you're trying to do and allows it. For example:

SELECT * FROM table( SQLTOOLS.DISK_LIST() ) d
   WHERE EXISTS (SELECT * FROM CHICAGO.sysibm.sysdummy1);        

This runs over the CHICAGO partition, as expected!

Of course I realize if you're on V7R3 or later, then in the context of Disk Protection Status, you can easily use the following IBM-supplied View instead:

select unit_number, resource_name, percent_used,protection_status
 from chicago.qsys2.sysdiskStat;        

Remember that a single physical system can contain multiple partitions (e.g., Production, DevOps, Test). Three-level naming can be used between those partitions the same way you do between physical systems. This provides an easy way to update data on the development partition when SAVRSTOBJ isn't configured or isn't right for the situation.

That's all there is to it.