Db2 – How to call Oracle’s stored procedure from DB2 stored procedure

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

WHILE (@@FETCH_STATUS = 0) AND
            ( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
    -- about 700 lines of logic, math and if-parameter-this-then-that
    -- stuff omitted
      INSERT InnerProcedureCallWithinLoop__TraceTable
              VALUES (@from_locn, @About53PARAMSOMITTED
      EXEC @ConsiderItem =
            InnerProcedureCallWithinLoop
                  @from_locn,
                        @About53PARAMSOMITTED,
...

If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;