Scoping rules in DB2
Comments (4) Visits (11946)
All there was were columns.
But then along came routines. Routines had parameters, local variables and FOR loops.
Next came global variables, then modules and ultimately pseudo-columns.
All of these objects share one and the same name space..
In this post I will try to illuminate the rules by which DB2 decides who is who.
Let's start with a simple example:
Presumably to no-one's surprise.CREATE TABLE T(c1 VARCHAR(10));
Let's complicate matters as bit:
There are two columns C1 that could be resolved here. But DB2 will always resolve to the one in the innermost scope.CREATE TABLE S(c1 VARCHAR(10));
The innermost scope is always the immediate FROM of a given query.
Two lessons are being taught by the example above.SELECT (SELECT c1 FROM T AS X(c2)) FROM S;
First when the column is renamed in the FROM clause is renamed then the original name becomes inaccessible.
That is in the above example neither T nor T.c1 are accessible. They are now known as X and X.c2 respectively.
Second when c1 cannot be found within the innermost FROM clause DB2 will look in the surrounding context.
What defines "outer"? Let's try some more scenarios:
This didn't go so well.SELECT (SELECT res FROM T, (VALUES c1) AS X(res)) FROM S;
DB2 didn't pick up T.c1 here because T is not an outer scope to the VALUES. It is "lateral" to VALUES.
But DB2 also didn't jump two levels up to S.c1. I suppose it could but, alas, it does not.
A small modification:
By telling DB2 that we want to allow LATERAL correlation it will find T.c1.SELECT (SELECT res FROM T, LATERAL(VALUES c1) AS X(res)) FROM S;
But will it also be able to find S.c1?
Indeed! So LATERAL not only allows DB2 to refer to laterally correlated columns it also allows it to look further out in the scope.SELECT (SELECT res FROM T AS Y(c2), LATERAL(VALUES c1) AS X(res)) FROM S;
Let's add another twist
So DB2 can look outside of an EXISTS to correlate it without problem.SELECT 1 FROM T WHERE EXISTS(SELECT c1 FROM S AS X(c2));
The column c1 wins over the variable because the variable is "further out" than the column.SET SERVEROUTPUT ON;
Let's validate that we can get to the variable.
FOR loops also produce a form of local variables.BEGIN
So the FOR loop introduces a level of nesting here and therefore eclipses the local variable.BEGIN
Note that compounds can be nested:
If we want to refer to the for loop we need to qualify:BEGIN
Let's move further out. The next level out are parameters of routines and transition variables of trigger.BEGIN
I'll limit myself to demonstrating functions here:
The pattern is becoming pretty obvious now the local variable is inside the compound block and it eclipses the parameter with the same nameCREATE OR REPLACE FUNCTION foo(c1 VARCHAR(10) DEFAULT 'Parm Foo')
If we want to reference the parameter we qualify it with the routine name
There is one more interesting scenario within functions.CREATE OR REPLACE FUNCTION foo(c1 VARCHAR(10) DEFAULT 'Parm Foo')
It is not possible to see a variable from compound that a statement isn't itself within.CREATE OR REPLACE FUNCTION foo(c1 VARCHAR(10) DEFAULT 'Parm Foo')
Again we are going inside out. The parameter wins over the module variable, but the module variable can be referred to by qualifying it with the module name or unqualified if it is not eclipsed:CREATE OR REPLACE MODULE mod@
Note that within a module context even variables which are not published can be accessed as long as they belong to the same module as the routine.ALTER MODULE mod DROP FUNCTION FOO(VARCHAR())@
We are slowly digging digging ourselves out from the depth of name resolution to the outer layers.
Next we deal with global variables.
By now I trust you will believe me if I tell you that any local module variable, parameter, local variable or column will eclipse a global variable. No need for proof.
But we are now starting to encounter a new curious beast. The PATH.CREATE OR REPLACE VARIABLE schema.c1 VARCHAR(10) DEFAULT 'Var Schema'@
Since variables reside in schemas we can only see them if their schema is on the PATH
OK, now as far as DB2 would be concerned we should be done now. But there are some rather curious behaviors that our beloved competition has concocted that, like it or not, DB2 must support for the sake of portability. The first one are PSEUDOCOLUMsSET PATH = CURRENT PATH, SCHEMA@
A pseudo column looks like a column but it isn't one at all. The three most common pseudo columns are NEXTVAL, CURRVAL and ROWNUM.
In Oracle S.NEXTVAL has the same meaning as NEXT VALUE FOR S.CREATE OR REPLACE VARIABLE S.NEXTVAL VARCHAR(10) DEFAULT 'Var Global'@
DB2 will prefer referencing to anything else first before, as a last resort try it's luck with a pseudo column
OK, this post certainly making up for my six weeks of silence in sheer length. There is one last curiosity that I want to discuss for completeness.DROP VARIABLE S.NEXTVAL@
Hold it.... wait for it....CREATE OR REPLACE FUNCTION C1() RETURNS VARCHAR(10) RETURN 'Func'@
Tadah!DROP VARIABLE SCHEMA.C1@
Again, thanks to our friends at Redwood Shores you may think you are referring to a column, or a variable, or a parameter or whatever, but really you are referring to a function.
DB2 will only do this if it has exhausted absolutely every other possibility.
Now I could talk about the rules for qualified names, but perhaps I reserve this for another day.
Let's do a final summary:
The order of resolution for simple identifiers is as such: