Oracle to DB2 UDB Conversion Guide2003

4.6 The Generate Data Transfer Scripts task

The next task in our conversion is the Generate Data Transfer Scripts tab. From this tab it is possible to:

• Generate scripts to unload data from the Oracle database

• Generate scripts to Import or Load data into DB2 UDB

This tab is separated into three panes: left, middle, and right. When first opened, the left pane shows all the tables in the Oracle schema that are being converted; the right pane shows the file that contains all of the converted DB2 code; the middle pane contains options that may be specified when data import or load is selected (Figure 4-22).

Figure 4-22: The Generate Data Transfer Scripts tab

Choosing Data Import or Load

The choice of whether to import or load data into DB2 UDB is usually dictated by the amount of data.

For small amounts of data, Import is usually sufficient. This method inserts data into a database table from an external file, inserting one row at a time. During the import of data, the table remains accessible to other applications. All applicable constraints and triggers remain in effect, and are activated in the usual way as rows are inserted.

For large amounts of data, LOAD is usually preferable. The LOAD utility constructs page images containing many rows and inserts them into the database one page at a time. It requires exclusive access to the tablespace being loaded. During the loading of data, the tablespace that contains the table is not accessible to other applications. Applicable constraints and triggers are deactivated for the duration of the load. Applicable check constraints and foreign key constraints are enforced at the end of the load process. The enforcement of business rules that are implemented by triggers are not guaranteed after the load completes.

During load and import, MTK will disable the referential integrity. MTK deletes the foreign keys before load and import, and re-create them after.

Import or Load options

The following options are available for Import or Load:

• MODE:

  • INSERT: IMPORT will insert new rows without affecting the existing content.

  • REPLACE: IMPORT will delete all existing data and replace it with the imported data.

• File Formats:

  • DEL: delimited ASCII format

    Specifies that the data is represented in delimited text files. These files contain streams of data values, ordered by row, and then by column. Column delimiters separate the values (a comma is the default), and new line characters separate the rows. Character strings are enclosed in string delimiters (a double-quote is the default). NULL values are represented by nothing between the column delimiters for a particular column.

    If DEL is selected, some advanced options are automatically set by the IBM DB2 Migration Toolkit:

    • Character delimiter first: The character delimiter is given the highest priority, so that a special character between two character delimiters will be read as just another character.

    • Column delimiter: Set to a comma (,).

    • Character string delimiter: Set to a double quote (").

      Note

      During extraction, MTK doubles any string delimiter found within a string. This allows the use of any string delimiter without a problem. There is no need to search the database for a character never used in the data.

• ASC: non-delimited ASCII format

  This format specifies that the data is represented in non-delimited text files, which have the columns of data in fixed positions. No delimiters are needed. Nulls are identified by a table of null value indicators at the end of the row.

  If ASC is chosen, MTK sets the record length advanced option. Instead of new line characters marking the end of each record, the length of the data is used to set the number of characters read for each row.

  Note

  For complete information about Import or Load options, please refer to the manual(s): IBM DB2 Universal Database Data Movement Utilities Guide and Reference (version 7), SC09-2955, or (version 8), SC09-4830.

Advanced Import or Load options

When the Advanced Options button is clicked, a screen opens that displays the options for the data movement that you have selected (Import or Load). Figure 4-23 shows the screen opened with the options available for Load. One parameter which we would like to bring to your attention is the message file. DB2 writes the output of Import and Load to the message file specified here. The default is message.txt in /tmp directory on UNIX and c:/temp on Windows.

Figure 4-23: Advanced Options screen for LOAD

4.6.1 Creating unload and load scripts

For our conversion we chose to load the data into DB2; all the defaults will be taken:

• MODE: insert

• File format: ASC non-delimited ASCII format

• Directory for data extraction: MTK default

• Advanced Options: none

Figure 4-24 shows the screen after the Create Scripts button is clicked, and scripts have been generated.

Figure 4-24: The Generate Data Transfer Scripts screen after scripts generated

4.6.2 Files generated by the Generate Data Transfer Script task

After the Create script button is clicked on the Generate Data Transfer Scripts task, the following files are created:

• DataMove_your_file_name_data.bat

• DataMove_your_file_name_data.sh

  The files ending in _data.bat, or _data.sh contain statements to extract data from the source database.

• DataMove_your_file_name.qry

  The file ending in .qry contains examples of statements generated for selecting and converting the data from the source database.

• DataMove_your_file_name_db2.bat

• DataMove_your_file_name_db2.sh

  The files ending in _db2.bat, or _db2.sh contain statements to execute the load data into DB2.

• DataMove_your_file_name.bat

• DataMove_your_file_name.sh

  The files ending in .bat, or .sh contain DB2 load scripts.

For our example, the following scripts were created:

• DataMove_tabs_views_seqs_data.bat

• DataMove_tabs_views_seqs _data.sh

• DataMove_tabs_views_seqs.qry

• DataMove_tabs_views_seqs _db2.bat

• DataMove_tabs_views_seqs _db2.sh

• DataMove_tabs_views_seqs.bat

• DataMove_tabs_views_seqs.sh

  Note

  In there are cases where not enough resources may be available on one machine, you might decide to convert and refine the metadata in a convenient location, such as on your desktop workstation, and later go to the server system to migrate the large amount of data. For these reasons, it is also possible to manually extract the data from the source, and load/import the data into DB2 using the scripts created in this task. Read the MTK documentation for more information.