SQL stored procedure debug limitations (SQL Stored Procedure Debugger)

General

Importing or deleting database in Data Definition view can cause loss of set breakpoints: If you debug an SQL stored procedure before you import the database to a folder in the Data Definition view and then import the database, any line breakpoints you have created will be lost. Once you have imported the database, the debugger will use that folder to view the source. If you delete the imported database information, you will also lose the breakpoint information the next time you try to debug a SQL stored procedure. This will not restore breakpoints lost when the database was first imported.
We recommend that you import the database before debugging a stored procedure to avoid this problem.
Debugging Java stored procedures is not supported: The editor allows you to add breakpoints to the source code of a Java stored procedure. However, these breakpoints are ignored because the debugging of Java stored procedures is not yet supported.
Delimited stored procedure names: the SQL stored procedure debugger provides limited support for stored procedures with delimited schema or procedure names. Such procedures must be launched from the Launch Configuration dialog and not from the context menu in the Data Definition view.
Support for having more than one active SQL stored procedure debugger session open at the same time: In Version 5.0 of this product, you could not have more than one active SQL stored procedure debugger session open at one time. This restriction no longer applies in Versions 5.0.1 or greater of this product.

Linux limitation

When you are debugging an SQL Stored Procedure on a local 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). This procedure will allow the debugger to 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 may be used.
Steps 2 to 7 below 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, for example, db2set db2comm=tcpip,appc.

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 below, 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 below.
Catalog the loopback node by entering the following command:
db2 catalog tcpip node <nodename> remote <hostname> 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).
- <hostname> is the name of the machine on which DB2 is installed. The hostname 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 commands given below to generate sample output if you wish 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 wish to debug a stored procedure.

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:

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:

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:

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:

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 sample output below):

db2 connect to wasloop
db2 connect to was

where db2 connect to wasloop should print the connection information and db2 connect to was should 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 may 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 datasource configuration.
If you want to drop the database, do the following:
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
```