UDF and SP in IBM RPM

IBM Rational Portfolio Manager (assuming version 7.1.1.2) is using Dynamic Linking Library (DLL) as its implementation for some of the database objects, namely stored procedure (SP) and user defined functions (UDF). More precisely the file name of this dll is IBMRPM.dll and it resides in two locations, i.e. ${DB2_INSTALL_ROOT}\FUNCTION\IBMRPM.dll and ${DB2_INSTALL_ROOT}\FUNCTION\unfenced\IBMRPM.dll.

During RPM installations/migrations, the process will define the linkage between SP/UDF to the appropriate methods in the dll and this piece of information is stored in SYSIBM.SYSROUTINES table's IMPLEMENTATION column.

The problem with IBM RPM installation is that it hardcoded the directory information in IMPLEMENTATION column. This shall causes portability issue when you desire to move the system to another environment where the ${DB2_INSTALL_ROOT} is not consistent with each other. For example, your initial RPM machine might have DB2 at drive C then the latest environment put it in drive E.

In such a setting, after you restored the database successfully into the new environment, you shall get alerts and some errors in the application server's logs specifying database errors with SQLSTATE 42724 REASON CODE 4.

You maybe be tempted to update the path information directly in SYSIBM but the column is only updateable through a proper DROP/CREATE PROCEDURE or ALTER PROCEDURE and in this case the number of objects to change is way too many even if you plan to write a batch script for it.

A shortcut to this problem is to copy the IBMRPM.dll and create the necessary dummy folders that reflect the original path information and paste them there. This works but it's not recommended because it might causes complication next time when doing patching or upgrading of RPM.

The best way to resolve this is to reuse some of the scripts from the installer.

Note: Please do this at your own risk. Backup everything first.


Step 1: Setting Up Environment

To do this, open up an instance of DB2CMD.

Change Directory (CD) to ${RPM_INSTALLER}\Database\DB2\Windows\CSP

If you are using DB2 V9, Please set the following environment variables using:

SET DB2TEMPDIR=${DB2_INSTALL_ROOT}\

Note: Please make sure the above path end with a slash (\)

Then

SET RPMDLLNAME=IBMRPM

You will then login to DB2 by using the user name where the name is the schema name of the objects. For example, DB2ADMIN. If you are not sure what name is that, you can always open up the control center and browse the list of stored procedures and check the schema name column. A common procedure is the SP_LOGON.

db2 connect to MYRPM user db2admin using your_password

Step 2: Recreate SP/UDF

Run the drop_sp.bat by using the following command:

drop_sp.bat > drop_sp.log

Check the drop_sp.log to verify the completion.

Run the create_sp.bat by using the following command:

create_sp.bat > create_sp.log

Check the create_sp.log to verify the completion.

Step 3: Bind packages

Because the dropping of previous SP/UDF invalidated package objects used by RPM, you need to bind them again. Open bindall.bat using notepad or other editor. Copy the for loop command and paste it in a new file. Name the file bindall2.bat. Change the value for QUALIFIER to DB2ADMIN. Save it and run the following command using the previous DB2 Command Window session.

bindall2.bat > bindall2.log

Check the bindall2.log to verify the completion.

You might to recycle your DB2 process to get a fresh start.