Isolation Level inside C++ routines / Slow performance.

[Michel Esber]

DB2 LUW versions v7 and v8

We have an application that opens a cursor, retrieves the result set
into memory and summarizes it, saving data into another DB.

Due to high network bandwidth usage, we have decided to implement the
same concept using C++ stored procedures. The procedure is called,
reads and summarizes data, saving data into a table. The application
will then read this summarized data and save it into another DB.

Our network consumption has indeed decreased using the second approach.
However, the procedure takes longer to return compared to our previous
model (where the business intelligence was in the application).

This is a high workload and transaction system. Do I need to enforce an
isolation level inside the C++ routine, or will it inherit the same
isolation of the calling application?

Is there any way to troubleshoot and identify where and why the
procedure is slower than the previous method?

TIA for any suggestions,

[Serge Rielau]

Michel Esber wrote:

DB2 LUW versions v7 and v8

We have an application that opens a cursor, retrieves the result set
into memory and summarizes it, saving data into another DB.

Due to high network bandwidth usage, we have decided to implement the
same concept using C++ stored procedures. The procedure is called,
reads and summarizes data, saving data into a table. The application
will then read this summarized data and save it into another DB.

Our network consumption has indeed decreased using the second approach.
However, the procedure takes longer to return compared to our previous
model (where the business intelligence was in the application).

This is a high workload and transaction system. Do I need to enforce an
isolation level inside the C++ routine, or will it inherit the same
isolation of the calling application?

Is there any way to troubleshoot and identify where and why the
procedure is slower than the previous method?

TIA for any suggestions,

The isolation level will be determined at prep/bind time as defined in
the DB CFG (I think) unless you override with a BIND option.
make sure you have BLOCKING ALL set to batch resultsets (yes, even for
procedure this is important)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab