DB2 returns errors if the connection string used by the BizTalk Adapter for DB2 does not include a Default Schema

  • Article

If you use the BizTalk Adapter for DB2 to connect to a DB2 system, you may see errors similar to the following in the application event log if you use a connection string that does not include a Default Schema:

Event ID: 5743
Source: BizTalk Server
Level: Warning
Description:
The adapter failed to transmit message going to send port “Send Port name” with URL “url”. It will be retransmitted after the retry interval specified for this Send Port. Details: “DB2TEST1.TABLE1 IS AN UNDEFINED NAME SQLSTATE: 42704, SQLCODE: -204”

In this example, DB2TEST1 is the Initial Catalog value that was specified in the DB2 connection string. TABLE1 is the DB2 table name that the query was being submitted against.

If the Initial Catalog value in the connection string is longer than 8 characters in length (e.g. DB2TEST1_TEXAS) and you are connecting to an older version of DB2 (e.g. DB2 for z/OS V7), the error message that is logged will be similar to the following:

Event ID: 5743
Source: BizTalk Server
Level: Warning
Description:
The adapter failed to transmit message going to send port “Send Port name” with URL “url”. It will be retransmitted after the retry interval specified for this Send Port. Details: :"THE NAME DB2TEST1_TEXAS? 8 IS TOO LONG. MAXIMUM ALLOWABLE SIZE IS SQLSTATE: 42622, SQLCODE: -107".

If you don’t specify a value for the Default Schema in the connection string, the BizTalk Adapter for DB2 prepends that Initial Catalog value to the Table name when submitting the query (i.e. SELECT, UPDATE, INSERT) to DB2. This will generally result in an error because the resulting name is not a valid DB2 name.

A different error message occurs when using a long Initial Catalog value against an older version of DB2 because the maximum length for the Schema name is 8 characters. The Initial Catalog value is prepended to the table name in the location that the schema is usually included. Therefore, you get an error about the name being too long.

If a Default Schema is specified in the connection string, the query will include the Initial Catalog, the Default Schema (HISDEMO1 in this example) and the table name as shown here for an UPDATE command:

UPDATE DB2TEST1.HISDEMO.TEST1 SET COL1 = ?, COL2 = ?, COL3 = ? WHERE COL1 = ? AND COL2 = ? AND COL3 = ?

Without a Default Schema, the same UPDATE command would be sent to DB2 as follows:

UPDATE DB2TEST1.TEST1 SET COL1 = ?, COL2 = ?, COL3 = ? WHERE COL1 = ? AND COL2 = ? AND COL3 = ?

Note: You may not see the same behavior when using the same connection string (or UDL) in other scenarios. For example, I was able to use a UDL in the Data Access Tool (DAT) without a Default Schema and I was able to run the Sample Query in the DAT without any errors against the same DB2 system.