sql-server - 将 clob 字段从 Oracle 导出到 Azure DB 上的文本字段时出错

Question

我在存储 PDF 文件的 Oracle 表上有一个 CLOB 字段。当我尝试将此字段导出到 Azure 上的 SQL Server Db 时，出现此错误:

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

但是，如果我对本地 SQL Server DB 执行相同的转换，则效果很好。

如何解决这个问题？

更新1

我意识到，当我在 TableOutput 步骤中将“Commit Size”设置为大于 15 并将“Use batch update for inserts”设置为 true 时，就会出现问题。

Answer 1

您可以尝试修改客户端的TCP参数:

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