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:
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 |
| 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. |
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:
For DB2 version 9 data export:
db2 export to <file name> of DEL modified by lobsinsepfiles coldel"#" timestampformat=\"YYYY/MM/DD HH.mm.ss\" datesiso nochardel <select query>
For DB2 version 8 data export:
db2 export to <file name> of DEL modified by coldel"#" timestampformat=\"YYYY/MM/DD HH.mm.ss\" datesiso nochardel <select query>
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.
To unload data from the IBM DB2 database using the unload_script.bat or unload_script.sh file, you must perform the following steps:
Activate the IBM DB2 Command Line Processor (CLP) Window by using the following command in a Microsoft DOS window command prompt:
db2cmd
Activate the required database using the following command in the CLP window:
db2 acitvate database database_name
Connect to the database using the following command in the CLP window:
db2 connect to database_name user user_name using password
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.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: |
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. |
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:
Theoracle_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.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.