SQL Programming Manual for Pascal

Program Compilation and Execution
HP NonStop SQL Programming Manual for Pascal—528614-001
5-11
Running the SQL Compiler
Statistics Information
For the SQL compiler to determine the best execution plan, it needs the current
statistics for any referenced tables. To provide these statistics, a database
administrator can use the UPDATE STATISTICS statement, which writes statistical
information about the tables in the catalog in which the table is registered. For more
information, see the UPDATE STATISTICS statement in the SQL/MP Reference
Manual.
Functions of the SQL Compiler
When you SQL compile a program file, the SQL compiler performs these functions:
•
Resolves names—The compiler expands SQL object names, including DEFINE
names, in the program using the current default volume and the current catalog.
•
DEFINE names that have been used are stored in the SQL object file so they can
be re-used.
•
Performs type checking.
•
Expands views.
•
Checks object references in catalogs—The compiler checks the catalogs for SQL
object names to verify their existence and to read their descriptions. Then the
compiler evaluates the object type and characteristics for each reference.
•
Determines an optimized execution plan—The compiler analyzes the SELECT,
INSERT, UPDATE, and DELETE statements to determine the best access paths
and join, sort, and blocking strategies for executing the statement. Then the
compiler writes the object code for the execution plan to the program file.
•
Generates executable code—The compiler compiles the execution plan into
executable object code.
•
Stores information in catalogs—If the compilation is successful, the compiler
registers the program in the specified PROGRAMS catalog table and stores
dependences in the USAGES table. The compiler also stores dependencies in the
USAGES catalog table of the catalogs where tables, views, and indexes that the
program uses are registered.
•
Marks the program file SQL sensitive and SQL valid—If the compilation is
successful, the compiler sets the SQL sensitive and SQL valid indicators in the
program file label.
•
Generates a listing—The compiler writes all the SQL statements to the program file
and any warning or error messages to the OUT file or device.
•
Estimates execution cost—For SQL DML statements, the compiler lists an
estimate for the cost of processing the statement based on the statistics in the
catalogs.