Microsoft KB Archive/286464

= PRB: Error 7008 Occurs When Using DB2OLEDB Provider with DB2/400 =

Article ID: 286464

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft Host Integration Server 2000 Standard Edition
 * Microsoft SNA Server 4.0 Service Pack 2
 * Microsoft SNA Server 4.0 Service Pack 3
 * Microsoft SNA Server 4.0 Service Pack 4

-



This article was previously published under Q286464



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:
 * Create the journal receiver (CRTJRNRCV).
 * Create the journal (CRTJRN) by inputting the journal receiver created in step 1.
 * 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.



STATUS
This behavior is by design.



MORE INFORMATION
For more information on using the commands referenced in this article, please refer to the IBM OS/400 Backup and Recovery (Document Number SC41-5304-03) and IBM OS/400 CL Reference V4R4 (Document Number SC41-5722-03).

AS/400 and DB2 are IBM products.

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Additional query words: OS400 OS/400 HIS 2000

Keywords: kb3rdparty kbdb2 kbprb KB286464

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.