my client requires in-day ETL (i.e. during working hours).
In order to achieve this I want to run all operations on a table within a single database transaction.
The current workflow has the ‘Delete data from table before loading’ option set on the target table, and reloads all of the data each time.
I’ve changed the flow so that it loads into a copy of the target table (_stage), and then my intention was to DELETE from the target table, and then INSERT into the target table from the (_stage) table, but as a single database transaction.
If I use the following, then BODI appears to be sending a COMMIT after the DELETE statement and before the INSERT statement which is not what I want.
sql(‘RM_DS’, ‘DELETE FROM journal_batch’);
sql(‘RM_DS’, ‘INSERT INTO journal_batch SELECT * from journal_batch_load’);
I then tried the following, but BODI only seems to send the DELETE statement to the database, and completly ignores the INSERT
sql(‘RM_DS’, ’
DELETE FROM journal_batch;
INSERT INTO journal_batch SELECT * from journal_batch_stage;
');
Any suggestions out there would be appreciated. I thought about sending an anonymous block but we’re on a DB2 target database, which doesn’t appear to support blocks.
Could call a database stored proc to do it I suppose but I can’t believe that there is no way of controlling the database transactions from BODI itself!!!
Indicates that this target is included in the transaction processed by a batch or real-time job. This option allows you to commit data to multiple tables as part of the same transaction. If loading fails for any one of the tables, no data is committed to any of the tables.
Transactional loading can require rows to be buffered to ensure the correct load order. If the data being buffered is larger than the virtual memory available, Data Integrator reports a memory error.
The tables must be from the same datastore. If you choose to enable transactional loading, other options are not available:
Rows per commit
Use overflow file, and overflow file specification
Number of loaders
Enable partitioning
Delete data from table before loading
Bulk loader options
Pre load commands
Post load commands
Data Integrator also does not parameterize SQL or push operations to the database if transactional loading is enabled.
Transaction order:
Transaction order indicates where this table falls in the loading order of the tables being loaded. By default, there is no ordering. All loaders have a transaction order of zero. If you specify orders among the tables, the loading operations are applied according to the order. Tables with the same transaction order are loaded together. Tables with a transaction order of zero are loaded at the discretion of the data flow process.
thanks Data Guy for your response. I subsequently found that the problem I was having.
We’re using a DB2 data mart, and apparently there is a client setting which means that all queries are auto-committed. (you can turn this off if you’re using Control Centre). I was assuming that DB2 would naturally work like Oracle and by default allow you to use transactions to control your work.
I though that it was Data Integrator that was doing the COMMIT when I didn’t want it to, but in fact it was behaving itself.
I think I recieve this error when I change my Data source. Am I right…
The Data_Transfer target table or file <ORACLE_…_DT.dat> only accepts rows flagged with the NORMAL operation code. It received a row flagged with UPDATE or DELETE operation code.
If you have a Table Comparison in front of a Data Transfer, that is your problem. The Data Transfer wants only normal rows while the Table Comparison generates insert, update and delete operation rows.
(BOB member since 2006-08-19)