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.
Table 1. Differences between the CLP and ADMIN_CMD procedure.| Differences | CLP | ADMIN_CMD_procedure |
|---|
| Syntax | The query statement as well as the source database used by the cursor are defined outside of the LOAD command using a DECLARE CURSOR statement. | The query statement as well as the source database used by the cursor is defined within the LOAD command using the LOAD from (DATABASE database-alias query-statement) |
| User authorization for accessing a different database | If the data is in a different database than the one you currently connect to, the DATABASE keyword must be used in the DECLARE CURSOR statement. You can specify the user id and password in the same statement as well. If the user id and password are not specified in the DECLARE CURSOR statement, the user id and password explicitly specified for the source database connection are used to access the target database. | If the data is in a different database than the one you are currently connected to, the DATABASE keyword must be used in the LOAD command before the query statement. The user id and password explicitly specified for the source database connection are required to access the target database. You cannot specify a userid or password for the source database. Therefore, if no userid and password were specified when the connection to the target database was made, or the userid and password specified cannot be used to authenticate against the source database, the ADMIN_CMD procedure cannot be used to perform the load. |
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:
- 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 above 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.
- Suppose the source and target tables reside in different databases with the following definitions:
Table ABC.TABLE1 in database 'dbsource' has 3 columns:
- ONE INT
- TWO CHAR(10)
- THREE DATE
Table ABC.TABLE2 in database 'dbtarget' has 3 columns:
- ONE VARCHAR
- TWO INT
- THREE DATE
Provided that you have enabled federation and cataloged the data source ('dsdbsource'), you can declare a nickname against the source database, then declare a cursor against this nickname, and invoke the LOAD command with the FROM CURSOR option, as demonstrated in the following example:
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
Or, you can use the DATABASE option of the DECLARE CURSOR statement, as demonstrated in the following example:
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 andUSING 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.
Note:- The previous example shows how to load from an SQL query against a cataloged database through the CLP using the DATABASE option of the DECLARE CURSOR statement. However, loading from an SQL query against a cataloged database can also be done through the db2LoadAPI, by defining the piSourceList and piFileTypevalues of the db2LoadStruct structure to use the sqlu_remotefetch_entry media entry and SQLU_REMOTEFETCH media type respectively.
- As demonstrated in the previous example, the source column types of the SQL query do not need to be identical to their target column types, although they do have to be compatible.
Restrictions
When loading from a cursor defined using the
DATABASE option (or equivalently when using the sqlu_remotefetch_entry media entry with the
db2Load API), the following restrictions apply:
- The SOURCEUSEREXIT option cannot be specified concurrently.
- The METHOD N option is not supported.
- The usedefaults file type modifier is not supported.
