6. SQL History (CTRL+ ALT+H)

Aqua Data Studio includes an SQL History, and SQL History Archive which provides a history of all SQL statements and scripts that have been executed within the Query Analyzer, Query Builder, Table Data Editor and SQL Debuggers. Double clicking on an SQL History item writes it into the current tool for reexecution or editing.

Launching the SQL History

The SQL History dialog can be activated by clicking on the SQL History toolbar button. The history window does not need to be closed to continue working on your queries, and may run side-by-side with the main window.

The SQL History window provides a list of previously executed statements from which to choose a query. The ’Max History’ sets the maximum number of SQL commands stored in the history. The ’Max Statments Per Entry’ sets the maximum number of statments stored in a history entry. If the ’Max Statements Per Entry’ is set to 5 and a script of 100 statements are executed only the first five would be stored with that entry.

SQL History Workspace

The SQL History and SQL History Archive display history items in a window with buttons for Select, Select and Close, Delete Selected, Delete All, Archive Selected, Archive All, Save Script, Toggle Preview, Close, Copy Compare, Max History, Max Statements per Entry, Auto Archive checkbox and the Quickfilter.

The Select button writes the currently selected item into the current tool in the Main Application Window. The Select and Close button does the same, but closes the History after writing the item to the Main Application Window. Delete Selected deletes the current item in the SQL History. Delete All deletes all of the contents of the SQL History. Archive Selected places the current item in the SQL History Archive. Archive All writes all of the displayed contents of the SQL History to the SQL Archive. Save Script takes the currently selected item and prompts the user to save it with a file name. Close shuts the history. Copy Compare launches the comparison tool for selected items. The Max History input field sets the number of items visible before storing to the archive. Max Statements per Entry limits the number of statements an individual item in the history can contain. Auto Archive automates the archiving process.  The Quickfilter allows live filtering of the contents of the History grid.

Below these buttons and form fields are tabs for SQL History and SQL Archive.  The SQL History tab displays only the number of entries set by the Max History value. Below each tab the history items are displayed in a grid. The SQL Archive tab displays all queries in the Archive (limited by the archive size set in File->Options->General->SQL History). The contents of the History and Archive grid, like all of the other grids displayed in Aqua Data Studio, can be sorted CTRL + Click on multiple column headers or rearranged by dragging the column headers into new positions. The SQL History Archive has an additional "Information" button, an I within a circle, within its toolbar. Pressing it displays an alert box with the path to the Archive directory, the total number of entries in the archive, the total archive size in Kilobytes, and the date and time of the latest archive entry.

History Grid - The ’Start Date/Time’ and ’End Data/Time’ columns show the time the query started and ended execution. Those columns are formatted according to the locale. The ’Server Type’ indicates on what type of database server the query was executed. The ’Server Name’ column shows the server name. The SQL Statement column shows the sql statemtent being executed. The ’# Stmts’ shows how many statements are stored in that entry. The ’Rows Affected’ column shows how many rows have been affected by the query. The ’Database Name’ column shows the database name. The ’Final Status’ column shows the final status of the query. The ’Tool’ column shows what tool executed the query. The columns are sortable and filterable. Column sizes and window position are saved and reloaded when the application is restarted. Queries from SQL History may also be selected in the SQL History window, and manually moved and saved in SQL History Archive.

Preview Pane - The SQL History panel has a preview pane that displays the full SQL that is currently selected in the history list. This preview may be enabled/disabled on the toolbar with the Toggle Preview button.

Options - These are set in File->Options->General->SQL History. The "maximun number of entries" and "maximum number of statements" for SQL History to determine persistent values, even though values may be temporarily changed in the SQL History dialog.  The SQL History allows automatic archiving of SQL statements that are removed from the SQL History when the number of queries exceeds the defined maximum number. This option can be turned on and off and an archive folder can be selected within File->Options->General->History. It is possible to automatically trim the archive to the specified limit every given number of minutes. This indicates when the Archive discards older archive entries to stay at its size limit. The SQL History Archive can be limited to a specific size to prevent it from consuming too much disk storage. This can be very useful when multiple users record queries to a single archive location .

Compare Functionality - The "Copy Compare" button on the toolbar performs a comparison of two selected SQL statements from the History or Archive in a Copy Compare tool window in the Main Application Window. The Copy Compare tool page has more information on how the Copy Compare Tool works.

Hot keys - Alt-M allows focus on the Max History field, Alt-S allows focus on the Max Statements per entry field and Alt-Q allows focus on the Quick Filter field. Press "Escape" to regain focus on the history grid.

Search functionality - "Find" functionality for both the Grid list and Preview Panel allows searching for text using menu options "Find", "Find Next" and "Find Previous." A tab in the SQL History window allowing a user to search through the archive by keyword and date, and presenting the search results as a table which is similar to the current SQL History tab. The last selected search parameters are persistent across multiple runs of the application. If a search takes an extended period of time, a progress monitor pops up showing the operation progress.

Using SQL History and SQL Archive for Audit Trail and SQL Query Analysis

All SQL Statements executed on Production Servers are stored into user directories. The archives users generate can be examined and analyzed.

AUDIT TRAIL

These files can be parsed and stored in a database for keeping a full audit trail of who did what on the database at what time. This can be valuable for Sarbanes-Oxley compliance or any other regulatory requirements.

SQL QUERY ANALYSIS

These files can be parsed and stored in a database then analyzed for performance and tuning. It is easy to find which queries are: taking the longest time to run, return the largest number of queries, or being executed repeatedly.