1 Reply Latest reply on Mar 30, 2017 11:32 AM by Ramesh Reddy

    Batch insertion is getting failed with netezza database

    Kulbhushan Chaskar Master

      Hi,

       

      I have creating non-xa datasource for netezza database.

       

      I am trying to execute batch(e.g. batch size >1 ) with preparedstatement but it is getting failed with netezza database for bulk insertion,which is working with other databases like (mysql,sqlserver,oracle etc.)

       

      I am able to insert data with batch size = 1.

       

      Below is the stackTrace I am using to perform batch insertion:

       

      public void insertData(){

      final int batchSize = 5;

        final int commitCnt = 10000;

        int count = 0;

        if (sql == null)

        return;

        Connection connection = getDriverConnection();

        String query=sql + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        //String query=sql + " values (1,'test')";

        PreparedStatement pstmt=null;

        try{

        pstmt=connection.prepareStatement(query);

        for(int i=1;i<=5;i++){

        pstmt.setString(1, ""+i);

        pstmt.setString(2,"2");

        pstmt.setString(3, "Test3_"+i);

        pstmt.setString(4, "Test4_"+i);

        pstmt.setString(5, "Test5_"+i);

        pstmt.setString(6, "Test6_"+i);

        pstmt.setString(7, "Test7_"+i);

        pstmt.setString(8, "Test8_"+i);

        pstmt.setString(9, "Test9_"+i);

        pstmt.setString(10, "Test10_"+i);

        pstmt.setString(11, "Test11_"+i);

        pstmt.setString(12, "Test12_"+i);

        pstmt.setString(13, "Test13_"+i);

        pstmt.setString(14, "Test14_"+i);

        pstmt.setString(15, "Test15_"+i);

        pstmt.setString(16, "Test16_"+i);

        pstmt.setString(17, "Test17_"+i);

        pstmt.setString(18, "Test18_"+i);

        pstmt.setString(19, "Test19_"+i);

        pstmt.setString(20, "Test20_"+i);

       

       

        pstmt.addBatch();

        if(++count % batchSize == 0) {

        int arrResult[]= pstmt.executeBatch();

        connection.commit();

        long endTime = System.currentTimeMillis();

            long elapsedTime = (endTime - startTime)/1000;;

            System.out.println("arrResult=="+arrResult.length);

            System.out.println("1000 Time diff=="+elapsedTime);

           }

       

        }

       

       

        int arrResult[]=pstmt.executeBatch();

        connection.commit();

        System.out.println("arrResult=="+arrResult.length);

        }

        catch (BatchUpdateException ex) {

        int[] updateCount = ex.getUpdateCounts();

        int count1 = 1;

        for (int i : updateCount) {

        if  (i == Statement.EXECUTE_FAILED) {

        System.out.println("Error on request " + count1 +": Execute failed");

        } else {

        System.out.println("Request " + count1 +": OK");

        }

        count1++;

       

        }

        }

        catch(SQLException e){

        e.printStackTrace();

       

        }

       

        if(isRollBack){

        connection.rollback();

        }else{

        connection.commit();

        }

        //statement.close();

        connection.close();

        System.out.println(" ");

        }

       

       

       

      static Connection getDriverConnection() throws Exception {

        System.out.println("Used values{host = "+ host + "}{Port = "+port+" }{vdb name = "+vdb+" }");

        String url = "jdbc:teiid:"+vdb+"@mm://"+host+":"+port+";showplan=on"; //note showplan setting

        Class.forName("org.teiid.jdbc.TeiidDriver");

         return DriverManager.getConnection(url,"user", pwd);

        }

       

       

      Exception:

       

      Used values{host = 10.10.127.101}{Port = 31000 }{vdb name = SvcSourceVdb_netezzaConn }

      org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 netezzaConn: 1100 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "TDM"."ADMIN"."PARENT_TABLETEST" ("COL1", "COL2", "COL3", "COL4", "COL5", "COL6", "COL7", "COL8", "COL9", "COL10", "COL11", "COL12", "COL13", "COL14", "COL15", "COL16", "COL17", "COL18", "COL19", "COL20") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]

      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

      at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:723)

      at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:65)

      at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:561)

      at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

      at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

      at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

      at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:285)

      at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:323)

      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

      at java.lang.reflect.Method.invoke(Unknown Source)

      at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

      at com.sun.proxy.$Proxy3.read(Unknown Source)

      at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:422)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:570)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:423)

      at org.teiid.jdbc.PreparedStatementImpl.executeBatch(PreparedStatementImpl.java:300)

      at TestPreapreStmntWithTeiid.WithTeiid.executeSqlUpdate(WithTeiid.java:626)

      at TestPreapreStmntWithTeiid.WithTeiid.testWith_Netezza_NonXA(WithTeiid.java:143)

      at TestPreapreStmntWithTeiid.WithTeiid.main(WithTeiid.java:53)

      Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 netezzaConn: 1100 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "TDM"."ADMIN"."PARENT_TABLETEST" ("COL1", "COL2", "COL3", "COL4", "COL5", "COL6", "COL7", "COL8", "COL9", "COL10", "COL11", "COL12", "COL13", "COL14", "COL15", "COL16", "COL17", "COL18", "COL19", "COL20") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]

      at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401)

      at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161)

      at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:392)

      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

      at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

      at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

      at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

      at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:477)

      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:349)

      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

      at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

      at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

      at java.lang.Thread.run(Unknown Source)

      Caused by: org.teiid.core.TeiidException: 1100 Remote org.teiid.translator.jdbc.JDBCExecutionException: 1100 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "TDM"."ADMIN"."PARENT_TABLETEST" ("COL1", "COL2", "COL3", "COL4", "COL5", "COL6", "COL7", "COL8", "COL9", "COL10", "COL11", "COL12", "COL13", "COL14", "COL15", "COL16", "COL17", "COL18", "COL19", "COL20") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]

      at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:330)

      at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:86)

      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:405)

      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:367)

      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

      at java.lang.reflect.Method.invoke(Unknown Source)

      at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)

      at com.sun.proxy.$Proxy27.execute(Unknown Source)

      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)

      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

      at java.util.concurrent.FutureTask.run(Unknown Source)

      at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)

      ... 6 more

      Caused by: java.sql.SQLException: Remote org.netezza.error.NzSQLException: netezza.ext.table.create.failed

      at org.netezza.sql.NzPreparedStatament.executeBatch(NzPreparedStatament.java:1040)

      at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1190)

      at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:289)

      ... 20 more

       

      Thanks,

      Kulbhushan Chaskar.