Running a batch of sql queries?

Our application currently interfaces to SQL Server and Oracle, we're
implementing it into DB2 currently and I have a question to ask.

First a little background - We're interfacing into DB2 v8 on zOS (I
hope the terminology is right - ours is a Windows client app, and the
DB2 is at a customer site and they are taking care of the server),
using ADO with the IBM OLE Db Provider (IBMDADB2). We don't use any
vendor specific APIs, instead we stick to running all of our sql
queries through the recordset and execute ADO methods. This allows us
to minimize the vendor specific code in our app since we restrict
ourselves to using the industry standard SQL syntax (hah! Well, at
least it's less "unstandard" than the individual vendor APIs).

In working with SQL Server and Oracle, one lesson learned is that
sending a series of related queries in a single batch (that is, a
single recordset or execute method) to the database significantly
improves performance (one trip across the WAN to the server, one
execution plan compiled, etc.). Assuming that this is also true for
DB2, my question is how to make this work. So far, I haven't had
success at this (it's working just fine in sending the queries
individually, I'm trying to get it to work in a batch).

Here's a simplification of my situation (hopefully, not an
oversimplification). Table1 has an identity field, along with other
data columns, and there is a one-to-many relationship to Table2. There
could be one or a couple of dozen records inserted into Table2,
depending on the context, for each record in Table1, so doing this work
in a stored procedure is, unfortunately, not feasible.

This is an example of the batch that's sent when we're interfacing to
SQL Server:

declare @RecId as int;
Begin Transaction;
Insert Into Table1 (data columns) Values (data values);
Set @RecId = @@Identity;
Insert Into Table2 (Recordid, other data columns) Values (@RecId, other
data values);
Insert Into Table2 (Recordid, other data columns) Values (@RecId, other
data values);
....
Commit Transaction

So, I've tried a variety of things to accomplish the same sort of task
for DB2. Here's one sample:

Declare vRecId as int;
Insert Into Table1 (data columns) Values (data values);
Values IDENTITY_VAL_LOCAL() into :vRecId;
Insert Into Table2 (Recordid, other data columns) Values (:vRecId,
other data values);
Insert Into Table2 (Recordid, other data columns) Values (:vRecId,
other data values);
....
Commit

So, what am I doing wrong? Is this feasible?

Apologies for the longish post, TIA, and have a nice day!
Mark

re: Running a batch of sql queries?

mark.a.lemoine@gmail.com wrote:[color=blue]
> Our application currently interfaces to SQL Server and Oracle, we're
> implementing it into DB2 currently and I have a question to ask.
>
> First a little background - We're interfacing into DB2 v8 on zOS (I
> hope the terminology is right - ours is a Windows client app, and the
> DB2 is at a customer site and they are taking care of the server),
> using ADO with the IBM OLE Db Provider (IBMDADB2). We don't use any
> vendor specific APIs, instead we stick to running all of our sql
> queries through the recordset and execute ADO methods. This allows us
> to minimize the vendor specific code in our app since we restrict
> ourselves to using the industry standard SQL syntax (hah! Well, at
> least it's less "unstandard" than the individual vendor APIs).
>
> In working with SQL Server and Oracle, one lesson learned is that
> sending a series of related queries in a single batch (that is, a
> single recordset or execute method) to the database significantly
> improves performance (one trip across the WAN to the server, one
> execution plan compiled, etc.). Assuming that this is also true for
> DB2, my question is how to make this work. So far, I haven't had
> success at this (it's working just fine in sending the queries
> individually, I'm trying to get it to work in a batch).
>
> Here's a simplification of my situation (hopefully, not an
> oversimplification). Table1 has an identity field, along with other
> data columns, and there is a one-to-many relationship to Table2. There
> could be one or a couple of dozen records inserted into Table2,
> depending on the context, for each record in Table1, so doing this work
> in a stored procedure is, unfortunately, not feasible.
>
> This is an example of the batch that's sent when we're interfacing to
> SQL Server:
>
> declare @RecId as int;
> Begin Transaction;
> Insert Into Table1 (data columns) Values (data values);
> Set @RecId = @@Identity;
> Insert Into Table2 (Recordid, other data columns) Values (@RecId, other
> data values);
> Insert Into Table2 (Recordid, other data columns) Values (@RecId, other
> data values);
> ...
> Commit Transaction
>
> So, I've tried a variety of things to accomplish the same sort of task
> for DB2. Here's one sample:
>
> Declare vRecId as int;
> Insert Into Table1 (data columns) Values (data values);
> Values IDENTITY_VAL_LOCAL() into :vRecId;
> Insert Into Table2 (Recordid, other data columns) Values (:vRecId,
> other data values);
> Insert Into Table2 (Recordid, other data columns) Values (:vRecId,
> other data values);
> ...
> Commit
>
> So, what am I doing wrong? Is this feasible?
>
> Apologies for the longish post, TIA, and have a nice day!
> Mark
>[/color]
You want t take a look at multi-row insert:
http://publib.boulder.ibm.com/infoce...bjnvmstr50.htm

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Running a batch of sql queries?

>From the link reference, and reading other docs on the Insert, this
means that I can collapse the multiple Insert queries for Table2 down
to a single query. That's cool - thanks.

But I think my bigger challenge is how do I get the identity value
generated by the insert into Table1 into the insert query (or queries)
when inserting into Table2?

I'd like to run a single execute method from my client app, for
improved efficiency, that'll perform the inserts into both tables:

Insert into Table1 (column names) Values (data values);
Insert into Table2 (RecordId, other column names) Values (<Identity
from Table1>, other data values), (<Identity from Table1>, other data
values), ...

What do I need to add to the above sql (maybe similar to my SQL Server
version of this, maybe not) to achieve this?

Thanks for your time
Mark

re: Running a batch of sql queries?

mark.a.lemoine@gmail.com wrote:[color=blue][color=green]
>>From the link reference, and reading other docs on the Insert, this[/color]
> means that I can collapse the multiple Insert queries for Table2 down
> to a single query. That's cool - thanks.
>
> But I think my bigger challenge is how do I get the identity value
> generated by the insert into Table1 into the insert query (or queries)
> when inserting into Table2?
>
> I'd like to run a single execute method from my client app, for
> improved efficiency, that'll perform the inserts into both tables:
>
> Insert into Table1 (column names) Values (data values);
> Insert into Table2 (RecordId, other column names) Values (<Identity
> from Table1>, other data values), (<Identity from Table1>, other data
> values), ...
>
> What do I need to add to the above sql (maybe similar to my SQL Server
> version of this, maybe not) to achieve this?[/color]
DB2 has two solutions for you here.
One is the solution that mimics SQL Server .
The function identity_val_local() will return the identity value
generated for the last single row insert.
The second solution is to use
SELECT <identity_col> FROM FINAL TABLE(INSERT INTO table1 ....)
If you are familiar with Oracle's INSERT WITH RETURN, it's the same
thing (just better ;-)

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Running a batch of sql queries?

In article <46oksdFc5n6aU1@individual.net>, Serge Rielau
(srielau@ca.ibm.com) says...[color=blue]
> DB2 has two solutions for you here.
> One is the solution that mimics SQL Server .
> The function identity_val_local() will return the identity value
> generated for the last single row insert.
> The second solution is to use
> SELECT <identity_col> FROM FINAL TABLE(INSERT INTO table1 ....)
> If you are familiar with Oracle's INSERT WITH RETURN, it's the same
> thing (just better ;-)
>
> Cheers
> Serge[/color]

Is his gonna work on DB2 for zOS also (because that's what the OP was
talking about).

re: Running a batch of sql queries?

Gert van der Kooij wrote:[color=blue]
> In article <46oksdFc5n6aU1@individual.net>, Serge Rielau
> (srielau@ca.ibm.com) says...[color=green]
>> DB2 has two solutions for you here.
>> One is the solution that mimics SQL Server .
>> The function identity_val_local() will return the identity value
>> generated for the last single row insert.
>> The second solution is to use
>> SELECT <identity_col> FROM FINAL TABLE(INSERT INTO table1 ....)
>> If you are familiar with Oracle's INSERT WITH RETURN, it's the same
>> thing (just better ;-)
>>
>> Cheers
>> Serge[/color]
>
> Is his gonna work on DB2 for zOS also (because that's what the OP was
> talking about).[/color]
Yes.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Running a batch of sql queries?

I have tried using identity_val_local (). Maybe something else is
wrong because it's returning NULL. In the simpliest scenario of a
single record into Table1 and Table2:

Insert into Table1 (column names) Values (data values);
Insert into Table2 (RecordId, other column names) Values
(identity_val_local(), other data values);
Commit

This ends up trying to insert a null value in Table2 for that column.

re: Running a batch of sql queries?

In article <46or6oFc6qcdU1@individual.net>, Serge Rielau
(srielau@ca.ibm.com) says...[color=blue]
> Gert van der Kooij wrote:[color=green]
> > In article <46oksdFc5n6aU1@individual.net>, Serge Rielau
> > (srielau@ca.ibm.com) says...[color=darkred]
> >> DB2 has two solutions for you here.
> >> One is the solution that mimics SQL Server .
> >> The function identity_val_local() will return the identity value
> >> generated for the last single row insert.
> >> The second solution is to use
> >> SELECT <identity_col> FROM FINAL TABLE(INSERT INTO table1 ....)
> >> If you are familiar with Oracle's INSERT WITH RETURN, it's the same
> >> thing (just better ;-)
> >>
> >> Cheers
> >> Serge[/color]
> >
> > Is his gonna work on DB2 for zOS also (because that's what the OP was
> > talking about).[/color]
> Yes.
>[/color]

That's nice, thanks.