jSQL offers a basic SQL execution console rendering results as an HTML table. It also contains a database object browser which can be used to view the source of stored procedures, tables and views to print and modify them. Also included for Sybase servers is a very comprehensive performance monitoring tool that shows all processes currently executing on the server, the line number of the procedure executing, a graph of historical logical and physical i/o and an analysis too to locate hot spots in procedure code.
The application itself is a java servlet web application displaying results and SQL source as HTML in a web browser. This allows links to be inserted into the source viewer to facilitate convenient browsing from one object to those it references. For example, tables referred to in stored procedure source are highlighted with a hyperlink which will take you to the tables underlying definition.
The application is hosted in a number of configurations. It can be deployed as an Eclipse plugin available from DBEclipse.org, run straight off the Internet without instalation as a Signed Java Applet in Internet Explorer at DBExlorer.com or it can be run from the windows or unix command line as you would a conventional web application server. The architecture of the DBEclipse and DBExplorer implementations are a little novel however in that the web server runs on the client PC inside the client process as a thread.
To get an overall feel for jSQL there is a short video which shows the DBEclipse.org version in use inside the Eclipse Workbench.
The video shows the SQL command window, database object browser and source viewer. It then shows the Sybase SQL optimiser for viewing query plans and finally the server monitoring tool showing how server locks and blocking can be diagnosed (taken from a real server, the object names have been anonomised).
Click here to view short video: jSQL in use in Eclipse Workbench.
Screen shots from this video showing particular features of the interface are described in more detail below.
At the left hand side is the object browser frame for database "master" where you can click links to view the list of links to procedures, tables or views enter open the source viewer window described next.
The image below shows how the source for stored procedure sp_getmessage is displayed for browsing. A SQL parser highlights object references and inserts hyperlinks which can be clicked through to the definition of the referenced object. The internal parser can be used to determine all references to a table by column in the database.
The screenshot below shows the procedure "optimiser" in use. In fact it does not optimise a query for you but is a tool that provides the information to make it far easier to do so. Formatted scan counts and the query plan that Sybase has used are displayed alongside the associated source. A good query plan is critical for performance. It can make a hundred-fold difference in execution time to resolve a problem using an index for example.
The final and perhaps most unique feature of jSQL is the server monitor tool. The display below contains a good deal of information about the health of a server. The top section is an historical graph of server utilisation with time spent on logical i/o (CPU) showing as blue and time spent waiting for physical i/o to disk showing as yellow. When blocking occurs where one process is waiting for another process to free locks it has on tables or pages this shows as a red background.
The table shows the currently active processes with an process currently on a CPU highlighted as green.Where the "spid" at the left hand side is highlighted with a link it can be clicked to show the locks by table and type. This is particularly useful looking for any processes on the server that have a transaction open that holds a lock which is blocking other processes. The view below for example shows that spid 269 is being blocked by the lock on table48 held by spid 1078.
A handy feature is that you can click on the utilistaion graph to view what was executing on the server at a time in the past. This allows you to perform a detailed post-mortem of the cause any performance issues or blocking.
The final feature to point out is the third table "Cummulative totals" at the bottom of the page. The monitor page normally updates every 10 seconds and each time a stored procedure is seen to be executing a counter is incremented and displayed in this table along with the procedure line number.. If you leave the monitor running across the course of a day these totals give a good idea where the server is spending most of its time and therefore which code should be targeted for optimisation.
A further video or the monitor tool in use can be viewed here.
This short introduction gives a good idea of the principal tools jSQL offers inside Eclipse or Internet Explorer. For more detailed information contact the author using the link below.