Bulk Loading Oracle without SQL*Loader | Part 2 - Loading Data into Oracle with ODBC Driver

As you've done with DB2 in part 1, you need to configure a Progress DataDirect ODBC Driver for the target database, which is Oracle.

Enter all of the connection information for the target Oracle database.

Do a "Test Connect" to ensure no fat-fingering. Not surprising, you need to supply your user name and password.

Assuming no typos or errors, all should be ok.

Now to the real meat of the loading operation.

Click on the "Bulk" tab in the Oracle ODBC driver configuration pane, again, mirroring what you did with DB2. Now, rather than exporting data, you want to prepare for loading.

You could go directly to the "Load Table…" button, but let's be safe and verify the exported DB2 data against the target Oracle database before actually attempting to load it.

Clicking the "Verify…" button presents you with a dialog where you select the configuration file (the .xml file) associated with the DB2 "Export Table" operation. You're going to choose the one generated when you exported the ASSIGNMENTS table, the ASSIGNMENTS.xml file.

Specify the target Oracle table name and the configuration file, and choose "Verify". (While the DB2 and Oracle tables have the same name here, they do not have to.)

After clicking the "Verify" button, you will be prompted to enter your ID and password for the target Oracle system. The .xml metadata file, which was built from the source DB2 database columns, will be analyzed against the column definitions of the target Oracle table, to see if there are any inconsistencies or mismatches.

IMPORTANT - The target table must already exist.

Assuming all is in order, you'll see the following.

Note that completion of the verify process does NOT mean that all is ok. It simply means that the operation ran to completion. It's the difference between taking a test, and passing one. Even though the "verify" operation completed, some potential problems were detected.

You're alerted that the verification process has encountered a possible problem. Turns out to be a possible code-page conflict, in the TASK column. Not flagged as an outright error, just a potential problem. Consider it an "FYI" or "Heads up".

The warning isn't anything that concerns you, so, you now initiate the actual loading into Oracle. Click the "Load Table..." button on the Bulk pane of the ODBC driver.

All of the information required for the load has been carried over from the "Verify" operation. The name of the target table, the locations of the ASSIGNMENTS.csv data file, the ASSIGNMENTS.xml metadata file, and the log file where details of the operation will be recorded.

Continuing on. Clicking the "Load Table" button will again prompt you for your target Oracle database credentials.

AND THAT'S IT! No complicated applications or configuration files. If you've been successful up until this point, you should see this.

You can validate this a number of ways. You can go and do a "SELECT * from ASSIGNMENTS" on your Oracle system to see that the data is there. You can also go to the ASSIGNMENTS.log file, and see what has been recorded in the log.

45 rows successfully loaded! That's all there is to it. By simply using a few additional fields in the Progress DataDirect Connect for ODBC drivers, you've been able to easily move data from DB2 on z/OS to Oracle! No complicated command line utilities or expensive, difficult-to-learn applications.