• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2140
  • Last Modified:

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!
0
TomMann
Asked:
TomMann
  • 2
1 Solution
 
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:
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
 
TomMannAuthor Commented:
Native solution to the AS400.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now