DB2 to Oracle

Offline Data Loading "http://download.oracle.com/docs/html/B10229_01/ch4.htm"

The Migration Workbench uses the IBM DB2 UDB 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 UDB Export utility to extract data from an IBM DB2 UDB 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. The Migration Workbench automatically generates the required scripts to enable offline data loading. The generated scripts support the offline data loading of all types of data, including LOB data.

This chapter includes information on the following:

• Script Directory Structure

   

• Generating Export Scripts

   

• Unloading Data from an IBM DB2 UDB Database

   

• Loading Data into an Oracle database

   

Script Directory Structure

All offline data export scripts are stored in the
%ORACLE_HOME%OMWBSQLLOADER_SCRIPTS directory. The UDBn subdirectory in this directory represents the version of the IBM DB2 UDB plug-in that you are using. The Migration Workbench 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-02_17-56-16 indicates that you generated the scripts at 17:56p.m. on September 21^st 2002. The Migration Workbench creates the batch command file EXPORT.BAT in this subdirectory. This file contains commands used to extract data from the IBM DB2 UDB source database, and store the data in delimited ASCII files.

The EXPORT.BAT file creates a subdirectory called ORACLE in the TIMESTAMP directory. In addition to storing the generated data files, the ORACLE directory contains SQL*Loader control files and a SQL*Loader script called
SQL_LOAD_SCRIPT.BAT that are used to load the data into the Oracle database.

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

1. Directory	Description
   %ORACLE_HOME% Omwbsqlloader_scriptsUDBn	The UDBn directory stored the scripts for the offline data load.
   %ORACLE_HOME% Omwbsqlloader_scriptsUDBn timestamp	The TIMESTAMP directory contains a subdirectory called ORACLE. It also contains a files called EXPORT.BAT file. This file contains the commands used to export the data from the IBM DB2 UDB database into delimited ASCII files. The exported data is automatically generated into the ORACLE subdirectory.
   %ORACLE_HOME% Omwbsqlloader_scriptsUDBn timestampOracle	The ORACLE directory contains a file called SQL_LOAD_SCRIPT.BAT. This file contains the SQL*Loader commands used to load the data files ,generated by EXPORT.BAT, into the specified Oracle database. This directory also contains a SQL*Loader Control File (.CTL) for each table you want to move data from.

   Generating Export Scripts

   To create the required offline data load scripts for all tables you must do the following:

   1. From the Oracle Model, select the Tables folder.

       

   2. Choose Object -> Generate SQL*Loader Scripts.

   Note: You can also generate the scripts for a specific table by selecting that table from the Oracle Model, then choosing Object -> Generate SQL*Loader Scripts.

    

2. When you are sure you want to generate the SQL*Loader scripts for the tables specified, click Yes, in the Generate SQL*Loader Scripts dialog prompt.

    

3. The Migration Workbench informs you of the location of the generated scripts on your system.

    

Unloading Data from an IBM DB2 UDB Database

After generating the offline data loading scripts, you can use them to unload the data from the IBM DB2 UDB database. To unload the data from the IBM DB2 UDB database you must execute the EXPORT.BAT file. Refer to the "Procedure to Unload Data from an IBM DB2 UDB Database" section for information on this procedure.

The format for the export command in the EXPORT.BAT file for a table that does not contain any LOB data is as follows:

db2 export to OracleTABLE_NAME.dat of del modified by chardel’’ coldel0x07 
decplusblank select * from SCHEMA_NAME.TABLE_NAME

The format for the export command in the EXPORT.BAT file for a table that does contain LOB data is as follows:

db2 export to OracleTABLE_NAME.dat of del lobs to lob_directory_path lobfile 
TABLE_NAME modified by lobsinfile chardel’’ coldel0x07 decplusblank select * 
from SCHEMA_NAME. TABLE_NAME 

The following table provides a description of the commands used in the EXPORT.BAT file:

1. Parameter	Description
   Db2	This parameter invokes the IBM DB2 UDB command line environment.
   export to	This parameter is the call to the IBM DB2 UDB export utility.
   %ORACLE_HOME%Omwb sqlloader_scripts UdbnOracle TABLE_NAME.DAT	This parameter is the name of the file that stores the data extracted from the IBM DB2 UDB table where TABLE_NAME is the name of the table. All of the data files are generated in the %ORACLE_HOME%Omwbsqlloader_scriptsUDBnOracle directory. This enables the SQL_LOADER_SCRIPT.BAT file to locate the data files when they are required.
   of del	This parameter instructs the IBM DB2 UDB Export utility to insert the data into delimited ASCII format. In this format, column delimiters separate column data, and row delimeters separate rows. This is the file format supported by the the Oracle SQL*Loader utility.
   lobs to lob_directory_ path	This parameter specifies directory path where the LOB files are stored. The directory path location specified must be an absolute directory path and must end with a backslash () character. For example c:templobs
   lobfile TABLE_NAME	This parameter specifies the base file names for the LOB files. Each LOB file is post-fixed with a three-digit extension that uniquely identifies an individual LOB file. For example EMP.001, EMP.002, EMP.003.
   lobsinfile	This parameter instructs the IBM DB2 UDB Export utility to generate separate data files for each LOB column.
   chardel	This parameter instructs the IBM DB2 UDB Export utility to enclose character-based data in single quotes. You must ensure that there are no spaces between the chardel identifier and the string delimiter character as IBM DB2 UDB does not support spaces between them.
   coldel0x07	This parameter instructs the IBM DB2 UDB Export utility to use the ’^G’ (single non-printable character, CTRL-G) character as the column delimiter. The character is expressed in HEX notation 0x07. If you need to change the delimiter character, you must edit the EXPORT.BAT file to change the character referred to after the coldel parameter. You must also change the character referred to after the FIELDS TERMINATED BY parameter in the corresponding SQL*Loader Control File to the character you choose for the coldel parameter. You must ensure that there are no spaces between the coldel identifier and the column delimiter character as IBM DB2 UDB does not support spaces between them.
   decplusblank	This parameter instructs the IBM DB2 UDB Export utility to omit the leading plus (+) character from positive decimal numbers.
   select * from SCHEMA_ NAME.TABLE_NAME	This parameter is the SQL command that instructs the IBM DB2 UDB Export utility to load all column data from the specified table residing in the specified schema. In the following example, the command instructs the IBM DB2 UDB Export utility to unload all data from all columns of the EMP table residing in the UDB schema: select * from UDB.EMP

   Procedure to Unload Data from an IBM DB2 UDB Database

   To unload data from the IBM DB2 UDB database using the EXPORT.BAT file, you must perform the following steps:

   1. Activate the IBM DB2 UDB 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 EXPORT.BAT file by entering the command in the CLP window:

      export.bat
      
      

      This command unloads data for the tables specifies in the EXPORT.BAT file.

      The IBM DB2 UDB Export utility generates all data files in the ORACLE subdirectory for subsequent processing by the SQL_LOAD_SCRIPT.BAT file.

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

    

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 SQL_LOAD_SCRIPT.BAT file.

The format for the SQL*Loader command in the SQL_LOAD_SCRIPT.BAT file for a table is as follows:

sqlldr user_name/password control=TABLE_NAME.ctl log=TABLE_NAME.log direct=true

The following table provides a description of each of the commands and parameters used when executing the SQL*Loader utility in the SQL_LOAD_SCRIPT.BAT file:

Parameter	Description
sqlldr	This parameter of the command invokes the Oracle SQL*Loader utility.
user_name/password	This parameter is the user name and password of the user you want to connect to the Oracle database as. You may also need to specify a connect identifier of the form @connect identifier after the password to connect to a remote Oracle database. For example: sqlldr system/[e-mail]
control=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.
direct=true	This parameter specifies the load method used, by either a conventional path or direct path. The true value specifies a direct path load. A direct path load is typically faster than a conventional path load. If the SQL*loader control file contains any field masks, direct path load will be inactive for that table because direct path load does not support field masks.

Procedure to Unload Data from the IBM DB2 UDB 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:

	sql_load_script.bat

Note: The SQL_LOAD_SCRIPT.BAT file does not have to be executed from within the CLP window; it can be executed in a normal DOS command prompt

The sql_load_script.bat file does not have to be executed from within the CLP window; it can be executed from within a normal DOS command prompt.

SQL*Loader Control File

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

load data
infile ’TABLE_NAME.dat’
into table TABLE_NAME
fields terminated by X’07’ optionally enclosed by ’’’
trailing nullcols
( FIELD_NAME ,
  FIELD_NAME ,
  FIELD_NAME DATE ’YYYYMMDD’,
  FIELD_NAME TIMESTAMP,
  FIELD_NAME DATE ’HH24.MI.SS’,
  lobFilename FILLER CHAR(50),
  FIELD_NAME LOBFILE (lobFilename) TERMINATED BY EOF)

Fields representing date, time, and timestamp data have masks specified after their name. The masks provide a format pattern, instructing the SQL*Loader utility how to interpret the data in the record. For example, the third, fourth, and fifth fields specified above represent date, time, and timestamps fields respectively.

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 X’07’ optionally enclosed by ’	This parameter specifies that each field is terminated by the ’^G’ (single non-printable character, CTRL-G) character. The character is expressed in HEX notation i.e. X’07’. The optionally enclosed by parameter specifies that character-based data may be enclosed by single quotes and that the SQL*loader utility should recognize this when processing the data.
trailing nullcols	This parameter tells SQL*Loader utility to treat any relatively positioned columns that are not present in the record as null columns.
lobFilename FILLER CHAR(50)	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.