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!
TomMannAsked:
Who is Participating?
 
TomMannConnect With a Mentor Author Commented:
Thanks Dave, I think I found a solution using the AS400 IBM DB2 .Net Data Provider.  It's the DB2BulkCopy class.  It copies all rows from a data source to the AS400 table.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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

0
 
TomMannAuthor Commented:
Native solution to the AS400.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.