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
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?