Application - SQL History and Archive

Aqua Data Studio includes an SQL History feature which provides a history of all SQL statements and scripts that have been executed.

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 information - 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 on what tool the query has been executed. The columns are sortable and filterable. Column sizes and window position are saved and reloaded when the application is restarted.

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

Options - File>Options:[General:SQL History] has options "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.

Compare Functionality - The "Copy Compare" button on the toolbar allows comparison of two SQL statements in a Compare tab after selecting the two statements from SQL History.

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 table

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."

• The SQL History now allows you to automatically archive 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 by a user in File->Options:General:History.
• Queries from SQL History may also be selected in the SQL History window, and manually moved and saved in SQL History Archive.
• A tab has been added to 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 including 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.