BizTalk Adapter for DB2 Error SQLSTATE: HY000, SQLCODE: -270
My client had been using the BizTalk Adapter for DB2 from the BizTalk Adapters for Host Systems for quite some time with no significant issues. The target system was DB2 on an AS/400 (iSeries/System i). Late last week, the AS/400’s i5/OS was upgraded from V5R4 to V6R1 and the DB2 team did a database restore.
Somewhere around that time, BizTalk started logging the following errors:
An internal network library error has occurred. The requested command encountered an implementation-specific error condition on the target system. SQLSTATE: HY000, SQLCODE: –270
Searches for this error turned up only one post on a Host Integration Server newsgroup, which didn’t give us any answers.
I started out by looking up the SQLCODE –270 in the IBM SQL Messages and Codes book. That description had to do with unique indexes or constraints on distributed tables, which didn’t seem relevant to our situation.
I found the actual meaning of –270 in DB2OLEDB.H on the Host Integration Server 2006 CD (MSI\x86\PFiles\SDK\Include). It’s defined there as DB2OLEDB_DDM_CMDCHKRM, and CMDCHKRM means “command check reply message.” The problem is that the error code(s) contained in the reply message are not surfaced, so this was still a dead end.
The Microsoft OLE DB Provider for DB2 is the foundation for the DB2 Adapter, so in order to rule out BizTalk and the DB2 Adapter as possible problems, we created a five-line C# command-line app:
OleDbConnection cn =
new OleDbConnection("Provider=DB2OLEDB;REST_OF_CONNECTION_STRING_HERE");
cn.Open();
OleDbCommand cmd =
new OleDbCommand("SELECT COUNT(*) FROM A-TABLE-IN-DB2", cn);
int rc = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine("Rows in table: " + rc.ToString());
Sure enough, the test app encountered the same error. The problem was definitely in the OLE DB Provider for DB2.
The OLE DB Provider requires various “packages” (a DB2 concept) to exist in the DB2 system. The packages correspond to various transaction isolation levels, so they are named READ UNCOMMITTED, REPEATABLE READ, etc. We did not enable transactions in the DB2 connection string, nor did we configure isolation level in BizTalk, so we still don’t know which isolation level (and thus which package) is being used. SERIALIZABLE is a good guess since it is often the BizTalk default.
When a connection is opened, if the DB2 Provider finds that the package associated with the active isolation level does not exist, it is supposed to automatically create it. The active user account must have sufficient rights in DB2. If that is not an option, then the Data Access Tool can be used to manually create the packages (the Packages button on the next-to-last page of the New Data Source wizard).
In our case, the user account should have had enough permissions to automatically create a package, but evidently that process failed and resulted in the obscure SQLSTATE: HY000, SQLCODE: –270 error. As soon as I manually created the packages in the Data Access Tool, the error disappeared and everything began working normally again!
This page of the OLE DB Provider for DB2 documentation is an excellent resource for understanding the DB2 packages, the auto-create process, various error messages that may result and more.
