8.1 Data Load scripts

< Day Day Up >

Data from other systems with no direct connection are mostly loaded into the database with a load utility. Oracle uses the SQL*Loader in combination with a control file. With DB2 UDB you can use:

DB2 UDB Load utility

The load utility is capable of efficiently moving large quantities of data into newly created tables, or into tables that already contain data. The load process contain the four phases:
- Load data
- Build indexes
- Delete rows with a unique key violation or a DATALINK violation
- Copy index data from a system temporary table space to the original table space
DB2 UDB Import utility

The import utility inserts data from an input file into a table or updatable view. If the table or view receiving the imported data already contains data, you can either replace or append to the existing data.

The load utility is faster than the import utility, because it writes formatted pages directly into the database, while the import utility performs SQL INSERTs. The load utility does not fire triggers, and does not perform referential or table constraints checking (other than validating the uniqueness of the indexes).

Oracle loader utility has two modes: direct path and conventional path. Oracle's direct path more closely resembles the DB2 load utility while the conventional path more closely resembles the DB2 import utility.

Oracle define its own Data Definition Language (DDL) to load data from a file into the database. The DDL is different to the DB2 UDB syntax.

For the most frequently used commands in an Oracle control file, in this chapter are scripts to convert the controls files to DB2 load or DB2 import files.

We recommend to migrate the more complex Oracle control file manually and implement workarounds for the functionality not available within the DB2 UDB load command. For detailed information about the DB2 load command and DB2 import command, see Chapter 6, "Data conversion" on page 211.

8.1.1 Migration approach

We propose the following approach in converting Oracle load commands:

Examine the Oracle control file, the datafile format, and the target table.
Check for alternatives, such as the use of database links, DB2 Information Integrator, etc. to improve the data import.
Convert the Oracle control file to the proper DB2 command. In the next section we show you an easy way to convert the scripts automatically. Please note the variety of DB2 load options.

8.1.2 Loading fixed-format fields

In Example 8-1 is a simple control file to load data into the table accounts of the ORA_EMP database. The file contains a reference to the datafile accounts.dat, the target table, the fixed data positions, and its data types.

Example 8-1: SQL*Loader control file with fixed-format fields

 LOAD DATA INFILE '/home/ora_usr/accounts.dat' INTO TABLE accounts (  acct_id             POSITION(0001:0003) NUMBER    ,dept_code          POSITION(0004:0006) CHAR    ,acct_desc          POSITION(0009:0100) VARCHAR2    ,max_employees      POSITION(0101:0103) NUMBER    ,current_employees  POSITION(0104:0106) NUMBER    ,num_projects       POSITION(0107:0107) NUMBER )

Example 8-2 shows the corresponding DB2 load command. The details of the load command are nearly the same. The most sensitive part during the migration is the correct conversion of the POSITION() specification. To avoid errors, we recommend to migrate at least this part of the control file automatically. In Appendix D, "Converter for SQL*Loader" on page 383 is the Perl script conv_ctl.pl to convert simple SQL*Loader files to DB2 load files.

Example 8-2: DB2 UDB Load file for table ACCOUNTS

 LOAD FROM '/home/ora_usr/accounts.dat' of ASC METHOD L         ( 0001 0003          ,0004 0006          ,0009 0100          ,0101 0103          ,0104 0106 ) INSERT INTO accounts         ( acct_id          ,dept_code          ,acct_desc          ,max_employees          ,current_employees );

8.1.3 Loading variable-length data

Example 8-3 is a simple Oracle control file to load data with a variable length into the table ACCOUNTS. The delimiter in this sample is a comma, the fields may be enclosed in double quotes. The datafile accounts.dat looks like:

    101,"ACT","Major Bank Co.",30,11,4    301,"ACT","Large Telco Inc.",30,0,4    101,"IT","Huge Software Co.",50,0,4    203,"MKT","Basic Insurance Co.",15,0,3

Example 8-3: SQL*Loader control file with variable-length fields

 LOAD DATA INFILE '/home/ora_usr/accounts.dat' INTO TABLE accounts FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ( acct_id  ,dept_code  ,acct_desc  ,max_employees  ,current_employees  ,num_projects )

The Perl script conv_ctl.pl in Appendix D, "Converter for SQL*Loader" on page 383 converts the control file to a proper DB2 Load file. Modify the generated DB2 load command if you need additional features.

Example 8-4: DB2 load commend with variable-length fields

 LOAD FROM '/home/ora_usr/accounts.dat' of ASC MODIFIED BY COLDEL, METHOD P (1, 2, 3, 4, 5 ) INSERT INTO accounts         ( acct_id          ,dept_code          ,acct_desc          ,max_employees          ,current_employees )!

8.1.4 Initializations in Oracle SQL*Loader control file

In the Oracle SQL*Loader file you are able to specify initialization values for defined data.

Example 8-5 shows the same sample as above, but with conditions for the columns dept_code and current_employees.

Example 8-5: SQL*Loader control file with conditions for table ACCOUNTS

 LOAD DATA CHARACTERSET we8pc850 INFILE '/home/ora_usr/accounts.dat' INTO TABLE accounts ( acct_id            POSITION(0001:0003) NUMBER   ,dept_code          POSITION(0004:0006) CHAR DEFAULTIF dept_code=" "   ,acct_desc          POSITION(0009:0100) VARCHAR2   ,max_employees      POSITION(0101:0103) NUMBER   ,current_employees  POSITION(0104:0106) NUMBER NULLIF current_employees="0"   ,num_projects       POSITION(0107:0107) NUMBER )

The column dept_code is set to its default value if the loaded data is blank and the variable current_employees is set to null if the loaded data is 0. Such data manipulations are not allowed with DB2 load. To achieve the data manipulation, run a separate UPDATE command after the data is loaded:

    UPDATE accounts    SET dept_code=DEFAULT    WHERE dept_code=" ");    UPDATE accounts    SET current_employees=NULL    WHERE current_employees="0");

In Appendix D, "Converter for SQL*Loader" on page 383 is the Perl script gen_load_update.pl used to generate DB2 UDB UPDATE commands from Oracle control files.

8.1.5 Loading data into multiple tables

To achieve data loads into multiple tables, migrate the Oracle commands in this sequence:

Separate the load commands to single commands per script and file.
Convert the separated load command. Consider the suggestions we make in the previous chapters.
Control the DB2 commands with scripts (sh, batch, etc.) regarding the needs of your system.

< Day Day Up >