Resolving The Problem

This document will discuss the problem and resolution to an issue reported where ODBC XA transactions rolled back automatically in SQL Server when using a SSIS package.

Problem

The following error message is thrown in SQL Server 2008 when attempting to execute a SSIS package that uses the System i Access for Windows ODBC driver to execute a simple SELECT statement in a XA transaction:

SSIS package "AS400 to SQL CATS Transfer.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4001100A at Data Flow Task: Starting distributed transaction for this container.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "SQL Server Destination" (85)" wrote 520 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Information: 0x4001100B at Data Flow Task: Committing distributed transaction started by this container.
Warning: 0x8004D019 at Data Flow Task: The transaction has already been aborted.
SSIS package "AS400 to SQL CATS Transfer.dtsx" finished: Success.
The program '[18216] AS400 to SQL CATS Transfer.dtsx: DTS' has exited with code 0 (0x0).

A CWBCOTRC shows the XA_END process returning a RC=100 (The rollback was caused by an unspecified reason). Because of this, a XA_ROLLBACK is performed. Thus, the reason why no data is returned to the client.

t=0d54 07:50:49.799 [00]        Communication: XA: errClass=0x9 errCode=0x64
t=0d54 07:50:49.799 [00]        Communication: XA:sendrecv Exit rc=0
t=0d54 07:50:49.799 [00]          Comm-System: SysObj 2 : Use Count Inc'd to 5
t=0d54 07:50:49.799 [00]          Comm-System: SysObj 2 : Use Count Dec'd to 4
t=0d54 07:50:49.799 [00]          Comm-System: SysObj 2 : Use Count Dec'd to 3
t=0d54 07:50:49.799 [00]        Communication: XA:commonflow Exit rc=100
t=0d54 07:50:49.799 [00]        Communication: XA:end Exit rc=100
t=0d54 07:50:49.799 [00]        Communication: XA:rollback Entry

It was found the application is calling the SQLSetConnectAttr API with the attribute being 'ENLIST_IN_DTC' (1207). For this attribute, ODBC expects the parameter to be the address of the transaction object, or a null pointer, indicating that the transaction is done. From the trace, the application is passing a NULL pointer on the SQLSetConnectAttr request, indicating the transaction is done. Because the transaction is never committed and a XA_End is never issued, we end up sending a TMFAIL flag on the X_End, which causes the rollback.

t=0d54 07:50:49.718 [00]                ODBC: setConnectAttr attr=1207 value=0x0 len=4
t=0d54 07:50:49.718 [00]                 ODBC: mts.mtsinfo.dump fDTC=1 ITrans=0x1EA30878 xaMapper=0x1CC3D628
t=0d54 07:50:49.718 [00]                 ODBC: mts.xamapper.dump desc= hrc=0x0 rc=0 rmCookie=0xFC000001 dbc=0x1CC35D80 ITransResource=0x1CBF9F50 ITransEnlist=0x1D9C07C8 xid=ae3371d4a1b1e34ab7747e1b88a8abf594deb192f82eaa488dae312fb0cb45cb1d317571e178594a87773462f767d248b3878c2852152242bbeaa721f97edb3200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
t=0d54 07:50:49.718 [00]                 ODBC: odbcmts.mtsinfo.enlistInDTC Entry
t=0d54 07:50:49.718 [00]                 ODBC: mts.mtsinfo.enlistInDTC transaction=0x0
t=0d54 07:50:49.718 [00]                 ODBC: SEM: xamapper.waitEventSem
t=0d54 07:50:49.718 [00]                 ODBC: SEM: Waiting for event
t=0d54 07:50:49.718 [00]                 ODBC: odbcmts.mtsinfo.Cleanup Entry
t=0d54 07:50:49.718 [00]                 ODBC: odbcmts.xamapper.Cleanup Entry

Request ID:                    XA_End
-------------------------------------------------------
Parameter:                     Resource Manager ID
   Value:                      482565504

Parameter:                     Flags
   Bitmap:                     Bitmap: 0x20000000 (TMFAIL)
                                  Bit  3: Dissociate caller and mark transaction branch read-only

Resolution

It was found by working with Microsoft that we were not adhering to Microsoft's specifications on how to handle connections that are disassociated from transactions. The ODBC driver was patched to provide support for connections to be disassociated from transactions, recovered and then re-associated again back to the transaction.

The issue is addressed in APAR SE45105. ODBC patches were provided for V6R1 and V7R1 of System i Access for Windows. The patch will not be ported back to V5R4. If the client is on V5R4, they will need to obtain the 5722-XW1 refresh of V6R1 IBM i Access Family. Additional details can be found at the following URL:
http://www-03.ibm.com/systems/i/software/access/caorder.html

The fix for V6R1 is in service pack SI42423 and the V7R1 fix is in service pack SI49800.