asked on

AS400 File Bulk Insert from Dataset Table

With VB.Net in VS2010, I'm using the IBMDA400 OLE DB connection to update an AS400 file from a dataset table that I populate from MS-Access. I fill the AS400 Dataset Table from the MS-Access table by looping through each row. After all rows have been added, I use the DataAdapter.Update(Dataset.Table) to update the Table on the AS400. It's working, but is extemely slow and I suspect it's doing individual inserts for each row (14,000 rows takes 2 hours!!). Is there a BULK INSERT method or another process that would be much faster. Thanks!

Member_2_2484401

Greetings, Tom!

In situations such as this, I usually use a "blocked insert" from a stored procedure in C. It's WAY faster than doing it record-by-record.

I'll copy in an excerpt from example.

HTH,
DaveSlash

void main(int argc, char* argv[]) {
 _Packed struct {
  char   v_col1;
  char   v_col2;
  char   v_col3;
  char   v_col4;
  char   v_col5;
  char   v_col6;
  char   v_col7;
  char   v_col8;
  char   v_col9;
 } columns[20000];
 
  long   i;
  long   BLOCKSIZE;
 
  BLOCKSIZE = 20000;
 
  for ( i = 1; i < 20001; i++ )  {
      columns[i].v_col1 = '1';
      columns[i].v_col2 = '2';
      columns[i].v_col3 = '3';
      columns[i].v_col4 = '4';
      columns[i].v_col5 = '5';
      columns[i].v_col6 = '6';
      columns[i].v_col7 = '7';
      columns[i].v_col8 = '8';
      columns[i].v_col9 = '9';
  }

  EXEC SQL                        
  INSERT INTO TESTTABLE ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9 )
    :BLOCKSIZE  ROWS VALUES( :columns );
 
  EXEC SQL COMMIT;
}

Open in new window

ASKER CERTIFIED SOLUTION

TomMann

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TomMann

ASKER

Native solution to the AS400.