IBM Support

Why is the Capture program receiving SQLCODE -311 from DB2?

Question & Answer


Question

Why is the Capture program receiving SQLCODE -311 from DB2?

Answer

The most common reason that the Capture program receives SQLCODE -311 from DB2 is incorrect attributes on a change-data (CD) table. For example, the Capture program receives SQLCODE -311 if a before-image column is defined as Not Null. Before-image columns must allow nulls because no before value exists for an insert into a source table.

CD-table attributes can be incorrect in either of the following cases:

    • The CD table was created with the wrong attributes.
    • The associated source table is dropped and recreated, or altered in such a way that the CD table is no longer consistent with the source table.
The Capture program records two messages in the ASN.IBMSNAP_TRACE table when it receives a SQLCODE -311 occurs: Capture's ASN0001E and DB2's SQL0311N. If the SQLCODE -311 is due to an incorrect CD table attribute, the completed messages look similar to the following:
    ASN0001E: The Capture program encountered an SQL error. The routine name exec_isr_cd(); the SQL request is EXECUTE INSERT TO CD; the table name is "MYSCHEMA"."MYCDTABLE"; the SQLCODE is -311; the SQLERRML is 1; the SQLERRMC is 7.
    SQL0311N The length of string host variable number "7" is negative or greater than the maximum. SQLSTATE=22501

The key phrase to look for in these messages is "EXECUTE INSERT TO CD". The "variable number" and SQLERRMC might be different for your specific situation.

When trying to resolve the SQLCODE -311, first verify your CD table attributes. If any are incorrect, change them and restart the Capture program. If this does not resolve the problem, contact IBM Support.

[{"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"SQL replication - Capture","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1;8.2;9.7;10.2.1.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Misc","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

More support for:
InfoSphere Replication Server

Software version:
8.1, 8.2, 9.7, 10.2.1.0

Document number:
658451

Modified date:
16 June 2018

UID

swg21023736

Page Feedback