4 Replies Latest reply on Oct 3, 2016 3:50 PM by kk31

    issue with jdbcItemReader DB2 driver

    kk31 Newbie

      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.

        • 1. Re: issue with jdbcItemReader DB2 driver
          Cheng Fang Master

          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

          • 2. Re: issue with jdbcItemReader DB2 driver
            kk31 Newbie

            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>

            • 3. Re: issue with jdbcItemReader DB2 driver
              Cheng Fang Master
              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.

              • 4. Re: issue with jdbcItemReader DB2 driver
                kk31 Newbie

                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>