update DB2 without journaling

Question

update DB2 without journaling

joshcsmith13

Hall of Fame

Points: 3381
More actions
September 25, 2006 at 3:42 pm

#62201

I have a linked server to our IBM DB2 database with a handful of tables that I need to update from SQL Server. Unfortunately, our iSeries admins to not want to enable journaling on one of the tables that I need to update. Is there a way to do this? I saw this article (http://www.sqlservercentral.com/columnists/bHullur/updatingas400linkedservertablesfromsqlserver.asp) which seems to suggest that you can perform the updates from DTS (called by a stored procedure), but that sure seems hokey. I'd like to keep all of the sql (for MS and DB2) in the same database.
We've tried using ODBC (with iSeries drivers) and IBMDASQL from IBM and DB2OLEDB from Microsoft for OLE, but neither one seems to work without journaling enabled on the iISeries.
Let me know if you've got any suggestions.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Gray SSChampion Points: 10667 More actions · Answer 1

Hello,

You might try setting the commit mode to "Commit Immediate(*NONE)" in the ODBC configuration. The setting is on the "server" tab, "advanced" button of the ODBC settings.

jg

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 2

Thanks Jeff, that's a good idea - sadly one that we've already tried without success. It seems like SQL Server causes a transaction regardless of the linked server properties or provider.

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

Yes, that is the default setting in the ODBC driver. SQL probably overrides that with the default transaction isolation level of READ COMITTED.

Even the lowest isolation level is READ UNCOMMITTED which still requires journaling. I suppose I should have thought of that.

Have you tried it with SET IMPLICIT_TRANSACTIONS OFF?

jg