MQSeries.net :: View topic - JDBC TYPE 4 SQL Server 2008 Connection Problem in Broker v8

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » JDBC TYPE 4 SQL Server 2008 Connection Problem in Broker v8

JDBC TYPE 4 SQL Server 2008 Connection Problem in Broker v8

« View previous topic :: View next topic »

Author

Message

new_to_wmb8

Posted: Wed Jul 10, 2013 4:19 am Post subject: JDBC TYPE 4 SQL Server 2008 Connection Problem in Broker v8

Centurion

Joined: 28 May 2013
Posts: 127
Location: Hyderabad, India

Hi All,

i have seen all the previous post related to this issue, i have followed all the steps creating the JDBC provider connection.

below is the JDBC provider property,

$ mqsireportproperties MBDEV01 -c JDBCProviders -o Microsoft_SQL_Server -r

Code:

JDBCProviders
Microsoft_SQL_Server
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='PPDB'
databaseType='Microsoft SQL Server'
databaseVersion='default_Database_Version'
description='default_Description'
environmentParms='default_none'
jarsURL='XXXX'
jdbcProviderXASupport='false'
maxConnectionPoolSize='0'
portNumber=''
securityIdentity='mySecurityIdentity'
serverName='XXXXXX'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriverâ€™

Exception list Error below:

Code:

ExceptionList
RecoverableException
File:CHARACTER:/build/slot1/S800_P/src/DataFlowEngine/ImbDataFlowNode.cpp
Line:INTEGER:1154
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:SAP_2_PPDB_VENDOR_MF#FCMComposite_1_1
Label:CHARACTER:SAP_2_PPDB_VENDOR_MF.MQ Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:/build/slot1/S800_P/src/DataFlowEngine/PluginInterface/ImbJniNode.cpp
Line:INTEGER:1267
Function:CHARACTER:ImbJniNode::evaluate
Type:CHARACTER:ComIbmMSLMappingNode
Name:CHARACTER:SAP_2_PPDB_VENDOR_MF#FCMComposite_1_3
Label:CHARACTER:SAP_2_PPDB_VENDOR_MF.Mapping
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:MbErrorHandler.java
Line:INTEGER:154
Function:CHARACTER:evaluate
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:3947
Text:CHARACTER:Caught BrokerXCIDynamicException
Insert
Type:INTEGER:5
Text:CHARACTER:{default}:SAP_2_PPDB_VENDOR_Mapping
Insert
Type:INTEGER:5
Text:CHARACTER:Static namespace declarations: let $dbinsert1 := jdbc:insert('PPDB', 'insert into dbo.Vendor Table ', $dbinsert1_var_params, xs:int('0'),false(),'', xs:int('2') )
RecoverableException
File:CHARACTER:MbErrorHandler.java
Line:INTEGER:281
Function:CHARACTER:throwableToMbException
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:3949
Text:CHARACTER:Caught BrokerXCIDynamicException
Insert
Type:INTEGER:5
Text:CHARACTER:com.ibm.broker.xci.BrokerXCIException: <com.ibm.broker.plugin.MbRecoverableException class:com.ibm.broker.jdbctype4.jdbcdbasemgr.JDBCType4Connection@57bd57bd method:JDBCType4Connection::createXAConnection source:BIPmsgs key:6231 >
<com.ibm.broker.plugin.MbRecoverableException class:com.ibm.broker.jdbctype4.jdbcdbasemgr.JDBCType4Connection@57bd57bd method:JDBCType4Connection::createXAConnection source:BIPmsgs key:6231 >

RecoverableException
File:CHARACTER:JDBCCommon.java
Line:INTEGER:527
Function:CHARACTER:JDBCType4Connection::createXAConnection
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:6231
Text:CHARACTER:Problem encountered obtaining JDBC connection
Insert
Type:INTEGER:5
Text:CHARACTER:Broker 'MBDEV01'; Execution Group 'SAP_GENERIC'; Message Flow 'SAP_2_PPDB_VENDOR_MF'; Node 'Mapping'; Node Type 'Mapping
Insert
Type:INTEGER:5
Text:CHARACTER: message: com.microsoft.sqlserver.jdbc.SQLServerXADataSource stack trace: [java.net.URLClassLoader.findClass(URLClassLoader.java:434)
com.ibm.broker.classloading.JavaResourceClassLoader.findClass(JavaResourceClassLoader.java:155)
com.ibm.broker.classloading.SharedClassLoader.findClass(SharedClassLoader.java:195)
java.lang.ClassLoader.loadClass(ClassLoader.java:660)
java.lang.ClassLoader.loadClass(ClassLoader.java:626)
com.ibm.broker.jdbctype4.jdbcdbasemgr.JDBCType4Connection.createXAConnection(JDBCType4Connection.java:189)
com.ibm.broker.jdbctype4.jdbcdbasemgr.JDBCType4DatabaseManager.getThreadOwnedConnections(JDBCType4DatabaseManager.java:333)
com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandler.getConnection(JDBCType4SinglePhaseTrxnHandler.java:268)
com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandler.getConnection(JDBCType4SinglePhaseTrxnHandler.java:154)
com.ibm.broker.jdbctype4.connfact.JDBCType4ConnectionFactory.getConnection(JDBCType4ConnectionFactory.java:163)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
java.lang.reflect.Method.invoke(Method.java:611)
com.ibm.broker.plugin.MbNode.getJDBCType4Connection(MbNode.java:1564)
com.ibm.broker.ct.mapping.MappingNode.getDBConnection(MappingNode.java:1208)
com.ibm.broker.ct.mapping.JDBCWrapper.prepare(JDBCWrapper.java:1972)
com.ibm.broker.ct.mapping.JDBCWrapper.insert(JDBCWrapper.java:562)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
java.lang.reflect.Method.invoke(Method.java:611)
com.ibm.xml.xci.exec.BasicMutableDynamicContext$JavaMethodExecutable.execute(BasicMutableDynamicContext.java:936)
com.ibm.xml.xci.exec.BasicDynamicContext$ExternalFunction.execute(BasicDynamicContext.java:361)
xmap2108587438_Common_Partition0.xslt2$24$contact admin(com_ibm_xmlns_prod_xltxe_j_linetable)
xmap2108587438_Common_Partition0.xmap2108587438$24$$24$function$24$__xquery_result_fun__$2d$3(com_ibm_xmlns_prod_xltxe_j_linetable:16)
xmap2108587438.evaluate(xmap2108587438.java)
com.ibm.xltxe.rnm1.xtq.xquery.drivers.XQueryExecutable.execute(XQueryExecutable.java:220)
com.ibm.xltxe.rnm1.xtq.xquery.drivers.XQueryExecutable.execute(XQueryExecutable.java:97)
com.ibm.broker.ct.mapping.MappingNode.evaluate(MappingNode.java:393)
com.ibm.broker.plugin.MbNode.evaluate(MbNode.java:1480)

can any one please help in understanding what is the error causing this issue.

new_to_wmb8

Posted: Wed Jul 10, 2013 4:26 am Post subject:

Centurion

Joined: 28 May 2013
Posts: 127
Location: Hyderabad, India

Message flow,

MQInput Node--->Mapping Node(Insert Into DB)---->MQOutput Node.

the ODBC connection is also configured and its connected.

lancelotlinc

Posted: Wed Jul 10, 2013 4:49 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

Quote:

On distributed platforms, only DB2 and Oracle are supported.
On z/OS, XA connections are not supported.

http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fah10030_.htm
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

new_to_wmb8

Posted: Wed Jul 10, 2013 5:11 am Post subject:

Centurion

Joined: 28 May 2013
Posts: 127
Location: Hyderabad, India

both ODBC and JDBC connection are not XA. In JDBC provider

Code:

jdbcProviderXASupport='false'

lancelotlinc

Posted: Wed Jul 10, 2013 5:14 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

new_to_wmb8 wrote:

both ODBC and JDBC connection are not XA. In JDBC provider

Code:

jdbcProviderXASupport='false'

Apparently your driver is unaware of your command to process non-XA transaction:

Quote:

com.microsoft.sqlserver.jdbc.SQLServerXADataSource

Quote:

createXAConnection

Did you set Transaction mode on your input node to 'NO'?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

new_to_wmb8

Posted: Wed Jul 10, 2013 5:29 am Post subject:

Centurion

Joined: 28 May 2013
Posts: 127
Location: Hyderabad, India

In IBM Infocenter it is wrriten as

Quote:

◦type4DatasourceClassName. The name of the JDBC data source class name that is used to establish a type 4 connection to a remote database, and to coordinate transaction support. For example, specify com.ibm.db2.jcc.DB2XADataSource for DB2, or specify oracle.jdbc.xa.client.OracleXADataSource for Oracle. You must specify the XA class name when using the getJDBCType4Connection() API call for coordinated transactions. If the database server does not support XA transactions, or you do not want to use the XA protocol, this property is optional and you must set the jdbcProviderXASupport property to false.

so if i am using as false, the

Quote:

type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'

does not work.
link for IBM Infocenter :
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fah61310_.htm

lancelotlinc

Posted: Wed Jul 10, 2013 5:31 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

All I can tell you , based on the error you posted, your JDBC operation invoking the database connection is attempting to create an XA transaction, which is not supported for Microsoft SQLServer.

DID YOU SET THE TRANSACTION MODE TO 'NO' ON YOUR INPUT NODE?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

new_to_wmb8

Posted: Wed Jul 10, 2013 5:36 am Post subject:

Centurion

Joined: 28 May 2013
Posts: 127
Location: Hyderabad, India

yes i have set it as "NO" in the MQInput node, but the error still same.

lancelotlinc

Posted: Wed Jul 10, 2013 5:37 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

new_to_wmb8 wrote:

yes i have set it as "NO" in the MQInput node, but the error still same.

Examine each and every node down the line, to be sure that Transaction mode is set to 'inherit' or 'no'.

Then stop, wait 60 seconds & start your EG. Try your transaction again.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

new_to_wmb8

Posted: Wed Jul 10, 2013 5:40 am Post subject:

Centurion

Joined: 28 May 2013
Posts: 127
Location: Hyderabad, India

Ok lancelotlinc, i will try that and let you know the result.
what is this error that is in Exception list :

Quote:

Text:CHARACTER:Caught BrokerXCIDynamicException

lancelotlinc

Posted: Wed Jul 10, 2013 5:44 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

Thats just the next error up the execution chain. The root cause of the exception is when your driver tries to create an XA transaction.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » JDBC TYPE 4 SQL Server 2008 Connection Problem in Broker v8

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP