4.6 The Generate Data Transfer Scripts task

< Day Day Up >

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).

click to expand
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.

click to expand
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.

click to expand
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.

Note

In addition to generating data movement files, MTK also generates the online data movement functionality "on the fly" from within the tool itself so that if you choose to move the data on-line within the GUI within the Deploy tab, the MTK tool knows how. That is why if you skip from the Refine tab to the Deploy tab, you will not have an online option of extracting and loading data; these options will remain in a "grayed out" state.

< Day Day Up >