SET CURRENT SCHEMA in a Trigger or Procedure

[TimMcConechy]

Hi,

In a trigger I need to do a SET CURRENT SCHEMA so that I update the
right set
of tables based on some "new" values I recieve in the trigger..

the logic looks a little like:

DECLARE lschema varchar(20);
set lschema=(select company from shared.logos where
segment=new.fie ld3);
SET CURRENT SCHEMA =lschema;
....
....
update styles set blah blah

Problem is I do this in a trigger..When I place the command in a
trigger I get:
SQL0104N An unexpected token "SCHEMA"
I also tried it in a function and I get the same variable?

Is there any way to dynamicly set the current schema like I want to
do..
CREATE FUNCTION SHARED.GETSCHEM A
(P_SEGMENT VARCHAR(200))
RETURNS VARCHAR(200)
BEGIN ATOMIC
DECLARE lSchema VARCHAR(200);
SET lSchema = (select company from shared.logos where
segment=P_SEGME NT FETCH FIRST ROW ONLY);
SET CURRENT SCHEMA = 'D123';
RETURN lSchema;
END;
[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "SCHEMA" was
found following "W ONLY); SET CURRENT". Expected tokens may include:
"<space>". LINE NUMBER=1. SQLSTATE=42601
Any ideas?

BR
Tim

[Serge Rielau]

Tim,

Special registers only affect dynamic SQL.
Since inline SQL PL does not support EXECUTE IMMEDIATE the whole point
of setting sich a register is moote.
In your example with teh function you would also be bitten by the fact
that special registers are scoped and never inherit up. Only down.

Anyway: What are you trying to do?
As long as you do not need to do writes you could put the dynamic logic
including the "set schema" into an external UDF (written in C or Java).
In Stinger you will be able to use CALL for that which will allow you to
stay within SQL PL.

Cheers
Serge