![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
©2002 International Business Machines Corporation. All rights reserved.
The DB2® SQL Debugger is the latest addition to the application development tool suite that was shipped with DB2 V7.1 FixPack3 (otherwise known as DB2 V7.2). The debugger gives you the ability to perform source-level debugging of SQL code. The DB2 Stored Procedure Builder includes a client front end to the SQL Debugger, which allows you to remotely debug server-side SQL stored procedures. The SQL Debugger is made up of two components: the SQL Debugger server and the SQL Debugger client. The SQL Debugger server is the back end interface of the debugger. It forwards the debug session execution state to the debugger front end (the client interface), and responds to the commands that the front end issues. The SQL Debugger client is the user interface to the backend debugger. It presents the debug session state, including all of the debugger views (source code, variables, call stack, and breakpoints), and it forwards user commands to the debugger back end.
DB2's Stored Procedure Builder includes one example of such a debugger client. One of the design goals of the DB2 SQL Debugger is to make the server implementation independent of the client implementation so as to allow for third party tools developers to easily write their own interface to the debugger. This article outlines a recommended debugger client model.
Debugger client user interface
Like any typical source level debugger, the SQL debugger user interface is made up of the following views:
- Editor View showing the SQL code of the stored procedures
- Breakpoints View showing the list of break points currently set
- Call-Stack View showing the list of nested stored procedures
- Variables View showing the list of defined variables
- Status Bar showing the occurrence of SQL exceptions
Although the SQL Debugger was designed in such a way as to allow for the server implementation to be independent of the actual client implementation, a generic extensible client framework can be outlined to provide the required functionality of the debugger.
The debugger data model is a collection of type definitions required to describe the various debugger objects. This definition is required to allow the debugger client to render the debugger status onto the client user interface and to allow the user to control the debug session, such as add breakpoints or to modify variable values.
The debugger data model is made up of the following objects:
- Routine - This object represents a single SQL stored procedure.
- Variable - This object represents a variable that is defined inside of the routine body or is a parameter to the routine.
- Breakpoint - This is the base object representing either a source line breakpoint or a variable change break point.
- LineBreakpoint - This object represents a source code breakpoint.
- VarBreakpoint - This object represents a variable change event breakpoint.
The debugger routine definition represents an SQL stored procedure object. Apart from its object properties, this object has a list of zero or more variable objects and zero or more breakpoint objects.
The routine properties are described below.
Routine RID The RID is the routine ID that uniquely identifies an SQL stored procedure to the DB2 server. This ID is assigned by the debugger server, and must be initially set to null.
Debugger clients typically maintain a list of SQL stored procedures that the user may be interested in debugging. This list is retrieved from the DB2 system catalog, namely SYSCAT.PROCEDURES. Information that may be extracted from the catalog table includes the specific name, the schema name, the procedure name, the fenced flag, and the procedure source code.
During a debug session, the debugger server sends status reports describing the state of the debug session. Whenever an SQL stored procedure is entered, be it the stored procedure being debugged or a nested stored procedure, the routine ID, name, schema, and specific names are reported to the client. See Debugger Communication Protocol for more details.
Routine Schema The Schema name is the DB2 schema identifier. See the SQL Reference for more information about this field. It is used for display only.
Routine Specific The Specific name is the DB2 unique identifier for an SQL stored procedure. See the SQL Reference for more information about this field. This is field is used to retrieve information about a given stored procedure from the system catalogues.
Routine Name The Name is the actual name of the SQL stored procedure. See the SQL Reference for more information about this field. It is used for display only.
Routine IsFenced The IsFenced is a flag that indicates if an SQL stored procedure is fenced (none trusted) or unfenced (trusted). This field is retrieved from the system catalogues and is used by the client logic. See Debugger Client Architecture for more details.
Routine Code The Code field is the source code for an SQL stored procedure. This field is retrieved from the system catalogs and is used by the client logic. See Debugger Client Architecture for more details. The data in this field should be displayed in the source view pane of the client debugger user interface.
The debugger variable definition represents an SQL stored procedure variable object. A procedure variable is either a variable that is declared inside of the stored procedure body, or it is a variable that is a parameter to the stored procedure.
The routine variables should be shown in the variables view pane of the client debugger user interface.
The debugger server sends variable definitions to the debugger client as they are declared during the execution of the stored procedure being debugged. The debugger server also sends variable value updates to the debugger client whenever the variable value is modified.
The variable properties are described below.
- Variable VID
- The VID is the variable ID that uniquely
identifies an SQL stored procedure variable to
the debugger for a given stored procedure. The
debugger server generates this ID every time a
stored procedure is called and the variable is
declared. The ID is only unique within the scope
of a single stored procedure.
Although VIDs are assigned by the debugger server and may be modified by the debugger client, a simple client implementation should not modify these IDs and should discard the list of routine variables at the end of the debug session, including the list of variable breakpoints. This point will be discussed in detail later on. - Variable Scope
- The variable scope is the block at which the
variable was declared. This field in conjunction
with the variable name is required to uniquely
identify a variable, as multiple variables having
the same variable name may be declared in
different blocks of code.
For DB2 V7.2, this is simply the line number at which the variable was declared. This field is used for display only. - Variable Name
- This is the declaration name for a routine variable. This field is used for display only.
- Variable Type Name
- The variable type name is a generated field
based on the Type, Size, and Scale fields. This
field is used for display only.
The following table describes the various types and the expected generated type name:
Table 1. Variable types and generated type names
| Type | Type Constant | Type Name |
|---|---|---|
| 0 | PD_VTYPE_SMALLINT | SMALL INT |
| 1 | PD_VTYPE_INT | INTEGER |
| 2 | PD_VTYPE_LARGEINT | LARGE INT |
| 3 | PD_VTYPE_FLOAT | FLOAT |
| 4 | PD_VTYPE_DOUBLE | DOUBLE |
| 5 | PD_VTYPE_TCHAR | CHAR(Size) |
| 6 | PD_VTYPE_BCHAR | CHAR(Size) FOR BIT DATA |
| 7 | PD_VTYPE_TSQLCHAR | VARCHAR(Size) |
| 8 | PD_VTYPE_BSQLCHAR | VARCHAR(Size) FOR BIT DATA |
| 9 | PD_VTYPE_TSQLLOB | CLOB(Size) |
| 10 | PD_VTYPE_BSQLLOB | BLOB(Size) |
| 11 | PD_VTYPE_DATE | DATE |
| 12 | PD_VTYPE_TIME | TIME |
| 13 | PD_VTYPE_TIMESTAMP | TIMESTAMP |
| 14 | PD_VTYPE_GRAPHIC | GRAPHIC(Size) |
| 15 | PD_VTYPE_SQLDBCHAR | VARGRAPHIC(Size) |
| 16 | PD_VTYPE_DBCLOB | DBCLOB(Size) |
| 17 | PD_VTYPE_DECIMAL | DECIMAL(Size, Scale) |
- Variable Type
- This is the integer value of the variable type. This filed is used to generate the TypeName above.
- Variable Size
- This is the size of the variable. This field is used to generate the TypeName above. For character arrays, it is also used to limit the size of the user-modified variable value.
- Variable Scale
- This is the scale value of a decimal variable. This field is used to generate the TypeName above.
- Variable IsBitData
- This field is derived from the variable type. It is set to TRUE if the variable type is one of PD_VTYPE_BCHAR, PD_VTYPE_BSQLCHAR, or PD_VTYPE_BSQLLOB. This flag is used to determine which value field to access (Value or BitValue).
- Variable DataFrom
- This field is typically set to zero. It is only used for large variable types where only a specific value range is sent to the client. By default, only the first 80 bytes of a character array or binary bytes array are sent. Users may elect to request a different value range, say from offset 1000 to 1500. In this case, the DataFrom variable is set to 1000. This field is used for display only.
- Variable DataSize
- This field is used to indicate the size of the variable value. It is used in conjunction with the DataFrom field. This field is used for display only. For bit data, this would be the size of the bytes array.
- Variable Value
- For variables that are not For Bit Data, the Value field is the textual representation of the variable's value.
- Variable BitValue
- For variables that are For Bit Data, this field is the bytes array representing the variable's value. The size of this array is stored in the DataSize field.
- Variable IsInScope
- This flag is used to indicate whether a variable is in scope or out of scope. Variables are in scope when the current execution line is in the block where the variable was declared or in a nested block. The variable goes out of scope when the current execution line is outside the block where it was declared in.
The debugger breakpoint definition represents the base definition of a debugger break point. Both source line breakpoint and variable value change event breakpoint inherit from this object definition. At any given point in time, a breakpoint is either enabled or disabled.
The routine list of breakpoints should be shown in the breakpoints view pane of the client debugger user interface.
Breakpoints are created by the user through the debugger client and are forwarded to the server during an active debug session. When breakpoints are enabled, the server side execution of a stored procedure will break (pause) when the enabled breakpoint is encountered.
The breakpoint properties are described below.
- Breakpoint BID
- The break point BID is the unique numeric identifier for each break point. The uniqueness is only required within the context of one stored procedure. The debugger client creates the breakpoint objects and assigns the BID to these objects before sending the add breakpoints command to the debugger server.
- Breakpoint IsEnabled
- This flag is used to indicate whether a given breakpoint is currently enabled or not. This state determines the icon type used to represent the breakpoint as well as whether or not the server execution of the stored procedure would break at that given line / variable change event or not.
- Breakpoint Type
- This field is used to differentiate between a line breakpoint and a variable value change event breakpoint. A source line breakpoint is represented by value of zero. A variable value change event breakpoint is represented by a value of one.
The debugger LineBreakpoint definition represents the definition of a stored procedure source line debugger breakpoint.
Line breakpoints are created by the user using the source code view pane of the debugger client user interface and are forwarded to the server during an active debug session. The server side execution of a stored procedure breaks (pauses) when an enabled breakpoint is encountered.
The LineBreakpoint definition inherits from the generic breakpoint definition. The remaining LineBreakpoint properties are described below.
- Breakpoint Line
- The breakpoint line is the source code line number at which the breakpoint is placed. The debugger pauses the execution of the stored procedure when this line is reached.
The debugger VarBreakpoint definition represents the definition of a stored procedure variable value change event debugger breakpoint. A variable breakpoint references a particular stored procedure variable.
Variable breakpoints are created by the user using the variables view pane of the debugger client user interface and are forwarded to the server during an active debug session. The server side execution of a stored procedure breaks (pauses) when a variable value having an enabled breakpoint changes.
The VarBreakpoint definition inherits from the generic breakpoint definition. The remaining VarBreakpoint properties are described below.
- Breakpoint VID
- The breakpoint VID is the variable identifier for which the breakpoint is activated when that variable's value changes. The debugger pauses the execution of the stored procedure when the breakpoint is activated by the variable value change event.
The debugger views model is a collection of type definitions required to render the various debugger views to present the debugger status. This definition is required to allow the debugger client to render the debugger status onto the client user interface and to allow the user to control the debug session, such as add break points or modify variable values.
The debugger views model is broken down into the definitions required to represent the various views:
- Routines View - This is the collection of routines that are requested by the user and are retrieved from the DB2 database catalogs.
- Source View - This is the editor pane that displays the SQL source code for the currently selected routine.
- Breakpoints View - This is the current routine's list of break points (both variables and lines breakpoints).
- Variables View - This is the current routine's list of variables, enumerating the variable names, types, and values.
- Call Stack View - This is the current debugger session call stack, displaying the list of nested stored procedure calls if any.
The debugger routines view displays the list of, possibly filtered, routines. This view is typically rendered as a list having the following columns:
Table 2. Columns of the debugger routines view
| Column Name | Description |
|---|---|
| RID (hidden) | Associates an entry with the routine model object |
| Specific (hidden) | Identifies the stored procedure to DB2 |
| Schema | The schema name for the stored procedure |
| Name | The name of the SQL procedure |
| Parameters (optional) | The parameter signature of the stored procedure |
| Fenced | Yes/No value to indicate fenced/unfenced |
The debugger routines view displays the source code for the currently selected routine. This view is typically made up of these columns:
Table 3. Columns of the debugger source view
| Column Name | Description |
|---|---|
| RID (hidden) | Associates an entry with the routine model object |
| BP Status | Enabled/disabled line breakpoint status indicator |
| Code | The stored procedure SQL source code |
Apart from these columns, the source view also needs to highlight the current execution line as well as the current cursor location.
The stored procedure source code is retrieved from the server catalog and is stored in the routine's model object. The source should only be retrieved on demand. Users should be able to add, remove, or toggle breakpoints at any time regardless whether or not a debug session has been initiated.
The debugger breakpoints view reflects the list of the current routine break points as outlined in the Debugger Client Model section earlier. This view is typically rendered as a list having the following columns:
Table 4. Columns of the debugger breakpoints view
| Column Name | Description |
|---|---|
| BID (hidden) | Associates an entry with the routine model object |
| BP Status | Enabled/disabled break point status indicator |
| Description | This field will depend on
the break point type: Line BP displays "at line X" Variable BP displays "for variable name.scope" |
The content of this list, and hence the routine's list of break points model, is updated through user actions (add, remove, or toggle between enabled and disabled). The update will translate into routine's model update and a commands report to be sent to the debugger server.
The debugger variables view reflects the list of the current routine variables as outlined in Debugger Client Model[production, code as intradocument link]. This view is typically rendered as a list having the following columns:
Table 5. Columns of the debugger variables view
| Column Name | Description |
|---|---|
| VID (hidden) | Associates an entry with the variable model object |
| BP Status | Variable value change breakpoint status indicator |
| Name.Scope | The name and the declare line of a variable |
| Type | The generated type name of a variable |
| Value | The current text representation of a variable value |
The content of this view model is an exact replica of the routine's list of variables. The one deviation from the list is the fact that those variables whose IsInScope flag is FALSE should be removed from the view's list.
The debugger server via the debugger status report updates the model variables data.
The debugger call stack view displays the current debug session call stack. More than one entry will by displayed when debugging nested procedure calls. This view is typically rendered as a list having the following columns:
Table 6. Columns of the debugger call stack view
| Column Name | Description |
|---|---|
| RID (hidden) | Associates an entry with the routine model object |
| Schema.Name | The schema and procedure name |
| Line | The current execution line for the stored procedure |
The debugger server as part of the debugger status report provides the model values for this view. This information is only sent if the call stack changes due to a stored procedure nested call or call return.
Debugger communication protocol
The data exchanged between the client and the server uses XML as the data formatting mechanism. XML allows the SQL debugger interface to be extensible and flexible, and it helps reduce the actual number of router stored procedures that are required for the debugger server interface.
The debugger communicates using two data flows: a server report flow and a client commands flow. Both of these flows use two streams of data: an XML document stream with textual data, and a binary stream with binary data.
These data streams with a description of the communication protocol will be covered in a follow-on article.
This article will describe the actual communication protocol covering both debugger commands and debugger status reports.
DB2's integrated SQL debugger provides an open interface that allows for third party tools developers to provide client front-end to the DB2 SQL debugger back-end that is tightly integrated into their own toolset. This article outlines a recommended model representation of the debugger client objects and the debugger client views.
IBM encourages others to create SQL Debugger Client front ends in their own applications to interface with the SQL Debugger Server. It is important to note, however, that other aspects of the SQL Debugger Architecture are the subjects of pending patent applications in the United States Patent Office, and possibly in other countries. Please contact an IBM Licensing Representative should you wish to license other aspects of the architecture disclosed.
IBM and DB2 are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
IBM copyright and trademark information
Abdul H. Al-Azzawe is a senior software engineer at the IBM Silicon Valley Laboratory in San Jose and an IBM veteran since 1990. He is the lead architect for the next generation application development tooling for DB2. Prior to his current assignment, Abdul was a member of the core DB2 engine development team at the Toronto Lab. Abdul is the chief architect of the DB2 SQL debugger technology.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
