DatabaseMetaData extensions for obtaining information about modules

The com.ibm.db2.jcc.DB2DatabaseMetaData class contains methods that let you retrieve information about procedures, functions, and user-defined types that are in modules.

A module is a database object that is a collection of other database objects such as functions, procedures, types, and variables. Modules are similar to Java™ classes. The primary purpose of modules is to group together object definitions that have a common business purpose or use. Modules are supported by Db2® on Linux®, UNIX, and Windows systems Version 9.7 or later.

The IBM® Data Server Driver for JDBC and SQLJ provides the following methods in the DB2DatabaseMetaData interface that let you retrieve information about database objects that are in modules. Each method returns a result set that contains all of the columns in the related java.sql.DatabaseMetaData method, and an extra column that identifies the module in which the database object resides.

Method	Related DatabaseMetaData method	Information returned
getDBFunctionColumns	getFunctionColumns	Information about the parameters and return values for user-defined functions or built-in functions that are at a data source.
getDBFunctions	getFunctions	Information about the user-defined functions or built-in functions that are at a data source.
getDBProcedureColumns	getProcedureColumns	Information about the parameters and return values for stored procedures that are at a data source.
getDBProcedures	getProcedures	Information about the stored procedures that are at a data source.
getDBUDTs	getUDTs	Information about the user-defined types that are at a data source.

If the same stored procedures, functions, or user-defined types are defined in several different modules, and you call one of the DatabaseMetaData methods, those methods return more than one row for the same procedure, function, or user-defined type. Those rows are identical, except for the SPECIFICNAME column. To uniquely identify stored procedures, functions, or user-defined types by module name, use the DB2DatabaseMetaData methods.

Example

Suppose that your ID is DB2ADMIN, and you create two modules, named MYMOD1 and MYMOD2:

…
stmt.execute ("CREATE MODULE MYMOD1");
stmt.execute ("CREATE MODULE MYMOD2");
…

Next, you create the same SQL procedure named PROC1 in module MYMOD1 and module MYMOD2:

…
stmt.execute ("ALTER MODULE MYMOD1 PUBLISH PROCEDURE PROC1 ( " +
                "IN PARM1 BOOLEAN, " +
                "INOUT PARM2 INTEGER) " +
                "LANGUAGE SQL " +
                "BEGIN…END");
stmt.execute ("ALTER MODULE MYMOD2 PUBLISH PROCEDURE PROC1 ( " +
                "IN PARM1 BOOLEAN, " +
                "INOUT PARM2 INTEGER) " +
                "LANGUAGE SQL " +
                "BEGIN…END");

The following example uses the DB2DatabaseMetaData.getDBProcedures method to return information about all procedures named PROC1 in schema DB2ADMIN that are in modules with a name like "MYMOD%".

Connection con;
…                       // Establish a connection to the data source
DatabaseMetaData dbmd = con.getMetaData();
                        // Create a DatabaseMetaData object
String schemaname="DB2ADMIN";
String modulename="MYMOD%";
String procname="PROC1";    
                        // Indicate that you want information
                        // about all procedures that meet these
                        // criteria:
                        // Schema: DB2ADMIN
                        // Module: MYMOD%
                        // Procedure name: PROC1
ResultSet rs = 
 ((com.ibm.db2.jcc.DB2DatabaseMetaData)dbmd).
   getDBProcedures(null,schemaname,modulename,procname);  
                        // Cast the DatabaseMetaData object
                        // to a DB2DatabaseMetaData object, 
                        // and call DB2DatabaseMetaData.getDBProcedures
ResultSetMetaData rsmd = rs.getMetaData();
                        // Retrieve a ResultSetMetaData object from
                        // the ResultSet that contains the procedure 
                        // information, so you know how many columns
                        // to retrieve, and the name of each column.
while (rs.next ()) {
  System.out.println ("----------- Row " + rowcount++ + " -----------");
                        // Retrieve each row in the ResultSet
  for (int i = 1; i <= rsmd.getColumnCount (); i++) {
    System.out.println (i + ": " + rsmd.getColumnName (i) + " = " + 
      rs.getString (i));
  }
}

The values that are returned are similar to these:

----------- Row 1 -----------
1: PROCEDURE_CAT = null
2: PROCEDURE_SCHEM = DBADMIN
3: ROUTINENAME = PROC1
4: NUM_INPUT_PARAMS = 0
5: NUM_OUTPUT_PARAMS = 0
6: RESULT_SETS = 0
7: REMARKS = null
8: PROCEDURE_TYPE = 1
9: SPECIFICNAME = SQL090504144847100
10: MODULENAME = MYMOD1
----------- Row 2 -----------
1: PROCEDURE_CAT = null
2: PROCEDURE_SCHEM = DBADMIN
3: ROUTINENAME = PROC1
4: NUM_INPUT_PARAMS = 0
5: NUM_OUTPUT_PARAMS = 0
6: RESULT_SETS = 0
7: REMARKS = null
8: PROCEDURE_TYPE = 1
9: SPECIFICNAME = SQL090504144847800
10: MODULENAME = MYMOD2