1

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?

Improve this question

2 Answers 2

Reset to default
3

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".
Improve this answer
3
  • Although if Proc B calls into Proc A, and I grant execute on B, but not A, I am assuming that when Proc B calls A, it will fail?
    – Chris Aldrich
    Commented Jul 31, 2014 at 16:12
  • You cannot grant execution privileges on individual routines in a module. You can only grant these privileges on the module itself, in which case the grantee will be able to execute all published routines in the module. These routines can invoke other, unpublished routines, to which the grantee otherwise does not have access. So, in your example you grant execute on the module M that has a published procedure B and unpublished procedure A. The caller can successfully call B, which calls A.
    – mustaccio
    Commented Jul 31, 2014 at 17:27
  • Yes, I understand that. I was attempting to address your final bullet point that while a person could modularize stored procedures you cannot prevent direct access to the modular ones due to permissioning. The only way to restrict access is via using a module.
    – Chris Aldrich
    Commented Aug 4, 2014 at 12:36
3

I use modules in two projects I have developed. With modules, I can organize the code more easily, and hide routines from the users. Also, modules provide me a way to have a standard name for the module, and the schema provide the version (https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/appupgrade?lang=en_us)

Another great thing about modules is the flexibility to create recursive functions.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.