Using Sybase JConnect: ENABLE_BULK_LOAD

Question

Can someone provide an example of bulk inserts via JConnect (s ENABLE_BULK_LOAD) in Sybase ASE?

I browsed the internet and found nothing.

Answer 1

I contacted one of Sybase's engineers and they provided me with a sample code. Therefore, I can answer my question.

Basically, an example is given here, since the example code is quite large ... This involves a lot of pre-initialized variables, but otherwise it will be several hundred lines. Anyone interested should get this idea. This can give up to 22K inserts per second in an ideal world (Sybase anyway).

SybDriver sybDriver = (SybDriver) Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
sybDriver.setVersion(com.sybase.jdbcx.SybDriver.VERSION_6);
DriverManager.registerDriver(sybDriver);

//DBProps (after including normal login/password etc.
props.put("ENABLE_BULK_LOAD","true");

//open connection here for  sybDriver

dbConn.setAutoCommit(false);    
String SQLString = "insert into batch_inserts (row_id, colname1, colname2)\n values (?,?,?) \n";

PreparedStatement   pstmt;
try
{
   pstmt = dbConn.prepareStatement(SQLString);      
}
catch (SQLException sqle)
{
   displaySQLEx("Couldn't prepare statement",sqle);
   return;
}

for (String[] val : valuesToInsert)
{
   pstmt.setString(1, val[0]);  //row_id    varchar(30)
   pstmt.setString(2, val[1]);//logical_server varchar(30)
   pstmt.setString(3, val[2]);  //client_host varchar(30)

   try
   {
      pstmt.addBatch();
   }
   catch (SQLException sqle)
   {
      displaySQLEx("Failed to build batch",sqle);
      break;
   }
}

try {
   pstmt.executeBatch();
   dbConn.commit();
   pstmt.close();
} catch (SQLException sqle) {
   //handle
}

try {
   if (dbConn != null)
      dbConn.close();
} catch (Exception e) {
   //handle
}

Answer 2

To get the sample provided by Chris Cannon, remember to turn off auto-lock mode first:

dbConn.setAutoCommit(false);

And put the following line before dbConn.commit ():

pstmt.executeBatch();

Otherwise, this method will only slow down the insertion.

Answer 3

~ 100-1000rows . : * [5000rows in 500batches]: 1716ms = ~ 2914rows . ( !).

db (i7 ), :

CREATE TABLE
archive_account_transactions
(
account_transaction_id INT,
entered_by INT,
account_id INT,
transaction_type_id INT,
DATE DATETIME,
product_id INT,
amount float,
contract_id INT NULL,
note CHAR(255) NULL
)

account_transaction_id (pk), account_id, DATE, contract_id.

, , , :

jdbc:sybase:Tds:40.1.1.2:5000/ikp?EnableBatchWorkaround=true;ENABLE_BULK_LOAD=true   

.addBatch, , , java StringBuilder sql , execute. insert , , , , . Enable_bulk_load , EnableBatchWorkaround DYNAMIC_PREPARE = ​​false, , .

, , ! , - , , , ? - , , sybase, mysql 16 000 , " " .

Rod

Answer 4

, Java, LOAD TABLE SQL. Sybase ASA JConnect.

Answer 5

Statement () .

.. , SQL- sp jConnect.

. BatchUpdates.java (jConnect 4.x) sample2 (jConnect 5.x) Statement, PreparedStatement CallableStatement. jConnect PreparedStatements .

:

http://download.sybase.com/pdfdocs/jcg0420e/prjdbc.pdf

http://manuals.sybase.com/onlinebooks/group-jcarc/jcg0520e/prjdbc/@ebt-link;hf=0;pt=7694?target=%25N%14_4440_START_RESTART_N%25#X

.

http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html

http://www.jguru.com/faq/view.jsp?EID=5079