One of the things that irritates me the most about DB2 is the missing ability to access tables from more than one DB2-subsystem in the same SQL statement. It prevents you from copying data from one subsystem to another by using an INSERT INTO SELECT * FROM. Such a copy must always involve an UNLOAD followed by a LOAD. Of course you can do this in the same job with two steps in, but in DB2 version 7 it became possible to perform something similar to an INSERT INTO SELECT * FROM using DB2s LOAD utility. This option is called DB2 cross-loader by IBM.

In order to perform a socalled cross-load utility you need a DRDA/DDF connection between the two involved DB2 subsystems. Furthermore the DB2 package DSNUT810 (in version 7 DSNUT710 and DSNUT910 in version 9) must be present on these subsystems. BIND of this package is a part of the DB2 installation, so it should be in order. An then you just need a job step to run the cross-load. It might look like this:

//CROSLOAD EXEC PGM=DSNUTILB,PARM='SSID,MYJOB.CROSLOAD'
//SYSUT1   DD DISP=(MOD,DELETE,CATLG),SPACE=(CYL,(15,15),RLSE),
// DSN=&SYSUID..DB2LOAD.SYSUT1
//SORTOUT  DD DISP=(MOD,DELETE,CATLG),SPACE=(CYL,(15,15),RLSE),
// DSN=&SYSUID..DB2LOAD.SORTOUT
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  EXEC SQL
    DECLARE CROSLOAD CURSOR FOR
    SELECT * FROM DB2X.ANOTHER.TABLE
  ENDEXEC
  LOAD DATA INCURSOR(CROSLOAD) RESUME YES
  INTO TABLE MY.TABLE
/*

The SSID in PARM= you must replace with the name of the subsystem where the table MY.TABLE resides. You must replace DB2X with the name you use to access DB2 table ANOTHER.TABLE on the subsystem where ANOTHER.TABLE resides. It is a good idea to ensure that ANOTHER.TABLE and MY.TABLE contains the same columns defined in the same way, but it is not a requirement. The cross-loader will only transfer data between columns having the same name. If two columns with the same name have different definitions DB2 will try to convert and if the conversion fails the step fails. You are able to use AS in the SELECT part in order to give both source and target columns the same name if they have different names. The ability to rename columns using AS makes it possible to create all kinds of SELECT statements including JOINS.

The possibility to perform SQL statements as a command in DSNUTILB is not limited to SELECT. You may perform any kind of dynamically executable SQL statement including UPDATE and INSERT. You can use this to report in a table that a utility has been carried out or other interesting stuff.

In the LOAD statement you can of course use all the normally available options. For example you may use REPLACE instead of RESUME YES. Read more about the possibilities in Utility Guide and Reference. The link is to the DB2 version 8 manual.