MODULEs, a primer
Comments (2) Visits (9623)
Some of you may have been using DB2 as far back or even longer than DB2 7.1, or DB2 UDB V7.1 to be correct.
In that release we introduced SQL Procedures, and SQL Functions.
When we shipped the features there was a bit of a confusion because everyone from sales to marketing to customers seemed have a dire need for a name of logical constructs we supported.
It's just SQL we said. ANSI SQL comprises more than just SELECT. In includes IF, BEGIN.. END and so forth.
All we do is implement the SQL standard!
I learned and important lesson then. When you refuse to name a thing that wants to be named the market does it for you.
So we ended up with contraptions such as PL/SQL (oops, that's Oracle) and SPL (IDS).
We finally ended up with SQL Procedure Language (SQL PL) which has the unfortunate habit of being two acronyms SQL PL (hard to search) .
And it also get people confused with PL/SQL leaving us with SQL/PL.
This slash from OS/2 will haunt us to retirement I fear.
Internally however we did have a name: PSM
PSM is derived from the ANSI SQL/PSM section of the standard and stands for Persistent Stored Modules.
So, why didn't we use PSM externally? Simple: DB2 was missing the M for MODULE.
This post is all about that missing M which finally joined SQL PL in DB2 9.7.
In a nutshell a module in DB2 is an extension of the namespace for a number of objects types.
Objects in DB2 either have one part (TABLESPACE), or two parts (TABLE) to their name.
The first of the two parts is the schema.
Elements of modules
A module allows the the following objects a third name which is called the module name.
The objects for which this is allowed presently are:
There is no particular reason why no other objects are supported.
What these supported objects have in common however is that they are what one would typically find in a library in a regular procedural language.
And a library is really what a module is meant to model in DB2 (and ANSI SQL).
Creating a module
In ANSI SQL/PSM a Module is one single object very much like a file in, say C which contains all sorts of functions and types..
PL/SQL in Oracle is very similar. There is a head and a body and that's it.
We in DB2 Development didn't like that very much.
It seems outdated in an age of GUis and version control systems to thing as a module as something monolithic.
We wanted the properties associated with libraries, but with finer control.
So in DB2 you assemble modules piece by piece. And you can alter them on a per-object basis.
Every module starts of empty:
CREATE OR REPLACE MODULE HR;
That looks a lot like a CREATE SCHEMA statement, but a module itself is part of a schema.
So you can create modules of the same name in multiple schemata.
In addition access control is managed on a module level.
Something that is generally not available for schemata.
CREATE ROLE HR;
Now every member of the HR role will be able to reference all visible objects within the module.
I say visible here because libraries in most languages differentiate between private objects and public objects.
E.g. in C you may define an export file which tells the compiler/linker which symbols to export and make visible to users of the library.
Modules in DB2 support the same concept.
Adding prototypes to modules
Every object in the module can be either:
This is useful to implement recursive calls of routines as well as to provide an external specification for reference to the user which is needed to use the module.
Think of C-style header-files for comparison.
Since objects are added to the module we use the ALTER MODULE statement.
SET SCHEMA BLOG;
This is a valid specification for a module.
You cannot reference any object within the module without specifying the module itself.
Only within the module itself are references to its; own objects by simple name allowed.
To reference a module you can either qualify the module with its schema name
Unlike tables modules are not resolved by CURRENT SCHEMA :
VALUES CURRENT SCHEMA;
Instead they resolve strictly by PATH.
SET PATH = CURRENT PATH, BLOG;
By strictly I mean that the resolution of the module is independent of matching rules for routines.
Only once the module is determined will DB2 look at the best match for a referenced routine within that module.
The first module of the right name within the path is selected.
Lastly you can create public synonyms on modules.
If no module with the right name is found on the path DB2 will consider a public synonym:
SET PATH = SYSTEM PATH;
For more information about name resolution in general you can refer to Scoping Rules in DB2.
Adding routine bodies
While the type and variable are fully defined, procedures have no bodies.
Any attempt to execute them will yield and error:
Before fixing that we complete our schema with an employee table and a sequence;
SET SCHEMA = BLOG;
To provide the body for a previously specified routine we must use ADD and provide an exact match for the signature and any specified routine properties:
ALTER MODULE HR ADD PROCEDURE HIRE(INOUT emp hr.emp)
Now the procedures can be executed:
VARIABLE empid INTEGER;
Altering module elements
An advantage of modules over PL/SQL Packages is the ability to surgically change bits and pieces of it.
For example if we want to change the EMP type we can do that easily.
Currently DB2 does not support OR REPLACE fro module elements.
We need to DROP the TYPE before adding it back in.
ALTER TABLE emp ADD COLUMN country VARCHAR(20) DEFAULT 'Canada';
As a side note: DB2 automatically re-validated the HIRE procedure after the change to the emp data type.
So far all our objects within the module have been public.
Adding private module elements
Let's make the computation of the employee id a bit more interesting.
We want to empid to be combined with a country code.
The translation of countries to codes will be stored in a private array.
And we will define a routine to encapsulate the sequence generation
ALTER MODULE HR ADD TYPE countries AS INTEGER ARRAY[VARCHAR(20)];
Note that there is no requirement to have a separate specification for the routine.
The same is true for the public routine HIRE.
When we replace it we can immediately provide the body if that's what we want.
ALTER MODULE HR DROP PROCEDURE HIRE;
We forgot to fill in values for the countries!
This is an opportunity to introduce module initialization.
You can ADD a module procedure called SYS_INIT without parameters which is invoked by DB2 the first time an object in the module is referenced.
Note that there is no "un-init".
ALTER MODULE HR ADD PROCEDURE SYS_INIT
Sidenote: since my connection is the only one on my laptop and I did not activate my database explicitly DB2 deactivated the database.
As a result I lost a few sequence values from the cache.
Module initialization is rarely used since module variables provide DEFAULT clauses. But not every variable can have defaults.
Also SYS_INIT provides a better control about the timing of the initialization.
When using external routines SYS_INIT can be useful to set up the environment.
I have shown the highlights of module creation. There is nothing left but switching of the lights.
One thing you will learn to appreciate about modules is that all the module's objects get dropped when you drop the module.
DROP MODULE BLOG.HR;