issue with jdbcItemReader DB2 driver

[kk31]

Hi,

         I am trying to develop standalone java batch application which reads data from DB2(v9.7) tables and writes it to csv file with out header with JDK 1.8.

In  jberet.properties , i have the following setup.

job-repository-type = jdbc

db-url = jdbc:db2://xxx:123/schemaName

db-user = xxxx

db-password = xxxx

     In  job.xml,  i have the following configuration.

<reader ref="jdbcItemReader">

  <properties>

  <property name="sql"   value=" /> 

         <property name="driver-name" value="com.ibm.db2.jcc.DB2Driver" />

         <property name="url" value="jdbc:db2://xxx:123/schemaName" />

         <property name="user" value="xxxx" />

         <property name="password" value="xxxx" />

         <property name="beanType" value="java.util.List" />

  </properties>

  </reader>

                  In my classpath, i set  db2jcc.jar which is JDBC 3 complaint jar, and i am getting the following error message

Caused by: java.sql.SQLException: No suitable driver found forjdbc:db2://xxx:123/schemaName

  at java.sql.DriverManager.getConnection(DriverManager.java:689)

  at java.sql.DriverManager.getConnection(DriverManager.java:208)

  at org.jberet.repository.JdbcRepository.getConnection(JdbcRepository.java:976)

  ... 17 more

           If i add db2jcc4.jar which is JDBC 4 complaint  jar, it is happy and able to read the messages from tables.

          I also observed that the order of these two jar files are important. Correct order is db2Jcc.jar followed by db2JCC4.jar. If I change the order in the classpath, it throws the following exception.

Exception in thread "main" javax.batch.operations.BatchRuntimeException: JBERET000626: Failed to run INSERT INTO JOB_INSTANCE(JOBNAME, APPLICATIONNAME) VALUES(?, ?)

  at org.jberet.repository.JdbcRepository.insertJobInstance(JdbcRepository.java:326)

  at org.jberet.repository.AbstractPersistentRepository.createJobInstance(AbstractPersistentRepository.java:105)

  at org.jberet.operations.JobOperatorImpl$2.invoke(JobOperatorImpl.java:127)

  at org.jberet.operations.JobOperatorImpl$2.invoke(JobOperatorImpl.java:124)

  at org.jberet.operations.JobOperatorImpl.invokeTransaction(JobOperatorImpl.java:402)

  at org.jberet.operations.JobOperatorImpl.start(JobOperatorImpl.java:124)

  at org.jberet.operations.JobOperatorImpl.start(JobOperatorImpl.java:104)

Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][10120][11803][4.7.85] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

  at com.ibm.db2.jcc.am.dd.a(dd.java:660)

  at com.ibm.db2.jcc.am.dd.a(dd.java:60)

  at com.ibm.db2.jcc.am.dd.a(dd.java:103)

  at com.ibm.db2.jcc.am.fb.b(fb.java:1259)

  at com.ibm.db2.jcc.am.fb.b(fb.java:1284)

  at com.ibm.db2.jcc.am.fb.getLong(fb.java:476)

  at org.jberet.repository.JdbcRepository.insertJobInstance(JdbcRepository.java:323)

  ... 8 more

            So question is why do we need to have two jar files in classpath with correct order?   

Thanks,

KK.

[cfang]

In my testing with db2, I only have db2jcc4.jar in the client classpath, and it works fine:

-rw-r--r--@ 1 staff  3411524 Aug  5 14:12 /Users/cfang/tmp/db2jcc4.jar

The reason for "no suitable driver" error is that jdbc4-compliant jdbc driver jars can automatically discover and register the suitable jdbc driver.  A similar recent thread:

Running standalone - JBERET000601: Failed to get job xml file for job

I'm not sure about the error from having both db2 driver jars in the classpath.  That's something I would avoid anyway, once you decide on a appropriate version of driver to support.

For older driver jars, you can set the system property to explicitly specify the driver class:

-Djdbc.drivers=com.ibm.db2.jcc.DB2Driver

[kk31]

I tried only with db2jcc4.jar  in my classpath before posting my question in this forum.  I tried one again only with db2jcc4.jar  and i am getting second exception which i posted earlier.

WARN: WELD-001700: Interceptor annotation class javax.ejb.PrePassivate not found, interception based on it is not enabled

Exception in thread "main" javax.batch.operations.BatchRuntimeException: JBERET000626: Failed to run INSERT INTO JOB_INSTANCE(JOBNAME, APPLICATIONNAME) VALUES(?, ?)

  at org.jberet.repository.JdbcRepository.insertJobInstance(JdbcRepository.java:326)

  at org.jberet.repository.AbstractPersistentRepository.createJobInstance(AbstractPersistentRepository.java:105)

  at org.jberet.operations.JobOperatorImpl$2.invoke(JobOperatorImpl.java:127)

  at org.jberet.operations.JobOperatorImpl$2.invoke(JobOperatorImpl.java:124)

  at org.jberet.operations.JobOperatorImpl.invokeTransaction(JobOperatorImpl.java:402)

  at org.jberet.operations.JobOperatorImpl.start(JobOperatorImpl.java:124)

  at org.jberet.operations.JobOperatorImpl.start(JobOperatorImpl.java:104)

  at org.jberet.se.Main.main(Main.java:51)

Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][10120][11803][4.7.85] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

  at com.ibm.db2.jcc.am.dd.a(dd.java:660)

  at com.ibm.db2.jcc.am.dd.a(dd.java:60)

  at com.ibm.db2.jcc.am.dd.a(dd.java:103)

  at com.ibm.db2.jcc.am.fb.b(fb.java:1259)

  at com.ibm.db2.jcc.am.fb.b(fb.java:1284)

  at com.ibm.db2.jcc.am.fb.getLong(fb.java:476)

  at org.jberet.repository.JdbcRepository.insertJobInstance(JdbcRepository.java:323)

  ... 8 more

       To avoid this exceptions i have to keep both jar files in classpath and in right order.

Here is my updated  classpath with only db2jcc4.jar .

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

<classpath>

  <classpathentry kind="src" path="src"/>

  <classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/beanio-2.1.0.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/cdi-api-1.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jberet-core-1.2.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jberet-se-1.2.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jberet-support-1.3.0.Beta3.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-batch-api_1.0_spec-1.0.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-logging-3.3.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-transaction-api_1.2_spec-1.0.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/weld-core-2.3.0.Beta3.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/weld-se-2.3.0.Beta3.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/wildfly-security-manager-1.1.2.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-marshalling-1.4.10.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/validation-api-1.1.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-annotations-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-core-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-databind-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-dataformat-csv-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-dataformat-xml-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/stax2-api-4.0.0.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/super-csv-2.3.1.jar"/>

  <classpathentry kind="lib" path="C:/xxxx/db2jcc4.jar"/>

  <classpathentry kind="output" path="bin"/>

</classpath>

[cfang]

1. JBERET-253

jdbcItemReader on db2: Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

For the benefits of others also seeing this problem, the above JBERET issue was fixed on 2018-08-05, and included in JBeret

1.3.0.Beta3

which was released on 2016-09-11.

[kk31]

I had this jar file in my above classpath. Still had same issue.

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

<classpath>

  <classpathentry kind="src" path="src"/>

  <classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/beanio-2.1.0.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/cdi-api-1.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jberet-core-1.2.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jberet-se-1.2.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jberet-support-1.3.0.Beta3.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-batch-api_1.0_spec-1.0.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-logging-3.3.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-transaction-api_1.2_spec-1.0.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/weld-core-2.3.0.Beta3.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/weld-se-2.3.0.Beta3.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/wildfly-security-manager-1.1.2.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jboss-marshalling-1.4.10.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/validation-api-1.1.0.Final.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-annotations-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-core-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-databind-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-dataformat-csv-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/jackson-dataformat-xml-2.6.1.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/stax2-api-4.0.0.jar"/>

  <classpathentry kind="lib" path="C:/jberet-distribution-1.2.0.Final/lib/super-csv-2.3.1.jar"/>

  <classpathentry kind="lib" path="C:/xxxx/db2jcc4.jar"/>

  <classpathentry kind="output" path="bin"/>

</classpath>