INHERIT SPECIAL REGISTERS

[jefftyzzer]

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

[Serge Rielau]

jefftyzzer wrote:

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

In general special registers apply to dynamic SQL only.
So if you want the schema of a query to be influenced by the outside
world then you need to use PREPARE and a dynamic cursor.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[jefftyzzer]

Thanks, Serge. An experiment and a more careful reading of the CURRENT
SCHEMA entry in V1 of the SQL Reference confirms your reply:

"The CURRENT SCHEMA ...special register specifies a...value that
identifies the schema name used to qualify database object
references...in dynamically prepared SQL statements.... The QUALIFIER
bind option controls the schema name used to qualify database object
references...for static SQL statements."

Looks like I'll be rewriting some FOR loops and EXECUTing IMMEDIATE a
DELETE and INSERT.

Regards,

--Jeff

Serge Rielau wrote:

jefftyzzer wrote:

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

In general special registers apply to dynamic SQL only.
So if you want the schema of a query to be influenced by the outside
world then you need to use PREPARE and a dynamic cursor.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

jefftyzzer wrote:

Thanks, Serge. An experiment and a more careful reading of the CURRENT
SCHEMA entry in V1 of the SQL Reference confirms your reply:

"The CURRENT SCHEMA ...special register specifies a...value that
identifies the schema name used to qualify database object
references...in dynamically prepared SQL statements.... The QUALIFIER
bind option controls the schema name used to qualify database object
references...for static SQL statements."

Looks like I'll be rewriting some FOR loops and EXECUTing IMMEDIATE a
DELETE and INSERT.

Keep in mind that by default dynamic SQL executes under "invoker's
rights". You can modify that by setting the respective bind options via
the SET_ROUTINE_OPTS() procedure.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/