Simultaneously accessing the result sets returned by CURSOR type output parameters

For example, OrderDetails stored procedure declares three cursors, each giving relevant information about the product and its sales.

CREATE OR REPLACE TYPE cur AS CURSOR 
CREATE PROCEDURE OrderDetails (p_startDate TIMESTAMP, p_endDate TIMESTAMP, 
  OUT prodDetails cur, OUT prodOrderDetails cur, OUT custOrderDetails cur) 
LANGUAGE SQL 
BEGIN 
  SET prodDetails = CURSOR WITH HOLD FOR 
    SELECT p.pid, price, quantity FROM products p, inventory i 
      WHERE p.pid = i.pid AND p.pid IN (SELECT DISTINCT pid FROM orders) ORDER BY pid; 
  SET prodOrderDetails = CURSOR WITH HOLD FOR 
    SELECT pid, COUNT(*), SUM (quantity) FROM orders  
      WHERE date >= p_startDate AND date <= p_endDate GROUP BY pid ORDER BY pid; 
  SET custOrderDetails = CURSOR WITH HOLD FOR 
    SELECT pid, custID, COUNT(*), SUM(quantity) FROM orders 
      WHERE date >= p_startDate AND date <= p_endDate 
      GROUP BY pid, custID ORDER by pid, custID; 
  OPEN prodDetails; 
  OPEN prodOrderDetails; 
  OPEN custOrderDetails; 
END; 

The caller needs to access the cursors simultaneously so that it can gather the relevant information for a particular product from each of the cursors and calculate the discount. To provide simultaneous access to the cursors, the stored procedure returns the cursors as output parameters. The application must set the DB2Type to DB2Type.Cursor when binding the CURSOR type output parameters for simultaneous access to occur.

//C# Code sample
cmd.CommandText = "CALL OrderDetails( 
      @p_startDate, @p_endDate, @prodDetails, @prodOrderDetails, @custOrderDetails)"; 
cmd.Parameters.Add("@p_startDate", DateTime.Parse("1/1/2010")); 
cmd.Parameters.Add("@p_endDate", DateTime.Parse("12/31/2010")); 
cmd.Parameters.Add("@prodDetails", DB2Type.Cursor); 
cmd.Parameters["@prodDetails"].Direction = ParameterDirection.Output; 
cmd.Parameters.Add("@prodOrderDetails", DB2Type.Cursor); 
cmd.Parameters["@prodOrderDetails"].Direction = ParameterDirection.Output; 
cmd.Parameters.Add("@custOrderDetails", DB2Type.Cursor); 
cmd.Parameters["@custOrderDetails"].Direction = ParameterDirection.Output; 
cmd.ExecuteNonQuery(); 
DB2DataReader prodDetailsDR = 
    (DB2DataReader)cmd.Parameters["@prodDetails"].Value; 
DB2DataReader prodOrderDetailsDR = 
    (DB2DataReader)cmd.Parameters["@prodOrderDetails"].Value; 
DB2DataReader custOrderDetailsDR = 
    (DB2DataReader)cmd.Parameters["@custOrderDetails"].Value; 

while (prodOrderDetailsDR.Read()) 
{ 
    pid = prodOrderDetailsDR.GetInt32(0); 
    numOrders = prodOrderDetailsDR.GetInt32(1); 
    totalOrderQuantity = prodOrderDetailsDR.GetInt32(2); 
    prodDetailsDR.Read(); 
    price = prodDetailsDR.GetDecimal(1); 
    currentInventory = prodDetailsDR.GetInt32(2); 
    int totalCustOrders = 0; 
    while (custOrderDetailsDR.Read()) 
    { 
        custID = custOrderDetailsDR.GetInt32(1); 
        numOrdersByCust = custOrderDetailsDR.GetInt32(2); 
        totalCustOrders += numOrdersByCust; 
        totalOrderQuantityByCust = custOrderDetailsDR.GetInt32(3); 
        //Calculate discount based on numOrders, numOrdersByCust, 
        //  totalOrderQuantity, totalOrderQuantityByCust, price and currentInventory 
        if (totalCustOrders == numOrders) //done with this pid 
            break; 
    } 
} 
prodDetailsDR.Close(); 
prodOrderDetailsDR .Close(); 
custOrderDetailsDR .Close();