Debugging PL/I DB2 LUW Stored Procedures

It is possible to debug PL/I DB2 LUW Stored Procedures but care should be used when doing this. It is not recommended that production DB2 LUW servers be used because of the impact on processing and potential security issues. Using a VM image to debug the stored procedure is one option or using a single user version of the software like DB2 Express-C on a development workstation is another option.

Do the following before attempting to debug stored procedures:

• When you install the DB2 LUW software, do not install the software using local system account as the ID to start all DB2 processes. Use the default setting of DB2ADMIN. Using local system account can cause the CodeWatch debugger to not attach the db2fmp process correctly.
• The external name for the stored procedure definition needs to be in upper case so the definition would look something like this:

  CREATE PROCEDURE GETPRML
   (IN PROCNM CHAR(18) 
   ,IN SCHEMA CHAR(8)  
   ,INOUT OUTCODE INTEGER 
   ,INOUT PARMLST CHAR(26) 
   )
    LANGUAGE COBOL
    DETERMINISTIC
    READS SQL DATA
    NO DBINFO
    FENCED
    NOT THREADSAFE
    EXTERNAL NAME "GETPRML!GETPRML"  
    PARAMETER STYLE GENERAL  
    PROGRAM TYPE SUB
    RESULT SETS 0;

• You should not use the EXTERNAL parameter in the PROC statement of the PL/I program. If you normally do, comment that code out like this:

  PROC(PROCNM, SCHEMA, OUTCODE, PARMLST); /* EXTERNAL('getprml'); */

• Build and link the stored procedure for debugging. The commands would look like this:

  mfplx -sql db2 -optsql db=sample -deb -coffdebug -defext getprml.pli -c –ppsql

  SET LINK=-DEBUG

  cbllink /d /V /K getprml.obj db2api.lib mfplimd.lib systemsql.obj setupdeffiles.obj

• Copy the DLL, STB, and PDB files to the \sqllib\function directory. STB files are created when setting the environment variable LINK=-DEBUG
• Execute the stored procedure so that it gets loaded - for example using the DB2 command to call it.
• Open an Enterprise Developer command prompt (32-bit) as ADMINISTRATOR
• Set the CODEWATCH_SRCPATH and CODEWATCH_STBPATH environment variables to the \sqllib\function directory. For example:

  set CODEWATCH_SRCPATH=C:\Program Files\IBM\SQLLIB\FUNCTION

  set CODEWATCH_STBPATH=C:\Program Files\IBM\SQLLIB\FUNCTION

• Find the PID for the DB2 LUW process that loaded the stored procedure. Look for db2fmp.exe.

  It is easier to find if you have 64-bit version of DB2 LUW installed since it is a 32-bit version.

  One tool that you could use is PSLIST which is available from www.sysinternals.com under PsTools download. The command to execute is:

  PSLIST DB2FMP

  The results would look something like this using 64-bit DB2 LUW:

  Name         Pid Pri Thd  Hnd   Priv       CPU Time    Elapsed Time
  db2fmp64    2240   8   9  211  16660    0:00:00.655     0:00:50.512
  db2fmp64    2240   8   9  211  16660    0:00:00.655     0:00:50.512
  db2fmp64    2056   8   4  143  10888    0:00:00.046     0:00:15.241
  db2fmp      2528   8   3  117  12172    0:00:00.140     0:00:15.163

  If more than one PID for db2fmp is listed, try them one at a time to determine which one is the stored procedure you want to debug. You could also use CTF trace to determine which PID is being used.

• In the Enterprise Developer 32-bit command prompt, enter java -jar <installation path>/cw_java.jar -pid nnnn where nnnn is the PID number. For example, using the last PID displayed above, the command would be:

  java –jar <installation path>/cw_java.jar –pid 2528

  The CodeWatch window appears.

• Select Shared Library from the Actions menu, enter the .dll name of the stored procedure you want to debug and click OK. For example:

  

• Load the source symbols by typing the proc name of the stored procedure in the Env dropdown. For example:

  

• Set a break point on the line where you want to stop the debugger. To do this, double click the line number. For example, double clicking line 15 will stop execution on line 15.

  

• In the CodeWatch window, click Continue to wait until the stored procedure is called again.
• Call the stored procedure again in another process.
• Switch back to the CodeWatch debugger window. If attached to the correct process, the debugger stops at the line set in the breakpoint.

  You can now perform debugging tasks including evaluating variables and stepping through code. When you return, the results of the stored procedure call are available to the calling process.