TABLE COMPARISON transform does not want to do the 'update'

dataservices job design
1

Hi,

My name is Jaak and I am new in these forums. The problem I have is related to the “Table Comparison” transform.
The trouble is that the TC does not want to UPDATE the records in target table. It only INSERTS.

I have searched these forums and found some very useful hints, but I can’t solve my issue nevertheless…

The version of BODS I am using is v12.1.1.0 and my data flow is as follows:

Query transform (from multiple source tables) -> Table Comparison -> Merge -> Target table

The INPUT dataset is basically like:

Column1 (int)
Column2 (int)
Column3 (int)
Debug_ID (varchar)-- this is actually a ROW_ID from MS-SQL Database and is unique
Date_Updated        -- this is sysdate()

And the TARGET is the same:

 Column1 (int)
Column2 (int)
Column3 (int)
Debug_ID (varchar)
Date_Updated (datetime)

The Table Comparison setup is as follows

Row-by-row select

Input Primary Key columns

Debug_ID

Compare Columns

Column1 (int)
Column2 (int)
Column3 (int)

The TARGET table DOES NOT have the primary key defined.

Now, the TC compares a row that comes from source with the specific Debug_ID.
If it DOESN’T EXIST in the target then the TC does an INSERT.
If it DOES EXIST then the TC should do an UPDATE but does INSERT again.

Thank you in advance,
Jaak

jaaktmgi :estonia: (BOB member since 2011-09-13)

2

You should use a Map operation Transform between TC and target table in order to assign the operation to be performed based on the Operation code detected for the specified row.

Hope that helps!

Astro :argentina: (BOB member since 2007-02-15)

3

Well, problem is that you are missing a primary key. Try opening your target table’s properties and checking the “use input keys” option.

Moreover, try to see how the record is flagged once out of the TC. This can be done in debug mode, where you will see records’ control flags (placed at the very left of the record).

Each record will be flagged by one among the following characters:

I = record is marked for Insert
U = record is marked for Update
D = record is marked for Delete
none = record is marked for Normal (i.e. Insert)

for further infos, go to technical manuals and look for “Table_Comparison transform”

CLS69 :it: (BOB member since 2009-06-11)

4

Yes the target should have Primary Key. Otherwise the database and DI cannot understand which record to update.

Just try to write a UPDATE query without a WHERE clause!!! Is that posssible? :shock:

ganeshxp :us: (BOB member since 2008-07-17)

5

Thank you all for your kind help!

Finally, after spending more than a week, I managed to solve it.

The aggravating circumstances were that the problem was on a system already in production and the code had been released more than a year ago. I had no slightest idea where to look at.

The root cause of the issue was that the target table (in Oracle) was configured to use BULK LOAD. After turning the bulk load off the updates started to work immediately.

Just for a side-remark: The primary key on the target is not necessary. As I wrote in my first post, the target table has a column that has unique values and BODS is happy with that.

The “use input keys” option in target table was left unchecked as well.

Kind regards,
Jaak

jaaktmgi :estonia: (BOB member since 2011-09-13)

6

Ok, thanks for sharing. On my side, I would never imagine to check bulk loading, hence you taught me something new. Thank you
Ciao

CLS69 :it: (BOB member since 2009-06-11)

7

Hi. How did you switch off bulk load for your oracle target table or did you mean that bulk loading for turned on in bods and you switched that off?

yeewaitu (BOB member since 2007-02-07)

8

Correct!
Bulk loading was turned ON in bods and I turned it OFF.

jaaktmgi :estonia: (BOB member since 2011-09-13)

9

I checked and the bulk loading is not turned on, but the updates were not working still. Its strange cos I tried another table and the updates were working.

Any ideas? The difference between the 2 tables are that the number of columns on the one which was not working is significantly more than the the table which is working. I am stumped. :frowning:

yeewaitu (BOB member since 2007-02-07)