ODBC: Sql "INSERT INTO" too slow

[Christian Luebke]

Hi,

I've to add several records via SQL into a database file on the AS/400.
When using the "INSERT INTO <file> VALUES(...)" statements it takes
about 30 secondes to add only 150 records to the file (using an already
established connection). Is there any method to block-write all records
at once instead of adding every single one?

Using the Client Access file transfer program needs only 1-2 seconds to
add the records to my file...

Any ideas?

Bye, Christian

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[Alba]

Christian Luebke wrote:

> Hi,
>
> I've to add several records via SQL into a database file on the AS/400.
> When using the "INSERT INTO <file> VALUES(...)" statements it takes
> about 30 secondes to add only 150 records to the file (using an already
> established connection). Is there any method to block-write all records
> at once instead of adding every single one?
>
> Using the Client Access file transfer program needs only 1-2 seconds to
> add the records to my file...
>
> Any ideas?
>
> Bye, Christian

File transfer is not SQL. Or - at least - it just simulate it but it
doesn't work with a DBMS. If your upload goes bad you cannot rollback, for
instance.
By the way, it's possible to use block insert statements but you need to
manipulate your client app source code to work with ODBC Apis. I don't know
any all-purpose client app which can handle this stuff.
The syntax for the block insert is like

INSERT INTO... ? ROWS VALUES (....)

and you need to specify the number of rows you're inserting ( the query can
be only a parametric query ) :

SQLParamOptions(hstmt, NUM_OF_ROWS, (UDWORD FAR*)&pNrows);

Hope this helps,
Alba

[OSITim]

Using ADO 2.0 and an ODBC driver you can block inserts.

[Christian Luebke]

In article <37D53D0A...@home.now>,

Alba <I...@home.now> wrote:
> By the way, it's possible to use block insert statements but you need
to
> manipulate your client app source code to work with ODBC Apis. I don't
know
> any all-purpose client app which can handle this stuff.
> The syntax for the block insert is like
>
> INSERT INTO... ? ROWS VALUES (....)
>
> and you need to specify the number of rows you're inserting ( the
query can
> be only a parametric query ) :
>
> SQLParamOptions(hstmt, NUM_OF_ROWS, (UDWORD FAR*)&pNrows);

And who can tell me how to get this working using MS Word with it's
WBODBC.WLL ? I'm transferring data from Word to the AS/400...

[Richard Hennessy]

Hi Alba

I have tried this syntax (? ROWS) and SQLParamOptions but if I insert
say 10 rows at a time, the values in all 10 rows are the same as the
first row.
Do you have a sample piece of code for block-inserting ?
Also, in the case of error the variable pNrows (in your example below)
does not get set.
Have you had this problem ?

regards
Richard Hennessy

Alba wrote:

> The syntax for the block insert is like
>
> INSERT INTO... ? ROWS VALUES (....)
>
> and you need to specify the number of rows you're inserting ( the
> query can
> be only a parametric query ) :
>
> SQLParamOptions(hstmt, NUM_OF_ROWS, (UDWORD FAR*)&pNrows);
>

> Hope this helps,
> Alba

[Kent Milligan]

There's a blocked insert code snippet at this page in the DB2 UDB for AS/400 web
site: http://www.as400.ibm.com/db2/db24blk.htm

Or you can goto the DB2 UDB for AS/400 home page (www.as400.ibm.com/db2) and
just take the Code Examples link.

--
Kent Milligan, DB2 & Business Intelligence team
AS/400 Partners In Development
km...@us.removethis.ibm.com GO HAWKEYES!!
(opinions stated are not necessarily those of my employer)