Batch insertion is getting failed with netezza database
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.
