Routine debug limitations

This page describes limitations that you may encounter when debugging routines and suggested methods for working around those limitations. Please also see the product readme, which may contain additional limitations for this debugger.

General

See also Known problems with the Routine debugger for updates.

Delimited routine names
The Routine debugger provides limited support for routines with delimited schema or routine names. Such routines must be launched from the Debug launch configurations dialog box and not from the context menu in the Data Definition view.
Enabling step actions
When a debug session starts, if the step action buttons (for example, the Step Over button) are not enabled, select the top stack frame to cause the buttons to be enabled.
Session manager
Use the session manager that is included with the product. The session manager can be either on the server, on the network, or on the client. The following steps describe how to configure the session manager on the client, using the session manager that is included with the product. This statement does not apply for DB2® PL/SQL and Oracle configurations.
  1. Open a command window and change to the product installation directory. By default, the product is installed in the C:\Program Files\IBM\SDP70 directory on Windows.
  2. Run db2dbgm.bat from the command window, and note the IP address and the port number for the session manager.
  3. Start the workbench and modify preferences for the debugger to use the local session manager:
    1. Click Window > Preferences, expand the Run/Debug node, and click DB2 Routine Debugger.
    2. n the Debugger pane, select Use already running session manager.
    3. In the Host field, specify the IP address of the machine. You can also obtain the IP address from the command or terminal window where the session manager is running.
    4. In the Port field, specify the port for the local session manager. By default, the port number is 4554. You can also obtain the port number from the command or terminal window where the session manager is running.
Variable names
Variable names that contain double quotes are not supported for debugging routines on DB2 for Linux, Unix, and Windows databases.
Break points
For Optim™ Development Studio Version 2.2.1.1, individual break points cannot be disabled.
Features that are available only on DB2 for Linux, UNIX, and Windows database Version 10.1 Fix Pack 2 or later databases
These debugging features are available only when debugging routines on a DB2 for Linux, UNIX, and Windows database Version 10.1 Fix Pack 2 or later database:
  • Debugging declared routines
  • Debugging triggers
  • Debugging a PL/SQL anonymous blocks
  • Starting the debugger from the SQL and XQuery editor
  • Setting persistent breakpoints in the routine editor
  • Recompiling routines with the debugging option without redeploying the routine
  • Viewing the following types of variables in the Variables view:
    • Global and package variables
    • Associative arrays
    • Array of rows
Array variables
The routine debugger lists only the first 100,000 elements of an array variable in the Variables view.
BLOB data types
If the data type of an output parameter is binary large object (BLOB), the output is returned in uppercase. Because the value that is returned represents a hexadecimal number, this does not cause a problem.
DB2 SYSDEBUG role
To debug routines that are deployed on a DB2 for Linux, UNIX, and Windows Version 10.1 Fix Pack 2 or later database, the database user ID deploying the routine must be a member of the SYSDEBUG role.

SQL and Java routines

Triggers

When debugging triggers in a DB2 database, you can debug only one trigger at a time. Debugging two or more triggers simultaneously is not supported.

PL/SQL and Oracle data types

See PL/SQL and Oracle data types not supported by Routine debugger.

PL/SQL routines

Oracle limitation

When a debugging procedure that has a CURSOR type, and it stops at a breakpoint in the middle of the procedure, if you click Terminate, the entry in the output view sometimes hangs. To work around this limitation, click Resume instead of Terminate.

Informix limitations

The following list describes limitations when debugging stored procedures on an Informix® database:
  • When debugging a stored procedure on an Informix database you must use the built-in debugger or use an already running session manager. The default preference of running the session manager on each connected server is not supported.

    To change the preference for the routine debugger session manager location, select Window > Preferences. In Preferences, choose Run/Debug > Routine Debugger to select the preferences for debugging. Select the database server for the routines you are debugging. In the section, Routine Debug Session Manager Location, select either Use the built-in session manager or Use already running session manager.

  • In the Data Project Explorer view, if you right-click an Informix stored procedure and the Informix database is disconnected, Debug is not enabled. To enable debugging, connect to the database. You can also debug the stored procedure from the Data Source Explorer view.

Linux limitation

When you are debugging a routine on a local DB2 database, it is possible to receive error number SQL1224N:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] SQL1224N A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command. SQLSTATE=55032

This is due to a problem in the Linux kernel (Linux kernel Bugzilla bug #351). The following instructions are a work-around that uses DB2's TCPIP connection method (as a loopback) instead of Call Level Interface (CLI). With this procedure, the debugger will use the same database alias as before:

  1. If a port for remote DB2 clients has not been set up, log on as root and create a TCP/IP port in /etc/services, (for example, db2c_db2inst1 50000/tcp). Alternatively, you can use the Control Center to create a TCP/IP port (by setting the communications properties for the database instance). An existing port for remote DB2 clients can be used.

    Steps 2 to 7 require you log in as the DB2 instance owner.

  2. Configure the database manager to start connection manager for the TCP/IP communication protocol. If you are not sure if this has already been done, issue the following command:
    db2set db2comm

    If the output does not contain the keyword tcpip you need to enter the following command to update the db2comm registry variable to include tcpip:

    db2set db2comm=<existing protocol names>,tcpip

    The db2comm registry variable determines which protocol's connection manager will be enabled when the database manager is started. You can set this variable for multiple communication protocols by separating the keywords with commas.

    You need to re-issue the db2start command in order to start the connection managers for the protocols specified by the db2comm registry parameter. Since we will restart DB2 in step 7, there is no need to now.

    .
  3. Update the SVCENAME database manager configuration parameter with the connection service name defined in /etc/services (step 1).

    To check the current setting of SVCENAME, enter the following command:

    db2 get dbm cfg | grep -i svcename

    If you need to update the setting of SVCENAME, enter the following command:

    db2 update dbm cfg using svcename <connection service name>

    where <connection service name> is case-sensitive and must match the name of the service port that you placed in /etc/services (for example, db2 update dbm cfg using svcename db2c_db2inst1).

    The update of the database manager configuration will not be effective until the next db2start command is issued. We will do this in step 7.

  4. Catalog the loopback node by entering the following command:
    db2 catalog tcpip node <nodename> remote <host name> server <connection service name>

    where,

    • <nodename> is a local alias for the node to be catalogued. This is an arbitrary name on the workstation, used to identify the node (for example, db2 catalog tcpip node mynode remote 127.0.0.1 server db2c_db2inst1).
    • <host name> is the name of the machine on which DB2 is installed. The host name that you specify must be the exact case and name of the machine. If you are not sure what the name of the machine is, check the Control Center.

    To verify that the catalog command worked properly, issue the following command:

    db2 list node directory

    A sample output of this command is (blank lines have been removed for legibility):

    Node Directory
    Number of entries in the directory = 1
    Node 1 entry:
    Node name = MYNODE
    Comment =
    Protocol = TCPIP
    Hostname = 127.0.0.1
    Service name = db2c_db2inst1
  5. Catalog the database as follows. See the following commands to generate sample output if you want to track the effects of each command:
    1. db2 catalog db <database name> as <database alias>
    2. db2 uncatalog db <database name>
    3. db2 catalog db <database alias as <database name> at node <nodename>
    for example,
    db2 catalog db WAS as WASLOOP
    db2 uncatalog db WAS
    db2 catalog db WASLOOP as WAS at node MYNODE

    Notes®:

    • The database alias can be any name you want but it cannot be the same as the database name. The alias must be 8 characters or less.
    • You will receive error number SQL1334N if you did not catalog the database correctly.
    • You need to repeat steps 5a to 5c for every database on which you want to debug a routine.

    Sample output for steps 5a to 5c

    Before step 5a, a local database named WAS has already been created. The command db2 list db directory has output similar to the following message:

    System Database Directory
    Number of entries in the directory = 1
    
    Database 1 entry:
    
    Database alias = WAS
    Database name = WAS
    Local database directory = /home/ctsui
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0

    After step 5a, db2 list db directory has output similar to the following message:

    System Database Directory
    Number of entries in the directory = 2
    
    Database 1 entry:
    
    Database alias = WAS
    Database name = WAS
    Local database directory = /home/ctsui
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0
    
    Database 2 entry:
    
    Database alias = WASLOOP
    Database name = WAS
    Local database directory = /home/ctsui
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0

    After step 5b, db2 list db directory has output similar to the following message:

    System Database Directory
    Number of entries in the directory = 1
    
    Database 1 entry:
    
    Database alias = WASLOOP
    Database name = WAS
    Local database directory = /home/ctsui
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0

    After step 5c, db2 list db directory has output similar to the following message:

    System Database Directory
    Number of entries in the directory = 2
    
    Database 1 entry:
    
    Database alias = WAS
    Database name = WASLOOP
    Node name = MYNODE
    Database release level = 9.00
    Comment =
    Directory entry type = Remote
    Catalog node number = -1
    
    Database 2 entry:
    
    Database alias = WASLOOP
    Database name = WAS
    Local database directory = /home/ctsui
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0

    To verify that the catalog db command worked properly, issue the following two commands (and see the following sample output):

    db2 connect to wasloop
    db2 connect to was

    where db2 connect to wasloop will print the connection information and db2 connect to was will give you SQL1403N.

    Sample output of db2 connect to wasloop:

    Database Connection Information
    System Database Directory
    
    Database server = DB2/6000 6.1.0
    SQL authorization ID = CTSUI
    Local database alias = WASLOOP

    Sample output of db2 connect to was:

    Database Connection Information
    System Database Directory
    
    Database server = DB2/6000 6.1.0
    SQL authorization ID = CTSUI
    Local database alias = WAS
  6. Update the authentication mechanism to Client authentication. Enter the command:
    db2 update dbm cfg using authentication client

    To verify that the command worked properly, display the new setting with the following command:

    db2 get dbm cfg

    Sample output:

    ....
    Database manager authentication     (AUTHENTICATION) = CLIENT
    ....
  7. Restart DB2 to refresh the directory cache. For example,
    db2stop
    db2start

    Note: You might need to use db2stop force to close all active database connections.

  8. For WAS, there is no need to update the admin.config file. For a WebSphere® application, there is no need to change the existing datasource configuration.
  9. If you want to drop the database, issue the following commands:
    1. db2 attach to <nodename> user <userid> using <password>
    2. db2 drop db <database name>
      for example,
      db2 attach to MYNODE user myid using mypasswd
      db2 drop db WAS

Feedback