Debugging SPL procedures with IBM Database Add-Ins for Visual Studio

You can use IBM Database Add-Ins for Visual Studio to debug Informix® SPL procedures.

You can debug SPL procedures as they run on the Informix server. You can step through your code, set line or variable breakpoints, view variable values, change variable values, view call stack information for nested procedures, and switch between different procedures on the call stack. By stepping through your code while running the procedure in debug mode and viewing the results, you can discover problems with your procedure and make the necessary changes.

To begin an SPL Routine debugging session in IBM Database Add-Ins for Visual Studio (IDAIVS), follow these steps:

Starting up and configuring the Informix database server instance

To configure the Informix database server instance so that SPL routine debugging is enabled, follow these steps:

  1. Install the Informix database server product.
  2. Configure and bring up the Informix database server with entries in the onconfig and sqlhosts files to support the DRDA communications protocol:
    • onconfig:
      DBSERVERNAME   ids_spldb
    • sqlhosts: 
      ids_spldb   drsoctcp   ids_server_machine_name   port_number
    Note: SPL Routine Debugger support for Informix connects the server to the client via the Informix .NET Provider, and requires a connection with theInformix DRDA protocol.
  3. You must provide an sbspace where the database server can store XML messages that IBM Database Add-Ins for Visual Studio sends to the server. The setting of the SBSPACENAME configuration parameter specifies the name of the system default sbspace. You can create a default sbspace on the database server by using the onspaces utility with the -Df "LOGGING=ON" option. This sbspace is a requirement for debugging SPL on IBM Database Add-Ins for Visual Studio.
  4. If this is a fresh server instance, create a new database using the CREATE DATABASE statement. You will later use this database to create a Database connection in IBM Database Add-Ins for Visual Studio to deploy and debug your SPL routines.

Starting up the routine debugger session manager

To start the session manager, follow these steps:

  1. To start the session manager manually, use the following command to export the CLASSPATH environment variable setting:
    export CLASSPATH=${INFORMIXDIR}/bin/db2dbgm.jar:$CLASSPATH
  2. Using Java™ 1.5.0 or above and ensuring it is in the PATH environment variable, run the following command, specifying your port number that the session manager will use and a pathname for the session manager log file:
    java com.ibm.db2.psmd.mgr.Daemon -port port_num -log sess_mgr_log_path
Note: The session manager must start on a machine on which the Informix instance is running. This is a requirement for IBM Database Add-Ins for Visual Studio to connect to the session manager.

Establishing the Informix database server connection

The Add Connection dialog can be accessed from the action (right-click) pop-up menu on the “Data Connections” node in the Server Explorer by selecting the “Add Connection…” menu item to create a new connection. The Data source field should display "IBM DB2, Informix and U2 Servers (IBM DB2, Informix, and U2 Data Provider."

  1. In the field immediately below the instruction "1. Select or enter server name:" enter the name of the Informix server instance.
  2. In the field immediately below the instruction "2. Enter information to log on to the server:" enter your user id as the User ID: field, and in the Password: field enter the password for the informix account on the server.
  3. In the field immediately below the instruction "3. Select or enter a database name:" enter the name of the database where the SPL procedure that you intend to debug is stored.
  4. Click the Test Connection button to test the connection with the Informix server instance.
  5. If the Test connection succeeded dialog appears, you can click the OK button. You can dismiss the Add Connection dialog, because you have created a connection between the Informix server and Visual Studio.

Setting up the session manager

Before you can start a debugging session, you need to specify the SPL routine to debug, and a database server connection and port number for the session manager.

  1. From the “Data Connections” node in the Server Explorer, click on a database of the Informix server to which you are connected.
  2. Expand the Procedures heading to display the names of all of the stored procedures in the database.
  3. Click on the name of the SPL routine that you would like to debug.
  4. From the Tools tab, select the "Options . . . " menu near the bottom of the Tools list.
  5. From the Options dialog, expand the IBM Database Tools item and click on the General item to display a list of categories.
  6. Use the slider control at the right of the General options list to display the Session Manager Connection and Session Manager Port number.
  7. Click OK to accept these values for the session manager configuration.

Refer to the IBM Database Add-Ins for Visual Studio documentation for details on how to use the debugging capability of Visual Studio with SPL routines.

Debugging Informix SPL procedures

These are the steps for debugging an SPL procedure using the IBM Database Add-Ins for Visual Studio:
  1. Enable debugging for the procedure:
    1. In the Server Explorer under your data connection, right-click the procedure that you want to debug, and then click Open Definition on the shortcut menu.
    2. In the Procedure view of the IBM Procedure Designer, select ALLOW in the Debug mode list.
    3. Save the procedure, but leave the Designer open.
  2. Set line breakpoints in the IBM Procedure Designer.
    1. If the procedure is not open in the Designer, in the Server Explorer under the data connection, right-click the procedure and then click Open Definition on the shortcut menu.
    2. In the SQL Body section of the Procedure view in the Designer, set line breakpoints. In
    3. To set properties for a breakpoint, right-click the breakpoint in the left margin, select Location, Filter, or When Hit on the shortcut menu, and then specify the necessary information in the window that opens.
  3. Start running the procedure in debug mode.
    • If the procedure is open in the IBM Procedure Designer, click Step Into on the IBM Procedure Designer toolbar.
    • If the procedure is not open in the Designer, right-click the procedure in the Server Explorer, and then click Step Into on the shortcut menu.
  4. Run each procedure in debug mode, and use either of the following methods:
    • Set variable breakpoints. In the SQL body, right-click a variable name, click Breakpoints on the shortcut menu, and then select Insert Variable Breakpoints.
    • Modify variable breakpoint values.
  5. Continue debugging the Informix SPL procedure until the procedure returns the expected results.

Debugging Informix SPL procedures in CLR applications

While you develop Windows or ASP.NET applications in C# and Visual Basic, you can debug the Common Language Runtime (CLR) applications with IBM Database Add-Ins for Visual Studio. If the applications access Informix database server instances, you can debug the SPL procedures that are called from the applications while you debug the applications.

The IBM Unified Debugger, which is the feature that you use to debug SPL procedures in C# and Visual Basic applications, is available for Informix, starting with version 11.70.

The IBM Unified Debugger debugs SPL procedures through the IBM Procedure Designer. If a procedure definition is open in the Designer when you start debugging an application, as the debugger steps into the procedure, that instance of the Designer is activated. If the procedure definition is not open in the Designer, as the debugger steps into the procedure, the debugger opens the procedure definition in a new instance of the Designer.

Prerequisite: To debug SPL procedures in a CLR application, enable IBM SQL debugging in the project that contains the application.

To debug SPL procedures in a CLR application, follow these steps:

  1. For each SPL procedure in the application:
    1. Enable debugging for the procedure:
      1. In the Server Explorer under your data connection, right-click the procedure that you want to debug, and then click Open Definition on the shortcut menu.
      2. In the Procedure view of the IBM Procedure Designer, select ALLOW in the Debug mode list.
      3. Save the procedure, and optionally leave the Designer open to set line breakpoints.
    2. Optional: Set line breakpoints in the IBM Procedure Designer.
      1. In the SQL Body section of the Procedure view in the Designer, set line breakpoints.
      2. To set properties for a breakpoint, right-click the breakpoint in the left margin, select Location, Filter, or When Hit on the shortcut menu, and then specify the necessary information in the window that opens.
      3. Leave the Designer open.
  2. Start debugging the application.

    In the Solution Explorer, right-click the application, select Debug on the shortcut menu, and then select Start new instance.

    Debugging starts and the Debugger Task Status window opens.

    The call stack for each procedure that is being debugged is merged with the call stack for the thread that calls the procedure. You can see where the procedure is called from the C# or Visual Basic code.

  3. Run each procedure in debug mode as it is called.
    • If you closed the IBM Procedure Designer in step 1a.iii, in the new instance of the Designer, set line breakpoints and breakpoint properties for the procedure in the SQL Body section.
    • Set variable breakpoints. In the SQL body, right-click a variable name, click Breakpoints on the shortcut menu, and then select Insert Variable Breakpoints.
    • Modify variable breakpoint values.
  4. To cancel a long-running task, click Cancel in the Debugger Task Status window.

    If you closed the window while you were debugging your application, from the Tools menu, select Show IBM Debugger Task Status to reopen the window.

  5. Continue debugging the application until all the Informix SPL procedures return the expected results.

Enabling SQL debugging in CLR applications

If you want to debug Informix SPL procedures in your C# and Visual Basic Common Language Runtime (CLR) application, you first must enable SQL debugging for the database project. You need to do this process only once for a given project. Enabling SQL debugging for a project sets a project property that persists from session to session in Visual Studio.

When you enable SQL debugging in an application, the IBM Unified Debugger must close and then reopen the database project that contains the application. After the project reopens, you must specify the data connection and, optionally, the port on which to run the debugger session manager. You must also change the settings of some standard Visual Studio project debug properties.

The add-ins, which is 32-bit application, does not support a 64-bit debugging process for console applications. On a 64-bit operating system, the default debug platform is Any CPU, which is a 64-bit debugging process. You must specify a 32-bit debug platform in the project properties.

Prerequisite: To enable SQL debugging in an application, the database project that contains the application must be open in the Solution Explorer.

To enable SQL debugging in an application, follow these steps:
  1. In the Solution Explorer, right-click the database project node, and then select Enable IBM SQL Debugging on the shortcut menu.

    A message is displayed that the project must be closed and reopened.

  2. Click Yes in the message window to confirm the closing and reopening of the project.

    All unsaved changes are saved, the project closes, and then reopens in the Solution Explorer.

  3. In the Solution Explorer, right-click the database project node, and then select Properties on the shortcut menu.

    The Project Designer opens.

  4. In the left box of the Designer, click IBM Unified Debugger.

    The IBM Unified Debugger page is displayed.

  5. Perform one of the following sets of actions:
    • If you have one of the following connections to use for the debugger session manager:
      • Informix Version 12.10 or earlier
      • IBM DB2® for Linux®, UNIX, and Windows, Version 9.1, or later
      • IBM DB2 for z/OS® Version 9.1 or Version 10
      • IBM DB2 for i V5R4 or V6R1
      1. Select Use an existing connection for session manager.
      2. In the list, select the data connection on which to run the debugger session manager.
    • If you are running the debugger session manager manually:
      1. Select Use a new host name for session manager.
      2. Specify a host name for the debugger session manager.
  6. If you are debugging a console application on a 64-bit operating system, in the Platform list, select x86.
  7. Optional: In the Session manager port field, type the port on which to run the debugger session manager.
  8. In the left box of the Project Designer, click Debug.

    The Debug page of the Project Designer is displayed.

  9. Change the following properties on the Debug page:
    • Under Start Options, clear the Use remote machine check box.
    • Under Enable Debuggers, clear all three of the following check boxes:
      • Enable unmanaged code debugging
      • Enable SQL Server debugging
      • Enable the Visual Studio hosting process
  10. Close the Project Designer.