Db2 – Does anyone use modules in DB2 LUW

best practicesdb2stored-procedures

Our environment is currently DB2 9.7 Fix Pack 4 on AIX.

I am in the processing of designing and coding some SQL PL stored procedures responsible for archiving data into an archive schema and then purging out old/un-needed data from the main schema.

I created some user defined types (UDTs), specifically row types and cursor types. I am looking at dividing my logic into three stored procedures. Proc A would be used to purge a particular set of data related to my main EVENT table (ie, the EVENT and everything underneath it). I don't really want others to be able to call this. I just want to modularize the logic. Proc B would archive data from EVENT (and other tables) that the end user wants and then call into Proc A to clean up the original data. Proc C would be used to look for EVENTs that could be discarded and then call into Proc A for actual work.

Now I could create all of these independent of each other, and then deploy them that way. Or….I could use a module. From what I understand a module allows me to associate related functionality (types, stored procs, functions, etc.) together. It also allows me to decide which objects I wish to be accessible external to the module, ie, can be called/used directly, versus which ones can only be called internal to the module (such as the case above, where I could hide stored Proc A, but allow B and C to call it).

This sounds incredibly intriguing. But it has its downsides too. I'm guessing it isn't part of any SQL standard, but something specific to IBM DB2. Even as such, you cannot directly interact with modules via Data Studio or Control Center (other than CALLing the exteral facing procedures/functions). And you cannot create them directly in IBM's InfoSphere Data Architect either. So I would have to maintain the DDL's independently.

So the module concept sounds handy. I can hide some things and choose what can be called to make sure someone doesn't misuse anything.

But, given it doesn't seem to be supported by IBM tooling outside of command line….I was wondering….who all uses modules (specifically in DB2 LUW)? Does anyone? Does it makes sense? If not, is there another way I could accomplish my thought of modularizing code while making the "modules" inaccessible for direct calling?

Best Answer

I won't be able to answer all your questions, but for what it's worth:

  • SQL modules seem to be a part of the SQL/PSM 2008 standard. At least they appear in the draft version, that can be found on the interwebs.
  • Tools will eventually catch up with the server features. Meanwhile, you can use the Oracle-compatible PL/SQL dialect, currently supported by tools, to create PL/SQL packages, which are essentially the same thing.
  • Yes, people do use module. Many of the DB2 system-provided routines are implemented in modules.
  • While you can restrict direct invocation of routines outside modules by granting appropriate privileges, you cannot "modularize" anything without "modules".
Related Question