SQL Compiler Query Debugger

NOTE: This page is a work in progress, and the information described here is subject to change.

Introduction

The SQL Compiler Query Debugger visualizes various stages of query compilation, optimization, and code generation. It requires open source Qt GUI toolkit for GUI front-end and a VNC running server on the Linux/CentOS machine.

The Debugger is in the form of a dynamic-link library (libSqlCompilerDebugger.so), which is loaded into sqlci process at run time.

Building the SQL Compiler Query Debugger

The GUI Debugger is integrated into Trafodion’s build system, which means it is built along with Trafodion, and both in debug or release version. The source code is in sql/SqlCompilerDebugger. It is also an optional part, it won't get built if you leave environment variable QT_TOOLKIT empty in sqenvcom.sh. If you want to build it, you need Qt 4.8.5, but be aware that it is available under LGPL license, which is not compatible with ASF software distributions.

sudo yum install qt-devel
export QT_TOOLKIT=<a path such that $QT_TOOLKIT/bin/qmake can be executed>
cd $TRAF_HOME/../sql/SqlCompilerDebugger
chmod +x mk.sh
./mk.sh

Invoking the SQL Compiler Query Debugger

Invoking in a VNC Session

To use the Debugger in a VNC session:

1. Run a VNC server in your linux machine, connect to the machine with a VNC client.
2. In your VNC session, open a terminal, start a sqlci session.
3. In sqlci, prefix the query with the keyword display:

   display select * from table1;the window of Debugger will display.

Invoking in GDB

In gdb session, if you want to see the tree structure of an ItemExpr or RelExpr, you can call displayTree() of the node(the node object should be an ExprNode, e.g. p ((ExprNode*)obj)->displayTree()). this will bring up a window visualizing the tree structure. Clicking "continue" button will close the window and return to your gdb session.

Using the SQL Compiler Query Debugger

Breakpoints Dialog Box

When you launch the Debugger, the Breakpoints dialog box appears first:

This dialog box is to set stop points at important phases for debugging. At any time, you can open the Breakpoints dialog box and change the configuration by clicking on the hand icon  on the toolbar or by selecting Options > Breakpoints… from the menu.

Main Window

After any breakpoint is set, the main window appears. It contains several subwindows to display the query tree, item expressions, properties, and steps for optimizing the query.

Main Window Menus

All functionality of the debugger can be found in main window's menu.

File

The File menu is currently unavailable.

Tools

The Tools menu provides following informations, which are displayed as sub-windows:

Options

Memo

The Memo menu controls process of optimization. For more information, see Memo Viewer.

View

Window

Help

Toolbar

The toolbar displays icons for frequently used functionalities. It appears as follows:

SQL Query Viewer

The SQL Query Viewer, which is the default sub-window inside the parent window, shows relational expression tree of the debugged query. You cannot close that sub-window.

Title of the sub-window indicates the current phase.

The SQL Query Viewer window displays these columns:

Right-clicking each node pops up a context menu with these options:

Item Expression Viewer

To open the Item Expression Viewer, select ItemExpr... in popup menu or click .

The Item Expression Viewer displays these columns:

Property Browser

To open the Property Browser, select Properties... from context menu.

Property names are highlighted with a gray background. Each property is separated with the next property by "----------end----------" notation.

Select or clear the check boxes to show or hide properties, 

Memo Viewer

The optimization process begins after the normalization phase and currently has two passes. You can use the Memo Viewer to step through the optimization process, and use toolbar icons or menu options to single-step or set breakpoints at specific groups, expressions, tasks, task numbers.

The Memo Viewer appears below the SQL Query Viewer. To show or hide the Memo Viewer, click the .

The Memo Viewer has three panels:

• Memo Grid
• Task List--Please provide a description.
• Context List

Memo Grid

The Memo Grid provides visualization of memo (a global object). Cells in the grid represent physical or logical expressions or plans. Each row has its own group ID. The first column of every row is a group type, not an expression.

To the right of the Memo Grid are the Group, Expr, and Task settings. As optimization steps forward, the contents of the Memo Grid, Task List, and Context List are updated, as well as those three settings. Sometimes,  diplays -1, which means that the current task does not work on any expression or plan of the current group.

When you click a cell in the Memo Grid, the following events are triggerd:

• The SQL Query Viewer displays the tree of the selected expression.
• The Task List is not affected.
• The Context List displays the contexts of the current group.
• If the cell is a plan, the plan's context will be marked green in the Context List.

Task List

Context List

The Context List displays the contexts associated with the currently selected group. When you select a plan in the Memo Grid, its context icon will be marked green. If you select a context in the Context List, its solution (if any) will be marked in the Memo Grid. At most, only one cell can be marked with a solution at a time.

Update Memo

To update the memo, right-click a node in the SQL Query Viewer and click the UpdateMemo option in popup menu. The debugger updates the Memo Grid and its input widgets according to the group and expression number of the selected node, and it also updates the Context List according to the group.

Memo Toolbar Options

Option	Description
	Advances to the next optimization task and updates all the memo panels.
	Stops at the task number specified in the Task setting.
	After selecting a task in the Task List, click this button to start and stop optimization at the selected task.
	Goes to and stops at the group number specified in the Group setting, ignoring the Expr setting.
	Stops at the expression specified by the Group and Expr settings. Note: Sometimes the value in Expr is -1 because no expression or plan is being processed for the current task. In that case, the debugger will stop at any expression of the specified group number. This is what happens when you click the  button.
	Finishes the current optimization pass. The  button does the same thing.
	Finishes all optimization passes.

Note: For the best results, you should run a query more than once to gather information about its maximum task number, group number, and expression number.

Grid Legend

The legend at the bottom of the window identifies what each symbol in the Memo Grid means.

TDB Tree Viewer

The debugger also displays the TDB tree generated by the compiler.

The TDB Tree Viewer displays these columns:

To view the list of expressions associated with a TDB node, click the appropriate expression column.

The expressions window displays these columns: