Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error while exporting clob field from oracle to text field on Azure DB

I have a CLOB field on an Oracle Table that stores a PDF file. When I try export this field to SQL Server Db on Azure I got this error:

2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Unexpected batch update error committing the database connection.
2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException: 
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Error updating batch
2017/01/19 11:14:32 - ImpostoRenda 2.0 - I/O Error: Connection reset by peer: socket write error
2017/01/19 11:14:32 - ImpostoRenda 2.0 - 
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1379)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1368)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:575)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at java.lang.Thread.run(Unknown Source)
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Caused by: java.sql.BatchUpdateException: I/O Error: Connection reset by peer: socket write error
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:969)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1355)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    ... 3 more
2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Unexpected error rolling back the database connection.
2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Error performing rollback on connection
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Invalid state, the Connection object is closed.
2017/01/19 11:14:32 - ImpostoRenda 2.0 - 
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.rollback(Database.java:854)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.rollback(Database.java:832)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:610)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at java.lang.Thread.run(Unknown Source)
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1699)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:2100)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.rollback(Database.java:845)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    ... 4 more

But, if I execute the same transform to local SQL Server DB, it works fine.

How do I resolve this?

UPDATE 1

I realized that just when I put "Commit Size" bigger than 15 and set true on "Use batch update for inserts" in my TableOutput Step, the problem occurs.

like image 521
Renatto Machado Avatar asked Jan 19 '17 13:01

Renatto Machado


People also ask

How do I export from CLOB?

Solution: I export data in XML or PDF or loader or Json. Then i got data that is clob or blob. Tools > Database Export > select connection > uncheck export DDl if you want only data > check export data > select format: XML > next > next > next > next >finish.

How do I export data from CLOB to excel?

Once you created the table with clob column and inserted some rows into it, enter the SQL editor and select the columns of your interest and execute it. In the results windows you have the icon to export the data into . csv file or open it in Microsoft excel file.


1 Answers

You can try to modify TCP parameters on the client:

REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveTime /t REG_DWORD /d 30000
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveInterval /t REG_DWORD /d 1000
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpMaxDataRetransmission /t REG_DWORD /d 10
like image 67
ErikEJ Avatar answered Oct 07 '22 20:10

ErikEJ