Operation Example of data Transfer task under DB2

Simplifies the data transfer process of DB2 for Linux, UNIX, and Windows using the From CURSOR option of the DB2 LOAD utility. This article describes the LOAD from CURSOR feature and provides an example of the use of two interface Command line Processor and admin_cmd stored procedures.

Brief introduction

A typical DB2 data transfer task involves three steps:

Export data from the source database to a temporary data interchange file in binary or text format

Transferring generated files between systems

Import or mount data from a file into the target database

In the case of large amounts of data, it often takes a long time to generate a data interchange file using the EXPORT utility. In addition, different database coding pages and operating systems must be considered when moving data into and out of the database.

You can use the LOAD utility's from CURSOR option to avoid these problems. When the From CURSOR option is specified, the load utility directly takes the result set of an SQL query as the source of the data load operation, so that no temporary data interchange file is required. Therefore, the LOAD from CURSOR is a quick and easy way to transfer data between different table spaces or databases. You can perform the LOAD from CURSOR on the command line, or you can execute it in an application or stored procedure by using a DB2 admin_cmd stored procedure. This article describes the LOAD from CURSOR feature and provides an example of the use of two interface Command line Processor (CLP) and admin_cmd stored procedures.

Transfer a table to another table space

First, see How to move a table from one table space to another table space. You may need to perform this data transfer if the page size of the table space in which you create the table is not appropriate, or if you should access the table with another buffer pool. In DB2 versions prior to 9.1, tables were often transferred between tablespaces due to the maximum size of the table space. However, in DB2 9.1 and later, this should no longer be a problem, because the table space size limit has been significantly increased (provided that you use large table space instead of the regular tablespace used previously).

This example scenario first creates the DB2 sample database. This can be done by invoking the Db2sampl command at the command line, as shown in Listing 1.

Listing 1. Create a SAMPLE database

C:\>db2sampl
Creating　database　"SAMPLE"...
Connecting　to　database　"SAMPLE"...
Creating　 tables　and　data　in　schema　"FECHNER"...
'db2sampl'　processing　complete.