Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

COBOL Conversion

The COBOL conversion searches for three items within the syntax of a SQL statement that are allowed in the COBOL, but are not valid SQL syntax: 1) a dash or minus in a variable name, 2) comments in the middle of the SQL statement, and 3) the ]] (double right square bracket) as the concatenate symbol.

This conversion is only applied when the Scanner Job is added to the Job List in the SQL Scanner window using the COBOL option under the Source Code page in the Add Jobs wizard.

Conversion for variable name

If a variable name contains "-" minus sign, then it is replaced with "_".

Conversion for comment

If the 7th column of the line is an asterisk (*) then the complete line is recognized as a line comment.

Conversion for concatenate character

If ]] (two right square brackets) are used to concatenate column names, they are replaced with a +.

For example:

Original SQL statement

SELECT *

FROM EMPLOYEE  

* Get the department number       

WHERE EMP_ID >  :employee-id

AND    :name-job = ENAME]]JOB

After conversion

SELECT *

FROM EMPLOYEE   -- * Get the department number

WHERE EMP_ID >  @employee_id

AND    ENAME + JOB = @name_job

Note: If your COBOL file has tags at the beginning of the lines of code, you need to use the Number of characters to be skipped at the beginning of every line for all files option found on the SQL Scanner tab in the Preferences window.

 

Related Topic

Index Advisor

Index Advisor Overview

The Index Advisor provides the index candidates by analyzing the structure of a SQL statement and identifying all the related tables and indexes currently used by the SQL statement. By looking into the search arguments and table join conditions of the SQL statement, different index candidates are generated for you to evaluate the effect new indexes may have on database performance. You can further investigate index possibilities by creating your own candidates and grouping the candidates into Index Sets.

 

Related Topic

Index Advisor Window

The Index Advisor window is divided into the following sections:  

Top Pane

SQL Editor tab

Used to enter the SQL statement you wish to analyze for index recommendations.

Database Objects tab

Displays information about the tables used in the SQL statement.

Left Bottom Pane

Displays the table names accessed by the SQL Statement

Right Bottom Pane

Displays specific information about the selected table on different tabs: Definition, Columns, Indexes, Constraints/Keys, and Data.

Index List tab

Displays a list of the individual index candidates proposed by the Index Advisor. These are the indexes used in the Index alternatives displayed in the bottom left pane. The following information displays on this page:

Item

Description

Database

Database where index resides

Owner

Owner of the index

Table

Indexed table

Columns

Indexed column(s)

Index

Index name. All indexes generated by SQL Optimizer will be prefixed with QUEST_SX_IDX_.

Selectivity

Selectivity value. The selectivity is the percentage of rows in the table that the SQL statement selects. A SQL statement that selects a small percentage of the rows from a table has good selectivity, while a query that selects a large percentage of rows has poor selectivity. The maximum selectivity value is 1 and it corresponds to the best selectivity.

Note: A Sample Block of the actual table data is used to calculate the selectivity value therefore it is possible that the value may vary according to what Sample Block is used.

Context

Index DDL for creating the index.

Time tab

Displays the run time information for the SQL statement using the alternative indexes. The values are filled in after the Show Plan, Batch Run, Run for First Record, or Run for All Records functions are executed.

Statistics tab

Displays the run time statistics for the SQL statement using the alternative indexes. These values are filled in after the Batch Run, the Run for First Record, or Run for All Record functions are executed. An image\I_RedX.gif is placed in the far left column if for some reason the index is not created when the SQL statement is executed.

Chart tab

Charts the run time statistics for the SQL statement using the alternative indexes.

Bottom Left Pane

Displays the Used Index which is the index(es) that are currently used by the SQL statement, the Index candidates which are generated by the Index Advisor, or the Index Sets that are created using the User-Defined Index window.

Bottom Right Pane - SQL Information Pane

Displays information about the SQL statement when it is using a specific index.

 

Related Topics

Related Documents