Back to Index

 

How to use SQL Batching in Oracle (ODBC)

Note: This document refers to accessing Oracle via ODBC or OleDb and is not applicable for ODP.NET (the Oracle Data Provider object).

The Oracle™ database supports SQL Batching via ODBC and/or OleDb, whereas BlueSky Integration Studio is able to batch SQL output statements into an in-memory queue and send them to the database in batches of 'x' statements based on the RowsPerTransaction property.  This functionality can greatly enhance the load time for any database, including Oracle, up to 300%!

Some databases support this functionality with no additional SQL wrappers where 'x' number of SQL statements can simply be sent to the database in a single command object, however Oracle™ databases require that batch SQL statements are wrapped in a PL/SQL code block.

For instance, let's say we set the RowsPerTransaction property to 1000.  BlueSky Integration Studio will read and process up to 1000 rows from the input data source before attempting to insert or update them in the output data source.   When it does send them to the Oracle™ database, it constructs one large SQL statement for the entire transaction.   Since the Oracle database requires that the SQL statements are wrapped in a PL/SQL code block, we must set the PreTransactionSQL and PostTransactionSQL appropriately.

RowsPerTransaction = 1000

PreTransactionSQL = BEGIN

PostTransactionSQL = END;

By setting the properties this way, BlueSky Integration Studio will output the following batch SQL statement for each transaction:

BEGIN

INSERT INTO <table> VALUES(<value1>, <value2>, <value3>, etc...);

INSERT INTO <table> VALUES(<value1>, <value2>, <value3>, etc...);

INSERT INTO <table> VALUES(<value1>, <value2>, <value3>, etc...);

INSERT INTO <table> VALUES(<value1>, <value2>, <value3>, etc...);

ETC.... to 1000

END;

Job Monitoring when using SQL Batching for Oracle via ODBC or OleDb

Also, please note that because the transaction is wrapped in a PL/SQL block, Oracle will not return the number of rows processed, but instead will return 1, since to Oracle this is one transaction.   This means that in the Job Monitor (where you can view the progress of your job), will see 'x' number of rows processed for the input source, but only 1 record for each transaction in the output source.

For instance, say we set the RowsPerTransaction property to 1000, and we are loading 50000 records from an input source.  At the end of the job, the Job monitor will show 50000 records read from the input source, but only 50 for the output source because BlueSky Integration Studio sent 50 batches of SQL Statements to Oracle.

 


© 2003 - 2007 Relational Solutions, Inc. - All rights reserved