Known problems with the Routine debugger

Release notes


Abstract

The following items are known problems for the Routine debugger.

Content

This topic describes limitations that you may encounter when debugging routines and suggested methods for working around those limitations.

General

  • 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. In 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.

SQL and Java routines

  • Java™ stored procedures that are dragged and dropped from the Database Explorer to a data development project cannot be debugged until you open them in the routine editor to force the required classpath to be set up correctly.
  • You cannot debug a Java stored procedure if it is called from a native SQL stored procedure on DB2 for z/OS®.
  • When working offline, you cannot debug stored procedures, although the context menu is enabled.
  • The debugger does not stop at a breakpoint if it is not positioned at the first token of an executable statement, such as SET. In addition, it does not stop on DECLARE CONTINUE, CLOSE CURSOR, or ROLLBACK.
  • The following scenario results in a stored procedure that cannot be debugged: Import a stored procedure that was created for DB2 for z/OS prior to Version 9, without a version statement, then deploy it to a DB2 for z/OS Version 9 database. In this case you cannot debug the stored procedure. To work around this problem, copy the stored procedure from the Database Explorer to the project and then debug it.
  • Watch expressions are only supported for dynamic Java stored procedures. They are not supported for SQL and SQLJ stored procedures.
  • If you are debugging a Java stored procedure and you select a Terminate action, it might take several minutes for the debug session to fully terminate. New debug sessions that are started during this time may behave erratically.
  • When you are debugging a Java stored procedure that is called from another SQL or Java stored procedure, the Step Return button is disabled. To work around this problem, you can add a breakpoint at the next line of the calling stored procedure and then click Resume to get back to the calling stored procedure.
  • When a Java stored procedure calls another stored procedure and then you step back into the Java stored procedure, you might stop in some intermediate Java code. To work around this problem, use the Step Return button to return to the Java stored procedure stack frame.
  • When you are debugging a Java stored procedure, if you use the Change Value action to modify a variable that has an empty string value, the OK button in the edit dialog might not become enabled. To enable the button, select Input an evaluation, set the value to a non-empty string (for example, 'a'), and then select Input literal text. The OK button will then be available.
  • If you do not see local variables when you are debugging a Java stored procedure, the stored procedure might have been deployed without the -g compiler option. Ensure that you specify the -g compiler option when you deploy Java stored procedures.
  • When you are debugging an SQLJ stored procedure that is running on DB2 UDB for iSeries® V5 R4, the current line that is being executed will not correspond to the indicated SQLJ source line displayed in the Debug view unless you have applied an iSeries PTF that updates the linemap to correspond to the SQLJ source instead of the Java source.
  • If you start a debug session for a Java stored procedure and add breakpoints, then disable the breakpoints, the breakpoints are still enabled. To work around this issue, when you start a new debug session, you should first remove all of the old breakpoints and then add new breakpoints.
  • If you click step over while debugging a Java stored procedure against DB2® for Linux, UNIX, and Windows Version 97 Fix Pack 3 and DB2 for Linux, UNIX, and Windows Version 97 Fix Pack 4, the debugger might perform a step into instead of a step over. As a result, the debugger displays a source not found message. Performing a step into is caused by the JDK that is shipped with DB2 V97 FP3, and DB2 V97 FP4.
    To avoid this problem, update the database manager configuration to specify a different JDK by running the following command from a DB2 command window:
      db2 update dbm cfg using JDK_PATH new-JDK-path
  • Java stored procedures cannot be debugged on DB2 v97 Fix Pack 4 and later fix packs when DB2 v97 is running on a Linux 64-bit computer. Perform the following steps to ensure the DB2LIBPATH is set correctly:
    1. At a command prompt, type:
      db2set DB2LIBPATH

      If the path /home/db2inst1/sqllib/java/jdk64/jre/lib/amd64, is not listed enter this command at the command prompt to set the DB2LIBPATH:
      db2set DB2LIBPATH=/home/db2inst1/sqllib/java/jdk64/jre/lib/amd64
    2. Stop and start the database instance.  At the command line enter this command to stop the instance:
      db2stop force

      Then enter this command to start the instance:
      db2start
    3. Verify the change was successful by entering this command at the command line:
      db2set DB2LIBPATH

PL/SQL and Oracle data types

The following tables show the PL/SQL and Oracle data types that are not supported by the Routine debugger, as well as the equivalent DB2® SQL data type. All other data types are supported for display and updated in the Routine debugger.

Table 1. PL/SQL data types not supported by Routine debugger
PL/SQL data type DB2 SQL data type Type of support
BLOB BLOB (4096) Display only.
BLOB(n) BLOB (n) n=1-2147483647 Display only. First 32K bytes only.
LONG RAW BLOB(32760) Display only.
NCLOB DBCLOB(2048) Display only.
NCLOB(n) DBCLOB(2000) Display only.
RAW BLOB(32767) RAW(n) BLOB n=1-32767 Display only.
CURSOR, SYS_REFCURSOR, REFCURSOR, REF CURSOR Display only. not supported for PL/SQL user-defined functions are supported only for OUT parameters
XMLTYPE XML Not supported. Not supported.

The ROW and ARRAY (scalar array) local variables are supported in PL/SQL routines by the Routine debugger.

Additional data type limitations for PL/SQL routines

  • LOB element (LOB variable, LOB field within a row variable, and LOB element within an array variable) cannot be updated.
  • Only the first 32k bytes of LOB element's value are displayed.
  • An ARRAY of ROW types is not supported.
  • Elements in the SCALAR ARRAY variable cannot be updated.
Table 2. Oracle data types not supported by Routine debugger
Oracle data type Type of support
BFILE (4) Not supported.
BLOB (4) Not supported.
BOOLEAN, PL/SQL BOOLEAN For display only.
CLOB (4) Not supported.
INTERVAL Not supported.
INTERVAL YEAR TO MONTH Not supported.
INTERVAL DAY TO SECOND Not supported.
LONG (5) Not supported.
NCHAR, NATIONAL CHARACTER, NATIONAL CHAR Cannot be displayed.
NCLOB (4) Does not display real value.
NVARCHAR2, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NCHAR VARYING Not displayed.
NATURAL Not supported.
NATURALN Can be displayed and variables updated locally
POSITIVE Not supported by DB2
POSITIVEN Can be displayed and variables updated locally
SIMPLE_INTEGER Can be displayed and variables updated locally
TABLE Not supported.
UROWID Not supported.
VARRAY Not supported.
XMLTYPE Not supported.


PL/SQL routines

  • Routine debugger supports stand alone PL/SQL procedures, PL/SQL procedures within package, and PL/SQL functions within package.
  • You must start the PL/SQL Routine debugger from the Data Project Explorer.
  • The PL/SQL Routine debugger does not support step into PL/SQL function within a package.
  • The PL/SQL Routine debugger does not support global variables. The Variable view does not show global variables that are used within a PL/SQL stored procedure, a function, or a PL/SQL package.

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 data source 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
    

Cross reference information
Segment Product Component Platform Version Edition
Information Management Data Studio Stored Procedure and UDF Development Linux, Windows 2.2, 2.2.0.1, 2.2.0.2, 2.2.1, 2.2.1.1, 3.1, 3.1.1

Rate this page:

(0 users)Average rating

Document information


More support for:

Optim Development Studio

Software version:

2.1, 2.1.0.1, 2.2, 2.2.0.1, 2.2.0.2, 2.2.0.3, 2.2.1, 2.2.1.1

Operating system(s):

Linux, Windows

Reference #:

7015066

Modified date:

2012-04-27

Translate my page

Machine Translation

Content navigation