Topic
  • 24 replies
  • Latest Post - ‏2012-05-30T14:27:55Z by dblwizard
pai_sun
pai_sun
1 Post

Pinned topic XA Transaction using DB2 .Net Provider

‏2010-02-26T16:12:48Z |
I am trying to open a DB2 connection with a Transaction Scope and getting the following error.

IBM.Data.DB2.DB2Exception: ERROR 58005 IBMDB2 SQL0998N Error occurred during transaction or heuristic processing.
Reason Code = "16". Subcode = "2-80004005". SQLSTATE=58005

I didnt find a solution in any where in the Forums

Following are the testconn20 output.

Step 1: Printing version info
.NET Framework version: 2.0.50727.3082
DB2 .NET provider version: 9.0.0.2
DB2 .NET file version: 9.7.0.2
Capability bits: ALLDEFINED
Build: 20090522
Factory for invariant name IBM.Data.DB2 verified
Factory for invariant name IBM.Data.Informix verified
IDS.NET from DbFactory is Common IDS.NET
Warning, failed load of DbFactory for:Microsoft.SqlServerCe.Client
VSAI assembly version: 9.1.0.0
VSAI file version: 9.7.0.489
Elapsed: 0.498752

Step 2: Connecting using "Database=db2c;Server=db2c:446;Userid=XXXX;password=XXXX"
Server type and version: OS/390 09.01.0005
Elapsed: 0.249376

Step 3: Selecting rows from SYSIBM.systables to validate existance of packages
SELECT * FROM SYSIBM.systables FETCH FIRST 5 rows only
Elapsed: 0.124688

Step 4: Calling GetSchema for tables to validate existance of schema functions
Elapsed: 12.375284

Step 5: Creating XA connection
DB2TransactionScope: Failed to open connection to database!
IBM.Data.DB2.DB2Exception: ERROR 58005 IBMDB2 SQL0998N Error occurred during transaction or heuristic processing.
son Code = "16". Subcode = "2-80004005". SQLSTATE=58005

at IBM.Data.DB2.DB2ConnPool.Open(DB2Connection connection, String& szConnectionString, DB2ConnSettings& ppSettings, O
& ppConn)
at IBM.Data.DB2.DB2Connection.Open()
at TestConn.DB2TransactionScope.Open(String connstr)

Looking for XADLL key for C:\SQLLIB\bin\db2app.dll
Correct entry found
Check if XA Transactions are enabled:
Component services, My computer, properties
MSDTC tab, Security configuration, enable XA Transactions

I have XA Enabled on the MSDTC
and registry entries look fine
I tried even editing Registry entry with a extra backslash before bin that didnt work either.

Any help is greatly appreciated

Thanks
Updated on 2012-05-30T14:27:55Z at 2012-05-30T14:27:55Z by dblwizard
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2010-02-26T16:40:27Z  
    Can you please verify that MSDTC is running under "NT AUTHORITY\NetworkService" account and also that "NETWORK SERVICE" account has read access to the SQLLIB\bin directory.
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2010-02-26T18:33:25Z  
    Can you please verify that MSDTC is running under "NT AUTHORITY\NetworkService" account and also that "NETWORK SERVICE" account has read access to the SQLLIB\bin directory.
    I've also noticed that you're running testconn20 directly against the z/OS V9 server ( i.e. without catalogging a local alias ). In this case, you can try one more thing. Add a <database> entry to the db2dsdriver.cfg for the database you're running against, and set the enableDirectXA parameter to true ( see below ). This may resolve the issue, provided your z/OS server as the PTF installed that enabled direct XA support.

    <database name="db2c" host="db2c" port=="446">
    <parameter name="enableDirectXA" value="true"/>
    </database>
  • kdawg
    kdawg
    9 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T19:49:08Z  
    I'm chasing down a very similar XA Transaction. Everything is set up and it should be working.

    In my debugging effort, I ran testconn20.exe. However, Step 5 didn't try an XA Transaction, instead it is a call to GetSchema "for tables to validate existance of schema functions"

    How can I get testconn20.exe to test XA Transaction capability?
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T19:53:23Z  
    • kdawg
    • ‏2011-02-01T19:49:08Z
    I'm chasing down a very similar XA Transaction. Everything is set up and it should be working.

    In my debugging effort, I ran testconn20.exe. However, Step 5 didn't try an XA Transaction, instead it is a call to GetSchema "for tables to validate existance of schema functions"

    How can I get testconn20.exe to test XA Transaction capability?
    Hi,

    please use -dtc option to test XA Transactions using testconn20.exe.

    
    testconn20.exe -dtc 
    "<connection string>"
    
  • kdawg
    kdawg
    9 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T20:11:58Z  
    Hi,

    please use -dtc option to test XA Transactions using testconn20.exe.

    <pre class="jive-pre"> testconn20.exe -dtc "<connection string>" </pre>
    Hi Alex, thanks for the quick reply.

    I can successfully create a XA Transaction when running testconn20.exe. However, I still receive this error in Event Viewer when a XA Transaction is attempted in a .NET service on the server:

    Description:
    The XA Transaction Manager attempted to load the XA resource manager DLL. The call to LOADLIBRARY for the XA resource manager DLL failed: DLL=C:\Program Files\IBM\SQLLIB\BIN\DB2APP.DLL, HR=0x800700c1, File=d:\w7rtm\com\complus\dtc\dtc\xatm\src\xarmconn.cpp Line=2446.


    db2diag.log simply show this entry repeated for every attempt:

    2011-02-01-14.07.43.186000-360 I110243H400 LEVEL: Severe
    PID : 3548 TID : 3900 PROC : w3wp.exe
    INSTANCE: DB2 NODE : 000
    APPID : 10.187.55.74.60833.110201200743
    EDUID : 3900
    FUNCTION: DB2 UDB, XA DTP Support, MicrosoftXARMCreate, probe:363
    DATA #1 : String, 69 bytes
    HRESULT: 80004005
    HRESULT Message: Unspecified error

    ITransaction:


    XA Transactions are enabled on the server.
    I'm working with a 64-bit Windows Server 2008 machine.
    64-bit DB2 Data Server Client 9.7fp3a installed.
    MSDTC is set to run as NT AUTHORITY\NetworkService.
    I've granted read access to SQLLIB\BIN for NT AUTHORITY\NETWORK SERVICE.
    I have two entries in XADLL registry, both full paths, one for DB2APP.DLL and one for DB2APP64.DLL.

    For XA Transaction support on a 64-bit server, should the MSDTC registry entry be pointing to DB2APP.DLL or DB2APP64.DLL? It appears to be looking for DB2APP.DLL because it complains if I remove that entry from the registry.

    We experienced this similar error when working with our local Windows XP boxes. I'm unsure how it actually started working. (It appeared to be "magic" as it started working after countless reenabling of XA Transactions and a complete reinstall of 9.7fp3a.)

    Any ideas? Is there anything special that needs to be configured for DB2 XA Transaction support on a 64-bit server?
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T20:19:13Z  
    • kdawg
    • ‏2011-02-01T20:11:58Z
    Hi Alex, thanks for the quick reply.

    I can successfully create a XA Transaction when running testconn20.exe. However, I still receive this error in Event Viewer when a XA Transaction is attempted in a .NET service on the server:

    Description:
    The XA Transaction Manager attempted to load the XA resource manager DLL. The call to LOADLIBRARY for the XA resource manager DLL failed: DLL=C:\Program Files\IBM\SQLLIB\BIN\DB2APP.DLL, HR=0x800700c1, File=d:\w7rtm\com\complus\dtc\dtc\xatm\src\xarmconn.cpp Line=2446.


    db2diag.log simply show this entry repeated for every attempt:

    2011-02-01-14.07.43.186000-360 I110243H400 LEVEL: Severe
    PID : 3548 TID : 3900 PROC : w3wp.exe
    INSTANCE: DB2 NODE : 000
    APPID : 10.187.55.74.60833.110201200743
    EDUID : 3900
    FUNCTION: DB2 UDB, XA DTP Support, MicrosoftXARMCreate, probe:363
    DATA #1 : String, 69 bytes
    HRESULT: 80004005
    HRESULT Message: Unspecified error

    ITransaction:


    XA Transactions are enabled on the server.
    I'm working with a 64-bit Windows Server 2008 machine.
    64-bit DB2 Data Server Client 9.7fp3a installed.
    MSDTC is set to run as NT AUTHORITY\NetworkService.
    I've granted read access to SQLLIB\BIN for NT AUTHORITY\NETWORK SERVICE.
    I have two entries in XADLL registry, both full paths, one for DB2APP.DLL and one for DB2APP64.DLL.

    For XA Transaction support on a 64-bit server, should the MSDTC registry entry be pointing to DB2APP.DLL or DB2APP64.DLL? It appears to be looking for DB2APP.DLL because it complains if I remove that entry from the registry.

    We experienced this similar error when working with our local Windows XP boxes. I'm unsure how it actually started working. (It appeared to be "magic" as it started working after countless reenabling of XA Transactions and a complete reinstall of 9.7fp3a.)

    Any ideas? Is there anything special that needs to be configured for DB2 XA Transaction support on a 64-bit server?
    Hi,

    on 64 bit platforms testconn20.exe is a 64bit process and would use DB2APP64.DLL for XA transations. However, judging by the db2diag.log entry you are running a web application ( w3wp.exe process ), which is a 32 bit application and would use DB2APP.DLL for XA transactions. To test your 32 bit XA setup, please use testconn20_32.exe:

    
    testconn20_32.exe -dtc 
    "<connection string>"
    
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T21:15:45Z  
    Hi,

    on 64 bit platforms testconn20.exe is a 64bit process and would use DB2APP64.DLL for XA transations. However, judging by the db2diag.log entry you are running a web application ( w3wp.exe process ), which is a 32 bit application and would use DB2APP.DLL for XA transactions. To test your 32 bit XA setup, please use testconn20_32.exe:

    <pre class="jive-pre"> testconn20_32.exe -dtc "<connection string>" </pre>
    Hi,

    actually, now that I think about it, your scenario is actually not supported. Since msdtc is a 64bit process, it can't load 32 bit DLLs which is what's being requested by your 32 bit web application.
    What that means in your particular case is that you can't use XA transactions in your web application on a 64 bit windows platform.
  • kdawg
    kdawg
    9 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T21:29:46Z  
    Hi,

    on 64 bit platforms testconn20.exe is a 64bit process and would use DB2APP64.DLL for XA transations. However, judging by the db2diag.log entry you are running a web application ( w3wp.exe process ), which is a 32 bit application and would use DB2APP.DLL for XA transactions. To test your 32 bit XA setup, please use testconn20_32.exe:

    <pre class="jive-pre"> testconn20_32.exe -dtc "<connection string>" </pre>
    Thanks again for the quick responses -- these are really helping out and are much appreciated.

    Sure enough, it croaks when trying to create an XA connection in the 32-bit testconn executable. However, the error says that it's missing the registry entry, even though it's clearly there in the registry.

    Step 6: Creating XA connection
    DB2TransactionScope: Failed to open connection to database!
    IBM.Data.DB2.DB2Exception: ERROR 58005 IBMIDS/UNIX64 SQL0998N Error occurred during transaction or heuristic
    processing. Reason Code = "16". Subcode = "2-80004005".
    at IBM.Data.DB2.DB2ConnPool.Open(DB2Connection connection, String& szConnectionString, DB2ConnSettings& ppSettin
    gs, Object& ppConn)
    at IBM.Data.DB2.DB2Connection.Open()
    at TestConn.DB2TransactionScope.Open(String connstr)

    Looking for XADLL key for C:\Program Files\IBM\SQLLIB\bin\db2app.dll
    Missing registry entry for C:\Program Files\IBM\SQLLIB\bin\db2app.dll in SOFTWARE\Microsoft\MSDTC\XADLL
    Test failed.


    The results of db2diag.log remain the same, except now the PROC is testconn20_32.exe instead of w3wp.exe.

    The registry values at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL appear to correct:

    name: C:\Program Files\IBM\SQLLIB\bin\db2app.dll
    value: C:\Program Files\IBM\SQLLIB\bin\db2app.dll

    name: C:\Program Files\IBM\SQLLIB\BIN\DB2APP64.DLL
    value: C:\Program Files\IBM\SQLLIB\BIN\DB2APP64.DLL

    I changed the first one to lowercase for 'bin' and 'db2app.dll' after seeing the error message from testconn20_32.exe. However, still no luck.

    The registry key is there yet testconn20_32.exe says it isn't. I know this isn't a MSDTC forum, but it's my understanding that I don't need Network DTC Access in my situation, but I could be wrong.
  • kdawg
    kdawg
    9 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T21:30:48Z  
    Hi,

    actually, now that I think about it, your scenario is actually not supported. Since msdtc is a 64bit process, it can't load 32 bit DLLs which is what's being requested by your 32 bit web application.
    What that means in your particular case is that you can't use XA transactions in your web application on a 64 bit windows platform.
    Thanks for the follow-up info Alex. While not great news, it at least answers this issues I'm facing.
  • kdawg
    kdawg
    9 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T21:31:43Z  
    • kdawg
    • ‏2011-02-01T21:30:48Z
    Thanks for the follow-up info Alex. While not great news, it at least answers this issues I'm facing.
    That would suggest that if I ran my web application as a 64-bit process, things should work?
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T21:38:20Z  
    • kdawg
    • ‏2011-02-01T21:31:43Z
    That would suggest that if I ran my web application as a 64-bit process, things should work?
    Hi,

    yes, if your entire application stack is 64 bit, then XA transactions would work.
  • kdawg
    kdawg
    9 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2011-02-01T22:27:58Z  
    Hi,

    yes, if your entire application stack is 64 bit, then XA transactions would work.
    It works after I change the "Enable 32-bit Applications" parameter to False for the my service's ApplicationPool in IIS.

    Thanks so much for all your insight, Alex! Invaluable!!

    Kenneth
  • dblwizard
    dblwizard
    46 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-04-24T15:18:47Z  
    Howdy,

    I'm having the same problem except I'm running on XP SP2. Here is the output when I run testconn40.exe with the -dtc parameter:

    adding MSDTC step

    Step 1: Printing version info
    .NET Framework version: 4.0.30319.239
    DB2 .NET provider version: 9.7.4.4
    DB2 .NET file version: 9.7.5.4
    Capability bits: ALLDEFINED
    Build: 20111018
    Factory for invariant name IBM.Data.DB2 verified
    Factory for invariant name IBM.Data.Informix verified
    IBM.Data.Informix from DbFactory is the Common Informix .NET provider
    IBM Database Add-ins assembly version: 9.1.0.0
    IBM Database Add-ins file version: 9.7.5.142
    Elapsed: 2.4844704

    Step 2: Validating db2dsdriver.cfg against db2dsdriver.xsd schema file
    D:\documents and settings\All Users\Application Data\IBM\DB2\DB2COPY1\cf
    g\db2dsdriver.cfg against C:\Program Files\IBM\SQLLIB\cfg\db2dsdriver.xsd
    Elapsed: 0.0312512

    Step 3: Connecting using "Server=server:port;Database=xxyy - removed for security reasons"
    Server type and version: OS/390 09.01.0005
    Elapsed: 0.625024

    Step 4: Selecting rows from SYSIBM.systables to validate existence of packages
    SELECT * FROM SYSIBM.systables FETCH FIRST 5 rows only
    Elapsed: 0.1406304

    Step 5: Calling GetSchema for tables to validate existence of schema functions
    IBM.Data.DB2.DB2Exception (0x80004005): ERROR 38113 IBMDB2 SQL0443N Routi
    ne "SQLTABLES" (specific name "SQLTABLES") has returned an error SQLSTATE with d
    iagnostic text "-300 4".
    at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, Stri
    ng method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg, Boolean skip
    Deleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)
    at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, Stri
    ng method)
    at IBM.Data.DB2.DB2MetaDataFactory.b(DB2Command& A_0, String A_1)
    at IBM.Data.DB2.DB2MetaDataFactory.l(String[] A_0)
    at IBM.Data.DB2.DB2MetaDataFactory.GetSchema(String collectionName, String[]
    restrictionValues)
    at IBM.Data.DB2.DB2Connection.GetSchema(String collectionName, String[] restr
    ictionValues)
    at IBM.Data.DB2.DB2Connection.GetSchema(String collectionName)
    at TestConn.myClassName.GetSchemaTable()
    Test failed.

    I removed the connection string information for security reasons. As you can see I still don't get an XA Transaction in step 5 but it errors. I have validated that MSDTC is using the Network Service and that XA Transaction are "enabled". I did not previously have db2dsdriver.cfg file so I created one and added a dsn entry and a database entry for the database I am trying to hit.

    Any suggestions?

    dbl
  • dblwizard
    dblwizard
    46 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-04-24T15:26:22Z  
    One other thing. Here is the last entry in the db2diag.log

    2012-04-24-08.51.32.616000-360 I32784H418 LEVEL: Severe
    PID : 3628 TID : 1248 PROC : ADRP.NH.Console.vshost.exe
    INSTANCE: DB2 NODE : 000
    APPID : 10.168.65.239.1643.120424145130
    EDUID : 1248
    FUNCTION: DB2 UDB, XA DTP Support, MicrosoftXARMCreate, probe:364
    DATA #1 : String, 69 bytes
    HRESULT: 80004005
    HRESULT Message: Unspecified error

    ITransaction:

    Several entries prior to this look very similar. But I did find a some interesting entries, not sure if the mean that much but thought I'd include them, like the two below:
    2012-04-19-11.39.24.476000-360 I24845H695 LEVEL: Warning
    PID : 476 TID : 2724 PROC : DTSWizard.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 2724
    FUNCTION: DB2 UDB, Connection Manager, sqleUCgetDirInfo, probe:1012
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1013 sqlerrml: 1
    sqlerrmc:
    sqlerrp : SQLEUCCM
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    2012-04-23-12.45.33.727000-360 I25542H467 LEVEL: Severe
    PID : 5132 TID : 7588 PROC : WebDev.WebServer40.exe
    INSTANCE: DB2 NODE : 000
    APPID : 10.168.65.239.2305.120423184533
    EDUID : 7588
    FUNCTION: DB2 UDB, XA DTP Support, MicrosoftXARMCreate, probe:364
    DATA #1 : String, 121 bytes
    HRESULT: 8004D026
    HRESULT Message: The transaction manager has disabled its support for XA transactions.

    ITransaction:
    2012-04-23-14.43.07.576000-360 I26011H471 LEVEL: Severe
    PID : 3504 TID : 4496 PROC : ADRP.NH.Console.vshost.exe
    INSTANCE: DB2 NODE : 000
    APPID : 10.168.65.239.3005.120423204307
    EDUID : 4496
    FUNCTION: DB2 UDB, XA DTP Support, MicrosoftXARMCreate, probe:364
    DATA #1 : String, 121 bytes
    HRESULT: 8004D026
    HRESULT Message: The transaction manager has disabled its support for XA transactions.

    ITransaction:

    Thanks

    dbl
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-04-25T21:34:19Z  
    • dblwizard
    • ‏2012-04-24T15:18:47Z
    Howdy,

    I'm having the same problem except I'm running on XP SP2. Here is the output when I run testconn40.exe with the -dtc parameter:

    adding MSDTC step

    Step 1: Printing version info
    .NET Framework version: 4.0.30319.239
    DB2 .NET provider version: 9.7.4.4
    DB2 .NET file version: 9.7.5.4
    Capability bits: ALLDEFINED
    Build: 20111018
    Factory for invariant name IBM.Data.DB2 verified
    Factory for invariant name IBM.Data.Informix verified
    IBM.Data.Informix from DbFactory is the Common Informix .NET provider
    IBM Database Add-ins assembly version: 9.1.0.0
    IBM Database Add-ins file version: 9.7.5.142
    Elapsed: 2.4844704

    Step 2: Validating db2dsdriver.cfg against db2dsdriver.xsd schema file
    D:\documents and settings\All Users\Application Data\IBM\DB2\DB2COPY1\cf
    g\db2dsdriver.cfg against C:\Program Files\IBM\SQLLIB\cfg\db2dsdriver.xsd
    Elapsed: 0.0312512

    Step 3: Connecting using "Server=server:port;Database=xxyy - removed for security reasons"
    Server type and version: OS/390 09.01.0005
    Elapsed: 0.625024

    Step 4: Selecting rows from SYSIBM.systables to validate existence of packages
    SELECT * FROM SYSIBM.systables FETCH FIRST 5 rows only
    Elapsed: 0.1406304

    Step 5: Calling GetSchema for tables to validate existence of schema functions
    IBM.Data.DB2.DB2Exception (0x80004005): ERROR 38113 IBMDB2 SQL0443N Routi
    ne "SQLTABLES" (specific name "SQLTABLES") has returned an error SQLSTATE with d
    iagnostic text "-300 4".
    at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, Stri
    ng method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg, Boolean skip
    Deleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)
    at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, Stri
    ng method)
    at IBM.Data.DB2.DB2MetaDataFactory.b(DB2Command& A_0, String A_1)
    at IBM.Data.DB2.DB2MetaDataFactory.l(String[] A_0)
    at IBM.Data.DB2.DB2MetaDataFactory.GetSchema(String collectionName, String[]
    restrictionValues)
    at IBM.Data.DB2.DB2Connection.GetSchema(String collectionName, String[] restr
    ictionValues)
    at IBM.Data.DB2.DB2Connection.GetSchema(String collectionName)
    at TestConn.myClassName.GetSchemaTable()
    Test failed.

    I removed the connection string information for security reasons. As you can see I still don't get an XA Transaction in step 5 but it errors. I have validated that MSDTC is using the Network Service and that XA Transaction are "enabled". I did not previously have db2dsdriver.cfg file so I created one and added a dsn entry and a database entry for the database I am trying to hit.

    Any suggestions?

    dbl
    Hi,

    according to what I see in your testconn40 output, you haven't actually reached the MSDTC step ( which is step 6 ) because of the error in GetSchema() API call. According to the error, there seems to be an issue with the SQLTABLES SP on your server. Please contact your DBA and get them to take a look at the server logs ( if I remember correctly from your previous posts you're running against iSeries ). The server job logs should contain more information on the reason SQLTABLES SP is failing.
    Once that issue is resolved testconn40 will be able to move on to MSDTC step.

    I am still investigating the db2diag.log entries you've posted. When I have more info, I'll post additional details.

    Regards,
    Alex
  • dblwizard
    dblwizard
    46 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-04-25T21:39:34Z  
    Hi,

    according to what I see in your testconn40 output, you haven't actually reached the MSDTC step ( which is step 6 ) because of the error in GetSchema() API call. According to the error, there seems to be an issue with the SQLTABLES SP on your server. Please contact your DBA and get them to take a look at the server logs ( if I remember correctly from your previous posts you're running against iSeries ). The server job logs should contain more information on the reason SQLTABLES SP is failing.
    Once that issue is resolved testconn40 will be able to move on to MSDTC step.

    I am still investigating the db2diag.log entries you've posted. When I have more info, I'll post additional details.

    Regards,
    Alex
    Actually we are running against zOS.
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-04-25T22:20:47Z  
    • dblwizard
    • ‏2012-04-25T21:39:34Z
    Actually we are running against zOS.
    Sorry, my mistake. In any case, the same comment applies. :) You need to check with DBA to see why SQLTABLES SP is failing.

    With regards to the db2diag.log entries you've posted. Can you please verify that XA transactions are enabled in your MSDTC setup. The steps to enable XA transactions can be found here: http://support.microsoft.com/default.aspx?scid=kb;en-us;817066.

    Also, please check Event viewer to see if there are any warnings/errors there for MSDTC process.
  • dblwizard
    dblwizard
    46 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-04-26T13:59:20Z  
    Sorry, my mistake. In any case, the same comment applies. :) You need to check with DBA to see why SQLTABLES SP is failing.

    With regards to the db2diag.log entries you've posted. Can you please verify that XA transactions are enabled in your MSDTC setup. The steps to enable XA transactions can be found here: http://support.microsoft.com/default.aspx?scid=kb;en-us;817066.

    Also, please check Event viewer to see if there are any warnings/errors there for MSDTC process.
    Here is the response from my dba:

    .... failed in getStaticSQLTables SQLCODE=-300
    300, ERROR: THE STRING CONTAINED IN HOST VARIABLE OR PARAMETER 4 IS NOT NUL-TERMINATED
    = 22024 SQLSTATE RETURN CODE
    = DSNXRIHB SQL PROCEDURE DETECTING ERROR
    = -335 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    = X'FFFFFEB1' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' S

    IN CATALOGNAME VARCHAR(128)
    IN SCHEMANAME VARCHAR(128)
    IN TABLENAME VARCHAR(128) F
    IN TABLETYPE VARCHAR(4000)
    IN OPTIONS VARCHAR(4000) FO

    C strings are terminated with an X'00'.

    This sounds like there is a bug in the calling program.
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-04-26T22:54:09Z  
    • dblwizard
    • ‏2012-04-26T13:59:20Z
    Here is the response from my dba:

    .... failed in getStaticSQLTables SQLCODE=-300
    300, ERROR: THE STRING CONTAINED IN HOST VARIABLE OR PARAMETER 4 IS NOT NUL-TERMINATED
    = 22024 SQLSTATE RETURN CODE
    = DSNXRIHB SQL PROCEDURE DETECTING ERROR
    = -335 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    = X'FFFFFEB1' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' S

    IN CATALOGNAME VARCHAR(128)
    IN SCHEMANAME VARCHAR(128)
    IN TABLENAME VARCHAR(128) F
    IN TABLETYPE VARCHAR(4000)
    IN OPTIONS VARCHAR(4000) FO

    C strings are terminated with an X'00'.

    This sounds like there is a bug in the calling program.
    What's the version of the client and server you're running?
  • dblwizard
    dblwizard
    46 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-05-07T18:08:58Z  
    What's the version of the client and server you're running?
    Sorry, somehow I missed your reply and have been wondering why you hadn't gotten back to me ... the DB version is z/os v9. Is there a call I can do to verify the client version? I'm pretty sure I have installed 9.7 fp5 but I don't see that version number
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-05-07T20:08:28Z  
    • dblwizard
    • ‏2012-04-26T13:59:20Z
    Here is the response from my dba:

    .... failed in getStaticSQLTables SQLCODE=-300
    300, ERROR: THE STRING CONTAINED IN HOST VARIABLE OR PARAMETER 4 IS NOT NUL-TERMINATED
    = 22024 SQLSTATE RETURN CODE
    = DSNXRIHB SQL PROCEDURE DETECTING ERROR
    = -335 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    = X'FFFFFEB1' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' S

    IN CATALOGNAME VARCHAR(128)
    IN SCHEMANAME VARCHAR(128)
    IN TABLENAME VARCHAR(128) F
    IN TABLETYPE VARCHAR(4000)
    IN OPTIONS VARCHAR(4000) FO

    C strings are terminated with an X'00'.

    This sounds like there is a bug in the calling program.
    Hi,

    looking more at the error info your DBA reported it looks like the issue actually might be on the server side. The function reporting the error is getStaticSQLTables, which is not the name of the SP that the client calls. The SP we're calling is SYSIBM.SQLTables.
    I'm also quite confident that the call is being made correctly, as this code path has been tested extensively.
    At this point I would recommend contacting IBM Tech support through the usual channels, as I don't think this issue can be resolved on the forum.

    Regards,
    Alex
  • dblwizard
    dblwizard
    46 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-05-24T23:12:10Z  
    Sorry, my mistake. In any case, the same comment applies. :) You need to check with DBA to see why SQLTABLES SP is failing.

    With regards to the db2diag.log entries you've posted. Can you please verify that XA transactions are enabled in your MSDTC setup. The steps to enable XA transactions can be found here: http://support.microsoft.com/default.aspx?scid=kb;en-us;817066.

    Also, please check Event viewer to see if there are any warnings/errors there for MSDTC process.
    Ok, this got put on the back burner as my focus was directed else where but now I need to solve this. I am getting an error in the event log which doesn't make sense

    The XA Transaction Manager cannot load the requested XA Dll since a registry entry is required for this operation. Please create a string value in the registry under HKLM\Software\Microsoft\MSDTC\XADLL where the value name is the name of the dll in the format dllname.dll and the value is the full path of the Dll. If you are not sure about the name or path of your XA Dll then please check the documentation for your XA provider. If this machine is part of a cluster please note that this registry entry needs to be made for each node in the cluster. C:\PROGRAM FILES\IBM\SQLLIB\BIN\DB2APP.DLL

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    But that dll exists and is in that location. The registry entry looks like this: db2app.dll and the value is C:\Program Files\IBM\SQLLIB\BIN\db2app.dll. What am I missing?

    thanks

    dbl
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-05-28T14:11:59Z  
    • dblwizard
    • ‏2012-05-24T23:12:10Z
    Ok, this got put on the back burner as my focus was directed else where but now I need to solve this. I am getting an error in the event log which doesn't make sense

    The XA Transaction Manager cannot load the requested XA Dll since a registry entry is required for this operation. Please create a string value in the registry under HKLM\Software\Microsoft\MSDTC\XADLL where the value name is the name of the dll in the format dllname.dll and the value is the full path of the Dll. If you are not sure about the name or path of your XA Dll then please check the documentation for your XA provider. If this machine is part of a cluster please note that this registry entry needs to be made for each node in the cluster. C:\PROGRAM FILES\IBM\SQLLIB\BIN\DB2APP.DLL

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    But that dll exists and is in that location. The registry entry looks like this: db2app.dll and the value is C:\Program Files\IBM\SQLLIB\BIN\db2app.dll. What am I missing?

    thanks

    dbl
    Hi,

    the XADLL key name must contain the full path, not just the dll name. I.e. the registry entry should look like this:

    C:\Program Files\IBM\SQLLIB\BIN\db2app.dll with value C:\Program Files\IBM\SQLLIB\BIN\db2app.dll

    Regards,
    Alex
  • dblwizard
    dblwizard
    46 Posts

    Re: XA Transaction using DB2 .Net Provider

    ‏2012-05-30T14:27:55Z  
    Hi,

    the XADLL key name must contain the full path, not just the dll name. I.e. the registry entry should look like this:

    C:\Program Files\IBM\SQLLIB\BIN\db2app.dll with value C:\Program Files\IBM\SQLLIB\BIN\db2app.dll

    Regards,
    Alex
    Alex,

    That worked. I somehow missed the entry above that noted that, maybe because it conflicts with what the MS article states the values should be ... but doesn't really mater at this point. It works. I'm still getting the error when running Stage 5 of the TestConn40 app and am trying to get time with the DBA to research it, I'll let you know when I do.

    Thanks

    dbl