Running a batch of sql queries?

[mark.a.lemoine]

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

[Serge Rielau]

ma************@gmail.com wrote:

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

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

[mark.a.lemoine]

>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

[Serge Rielau]

ma************@gmail.com wrote:

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?

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

[Gert van der Kooij]

In article <46************@individual.net>, Serge Rielau
(sr*****@ca.ibm.com) says...

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

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

[Serge Rielau]

Gert van der Kooij wrote:

In article <46************@individual.net>, Serge Rielau
(sr*****@ca.ibm.com) says...

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

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

Yes.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[mark.a.lemoine]

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.

[Gert van der Kooij]

In article <46************@individual.net>, Serge Rielau
(sr*****@ca.ibm.com) says...

Gert van der Kooij wrote:

In article <46************@individual.net>, Serge Rielau
(sr*****@ca.ibm.com) says...

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

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

Yes.

That's nice, thanks.