RE: IBMDA400 and performance using VBA in Excel

It's doing what you told it to do and the way you told it to do it is
about the worst possible way.

A few things:
-I know the ODBC driver supported the concept of "blocked inserts" I
assume OLEDB does, but a quick search didn't turn up any links to give
you.
-ODBC also supported "lazy close".  The DB would keep the file open
after being told to close it in case it was used again.
-A parameterized statement instead of the execute immediate should help.

In addition, one thing I've done when I couldn't use actual blocked
insert support is a statement like so

insert into mytable
 (select 'DOG', 'APPLE' from sysibm.sysdummy1
   UNION ALL
  select 'CAT', 'PEAR' from sysibm.sysdummy1
   UNION ALL
  select 'BIRD', 'BANANA' from sysibm.sysdummy1
 )


In any event, be sure and post back if you figure out the syntax for
blocked inserts with OLEDB.  I'd like to be able to file it away for
future reference.

HTH,



Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of nick
> Sent: Thursday, January 05, 2006 8:54 AM
> To: Midrange Forumn
> Subject: IBMDA400 and performance using VBA in Excel
> 
> Greetings all,
> 
>  
> 
> I'm not sure if this is the correct place to post this, but 
> please correct
> me if its not.
> 
>  
> 
> I wrote a VBA proc to upload records from an Excel spreadsheet to the
> iSeries. I realize I can use IBM's file upload addin for 
> Excel, but chose
> not to. When the code runs, it take a very long time. I 
> looked at the job
> running on the iSeries  and noticed it opening and closing 
> the file I'm
> uploading to for each row in the spreadsheet. A snipit of the code is
> follows: 
> 
>  
> 
> Set cn_iSeries = CreateObject("ADODB.Connection")
> 
> cn_iSeries.Open "provider=IBMDA400;data source=S103LB4M;", "", ""
> 
> Set rs = CreateObject("ADODB.Recordset")
> 
> Set cmd = CreateObject("ADODB.Command")
> 
> Set cmd.ActiveConnection = cn_iSeries
> 
>   
> 
> Set currentcell = Worksheets(ActiveSheet.Name).Range("A3")
> 
>  
> 
> Do Until currentcell.Value = ""
> 
>   
> 
>   cmd.CommandText = "INSERT INTO library.file VALUES ("
> 
>   cmd.CommandText = cmd.CommandText & "'" & 
> currentcell.Offset(0, 0).Value &
> "'" & ","
> 
>   cmd.CommandText = cmd.CommandText & "'" & 
> currentcell.Offset(0, 1).Value &
> "'"
> 
>   cmd.CommandText = cmd.CommandText & ")"
> 
>   
> 
>   cmd.CommandType = adCmdText
> 
>   cmd.Execute
> 
>   
> 
>    Loop  
> 
>  
> 
>  
> 
> I was wondering if there is a better way performance wise to 
> do this. It
> would be nice if the iSeries wouldn't open and close the file 
> for each row,
> but I'm not sure if that can be controlled or not. 
> 
>  
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>