Friends:

I have a stored procedure declared as:

CREATE PROCEDURE X.Y
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    SPECIFIC Y
    INHERIT SPECIAL REGISTERS
    CALLED ON NULL INPUT
BEGIN
...

Let's say all this SP does is "select * from table X" and return the
result set, and that X exists in schemas S1 and S2. Further, X is empty
in schema S1 but populated in S2. Lastly, when I created the SP, my
current schema was set to S1.

If I execute SET SCHEMA S2 and call the procedure, I get no rows, even
though X is populated there *and* I have "INHERIT SPECIAL REGISTERS"
declared (which, anyway, I thought was the default). The only way my SP
will read X in S2 is if I drop it, SET SCHEMA S2, and recreate the SP.
I of course have to SET SCHEMA to some value where the table referenced
in the SP actually exists, otherwise I get an object not found error.

I thought INHERIT SPECIAL REGISTERS was the answer to the "SP exists
here, but the objects it works with are over here" question, but it
doesn't seem to be working as I understand it to be documented. I'd
imagine the requirement to have a centralized SP that can run against
objects in multiple schemas is fairly common, so what am I doing wrong?

BTW, when I select QUALIFIER and SPEC_REG FROM SYSCAT.ROUTINES, I see
the schema I was set to before I issued the create (as expected), and
'I', respectively. SOURCESCHEMA is NULL.

Env: DB2 LUW 8.3, AIX 5.x

Thanks,

--Jeff