Moving data using the CURSOR file type

By specifying the CURSOR file type when using the LOAD command, you can load the results of an SQL query directly into a target table without creating an intermediate exported file.

Additionally, you can load data from another database by referencing a nickname within the SQL query, by using the DATABASE option within the DECLARE CURSOR statement, or by using the sqlu_remotefetch_entry media entry when using the API interface.

There are three approaches for moving data using the CURSOR file type. The first approach uses the Command Line Processor (CLP), the second the API, and the third uses the ADMIN_CMD procedure. The key differences between the CLP and the ADMIN_CMD procedure are outlined in the following table.

To execute a LOAD FROM CURSOR operation from the CLP, a cursor must first be declared against an SQL query. Once this is declared, you can issue the LOAD command using the declared cursor's name as the cursorname and CURSOR as the file type.

For example:

1. Suppose a source and target table both reside in the same database with the following definitions:
   Table ABC.TABLE1 has 3 columns:

   • ONE INT
   • TWO CHAR(10)
   • THREE DATE
   Table ABC.TABLE2 has 3 columns:

   • ONE VARCHAR
   • TWO INT
   • THREE DATE
   Executing the following CLP commands will load all the data from ABC.TABLE1 into ABC.TABLE2:

   DECLARE mycurs CURSOR FOR SELECT TWO, ONE, THREE FROM abc.table1
      LOAD FROM mycurs OF cursor INSERT INTO abc.table2

   Note: The preceding example shows how to load from an SQL query through the CLP. However, loading from an SQL query can also be accomplished through the db2Load API. Define the piSourceList of the sqlu_media_list structure to use the sqlu_statement_entry structure and SQLU_SQL_STMT media type and define the piFileType value as SQL_CURSOR.
2. Suppose the source and target tables reside in different databases with the following definitions:

CREATE NICKNAME myschema1.table1 FOR dsdbsource.abc.table1 
DECLARE mycurs CURSOR FOR SELECT TWO,ONE,THREE FROM myschema1.table1 
LOAD FROM mycurs OF cursor INSERT INTO abc.table2 

DECLARE mycurs CURSOR DATABASE dbsource USER dsciaraf USING mypasswd 
FOR SELECT TWO,ONE,THREE FROM abc.table1 
LOAD FROM mycurs OF cursor INSERT INTO abc.table2

Using the DATABASE option of the DECLARE CURSOR statement (also known as the remotefetch media type when using the Load API) has some benefits over the nickname approach:

Performance

Fetching of data using the remotefetch media type is tightly integrated within a load operation. There are fewer layers of transition to fetch a record compared to the nickname approach. Additionally, when source and target tables are distributed identically in a multi-partition database, the load utility can parallelize the fetching of data, which can further improve performance.

Ease of use

There is no need to enable federation, define a remote datasource, or declare a nickname. Specifying the DATABASE option (and the USER and USING options if necessary) is all that is required.

While this method can be used with cataloged databases, the use of nicknames provides a robust facility for fetching from various data sources which cannot simply be cataloged.

To support this remotefetch functionality, the load utility makes use of infrastructure which supports the SOURCEUSEREXIT facility. The load utility spawns a process which executes as an application to manage the connection to the source database and perform the fetch. This application is associated with its own transaction and is not associated with the transaction under which the load utility is running.