3.1 Architecture of Integra4 SQL EngineThe following diagram illustrates the internal architecture of Integra4 engine:
3.2 Important Features of Integra4 EngineIntegra4 has ANSI SQL92 intermediate level conformance and also some useful extensions. Given below is a list of important features:
ØConnection and user management
ØCatalogs and schema
Ø Rename schema, table, column
Ø Create, Drop indexes
Ø Alter table
ØAdditional operators: CAST, COALESCE, MOD
Ø CASE construct
Ø String concatenation operator
Ø NULL value support
Ø User Defined Functions: Normal and aggregate
Ø Built-in functions, uniformly available in SQL engine as well as forms and report tools:
Ø INSERT INTO table FROM delimited file / SELECT ..
Ø SELECT .. FROM table INTO delimited file
Ø System tables access through SQL statements
Ø Several pre-canned views for system information (InfoSchema) provides ANSI standard system catalog access
Ø Compiled query support
Ø Stored procedures
Ø Recovery features
Optimizations Done In Integra4Integra4 engine does extensive query optimisations.
It is possible for the user to obtain the query evaluation plan (QEP).
Performance TuningThe following controls are available for users to improve database performance:
Ø Placement control for tables:
Each Integra4 table is normally stored as one ObjecTrieve file consisting of 2 physical files,
one for data and one for index. They are normally created in the database directory. However,
it is possible to create different tables in different devices. Further it is even possible to keep the
data part and index part in different devices for better performance.
Ø Placing different tables in different directories reduces concurrency bottlenecks.
Ø ObjecTrieve also supports the container file feature, using which several tables can be kept in a single pair of physical files. This ensures better disk space utilization for highly dynamic files.
Ø System tables cache size
Ø Sort area size
Ø Physical file manager (ObjecTrieve) cache size
Ø IN list cache size
Ø Memory file size
While Integra4's SQL gives fast access, in situations where very fast access is required,
it is possible to access the tablesdirectly through ObjecTrieve, from C, C++ or Java
bypassing all the overheads of SQL.