PRB: Error 7008 Occurs When Using DB2OLEDB Provider with DB2/400 (286464)

SYMPTOMS

When you use the Microsoft OLE DB Provider for DB2 (DB2OLEDB) to try to execute an INSERT, UPDATE, or DELETE statement against a DB2/400 table, you may receive the following error message:

OLE/DB provider returned message: A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 55019, SQLCODE: -7008

NOTE: You may also receive this error if you are trying to insert, update, or delete from a SQL Server linked server using the DB2OLEDB provider, and you are using the OPENQUERY syntax.

If you are trying to insert, update, or delete from a SQL Server linked server by using the DB2OLEDB provider, you may also receive the following error when you are using 4-part name query:

Server: Msg 7306, Level 16, State 2, Line 1 Could not open table 'CATALOGNAME.SCEMANAME.TABLENAME' from OLE DB provider 'DB2OLEDB'. Unknown provider error.

If you turn on tracing by using DBCC TRACEON(7300), you may receive the following error message:

Server: Msg 7306, Level 16, State 2, Line 1 Could not open table 'CATALOGNAME.SCEMANAME.TABLENAME' from OLE DB provider 'DB2OLEDB'. Unknown provider error. OLE DB error trace [Non-interface error: OLE DB provider DB2OLEDB returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported]. OLE DB error trace [OLE/DB Provider 'DB2OLEDB' IOpenRowset::OpenRowset returned 0x80040e14: Unknown provider error.].

CAUSE

This will occur under the following combination of conditions:

The data source is DB2/400.
The Auto Commit initialization property is set to FALSE. You can set the initialization properties in a UDL file using Microsoft Data Links, the OLE DB initialization string, or ADO connection string.
The Distributed Transaction initialization property is set to TRUE. NOTE: this applies to Host Integration Server only.
The target object that you are attempting to update is an AS/400 physical file or keyed physical file residing in an AS/400 library (as opposed to a DB2/400 collection) for which journaling has not been enabled.

RESOLUTION

The following are two ways to prevent this problem:

Set Auto Commit to TRUE, or set Distributed Transaction to FALSE. In this case, INSERT, UPDATE, and DELETE operations are automatically committed whenever they are executed. Journaling is not required .
Enable journaling in AS/400 for that particular table (physical file) for which you want to do an INSERT, UPDATE, or DELETE.

To enable journaling, do the following:
1. Create the journal receiver (CRTJRNRCV).
2. Create the journal (CRTJRN) by inputting the journal receiver created in step 1.
3. Start the journal physical file (STRJRNPF) by inputting the journal and journal receiver created in steps 1 and 2 and associating the journal to a specific SQL table (physical file).

IMPORTANT: If you are trying to do the INSERT from a Microsoft SQL Server Linked Server, you must enable journaling. SQL Server automatically starts a transaction and therefore setting Auto Commit to TRUE will not work.

MORE INFORMATION