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

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

October 30, 2009 0 Comments

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.

Win7_ODBC64_Bulk_ControlPanel_015

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

Win7_ODBC64_Bulk_ControlPanel_016

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

Win7_ODBC64_Bulk_ControlPanel_017

Assuming no typos or errors, all should be ok.

Win7_ODBC64_Bulk_ControlPanel_018

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.

Win7_ODBC64_Bulk_ControlPanel_019

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.

Win7_ODBC64_Bulk_ControlPanel_021

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

Win7_ODBC64_Bulk_ControlPanel_022

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.

Win7_ODBC64_Bulk_ControlPanel_023

IMPORTANT - The target table must already exist.

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

Win7_ODBC64_Bulk_ControlPanel_024

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.

Win7_ODBC64_Bulk_ControlPanel_025

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

Win7_ODBC64_Bulk_ControlPanel_026

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.

Win7_ODBC64_Bulk_ControlPanel_027

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.

Win7_ODBC64_Bulk_ControlPanel_028

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

Win7_ODBC64_Bulk_ControlPanel_029

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

Win7_ODBC64_Bulk_ControlPanel_031

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.

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

progress-blog-default-logo

Greg Stasko

View all posts from Greg Stasko on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.