SQL Repository Overview
The SQL Repository stores the SQL statements that are used in the analysis of database performance. These may be SQL statements that you have identified as critical to the performance of your database application.
SQL Repository Window
View SQL Repository Window
The SQL Repository window displays the information about the SQL statements that are saved in the SQL Repository. It is divided into three panes.
SQL Listing [Left pane]
Displays a tree diagram of the SQL statements and the folders they are stored in.
SQL Listing Details [Top right pane]
Displays detailed information about the folder, the SQL statement, or the folder with its contents depending on which item in the tree in the left pane is selected.
SQL Information Details [Bottom right pane]
The detailed information about the SQL statement displays in the button pages. The buttons for displaying specific information are found at the top of this pane.
Properties button
Displays general information, the SQL name, where the SQL statement was copied from, the logon connection, and database and other settings.
SQL Information button
Displays the SQL text, the query plan, the abstract plan, trace on information, SQL classification, and DDL for any temporary tables used in the SQL statement.
Save the Abstract Plan
The abstract plan for a SQL statement can be saved from this pane. In order to save the abstract plan, it must have been retrieved when the SQL statement was created in the SQL Repository. Before the SQL statement is created, you must enable the dumping of the Abstract Plan in the Optimization or the Scanner Preferences. If you are connected to Adaptive Server 15, use the Abstract Plan settings in the Optimization for ASE 15 tab. Otherwise, use the Abstract Plan settings in the Optimization for Pre-ASE 15 tab.
To save the abstract plan
-
Select the SQL statement in the left pane.
-
Click in the right pane.
-
Click
.
-
In the Save to group field, select the abstract plan group.
-
Click Save.
Add SQL to SQL Repository
Add SQL to SQL Repository
SQL statements are stored in the SQL Repository for use in an Index Impact Analysis, an Index Usage Analysis, a Migration Analysis, or a Configuration Analysis. From within the SQL Repository window, you can add a single SQL statement, some or all SQL statements found by the SQL Scanner, and SQL statements that reference specific tables or views. SQL statements can also be added to the SQL Repository from other modules.
To add a single SQL statement
-
Click
.
-
If no SQL exists in the SQL Repository, then the Add SQL wizard displays automatically. Otherwise, click Add SQL
.
-
The Add SQL wizard has three pages to selects the options for saving the SQL statement to the SQL Repository.
-
After making the selections from the three tabs, click OK to save the SQL to the SQL Repository.
The SQL syntax is checked and the query plan retrieved before adding a new node to the SQL tree view with the SQL name. Each SQL statement added to the SQL Repository contains a query plan, SQL classification type (Simple, Complex or Problematic) and the current connection information (login name, server name, database and user). The query plan stored with the SQL statement is important as it indicates the current performance of the SQL.
To add SQL from the SQL Scanner
-
From the SQL Repository window, select SQL | Add SQL from SQL Scanner.
-
On the General page of the wizard, select the location to save the SQL statements.
-
On the SQL Scanner page, select the Groups or Jobs that contain the SQL you want to add.
-
Click Finish.
To add all SQL statements that references a specific table or view
-
From the SQL Repository window, select SQL | Add SQL by Database Object.
-
In the Database object pane, select the tables or views that you want the SQL to reference.
-
Click Extract. The Save SQL to SQL Repository wizard displays.
-
On the General page, select the location to save the SQL statements.
-
Click Finish.