How to configure Xa-dataSource for Netezza.

[kulbhushanc]

Hi,

I am trying to configure Xa-datasource through Jboss admin console.

and providing below parameters:

Module Name:com.netezza

Driver Class:org.netezza.Driver

XA DataSource Class:org.netezza.datasource.NzDatasource

Major Version:4

Minor Version:6

DatabaseName : testDatabase

ServerName : testServer

UserName : testUser

Password : testPwd

I am getting below error :

javax.resource.ResourceException: IJ031084: Unable to create connection

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:509)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:416)

        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequ

ManagedConnectionPool.java:1319)

        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnecti

nPool.java:496)

        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.internalTestConnection(AbstractPool.java:1061)

        at org.jboss.jca.core.connectionmanager.pool.strategy.OnePool.testConnection(OnePool.java:93)

        at org.jboss.as.connector.subsystems.common.pool.PoolOperations$TestConnectionInPool.invokeCommandOn(PoolOperations.java:234)

        at org.jboss.as.connector.subsystems.common.pool.PoolOperations$1.execute(PoolOperations.java:90)

        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:890)

        at org.jboss.as.controller.AbstractOperationContext.processStages(AbstractOperationContext.java:659)

        at org.jboss.as.controller.AbstractOperationContext.executeOperation(AbstractOperationContext.java:370)

        at org.jboss.as.controller.OperationContextImpl.executeOperation(OperationContextImpl.java:1344)

        at org.jboss.as.controller.ModelControllerImpl.internalExecute(ModelControllerImpl.java:392)

        at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:217)

        at org.jboss.as.domain.http.server.DomainApiHandler.handleRequest(DomainApiHandler.java:212)

        at io.undertow.server.handlers.encoding.EncodingHandler.handleRequest(EncodingHandler.java:72)

        at org.jboss.as.domain.http.server.security.SubjectDoAsHandler$1.run(SubjectDoAsHandler.java:72)

        at org.jboss.as.domain.http.server.security.SubjectDoAsHandler$1.run(SubjectDoAsHandler.java:68)

        at java.security.AccessController.doPrivileged(Native Method)

        at javax.security.auth.Subject.doAs(Subject.java:422)

        at org.jboss.as.controller.AccessAuditContext.doAs(AccessAuditContext.java:92)

        at org.jboss.as.domain.http.server.security.SubjectDoAsHandler.handleRequest(SubjectDoAsHandler.java:68)

        at org.jboss.as.domain.http.server.security.SubjectDoAsHandler.handleRequest(SubjectDoAsHandler.java:63)

        at io.undertow.server.handlers.BlockingHandler.handleRequest(BlockingHandler.java:56)

        at org.jboss.as.domain.http.server.DomainApiCheckHandler.handleRequest(DomainApiCheckHandler.java:95)

        at io.undertow.security.handlers.AuthenticationCallHandler.handleRequest(AuthenticationCallHandler.java:52)

        at io.undertow.server.Connectors.executeRootHandler(Connectors.java:202)

        at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:793)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at java.lang.Thread.run(Thread.java:745)

Caused by: javax.resource.ResourceException: IJ031102: Failed to load XA datasource: org.netezza.datasource.NzDatasource

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXADataSource(XAManagedConnectionFactory.java:640)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:492)

        ... 30 more

Caused by: java.lang.ClassCastException: org.netezza.datasource.NzDatasource cannot be cast to javax.sql.XADataSource

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXADataSource(XAManagedConnectionFactory.java:627)

        ... 31 more

20:07:43,845 ERROR [org.jboss.as.controller.management-operation] (management task-2)  WFLYCTL0013: Operation ("test-connection-in-pool") failed - address: ([

    ("subsystem" => "datasources"),

    ("xa-data-source" => "NetezzaXa4")

]) - failure description: "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid"

[rareddy]

How is it any different from what you did here? Unable to create XA-DataSource for Oracle 10g database using Teiid 9.1.3?

[kulbhushanc]

I have gone through below steps for netezza Xa-Datasource:

1. Below steps used to configure netezza driver

1. I have placed 'module.xml' file and (nzjdbc.jar) netezza jar on beolw location

teiid-9.1.3-wildfly-server\teiid-9.1.3\modules\system\layers\base\com\netezza\main

module.xml file content:

<?xml version="1.0" encoding="UTF-8"?>

<module xmlns="urn:jboss:module:1.0" name="com.netezza">

  <resources>

    <resource-root path="nzjdbc.jar"/>

  </resources>

  <dependencies>

    <module name="javax.api"/>

  <module name="javax.transaction.api"/>

  </dependencies>

</module>

2. Made an driver tag for netezza on standalone-teiid.xml file:

<driver name="netezza" module="com.netezza">

             <driver-class>org.netezza.Driver</driver-class>

  </driver>

2. Below Steps Used to create Xa-Datasource for netezza through jboss admin console

Configuration ->Subsystems->DataSources->XA->Add

-Custom

-PostgreSQL XA Datasource

-MySQL XA Datasource

-Oracle XA Datasource

-Microsoft SQLServer XA Datasource

-IBM DB2 XA Datasource

-Sybase XA Datasource

where I had chosen Custom option to create xa-datasource for netezza(this is the step different from Unable to create XA-DataSource for Oracle 10g database using Teiid 9.1.3? )

Step 1/4: XA Datasoure Attributes

Name: NetezzaXa

JNDI Name: java:/NetezzaXa

Step 2/4: Datasource Class

Name:           netezza

Module Name: com.netezza

Driver Class: org.netezza.Driver

XA DataSource Class: org.netezza.datasource.NzDatasource

Major Version: 4

Minor Version: 6

Step 3/4: XA Properties

DatabaseName: test_db

ServerName :  hostName

Step 4/4: Connection Settings

Username : test_user

Password : test_pwd

On screen I am getting below error:

Failed to create JDBC connection.

An internal error occurred.

Details

Unexpected HTTP response: 500

Request

{

    "address" => [

        ("subsystem" => "datasources"),

        ("xa-data-source" => "NetezzaXa")

    ],

    "operation" => "test-connection-in-pool"

}

Response

Internal Server Error

{

    "outcome" => "failed",

    "failure-description" => "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid",

    "rolled-back" => true,

    "response-headers" => {"process-state" => "reload-required"}

}

And On Teiid Console I am getting below error message:

ERROR [org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl] (Periodic Recovery)  IJ000906: Error during crash recovery: java:/NetezzaXa (IJ031084: Unable

to create connection): javax.resource.ResourceException: IJ031084: Unable to create connection

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:509)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1$1.run(XAManagedConnectionFactory.java:395)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1$1.run(XAManagedConnectionFactory.java:392)

        at java.security.AccessController.doPrivileged(Native Method)

        at javax.security.auth.Subject.doAs(Subject.java:422)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:391)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:388)

        at java.security.AccessController.doPrivileged(Native Method)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:387)

        at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.open(XAResourceRecoveryImpl.java:355)

        at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.getXAResources(XAResourceRecoveryImpl.java:193)

        at com.arjuna.ats.internal.jbossatx.jta.XAResourceRecoveryHelperWrapper.getXAResources(XAResourceRecoveryHelperWrapper.java:51)

        at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.resourceInitiatedRecoveryForRecoveryHelpers(XARecoveryModule.java:519)

        at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:185)

        at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:747)

        at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:375)

Caused by: javax.resource.ResourceException: IJ031102: Failed to load XA datasource: org.netezza.datasource.NzDatasource

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXADataSource(XAManagedConnectionFactory.java:640)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:492)

        ... 15 more

Caused by: java.lang.ClassCastException: org.netezza.datasource.NzDatasource cannot be cast to javax.sql.XADataSource

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXADataSource(XAManagedConnectionFactory.java:627)

        ... 16 more

Please let me know if I am missing any step.

[van.halbert]

Regarding defining the driver, you need to define the XA driver class:

<xa-datasource-class>xaClassName</xa-datasource-class>

Example for mysql:

      <driver name="com.mysql" module="com.mysql">

        <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

      </driver>

[kulbhushanc]

Thanks for the reply Van, But I have already tried  by introducing <xa-datasource-class> as shown below:

<driver name="netezza" module="com.netezza">

             <driver-class>org.netezza.Driver</driver-class>

       <xa-datasource-class>org.netezza.datasource.NzDatasource</xa-datasource-class>

  </driver>

I have also tried with below mentioned datasources as  xa-datasource-class  one by one but still getting the same error.

org.netezza.datasource.NzDatasource

org.netezza.datasource.NzConnectionPool

org.netezza.datasource.NzConnectionPoolDatasource

[rareddy]

You may need to consult Netezza documentation on this, I looked on the web for answers but none were conclusive. Some of them suggested what you defined above, but all those did not work for you.

[shawkins]

Unfortunately it does not appear that Netezza offers an XADataSource - IBM Knowledge Center or IBM Knowledge Center