Skip Headers
Oracle® SQL Developer Supplementary Information for IBM DB2 Migrations
Release 3.0

E18460-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Offline Data Loading

SQL Developer uses the IBM DB2 Export utility, and the Oracle SQL*Loader utility, to provide an offline data loading capability. This is useful for migrating large volumes of table data. You can use the IBM DB2 Export utility to extract data from an IBM DB2 database, and save it to a file in one of several supported file formats. You specify the data you want to extract using a SQL query. SQL Developer automatically generates the required scripts to enable offline data dump as well as loading of the generated data. The generated scripts support the offline data loading of all types of data, including LOB data.

This chapter includes information about the following:

4.1 Script Directory Structure

All offline data export scripts are stored in target folder that you selects. To generate scripts, click Tools, then Migration, then Script generation, then Generate data move scripts.

SQL Developer creates a directory using timestamp in these subdirectories that represents the date and time you generated the offline data loading scripts. For example, a subdirectory called 21-08-07_17-56-16 indicates that you generated the scripts at 17:56 pm on September 21, 2007. SQL Developer creates the batch command file unload_script.bat or unload_script.sh in this subdirectory. This file contains commands used to extract data from the IBM DB2 source database, and store the data in delimited ASCII files.

The timestamp directory also contains SQL*Loader control files and a SQL*Loader script named oracle_ctl.bat or oracle_ctl.sh that are used to load the data into the Oracle database. Control files are contained within the Oracle folder.

The directory structure and its contents is summarized in the following table:

Directory Description
DB2\ timestamp The TIMESTAMP directory contains a subdirectory called ORACLE. It also contains a file named unload_script.bat or unload_script.sh. This file contains the commands used to export the data from the IBM DB2 database into delimited ASCII files. The exported data is automatically generated into the TIMESTAMP subdirectory.

The directory also contains a file called oracle_ctl.bat or oracle_ctl.sh. This file contains the SQL*Loader commands used to load the data files, generated by unload_script.bat or unload_script.sh, into the specified Oracle database.

DB2\ timestamp\Oracle This directory contains a SQL*Loader control file (.ctl) for each table from which you want to move. Any error during data move is saved as a log file here.

4.2 Unloading Data from an IBM DB2 Database

After generating the offline data loading scripts, you can use them to unload the data from the IBM DB2 database. To unload the data from the IBM DB2 database you must execute the EXPORT.BAT file. For more information about uploading data, see Section 4.2.1, "Procedure to Unload Data from an IBM DB2 Database".

The format for the export command in the unload_script.bat or unload_script.bat.sh file is as follows:

DB2 version 9 supports LOB data in separate files, which is better for migrating large data sizes. With version 8, to support large LOB data, you must modify the oracle ctl file command and db2 command in unload_script.bat or unload_script.sh.

The following table provides a description of the commands used in the unload_script.bat or unload_script.sh file:

Parameter Description
Db2 
This parameter invokes the IBM DB2 command line environment.
export to
This parameter is the call to the IBM DB2 export utility.
DB2\TABLE_NAME.DAT
This parameter is the name of the file that stores the data extracted from the IBM DB2 table where TABLE_NAME is the name of the table. All of the data files are generated in the DB2 directory. This enables the file to locate the data files when they are required.
of del
This parameter instructs the IBM DB2 Export utility to insert the data into delimited ASCII format. In this format, column delimiters separate column data, and row delimiters separate rows. This is the file format supported by the Oracle SQL*Loader utility.
lobsinsepfiles
This parameter specifies whether to create a separate file for each lob object in a table row or to use a single delimited file for all table data.
nochardel
This parameter instructs the IBM DB2 Export utility not to enclose character-based data in single quotes.
coldel#
This parameter instructs the IBM DB2 Export utility to use the "#" (hash or pound-sign) character as the column delimiter.
<select query>
This parameter is the SQL command that instructs the IBM DB2 Export utility to load all column data from the specified table residing in the specified schema.

The table data is exported to files with names in the format <catalog>.<schema>.<table>.dat. The format of file is as follows: data1#<COL_DEL> #data2#<COL_DEL>…<ROW_DEL> where COL_DEL and ROW_DEL come from migration offline preference settings.

Before you execute the DB2 data dump script, you must log in by entering a command in the following format:

db2 connect to <catalog> user <user name> using <password>

You can then execute the script using the logged connection session.

4.2.1 Procedure to Unload Data from an IBM DB2 Database

To unload data from the IBM DB2 database using the unload_script.bat or unload_script.sh file, you must perform the following steps:

  1. Activate the IBM DB2 Command Line Processor (CLP) Window by using the following command in a Microsoft DOS window command prompt:

    db2cmd
    
  2. Activate the required database using the following command in the CLP window:

    db2 acitvate database database_name
    
  3. Connect to the database using the following command in the CLP window:

    db2 connect to database_name user user_name using password
    
  4. Execute the unload_script.bat or unload_script.sh file by entering the command in the CLP window. For example:

    unload_script.bat
    

    The preceding example unloads data for the tables specified in the unload_script.bat file.

    The IBM DB2 Export utility generates all data files in the DB2 directory for subsequent processing by the oracle_ctl.bat or oracle_ctl.sh file.

    Note:

    To successfully execute the IBM DB2 Export utility you must have SYSADM or DBADM authority, or CONTROL or SELECT privileges, for each table you want to export.

4.3 Loading Data into an Oracle database

After generating the data files you must then load the data from these files into the Oracle database. To load the data into the Oracle database you must execute the oracle_ctl.bat or oracle_ctl.sh file.

The format for the SQL*Loader command in the oracle_ctl.bat or oracle_ctl.sh file for a table is as follows:

sqlldr %1/%2 control=Oracle\TABLE_NAME.ctl log=_TABLE_NAME.log

where %1/%2 represents the username/password supplied as command-line arguments.

The following table provides a description of each of the commands and parameters used when executing the SQL*Loader utility in the oracle_ctl.bat or oracle_ctl.sh file:

Parameter Description
sqlldr
This parameter of the command invokes the Oracle SQL*Loader utility.
%1/%2
This parameter is the user name and password of the user you want to connect to the Oracle database as.

These are passed as command line arguments from the command window. For example: oracle_ctl.bat “user” “password”

control=DB2\TABLE_NAME.ctl
This parameter specifies the SQL*loader control file you want to use during the data move. The name of the control file is the same as the table name.

The control file contains all the details about the data to be loaded, including the format of any date, time, or timestamp data, the column delimiter being used and the identification of any LOB columns.

log=TABLE_NAME.log
This parameter specifies the name of the log file generated by the SQL*loader utility. The name of the log file is the same as the table name.

4.3.1 Procedure to Unload Data from the IBM DB2 Database

To load the data from the data files into the Oracle database you must execute the SQL_LOAD_SCRIPT.BAT file to load the data by entering the following command:

  • Windows: oracle_ctl.bat

  • Linux: oracle_ctl.sh

Note:

The oracle_ctl.bat or oracle_ctl.sh file does not have to be executed from within the CLP window. You can execute the file from a command prompt.

4.3.1.1 SQL*Loader Control File

The format for the SQL*Loader control file for a table is as follows:

load data
infile 'SAMPLE.EMP_PHOTO.dat'
 "str '<EORD>\r\n'"
into table SAMPLE.EMP_PHOTO
fields terminated by '#<EOFD>#'
trailing nullcols
(
EMPNO CHAR(255),
PHOTO_FORMAT ,
PICTURE_ref  FILLER,
PICTURE LOBFILE(PICTURE_ref) TERMINATED BY EOF
)

On Linux systems, in the preceding example replace "str '<EORD>\r\n'" (the row data separator) with "str '<EORD>\r\n'".

The following table provides a description of each of the commands and parameters used in the SQL*Loader control file:

Parameter Description
Load data 
This parameter tells SQL*loader that this is the beginning of a new data load.
infile 'TABLE_NAME.dat
This parameter specifies the name of a data file containing data that you want to load.
into table TABLE_NAME
This parameter specifies the name of the table where the data is loaded.
fields terminated by '#<EOFD>#'
This parameter specifies that each field is terminated by the '#<EOFD>#' characters.
FIELD_NAME _ref FILLER
This parameter specifies an alias name for the LOB data file that contains the LOB data. The SQL*Loader utility retrieves the name of the LOB data file from the main data file during the data loading operation.

This parameter only applies to LOB data columns.

FIELD_NAME LOBFILE (lobFilename) TERMINATED BY EOF)
This parameter specifies that the LOB data file for the specified field be terminated by the EOF (End of File) marker.

This parameter only applies to LOB columns.


For more information about the SQL*Loader utility, see Oracle Database Utilities.