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.

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®.
Run db2dbgm.bat from the command window, and note the IP address and the port number for the session manager.
Start the workbench and modify preferences for the debugger to use the local session manager:

Click Window > Preferences, expand the Run/Debug node, and click DB2 Routine Debugger.
In the Debugger pane, select Use already running session manager.
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.
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:
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 border="1">

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 border="1">

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:


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.


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.

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.


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



Catalog the database as follows. See the following commands to generate sample output if you want to track the effects of each command: 

 db2 catalog db <database name> as <database alias> 

db2 uncatalog db <database name>

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



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



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.


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.

If you want to drop the database, issue the following commands: 

db2 attach to <nodename> user <userid> using <password>


db2 drop db <database name>


for example, 
db2 attach to MYNODE user myid using mypasswd
db2 drop db WAS



          
          
          
          
          
          
          
          
          
          
          
          
          
                      
                                                                      
          
          
          
          
          
          
          
          
          
                    
                              
                    
          
          
          
          
                    
          
                    
          
          
                                                                                    
          
          
          
          
          
          
          
                    
                                        Cross-reference informationProduct Component Platform Version
IBM Data Studio Linux; Windows 2.2, 2.2.0.1, 2.2.0.2, 2.2.1, 2.2.1.1, 3.1, 3.1.1
Optim Development Studio Linux; Windows 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
          
          [{"Product":{"code":"SSSQRS","label":"Optim Development Studio"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"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","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SS62YD","label":"IBM Data Studio"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Stored Procedure and UDF Development","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"2.2;2.2.0.1;2.2.0.2;2.2.1;2.2.1.1;3.1;3.1.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

*Cross-reference information*
Product	Component	Platform	Version
IBM Data Studio		Linux; Windows	2.2, 2.2.0.1, 2.2.0.2, 2.2.1, 2.2.1.1, 3.1, 3.1.1
Optim Development Studio		Linux; Windows	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


      
        
            
            
               
  
        Was this topic helpful?
                    
  
        

        







          


            
            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
Document number: 
321719

                        
            Modified date:
            

            17 June 2018
            
            
                        
  
        UID
                    swg27015066
          

            
          
        
You will be taken to the My Notifications interface where you have been auto-subscribed to Optim Development Studio
You can click the EDIT menu option to configure your subscription experience.
You can also search for and select other IBM products to subscribe to based on your needs.
Manage My Notification Subscriptions

IBM Support

Tips